@SuppressWarnings("deprecation")
private void ExcelExp2Seal(HttpServletRequest request,String title, String header, String headerName, List<String> result, String ids,
OutputStream output,String planCodeString,String approveName) throws Exception {
// 创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wkb = new HSSFWorkbook();
// 生成一个样式
HSSFCellStyle style = wkb.createCellStyle();
// 设置这些样式
// style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
HSSFFont font = wkb.createFont();
// font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 20);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
//再定义一个style,设置单元格为自动换行
HSSFCellStyle style2 = wkb.createCellStyle();
style2.setWrapText(true);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font2 = wkb.createFont();
font2.setFontHeightInPoints((short) 8);
style2.setFont(font2);
//再定义一个style,
HSSFCellStyle style3 = wkb.createCellStyle();
style3.setWrapText(false);
style3.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
// HSSFFont font2 = wkb.createFont();
// font2.setFontHeightInPoints((short) 7);
style3.setFont(font2);
//又定义一个style,这个样式用于含税总价的统计
HSSFCellStyle style4 = wkb.createCellStyle();
style4.setWrapText(false);
style4.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
// HSSFFont font2 = wkb.createFont();
// font2.setFontHeightInPoints((short) 7);
style4.setFont(font2);
// 建立新的sheet对象(excel的表单)
HSSFSheet sheet = wkb.createSheet(title);
//设置横向打印
sheet.getPrintSetup().setLandscape(true);
//设置纸张类型
sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
sheet.setAutobreaks(true);
sheet.getPrintSetup().setFitWidth((short) 1);
sheet.getPrintSetup().setFitHeight((short) 0);
//设置上下左右页边距
sheet.setMargin(HSSFSheet.TopMargin, 0.1);
sheet.setMargin(HSSFSheet.BottomMargin, 0.01);
sheet.setMargin(HSSFSheet.LeftMargin, 0.03);
sheet.setMargin(HSSFSheet.RightMargin, 0.01);
//列宽:列宽:4,12,6,6,14,4,6,6,14,4,4,4,6,8,4,4,12,4,6,4,4
//设置列宽,为什么设置列宽设置为4*256时,实际列宽为3.29;设置为2*256时,实际列宽为1.29;我只好都加0.71了
sheet.setColumnWidth(0, 1717);//6.71*256
sheet.setColumnWidth(1, 2741);//10
sheet.setColumnWidth(2, 2229);//8
sheet.setColumnWidth(3, 1717);//6
sheet.setColumnWidth(4, 3253);//12
sheet.setColumnWidth(5, 1205);//4
sheet.setColumnWidth(6, 1717);//6
sheet.setColumnWidth(7, 2229);//8
sheet.setColumnWidth(8, 3253);//12
sheet.setColumnWidth(9, 1205);//4
sheet.setColumnWidth(10, 1205);//4
sheet.setColumnWidth(11, 1205);//4
sheet.setColumnWidth(12, 1717);//6
sheet.setColumnWidth(13, 2229);//8
sheet.setColumnWidth(14, 1205);//4
sheet.setColumnWidth(15, 1973);//7
sheet.setColumnWidth(16, 1717);//6
sheet.setColumnWidth(17, 1717);//6
sheet.setColumnWidth(18, 1717);//6
sheet.setColumnWidth(19, 1717);//6
String[] headers = header.split(",");
String[] headerNames = headerName.split(",");
int currentIndex = 0;
for(int m=0;m < result.size();m++){
String planCode = (String) result.get(m);
List<Map<String, Object>> list = tWhxzpPlanSuppliesDemandFpService.getSelectedData(ids, planCode);
//字段(含税总价)的合计
double zfpzjSum = 0.0;
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
if (CommonUtils.null2Empty(map.get("alflag")).equals("N")&&
CommonUtils.null2Empty(map.get("zbz")).equals("本次分配在既定分配原则中找不到满足条件的协议库存,无法分配")) {
list.get(i).put("supplier_name", "分配额度已满");
}
}
int page = 1;
int listLength = list.size();
page += (listLength - 8) / 9;
if(listLength > 8 && (listLength-8) % 9 != 0){
page += 1;
}
//只有第一个页面需要title
for (int i = 1; i <= page; i++) {
//行高: 50,50,50,50,50,50,50,50,50,50,50,42
// 在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
if (i == 1) {
// 表名
// 创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
HSSFRow row0 = sheet.createRow(0 + currentIndex);
row0.setHeightInPoints((short) 40);
HSSFCell cell = row0.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(title);
// 合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0 + currentIndex, 0+currentIndex, 0, 19));
// 在sheet里创建第二行
// HSSFCellStyle style4 = wkb.createCellStyle();
HSSFFont font4 = wkb.createFont();
font4.setFontHeightInPoints((short)8);
style4.setFont(font4);
HSSFRow row1 = sheet.createRow(1+ currentIndex);
row1.setHeightInPoints((short) 15);
Cell cell10 = row1.createCell(0);
cell10.setCellStyle(style4);
cell10.setCellValue("计划编号:"+planCode);
// 表头
HSSFRow row2 = sheet.createRow(2+currentIndex);
row2.setHeightInPoints((short) 50);
//String[] headers = header.split(",");
for (int j = 0; j < headers.length; j++) {
//row2.createCell(j).setCellValue(headers[j]);
HSSFCell cell1 = row2.createCell(j);
cell1.setCellStyle(style2);
cell1.setCellValue(headers[j]);
}
// 在sheet里创建第3~10行
// 表体
for (int j = 3+currentIndex; j < 11+currentIndex; j++) {
//如果打印的数据<8行时
if(listLength == j-3-currentIndex){
// 在sheet里创建最后一部分
// 在sheet里创建11行
// 表说明
HSSFRow row4 = sheet.createRow(j);
row4.setHeightInPoints((short) 16);
//创建单元格并设置单元格内容
HSSFCell cell417 = row4.createCell(16);
cell417.setCellStyle(style3);
cell417.setCellValue("合计金额(元)");
HSSFCell cell419 = row4.createCell(18);
cell419.setCellStyle(style4);
//cell419.setCellValue(CommonUtils.null2Empty(zfpzjSum));
cell419.setCellValue(String.format("%.2f", zfpzjSum));
// 在sheet里创建12行
// 表说明
HSSFRow row5 = sheet.createRow(j+1);
row5.setHeightInPoints((short) 1);
/**
* 设置公章图片开始
*/
BufferedImage bufferimg=null;
ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
try {
String realPath = request.getSession().getServletContext().getRealPath("\\") ;// 文件的硬盘真实路径
String source=realPath+"images\\公章扫描件.png";
//bufferimg=ImageIO.read(new File("d:/公章扫描件1.png"));
bufferimg=ImageIO.read(new File(source));
ImageIO.write(bufferimg, "png", byteArrayOut);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//HSSFClientAnchor anchor=new HSSFClientAnchor(15,2,19,6,(short)16,j+1,(short)19,j+7);
HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,0,0,(short) 0,j-2,(short) 2,j);
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wkb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
/**
* 设置公章图片结束
*/
// 在sheet里创建13行
// 表说明
HSSFRow row6 = sheet.createRow(j+2);
row6.setHeightInPoints((short) 16);
// 创建单元格并设置单元格内容
HSSFCell cell60 = row6.createCell(0);
cell60.setCellStyle(style3);
cell60.setCellValue("分配时间:" + DateUtils.getStringDateShort());
HSSFCell cell66 = row6.createCell(6);
cell66.setCellStyle(style3);
cell66.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
HSSFCell cell614 = row6.createCell(14);
cell614.setCellStyle(style3);
cell614.setCellValue("审核人:" + approveName);
HSSFCell cell617 = row6.createCell(17);
cell617.setCellStyle(style3);
cell617.setCellValue("总条目数:" + listLength);
HSSFCell cell619 = row6.createCell(19);
cell619.setCellStyle(style3);
cell619.setCellValue(i+"/"+page);
// 在sheet里创建14行
// 表说明
HSSFRow row7 = sheet.createRow(j+3);
row7.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell70 = row7.createCell(0);
cell70.setCellStyle(style3);
cell70.setCellValue("注:");
HSSFCell cell71 = row7.createCell(1);
cell71.setCellStyle(style3);
cell71.setCellValue("1、本分配表所列单价均为依据采购合同实施价格联动后的结算价格,各指定最终用户(项目实施单位)应在本表分配日期的当月与供应商签订《采购供货单》。");
// 在sheet里创建15行
// 表说明
HSSFRow row8 = sheet.createRow(j+4);
row8.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell81 = row8.createCell(1);
cell81.setCellStyle(style3);
cell81.setCellValue("2、由于指定最终用户(项目实施单位)的原因,未在本表分配日期的当月与供应商签订《采购供货单》,所造成的合同索赔及价差损失,由指定最终用户(项目实施单位)承担。");
// 在sheet里创建16行
// 表说明
HSSFRow row9 = sheet.createRow(j+5);
row9.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell91 = row9.createCell(1);
cell91.setCellStyle(style3);
cell91.setCellValue("3、由于供应商的原因,未在本表分配日期的当月签订《采购供货单》的,各项目实施单位应在当月上报大客户室,由大客户室转物资中心协调处理。");
//当listLength <= 7时,补齐当前页
for(int n=1;n <= 8-listLength;n++){
HSSFRow rown = sheet.createRow(j+5+n);
rown.setHeightInPoints((short) 50);
}
// // 表说明
// HSSFRow row4 = sheet.createRow(j);
// row4.setHeightInPoints((short) 30);
// // 创建单元格并设置单元格内容
// HSSFCell cell40 = row4.createCell(0);
// cell40.setCellStyle(style3);
// cell40.setCellValue("分配时间:" + DateUtils.getStringDateShort());
// HSSFCell cell46 = row4.createCell(6);
// cell46.setCellStyle(style3);
// cell46.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
// HSSFCell cell414 = row4.createCell(14);
// cell414.setCellStyle(style3);
// cell414.setCellValue("审核人:" + approveName);
// HSSFCell cell417 = row4.createCell(19);
// cell417.setCellStyle(style3);
// cell417.setCellValue(i+"/"+page);
break;
}
HSSFRow row = sheet.createRow(j);
row.setHeightInPoints((short) 50);
for (int k = 0; k < headers.length; k++) {
HSSFCell cellData = row.createCell(k);
cellData.setCellStyle(style2);
cellData.setCellValue(CommonUtils.null2Empty(list.get(j-3-currentIndex).get(headerNames[k])));
if(k == headers.length - 1){
BigDecimal zfpzj = (BigDecimal) list.get(j-3-currentIndex).get(headerNames[k]);
try {
zfpzjSum += zfpzj.doubleValue();
} catch (Exception e) {
// TODO Auto-generated catch block
}
}
}
//当前页的最后一行数据打印完后
if(j == 10+currentIndex){
// 在sheet里创建11行
// 表说明
HSSFRow row4 = sheet.createRow(j+1);
row4.setHeightInPoints((short) 16);
// 在sheet里创建12行
// 表说明
HSSFRow row5 = sheet.createRow(j+2);
row5.setHeightInPoints((short) 1);
/**
* 设置公章图片开始
*/
BufferedImage bufferimg=null;
ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
try {
String realPath = request.getSession().getServletContext().getRealPath("\\") ;// 文件的硬盘真实路径
String source=realPath+"images\\公章扫描件.png";
//bufferimg=ImageIO.read(new File("d:/公章扫描件1.png"));
bufferimg=ImageIO.read(new File(source));
ImageIO.write(bufferimg, "png", byteArrayOut);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//HSSFClientAnchor anchor=new HSSFClientAnchor(15,2,19,6,(short)16,j+1,(short)19,j+7);
HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,0,0,(short) 0,j-1,(short) 2,j+1);
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wkb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
/**
* 设置公章图片结束
*/
// 在sheet里创建13行
// 表说明
HSSFRow row6 = sheet.createRow(j+3);
row6.setHeightInPoints((short) 16);
// 创建单元格并设置单元格内容
HSSFCell cell60 = row6.createCell(0);
cell60.setCellStyle(style3);
cell60.setCellValue("分配时间:" + DateUtils.getStringDateShort());
HSSFCell cell66 = row6.createCell(6);
cell66.setCellStyle(style3);
cell66.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
HSSFCell cell614 = row6.createCell(14);
cell614.setCellStyle(style3);
cell614.setCellValue("审核人:" + approveName);
//如果数据只有8行,就打出合计金额
if(listLength == 8){
//创建单元格并设置单元格内容
HSSFCell cell417 = row4.createCell(16);
cell417.setCellStyle(style3);
cell417.setCellValue("合计金额(元)");
HSSFCell cell419 = row4.createCell(18);
cell419.setCellStyle(style4);
//cell419.setCellValue(CommonUtils.null2Empty(zfpzjSum));
cell419.setCellValue(String.format("%.2f", zfpzjSum));
HSSFCell cell617 = row6.createCell(17);
cell617.setCellStyle(style3);
cell617.setCellValue("总条目数:" + listLength);
}
HSSFCell cell619 = row6.createCell(19);
cell619.setCellStyle(style3);
cell619.setCellValue(i+"/"+page);
// 在sheet里创建14行
// 表说明
HSSFRow row7 = sheet.createRow(j+4);
row7.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell70 = row7.createCell(0);
cell70.setCellStyle(style3);
cell70.setCellValue("注:");
HSSFCell cell71 = row7.createCell(1);
cell71.setCellStyle(style3);
cell71.setCellValue("1、本分配表所列单价均为依据采购合同实施价格联动后的结算价格,各指定最终用户(项目实施单位)应在本表分配日期的当月与供应商签订《采购供货单》。");
// 在sheet里创建15行
// 表说明
HSSFRow row8 = sheet.createRow(j+5);
row8.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell81 = row8.createCell(1);
cell81.setCellStyle(style3);
cell81.setCellValue("2、由于指定最终用户(项目实施单位)的原因,未在本表分配日期的当月与供应商签订《采购供货单》,所造成的合同索赔及价差损失,由指定最终用户(项目实施单位)承担。");
// 在sheet里创建16行
// 表说明
HSSFRow row9 = sheet.createRow(j+6);
row9.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell91 = row9.createCell(1);
cell91.setCellStyle(style3);
cell91.setCellValue("3、由于供应商的原因,未在本表分配日期的当月签订《采购供货单》的,各项目实施单位应在当月上报大客户室,由大客户室转物资中心协调处理。");
}
}
}else{
// 表头
HSSFRow row1 = sheet.createRow(16*(i-1)+1+currentIndex);
row1.setHeightInPoints((short) 50);
//String[] headers = header.split(",");
for (int j = 0; j < headers.length; j++) {
//row2.createCell(j).setCellValue(headers[j]);
HSSFCell cell1 = row1.createCell(j);
cell1.setCellStyle(style2);
cell1.setCellValue(headers[j]);
}
// 表体17-25,33-41
//String[] headerNames = headerName.split(",");
for (int j = 16*i-15+1+currentIndex; j < 16*i-6+1+currentIndex; j++) {
if(listLength == j-7*i+5-1-currentIndex){
// // 在sheet里创建最后一行
// // 表说明
// HSSFRow row4 = sheet.createRow(j);
// row4.setHeightInPoints((short) 30);
// // 创建单元格并设置单元格内容
// HSSFCell cell40 = row4.createCell(0);
// cell40.setCellStyle(style3);
// cell40.setCellValue("分配时间:" + DateUtils.getStringDateShort());
// HSSFCell cell46 = row4.createCell(6);
// cell46.setCellStyle(style3);
// cell46.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
// HSSFCell cell414 = row4.createCell(14);
// cell414.setCellStyle(style3);
// cell414.setCellValue("审核人:" + approveName);
// HSSFCell cell417 = row4.createCell(19);
// cell417.setCellStyle(style3);
// cell417.setCellValue(i+"/"+page);
// 在sheet里创建最后一部分
// 在sheet里创建11行
// 表说明
HSSFRow row4 = sheet.createRow(j);
row4.setHeightInPoints((short) 16);
//创建单元格并设置单元格内容
HSSFCell cell417 = row4.createCell(16);
cell417.setCellStyle(style3);
cell417.setCellValue("合计金额(元)");
HSSFCell cell419 = row4.createCell(18);
cell419.setCellStyle(style4);
//cell419.setCellValue(CommonUtils.null2Empty(zfpzjSum));
cell419.setCellValue(String.format("%.2f", zfpzjSum));
// 在sheet里创建12行
// 表说明
HSSFRow row5 = sheet.createRow(j+1);
row5.setHeightInPoints((short) 1);
/**
* 设置公章图片开始
*/
BufferedImage bufferimg=null;
ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
try {
String realPath = request.getSession().getServletContext().getRealPath("\\") ;// 文件的硬盘真实路径
String source=realPath+"images\\公章扫描件.png";
//bufferimg=ImageIO.read(new File("d:/公章扫描件1.png"));
bufferimg=ImageIO.read(new File(source));
ImageIO.write(bufferimg, "png", byteArrayOut);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//HSSFClientAnchor anchor=new HSSFClientAnchor(15,2,19,6,(short)16,j+1,(short)19,j+7);
HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,0,0,(short) 0,j-2,(short) 2,j);
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wkb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
/**
* 设置公章图片结束
*/
// 在sheet里创建13行
// 表说明
HSSFRow row6 = sheet.createRow(j+2);
row6.setHeightInPoints((short) 16);
// 创建单元格并设置单元格内容
HSSFCell cell60 = row6.createCell(0);
cell60.setCellStyle(style3);
cell60.setCellValue("分配时间:" + DateUtils.getStringDateShort());
HSSFCell cell66 = row6.createCell(6);
cell66.setCellStyle(style3);
cell66.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
HSSFCell cell614 = row6.createCell(14);
cell614.setCellStyle(style3);
cell614.setCellValue("审核人:" + approveName);
HSSFCell cell617 = row6.createCell(17);
cell617.setCellStyle(style3);
cell617.setCellValue("总条目数:" + listLength);
HSSFCell cell619 = row6.createCell(19);
cell619.setCellStyle(style3);
cell619.setCellValue(i+"/"+page);
// 在sheet里创建14行
// 表说明
HSSFRow row7 = sheet.createRow(j+3);
row7.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell70 = row7.createCell(0);
cell70.setCellStyle(style3);
cell70.setCellValue("注:");
HSSFCell cell71 = row7.createCell(1);
cell71.setCellStyle(style3);
cell71.setCellValue("1、本分配表所列单价均为依据采购合同实施价格联动后的结算价格,各指定最终用户(项目实施单位)应在本表分配日期的当月与供应商签订《采购供货单》。");
// 在sheet里创建15行
// 表说明
HSSFRow row8 = sheet.createRow(j+4);
row8.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell81 = row8.createCell(1);
cell81.setCellStyle(style3);
cell81.setCellValue("2、由于指定最终用户(项目实施单位)的原因,未在本表分配日期的当月与供应商签订《采购供货单》,所造成的合同索赔及价差损失,由指定最终用户(项目实施单位)承担。");
// 在sheet里创建16行
// 表说明
HSSFRow row9 = sheet.createRow(j+5);
row9.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell91 = row9.createCell(1);
cell91.setCellStyle(style3);
cell91.setCellValue("3、由于供应商的原因,未在本表分配日期的当月签订《采购供货单》的,各项目实施单位应在当月上报大客户室,由大客户室转物资中心协调处理。");
//当listLength <= 8时,补齐当前页
for(int n=1;n <= 9*i-1-listLength;n++){
HSSFRow rown = sheet.createRow(j+5+n);
rown.setHeightInPoints((short) 50);
}
break;
}
HSSFRow row = sheet.createRow(j);
row.setHeightInPoints((short) 50);
for (int k = 0; k < headers.length; k++) {
HSSFCell cellData = row.createCell(k);
cellData.setCellStyle(style2);
cellData.setCellValue(CommonUtils.null2Empty(list.get(j-7*i+5-1-currentIndex).get(headerNames[k])));
if(k == headers.length - 1){
BigDecimal zfpzj = (BigDecimal) list.get(j-7*i+5-1-currentIndex).get(headerNames[k]);
try {
zfpzjSum += zfpzj.doubleValue();
} catch (Exception e) {
// TODO Auto-generated catch block
}
}
}
if(j == 16*i-7+1+currentIndex){
// // 在sheet里创建最后一行
// // 表说明
// HSSFRow row4 = sheet.createRow(j+1);
// row4.setHeightInPoints((short) 30);
// // 创建单元格并设置单元格内容
// HSSFCell cell40 = row4.createCell(0);
// cell40.setCellStyle(style3);
// cell40.setCellValue("分配时间:" + DateUtils.getStringDateShort());
// HSSFCell cell46 = row4.createCell(6);
// cell46.setCellStyle(style3);
// cell46.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
// HSSFCell cell414 = row4.createCell(14);
// cell414.setCellStyle(style3);
// cell414.setCellValue("审核人:" + approveName);
// HSSFCell cell417 = row4.createCell(19);
// cell417.setCellStyle(style3);
// cell417.setCellValue(i+"/"+page);
// 在sheet里创建11行
// 表说明
HSSFRow row4 = sheet.createRow(j+1);
row4.setHeightInPoints((short) 16);
// 在sheet里创建12行
// 表说明
HSSFRow row5 = sheet.createRow(j+2);
row5.setHeightInPoints((short) 1);
/**
* 设置公章图片开始
*/
BufferedImage bufferimg=null;
ByteArrayOutputStream byteArrayOut=new ByteArrayOutputStream();
try {
String realPath = request.getSession().getServletContext().getRealPath("\\") ;// 文件的硬盘真实路径
String source=realPath+"images\\公章扫描件.png";
//bufferimg=ImageIO.read(new File("d:/公章扫描件1.png"));
bufferimg=ImageIO.read(new File(source));
ImageIO.write(bufferimg, "png", byteArrayOut);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
//HSSFClientAnchor anchor=new HSSFClientAnchor(15,2,19,6,(short)16,j+1,(short)19,j+7);
HSSFClientAnchor anchor=new HSSFClientAnchor(0,0,0,0,(short) 0,j-1,(short) 2,j+1);
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wkb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
/**
* 设置公章图片结束
*/
// 在sheet里创建13行
// 表说明
HSSFRow row6 = sheet.createRow(j+3);
row6.setHeightInPoints((short) 16);
// 创建单元格并设置单元格内容
HSSFCell cell60 = row6.createCell(0);
cell60.setCellStyle(style3);
cell60.setCellValue("分配时间:" + DateUtils.getStringDateShort());
HSSFCell cell66 = row6.createCell(6);
cell66.setCellStyle(style3);
cell66.setCellValue("分配人:" + ResourceUtil.getSessionUserName().getRealName());
HSSFCell cell614 = row6.createCell(14);
cell614.setCellStyle(style3);
cell614.setCellValue("审核人:" + approveName);
if(listLength == 9*i-1){
//创建单元格并设置单元格内容
HSSFCell cell417 = row4.createCell(16);
cell417.setCellStyle(style3);
cell417.setCellValue("合计金额(元)");
HSSFCell cell419 = row4.createCell(18);
cell419.setCellStyle(style4);
//cell419.setCellValue(CommonUtils.null2Empty(zfpzjSum));
cell419.setCellValue(String.format("%.2f", zfpzjSum));
HSSFCell cell617 = row6.createCell(17);
cel}l617.setCellStyle(style3);
cell617.setCellValue("总条目数:" + listLength);
}
HSSFCell cell619 = row6.createCell(19);
cell619.setCellStyle(style3);
cell619.setCellValue(i+"/"+page);
// 在sheet里创建14行
// 表说明
HSSFRow row7 = sheet.createRow(j+4);
row7.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell70 = row7.createCell(0);
cell70.setCellStyle(style3);
cell70.setCellValue("注:");
HSSFCell cell71 = row7.createCell(1);
cell71.setCellStyle(style3);
cell71.setCellValue("1、本分配表所列单价均为依据采购合同实施价格联动后的结算价格,各指定最终用户(项目实施单位)应在本表分配日期的当月与供应商签订《采购供货单》。");
// 在sheet里创建15行
// 表说明
HSSFRow row8 = sheet.createRow(j+5);
row8.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell81 = row8.createCell(1);
cell81.setCellStyle(style3);
cell81.setCellValue("2、由于指定最终用户(项目实施单位)的原因,未在本表分配日期的当月与供应商签订《采购供货单》,所造成的合同索赔及价差损失,由指定最终用户(项目实施单位)承担。");
// 在sheet里创建16行
// 表说明
HSSFRow row9 = sheet.createRow(j+6);
row9.setHeightInPoints((short) 15);
//创建单元格并设置单元格内容
HSSFCell cell91 = row9.createCell(1);
cell91.setCellStyle(style3);
cell91.setCellValue("3、由于供应商的原因,未在本表分配日期的当月签订《采购供货单》的,各项目实施单位应在当月上报大客户室,由大客户室转物资中心协调处理。");
}
}
}
}
currentIndex += 16 * page + 1;
}
try {
wkb.write(output);
output.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
HSSFWorkbook操作Excel例子
最新推荐文章于 2024-08-22 16:32:45 发布