最近公司有需求，需要實現(xiàn)數(shù)據(jù)打印功能船响。需要將數(shù)據(jù)填充到對應的模板文件數(shù)據(jù)膜宋，并將excel轉(zhuǎn)成pdf流傳給前端。其中呢數(shù)據(jù)的渲染使用了 easyexcel 做个，轉(zhuǎn)pdf使用到了Free Spire.Xls for Java 產(chǎn)品（免費java 組件）
基于excel模板填充數(shù)據(jù)
poi-tl 是一個Word 模板引擎鸽心，基于 Microsoft Word 模板和數(shù)據(jù)生成新的文檔腔呜，并且支持用戶自定義函數(shù)，函數(shù)可以在 Word 模板的任何位置執(zhí)行再悼。
導入依賴：
<!-- 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>
具體的使用可以去查相關的文檔核畴，比較簡單，只需定義要你的模板冲九，查詢對應的數(shù)據(jù)出來
2谤草、Excel轉(zhuǎn)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) {
//創(chuàng)建Workbook 實例并加載示例文檔
Workbook workbook = new Workbook();
workbook.loadFromHtml(filePath);
//轉(zhuǎn)換時設置工作表適應寬度
workbook.getConverterSetting().setSheetFitToWidth(true);
//轉(zhuǎn)換時設置工作表適應頁面
workbook.getConverterSetting().setSheetFitToPage(true);
//保存為PDF文檔格式
document.saveToFile("D://最終委托書.pdf", FileFormat.PDF);
}
以下是對應開發(fā)的接口代碼如下:
@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");
//獲取需要導出的數(shù)據(jù)
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();
//因為列表要循環(huán)多個 但是如果你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);
//處理項目內(nèi)容
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;
}
//處理商品內(nèi)容
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();
}
//創(chuàng)建Workbook 實例并加載示例文檔
Workbook workbook = new Workbook();
workbook.loadFromHtml(filePath);
//轉(zhuǎn)換時設置工作表適應寬度
workbook.getConverterSetting().setSheetFitToWidth(true);
//轉(zhuǎn)換時設置工作表適應頁面
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
