在項目中需要對各種報表進行excel導出,主要使用了兩種方式:esayExcel模闆導出,poi手工導出。
esayExcel模闆導出
使用模闆導出相對而已比較簡單,其過程如下:
- 準備excel模闆 按照需求設計展示樣式,同時填入對應的字段名稱。字段的名稱和填充的資料相關。
EasyExcel導出中單元格合并遇到問題
如果是填充的集合,則需通過“對象名.屬性”的方式進行模闆設計。
- 通過模闆得到ExcelWriter
public static ExcelWriter getExcelWriterWithMergeStrategy(HttpServletResponse response, String fileName, String templateFileName,
ExcelFillCellMergeStrategy excelFillCellMergeStrategy,
ExcelFillCellMergeStrategy detailMergeStrategy) throws Exception {
OutputStream outputStream = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName,"UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");//設定類型
response.setHeader("Pragma", "No-cache");//設定頭
response.setHeader("Cache-Control", "no-cache");//設定頭
response.setDateHeader("Expires", 0);//設定日期頭
ExcelWriter excelWriter = EasyExcel.write(outputStream)
.registerWriteHandler(excelFillCellMergeStrategy)
.registerWriteHandler(detailMergeStrategy)
.withTemplate(EasyExcelUtil.class.getClassLoader().getResourceAsStream("template/excel/"+templateFileName))
.build();
return excelWriter;
}
主要是通過模闆檔案得到一個ExcelWriter 對象,其中xxxMergeStrategy為合并政策類。
- 建構WriteSheet 并填充資料
WriteSheet writeSheet = EasyExcel.writerSheet("總體資源使用情況").build();
writeSheet.setAutoTrim(true);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 不加這個配置,第一行無法合并
//填充普通變量
excelWriter.fill(data, writeSheet);
excelWriter.fill(new FillWrapper("resourceScale", resourceScaleStatisticsVoList), fillConfig, writeSheet);
- 模闆填充整個過程比較簡單,主要是考慮單元格合并問題。單元格合并需要傳入一個合并政策類,主要的邏輯是在資料填充過程中,會比較目前行和前一行的資料是否相同,如果相同則合并。
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private String sheet;
private List<MergeStrategyParam> mergeStrategyParams;
public ExcelFillCellMergeStrategy(List<MergeStrategyParam> mergeStrategyParams, String sheet) {
this.sheet = sheet;
this.mergeStrategyParams = mergeStrategyParams;
}
public static ExcelFillCellMergeStrategy createMergeStrategy(int[] mergeColumnIndex, int mergeRowIndexStart, int mergeRowIndexEnd, String sheet){
ExcelFillCellMergeStrategy.MergeStrategyParam mergeStrategyParam = new ExcelFillCellMergeStrategy.MergeStrategyParam();
mergeStrategyParam.setMergeColumnIndex(mergeColumnIndex);
mergeStrategyParam.setMergeRowIndexStart(mergeRowIndexStart);
mergeStrategyParam.setMergeRowIndexEnd(mergeRowIndexEnd);
List<ExcelFillCellMergeStrategy.MergeStrategyParam> paramList = new ArrayList<>();
paramList.add(mergeStrategyParam);
return new ExcelFillCellMergeStrategy(paramList, sheet);
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
if (StringUtil.isNotEmpty(sheet) && !writeSheetHolder.getSheetName().equalsIgnoreCase(sheet)){
return;
}
//目前行
int curRowIndex = cell.getRowIndex();
//目前列
int curColIndex = cell.getColumnIndex();
for (MergeStrategyParam mergeStrategyParam : mergeStrategyParams) {
int[] columnIndex = mergeStrategyParam.getMergeColumnIndex();
if (curRowIndex > mergeStrategyParam.getMergeRowIndexStart() && curRowIndex <= mergeStrategyParam.getMergeRowIndexEnd()) {
for (int i = 0; i < columnIndex.length; i++) {
if (curColIndex == columnIndex[i]) {
try {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
} catch (Exception e) {
e.printStackTrace();
break;
}
break;
}
}
break;
}
}
}
/**
* 目前單元格向上合并
*
* @param writeSheetHolder
* @param cell 目前單元格
* @param curRowIndex 目前行
* @param curColIndex 目前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//擷取目前行的第一列的資料和上一行的第一列資料,通過第一行資料是否相同進行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比較目前行的第一列的單元格與上一行是否相同,相同合并目前單元格與上一行
if (curData.equals(preData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一個單元格已經被合并,則先移出原有的合并單元,再重新添加合并單元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一個單元格未被合并,則新增合并單元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
@Data
public static class MergeStrategyParam{
private int[] mergeColumnIndex;
private int mergeRowIndexStart;
private int mergeRowIndexEnd;
}
}
這裡主要增加了一個sheet變量和MergeStrategyParam對象。用來控制合并的sheet以及行數和列數。
問題
通過政策類合并單元格會出現資料的第一行無法合并。
填充時設定強制産生新行。
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); // 不加這個配置,第一行無法合并
手工導出excel
CellRangeAddress blockRange = new CellRangeAddress(startRow, endRow - 1, 0, 0);
sheet.addMergedRegionUnsafe(blockRange);
需要手動指定需要合并的區域,包括行數和列數。
通常我們是按照某個分類字段進行單元格合并,是以需要将某個分類進行分組。stream的groupby剛好可以滿足這個要求。
這樣就可以計算出每一個分類一共多少行,然後可以指定需要合并的行數。
問題
groupingBy()預設是使用的HashMap進行資料存儲。對key進行hash值計算,是以産生的資料的順序就會改變。合并單元格的資料順序,和資料填入的順序不一樣,會導緻excel資料展示紊亂。
是以在進行分組時需要指定排序規則,使用TreeMap來進行資料存儲,可以保證資料按字元順序進行排序。