easyExcel通用导出工具类
需求前景
近期在做几个关于excel文件导出的操作，其中有需要分部门导出多个sheet的，也有需要根据模板进行导出的，还有需要合并单元格的，于是便在Easyexcel的基础上继续造了个轮子。
轮子开始
EasyExcelExportUtil
/**
* @version 1.0.0
* @className: EasyExcelExportUtil
* @description: EasyExcelExportUtil 通用多sheet、使用模板进行导出的工具类封装
* @author: LiJunYi
* @create: 2022/9/5 15:55
*/
@Slf4j
public class EasyExcelExportUtil
{
/**
* 写多个sheet
*
* @param response 响应
* @param fileName 文件名字
* @param elementType 元素类型
* @param dataMap 数据Map集合
* @param excludeColumnFieldNames 排除列字段名称
* @throws IOException ioexception
*/
public static void writeMultipleSheet(HttpServletResponse response, String fileName, Class<?> elementType, Map<String, ? extends Collection<?>> dataMap,Collection<String> excludeColumnFieldNames) throws IOException {
try {
setResponse(response,fileName);
// 分sheet写入
try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).excludeColumnFieldNames(excludeColumnFieldNames).build()) {
AtomicInteger i = new AtomicInteger();
dataMap.forEach((k, v) -> {
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
WriteSheet writeSheet = EasyExcel.writerSheet(i.get(), k).build();
excelWriter.write(v, writeSheet);
i.incrementAndGet();
});
if (dataMap.isEmpty())
{
// 写入空表
WriteSheet writeSheet = EasyExcel.writerSheet(i.get(), "暂无数据").build();
excelWriter.write(Lists.newArrayList(), writeSheet);
}
excelWriter.finish();
response.flushBuffer();
}
} catch (Exception e) {
writeWhenNotData(response,e,"下载文件失败");
}
}
/**
* 写表中使用模板
*
* @param response 响应
* @param data 数据
* @param serverMbPath 模板存放路径
* @param templateFileName 模板文件名字
* @param elementType 元素类型
* @param filenamePrefix 文件名前缀
* @param sheetName sheet工作簿名称
* @param converter 转换器
* @throws IOException ioexception
*/
public static void writeSheetUseTemplate(HttpServletResponse response, Class<?> elementType, Collection<?> data, String serverMbPath, String templateFileName, String filenamePrefix, String sheetName, Converter<?> converter) throws IOException {
String tmpName = serverMbPath + templateFileName;
String fileName = FileNameUtil.mainName(tmpName);
setResponse(response,filenamePrefix + fileName);
try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).withTemplate(tmpName).registerConverter(converter).build())
{
WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName).build();
excelWriter.fill(data,writeSheet);
excelWriter.finish();
response.flushBuffer();
} catch (IOException e) {
writeWhenNotData(response,e,"下载文件失败");
}
}
/**
* 写表中使用模板
*
* @param response 响应
* @param data 数据
* @param serverMbPath 模板存放路径
* @param templateFileName 模板文件名字
* @param elementType 元素类型
* @param filenamePrefix 文件名前缀
* @param sheetName sheet工作簿名称
* @param converter 转换器
* @param mergeCellIndex 合并单元序号
* @throws IOException ioexception
*/
public static void writeSheetUseTemplate(HttpServletResponse response, Class<?> elementType, Collection<?> data, String serverMbPath, String templateFileName, String filenamePrefix, String sheetName, Converter<?> converter,int... mergeCellIndex) throws IOException {
String tmpName = serverMbPath + templateFileName;
String fileName = FileNameUtil.mainName(tmpName);
setResponse(response,filenamePrefix + fileName);
try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).withTemplate(tmpName).registerConverter(converter).registerWriteHandler(new EasyExcelMergeStrategy(data.size(),mergeCellIndex)).build())
{
WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName).build();
excelWriter.fill(data,writeSheet);
excelWriter.finish();
response.flushBuffer();
} catch (IOException e) {
writeWhenNotData(response,e,"下载文件失败");
}
}
/**
* 设置响应
*
* @param response 响应
* @param fileName 文件名字
*/
public static void setResponse(HttpServletResponse response,String fileName) throws UnsupportedEncodingException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String downFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + downFileName + ".xlsx");
}
/**
* 当没有数据写
*
* @param response 响应
*/
public static void writeWhenNotData(HttpServletResponse response,Exception e,String msg) throws IOException {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
if (null != e)
{
String s = msg + e.getMessage();
response.getWriter().println(JSONUtil.toJsonStr(AjaxResult.error(s)));
}else
{
response.getWriter().println(JSONUtil.toJsonStr(AjaxResult.warn(msg)));
}
}
}
EasyExcel合并单元格策略
/**
* @version 1.0.0
* @className: EasyExcelMergeStrategy
* @description: EasyExcel通用合并单元格策略实现
* @author: LiJunYi
* @create: 2022/12/15 10:49
*/
public class EasyExcelMergeStrategy extends AbstractMergeStrategy {
/**
* 合并的列编号，从0开始，指定的index或自己按字段顺序数
*/
private Set<Integer> mergeCellIndex = new HashSet<>();
/**
* 数据集大小，用于区别结束行位置
*/
private Integer maxRow = 0;
/**
* 禁止无参声明
*/
private EasyExcelMergeStrategy() {
}
/**
* 有参构造
*
* @param maxRow 最大行
* @param mergeCellIndex 合并哪几列的行序号
*/
public EasyExcelMergeStrategy(Integer maxRow, int... mergeCellIndex) {
Arrays.stream(mergeCellIndex).forEach(item -> {
this.mergeCellIndex.add(item);
});
this.maxRow = maxRow;
}
/**
* 记录上一次合并的信息
*/
private Map<Integer, MergeRange> lastRow = new HashedMap();
/**
* 合并
*
* @param sheet 表
* @param cell 细胞
* @param head 头
* @param relativeRowIndex 相对行序号
*/
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int currentCellIndex = cell.getColumnIndex();
// 判断该列是否需要合并
if (mergeCellIndex.contains(currentCellIndex)) {
// 动态获取cell的值，避免cell的类型不同获取方式不同
String currentCellValue = getCellValueByCellType(cell);
int currentRowIndex = cell.getRowIndex();
if (!lastRow.containsKey(currentCellIndex)) {
// 记录首行起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
return;
}
//有上行这列的值了，拿来对比.
MergeRange mergeRange = lastRow.get(currentCellIndex);
if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
// 结束的位置触发下合并.
// 同行同列不能合并，会抛异常
if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
}
// 更新当前列起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
}
// 合并行 + 1
mergeRange.endRow += 1;
// 结束的位置触发下最后一次没完成的合并
if (relativeRowIndex.equals(maxRow - 1)) {
MergeRange lastMergeRange = lastRow.get(currentCellIndex);
// 同行同列不能合并，会抛异常
if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
}
}
}
}
/**
* 获取不同类型的cell的Value，转换成String返回
* 可根据CellType的枚举自行补全对应的类型
* @return {@link String}
*/
private static String getCellValueByCellType(Cell cell)
{
CellType cellType = cell.getCellType();
int ordinal = cellType.ordinal();
Object value;
switch (ordinal)
{
case 1:
value = cell.getNumericCellValue();
break;
case 2:
value = cell.getStringCellValue();
break;
default:
value = StrUtil.EMPTY;
}
return Convert.toStr(value);
}
}
class MergeRange{
/**
* 开始行
*/
public int startRow;
/**
* 结束行
*/
public int endRow;
/**
* 开始单元格
*/
public int startCell;
/**
* 结束单元格
*/
public int endCell;
/**
* 最后值
*/
public String lastValue;
/**
* 构造函数
*
* @param lastValue 最后值
* @param startRow 开始行
* @param endRow 结束行
* @param startCell 开始单元格
* @param endCell 结束单元格
*/
public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
this.startRow = startRow;
this.endRow = endRow;
this.startCell = startCell;
this.endCell = endCell;
this.lastValue = lastValue;
}
}
使用实例
@Slf4j
@RestController
@RequestMapping("payroll")
public class PayrollController
{
/**
* 多sheet进行导出
* @param month 月
* @return {@link AjaxResult}
*/
@GetMapping(value = "download/job")
public void downloadPayrollFileInJob(HttpServletResponse response) throws IOException
{
Map<String,List<PayrollModel>> dataMap = payrollService.getPayrollsByMonth(month,type);
// 文件名
String fileName = FileConstants.FIVE_PAYROLL_JOB;
// 要忽略的字段名称
Set<String> excludeColumnFiledNames = new HashSet<String>();
excludeColumnFiledNames.add("vestingYear");
excludeColumnFiledNames.add("vestingMonth");
EasyExcelExportUtil.writeMultipleSheet(response, fileName, PayrollModel.class, dataMap,excludeColumnFiledNames);
}
/**
* 使用模板导出
*
* @param month 月
* @param response 响应
*/
@GetMapping("download/published/{month}")
public void downloadForPublication(@PathVariable("month") String month, HttpServletResponse response) throws IOException {
List<PayrollModel> models = payrollService.getPayrollsToPublicationByMonth(month);
if (models.isEmpty())
{
EasyExcelExportUtil.writeWhenNotData(response,null,"暂无数据");
}else
{
EasyExcelExportUtil.writeSheetUseTemplate(response,PayrollModel.class,models,globalProperties.getServerMbPath(), FileConstants.PAYROLL_FOR_PUBLICATION,month);
}
}
/**
* 使用模板导出并合并行
*
* @param request 请求
* @param response 响应
* @throws IOException ioexception
*/
@GetMapping("dept")
public void downloadDeptCarDataFile(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<CarModel> carModels = carService.getDownloadDeptCarDataFile();
if (carModels.isEmpty())
{
EasyExcelExportUtil.writeWhenNotData(response,null,"暂无数据");
}else
{
EasyExcelExportUtil.writeSheetUseTemplate(response, DormModel.class,carModels,globalProperties.getServerMbPath(), FileConstants.DEPT_CAR_EXCEL_FILE,"","xx一览表",new LocalDateConverter(),0,1);
}
}
}