一、环境介绍

  • JDK 1.8+
  • EasyExcel 2.2.7

二、功能实现

此功能可以实现根据传入自定义的 导出实体类或Map 进行excel文件导出。若根据Map导出，导出列的顺序可以自定义。
话不多说,直接看代码

导出实体类

点击查看代码
import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.format.DateTimeFormat;import com.alibaba.excel.annotation.write.style.*;import com.*.core.tool.utils.DateUtil;import lombok.Data;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.HorizontalAlignment;import java.time.LocalDateTime;/** * excel导出对象实体类 * * @author 热得快炸了 * @since 2023-4-3 */@Data@HeadStyle(borderBottom = BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,borderTop = BorderStyle.THIN)@ContentStyle(borderBottom = BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,borderTop = BorderStyle.THIN,wrapped = true,horizontalAlignment = HorizontalAlignment.LEFT)@HeadFontStyle(fontHeightInPoints = (short) 16)@ContentFontStyle(fontHeightInPoints = (short) 14)public class ExportDataDTO {private static final long serialVersionUID = 1L;/** * 序号 */@ColumnWidth(8)@ExcelProperty({"文件登记簿", "序号"})private Integer rowNum;/** * 标题 */@ColumnWidth(50)@ExcelProperty({"文件登记簿", "姓名"})private String name;/** * 业务类型 */@ColumnWidth(20)@ExcelProperty({"文件登记簿", "年龄"})private String age;/** * 业务类型 */@ColumnWidth(18)@ExcelProperty({"文件登记簿", "性别"})private String gender;}

导出工具类

