写在前面的话
接下来我会使用传统的RESTful风格的方式结合MVC的开发模式给大家介绍一下如何去实现标题的效果。
基本思路讲解
- 先从数据库中查询出一组人员信息记录,保存在List list中。
- 遍历这个列表,对于每一个人员信息,将其填充到一个Excel模板文件中,生成一个新的Excel文件。这个模板文件在项目的"classpath:excel/demo.xlsx"路径下。填充过程是通过getSheets方法完成的。
- 通过传入的InputStream对象(对应Excel模板文件的输入流)创建一个XSSFWorkbook对象,代表整个Excel工作簿。遍历工作簿中的所有工作表(Sheet),对于每一个工作表,遍历其中的所有行(Row),再对每一行中的所有单元格(Cell),进行模板替换。模板替换的过程是:检查当前单元格的类型,如果是字符串类型,检查其中是否包含某个模板标记,如果包含,则替换为对应的人员信息。例如,如果单元格中的文本是"${psnName}",则将其替换为当前人员的名字(t.getSponsorName())。以上过程完成后,返回填充完成的工作簿对象。然后将这个对象写入到一个ByteArrayOutputStream对象中,一起存入列表excelFiles中。
- 将所有生成的Excel文件打包成一个zip文件,然后通过HttpServletResponse对象将zip文件写回给客户端
Controller层的代码
/*** <p>* 前端控制器* </p>** @author 舒一笑* @since 2023-09-24*/
@RestController
@RequestMapping("/demo02/personnelInfo")
public class PersonnelInfoController {@Autowiredprivate IPersonnelInfoService service;@PostMapping("/downloadZip")@ApiOperation(value = "个人模板压缩包导出")public void downloadZip(HttpServletResponse response){try {service.downloadZip(response);} catch (Exception e) {e.printStackTrace();}}
}
Service层代码
接口层
/*** <p>* 服务类* </p>** @author 舒一笑* @since 2023-09-24*/
public interface IPersonnelInfoService extends IService<PersonnelInfo> {void downloadZip(HttpServletResponse response);
}
实现类层
/*** <p>* 服务实现类* </p>** @author 舒一笑* @since 2023-09-24*/
@Service
public class PersonnelInfoServiceImpl extends ServiceImpl<PersonnelInfoMapper, PersonnelInfo> implements IPersonnelInfoService {@Overridepublic void downloadZip(HttpServletResponse response) {// 数据查询List<PersonnelInfo> list = this.list();// 文件生成List<Map.Entry<String, ByteArrayOutputStream>> excelFiles = new ArrayList<>();list.forEach(t ->{// 读取excel文件try {ClassPathResource classPathResource = new ClassPathResource("excel/demo.xlsx");InputStream inputStream = classPathResource.getInputStream();Workbook workbook = getSheets(t, inputStream);ByteArrayOutputStream excelByteArrayOutputStream = new ByteArrayOutputStream();workbook.write(excelByteArrayOutputStream);// 将每个人的名字作为文件名保存
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日");String format = formatter.format(t.getCreateTime());String fileName = t.getSponsorName() + "-"+format;excelFiles.add(new AbstractMap.SimpleEntry<>(fileName, excelByteArrayOutputStream));inputStream.close();excelByteArrayOutputStream.close();} catch (FileNotFoundException e) {throw new RuntimeException(e);} catch (IOException e) {throw new RuntimeException(e);}});// 压缩导出try {response.setContentType("application/zip");String fileName = URLEncoder.encode("压缩包导出","UTF-8");response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".zip");ZipOutputStream zos = new ZipOutputStream(response.getOutputStream());for (int i = 0; i < excelFiles.size(); i++) {Map.Entry<String, ByteArrayOutputStream> excelFileEntry = excelFiles.get(i);ZipEntry entry = new ZipEntry(excelFileEntry.getKey() + ".xlsx");zos.putNextEntry(entry);zos.write(excelFileEntry.getValue().toByteArray());zos.closeEntry();excelFileEntry.getValue().close();}zos.finish();zos.close();} catch (IOException e) {throw new RuntimeException(e);}}/*** 模板替换* @param t* @param inputStream* @return* @throws IOException*/private static Workbook getSheets(PersonnelInfo t, InputStream inputStream) throws IOException {Workbook workbook = new XSSFWorkbook(inputStream);// 遍历所有的sheetIterator<Sheet> sheetIterator = workbook.sheetIterator();while (sheetIterator.hasNext()) {Sheet sheet = sheetIterator.next();// 遍历sheet中所有的行Iterator<Row> rowIterator = sheet.rowIterator();while (rowIterator.hasNext()) {Row row = rowIterator.next();// 遍历行中所有的单元格Iterator<Cell> cellIterator = row.cellIterator();while (cellIterator.hasNext()) {Cell cell = cellIterator.next();// 如果单元格类型是字符串,则查找并替换if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${psnName}")) {String newCellValue = cell.getStringCellValue().replace("${psnName}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${psnNum}")) {String newCellValue = cell.getStringCellValue().replace("${psnNum}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${personTypeName}")) {String newCellValue = cell.getStringCellValue().replace("${personTypeName}", t.getSponsorName() != null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${posName}")) {String newCellValue = cell.getStringCellValue().replace("${posName}", t.getSponsorName() != null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${suitUnitName}")) {String newCellValue = cell.getStringCellValue().replace("${suitUnitName}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${orgName}")) {String newCellValue = cell.getStringCellValue().replace("${orgName}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${periodName}")) {String newCellValue = cell.getStringCellValue().replace("${periodName}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${interviewTime}")) {// 定义日期时间格式DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日");
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");String newCellValue = cell.getStringCellValue().replace("${interviewTime}", formatter.format(t.getCreateTime())!= null ? formatter.format(t.getCreateTime()) : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${performanceReview}")) {String newCellValue = cell.getStringCellValue().replace("${performanceReview}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);} else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${improvePropose}")) {String newCellValue = cell.getStringCellValue().replace("${improvePropose}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);}else if (cell.getCellType() == CellType.STRING && cell.getStringCellValue().contains("${improvePlan}")) {String newCellValue = cell.getStringCellValue().replace("${improvePlan}", t.getSponsorName()!= null ? t.getSponsorName() : "");cell.setCellValue(newCellValue);}}}}return workbook;}}
模板展示
部分业务信息我直接模糊掉了,希望小伙伴理解。