一. 不多废话，直接开始整合
pom.xml 中引入 easyexcel、lombok，idea记得安装lombok插件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easy.excel.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
二. 建一个实体类，导入导出需要用到
@Data
public class ExportModel {
@ExcelProperty(value = "姓名" ,index = 0)
private String name;
@ExcelProperty(value = "性别" ,index = 1)
private String sex;
@ExcelProperty(value = "年龄" ,index = 2)
private Integer age;
}
三.引入封装好的工具类
package utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
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 org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/**
*
* @Author zzx
* @Description Excel读写工具类
* @Date 2020-01-16
*/
public class ExcelUtil {
private ExcelUtil(){}
/**
* 读取Excel（一个sheet）
* @param excel 文件
* @param clazz 实体类
* @param sheetNo sheet序号
* @return 返回实体列表(需转换)
*/
public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz,int sheetNo) {
ExcelListener excelListener = new ExcelListener();
ExcelReader excelReader = getReader(excel,clazz,excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
excelReader.read(readSheet);
excelReader.finish();
return BeanConvert.objectConvertBean(excelListener.getDataList(), clazz);
}
/**
* 读取Excel（多个sheet可以用同一个实体类解析）
* @param excel 文件
* @param clazz 实体类
* @return 返回实体列表(需转换)
*/
public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz) {
ExcelListener excelListener = new ExcelListener();
ExcelReader excelReader = getReader(excel,clazz,excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet:readSheetList){
excelReader.read(readSheet);
}
excelReader.finish();
return BeanConvert.objectConvertBean(excelListener.getDataList(), clazz);
}
/**
* 导出Excel(一个sheet)
*
* @param response HttpServletResponse
* @param list 数据list
* @param fileName 导出的文件名
* @param sheetName 导入文件的sheet名
* @param clazz 实体类
*/
public static <T> void writeExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz) {
OutputStream outputStream = getOutputStream(response, fileName);
ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
}
/**
* 导出Excel(带样式)
*
* @return
*/
public static <T> void writeStyleExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz) {
//表头策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//背景浅灰
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)20);
headWriteCellStyle.setWriteFont(headWriteFont);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 否则无法显示背景颜色；头默认了FillPatternType
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//背景浅绿
contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
WriteFont contentWriteFont = new WriteFont();
//字体大小
contentWriteFont.setFontHeightInPoints((short)15);
contentWriteCellStyle.setWriteFont(contentWriteFont);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
OutputStream outputStream = getOutputStream(response, fileName);
EasyExcel.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet(sheetName).doWrite(list);
}
/**
* 导出Excel(动态表头)
* write时不传入class,table时传入并设置needHead为false
* @return
*/
public static <T> void writeDynamicHeadExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz,List<List<String>> headList) {
OutputStream outputStream = getOutputStream(response, fileName);
EasyExcel.write(outputStream)
.head(headList)
.sheet(sheetName)
.table().head(clazz).needHead(Boolean.FALSE)
.doWrite(list);
}
/**
* 导出时生成OutputStream
*/
private static OutputStream getOutputStream(HttpServletResponse response,String fileName) {
//创建本地文件
String filePath = fileName + ".xlsx";
File file = new File(filePath);
try {
if (!file.exists() || file.isDirectory()) {
file.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 返回ExcelReader
* @param excel 文件
* @param clazz 实体类
* @param excelListener
*/
private static <T> ExcelReader getReader(MultipartFile excel, Class<T> clazz, ExcelListener excelListener) {
String filename = excel.getOriginalFilename();
try {
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
InputStream inputStream = new BufferedInputStream(excel.getInputStream());
ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
inputStream.close();
return excelReader;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
四，开始调用工具类进行导出
package com.example.user.controller;
import model.ExportModel;
import model.ImportModel;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import utils.ExcelUtil;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName ExcelTestController
* @Description
* @Author 朱正新
* @Version 1.0.0
* @Date 2021/4/1 21:25
*/
@RestController
@RequestMapping(value = "/easyExcel")
public class ExcelTestController {
@PostMapping(value = "/import")
public List<ImportModel> read(MultipartFile excel) {
return ExcelUtil.readExcel(excel, ImportModel.class, 0);
}
@GetMapping(value = "/export")
public void writeExcel(HttpServletResponse response) {
List<ExportModel> list = getList();
String fileName = "Excel导出测试";
String sheetName = "sheet1";
ExcelUtil.writeDynamicHeadExcel(response, list, fileName, sheetName, ExportModel.class, head());
}
@GetMapping(value = "/export2")
public void writeExcel2(HttpServletResponse response) {
List<ExportModel> list = getList();
String fileName = "Excel导出测试2";
String sheetName = "sheet1";
ExcelUtil.writeExcel(response, list, fileName, sheetName, ExportModel.class);
}
private List<ExportModel> getList() {
List<ExportModel> modelList = new ArrayList<>();
ExportModel firstModel = new ExportModel();
firstModel.setName("李明");
firstModel.setSex("男");
firstModel.setAge(20);
modelList.add(firstModel);
ExportModel secondModel = new ExportModel();
secondModel.setName("珍妮");
secondModel.setSex("女");
secondModel.setAge(19);
modelList.add(secondModel);
return modelList;
}
private List<List<String>> head() {
List<List<String>> headList = new ArrayList<>();
List<String> nameHead = new ArrayList<>();
nameHead.add("姓名");
List<String> genderHead = new ArrayList<>();
genderHead.add("性别");
List<String> ageHead = new ArrayList<>();
ageHead.add("年龄");
headList.add(nameHead);
headList.add(genderHead);
headList.add(ageHead);
return headList;
}
}
