在实际的项目开发中,经常会有一些涉及到导入导出的文档的功能。apache开源项目之一poi对此有很好的支持,对之前的使用做一些简要的总结。
1,导入jar
为了保证对格式的兼容性,在项目的pom.xml添加这三个jar:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency>
2,生成文件、下载
一般我们对导出的数据变化不会太大,所以考虑到重复使用,我们可以在服务端生成一个文件,在用户再次导出时直接取这个文件下载即可,相当于一个缓存,当然如果服务端文件数过多需要定时任务或者shell去清理.. 不多啰嗦,下面开始贴代码
/** * 生成考勤信息文档 * @param date 导出日期 * @param type 1考勤统计总数word,2考勤详情excle * @return * @throws IOException */ @RequestMapping(value="/export_message",method=RequestMethod.GET) public @ResponseBody CommonResponse export(@RequestParam String date, @RequestParam Integer type, @RequestParam Integer orgId, @RequestParam String orgName, HttpServletResponse response, HttpServletRequest request) throws IOException{ CommonResponse rsp = null; if (!StringUtil.isEmpty(date)) { String fileName = ""; File file; if (type == 1) { fileName = date.trim()+ ".docx"; file = new File(WORD+ fileName); }else{ fileName = date.trim() + orgId + ".xls"; file = new File(EXCEL + fileName); } if (file.exists()) { rsp = new CommonResponse(1, "exists",fileName); }else{ //文件不存在,用poi生成 int rs = messageService.exportMessage(DateUtil.str2Date(date),type,orgId,orgName); if (rs == 1){ rsp = new CommonResponse(0, "ok",fileName); }else{ rsp = new CommonResponse(-1, "error"); } } }else{ //参数错误 rsp = new CommonResponse(-1,"error"); logger.error("Fail to convert the parameters for export_message,date:{}", date); } return rsp; }
/** * 下载考勤信息文档 * @param response * @param request * @throws FileNotFoundException * @throws UnsupportedEncodingException */ @RequestMapping(value="/down_info_file",method = RequestMethod.GET) public @ResponseBody void downFile(HttpServletResponse response,HttpServletRequest request) throws FileNotFoundException, UnsupportedEncodingException { String filePath,newFileName; String fileName = request.getParameter("fileName"); //兼容性配置 if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0){ fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");//firefox }else if(request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0){ fileName = URLEncoder.encode(fileName, "UTF-8");//IE } if (fileName.endsWith(".docx")) filePath = WORD; else filePath = EXCEL; // 读到流中 InputStream inStream = new FileInputStream(filePath + fileName); if (fileName.endsWith(".docx")) newFileName = fileName.substring(0, 10) + "勤务上报数与排名统计.docx"; else newFileName = fileName.substring(0, 10) + "勤务详情统计.xls"; // 设置输出的格式 response.reset(); response.setCharacterEncoding("UTF-8"); response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(newFileName, "UTF-8")+ "\""); // 循环取出流中的数据 byte[] b = new byte[1024]; int len; try { while ((len = inStream.read(b)) > 0) response.getOutputStream().write(b, 0, len); inStream.close(); } catch (IOException e) { logger.error("Download the document is failed,message:{}", e.getMessage()); } }
@Override public int exportMessage(Date date,Integer type,Integer orgId,String orgName) { int rs = 0; File wordfile = new File(WORD); File excelfile = new File(EXCEL); if (!wordfile.exists() || !excelfile.exists()) { wordfile.mkdirs();// 目录不存在的情况下,创建目录。 excelfile.mkdirs(); } String exportExcel = EXCEL + DateUtil.date2Str(date) + orgId +".xls"; String exportWord = WORD + DateUtil.date2Str(date) +".docx"; if (type == 1) { try { List<MessageCount> listMc = new ArrayList<>(); List<MessageTopUser> listMt = null; List<Organization> list = organizationService.findLessFour(); for (int i = 0; i < list.size(); i++) { Integer _orgId = list.get(i).getOrgId(); if (_orgId != 1) { MessageCount mc = messageDao.findAllCountByOrgId(date, _orgId); listMt = messageDao.findTopThree(date); listMc.add(mc); } } rs = exportWord(date, exportWord, listMc, listMt); } catch (Exception e) { e.printStackTrace(); } }else{ List<Message> mes = messageDao.exportMessage(date,orgId); rs = exportExcle(date, mes, exportExcel, orgName); } return rs; }
3,word文档流生成
public int exportWord(Date date,String exportPath, List<MessageCount> listMc, List<MessageTopUser> listMt) throws Exception{ int result = 1; logger.debug("export excle start!,which date is : {}" + date); XWPFDocument doc = new XWPFDocument();//新建一个文档 XWPFParagraph nav = doc.createParagraph(); nav.setStyle("1"); //1级大纲 XWPFRun runNav = nav.createRun(); runNav.setBold(true); //加粗 runNav.setText("【塔式智能系统每日监测情况通报】"); String text = DateUtil.date2Str(date) + ","; int alarm = 0,news = 0,danger = 0,duty = 0,other = 0,police = 0,trail = 0; String allOutText = ""; for (int i = 0; i < listMc.size(); i++) { MessageCount list = listMc.get(i); int getalarm = list.getAlarm().intValue();//一键报警 int getnews = list.getNews().intValue();//动态 int getdanger = list.getDanger().intValue();//隐患 int getduty = list.getDuty().intValue();//勤务记录 int getother = list.getOther().intValue();//其他 int getpolice = list.getPolice().intValue();//警情 int gettrail = list.getTrail().intValue();//线索 alarm += getalarm;//一键报警 news += getnews;//动态 danger += getdanger;//隐患 duty += getduty;//勤务记录 other += getother;//其他 police += getpolice;//警情 trail += gettrail;//线索 int count = getalarm + getnews + getdanger + getduty + getother + getpolice + gettrail; /*if (count != 0) {}*/ String name = organizationService.findByOrgId(list.getOrgId()).getName().replace("中队", "所"); allOutText += i+1 +"." + name + count + "条。"; } int allCount = alarm + news + danger + duty + other + police + trail; text += "发布信息总数" + allCount + "条。"; text += "其中动态" + news + "条,"; text += "警情" + police + "条,"; text += "线索" + trail + "条,"; text += "隐患" + danger + "条,"; text += "勤务记录" + duty + "条,"; text += "一键报警" + alarm + "条,"; text += "其他" + other + "条。"; XWPFParagraph para = doc.createParagraph(); XWPFRun run = para.createRun(); run.setText(text); run.setFontSize(8); run.setFontFamily("微软雅黑"); XWPFParagraph everyOrg = doc.createParagraph(); XWPFRun ev = everyOrg.createRun(); ev.setText(allOutText); ev.setFontSize(8); ev.setFontFamily("微软雅黑"); String topThree = ""; for (int j = 0; j < listMt.size(); j++) { int total = listMt.get(j).getTotal().intValue(); NumberFormat nt = NumberFormat.getPercentInstance(); nt.setMinimumFractionDigits(2);//百分数保留两位 double divisor = (double)total/allCount; String percent = nt.format(divisor); logger.debug("The export word data for percent is "+percent); /*DecimalFormat df2 = new DecimalFormat("###.00");//保留两位小数 System.out.println("小数"+df2.format(p));*/ topThree += j+1 + "." + listMt.get(j).getOrgName().replace("中队", "所") + listMt.get(j).getUname() + ",发布数" + total + "条,占比" + percent + "; "; } XWPFParagraph topThreeP = doc.createParagraph(); XWPFRun evp = topThreeP.createRun(); evp.setText(topThree); evp.setFontSize(8); evp.setFontFamily("微软雅黑"); try { FileOutputStream fout = new FileOutputStream(exportPath); logger.debug("export excle end!"); doc.write(fout); //把doc输出到输出流 fout.close(); } catch (Exception e){ result = 0; logger.error("export excle error!,{}",e.getLocalizedMessage()); } return result; }
4,excel文档流生成
@SuppressWarnings("deprecation") public int exportExcle(Date date,List<Message> mes,String exportPath,String orgName){ int result = 1; logger.debug("Start export excle,which date is : {}" + date); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(DateUtil.date2Str(date)); HSSFRow rowMerging = sheet.createRow((short) 0); HSSFRow row = sheet.createRow((short) 1); //标题样式 HSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 titleStyle.setBorderBottom((short)100);// 下边框 titleStyle.setBorderTop((short)100);// 上边框 HSSFFont titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 20);//设置字体大小 titleFont.setFontName("宋体"); titleFont.setColor(HSSFColor.RED.index);//红字 titleStyle.setFont(titleFont); //导航行样式 HSSFCellStyle navStyle = wb.createCellStyle(); navStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); navStyle.setBorderBottom((short)50);// 下边框 navStyle.setBorderTop((short)50);// 上边框 HSSFFont font = wb.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 14); navStyle.setFont(font); sheet.addMergedRegion(new Region(0, (short) (0), 0,(short) (11))); //设置跨行, 四个参数分别是:起始行,起始列,结束行,结束列 //设置列宽,第一个参数代表列id(从0开始),第2个参数代表宽度值 sheet.setColumnWidth(0, 6500); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 4000); sheet.setColumnWidth(5, 4000); sheet.setColumnWidth(6, 4000); sheet.setColumnWidth(7, 4000); sheet.setColumnWidth(8, 4000); sheet.setColumnWidth(9, 4000); sheet.setColumnWidth(10, 4000); HSSFCell merging = rowMerging.createCell((short) 0); merging.setCellValue(DateUtil.date2Str(date) + orgName + "群防群治数据统计表"); merging.setCellStyle(titleStyle); HSSFCell cell = row.createCell((short) 0); cell.setCellValue("时间"); cell.setCellStyle(navStyle); cell = row.createCell((short) 1); cell.setCellValue("主题"); cell.setCellStyle(navStyle); cell = row.createCell((short) 2); cell.setCellValue("描述"); cell.setCellStyle(navStyle); cell = row.createCell((short) 3); cell.setCellValue("发送人"); cell.setCellStyle(navStyle); cell = row.createCell((short) 4); cell.setCellValue("机构ID"); cell.setCellStyle(navStyle); cell = row.createCell((short) 5); cell.setCellValue("机构名称"); cell.setCellStyle(navStyle); cell = row.createCell((short) 6); cell.setCellValue("派出所ID"); cell.setCellStyle(navStyle); cell = row.createCell((short) 7); cell.setCellValue("所属派出所"); cell.setCellStyle(navStyle); cell = row.createCell((short) 8); cell.setCellValue("发布数"); cell.setCellStyle(navStyle); cell = row.createCell((short) 9); cell.setCellValue("发布率"); cell.setCellStyle(navStyle); cell = row.createCell((short) 10); cell.setCellValue("重复数"); cell.setCellStyle(navStyle); cell = row.createCell((short) 11); cell.setCellValue("不规范数"); cell.setCellStyle(navStyle); //内容样式 HSSFCellStyle contentStyle = wb.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); titleStyle.setBorderBottom((short)50);// 下边框 titleStyle.setBorderTop((short)50);// 上边框 HSSFFont fontcontent = wb.createFont(); fontcontent.setFontName("宋体"); fontcontent.setFontHeightInPoints((short) 12); navStyle.setFont(fontcontent); for (int i = 1; i <= mes.size(); i++) { row = sheet.createRow((int) i + 1); Message rs = mes.get(i - 1); HSSFCell cellContent = row.createCell((short) 0); cellContent.setCellValue(DateUtil.toTimeString(rs.getSendTime())); cellContent.setCellStyle(contentStyle); cellContent = row.createCell((short) 1); cellContent.setCellValue(rs.getMsgTypeName()); cellContent.setCellStyle(contentStyle); cellContent = row.createCell((short) 2); cellContent.setCellValue(StringUtil.isNull(rs.getContent())?"":rs.getContent()); cellContent.setCellStyle(contentStyle); cellContent = row.createCell((short) 3); cellContent.setCellValue(rs.getUname()); cellContent.setCellStyle(contentStyle); cellContent = row.createCell((short) 4); cellContent.setCellValue(rs.getOrgId()); cellContent.setCellStyle(contentStyle); cellContent = row.createCell((short) 5); cellContent.setCellValue(rs.getOrgName()); cellContent.setCellStyle(contentStyle); cellContent = row.createCell((short) 6); int ancestors = rs.getAncestors().equals("") ? rs.getOrgId() : Integer.parseInt(rs.getAncestors()); cellContent.setCellValue(ancestors); cellContent.setCellStyle(contentStyle); } try { FileOutputStream fout = new FileOutputStream(exportPath); logger.debug("export excle end!"); wb.write(fout); fout.close(); } catch (Exception e) { result = 0; logger.error("export excle error!,{}",e.getLocalizedMessage()); } return result; }
5,总结
其实导出是很简单的,贴上代码后基本上意思就出来了,无非就是通过使用XWPFDocument、HSSFWorkbook这两个类,set相关属性值,数据通过sql从数据库里读取数据做一些相关的循环逻辑,再通过文档流的形式反馈给客户端。可能相对来说导入会比较麻烦,首先是从服务端下载一个模板,然后再根据模板填入数据上传到服务器,这个时候需要做大量的验证处理,再反馈给客户有一个预览界面等等,思路大致是这样,后续再整理excel导入。
poi相关属性参考:http://poi.apache.org/
各jar包作用参考:http://www.07net01.com/2014/09/63956.html