原文網址：EasyExcel--導入和導出Excel的方法_IT利刃出鞘的博客-CSDN博客

簡介

本文介紹SpringBoot整合EasyExcel導入和導出Excel的方法。

使用

Excel導入

實體類

@Data
public class OrderImportBO {@ExcelProperty("訂單號")@NotBlank(message = "訂單號不能為空")private String scOrderPoolId;@ExcelProperty("金額")private String amount;
}

Controller

@PostMapping("importOrder")
public void importOrder(@RequestPart MultipartFile file) {List<OrderImportBO> orderImportBOList = ExcelUtil.importExcel(file, OrderImportBO.class);
}

Excel導出

?實體類

@Data
public class OrderExportBO {@ExcelProperty("訂單號")@NotBlank(message = "訂單號不能為空")private String scOrderPoolId;@ExcelProperty("金額")private String amount;
}

Service

List<OrderExportVO> exportVOS = new ArrayList();
ExcelUtil.exportExcel(exportVOS, OrderExportVO.class);

詳細代碼

依賴

EasyExcel

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.11</version>
</dependency>

整個pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.3.0.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.knife</groupId><artifactId>demo_EasyExcel_SpringBoot</artifactId><version>0.0.1-SNAPSHOT</version><name>demo_EasyExcel_SpringBoot</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.11</version></dependency><dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spring-boot-starter</artifactId><version>3.0.3</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><scope>provided</scope></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><version>2.3.0.RELEASE</version></plugin></plugins></build></project>

Excel配置

字符串轉換器

package com.knife.excel.handler;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;public class StringConverter implements Converter<String> {@Overridepublic Class supportJavaTypeKey() {return String.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}/*** 將excel對象轉成Java對象，這里讀的時候會調用*/@Overridepublic String convertToJavaData(CellData cellData, 
ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return cellData.getStringValue().trim();}/*** 將Java對象轉成String對象，寫出的時候調用*/@Overridepublic CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData(value);}
}

writeHandler

package com.knife.excel.halper;import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
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.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class ExcelHandler extends AbstractColumnWidthStyleStrategy {private static final int MAX_COLUMN_WIDTH = 255;//因為在自動列寬的過程中，有些設置地方讓列寬顯得緊湊，所以做出了個判斷private static final int COLUMN_WIDTH = 20;private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);public ExcelHandler() {}@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap(16);CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > MAX_COLUMN_WIDTH) {columnWidth = MAX_COLUMN_WIDTH;}else {if(columnWidth<COLUMN_WIDTH){columnWidth =columnWidth*2;}}Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(),  columnWidth* 256);}}}}private  Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = (CellData)cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch(type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}public static HorizontalCellStyleStrategy getStyleStrategy(){// 頭的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景設置為灰色headWriteCellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short)11);// 字體樣式headWriteFont.setFontName("Arial Unicode MS");headWriteFont.setColor(IndexedColors.WHITE.getIndex());headWriteCellStyle.setWriteFont(headWriteFont);//自動換行headWriteCellStyle.setWrapped(false);// 水平對齊方式headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 垂直對齊方式headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 內容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 這里需要指定 FillPatternType 為FillPatternType.SOLID_FOREGROUND 不然無法顯示背景顏色.頭默認了 FillPatternType所以可以不指定// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);// 背景白色contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());WriteFont contentWriteFont = new WriteFont();// 字體大小contentWriteFont.setFontHeightInPoints((short)11);// 字體樣式contentWriteFont.setFontName("宋體");contentWriteCellStyle.setWriteFont(contentWriteFont);//是否換行contentWriteCellStyle.setWrapped(false);//  垂直對齊方式contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 這個策略是 頭是頭的樣式 內容是內容的樣式 其他的策略可以自己實現return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}
}

數據校驗

