目錄
Maven依賴
實體類
表頭列寬自適應處理器
行列凍結處理器
合并單元格處理器
工具類
Maven依賴
<!--easy excel--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency><!-- excel工具 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><!--lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.16.10</version><scope>provided</scope></dependency><!--工具集--><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.12</version></dependency>實體類
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;import java.io.Serializable;
import java.util.Date;/*** entity實體類** @Package_Name * @Author Leslie Lee* @TIME * @Version 1.0*/
@Data
@ExcelIgnoreUnannotated //忽略未標注的字段
public class EntityDemo implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty("姓名")private String name;//private String age;//@ExcelProperty("申請日期")@DateTimeFormat("yyyy-MM-dd")//時間格式private Date applicationDate;//申請日期}表頭列寬自適應處理器
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
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.Sheet;import java.util.List;/*** 表頭自動列寬** @Package_Name * @Author Leslie Lee* @TIME * @Version 1.0*/
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {// 統計setColumnWidth被調用多少次private static int count = 0;@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head,Integer relativeRowIndex, Boolean isHead) {// 簡單設置Sheet sheet = writeSheetHolder.getSheet();sheet.setColumnWidth(cell.getColumnIndex(), 5000);}
}
行列凍結處理器
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.Sheet;/*** excel導出，凍結列和行 處理器** @Package_Name* @Author Leslie Lee* @TIME* @Version 1.0*/
public class FreezeRowColHandler implements SheetWriteHandler {/*** colSplit:表示要凍結的列數；* rowSplit:表示要凍結的行數；* firstCellNum:表示被固定列右邊第一列的列號；* firstRollNum :表示被固定行下邊第一列的行號;*/private int colSplit = 0, rowSplit = 1, firstCellNum = 0, firstRollNum = 0;/* 空參構造，不傳值默認凍結一行 */public FreezeRowColHandler() {}public FreezeRowColHandler(int colSplit, int rowSplit, int firstCellNum, int firstRollNum) {this.colSplit = colSplit;this.rowSplit = rowSplit;this.firstCellNum = firstCellNum;this.firstRollNum = firstRollNum;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();sheet.createFreezePane(colSplit, rowSplit, firstCellNum, firstRollNum);}
}合并單元格處理器
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.lang.reflect.Field;
import java.util.List;/*** easyExcel合并單元格** @Package_Name* @Author Leslie Lee* @TIME* @Version 1.0*/
@Slf4j
public class ExcelCellMergeStrategy implements SheetWriteHandler {private List<CellRangeAddress> addressList;public ExcelCellMergeStrategy() {}public ExcelCellMergeStrategy(List<CellRangeAddress> addressList) {this.addressList = addressList;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/* 執行合并 */@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();//打印合并信息
// log.info("ExcelCellMergeStrategy|start|合并單元格|sheet名稱:{}|開始執行", sheet.getSheetName());for (CellRangeAddress address : addressList) {sheet.addMergedRegionUnsafe(address);}}/*** 合并單元格策略，根據首列相同,合并指定列數** @param mergeColumnIndexes 合并列數 前{x}列* @param tableHeaderRows 表頭行數* @param list 導出的數據* @param <T> 導出數據-實體類* @return 合并范圍*/public static <T> List<CellRangeAddress> getOneColAddressList(int mergeColumnIndexes, int tableHeaderRows, List<T> list) {List<CellRangeAddress> addressList = CollUtil.newArrayList();if (CollUtil.isNotEmpty(list)) {Class<?> aClass = list.get(0).getClass();Field f = null;Field[] fields = aClass.getDeclaredFields();for (Field field : fields) {if (field.isAnnotationPresent(ExcelProperty.class)) {f = field;break;}}if (f != null) {//list長度,最大行數int size = list.size();//防止下標越界使用int i2 = size - 2;for (int i = 0; i < size; i++) {//開始行 從 不合并標題int firstRow = i + tableHeaderRows;//結束行int lastRow = firstRow;//開始列,結束列//int firstCol,lastCol = 0;String value = StrUtil.toString(ReflectUtil.getFieldValue(list.get(i), f));boolean flag = true;while (flag) {//最后一行不再進行匹配,直接退出if (i > i2) {break;}String value1 = StrUtil.toString(ReflectUtil.getFieldValue(list.get(i + 1), f));//值相同則合并 -> 合并行數+1 下標+1遍歷跳過相同行if (StrUtil.equals(value1, value)) {i++;lastRow++;} else {flag = false;}if (i == size) {flag = false;}}if (lastRow != firstRow) {for (int i1 = 0; i1 < mergeColumnIndexes; i1++) {addressList.add(new CellRangeAddress(firstRow, lastRow, i1, i1));}}}}}return addressList;}
}工具類
import com.alibaba.excel.EasyExcel;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;/*** @Package_Name * @Author Leslie Lee* @TIME * @Version 1.0*/
public class EasyExcelUtils<T> {/*** 分片導出** @param response* @param list* @param sheetName* @throws IOException*/public static void downloadFragmentation2(HttpServletResponse response, List<?> list, String sheetName, int mergeColumnIndexs, int hdrRow) throws IOException {Class<?> clazz = list.get(0).getClass();response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// URLEncoder.encode 防止中文亂碼 和easyexcel沒有關系String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 定義每次寫入的數據量int batchSize = 2000;// 定義寫入的起始位置int start = 0;// 定義寫入的結束位置int end = 0;while (end < list.size()) {// 計算下一次寫入的結束位置end = Math.min(start + batchSize, list.size());// 獲取本次寫入的數據List<?> subList = list.subList(start, end);// 寫入數據EasyExcel.write(response.getOutputStream(), clazz)/* 列寬自適應 */.registerWriteHandler(new ExcelWidthStyleStrategy())/* 行列凍結 */.registerWriteHandler(new FreezeRowColHandler())/* 單元格合并 */.registerWriteHandler(new ExcelCellMergeStrategy(getOneColAddressList(mergeColumnIndexs,hdrRow,list))).sheet(sheetName).doWrite(list);// 更新起始位置start = end;}}
}
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Leslie Lee 隨筆
)
