最近公司有需求，需要实现数据打印功能。需要将数据填充到对应的模板文件数据，并将excel转成pdf流传给前端。其中呢数据的渲染使用了 easyexcel ，转pdf使用到了Free Spire.Xls for Java 产品（免费java 组件）
基于excel模板填充数据
poi-tl 是一个Word 模板引擎，基于 Microsoft Word 模板和数据生成新的文档，并且支持用户自定义函数，函数可以在 Word 模板的任何位置执行。
导入依赖：
<!-- easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel-core</artifactId>
<version>3.2.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
<!-- POI导入导出 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
具体的使用可以去查相关的文档，比较简单，只需定义要你的模板，查询对应的数据出来
2、Excel转pdf
使用Free Spire.xls for Java 工具
对应文档链接：https://www.e-iceblue.cn/Downloads/Free-Spire-XLS-JAVA.html
导入依赖：
可以通过maven仓库安装，也可以自行下载安装
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>
示例：
public static void main(String[] args) {
//创建Workbook 实例并加载示例文档
Workbook workbook = new Workbook();
workbook.loadFromHtml(filePath);
//转换时设置工作表适应宽度
workbook.getConverterSetting().setSheetFitToWidth(true);
//转换时设置工作表适应页面
workbook.getConverterSetting().setSheetFitToPage(true);
//保存为PDF文档格式
document.saveToFile("D://最终委托书.pdf", FileFormat.PDF);
}
以下是对应开发的接口代码如下:
@ApiOperation(value = "服务委托书模板")
@GetMapping(value = "/exportServiceBook/{id}")
public void exportMyArchiveList(HttpServletResponse response, @PathVariable("id") String id, Date createTime) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("服务委托书", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//获取需要导出的数据
MtOrderExportInfo exportInfo = mtOrderTimeService.selectExportInfo(id, createTime, SecurityUtils.getGroupId(), SecurityUtils.getUserId());
//获取模板
String FileName = "服务委托书.xlsx";
String path = "D://服务委托书.xlsx";
String filePath = "D://aaa_temp.xlsx";
//URL url = new URL(path);
//Resource resource = new UrlResource(url);
try (InputStream inputStream = new FileInputStream(path);
ServletOutputStream outputStream = response.getOutputStream()) {
//设置输出流和模板信息
ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(inputStream).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
excelWriter.fill(exportInfo, writeSheet);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
//定义两个list导出
List<MtOrderItemTimeExport> itemInfo = exportInfo.getItemInfoList();
List<MtOrderItemTimeExport> goodsItemInfo = exportInfo.getGoodsItemInfoList();
excelWriter.fill(new FillWrapper("itemInfo", itemInfo), fillConfig, writeSheet);
excelWriter.fill(new FillWrapper("goodsItemInfo", goodsItemInfo), fillConfig, writeSheet);
excelWriter.finish();
//因为列表要循环多个 但是如果你excel有定义边框样式 他不会自己设置 所以需要手动设置边框处理
//如果项目/商品 有多行的话 需要手动进行列合并处理
if ((CollectionUtils.isNotEmpty(itemInfo) && itemInfo.size() > 1) || (CollectionUtils.isNotEmpty(goodsItemInfo) && goodsItemInfo.size() > 1)) {
FileInputStream sheetInputStream = new FileInputStream(new File(filePath));
//判断要处理多少行
int itemInfoSize = CollectionUtils.isNotEmpty(itemInfo) ? itemInfo.size() : 0;
int goodsInfoSize = CollectionUtils.isNotEmpty(goodsItemInfo) ? goodsItemInfo.size() : 0;
XSSFWorkbook workbook = new XSSFWorkbook(sheetInputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
//处理项目内容
int itemStartRow = 19;
for (int i = 1; i < itemInfoSize; i++) {
// 合并列
// 自适应列宽
sheet.autoSizeColumn(0);
sheet.addMergedRegion(new CellRangeAddress(itemStartRow, itemStartRow, 1, 5));
sheet.addMergedRegion(new CellRangeAddress(itemStartRow, itemStartRow, 6, 11));
sheet.addMergedRegion(new CellRangeAddress(itemStartRow, itemStartRow, 12, 15));
sheet.addMergedRegion(new CellRangeAddress(itemStartRow, itemStartRow, 16, 17));
RegionUtil.setBorderRight(BorderStyle.THIN, new CellRangeAddress(itemStartRow, itemStartRow, 16, 17), sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(itemStartRow, itemStartRow, 1, 17), sheet);
itemStartRow += 1;
}
//处理商品内容
int goodsStartRow = itemStartRow + 4;
for (int i = 1; i < goodsInfoSize; i++) {
// 合并列
// 自适应列宽
sheet.autoSizeColumn(0);
sheet.addMergedRegion(new CellRangeAddress(goodsStartRow, goodsStartRow, 1, 3));
sheet.addMergedRegion(new CellRangeAddress(goodsStartRow, goodsStartRow, 4, 6));
sheet.addMergedRegion(new CellRangeAddress(goodsStartRow, goodsStartRow, 7, 9));
sheet.addMergedRegion(new CellRangeAddress(goodsStartRow, goodsStartRow, 10, 12));
sheet.addMergedRegion(new CellRangeAddress(goodsStartRow, goodsStartRow, 13, 15));
sheet.addMergedRegion(new CellRangeAddress(goodsStartRow, goodsStartRow, 16, 17));
RegionUtil.setBorderRight(BorderStyle.THIN, new CellRangeAddress(goodsStartRow, goodsStartRow, 16, 17), sheet);
RegionUtil.setBorderBottom(BorderStyle.THIN, new CellRangeAddress(goodsStartRow, goodsStartRow, 1, 17), sheet);
goodsStartRow += 1;
}
FileOutputStream newOutputStream = new FileOutputStream(filePath);
workbook.write(newOutputStream);
outputStream.flush();
}
//创建Workbook 实例并加载示例文档
Workbook workbook = new Workbook();
workbook.loadFromHtml(filePath);
//转换时设置工作表适应宽度
workbook.getConverterSetting().setSheetFitToWidth(true);
//转换时设置工作表适应页面
workbook.getConverterSetting().setSheetFitToPage(true);
//保存为PDF文档格式
workbook.saveToStream(outputStream, FileFormat.PDF);
//删除生成的excel临时文件
File file = new File(filePath);
if (file.exists()) {
file.delete();
}
} catch (Exception e) {
e.printStackTrace();
}
}
模板如图：
image.png
最终效果：
image.png
