java poi导出excel模板_使用POI导出Excel（二）-利用模板

二、本次功能需求

给了一个模板，里面有6个sheet页，每页里面都需要填充相应的数据。如图：

三、需求分析

1、分了6个sheet页，每页的数据都不一样，首先代码里要获得它们的数据，然后6个sheet页只能一个个填进去，没法批量操作。

2、如果直接代码创建Excel并画表格样式和数据，那么工程量将会很大，而且会导致代码很乱。我采用的方法是把模板保存到项目里，再通过路径获取到该模板文件，把其内容全部复制到新创建的Excel中，再往里填充数据。

3、注意到第一张图中有总计这一行，我的建议是先在代码中计算出相应的数据，再填充，我也是这样做的。当然，我的第一想法是给它设置函数公式，但是后来我并没有这么做。

4、从第二张图中可以看得出要填充的数据的行是不固定的，除非规定了只填充前几条数据。而在11、12行中的数据是已经有了的，当填充的数据太多，就会覆盖掉那行数据。有两种方法解决，一是把它获取到，先填充数据覆盖掉，再在最后一条数据的下一行把它再填充回去。二是根据数据的总条数通过代码往中间插入空白行。两种方法都可行，我采用了第二种。

四、上代码

1 public voidexportAll() {2 OutputStream out = null;3 try{4 //获取数据

5 List talentFlows = this.talentFlowQuestionnaireService.listAnalysis();6 List basicSituations = this.basicSituationService.listAnalysis();7 List talentInputs = this.talentInputService.listAnalysis();8 List talentDemands = this.talentDemandService.listAnalysis();9 List talentRosters = this.rosterService.listAnalysis();10 Map results = new HashMap<>();11 results.put("talentFlows", talentFlows);12 results.put("basicSituations", basicSituations);13 results.put("talentInputs", talentInputs);14 results.put("talentDemands", talentDemands);15 results.put("talentRosters", talentRosters);16

17 //拿到模板文件

18 String path = ServletActionContext.getServletContext().getRealPath("/");19 String filePath = path + "\\resources\\temp\\人才统计报表模板.xls";20 FileInputStream tps = new FileInputStream(newFile(filePath));21 final HSSFWorkbook tpWorkbook = newHSSFWorkbook(tps);22 out =response.getOutputStream();23 response.reset();24 response.setHeader("content-disposition",25 "attachment;filename=" + new String(("人才统计报表").getBytes("gb2312"), "ISO8859-1") + ".xls");26 response.setContentType("APPLICATION/msexcel");27 //新建一个Excel的工作空间

28 HSSFWorkbook workbook = newHSSFWorkbook();29 //把模板复制到新建的Excel

30 workbook =tpWorkbook;31 //填充数据

32 this.excelService.addData(workbook, results);33 //输出Excel内容，生成Excel文件

34 workbook.write(out);35 } catch (finalIOException e) {36 LOGGER.error(e);37 } catch (finalIllegalArgumentException e) {38 LOGGER.error(e);39 } catch (finalException e) {40 LOGGER.error(e);41 } finally{42 try{43 //最后记得关闭输出流

44 response.flushBuffer();45 if (out != null) {46 out.flush();47 out.close();48 }49 } catch (finalIOException e) {50 LOGGER.error(e);51 }52 }53 }

1 /**

2 * 第二个sheet页：人才流动情况表3 *4 *@paramworkbook5 *@paramtalentFlows6 */