点击查看代码
import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.converters.integer.IntegerNumberConverter;import com.alibaba.excel.read.builder.ExcelReaderBuilder;import com.alibaba.excel.read.listener.ReadListener;import com.alibaba.excel.util.DateUtils;import com.alibaba.excel.write.builder.ExcelWriterBuilder;import com.alibaba.excel.write.handler.WriteHandler;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 com.baomidou.mybatisplus.core.metadata.IPage;import com.*.core.excel.converter.BaseDateConverter;import com.*.core.excel.listener.DataListener;import com.*.core.excel.listener.ImportListener;import com.*.core.excel.strategy.AdjustColumnWidthToFitStrategy;import com.*.core.excel.support.ExcelException;import com.*.core.excel.support.ExcelImporter;import com.*.core.mp.support.Query;import com.*.core.tool.utils.*;import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import org.apache.commons.codec.Charsets;import org.springframework.util.StringUtils;import org.springframework.web.multipart.MultipartFile;import javax.annotation.Nullable;import javax.servlet.http.HttpServletResponse;import javax.validation.constraints.NotNull;import java.io.*;import java.lang.reflect.Field;import java.net.URLEncoder;import java.util.*;import java.util.function.BiFunction;/** * Excel工具类 * * @author Chill * @apiNote https://www.yuque.com/easyexcel/doc/easyexcel */@Slf4jpublic class ExcelUtil {        /** * 导出excel * * @param response  响应类 * @param fileName  文件名 * @param sheetName sheet名 * @param dataList  数据列表 * @param clazz     class类 * @param        泛型 */@SneakyThrowspublic static  void export(HttpServletResponse response, String fileName, String sheetName, List dataList, Class clazz) {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding(Charsets.UTF_8.name());fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);}/** * 根据分页查询导出excel,根据exportClazz类导出 * * @param dto          DTO(分页查询对象) * @param resp         响应对象 * @param exportClazz  需要导出的类 * @param fileName     文件名 * @param pageDataFunc 分页查询方法(须将字典值转为中文,可调用wrapper方法) * @param strategyList 写入策略集合 * @param           DTO类 * @param           VO类 * @param           导出类 */public static  void export(@NotNull D dto,    @NotNull HttpServletResponse resp,    @NotNull Class exportClazz,    @Nullable String fileName,    @NotNull BiFunction<D, Query, ? extends IPage> pageDataFunc,    @Nullable List strategyList) {log.info("==================开始导出excel==================");fileName = fileName + ".xlsx";String filePath = FileUtil.getTempDirPath() + fileName;InputStream in = null;OutputStream outp = null;File file = new File(filePath);try {if (!file.getParentFile().exists()) {file.getParentFile().mkdirs();}if (!file.exists()) {file.createNewFile();}// 构造表格样式List strategies = new ArrayList();if (ObjectUtil.isNotEmpty(strategyList)) {strategies.addAll(strategyList);} else {// 默认导出样式strategies.addAll(getDefaultStrategy());}List<List> content = new ArrayList();// 构建excel写入对象ExcelWriterBuilder writerBuilder = EasyExcel.write(file, exportClazz);// 注册写入策略strategies.forEach(writerBuilder::registerWriteHandler);// 注册对象转换器writerBuilder.registerConverter(new BaseDateConverter.LocalDateTimeConverter());writerBuilder.registerConverter(new BaseDateConverter.LocalDateConverter());writerBuilder.registerConverter(new BaseDateConverter.LocalTimeConverter());writerBuilder.registerConverter(new IntegerNumberConverter());ExcelWriter excelWriter = writerBuilder.build();// 这里注意 如果同一个sheet只要创建一次WriteSheet writeSheet = EasyExcel.writerSheet(DateUtil.format(DateUtil.now(), DateUtil.PATTERN_DATETIME_MINI)).build();// 分页查询数据Query query = new Query();query.setSize(500);  //mybatis-plus最大分页500条query.setCurrent(0);IPage dataPage = pageDataFunc.apply(dto, query);long total = dataPage.getTotal();if (total > 50000) {throw new ExcelException("数据量过大,请按条件筛选导出");} else if (total <= 0) {throw new ExcelException("没有可以导出的数据");}long totalPage = (long) (Math.ceil(((double) total / dataPage.getSize())));for (int i = 1; i <= totalPage; i++) {List exportList = new ArrayList();query.setCurrent(i);dataPage = pageDataFunc.apply(dto, query);List dataList = new ArrayList(dataPage.getRecords());exportList = BeanUtil.copyProperties(dataList, exportClazz);for (int j = 0; j < exportList.size(); j++) {E e = exportList.get(j);List fields = getField(e);Optional rowNumField = fields.stream().filter(field -> field.getName().equalsIgnoreCase("rowNum")).findFirst();int rowNum = query.getSize() * (i - 1) + (j + 1);rowNumField.ifPresent(field -> {field.setAccessible(true);try {field.set(e, rowNum);} catch (IllegalAccessException illegalAccessException) {illegalAccessException.printStackTrace();}});}excelWriter.write(exportList, writeSheet);}// 千万别忘记finish 会帮忙关闭流excelWriter.finish();in = new FileInputStream(filePath);outp = resp.getOutputStream();//设置请求以及响应的内容类型以及编码方式resp.setContentType("application/vnd.ms-excel;charset=UTF-8");resp.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));outp = resp.getOutputStream();//获取文件输入流byte[] b = new byte[1024];int i = 0;//将缓冲区的数据输出到客户浏览器while ((i = in.read(b)) > 0) {outp.write(b, 0, i);}outp.flush();log.info("============导出成功辣!!!!!!!!===========");} catch (IOException e) {e.printStackTrace();log.error("============导出失败===========,异常信息:{}", e.getMessage());} finally {IoUtil.closeQuietly(in);IoUtil.closeQuietly(outp);FileUtil.deleteQuietly(file);}}/** * 根据分页查询导出excel,导出列的顺序由exportFields的顺序决定 * * @param dto          DTO(分页查询对象) * @param resp         响应对象 * @param exportFields 需要导出的字段列表(有序map) * @param fileName     文件名 * @param columnWidth  自定义列宽map,key为列下标,value为宽度,单位:1000=1cm * @param pageDataFunc 分页查询方法(须将字典值转为中文,可调用wrapper方法) * @param strategyList 写入策略集合 * @param           DTO泛型 * @param           VO泛型 */public static  void export(@NotNull D dto, @NotNull HttpServletResponse resp, @NotNull LinkedHashMap exportFields, @Nullable String fileName, @NotNull Map columnWidth, @NotNull BiFunction<D, Query, ? extends IPage> pageDataFunc, @Nullable List strategyList) {log.info("==================开始导出excel==================");fileName = fileName + ".xlsx";String filePath = FileUtil.getTempDirPath() + fileName;InputStream in = null;OutputStream outp = null;File file = new File(filePath);try {if (!file.getParentFile().exists()) {file.getParentFile().mkdirs();}if (!file.exists()) {file.createNewFile();}// 构造表格样式List strategies = new ArrayList();if (ObjectUtil.isNotEmpty(strategyList)) {strategies.addAll(strategyList);} else {// 默认导出样式strategies.addAll(getDefaultStrategy());}List<List> head = new ArrayList();List<List> content = new ArrayList();exportFields.forEach((key, value) -> head.add(Collections.singletonList(value)));exportFields.forEach((key, value) -> content.add(Collections.singletonList(key)));// 构建excel写入对象ExcelWriterBuilder writerBuilder = EasyExcel.write(file).head(head);// 注册写入策略strategies.forEach(writerBuilder::registerWriteHandler);// 注册对象转换器writerBuilder.registerConverter(new BaseDateConverter.LocalDateTimeConverter());writerBuilder.registerConverter(new BaseDateConverter.LocalDateConverter());writerBuilder.registerConverter(new BaseDateConverter.LocalTimeConverter());writerBuilder.registerConverter(new BaseDateConverter.IntegerConverter());ExcelWriter excelWriter = writerBuilder.build();// 这里注意 如果同一个sheet只要创建一次WriteSheet writeSheet = EasyExcel.writerSheet(DateUtil.format(DateUtil.now(), DateUtil.PATTERN_DATETIME_MINI)).build();writeSheet.setColumnWidthMap(ObjectUtil.isNotEmpty(columnWidth) ? columnWidth : null);// 分页查询数据Query query = new Query();query.setSize(500);  //mybatis-plus最大分页500条query.setCurrent(0);IPage dataPage = pageDataFunc.apply(dto, query);long total = dataPage.getTotal();if (total > 50000) {throw new ExcelException("数据量过大,请按条件筛选导出");} else if (total <= 0) {throw new ExcelException("没有可以导出的数据");}long totalPage = (long) (Math.ceil(((double) total / dataPage.getSize())));for (int i = 1; i <= totalPage; i++) {List dataList = new ArrayList();List<List> exportList = new ArrayList();query.setCurrent(i);dataPage = pageDataFunc.apply(dto, query);dataList.addAll(dataPage.getRecords());for (int j = 0; j < dataList.size(); j++) {V dataVO = dataList.get(j);List exportMap = new ArrayList();for (List s : content) {String str = s.get(0);List fieldList = getField(dataVO);Field field = fieldList.stream().filter(o -> o.getName().equalsIgnoreCase(str)).findFirst().orElseThrow(() -> new RuntimeException(StringUtil.format("找不到字段:{}", str)));field.setAccessible(true);exportMap.add(Optional.ofNullable(field.get(dataVO)).orElse(""));}exportList.add(exportMap);}excelWriter.write(exportList, writeSheet);}// 千万别忘记finish 会帮忙关闭流excelWriter.finish();in = new FileInputStream(filePath);outp = resp.getOutputStream();//设置请求以及响应的内容类型以及编码方式resp.setContentType("application/vnd.ms-excel;charset=UTF-8");resp.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));outp = resp.getOutputStream();//获取文件输入流byte[] b = new byte[1024];int i = 0;//将缓冲区的数据输出到客户浏览器while ((i = in.read(b)) > 0) {outp.write(b, 0, i);}outp.flush();log.info("============导出成功辣!!!!!!!!===========");} catch (IOException | IllegalAccessException e) {e.printStackTrace();log.error("============导出失败===========,异常信息:{}", e.getMessage());} finally {IoUtil.closeQuietly(in);IoUtil.closeQuietly(outp);FileUtil.deleteQuietly(file);}}/** * 默认导出样式 * * @return */private static List getDefaultStrategy() {List writeHandlers = new ArrayList();/* 默认样式 */// 头的策略WriteCellStyle headStyle = new WriteCellStyle();WriteFont headFont = new WriteFont();headFont.setFontHeightInPoints((short) 12);headStyle.setWriteFont(headFont);// 内容的策略WriteCellStyle contentStyle = new WriteCellStyle();WriteFont contentFont = new WriteFont();contentFont.setFontHeightInPoints((short) 12);contentStyle.setWriteFont(contentFont);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);writeHandlers.add(horizontalCellStyleStrategy);/* 列宽自适应 */writeHandlers.add(new AdjustColumnWidthToFitStrategy());return writeHandlers;}/** * 获取对象所有字段(包括父类) * * @param o * @return */private static List getField(Object o) {Class c = o.getClass();List fieldList = new ArrayList();while (c != null) {fieldList.addAll(new ArrayList(Arrays.asList(c.getDeclaredFields())));c = c.getSuperclass();}return fieldList;}}
列宽自适应策略类
 
 点击查看代码
import com.alibaba.excel.enums.CellDataTypeEnum;import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Sheet;import org.springframework.util.CollectionUtils;import java.util.HashMap;import java.util.List;import java.util.Map;/** * @Description EasyExcel列宽自适应策略类 * @date: 2023-5-17 10:06 * @author: 热得快炸了 */public class AdjustColumnWidthToFitStrategy extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map> CACHE = new HashMap();@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap();CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > 254) {columnWidth = 254;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);Sheet sheet = writeSheetHolder.getSheet();sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 200);}//设置单元格类型cell.setCellType(CellType.STRING);// 数据总长度int length = cell.getStringCellValue().length();// 换行数int rows = cell.getStringCellValue().split("\n").length;// 默认一行高为20cell.getRow().setHeightInPoints(rows * 20);}}}/** * 计算长度 * * @param cellDataList * @param cell * @param isHead * @return */private Integer dataLength(List cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:// 换行符（数据需要提前解析好）int index = cellData.getStringValue().indexOf("\n");return index != -1 ?cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}}
分页查询工具类
 
 点击查看代码
