Java实现根据Word模板填充表格数据(poi方式),以及doc和docx转PDF,最全最详细版本,解决外部引用jar在linux上报ClassNotFound的问题。
适用场景:
1.固定格式的Word模板
2.Word模板中所有需要填充的数据都使用【Word表格】包起来
3.包含简单和复杂数据填充,场景表现为一对多关系的主表和明细列表数据等
4.此方式在Controller层返回为文件流,如果你使用ftp文件服务器,请修改你的代码。
5.此文是我搜集了网上的各种公开的实现方式修改并整合起来,并未用于商业化也不会用于商业化,不涉及侵权或抄袭问题。
现在由pom.xml开始
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.aspose</groupId>
<artifactId>aspose-words</artifactId>
<version>15.8.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
注意:第三个dependency在maven中不存在,百度网盘下载jar包:
链接:https://pan.baidu.com/s/13TQTxonrBlle94v3ge6UNg
提取码:xhv7
注意:这里使用15.8.0或15.12.0任意一个都可以
下载好后将jar包打在maven本地仓库中,有本地maven的使用cmd执行mvn命令,没有本地maven的在idea的maven框中点开“M”标记的按钮,输入命令:
mvn install:install-file -Dgroupld=com.aspose -Dartifactld=aspose-words -Dversion=15.8.0 -Dpackaging=jar -Dfile=C:\Users\Administrator.m2\repository\com\aspose\aspose-words\15.8.0\aspose-words-15.8.0-jdk16.jar
使用15.12.0的话则将jar包和命令都更换个版本,-Dfile是你项目中的maven使用的本地仓库默认路径,不一致则更换为你使用的本地仓库路径。
这里不使用将jar放入本地项目的方式是因为我之前使用的就是那种方式,在linux环境下一直找不到外部引用jar包,报ClassNotFound的错,即使在依赖包中存在该依赖,且打成的jar包也存在该依赖。
配置好后,接下来是使用的第一个Util
package com.ruoyi.common.utils;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge;
import java.io.File;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;
public class WordUtil {
/**
* 生成word
*
* @param templatePath
* @param temDir
* @param fileName
* @param params
*/
public static void exportWord(String templatePath, String temDir, String fileName, Map<String, Object> params) {
Assert.notNull(templatePath, "模板路径不能为空");
Assert.notNull(temDir, "临时文件路径不能为空");
Assert.notNull(fileName, "导出文件名不能为空");
Assert.isTrue(fileName.endsWith(".docx"), "word导出请使用docx格式");
if (!temDir.endsWith("/")) {
temDir = temDir + File.separator;
}
File dir = new File(temDir);
if (!dir.exists()) {
dir.mkdirs();
}
try {
XWPFDocument doc = WordExportUtil.exportWord07(templatePath, params);
String tmpPath = temDir + fileName;
FileOutputStream fos = new FileOutputStream(tmpPath);
doc.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param index(表格索引:第几个表格),dataMap(数据源)
* @Method doTable
* @Description 替换表格内容
* @Return void
* @Exception
*/
public static XWPFDocument doTable(int index, Map<String, Object> map, List<Map<String, Object>> dataMap, String templatePath) throws Exception {
//读取模板文件
XWPFDocument xwpfDocument = WordExportUtil.exportWord07(templatePath, map);
//使用xwpfDocument对象操作word文档
XWPFTable table = xwpfDocument.getTables().get(index);
if (dataMap != null && dataMap.size() > 0) {
List<XWPFTableRow> rows = table.getRows();
int rowIndex = 0;//寻找字段绑定行索引
String[] fields = null;字段绑定行字段顺序(a,b,c)
for (XWPFTableRow row : rows) {
List<XWPFTableCell> cells = row.getTableCells();
for (XWPFTableCell cell : cells) {
String key = cell.getText()
.replaceAll("\\{", "")
.replaceAll("}", "");
if (dataMap.get(0).get(key) != null) {//找到匹配
fields = new String[cells.size()];
break;
}
}
if (fields != null && fields.length > 0) {//找到,并获取字段
for (int i = 0; i < fields.length; i++) {
fields[i] = cells.get(i)
.getText()
.replaceAll("\\{", "")
.replaceAll("}", "");
}
break;
} else {
rowIndex++;
}
}
if (rowIndex >= 0 && fields != null) {
//从字段绑定行开始插入
for (Map<String, Object> rowdata : dataMap) {
XWPFTableRow row = null;
try {
row = rows.get(rowIndex);
} catch (Exception e) {
row = table.createRow();
}
if (row != null) {
List<XWPFTableCell> cells = row.getTableCells();
int cellIndex = 0;
for (XWPFTableCell cell : cells) {
if (cellIndex != 3) {
cell.removeParagraph(0);
XWPFParagraph newPara = cell.addParagraph();
XWPFRun run = newPara.createRun();
Object value = rowdata.get(fields[cellIndex]);
if (value != null) {
run.setText(value.toString());
run.setFontFamily("微软雅黑");
run.setFontSize(9);
}
}
cellIndex++;
}
/* if (rowIndex == 6) {
cells.get(3).setText("联系方式:☑");
}
if (rowIndex == 9) {
cells.get(3).setText("控单类型:");
}
if (rowIndex == 13) {
cells.get(3).setText("备注(请务必在此注明该柜的基本情况):");
}*/
}
rowIndex++;
}
}
}
return xwpfDocument;
}
/**
* word跨列合并单元格
*
* @param table
* @param row
* @param fromCell
* @param toCell
*/
public void mergeCellsHorizontal(XWPFTable table, int row, int fromCell, int toCell) {
for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) {
XWPFTableCell cell = table.getRow(row).getCell(cellIndex);
if (cellIndex == fromCell) {
// The first merged cell is set with RESTART merge value
cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
} else {
// Cells which join (merge) the first one, are set with CONTINUE
cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
}
}
}
/**
* word跨行并单元格
*/
public void mergeCellsVertically(XWPFTable table, int col, int fromRow, int toRow) {
for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++) {
XWPFTableCell cell = table.getRow(rowIndex).getCell(col);
if (rowIndex == fromRow) {
// The first merged cell is set with RESTART merge value
cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);
} else {
// Cells which join (merge) the first one, are set with CONTINUE
cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
}
}
}
}
第二个Util
import com.aspose.words.Document;
import com.aspose.words.License;
import com.aspose.words.SaveFormat;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.yaml.snakeyaml.Yaml;
import java.io.*;
import java.util.Map;
public class Word2PDFUtil {
private static boolean license = false;
private static String temDir;
private static final Logger logger = LoggerFactory.getLogger(Word2PDFUtil.class);
//初始化
static {
Yaml yaml = new Yaml();
//文件路径是相对类目录(src/main/java)的相对路径
InputStream in = Word2PDFUtil.class.getClassLoader().getResourceAsStream("application.yml");
Map<String, Object> map = yaml.loadAs(in, Map.class);
temDir = ((Map<String, Object>) map.get("file")).get("temDir").toString();
try {
// license.xml放在src/main/resources文件夹下
InputStream is = Word2PDFUtil.class.getClassLoader().getResourceAsStream("license.xml");
License aposeLic = new License();
aposeLic.setLicense(is);
license = true;
} catch (Exception e) {
license = false;
logger.error("License验证失败...");
e.printStackTrace();
}
}
public static InputStream wordToPdf(String fileName, XWPFDocument xwpfDocument) throws Exception {
FileOutputStream os = null;
InputStream pdfIs = null;
File file = null;
try {
if (!temDir.endsWith("/")) {
temDir = temDir + File.separator;
}
File dir = new File(temDir);
if (!dir.exists()) {
dir.mkdirs();
}
String tmpPath = temDir + fileName;
//生成根据模板插入数据后的docx文件
FileOutputStream fos = new FileOutputStream(tmpPath + ".docx");
xwpfDocument.write(fos);
fos.flush();
fos.close();
//生成一个空的PDF文件
file = new File(tmpPath + ".pdf");
os = new FileOutputStream(file);
//要转换的word文件
Document doc = new Document(tmpPath + ".docx");
doc.save(os, SaveFormat.PDF);
//要返回的pdf文件流
pdfIs = new FileInputStream(tmpPath + ".pdf");
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//删除临时文件
if (file.exists()) {
file.delete();
}
}
return pdfIs;
}
}
以上,如果包找不到,请去掉部分你没有用到的包。
Service
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.word.WordExportUtil;
import com.ruoyi.common.utils.*;
import com.ruoyi.common.utils.base.ErrorMessageException;
import com.ruoyi.common.utils.poi.PoiExcelUtils;
import com.ruoyi.ladingBill.dto.GlLadingBillOutDTO;
import com.ruoyi.marineSpecial.dto.GlMarineSpecialOutDTO;
import com.ruoyi.marineSpecial.service.GlMarineSpecialService;
import com.ruoyi.pay.dao.GlBusinessPayDao;
import com.ruoyi.pay.dto.GLBusinessPayForExport;
import com.ruoyi.pushMoney.dao.GlPushMoneyDao;
import com.ruoyi.receipt.dao.GlBusinessReceiptDao;
import com.ruoyi.receipt.dto.GLBusinessReceiptForExport;
import com.ruoyi.storage.dto.GlInventoryDetailOutDTO;
import com.ruoyi.storage.dto.GlInventoryOutDTO;
import com.ruoyi.storage.dto.GlOutStorageDTO;
import com.ruoyi.storage.entity.GlInventoryDetail;
import com.ruoyi.storage.entity.GlOutStorageDetail;
import com.ruoyi.storage.service.GlInventoryService;
import com.ruoyi.towing.dto.GlTowingOutDTO;
import com.ruoyi.utils.Word2PDFUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;
@Service
public class WordService {
@Resource
private GlMarineSpecialService glMarineSpecialService;
@Resource
private GlBusinessReceiptDao glBusinessReceiptDao;
@Resource
private GlBusinessPayDao glBusinessPayDao;
@Resource
private GlPushMoneyDao glPushMoneyDao;
@Resource
private GlInventoryService glInventoryService;
//标题,一共16个
String[] cloName = {"品名", "件数", "装箱数", "总数量", "单位", "价格", "金额", "总金额", "长", "宽", "高", "体积", "总体积", "材质", "条形码", "店面"};
/**
* 费用确认跟踪单导出
*
* @param id
* @return
* @throws Exception
*/
public XWPFDocument costConfirmation(Long id) throws Exception {
Map<String, Object> map = new HashMap<>();
GlMarineSpecialOutDTO dto = glMarineSpecialService.detail(id);
if (dto == null) {
throw new ErrorMessageException("找不到该id对应的海运单");
}
GlLadingBillOutDTO glLadingBillOutDTO = dto.getGlLadingBillOutDTO();
if (glLadingBillOutDTO == null) {
throw new ErrorMessageException("找不到该id对应的托单明细");
}
GlTowingOutDTO glTowingOutDTO = dto.getGlTowingOutDTO();
map.put("now", DateUtils.getDate());
map.put("no", dto.getNo());
map.put("businessNature", dto.getBusinessNatureName());
map.put("salerName", dto.getSalerName());
map.put("originPort", glLadingBillOutDTO.getOriginPort());
map.put("expectSailingStartDate", dto.getExpectSailingStartDate());
map.put("mblNo", dto.getMblNo());
map.put("destination", glLadingBillOutDTO.getDestination());
map.put("bookAgent", dto.getBookAgent() == null ? " " : dto.getBookAgent());
map.put("ship", dto.getShipName() + (StringUtils.isEmpty(dto.getVoyageNumber()) ? " " : " V." + dto.getVoyageNumber()));
map.put("caseNum", glLadingBillOutDTO.getCaseNum());
map.put("sealNum", glLadingBillOutDTO.getSealNum());
map.put("boxType", glLadingBillOutDTO.getBoxTypeName());
map.put("contactTel", dto.getContactTel());
map.put("operationRemark", glLadingBillOutDTO.getOperationRemark() == null ? " " : glLadingBillOutDTO.getOperationRemark());
map.put("operator", dto.getOperatorName());
map.put("clerk", dto.getDocumentClerkName());
map.put("business", dto.getBusinessPersonName());
if (glTowingOutDTO != null) {
map.put("shipmentDate", glTowingOutDTO.getShipmentDate());
map.put("destinationAddress", glTowingOutDTO.getDestinationAddress());
map.put("towingCompany", glTowingOutDTO.getCompany());
} else {
map.put("shipmentDate", " ");
map.put("destinationAddress", " ");
map.put("towingCompany", " ");
}
List<Map<String, Object>> mapList = mapList(map, id, dto.getCustomerName());
XWPFDocument xwpfDocument = WordUtil.doTable(1, map, mapList, "templates/费用确认跟踪单.docx");
return xwpfDocument;
}
/**
* 费用确认跟踪单,收款、付款、提成明细数据填充
*
* @param map
* @param id
* @param customerName
* @return
*/
private List<Map<String, Object>> mapList(Map<String, Object> map, Long id, String customerName) {
map.put("customerName", customerName);
map.put("providerAccountName", customerName);
double receiptRMBSum = 0.0;
double receiptUSDSum = 0.0;
double receiptEURSum = 0.0;
double payRMBSum = 0.0;
double payUSDSum = 0.0;
double payEURSum = 0.0;
List<GLBusinessReceiptForExport> receiptDTOList = glBusinessReceiptDao.exportReceipt(id);//收款
List<GLBusinessPayForExport> payDTOList = glBusinessPayDao.exportPay(id);//付款
List<GLBusinessPayForExport> pushMoneyList = glPushMoneyDao.exportPushMoney(id);//提成
int maxSize = 0;
if (receiptDTOList.size() > payDTOList.size()) {
maxSize = receiptDTOList.size();
} else {
maxSize = payDTOList.size();
}
maxSize += pushMoneyList.size();
List<Map<String, Object>> mapList = new ArrayList<>();
for (int i = 0; i < maxSize; i++) {
Map<String, Object> map1 = new HashMap<>();
if (i < receiptDTOList.size()) {//收款
GLBusinessReceiptForExport receipt = receiptDTOList.get(i);
map1.put("reName", receipt.getReceiptName());
map1.put("reMoney", receipt.getReceiptMoney());
map1.put("re", customerName.length() <= 8 ? customerName : customerName.substring(0, 8));
if (receipt.getCurrency().equals("269")) {
receiptRMBSum += receipt.getReceiptMoney();
} else if (receipt.getCurrency().equals("270")) {
receiptUSDSum += receipt.getReceiptMoney();
} else if (receipt.getCurrency().equals("271")) {
receiptEURSum += receipt.getReceiptMoney();
}
}
if (i < payDTOList.size()) {//付款
GLBusinessPayForExport pay = payDTOList.get(i);
map1.put("payName", pay.getPayName());
map1.put("payMoney", pay.getPayMoney());
map1.put("pay", pay.getPay());
if (pay.getCurrency().equals("269")) {
payRMBSum += pay.getPayMoney();
} else if (pay.getCurrency().equals("270")) {
payUSDSum += pay.getPayMoney();
} else if (pay.getCurrency().equals("271")) {
payEURSum += pay.getPayMoney();
}
} else {
//提成
if (i - payDTOList.size() < pushMoneyList.size()) {
GLBusinessPayForExport pushMoney = pushMoneyList.get(i - payDTOList.size());
map1.put("payName", pushMoney.getPayName());
map1.put("payMoney", pushMoney.getPayMoney());
map1.put("pay", pushMoney.getPay());
payRMBSum += pushMoney.getPayMoney();
}
}
mapList.add(map1);
}
map.put("receiptRMBSum", receiptRMBSum);
map.put("receiptUSDSum", receiptUSDSum);
map.put("receiptEURSum", receiptEURSum);
map.put("payRMBSum", payRMBSum);
map.put("payUSDSum", payUSDSum);
map.put("payEURSum", payEURSum);
double RMDResult = receiptRMBSum - payRMBSum;
double USDResult = receiptUSDSum - payUSDSum;
double EURResult = receiptEURSum - payEURSum;
double endResult = RMDResult + USDResult * 6.351 + EURResult * 7.0121;
map.put("RMDResult", RMDResult);
map.put("USDResult", USDResult);
map.put("EURResult", EURResult);
DecimalFormat df = new DecimalFormat(".##");
String endResultFormat = df.format(endResult);
map.put("EndResult", endResultFormat);
return mapList;
}
public InputStream costConfirmationPDF(Long id, String fileName) throws Exception {
XWPFDocument xwpfDocument = costConfirmation(id);
InputStream is = Word2PDFUtil.wordToPdf(fileName + UUID.randomUUID(), xwpfDocument);
return is;
}
}
以上,请删除无用代码、导包和逻辑。
Controller
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.Result;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.utils.Word2PDFUtil;
import com.ruoyi.file.service.WordService;
import com.ruoyi.storage.dto.GlOutStorageDTO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.yaml.snakeyaml.Yaml;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.Date;
import java.util.Map;
/**
* word、pdf、excel Controller
*
* @author admin
* @date 2022-03-22
*/
@Controller
@RequestMapping("/file/file")
@Api("word、pdf、excel文件操作")
public class OfficeController extends BaseController {
@Autowired
private WordService wordService;
private static String temDir;
//初始化
static {
Yaml yaml = new Yaml();
//文件路径是相对类目录(src/main/java)的相对路径
InputStream in = Word2PDFUtil.class.getClassLoader().getResourceAsStream("application.yml");//或者app.yaml
Map<String, Object> map = yaml.loadAs(in, Map.class);
temDir = ((Map<String, Object>) map.get("file")).get("temDir").toString();
}
@ApiOperation(value = "费用确认跟踪单导出")
@PreAuthorize("@ss.hasPermi('file:file:costConfirmation')")
@GetMapping("/costConfirmation")
public Result costConfirmation(HttpServletResponse response, @RequestParam("id") String id, @RequestParam("customerName") String customerName) {
response.setCharacterEncoding("UTF-8");
try {
XWPFDocument xwpfDocument = wordService.costConfirmation(Long.valueOf(id));
String fileNameEncode = URLEncoder.encode("费用确认跟踪单-" + customerName, "UTF-8");
String fileName = URLDecoder.decode(fileNameEncode, "UTF-8");
response.setContentType("application/msword");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".docx");
xwpfDocument.write(response.getOutputStream());
return Result.success();
} catch (Exception e) {
return Result.error(e.getMessage());
}
}
@ApiOperation(value = "费用确认跟踪单导出PDF")
@PreAuthorize("@ss.hasPermi('file:file:costConfirmationPDF')")
@GetMapping("/costConfirmationPDF")
public Result costConfirmationPDF(HttpServletResponse response, @RequestParam("id") String id, @RequestParam("customerName") String customerName) {
InputStream is = null;
response.setCharacterEncoding("UTF-8");
try {
String fileNameEncode = URLEncoder.encode("费用确认跟踪单-" + customerName, "UTF-8");
String fileName = URLDecoder.decode(fileNameEncode, "UTF-8");
is = wordService.costConfirmationPDF(Long.valueOf(id), "费用确认跟踪单-" + customerName);
response.setContentType("application/mspdf");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".pdf");
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
byte[] buffer = new byte[is.available()];
is.read(buffer);
toClient.write(buffer);
return Result.success();
} catch (Exception e) {
return Result.error(e.getMessage());
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
测试方式
以上,在PDF导出保存时,后缀丢了,重命名加上.pdf后缀就好,即时记录该文来不及解决,在Headers中可以看到文件名称。
调试时在D盘下查看生成的.docx和.pdf文件
生成的临时文件会在Word2PDFUtil.java中删除,如不需删除请找到//删除临时文件 并注释。
此时发布至linux,生成后的pdf会乱码。待解决,解决后更新至该文档。