7 private void addTalentFlows(HSSFWorkbook workbook, ListtalentFlows) {8 //获取第二个sheet页

9 Sheet talentFlowSheet = workbook.getSheetAt(1);10 Row talentFlowRow = talentFlowSheet.getRow(0);11 //声明总计的那几个数据

12 Integer totalLastYearTotal = 0;13 Integer totalHighLevelTalent = 0;14 Integer totalUndergraduateAndGraduate = 0;15 Integer totalCollegeStudents = 0;16 Integer totalSocialTalent = 0;17 Integer totalMilitaryTransferCadres = 0;18 Integer totalReturnees = 0;19 Integer totalRetirement = 0;20 Integer totalResignation = 0;21 Integer totalDismiss = 0;22 Integer totalOther = 0;23 Integer totalAverageIncrease = 0;24 Integer totalAverageReduction = 0;25 //循环数据

26 for(BaseTalentFlowAnalysisGridVo baseTalentFlowAnalysisGridVo : talentFlows) {27 //循环行

28 for (int tr = 4; tr < 11; tr++) {29 talentFlowRow =talentFlowSheet.getRow(tr);30 //当数据的项目字段和行的第一列内容相等，则把该条数据填入该行

31 if (talentFlowRow.getCell(0).getStringCellValue()32 .equals(baseTalentFlowAnalysisGridVo.getFkProjectName())) {33 talentFlowRow.getCell(1).setCellValue(baseTalentFlowAnalysisGridVo.getLastYearTotal());34 //给总计做计算

35 totalLastYearTotal +=baseTalentFlowAnalysisGridVo.getLastYearTotal();36 talentFlowRow.getCell(2).setCellValue(baseTalentFlowAnalysisGridVo.getHighLevelTalent());37 totalHighLevelTalent +=baseTalentFlowAnalysisGridVo.getHighLevelTalent();38 talentFlowRow.getCell(3).setCellValue(baseTalentFlowAnalysisGridVo.getUndergraduateAndGraduate());39 totalUndergraduateAndGraduate +=baseTalentFlowAnalysisGridVo.getUndergraduateAndGraduate();40 talentFlowRow.getCell(4).setCellValue(baseTalentFlowAnalysisGridVo.getCollegeStudents());41 totalCollegeStudents +=baseTalentFlowAnalysisGridVo.getCollegeStudents();42 talentFlowRow.getCell(5).setCellValue(baseTalentFlowAnalysisGridVo.getSocialTalent());43 totalSocialTalent +=baseTalentFlowAnalysisGridVo.getSocialTalent();44 talentFlowRow.getCell(6).setCellValue(baseTalentFlowAnalysisGridVo.getMilitaryTransferCadres());45 totalMilitaryTransferCadres +=baseTalentFlowAnalysisGridVo.getMilitaryTransferCadres();46 talentFlowRow.getCell(7).setCellValue(baseTalentFlowAnalysisGridVo.getReturnees());47 totalReturnees +=baseTalentFlowAnalysisGridVo.getReturnees();48 talentFlowRow.getCell(8).setCellValue(baseTalentFlowAnalysisGridVo.getRetirement());49 totalRetirement +=baseTalentFlowAnalysisGridVo.getRetirement();50 talentFlowRow.getCell(9).setCellValue(baseTalentFlowAnalysisGridVo.getResignation());51 totalResignation +=baseTalentFlowAnalysisGridVo.getResignation();52 talentFlowRow.getCell(10).setCellValue(baseTalentFlowAnalysisGridVo.getDismiss());53 totalDismiss +=baseTalentFlowAnalysisGridVo.getDismiss();54 talentFlowRow.getCell(11).setCellValue(baseTalentFlowAnalysisGridVo.getOther());55 totalOther +=baseTalentFlowAnalysisGridVo.getOther();56 talentFlowRow.getCell(12).setCellValue(baseTalentFlowAnalysisGridVo.getAverageIncrease());57 totalAverageIncrease +=baseTalentFlowAnalysisGridVo.getAverageIncrease();58 talentFlowRow.getCell(13).setCellValue(baseTalentFlowAnalysisGridVo.getAverageReduction());59 totalAverageReduction +=baseTalentFlowAnalysisGridVo.getAverageReduction();60 break;61 }62 }63 }64 //给总计行填充数据

65 talentFlowRow = talentFlowSheet.getRow(11);66 talentFlowRow.getCell(1).setCellValue(totalLastYearTotal);67 talentFlowRow.getCell(2).setCellValue(totalHighLevelTalent);68 talentFlowRow.getCell(3).setCellValue(totalUndergraduateAndGraduate);69 talentFlowRow.getCell(4).setCellValue(totalCollegeStudents);70 talentFlowRow.getCell(5).setCellValue(totalSocialTalent);71 talentFlowRow.getCell(6).setCellValue(totalMilitaryTransferCadres);72 talentFlowRow.getCell(7).setCellValue(totalReturnees);73 talentFlowRow.getCell(8).setCellValue(totalRetirement);74 talentFlowRow.getCell(9).setCellValue(totalResignation);75 talentFlowRow.getCell(10).setCellValue(totalDismiss);76 talentFlowRow.getCell(11).setCellValue(totalOther);77 talentFlowRow.getCell(12).setCellValue(totalAverageIncrease);78 talentFlowRow.getCell(13).setCellValue(totalAverageReduction);79 }

