1 模板打印
1.1 概述
自定义生成Excel报表文件还是有很多不尽如意的地方，特别是针对复杂报表头，单元格样式，字体等操作。手写这些代码不仅费时费力，有时候效果还不太理想。那怎么样才能更方便的对报表样式，报表头进行处理呢？答案是使用已经准备好的Excel模板，只需要关注模板中的数据即可。
1.2 模板打印的操作步骤
- 制作模版文件（模版文件的路径）
- 导入（加载）模版文件，从而得到一个工作簿
- 读取工作表
- 读取行
- 读取单元格
- 读取单元格样式
- 设置单元格内容
- 其他单元格就可以使用读到的样式了
1.3 代码实现
代码语言：javascript复制 @RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
//1.构造数据
List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month "%");
//2.加载模板流数据
Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
FileInputStream fis = new FileInputStream(resource.getFile());
//3.根据文件流，加载指定的工作簿
XSSFWorkbook wb = new XSSFWorkbook(fis);
//4.读取工作表
Sheet sheet = wb.getSheetAt(0);
//5.抽取公共的样式
Row styleRow = sheet.getRow(2);
CellStyle [] styles = new CellStyle[styleRow.getLastCellNum()];
for(int i=0;i<styleRow.getLastCellNum();i ) {
styles[i] = styleRow.getCell(i).getCellStyle();
}
//6.构造每行和单元格数据
AtomicInteger datasAi = new AtomicInteger(2);
Cell cell = null;
for (EmployeeReportResult report : list) {
Row dataRow = sheet.createRow(datasAi.getAndIncrement());
//编号
cell = dataRow.createCell(0);
cell.setCellValue(report.getUserId());
cell.setCellStyle(styles[0]);
//姓名
cell = dataRow.createCell(1);
cell.setCellValue(report.getUsername());
cell.setCellStyle(styles[1]);
//手机
cell = dataRow.createCell(2);
cell.setCellValue(report.getMobile());
cell.setCellStyle(styles[2]);
//最高学历
cell = dataRow.createCell(3);
cell.setCellValue(report.getTheHighestDegreeOfEducation());
cell.setCellStyle(styles[3]);
//国家地区
cell = dataRow.createCell(4);
cell.setCellValue(report.getNationalArea());
cell.setCellStyle(styles[4]);
//护照号
cell = dataRow.createCell(5);
cell.setCellValue(report.getPassportNo());
cell.setCellStyle(styles[5]);
//籍贯
cell = dataRow.createCell(6);
cell.setCellValue(report.getNativePlace());
cell.setCellStyle(styles[6]);
//生日
cell = dataRow.createCell(7);
cell.setCellValue(report.getBirthday());
cell.setCellStyle(styles[7]);
//属相
cell = dataRow.createCell(8);
cell.setCellValue(report.getZodiac());
cell.setCellStyle(styles[8]);
//入职时间
cell = dataRow.createCell(9);
cell.setCellValue(report.getTimeOfEntry());
cell.setCellStyle(styles[9]);
//离职类型
cell = dataRow.createCell(10);
cell.setCellValue(report.getTypeOfTurnover());
cell.setCellStyle(styles[10]);
//离职原因
cell = dataRow.createCell(11);
cell.setCellValue(report.getReasonsForLeaving());
cell.setCellStyle(styles[11]);
//离职时间
cell = dataRow.createCell(12);
cell.setCellStyle(styles[12]);
cell.setCellValue(report.getResignationTime());
}
String fileName = URLEncoder.encode(month "人员信息.xlsx", "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" new String(fileName.getBytes("ISO8859-1")));
response.setHeader("filename", fileName);
wb.write(response.getOutputStream());
}
