使用EasyExcel實作通用匯出功能-有解無憂

一、環境介紹

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
 */
@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 = https://www.cnblogs.com/HotBoom/archive/2023/05/19/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 = "https://www.cnblogs.com/HotBoom/archive/2023/05/19/當前頁")
	private Integer current;

	/**
	 * 每頁的數量
	 */
	@ApiModelProperty(value = "https://www.cnblogs.com/HotBoom/archive/2023/05/19/每頁的數量")
	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<User> 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<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);
	}

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/552926.html

標籤：其他

上一篇：SpringBoot實作WebSocket發送接收訊息 + Vue實作SocketJs接收發送訊息

下一篇：返回列表