1 /**

2 * 第四个sheet页：人才需求情况调查表3 *4 *@paramworkbook5 *@paramtalentFlows6 */

7 private void addalentDemands(HSSFWorkbook workbook, ListtalentDemands)8 throwsIllegalArgumentException, IllegalAccessException {9 Sheet talentDemandSheet = workbook.getSheetAt(3);10 Row talentDemandRow = talentDemandSheet.getRow(4);11 //如果数据大于模板中的行数，插入行并复制第一行数据的格式

12 if (talentDemands.size() > 5) {13 //插入行，5是模板中已有的行数

14 talentDemandSheet.shiftRows(5, talentDemandSheet.getLastRowNum(), talentDemands.size() - 5, true, false);15 Row sourceRow = talentDemandSheet.getRow(4);16 for (int i = 0; i < talentDemands.size() - 5; i++) {17 Row newRow = talentDemandSheet.createRow(4 + i + 1);18 newRow.setHeight(sourceRow.getHeight());19 for (int j = 0; j < sourceRow.getLastCellNum(); j++) {20 Cell templateCell =sourceRow.getCell(j);21 if (templateCell != null) {22 Cell newCell =newRow.createCell(j);23 copyCell(templateCell, newCell);24 }25 }26 }27 }28 //填充数据

29 for (int i = 0; i < talentDemands.size(); i++) {30 talentDemandRow = talentDemandSheet.getRow(4 +i);31 talentDemandRow.getCell(0).setCellValue(talentDemands.get(i).getPositionTitle());32 talentDemandRow.getCell(2).setCellValue(talentDemands.get(i).getDemand());33 talentDemandRow.getCell(3).setCellValue(talentDemands.get(i).getAge());34 talentDemandRow.getCell(4).setCellValue(talentDemands.get(i).getFkAcademicDegreeName());35 talentDemandRow.getCell(5).setCellValue(talentDemands.get(i).getTechnicalTitles());36 talentDemandRow.getCell(6).setCellValue(talentDemands.get(i).getProfession());37 talentDemandRow.getCell(7).setCellValue(talentDemands.get(i).getFkTalentCategoryName());38 talentDemandRow.getCell(8).setCellValue(talentDemands.get(i).getFkServiceFormName());39 talentDemandRow.getCell(9).setCellValue(talentDemands.get(i).getProvide());40 talentDemandRow.getCell(10).setCellValue(talentDemands.get(i).getOtherCases());41 talentDemandRow.getCell(11).setCellValue(talentDemands.get(i).getFkIntentionToChooseName());42 }43 }

五、缺点：一手烂代码，应该给对象设置对应的中文注释，和模板中的列头一样，然后再通过循环填充数据。这样一个个填上去太傻了。

六、通过实验测试，在getCell的时候，如果那是个合并的单元格，那么该单元格的数据存在了左上角，其他的格内容为空。