提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
- 前言
- 一、导入依赖
- 二、代码
- 1. 导出简单的Excel
- 2. 代码控制导出报表的格式
- 总结
前言
SpringBoot 使用Alibaba提供的EasyExcel导出Excel报表。
本文中涉及的业务逻辑有：
- 前端发起请求，后端封装Excel信息，直接在浏览器完成下载业务；
- 在后端，通过代码，控制导出的Excel样式。（主要包括：横向、纵向合并单元格）
本文中后端导出Excel的数据，只包括导出列表数据，具体指：将一个List< List< String>>类型的数据导出到Excel中。(可将数据导出到不同的sheet中)
也就是说导出的数据没有固定的对象格式。如果需要导出List< Object> 的列表，需要封装导出对象，并对属性配置@ExcelProperty等信息，此文目前将不涉及这部分的内容。
最终效果展示：
一、导入依赖
导入相关依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>
二、代码
1. 导出简单的Excel
// 导出的Excel 文件名称
String fileName = "xxx.xlsx";
try {response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Type", "application/vnd.ms-excel");fileName = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Access-Control-Expose-Headers","Content-Disposition");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);response.addHeader("Cache-Control", "no-cache");// 创建ExcelWriter对象ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();// 写入第一个sheet的数据WriteSheet sheet1 = EasyExcel.writerSheet(0, "Sheet1").registerWriteHandler(mergePrevCol1).build();// 设置Excel表头（表头横向展示）sheet1.setHead(Arrays.asList(Arrays.asList(""),Arrays.asList("1#加热炉"),Arrays.asList("1"),Arrays.asList("2"),Arrays.asList("3#加热炉"),Arrays.asList("1"),Arrays.asList("2")));// 写入数据excelWriter.write(heatFurnace1And3MZLValueList, sheet1);// 关闭ExcelWriterexcelWriter.finish();
} catch (UnsupportedEncodingException e) {throw new RuntimeException(e);
} catch (IOException e) {throw new RuntimeException(e);
}
以上就能完成简单的导出Excel报表的功能。
请注意Controller对应接口的返回值，一般设置为void
@PostMapping(value = "/export")@ApiOperation(value = "导出报表")public void export(@RequestBody RecordExportBO bean, HttpServletResponse response) {// 这里的RecordExportBO 为封装接收前端数据的对象iService.export(bean,response);
}
2. 代码控制导出报表的格式
这里主要包括控制表头的格式以及合并单元格 。
A. 合并行的工具类
package cisdi.info.imc.device.model.common.util;import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** 合并Excel的行（内容相同的行才合并）*/
public class ExcelFillCellMergeStrategyUtil implements CellWriteHandler {private int[] mergeColumnIndex; //数组存放这一行需要合并那几列 [0,1,2] 在这mergeRowIndex行中合并 0 、1、2列private int mergeRowIndex; // 存放需要向上合并的列private Integer noMergeRowIndex;// 不要合并的列public ExcelFillCellMergeStrategyUtil() {}public ExcelFillCellMergeStrategyUtil(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}public ExcelFillCellMergeStrategyUtil(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) {this.mergeColumnIndex = mergeColumnIndex;this.mergeRowIndex = mergeRowIndex;this.noMergeRowIndex = noMergeRowIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {int curRowIndex = cell.getRowIndex();int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}/*** 当前单元格向上合并* .* @param writeSheetHolder 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();Row preRow = cell.getSheet().getRow(curRowIndex - 1);if (preRow == null) {// 当获取不到上一行数据时，使用缓存sheet中数据preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);}Cell preCell = preRow.getCell(curColIndex);Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();//不需要合并的列直接跳出if ( (noMergeRowIndex != null) && noMergeRowIndex == (curRowIndex - 1) ){return;}// 将当前单元格数据与上一个单元格数据比较Boolean dataBool = preData.equals(curData);//此处需要注意：所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());if (dataBool && equals) {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);}}}}
B. 合并列的工具类
package cisdi.info.imc.device.model.common.util;import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.HashMap;
import java.util.Map;
import java.util.List;/*** 合并Excel的列*/
public class ExcelFillCellMergePrevColUtil implements CellWriteHandler {private static final String KEY ="%s-%s";//所有的合并信息都存在了这个map里面Map<String, Integer> mergeInfo = new HashMap<>();public ExcelFillCellMergePrevColUtil() {}public ExcelFillCellMergePrevColUtil(Map<String, Integer> mergeInfo) {this.mergeInfo = mergeInfo;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));if(null != num){// 合并最后一行 ,列mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);}}public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {Sheet sheet = writeSheetHolder.getSheet();CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);sheet.addMergedRegion(cellRangeAddress);}//num从第几列开始增加多少列// curRowIndex 在第几行进行行合并// curColIndex 在第几列进行合并// num 合并多少格// 比如我上图中中心需要在第三行 从0列开始合并三列 所以我可以传入 (3,0,2)public void add (int curRowIndex, int curColIndex , int num){mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);}}
C. 控制表头格式
/**
* 设置导出报表的表格格式
* @return
*/
public CellStyleStrategyUtil horizontalCellStyleStrategyBuilder() {WriteCellStyle headWriteCellStyle = new WriteCellStyle();//设置头字体WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 15);headWriteFont.setBold(true);headWriteCellStyle.setWriteFont(headWriteFont);//设置头居中headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//内容策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);return new CellStyleStrategyUtil(headWriteCellStyle, contentWriteCellStyle,Boolean.TRUE);}
D. 具体用法
直接结合具体例子，通过例子中关键代码的注解进行介绍：
// 导出的Excel 文件名称
String fileName = "xxx.xlsx";
try {response.setContentType("application/vnd.ms-excel");response.setHeader("Content-Type", "application/vnd.ms-excel");fileName = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Access-Control-Expose-Headers","Content-Disposition");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);response.addHeader("Cache-Control", "no-cache");// 创建ExcelWriter对象ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();// 创建第一个sheet的列合并工具类对象ExcelFillCellMergePrevColUtil mergePrevCol1 = new ExcelFillCellMergePrevColUtil();// 第一个参数：合并哪一行；第二个参数：合并的起始列；第三个参数：往右合并的列数；// 指一条代表合并第0行的第1，2，3列。mergePrevCol1.add(0,1,2);// 这一行代表合并第0行的4，5，6列mergePrevCol1.add(0,4,2);// 写入第一个sheet的数据WriteSheet sheet1 = EasyExcel.writerSheet(0, "Sheet1")// 字体表格样式工具类，下方展示.registerWriteHandler(horizontalCellStyleStrategyBuilder())// 合并行。第一个参数：从第几行开始合并、第二个参数：合并哪几列、第三个参数：到哪一行为止。// 这一行的意思是：从第0行开始合并第0列，到第2行为止。也就是合并了第0行和第1行的第0列。（注意这里必须合并列的内容相同才能够合并）.registerWriteHandler(new ExcelFillCellMergeStrategyUtil(0,new int[]{0}, 2))// 合并列。.registerWriteHandler(mergePrevCol1).build();// 设置Excel表头// Collections.singletonList("") 这样设置表头的第0列才能与第1行的第0列合并。使用Arrays.asList("")是不行的。sheet1.setHead(Arrays.asList(Collections.singletonList(""),Arrays.asList("1#加热炉"),Arrays.asList("1"),Arrays.asList("2"),Arrays.asList("3#加热炉"),Arrays.asList("1"),Arrays.asList("2")));// 写入数据// list1的类型为List<List<String>>excelWriter.write(list1, sheet1);// 创建第二个sheet的列合并工具类对象ExcelFillCellMergePrevColUtil mergePrevCol2 = new ExcelFillCellMergePrevColUtil();mergePrevCol2.add(0,1,3);mergePrevCol2.add(0,5,3);// 写入第二个sheet的数据WriteSheet sheet2 = EasyExcel.writerSheet(1, "Sheet2").registerWriteHandler(horizontalCellStyleStrategyBuilder()) // 字体表格样式工具类，下方展示.registerWriteHandler(new ExcelFillCellMergeStrategyUtil(0,new int[]{0}, 2)).registerWriteHandler(mergePrevCol2).build();sheet2.setHead(Arrays.asList(Collections.singletonList(""),Arrays.asList("2#加热炉"),Arrays.asList("1"),Arrays.asList("2"),Arrays.asList("3"),Arrays.asList("4#加热炉"),Arrays.asList("1"),Arrays.asList("2"),Arrays.asList("3")));// list2的类型为List<List<String>>excelWriter.write(list2, sheet2);// 关闭ExcelWriterexcelWriter.finish();
} catch (UnsupportedEncodingException e) {throw new RuntimeException(e);
} catch (IOException e) {throw new RuntimeException(e);
}
效果图
总结
阿巴阿巴…
文章内容就到这里结尾了，整个过程鄙人亲力亲为。如果你的需求相同，又还是无法达到想要的效果，抑或是出现了什么问题，欢迎找笔者沟通！！！
此外，本篇文章中单元格合并的工具类等代码，并非我自己亲自完成，如有侵权，请及时联系笔者。
