poi 导出excel 生成等比例图片
poi 导出的带等比例图片方法
/** * * <p>Description: 将一物一码列表导出到excel</p> * @param response * @param list * @throws IOException */ public void exportExcel(DispMeta meta) throws IOException { Map<String, Object> params = new HashMap<String, Object>(); params.put("orderNum", meta.getRequest().getParameter("orderNum")); meta.setParams(params); IDispApi service; meta.setParams(params); meta.setMapkey("OneThingAndOneYardService.queryProductCodeListExport"); try { service = find(ModulePbDesc.PB_SALES_02, meta.getMapkey()); service.disp(meta); } catch (DispException e) { e.printStackTrace(); } ArrayList<Map<String,Object>> list = (ArrayList<Map<String, Object>>) meta.getResult(); HSSFWorkbook workbook = new HSSFWorkbook ();// 创建一个Excel文件 HSSFSheet sheet = workbook.createSheet("一物一码");// 创建一个Excel的Sheet sheet.setColumnWidth(0, 40 * 256); sheet.setColumnWidth(1, 40 * 256); sheet.setColumnWidth(2, 40 * 256); sheet.setColumnWidth(3, 40 * 256); sheet.setColumnWidth(4, 40 * 256); sheet.setColumnWidth(5, 40 * 256); sheet.setColumnWidth(6, 40 * 256); sheet.setColumnWidth(7, 40 * 256); HSSFRow row4 = sheet.createRow(0); HSSFCell cell4_1 = row4.createCell(0); HSSFCell cell4_2 = row4.createCell(1); HSSFCell cell4_3 = row4.createCell(2); HSSFCell cell4_4 = row4.createCell(3); HSSFCell cell4_5 = row4.createCell(4); HSSFCell cell4_6 = row4.createCell(5); HSSFCell cell4_7 = row4.createCell(6); HSSFCell cell4_8 = row4.createCell(7); cell4_1.setCellValue( "一物一码" ); cell4_2.setCellValue( "订单编号" ); cell4_3.setCellValue( "商品编码" ); cell4_4.setCellValue( "规格材质" ); cell4_5.setCellValue( "主人账号" ); cell4_6.setCellValue( "柜号" ); cell4_7.setCellValue( "配置号" ); cell4_8.setCellValue( "生效日期" ); HSSFCellStyle style4= workbook.createCellStyle(); style4.setAlignment(HSSFCellStyle. ALIGN_CENTER);//水平居中 style4.setVerticalAlignment(HSSFCellStyle. VERTICAL_CENTER);//垂直居中 style4.setWrapText( true);//自动换行 style4.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); style4.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFFont font4 = workbook.createFont(); // font.setFontName("华文行楷");//设置字体名称 font4.setFontHeightInPoints(( short)15);//设置字号 // font4.setBoldweight(boldweight)( true); style4.setFont(font4); // cell4.setCellStyle(style4); cell4_1.setCellStyle(style4); cell4_2.setCellStyle(style4); cell4_3.setCellStyle(style4); cell4_4.setCellStyle(style4); cell4_5.setCellStyle(style4); cell4_6.setCellStyle(style4); cell4_7.setCellStyle(style4); cell4_8.setCellStyle(style4); //row 4 - items List header -------------------------end //row 5 - items List -------------------------start for (int i = 0; i < list.size(); i++) { HSSFRow row5 = sheet.createRow( i+1); HSSFCell cell5_1 = row5.createCell(0); HSSFCell cell5_2 = row5.createCell(1); HSSFCell cell5_3 = row5.createCell(2); HSSFCell cell5_4 = row5.createCell(3); HSSFCell cell5_5 = row5.createCell(4); HSSFCell cell5_6 = row5.createCell(5); HSSFCell cell5_7 = row5.createCell(6); HSSFCell cell5_8 = row5.createCell(7); Map<String, Object> map = list.get(i); // img --start ByteArrayOutputStream outStream_item = new ByteArrayOutputStream(); // 将图片写入流中 BufferedImage bufferImg_item = ImageIO.read(new FileInputStream("D:"+map.get("img_code").toString())); ImageIO. write(bufferImg_item, "PNG", outStream_item); row5.setHeightInPoints(80); int width = bufferImg_item.getWidth();//原始宽度 int height = bufferImg_item.getHeight();//原始高度 // 一个12号字体的宽度为13,前面已设置了列的宽度为30*256，故这里的等比例高度计算如下 height = (int) Math.round((height * (30 * 13) * 1.0 / width)); // excel单元格高度是以点单位，1点=2像素; POI中Height的单位是1/20个点，故设置单元的等比例高度如下 row5.setHeight((short) (height / 2 * 20)); // 利用HSSFPatriarch将图片写入EXCEL HSSFPatriarch patri_item = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor_item = new HSSFClientAnchor(0, 0, 0, 0, (short ) 0, i+1, (short) 1, i+2); patri_item.createPicture(anchor_item, workbook.addPicture(outStream_item.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); // img -- end //订单编号 cell5_2.setCellValue(map.get("order_num").toString()); //商品编码 cell5_3.setCellValue(map.get("product_num").toString()); //规格材质 cell5_4.setCellValue(map.get("size_name").toString()); //主人账号 cell5_5.setCellValue(map.get("host_account")==null?"":map.get("host_account").toString()); //柜号 cell5_6.setCellValue(map.get("cabinet_number").toString()); //配置号 cell5_7.setCellValue(map.get("config_num").toString()); //生效日期 cell5_8.setCellValue(map.get("effective_time").toString()); HSSFCellStyle style5=workbook.createCellStyle(); style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 style5.setWrapText( true);//自动换行 cell5_2.setCellStyle(style5); cell5_3.setCellStyle(style5); cell5_4.setCellStyle(style5); cell5_5.setCellStyle(style5); cell5_6.setCellStyle(style5); cell5_7.setCellStyle(style5); cell5_8.setCellStyle(style5); } //row 5 - items List -------------------------end // 第六步，将文件存到指定位置 try { String excelName="一物一码.xls"; meta.getResponse().addHeader( "Content-Disposition", "attachment;filename=" + new String(excelName.getBytes("gb2312" ), "ISO-8859-1" )); // response.addHeader("Content-Length", "" + 1024); meta.getResponse().setContentType("application/vnd.ms-excel;charset=utf-8" ); BufferedOutputStream out = new BufferedOutputStream(meta.getResponse().getOutputStream()); workbook.write(out); out.close(); } catch (Exception e ) { e.printStackTrace(); } }
