最近写Excel导出功能,发现需求有点复杂,这里整理一下思路和解决方案
一、需求背景:
老系统改造,功能和代码是现成的,预览了一下内容:
第一个是有特定样式,比如首行标题,以及红色的列名称
第二个,导出多个Sheet页
第三个,最后多一行放置导出时间
二、技术选型 :
我非常喜欢用Hutool的工具处理,然后看了下最新的文档案例,推测是可以满足上述需求的
http://hutool.cn/docs/#/poi/Excel生成-ExcelWriter
重点是关于如何多Sheet页导出的支持,Hutool这里没有细说,看看有没有现成的案例
经过简单测试发现是可行的
https://blog.csdn.net/ZLK1142/article/details/106531246/
三、落地实现:
1、前后交互问题:
本来是打算使用前端导出的,后端接口提供数据即可,但是前端导出怎么设置具体样式并不熟悉,加上自定义样式需求多,就放弃这个方案了
使用后端导出的基本办法是使用get请求方式的接口,然后前端使用window.open()打开新链接,这样跳转下载一个文件
- 这样好处是不用编写交互处理,用户等待新页面弹出下载提示即可
- 但是请求参数,令牌信息都要通过url携带,不安全的,也会暴露信息
再加上现有系统无法从url上获取参数,所以改用axios请求实现
axios请求实现的问题在于响应的处理,要在前端声明特定的blob类型、重新封装文件内容、和下载事件处理
2、Hutool基于业务需求的封装:
之前写的导出就是导出数据就行,这里参考多sheet自己实现的一个逻辑
Hutool支持了用Map配置别名映射,为了更方便实现更符合业务逻辑方式的开发,可以自定义映射注解
package cn.anmte.wd.excel.annotation; import java.lang.annotation.*; /** * @description Excel 字段信息 * @author OnCloud9 * @date 2024/3/6 16:26 */ @Documented @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.FIELD) public @interface ExportAlias { String value(); }
通过注解的导出实体类,可以得到映射信息
/** * @description 获取导出数据别名映射 * @author OnCloud9 * @date 2024/3/6 16:16 * @params * @return */ public static <ExportDTO> Map<String, String> getExportDataAlias(Class<ExportDTO> exportClass) { Map<String, String> aliasMap = new HashMap<>(); Field[] declaredFields = exportClass.getDeclaredFields(); for (Field field : declaredFields) { String name = field.getName(); ExportAlias annotation = field.getAnnotation(ExportAlias.class); if (Objects.isNull(annotation)) continue; aliasMap.put(name, annotation.value()); } return aliasMap; }
因为是多sheet,所以要声明一个类封装sheet信息
package cn.anmte.wd.excel; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; import java.util.Map; import java.util.function.Consumer; import java.util.function.Function; @Data @AllArgsConstructor @NoArgsConstructor @Builder public class SheetInfo<ExportData> { private String sheetName; private String sheetTitle; /* 导出实体的class对象 */ private Class<ExportData> exportDataClass; /* 导出的数据 */ private List<ExportData> exportDataList; /* 别名映射过滤方法 默认不处理 */ private Function<Map<String, String>, Map<String, String>> aliasFilter; /* 数据写入完成后的操作 -> 默认执行内容 */ private Consumer<WdImcExcelUtil.AfterWrite> awConsumer; }
存在动态列名导出的场景,这里基于解析映射信息的基础上,追加了调整映射信息的方法:
提供一个Function方法接口,投入解析好的映射Map,具体调整方法交给外部调用实现
private static Map<String, String> aliasConfig(ExcelWriter writer, SheetInfo<?> sheetInfo) { Map<String, String> aliasMap = getExportDataAlias(sheetInfo.getExportDataClass()); Function<Map<String, String>, Map<String, String>> aliasFilter = sheetInfo.getAliasFilter(); if (Objects.nonNull(aliasFilter)) aliasMap = aliasFilter.apply(aliasMap); writer.clearHeaderAlias(); writer.setHeaderAlias(aliasMap); writer.setOnlyAlias(true); return aliasMap; }
WebServlet下载逻辑部分:
private static void exportForDownload(HttpServletResponse response, ExcelWriter writer, String workBookName) { ServletOutputStream out = null; try { response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(workBookName + ".xlsx", "UTF-8")); out = response.getOutputStream(); writer.flush(out, true); } catch (IOException e) { e.printStackTrace(); } finally { // 关闭writer,释放内存 writer.close(); } // 关闭输出Servlet流 IoUtil.close(out); }
导出整装的逻辑:
后置处理和上面的动态映射也是同一个逻辑,外部实现
因为我想把那些样式处理放到这里统一执行,逻辑层次是清晰的,易于维护
在下面导出可以再补充其他下载方式,开发时间有限就写到这个程度了
/** * @description * @author Cloud9 * @date 2024/3/6 17:02 * @params * @return */ public static void writeWdMultiSheetWorkBook(HttpServletResponse response, String workBookName, List<SheetInfo<?>> sheetInfoList) { if (CollectionUtils.isEmpty(sheetInfoList)) return; ExcelWriter writer = ExcelUtil.getWriter(); /* 开启多sheet页支持方法 */ writer.renameSheet(0, sheetInfoList.get(0).getSheetName()); sheetInfoList.forEach(sheetInfo -> { /* sheet名称设置 */ writer.setSheet(sheetInfo.getSheetName()); /* sheet别名映射设置 */ Map<String, String> aliasMap = aliasConfig(writer, sheetInfo); /* 设置标头内容 */ if(StringUtils.isNotBlank(sheetInfo.getSheetTitle())) writer.merge(aliasMap.size() - 1, sheetInfo.getSheetTitle()); /* 写入数据 */ writer.write(sheetInfo.getExportDataList(), true); /* 后置处理 */ Consumer<AfterWrite> awConsumer = sheetInfo.getAwConsumer(); if (Objects.nonNull(awConsumer)) awConsumer.accept(AfterWrite.builder().writer(writer).aliasMap(aliasMap).build()); }); exportForDownload(response, writer, workBookName); }
完整工具类代码(WdImcExcelUtil):
这里样式设置的代码没完全写好,可以提供参考
package cn.anmte.wd.excel; import cn.anmte.wd.excel.annotation.ExportAlias; import cn.hutool.core.io.IoUtil; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import cn.hutool.poi.excel.style.StyleUtil; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.lang.reflect.Field; import java.net.URLEncoder; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Objects; import java.util.function.Consumer; import java.util.function.Function; /** * @description * https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter * @author OnCloud9 * @date 2024/3/6 16:11 */ public class WdImcExcelUtil { /** * @description 获取当前时间线后缀 * @author OnCloud9 * @date 2024/3/6 17:13 * @params * @return */ public static String getCurrentTimeSuffix(String format) { if (StringUtils.isBlank(format)) format = "yyyyMMddHHmmss"; DateTimeFormatter formatter = DateTimeFormatter.ofPattern(format); return LocalDateTime.now().format(formatter); } /** * @description * @author OnCloud9 * @date 2024/3/6 17:33 * @params * @return */ public static void writeWdSheetWorkBook(HttpServletResponse response, String workBookName, SheetInfo<?> sheetInfo) { ExcelWriter writer = ExcelUtil.getWriter(); writer.renameSheet(0, sheetInfo.getSheetName()); writer.setSheet(sheetInfo.getSheetName()); /* sheet别名映射设置 */ Map<String, String> aliasMap = aliasConfig(writer, sheetInfo); /* 设置标头内容 */ if(StringUtils.isNotBlank(sheetInfo.getSheetTitle())) writer.merge(aliasMap.size() - 1, sheetInfo.getSheetTitle()); /* 写入数据 */ writer.write(sheetInfo.getExportDataList(), true); /* 后置处理 */ Consumer<AfterWrite> awConsumer = sheetInfo.getAwConsumer(); if (Objects.nonNull(awConsumer)) awConsumer.accept(AfterWrite.builder().writer(writer).aliasMap(aliasMap).build()); exportForDownload(response, writer, workBookName); } /** * @description * @author OnCloud9 * @date 2024/3/6 17:02 * @params * @return */ public static void writeWdMultiSheetWorkBook(HttpServletResponse response, String workBookName, List<SheetInfo<?>> sheetInfoList) { if (CollectionUtils.isEmpty(sheetInfoList)) return; ExcelWriter writer = ExcelUtil.getWriter(); /* 开启多sheet页支持方法 */ writer.renameSheet(0, sheetInfoList.get(0).getSheetName()); sheetInfoList.forEach(sheetInfo -> { /* sheet名称设置 */ writer.setSheet(sheetInfo.getSheetName()); /* sheet别名映射设置 */ Map<String, String> aliasMap = aliasConfig(writer, sheetInfo); /* 设置标头内容 */ if(StringUtils.isNotBlank(sheetInfo.getSheetTitle())) writer.merge(aliasMap.size() - 1, sheetInfo.getSheetTitle()); /* 写入数据 */ writer.write(sheetInfo.getExportDataList(), true); /* 后置处理 */ Consumer<AfterWrite> awConsumer = sheetInfo.getAwConsumer(); if (Objects.nonNull(awConsumer)) awConsumer.accept(AfterWrite.builder().writer(writer).aliasMap(aliasMap).build()); }); exportForDownload(response, writer, workBookName); } private static Map<String, String> aliasConfig(ExcelWriter writer, SheetInfo<?> sheetInfo) { Map<String, String> aliasMap = getExportDataAlias(sheetInfo.getExportDataClass()); Function<Map<String, String>, Map<String, String>> aliasFilter = sheetInfo.getAliasFilter(); if (Objects.nonNull(aliasFilter)) aliasMap = aliasFilter.apply(aliasMap); writer.clearHeaderAlias(); writer.setHeaderAlias(aliasMap); writer.setOnlyAlias(true); return aliasMap; } private static void exportForDownload(HttpServletResponse response, ExcelWriter writer, String workBookName) { ServletOutputStream out = null; try { response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(workBookName + ".xlsx", "UTF-8")); out = response.getOutputStream(); writer.flush(out, true); } catch (IOException e) { e.printStackTrace(); } finally { // 关闭writer,释放内存 writer.close(); } // 关闭输出Servlet流 IoUtil.close(out); } /** * @description 获取导出数据别名映射 * @author OnCloud9 * @date 2024/3/6 16:16 * @params * @return */ public static <ExportDTO> Map<String, String> getExportDataAlias(ExportDTO dto) { Map<String, String> aliasMap = new HashMap<>(); Class<?> exportClass = dto.getClass(); Field[] declaredFields = exportClass.getDeclaredFields(); for (Field field : declaredFields) { String name = field.getName(); ExportAlias annotation = field.getAnnotation(ExportAlias.class); if (Objects.isNull(annotation)) continue; aliasMap.put(name, annotation.value()); } return aliasMap; } /** * @description 获取导出数据别名映射 * @author OnCloud9 * @date 2024/3/6 16:16 * @params * @return */ public static <ExportDTO> Map<String, String> getExportDataAlias(Class<ExportDTO> exportClass) { Map<String, String> aliasMap = new HashMap<>(); Field[] declaredFields = exportClass.getDeclaredFields(); for (Field field : declaredFields) { String name = field.getName(); ExportAlias annotation = field.getAnnotation(ExportAlias.class); if (Objects.isNull(annotation)) continue; aliasMap.put(name, annotation.value()); } return aliasMap; } /** * @description 头部样式设置 * @author OnCloud9 * @date 2024/3/6 16:08 * @params * @return */ public static void headerStyleSetting(ExcelWriter excelWriter) { CellStyle cellStyle = StyleUtil.createCellStyle(excelWriter.getWorkbook()); Sheet sheet = excelWriter.getSheet(); Row row = sheet.getRow(0); /* 设置单元行高度为50磅 */ row.setHeight((short) 1000); /* 创建头部样式的自定义字体 */ Font font = excelWriter.createFont(); font.setFontName("Arial"); font.setBold(true); font.setFontHeightInPoints((short) 24); /* 设置默认的背景色 */ cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中 /* 放置样式 */ Cell cell = row.getCell(0); cell.setCellStyle(cellStyle); } /** * @description 列名样式设置 * @author OnCloud9 * @date 2024/3/6 16:08 * @params * @return */ public static void columnNameStyleSetting(ExcelWriter excelWriter, int colSize) { CellStyle cellStyle = StyleUtil.createCellStyle(excelWriter.getWorkbook()); Sheet sheet = excelWriter.getSheet(); Row row = sheet.getRow(1); /* 创建头部样式的自定义字体 */ Font font = excelWriter.createFont(); font.setFontName("Arial"); font.setBold(true); font.setColor(Font.COLOR_RED); font.setFontHeightInPoints((short) 10); /* 设置样式 */ cellStyle.setFont(font); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); /* 边框样式 */ // 创建边框对象,并设置边框样式 BorderStyle borderStyle = BorderStyle.THIN; // 细边框 short blackIndex = IndexedColors.BLACK.getIndex(); cellStyle.setBorderTop(borderStyle); // 设置上边框 cellStyle.setTopBorderColor(blackIndex); // 设置边框颜色为黑色 cellStyle.setBorderBottom(borderStyle); // 设置下边框 cellStyle.setBottomBorderColor(blackIndex); cellStyle.setBorderLeft(borderStyle); // 设置左边框 cellStyle.setLeftBorderColor(blackIndex); cellStyle.setBorderRight(borderStyle); // 设置右边框 cellStyle.setRightBorderColor(blackIndex); for (int i = 0; i < colSize; i++) { Cell cell = row.getCell(i); cell.setCellStyle(cellStyle); } } /** * @description * @author OnCloud9 * @date 2024/3/7 17:37 * @params * @return */ public static void timelineMark(ExcelWriter excelWriter, int rowIdx, int colIdx) { CellStyle cellStyle = StyleUtil.createCellStyle(excelWriter.getWorkbook()); String currentTime = getCurrentTimeSuffix("yyyy-MM-dd HH:mm:ss"); excelWriter.setCurrentRow(rowIdx); excelWriter.merge(colIdx, "时间:" + currentTime); Cell cell = excelWriter.getOrCreateCell(rowIdx, 0); /* 设置右居中 */ cellStyle.setAlignment(HorizontalAlignment.RIGHT); // 设置右居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中 cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cell.setCellStyle(cellStyle); } @Data @AllArgsConstructor @NoArgsConstructor @Builder public static class AfterWrite { private ExcelWriter writer; private Map<String, String> aliasMap; } }
3、前端JS部分:
封装好axios处理
import { CUSTOMAPIURl } from '@/utils/define' import axios from 'axios' /** * @param apiPath 请求路径 * @param postData 请求参数 * @param token 令牌 * @param fileName 文件名 * @param whenDone 完成时回调 * @param whenErr 异常时回调 */ export function requestExcelExport({ apiPath, postData, token, fileName, whenDone, whenErr }) { axios({ method: 'post', url: CUSTOMAPIURl + apiPath, data: postData, responseType: 'blob', headers: { 'Content-Type': 'application/json', // 示例的 header,你可以根据需要添加更多 'Authorization': token// 示例的授权 header } }).then(function (response) { // 创建一个 blob URL const blobUrl = window.URL.createObjectURL(new Blob([response.data])) const link = document.createElement('a') link.href = blobUrl; link.setAttribute('download', fileName); // 设置下载文件的名称 document.body.appendChild(link) // 触发点击事件来下载文件 link.click(); // 清理 window.URL.revokeObjectURL(blobUrl); document.body.removeChild(link); whenDone() }).catch(function (error) { // 请求失败后的处理 console.error('Error downloading Excel file:', error); whenErr() }) }
方法使用:
四、实现效果:
这里还没处理映射顺序,看起来有点乱,等后面我再追加补充吧....
标签:Map,cellStyle,Vue,sheetInfo,writer,导出,aliasMap,HutoolExcel,import From: https://www.cnblogs.com/mindzone/p/18059722