package com.bondex.oms.excel;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.knife.util.BeanHelper;
import com.knife.util.ValidateUtil;public class ExcelValidateListener<T> extends AnalysisEventListener<T> {@Overridepublic void invoke(T bo, AnalysisContext analysisContext) {// 如果是空行，不處理if (BeanHelper.allFieldAreNull(bo)) {return;}int row = analysisContext.readRowHolder().getRowIndex() + 1;try {// 校驗輸入字段ValidateUtil.validate(bo);} catch (Exception e) {throw new RuntimeException("第[" + row + "]行數據校驗失敗:" + e.getMessage());}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}
}

?Bean工具

package com.knife.util;import org.springframework.beans.BeanUtils;
import org.springframework.cglib.core.ReflectUtils;
import org.springframework.util.CollectionUtils;import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;public class BeanHelper {public static <T> List<T> convert(List<?> sources, Class<T> target) {if (CollectionUtils.isEmpty(sources)) {return new ArrayList<>();}List<T> targets = new LinkedList<>();for (Object source : sources) {T t = null;try {t = target.newInstance();} catch (Exception e) {throw new RuntimeException(e);}BeanUtils.copyProperties(source, t);targets.add(t);}return targets;}public static <T> T convert(Object source, Class<T> target) {if (source == null) {return null;}T t;try {t = target.newInstance();} catch (Exception e) {throw new RuntimeException(e);}BeanUtils.copyProperties(source, t);return t;}public static boolean allFieldAreNull(Object o) {Class<?> aClass = o.getClass();PropertyDescriptor[] beanProperties = ReflectUtils.getBeanProperties(aClass);for (PropertyDescriptor beanProperty : beanProperties) {Method readMethod = beanProperty.getReadMethod();try {Object value = readMethod.invoke(o);if (value != null) {return false;}} catch (IllegalAccessException | InvocationTargetException e) {throw new RuntimeException(e);}}return true;}
}

Excel工具類

package com.bondex.oms.util;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.knife.entity.UserDTO;
import com.knife.util.UserDtoUtils;
import com.knife.excel.helper.ExcelHandler;
import com.knife.excel.helper.ExcelValidateListener;
import com.knife.excel.helper.HeadRowListener;
import com.knife.excel.helper.StringConverter;
import org.springframework.util.Assert;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;public class ExcelUtil {public static <T> void exportExcel(List<T> dataList,Class<T> tClass) {ServletRequestAttributes servletRequestAttributes =(ServletRequestAttributes)RequestContextHolder.getRequestAttributes();Assert.notNull(servletRequestAttributes, "RequestAttributes不能為null");HttpServletResponse response = servletRequestAttributes.getResponse();Assert.notNull(response, "Response不能為null");ServletOutputStream out = null;try {out = response.getOutputStream();} catch (IOException e) {throw new RuntimeException(e);}String fileName = "測試文件.xlsx";try {// 必須要轉一下，否則中文文件名會亂碼fileName = URLEncoder.encode(fileName, "UTF-8");} catch (UnsupportedEncodingException e) {throw new RuntimeException(e);}// 通知瀏覽器以附件的形式下載處理，設置返回頭要注意文件名有中文response.setHeader("Content-disposition", "attachment;filename=" + fileName);response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");// 輸出流到瀏覽器下載EasyExcel.write(out).needHead(true).head(tClass).excelType(ExcelTypeEnum.XLSX).autoCloseStream(true).registerWriteHandler(new ExcelHandler()).registerWriteHandler(ExcelHandler.getStyleStrategy()).sheet(0, "Sheet1").doWrite(dataList);}public static <T> List<T> importExcel(MultipartFile file, Class<T> tClass) {return importExcel(file, tClass, 1, null);}public static <T> List<T> importExcel(MultipartFile file, Class<T> tClass, String sheet) {return importExcel(file, tClass, 1, sheet);}public static <T> List<T> importExcel(MultipartFile file,Class<T> tClass,Integer headRowNumber,String sheet) {InputStream inputStream;try {inputStream = file.getInputStream();} catch (IOException e) {throw new RuntimeException(e);}List<T> list = null;list = EasyExcel.read(inputStream).registerConverter(new StringConverter()).registerReadListener(new ExcelValidateListener<T>()).head(tClass)// 可以指定sheet名字，不指定或null則表示第1個.sheet(sheet).headRowNumber(headRowNumber).doReadSync();list.removeIf(BeanHelper::allFieldAreNull);return list;}
}