poi导出Excel(分行单元格颜色设置,字体设置,合并单元格,插入图片)
这是一个日报导出功能的代码;图片是用JfreeChars生成好的,话不多少 看代码
public String excelExport(HttpServletRequest request,HttpServletResponse response,CarSaledailyDTO carSaledailyDTO){ //获取需要导出的报表数据
List<List<List>> list = carSaledailyFacade.excelData(carSaledailyDTO,request); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("销售日报"); CellRangeAddress region=new CellRangeAddress(0, 1, 0, 30); sheet.addMergedRegion(region); HSSFRow row=sheet.createRow(0); //合并列 HSSFCell cell=row.createCell(0); HSSFFont f = workbook.createFont(); f.setFontName("黑体"); f.setFontHeightInPoints((short) 22);//字号 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 HSSFCellStyle style = workbook.createCellStyle(); style.setFont(f); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中 cell.setCellValue("日报"); cell.setCellStyle(style); int r =2; for(int i = 0;i<list.size();i++){ r=createRow(list.get(i), sheet,r,workbook); } //cell.setCellStyle(style); //HSSFSheet sheet1 = workbook.createSheet("全国每日提报量和成交合同量"); FileInputStream stream; String path = request.getSession().getServletContext().getRealPath("/"); System.out.println(path); try { stream = new FileInputStream(path+"images\\pic\\line.png"); byte[] bytes=new byte[(int)stream.getChannel().size()]; stream.read(bytes);//读取图片到二进制数组 int pictureIdx = workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 33, (short)20, 60); HSSFPicture pict = patriarch.createPicture(anchor, pictureIdx); //pict.resize();//自动调节图片大小,图片位置信息可能丢失 } catch (Exception e1) { // TODO Auto-generated catch block e1.printStackTrace(); } ByteArrayOutputStream os = new ByteArrayOutputStream(); try { workbook.write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); try { // 设置response参数,可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); //response.setHeader("Content-Disposition", "attachment;filename=" + new String(".xls").getBytes(), "iso-8859-1")); String filename = "SaleDailyReport"+carSaledailyDTO.getSearchDate()+".xls"; response.setHeader("Content-Disposition", "attachment;filename= "+ URLEncoder.encode(filename, "UTF-8")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } if (bis != null) bis.close(); if (bos != null) bos.close(); } catch (final IOException e) { e.printStackTrace(); } return "导出成功!"; }
上面这个方法是koala框架里的基于spring mvc的一个controller,完全可以忽略参数*DTO;
public Integer createRow(List<List> list,HSSFSheet sheet,int r,HSSFWorkbook workbook){ for(int k = 0;k<list.size();k++){ HSSFCellStyle style = workbook.createCellStyle(); //设置边框样式 style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置边框颜色 style.setTopBorderColor(HSSFColor.BLACK.index); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setRightBorderColor(HSSFColor.BLACK.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); if(r==2){ style.setFillForegroundColor(HSSFColor.YELLOW.index); }else if(r==7||r==15||r==23||r==31){ style.setFillForegroundColor(HSSFColor.RED.index); }else if(r==9){ style.setFillForegroundColor(HSSFColor.GOLD.index); }else if(r==17){ style.setFillForegroundColor(HSSFColor.TAN.index); }else if(r==25){ style.setFillForegroundColor(HSSFColor.SEA_GREEN.index); }else{ style.setFillForegroundColor(HSSFColor.WHITE.index); } HSSFRow row = sheet.createRow(r); if(list.get(k)!=null){ for(int j =0;j<list.get(k).size();j++){ HSSFCell cell = row.createCell(j); cell.setCellValue((list.get(k).get(j))+""); cell.setCellStyle(style); } } r+=1; } if(r==8||r==16||r==24){ HSSFRow row1 = sheet.createRow(r); for(int j =0;j<list.get(0).size();j++){ HSSFCellStyle style = workbook.createCellStyle(); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); HSSFCell cell = row1.createCell(j); cell.setCellValue(""); cell.setCellStyle(style); } } r+=1; System.out.println(r); return r; }
除去Excel的标题‘日报’部分 和 图片导入;其余数据写入及单元格样式设计实在createRow方法里面实现的;
需要注意的是:
1.我们在页面导出excel文档时,都会涉及到一个导出路径选择的问题,在第一段代码中设置response参数之后就可以实现;发送到出请求时在直接在页面发送超链接请求,如:
2.使用浏览器导出excel的文件名如果是中文,可能会出现乱码问题(这里火狐浏览器比较特殊,所以为了不出现乱码,建议统一使用英文名)
response.setHeader("Content-Disposition", "attachment;filename=" + new String(".xls").getBytes(), "UTF-8"));// 火狐浏览器
response.setHeader("Content-Disposition", "attachment;filename= "+ URLEncoder.encode(filename, "UTF-8"));//其他