最近項(xiàng)目一個(gè)需求要求將訂單按照excel模板導(dǎo)出，其中商品有多行，需要?jiǎng)討B(tài)插入行并且存在合并單元格的情況，使用easyExcel官網(wǎng)提供的demo的填充和合并單元格：
官網(wǎng)填充demo
官網(wǎng)合并單元格demo
按模板導(dǎo)出主要代碼：
public class DataToExcel {
public void exportFile() {
File filePath = new File("D:\\test\\testMerge.xlsx");
OutputStream os= Files.newOutputStream(filePath.toPath());
int firstRow = 18; //從第18行開始合并
int lastRow = 18;
int beginRow = 18;
//單元格合并
List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
if (CollectionUtil.isNotEmpty(excelVoList)) {
if (excelVoList.size() > 1) {
for (int i = 0; i < excelVoList.size() - 1; i++) {
cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 1, 4));
cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 7, 8));
firstRow++;
lastRow++;
}
}
}
FillMergeStrategy fillMergeStrategy = new FillMergeStrategy(cellRangeAddressList, beginRow, excelVoList.size() - 1);
//獲取excel模板
File file = new File("D:\\template\\template01.xlsx");
InputStream inputStream = Files.newInputStream(file.toPath());
//InputStream inputStream = new URL(filePath).openStream();
ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(inputStream)
.registerWriteHandler(fillMergeStrategy)
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
//參數(shù)集合，直接寫入到Excel數(shù)據(jù)
excelWriter.fill(paramsMap, writeSheet);
//列表數(shù)據(jù)
excelWriter.fill(excelVoList, fillConfig, writeSheet);
excelWriter.finish();
}
}
合并單元格的策略為：
public class PiFillMergeStrategy implements RowWriteHandler {
//合并坐標(biāo)集合
private List<CellRangeAddress> cellRangeAddress;
//從哪行開始
private int beginRow;
//合并行數(shù)
private int mergeRows;
public PiFillMergeStrategy(List<CellRangeAddress> cellRangeAddress, int beginRow, int mergeRows) {
this.cellRangeAddress = cellRangeAddress;
this.beginRow = beginRow;
this.mergeRows = mergeRows;
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
if (CollectionUtil.isNotEmpty(cellRangeAddress)) {
if (row.getRowNum() >= beginRow && row.getRowNum() <= beginRow + mergeRows) {
for (CellRangeAddress item : cellRangeAddress) {
writeSheetHolder.getSheet().addMergedRegionUnsafe(item);
}
}
}
}
}
當(dāng)有多行商品導(dǎo)出的excel文件打開時(shí)會(huì)提示：
點(diǎn)擊“是”是可以打開的，但用戶體驗(yàn)很不好，認(rèn)為導(dǎo)出文件有問題！
調(diào)試了下easyExcel代碼，發(fā)現(xiàn)合并單元格的方法主要有兩個(gè)：
/**
* 添加單元格的合并區(qū)域（因此這些單元格形成一個(gè)）
* 參數(shù)：region – (rowfrom/colfrom-rowto/colto) 合并
* 返回：該地區(qū)的指數(shù)
*/
int addMergedRegion(CellRangeAddress region);
/**
* 添加單元格的合并區(qū)域（因此這些單元格形成一個(gè)）。跳過驗(yàn)證。可以創(chuàng)建重疊的合并區(qū)域或創(chuàng)建與多單元格
* 數(shù)組公式與此公式相交的合并區(qū)域，這可能會(huì)導(dǎo)致工作簿損壞。要在調(diào)用 addMergedRegionUnsafe 后檢
* 查合并區(qū)域重疊數(shù)組公式或其他合并區(qū)域，請(qǐng)調(diào)用validateMergedRegions() ，它在 O(n^2) 時(shí)間內(nèi)
* 運(yùn)行。
* 參數(shù)：region ——合并
* 返回：該地區(qū)的指數(shù)
* 拋出：IllegalArgumentException – 如果區(qū)域包含的單元格少于 2 個(gè)
*/
int addMergedRegionUnsafe(CellRangeAddress region);
可以看出使用addMergedRegionUnsafe方法合并單元格可能會(huì)導(dǎo)致工作簿損壞，而使用addMergedRegion會(huì)進(jìn)行單元格是否重復(fù)合并的校驗(yàn)：
private int addMergedRegion(CellRangeAddress region, boolean validate) {
if (region.getNumberOfCells() < 2) {
throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");
}
region.validate(SpreadsheetVersion.EXCEL2007);
if (validate) {
// throw IllegalStateException if the argument CellRangeAddress intersects with
// a multi-cell array formula defined in this sheet
validateArrayFormulas(region);
// Throw IllegalStateException if the argument CellRangeAddress intersects with
// a merged region already in this sheet
validateMergedRegions(region);
}
CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
ctMergeCell.setRef(region.formatAsString());
final int numMergeRegions=ctMergeCells.sizeOfMergeCellArray();
// also adjust the number of merged regions overall
ctMergeCells.setCount(numMergeRegions);
return numMergeRegions-1;
}
校驗(yàn)合并單元格的方法validateMergedRegions(region)，如果候選區(qū)域不與此工作表中的現(xiàn)有合并區(qū)域相交就會(huì)報(bào)錯(cuò)：
private void validateMergedRegions(CellRangeAddress candidateRegion) {
for (final CellRangeAddress existingRegion : getMergedRegions()) {
if (existingRegion.intersects(candidateRegion)) {
throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() +
" to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ").");
}
}
}
可以看出addMergedRegionUnsafe會(huì)跳過單元格合并的校驗(yàn)，但會(huì)導(dǎo)致文件被損壞，所以導(dǎo)出的文件打開后會(huì)提示文件有問題，如果使用addMergedRegion方法，easyExcel在列表動(dòng)態(tài)添加行excelWriter.fill(excelVoList, fillConfig, writeSheet);時(shí)就會(huì)直接報(bào)上述錯(cuò)誤，導(dǎo)致程序中斷。
我采用的方法是用easyExcel不使用合并策略導(dǎo)出xlsx文件到臨時(shí)文件中，然后使用poi的XSSFWorkbook讀取該臨時(shí)文件，然后用這個(gè)新的臨時(shí)文件進(jìn)行單元格合并，這樣單元格檢查時(shí)就不會(huì)報(bào)錯(cuò)了，順利導(dǎo)出，打開后也不會(huì)有錯(cuò)誤提示！
public class DataToExcel {
public void exportFile() {
File filePath = new File("D:\\test\\testMerge.xlsx");
OutputStream os= Files.newOutputStream(filePath.toPath());
int firstRow = 18; //從第18行開始合并
int lastRow = 18;
int beginRow = 18;
//單元格合并
List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();
if (CollectionUtil.isNotEmpty(excelVoList)) {
if (excelVoList.size() > 1) {
for (int i = 0; i < excelVoList.size() - 1; i++) {
cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 1, 4));
cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 7, 8));
firstRow++;
lastRow++;
}
}
}
//將easyExcel生成的文件保存在臨時(shí)文件中待poi進(jìn)一步做合并單元格
File tmpFile = new File("D:\\tmp\\tmpFile.xlsx");
OutputStream tmpOutputStream = Files.newOutputStream(tmpFile.toPath());
//獲取excel模板
File file = new File("D:\\template\\template01.xlsx");
InputStream inputStream = Files.newInputStream(file.toPath());
//將easyExcel生成的文件保存在臨時(shí)文件中待poi進(jìn)一步做合并單元格
//File tmpFile = new File("/tmp/" + "tmp_file.xlsx");
//OutputStream tmpOutputStream = Files.newOutputStream(tmpFile.toPath());
//獲取excel模板
//InputStream inputStream = new URL(filePath).openStream();
ExcelWriter excelWriter = EasyExcel.write(tmpOutputStream).withTemplate(inputStream)
// .registerWriteHandler(fillMergeStrategy) //不采用合并策略
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
//參數(shù)集合，直接寫入到Excel數(shù)據(jù)
excelWriter.fill(paramsMap, writeSheet);
//列表數(shù)據(jù)
excelWriter.fill(excelVoList, fillConfig, writeSheet);
excelWriter.finish();
//使用poi合并單元格，使用registerWriteHandler合并單元格會(huì)與fill方法中創(chuàng)建單元格后校驗(yàn)合并單元格沖突而引發(fā)報(bào)錯(cuò)
InputStream in = Files.newInputStream(tmpFile.toPath());
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheetAt(0);
if (CollectionUtils.isNotEmpty(cellRangeAddressList)) {
for (CellRangeAddress cellAddresses : cellRangeAddressList) {
//合并單元格
sheet.addMergedRegion(cellAddresses);
//設(shè)置單元格樣式，解決合并單元格后邊框缺失問題
setRegionStyle(sheet, cellAddresses, setDefaultStyle(workbook));
}
}
workbook.write(os);
os.flush();
os.close();
}
//使用poi設(shè)置合并單元格后的樣式
public void setRegionStyle(XSSFSheet sheet, CellRangeAddress region, XSSFCellStyle xssfCellStyle) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
XSSFRow row = sheet.getRow(i);
if (null == row) row = sheet.createRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
XSSFCell cell = row.getCell(j);
if (null == cell) cell = row.createCell(j);
cell.setCellStyle(xssfCellStyle);
}
}
}
public XSSFCellStyle setDefaultStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
// 邊框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
// 居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 字體
XSSFFont font = workbook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
return cellStyle;
}
}
如果有更好的解決方式，歡迎再評(píng)論區(qū)留言哦！文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-641455.html
參考文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-641455.html
到了這里，關(guān)于解決easyExcel按模板導(dǎo)出xlsx文件打開提示“發(fā)現(xiàn)xxx.xlsx中部分內(nèi)容有問題，是否讓我們盡量嘗試恢復(fù)？”的問題的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
