easyExcel 导入、导出Excel 封装公共的方法

文档包含三部分功能

1、easyExcel 公共导出list<对象>方法，可以自定义excel中第一行和样式
2、easyExcel 导入逻辑，结合spring Validator 验证导入数据是否符合规范
3、easyExcel 自定义导出 list<map> 、 list<对象> （可优化），可把sql.date,sql.time在excel转换正常显示

1、easyExcel 公共导出方法

1）依赖：

<!-- hutool -->
<!-- 阿里开源的excel处理包 -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.3</version>
</dependency>
<!-- poi -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.0.0</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.0.0</version>
</dependency>

2）封装的公共导出方法：

# 其中 templateBool 代表第一行是否为标题行
# 其中 firstRowContent 代码第一行写入的内容
FileUtil.exportExcel(response, "导出应用表列表", "sheet1", App.class, appList, templateBool,firstRowContent);

其中 App.class 实体类属性添加 @ExcelIgnore 忽略导出，@ExcelProperty("*excel列明") 指出导出列表

@ExcelIgnore
@ExcelProperty("*excel列明")

导出代码参考

easyExcel自定义导入头实现https://www.cnblogs.com/Dog1363786601/p/17352096.html

3）实现easyExcel 导入导出依赖公共文件方法

import cn.hutool.core.codec.Base64;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.util.IOUtils;
import org.springframework.web.multipart.MultipartFile;import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;/*** 项目名称：base** <p>功能: 功能描述。</p>** @author：yht* @version：V1.0 2023/5/30*/
@Slf4j
public class FileUtil {/*** 设置导出为excel 的 Response 中的描述** @param response    响应结果对象* @param rawFileName 文件名*/public static void setExcelResponse(HttpServletResponse response, String rawFileName) {//设置响应格式response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8"); // 设置字符编码// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = Base64.encode(rawFileName, Charset.defaultCharset());response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");}/*** MultipartFile对象转成File对象** @param multipartFile* @return*/public static File transferToFile(MultipartFile multipartFile) {
//        选择用缓冲区来实现这个转换即使用java 创建的临时文件 使用 MultipartFile.transferto()方法 。File file = null;try {String originalFilename = multipartFile.getOriginalFilename();String[] filename = originalFilename.split("\\.");file = File.createTempFile(filename[0], filename[1] + ".");multipartFile.transferTo(file);file.deleteOnExit();} catch (IOException e) {e.printStackTrace();}return file;}/*** 断面数据导出Excel* <a href="https://www.cnblogs.com/Dog1363786601/p/17352096.html">EasyExcelSheetWriteHandler 参考：包含map导出样例</a>** @param response：response* @param fileName：文件名* @param sheetName：sheet              页签名称* @param targetClass：目标对象Class* @param dateList：对象数据* @param firstRowDocBool：第一行是不是填写文档说明* @param firstRowContent：第一行的内容*/public static void exportExcel(HttpServletResponse response, String fileName, String sheetName,Class<?> targetClass, List<?> dateList, Boolean firstRowDocBool, String firstRowContent) throws IOException {if (StrUtil.isBlank(fileName)) {//当前日期fileName = DateUtil.format(new DateTime(), DatePattern.CHINESE_DATE_TIME_FORMATTER);}setExcelResponse(response, fileName);// 设置表头字体样式WriteCellStyle headWriteCellStyle = new WriteCellStyle();WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 12); // 设置字体大小为16headWriteCellStyle.setWriteFont(headWriteFont);headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());// 设置表头 和内容样式HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, (List<WriteCellStyle>) null);ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), targetClass).registerWriteHandler(new EasyExcelSheetWriteHandler(firstRowDocBool, firstRowContent)).registerWriteHandler(horizontalCellStyleStrategy).relativeHeadRowIndex(1).excelType(ExcelTypeEnum.XLSX).build();WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).build();excelWriter.write(dateList, writeSheet);excelWriter.finish();}
}

4）其中自定导入第一行导入内容依赖类

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;public class EasyExcelSheetWriteHandler implements SheetWriteHandler {/*** 首行是否为文档*/private final Boolean firstRowDocBool;/*** 填写文件说明*/private final String firstRowContent;public EasyExcelSheetWriteHandler(Boolean firstRowDocBool, String firstRowContent) {this.firstRowDocBool = firstRowDocBool;this.firstRowContent = firstRowContent;}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();//Sheet sheet = workbook.getSheetAt(0);Sheet sheet = workbook.getSheet(writeSheetHolder.getSheetName());Row row1 = sheet.getRow(0);if (row1 == null) {row1 = sheet.createRow(0);}row1.setHeight((short) 500);//25*20   实际行高*20Cell cell1 = row1.getCell(0);if (cell1 == null) {cell1 = row1.createCell(0);}cell1.setCellValue(firstRowContent);CellStyle cellStyle = workbook.createCellStyle();cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());cellStyle.setBorderTop(BorderStyle.NONE);cellStyle.setBorderBottom(BorderStyle.NONE);cellStyle.setBorderLeft(BorderStyle.NONE);cellStyle.setBorderRight(BorderStyle.NONE);if (Boolean.TRUE.equals(firstRowDocBool)) {cellStyle.setAlignment(HorizontalAlignment.LEFT);} else {cellStyle.setAlignment(HorizontalAlignment.CENTER);}Font font = workbook.createFont();font.setBold(false);if (Boolean.TRUE.equals(firstRowDocBool)) {font.setFontName("宋体");font.setFontHeight((short) 220);//11*20   实际字号（字高）*20} else {font.setFontHeight((short) 360);//18*20   实际字号（字高）*20font.setFontName("黑体");}cellStyle.setFont(font);cell1.setCellStyle(cellStyle);sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 15));}
}

2、easyExcel 导入逻辑

1）依赖于 fileutil，将 MultipartFile 转为file

File file = FileUtil.transferToFile(multipartFile);

2）拿到文件以后，导入实现逻辑如下

EasyExcel.read(file, App.class, new ReadListener<App>() {//临时存储数据对象private final List<App> cachedDataList = ListUtils.newArrayList();@Overridepublic void invoke(App data, AnalysisContext context) {cachedDataList.add(data);}@Transactional@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {StringBuffer errorCheckMsg = new StringBuffer();AtomicBoolean isPass = new AtomicBoolean(true);if (CollUtil.isEmpty(cachedDataList)) {throw new CommonException("导入Excel列表为空");}//数据行是从第三行开始AtomicInteger rowIndex = new AtomicInteger(3);cachedDataList.forEach((app) -> {//spring validator 验证每行数据是否合规BindingResult result = new BeanPropertyBindingResult(app, "app");validator.validate(app, result);if (result.hasErrors()) {isPass.set(false);// 处理验证错误，例如返回错误消息errorCheckMsg.append("第").append(rowIndex).append("行：");StringBuilder sb = new StringBuilder();for (ObjectError error : result.getAllErrors()) {sb.append(error.getDefaultMessage()).append("；");}errorCheckMsg.append(sb).append("\n");}rowIndex.getAndIncrement();});if (isPass.get()) {//mybatis 插入数据方法saveBatch(cachedDataList);}if (!StrUtil.isEmpty(errorCheckMsg.toString())) {throw new CommonException(errorCheckMsg.toString());}}}).sheet().headRowNumber(2)//设置标题行的行号.doRead();

3、easyExcel 自定义 list<map> 导出

1）导出工具类的使用

/*** 导出采购订单列表*/@PreAuthorize("@ss.hasPermi('mes:ec:purorder:export')")@Log(title = "采购订单", businessType = BusinessType.EXPORT)@PostMapping("acsEnvMonitHis/export")public void export(BaseEntity baseEntity, HttpServletResponse response) throws IOException {String[][] headMap = {{"AA"}, {"BB"}, {"CC"}, {"DD"}};String[] dataStrMap = {"CC", "EE"};int[] witdhMap = {15, 20};List<Map<String, Object>> listDatas = 获取数据的service方法NoModelWriteData nmwDate = new NoModelWriteData();nmwDate.setFileName("历史生产数据");nmwDate.setHeadMap(headMap);nmwDate.setDataStrMap(dataStrMap);nmwDate.setWitdhArray(witdhMap);nmwDate.setDataList(listDatas);EasyExcelUtils.noModleExportExcel(nmwDate, response);}

2）依赖工具类：EasyExcelUtils

package com.hy.common.utils.poi;import cn.hutool.core.util.ArrayUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.ConverterKeyBuild;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.util.IOUtils;
import org.springframework.web.bind.annotation.RequestBody;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;public class EasyExcelUtils {//不创建对象的导出public static void noModleExportExcel(@RequestBody NoModelWriteData data, HttpServletResponse response) throws IOException {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");OutputStream out = null;try {out = response.getOutputStream();// 这里需要设置不关闭流ExcelWriter excelWriter = EasyExcel.write(out).charset(StandardCharsets.UTF_8).build();SqlDateConverter converterSqlDate = new SqlDateConverter();excelWriter.writeContext().currentWriteHolder().converterMap().put(ConverterKeyBuild.buildKey(converterSqlDate.supportJavaTypeKey()), converterSqlDate);excelWriter.writeContext().currentWriteHolder().converterMap().put(ConverterKeyBuild.buildKey(converterSqlDate.supportJavaTypeKey(), converterSqlDate.supportExcelTypeKey()), converterSqlDate);ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.writerSheet();writerSheetBuilder.registerConverter(new SqlDateConverter());writerSheetBuilder.registerConverter(new SqlTimeConverter());if (ArrayUtil.isNotEmpty(data.getWitdhArray())) {writerSheetBuilder.registerWriteHandler(new ColumnWidthStyleStrategy(data.getWitdhArray()));}WriteSheet writeSheet = writerSheetBuilder.build();writeSheet.setHead(head(data.getHeadMap()));writeSheet.setSheetName(data.getFileName());excelWriter.write(dataList(data.getDataList(), data.getDataStrMap()), writeSheet);excelWriter.finish();} catch (Exception e) {throw e;} finally {IOUtils.closeQuietly(out);}}//创建对象的导出public <T> void simpleWrite(@RequestBody SimpleWriteData data, Class<T> clazz, HttpServletResponse response) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman//response.setContentType("application/vnd.ms-excel");response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode(data.getFileName(), "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), clazz).sheet(data.getFileName()).doWrite(data.getDataList());}//设置表头private static List<List<String>> head(String[][] headMap) {List<List<String>> list = new ArrayList<List<String>>();for (String[] headArray : headMap) {List<String> headList = new ArrayList<String>();for (String headStr : headArray) {headList.add(headStr);}list.add(headList);}return list;}//设置导出的数据内容private static List<List<Object>> dataList(List<Map<String, Object>> dataList, String[] dataStrMap) {List<List<Object>> list = new ArrayList<List<Object>>();for (Map<String, Object> map : dataList) {List<Object> data = new ArrayList<Object>();for (int i = 0; i < dataStrMap.length; i++) {data.add(map.get(dataStrMap[i]));}list.add(data);}return list;}
}

3）导出工具类、共依赖5个类，有不同作用，可根据实际情况删减

依赖3个类：2种参数，1个列宽策略

NoModelWriteData ：导出数据，类型为List<MAP>
SimpleWriteData：导出数据，类型为List<对象>
ColumnWidthStyleStrategy：导出的列样式宽度策略

其中解决显示异常的数据类型依赖于2个类

SqlDateConverter ：sqldate显示异常
SqlTimeConverter：sqltime显示异常

类1：NoModelWriteData ：导出的第1种参数

import java.io.Serializable;
import java.util.Arrays;
import java.util.List;
import java.util.Map;public class NoModelWriteData implements Serializable {/** 文件名 **/private String fileName;/** 表头数组 **/private String[][] headMap;/** 对应数据字段数组 **/private String[] dataStrMap;/** 列宽数组 **/private int[] witdhArray;/** 数据集合 **/private List<Map<String, Object>> dataList;public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName;}public String[][] getHeadMap() {return headMap;}public void setHeadMap(String[][] headMap) {this.headMap = headMap;}public String[] getDataStrMap() {return dataStrMap;}public void setDataStrMap(String[] dataStrMap) {this.dataStrMap = dataStrMap;}public int[] getWitdhArray() {return witdhArray;}public void setWitdhArray(int[] witdhArray) {this.witdhArray = witdhArray;}public List<Map<String, Object>> getDataList() {return dataList;}public void setDataList(List<Map<String, Object>> dataList) {this.dataList = dataList;}@Overridepublic String toString() {return "NoModelWriteData{" +"fileName='" + fileName + '\'' +", headMap=" + Arrays.toString(headMap) +", dataStrMap=" + Arrays.toString(dataStrMap) +", witdhArray=" + Arrays.toString(witdhArray) +", dataList=" + dataList +'}';}
}

类2：SimpleWriteData，导出的第2种参数

import java.io.Serializable;
import java.util.List;public class SimpleWriteData implements Serializable {/** 文件名 **/private String fileName;/** 数据列表 **/private List<?> dataList;public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName;}public List<?> getDataList() {return dataList;}public void setDataList(List<?> dataList) {this.dataList = dataList;}@Overridepublic String toString() {return "SimpleWriteData{" +"fileName='" + fileName + '\'' +", dataList=" + dataList +'}';}
}

类3：ColumnWidthStyleStrategy ：列宽度策略设置

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;import java.util.HashMap;
import java.util.Map;public class ColumnWidthStyleStrategy extends AbstractHeadColumnWidthStyleStrategy {private Map<Integer,Integer> columnWidth = new HashMap<>();public ColumnWidthStyleStrategy() {}public ColumnWidthStyleStrategy(int[] widthArray) {for (int i = 0; i < widthArray.length; i++) {columnWidth.put(i,widthArray[i]);}}@Overrideprotected Integer columnWidth(Head head, Integer columnIndex) {return columnWidth.getOrDefault(columnIndex,25);}
}

类四：SqlDateConverter：为了 sql.date 类型在excel中显示正常

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.math.BigDecimal;
import java.sql.Date;
import java.text.ParseException;/*** Date and string converter** @author Jiaju Zhuang*/
public class SqlDateConverter implements Converter<Date> {@Overridepublic Class<?> supportJavaTypeKey() {return Date.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic Date convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws ParseException {switch (cellData.getType()) {case NUMBER:BigDecimal numberValue = cellData.getNumberValue();return new Date(numberValue.longValue());case STRING:String stringValue = cellData.getStringValue();if (StrUtil.isBlank(stringValue) || "NA".equals(stringValue)) {return null;}try {return new Date(DateUtil.parse(cellData.getStringValue()).getTime());} catch (NumberFormatException e) {return null;}default:return null;}}@Overridepublic WriteCellData<?> convertToExcelData(Date value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {return new WriteCellData<>(DateUtil.formatDate(value));} else {return new WriteCellData<>(DateUtil.format(value, contentProperty.getDateTimeFormatProperty().getFormat()));}}
}

类五：SqlTimeConverter ：为了 sql.time 类型在excel中显示正常

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.math.BigDecimal;
import java.sql.Time;
import java.text.ParseException;/*** Date and string converter** @author Jiaju Zhuang*/
public class SqlTimeConverter implements Converter<Time> {@Overridepublic Class<?> supportJavaTypeKey() {return Time.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic Time convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws ParseException {switch (cellData.getType()) {case NUMBER:BigDecimal numberValue = cellData.getNumberValue();return new Time(numberValue.longValue());case STRING:String stringValue = cellData.getStringValue();if (StrUtil.isBlank(stringValue) || "NA".equals(stringValue)) {return null;}try {return new Time(DateUtil.parseTime(cellData.getStringValue()).getTime());} catch (NumberFormatException e) {return null;}default:return null;}}@Overridepublic WriteCellData<?> convertToExcelData(Time value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {return new WriteCellData<>(DateUtil.formatTime(value));} else {return new WriteCellData<>(DateUtil.format(value, contentProperty.getDateTimeFormatProperty().getFormat()));}}
}

4、你可能用到其他教程

1）poi 导出自定义样式excel

springboot、springmvc，excel上传解析、下载excel工具类_mediatype中适合excel-CSDN博客文章浏览阅读376次。工具类共7个方法： /** * 1）对外方法：解析上传的excel,只含一个sheet kly * @example: List strArrayList = ExcelUtil.getExcelData(MultipartFile); */ /** * 2）对外方法：获取第几个sheet页的数据..._mediatype中适合excel[]>https://blog.csdn.net/qq_26408545/article/details/103713665

2）poi 导出word

POI 导出横版A4word,并设置excel宽度(固定不变形)_a4 poi 宽度设置-CSDN博客文章浏览阅读2.6k次，点赞4次，收藏8次。1.maven依赖 org.apache.poipoi3.17 &_a4 poi 宽度设置https://blog.csdn.net/qq_26408545/article/details/110669104