HSSFWorkbook操作Excel例子

@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*256sheet.setColumnWidth(1, 2741);//10sheet.setColumnWidth(2, 2229);//8sheet.setColumnWidth(3, 1717);//6sheet.setColumnWidth(4, 3253);//12sheet.setColumnWidth(5, 1205);//4sheet.setColumnWidth(6, 1717);//6sheet.setColumnWidth(7, 2229);//8sheet.setColumnWidth(8, 3253);//12sheet.setColumnWidth(9, 1205);//4sheet.setColumnWidth(10, 1205);//4sheet.setColumnWidth(11, 1205);//4sheet.setColumnWidth(12, 1717);//6sheet.setColumnWidth(13, 2229);//8sheet.setColumnWidth(14, 1205);//4sheet.setColumnWidth(15, 1973);//7sheet.setColumnWidth(16, 1717);//6sheet.setColumnWidth(17, 1717);//6sheet.setColumnWidth(18, 1717);//6sheet.setColumnWidth(19, 1717);//6String[] 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;}//只有第一个页面需要titlefor (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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.printStackTrace();}}