package com.*.core.mp.support;import io.swagger.annotations.ApiModel;import io.swagger.annotations.ApiModelProperty;import lombok.Data;import lombok.experimental.Accessors;/** * 分页工具 * * @author 热得快炸了 */@Data@Accessors(chain = true)@ApiModel(description = "查询条件")public class Query {/** * 当前页 */@ApiModelProperty(value = "当前页")private Integer current;/** * 每页的数量 */@ApiModelProperty(value = "每页的数量")private Integer size;/** * 正排序规则 */@ApiModelProperty(hidden = true)private String ascs;/** * 倒排序规则 */@ApiModelProperty(hidden = true)private String descs;}
三、如何使用1、简单导出excel（需要定义导出实体类）
 
 点击查看代码
public void export(UserDTO userDTO, HttpServletResponse response) {List userList = userService.getList(userDTO);String fileName = "导出数据_" + System.currentTimeMillis();ExcelUtil.export(response, fileName, "导出数据", userList, ExportDataDTO.class);}
2、根据分页查询导出excel（需要定义导出实体类）
 
 点击查看代码
public void export(UserDTO userDTO, HttpServletResponse response) {String fileName = "导出数据_" + System.currentTimeMillis();ExcelUtil.export(userDTO, response, ExportDataDTO.class, fileName,// 将分页查询方法作为参数传入(dto, query) -> getPage(query, dto),                         // 此处可自定义excel写入策略                        null);}
3、根据分页查询导出excel,导出列顺序可调整（不需要定义导出实体类）
 
 点击查看代码
public void export(UserDTO userDTO, HttpServletResponse response) {String fileName = "导出数据_" + System.currentTimeMillis();                /* exportFields字段由用户在前端操作传入,字段顺序可自由调整                以下是前端传入参数样例                {                                exportFields:                     [{rowNum: "序号"},{name: "姓名"},{age: "年龄"},{gender: "性别"}    ]                }                也可自定义为如下结构LinkedHashMap exportFields = new LinkedHashMap();exportFields.put("subject","标题");exportFields.put("businessTypeName","业务类型");exportFields.put("instantLevel","紧急程度");exportFields.put("operator","承办人");exportFields.put("draftTime","拟稿时间");exportFields.put("sendOrgName","发文单位");                自定义列宽示例LinkedHashMap columnWidth = new LinkedHashMap();columnWidth.put(0, 20 * 1000);columnWidth.put(1, 8 * 1000);columnWidth.put(2, 5 * 1000);columnWidth.put(3, 8 * 1000);columnWidth.put(4, 8 * 1000);columnWidth.put(5, 10 * 1000);*/List<Map> exportFields = userDTO.getExportFields();LinkedHashMap exports = new LinkedHashMap();exportFields.forEach(exports::putAll);ExcelUtil.export(userDTO, resp, exports, fileName,                         // 此参数为自定义列宽时使用, 若传入null则启用自适应列宽                        null,this::getPage, null);}
相关文章
Copyright ©  maxssl.com 版权所有 浙ICP备2022011180号