使用EasyExcel实现通用导出功能

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
 */
@Slf4j
public class ExcelUtil {
        /**
	 * 导出excel
	 *
	 * @param response  响应类
	 * @param fileName  文件名
	 * @param sheetName sheet名
	 * @param dataList  数据列表
	 * @param clazz     class类
	 * @param <T>       泛型
	 */
	@SneakyThrows
	public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> 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 <D>          DTO类
	 * @param <V>          VO类
	 * @param <E>          导出类
	 */
	public static <D, V, E> void export(@NotNull D dto,
					    @NotNull HttpServletResponse resp,
					    @NotNull Class<E> exportClazz,
					    @Nullable String fileName,
					    @NotNull BiFunction<D, Query, ? extends IPage<V>> pageDataFunc,
					    @Nullable List<? extends WriteHandler> 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<WriteHandler> strategies = new ArrayList<>();
			if (ObjectUtil.isNotEmpty(strategyList)) {
				strategies.addAll(strategyList);
			} else {
				// 默认导出样式
				strategies.addAll(getDefaultStrategy());
			}

			List<List<String>> 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<V> 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<E> exportList = new ArrayList<>();
				query.setCurrent(i);
				dataPage = pageDataFunc.apply(dto, query);
				List<V> dataList = new ArrayList<>(dataPage.getRecords());
				exportList = BeanUtil.copyProperties(dataList, exportClazz);
				for (int j = 0; j < exportList.size(); j++) {
					E e = exportList.get(j);
					List<Field> fields = getField(e);
					Optional<Field> 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,导出列的顺序由<code>exportFields</code>的顺序决定
	 *
	 * @param dto          DTO(分页查询对象)
	 * @param resp         响应对象
	 * @param exportFields 需要导出的字段列表(有序map)
	 * @param fileName     文件名
	 * @param columnWidth  自定义列宽map,key为列下标,value为宽度,单位:1000=1cm
	 * @param pageDataFunc 分页查询方法(须将字典值转为中文,可调用wrapper方法)
	 * @param strategyList 写入策略集合
	 * @param <D>          DTO泛型
	 * @param <V>          VO泛型
	 */
	public static <D, V> void export(@NotNull D dto,
					 @NotNull HttpServletResponse resp,
					 @NotNull LinkedHashMap<String, String> exportFields,
					 @Nullable String fileName,
					 @NotNull Map<Integer, Integer> columnWidth,
					 @NotNull BiFunction<D, Query, ? extends IPage<V>> pageDataFunc,
					 @Nullable List<? extends WriteHandler> 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<WriteHandler> strategies = new ArrayList<>();
			if (ObjectUtil.isNotEmpty(strategyList)) {
				strategies.addAll(strategyList);
			} else {
				// 默认导出样式
				strategies.addAll(getDefaultStrategy());
			}

			List<List<String>> head = new ArrayList<>();
			List<List<String>> 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<V> 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<V> dataList = new ArrayList<>();
				List<List<Object>> 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<Object> exportMap = new ArrayList<>();
					for (List<String> s : content) {
						String str = s.get(0);
						List<Field> 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<WriteHandler> getDefaultStrategy() {
		List<WriteHandler> 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<Field> getField(Object o) {
		Class c = o.getClass();
		List<Field> 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<Integer, Integer>> CACHE = new HashMap<>();

	@Override
	protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
		boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
		if (needSetWidth) {
			Map<Integer, Integer> 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;
				// 默认一行高为20
				cell.getRow().setHeightInPoints(rows * 20);
			}
		}
	}

	/**
	 * 计算长度
	 *
	 * @param cellDataList
	 * @param cell
	 * @param isHead
	 * @return
	 */
	private Integer dataLength(List<CellData> 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;

}

	public void export(UserDTO userDTO, HttpServletResponse response) {
		List<User> userList = userService.getList(userDTO);
		String fileName = "导出数据_" + System.currentTimeMillis();
		ExcelUtil.export(response, fileName, "导出数据", userList, ExportDataDTO.class);
	}

	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);
	}

	public void export(UserDTO userDTO, HttpServletResponse response) {
		String fileName = "导出数据_" + System.currentTimeMillis();
                /* exportFields字段由用户在前端操作传入,字段顺序可自由调整
                以下是前端传入参数样例
                {            		
                    exportFields: 
                    [
			{rowNum: "序号"},
			{name: "姓名"},
			{age: "年龄"},
			{gender: "性别"}
		    ]
                }
                也可自定义为如下结构
		LinkedHashMap<String, String> exportFields = new LinkedHashMap<>();
		exportFields.put("subject","标题");
		exportFields.put("businessTypeName","业务类型");
		exportFields.put("instantLevel","紧急程度");
		exportFields.put("operator","承办人");
		exportFields.put("draftTime","拟稿时间");
		exportFields.put("sendOrgName","发文单位");

                自定义列宽示例
		LinkedHashMap<Integer, Integer> 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<String, String>> exportFields = userDTO.getExportFields();
		LinkedHashMap<String, String> exports = new LinkedHashMap<>();
		exportFields.forEach(exports::putAll);
		ExcelUtil.export(userDTO, resp, exports, fileName, 
                        // 此参数为自定义列宽时使用, 若传入null则启用自适应列宽
                        null,
			this::getPage, null);
	}