模板打印
概述
自定义生成Excel报表文件还是有很多不尽如意的地方,特别是针对复杂报表头,单元格样式,字体等操作。手写这些代码不仅费时费力,有时候效果还不太理想。那怎么样才能更方便的对报表样式,报表头进行处理呢?答案是使用已经准备好的Excel模板,只需要关注模板中的数据即可。
模板打印的操作步骤
1. 制作模版文件(模版文件的路径)
2. 导入(加载)模版文件,从而得到一个工作簿
3. 读取工作表
4. 读取行
5. 读取单元格
6. 读取单元格样式
7. 设置单元格内容
8. 其他单元格就可以使用读到的样式了
/**
* 采用模板打印的形式完成报表生成
* 模板
* 参数:
* 年月-月(2018-02%)
*
* sxssf对象不支持模板打印
*/
@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable 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.通过工具类完成下载
// new ExcelExportUtil(EmployeeReportResult.class,2,2).
// export(response,fis,list,month+"人事报表.xlsx");
//3.根据模板创建工作簿
Workbook wb = new XSSFWorkbook(fis);
//4.读取工作表
Sheet sheet = wb.getSheetAt(0);
//5.抽取公共样式
Row row = sheet.getRow(2);
CellStyle styles [] = new CellStyle[row.getLastCellNum()];
for(int i=0;i<row.getLastCellNum();i++) {
Cell cell = row.getCell(i);
styles[i] = cell.getCellStyle();
}
//6.构造单元格
int rowIndex = 2;
Cell cell=null;
for(int i=0;i<10000;i++) {
for (EmployeeReportResult employeeReportResult : list) {
row = sheet.createRow(rowIndex++);
// 编号,
cell = row.createCell(0);
cell.setCellValue(employeeReportResult.getUserId());
cell.setCellStyle(styles[0]);
// 姓名,
cell = row.createCell(1);
cell.setCellValue(employeeReportResult.getUsername());
cell.setCellStyle(styles[1]);
// 手机,
cell = row.createCell(2);
cell.setCellValue(employeeReportResult.getMobile());
cell.setCellStyle(styles[2]);
// 最高学历,
cell = row.createCell(3);
cell.setCellValue(employeeReportResult.getTheHighestDegreeOfEducation());
cell.setCellStyle(styles[3]);
// 国家地区,
cell = row.createCell(4);
cell.setCellValue(employeeReportResult.getNationalArea());
cell.setCellStyle(styles[4]);
// 护照号,
cell = row.createCell(5);
cell.setCellValue(employeeReportResult.getPassportNo());
cell.setCellStyle(styles[5]);
// 籍贯,
cell = row.createCell(6);
cell.setCellValue(employeeReportResult.getNativePlace());
cell.setCellStyle(styles[6]);
// 生日,
cell = row.createCell(7);
cell.setCellValue(employeeReportResult.getBirthday());
cell.setCellStyle(styles[7]);
// 属相,
cell = row.createCell(8);
cell.setCellValue(employeeReportResult.getZodiac());
cell.setCellStyle(styles[8]);
// 入职时间,
cell = row.createCell(9);
cell.setCellValue(employeeReportResult.getTimeOfEntry());
cell.setCellStyle(styles[9]);
// 离职类型,
cell = row.createCell(10);
cell.setCellValue(employeeReportResult.getTypeOfTurnover());
cell.setCellStyle(styles[10]);
// 离职原因,
cell = row.createCell(11);
cell.setCellValue(employeeReportResult.getReasonsForLeaving());
cell.setCellStyle(styles[11]);
// 离职时间
cell = row.createCell(12);
cell.setCellValue(employeeReportResult.getResignationTime());
cell.setCellStyle(styles[12]);
}
}
//7.下载
//3.完成下载
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
new DownloadUtils().download(os,response,month+"人事报表.xlsx");
}