EasyExcel导入和导出excel数据表格用法
2021-09-03 本文已影响0人
拄杖忙学轻声码
说明：平时项目中经常有需要把excel表格中的数据导入库中，或者需要把查询出来的数据导出到excel文件中，今天就来介绍阿里的 easyexcel 的用法
一、SpringBoot集成EasyExcel，引入如下依赖
<!-- alibaba easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
二、编写 EasyExcelUtil 工具类：
1、创建解析监听类，读取 excel 表格中的数据，会通过监听类处理读取到的数据进行持久化或者存储到集合中以供后续操作使用，监听类、读取数据方法、写入数据方法代码如下：
package com.hkl.utils;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* <p>ClassName：EasyExcel工具类</p >
* <p>Description：</p >
* <p>Date：2021/9/1</p >
*/
@Slf4j
public class EasyExcelUtil {
//-------------------------------------------------------------- 读取文件解析监听类 start ----------------------------------------------------
/**
* <p>ClassName：ExcelListener</p >
* <p>Description：读取文件解析监听类，此类供外部实例化使用需要设置为静态类</p >
* <p>Date：2021/9/2</p >
*/
public static class ExcelListener<T> extends AnalysisEventListener<T>{
/**
* <p>存放读取后的数据</p >
* @date 2021/9/2 0:10
*/
public List<T> datas = new ArrayList<>();
/**
* <p>读取数据，一条一条读取</p >
* @date 2021/9/2 0:15
*/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
datas.add(t);
}
/**
* <p>解析完毕之后执行</p >
* @date 2021/9/2 0:06
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("读取数据条数：{}条！", datas.size());
}
public List<T> getDatas(){
return this.datas;
}
}
//-------------------------------------------------------------- 读取文件解析监听类 end ----------------------------------------------------
//-------------------------------------------------------------- 导出excel表格，设置自适应列宽配置类 start ----------------------------------------------------
/**
* <p>ClassName：Custemhandler</p >
* <p>Description：设置自适应列宽配置类</p >
* <p>Date：2021/10/14</p >
*/
public static class Custemhandler 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);
@Override
protected 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;
}
}
}
}
}
//-------------------------------------------------------------- 导出excel表格，设置自适应列宽配置类 end -----------------------------------------------------
/**
* <p> 读取Excel文件返回数据集合，不包含表头，默认读取第一个sheet数据 </p >
* @date 2021/9/2 0:17
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
*/
public static <T> List<T> readExcel(InputStream inputStream, Class<T> tClass, ExcelListener<T> excelListener){
if(ObjectUtil.isNull(inputStream) || ObjectUtil.isNull(tClass) || ObjectUtil.isNull(excelListener)){
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
read.sheet().doRead();
return excelListener.getDatas();
}
/**
* <p> 读取Excel文件返回数据集合，不包含表头，读取第x个sheet数据，不设置sheet就读取全部 </p >
* @date 2021/9/2 0:17
* @param inputStream 输入流
* @param tClass 数据映射类
* @param excelListener 读取监听类
* @return List 结果集
*/
public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> tClass, ExcelListener<T> excelListener){
if(ObjectUtil.isNull(inputStream) || ObjectUtil.isNull(tClass) || ObjectUtil.isNull(excelListener)){
return null;
}
ExcelReaderBuilder read = EasyExcel.read(inputStream, tClass, excelListener);
if(ObjectUtil.isNotNull(sheetNo)){
read.sheet(sheetNo).doRead();
}else{
ExcelReader excelReader = read.build();
excelReader.readAll();
excelReader.finish();
}
return excelListener.getDatas();
}
/**
* <p>不带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 标注</p >
* @date 2021/9/2 0:32
* @param response 响应对象
* @param tClass 输出格式
* @param datas 输出的数据
* @return：
*/
public static <T> void writeEasyExcel(HttpServletResponse response, Class<T> tClass, List<T> datas, String fileName) throws IOException {
TimeInterval timer = DateUtil.timer();
if(ObjectUtil.isNull(response) || ObjectUtil.isNull(tClass)){
return;
}
if(StrUtil.isBlank(fileName)){
fileName = "excel.xlsx";
}else{
if(!fileName.contains(".xlsx")){
fileName = fileName+".xlsx";
}
}
response.setStatus(HttpServletResponse.SC_OK);
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName, "utf-8"));
EasyExcel.write(response.getOutputStream(), tClass)
.registerWriteHandler(new Custemhandler())
.excelType(ExcelTypeEnum.XLSX)
.sheet("sheet")
.doWrite(datas);
log.info("导出exlce数据：{}条，耗时：{}秒！", datas.size(), timer.intervalSecond());
}
/**
* <p>带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 标注</p >
* @date 2021/9/2 0:32
* @param outputStream 输出流
* @param tClass 输出格式
* @param datas 输出的数据
* @return：
*/
public static <T> void writeExcel(InputStream inputStream ,OutputStream outputStream, Class<T> tClass, List<T> datas){
TimeInterval timer = DateUtil.timer();
if(ObjectUtil.isNull(outputStream) || ObjectUtil.isNull(tClass)){
return;
}
// EasyExcel.write(outputStream, tClass).withTemplate(inputStream).sheet("sheet").doWrite(datas);
EasyExcel.write(outputStream, tClass).excelType(ExcelTypeEnum.XLSX).withTemplate(inputStream).sheet("sheet").doFill(datas);
log.info("导出exlce数据：{}条，耗时：{}秒！", datas.size(), timer.intervalSecond());
}
/**
* <p>不带模板输出数据到Excel，数据传输类属性用 @ExcelProperty("") 标注</p >
* @date 2021/9/2 0:32
* @param response 响应对象
* @param tClass 输出格式
* @param datas 输出的数据
* @return：
*/
public static <T> void writeExcel(HttpServletResponse response, Class<T> tClass, List<T> datas, String fileName) throws IOException, IllegalAccessException, NoSuchFieldException {
TimeInterval timer = DateUtil.timer();
if(ObjectUtil.isNull(response) || ObjectUtil.isNull(tClass)){
return;
}
if(StrUtil.isBlank(fileName)){
fileName = "excel.xlsx";
}else{
if(!fileName.contains(".xlsx")){
fileName = fileName+".xlsx";
}
}
//编码设置成UTF-8，excel文件格式为.xlsx
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
// 这里URLEncoder.encode可以防止中文乱码 和easyexcel本身没有关系
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//处理注解转换
// if(CollUtil.isNotEmpty(datas)){
// for (T dataObj : datas) {
// AnnotationUtil.resolveAnnotation(tClass, dataObj);
// }
// }
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), tClass).build();
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName("sheet");
excelWriter.write(datas, writeSheet);
excelWriter.finish();
log.info("导出exlce数据：{}条，耗时：{}秒！", datas.size(), timer.intervalSecond());
}
}
工具类代码中， ExcelListener 为定义的内部监听类，需要继承 AnalysisEventListener，读取和写入方法分别为 readExcel 和 writeExcel，详见方法注释说明，直接复制到项目中即可使用
三、使用示例：
读取表格
实体类
package com.hkl.modules.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
/**
* <p>ClassName：ImportTestDatasReq</p >
* <p>Description：</p >
* <p>Date：2021/9/2</p >
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ImportTestDatasReq implements Serializable {
@ExcelProperty(value = "userName")
@ApiModelProperty("用户姓名")
private String userName;
@ExcelProperty(value = "address")
@ApiModelProperty("地址")
private String address;
@ExcelProperty(value = "price")
@ApiModelProperty("价格")
private BigDecimal price;
@ExcelProperty(value = "createDate")
@ApiModelProperty("创建时间")
private Date createDate;
}
注意：实体类中不能使用链式写法注解 @Accessors(chain = true)，否则会读取不到数据
读取数据
@Slf4j
@Api(tags = "Excel文件操作管理")
@RestController
@RequestMapping("/excel")
public class ExcelController extends BaseController {
/**
* <p> MultipartFile 这个类一般是用来接收前台传过来的文件信息 </p >
*/
@ApiOperation(value = "导入XXX数据")
@PostMapping("/importTestDatas")
public CommonResult importTestDatas(@RequestParam("multipartFile") MultipartFile multipartFile) throws BizException, IOException {
isBlank(multipartFile, "参数");
List<ImportTestDatasReq> ImportTestDatasReqList = EasyExcelUtil.readExcel(multipartFile.getInputStream(), ImportTestDatasReq.class, new EasyExcelUtil.ExcelListener<>());
ImportTestDatasReqList.forEach(System.out::println);
return success(null);
}
}
读取的文件内容：
image.png
控制台输出读取结果：
image.png
写入数据到表格
写入接口：
@ApiOperation(value = "导出XXX数据")
@GetMapping("/exportTestDatas")
public void exportTestDatas(HttpServletResponse response) throws IOException, IllegalAccessException, NoSuchFieldException {
List<ExportTestDatasResp> exportDatas = new ArrayList<>();
exportDatas.add(new ExportTestDatasResp("王五","广州",new BigDecimal(15000),"0",null,new Date(),null));
exportDatas.add(new ExportTestDatasResp("赵六","深圳",new BigDecimal(18000),"1",null,new Date(), null));
EasyExcelUtil.writeEasyExcel(response, ExportTestDatasResp.class, exportDatas, "测试数据");
}
写入数据 映射类：
package com.hkl.modules.dto.resp;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.hkl.annotation.FieldConvert;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
@HeadRowHeight(value = 15) //设置表头高度
@ExcelIgnoreUnannotated //忽略没有注解标记的属性
//@ExcelProperty：如果 value 值相同标题列会合并，index不同不合并
public class ExportTestDatasResp implements Serializable {
@ExcelProperty(value = "用户姓名", index = 0)
@ApiModelProperty("用户姓名")
private String userName;
@ExcelProperty(value = "地址", index = 1)
@ApiModelProperty("地址")
private String address;
@ExcelProperty(value = "价格", index = 2)
@NumberFormat(value = "#.00")
@ApiModelProperty("价格")
private BigDecimal price;
@ExcelProperty(value = "状态", index = 3)
//@FieldConvert(codeType = "status")
@ApiModelProperty("状态")
private String status;
@ExcelProperty(value = "状态名称", index = 4)
//@FieldConvert(codeType = "status")
@ApiModelProperty("状态")
private String statusName;
@ExcelProperty(value = "创建时间", index = 5)
@DateTimeFormat(value = "yyyy-MM-dd")
@ApiModelProperty("创建时间")
private Date createDate;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
注解说明：
1、@HeadRowHeight：设置表头高度，作用域在类
2、@ExcelIgnoreUnannotated：忽略没有注解标记的字段，作用域在类
3、@ExcelIgnore：忽略指定的字段，作用域在字段
4、@ExcelProperty：指定导出列名和索引，作用域在字段
5、@ColumnWidth：设置列的宽度，作用域在字段
6、@NumberFormat：设置数值精度，作用域在字段，例：@NumberFormat(value = "#.00")
7、@DateTimeFormat：格式化日期，作用域在字段，例：@DateTimeFormat(value = "yyyy-MM-dd")
注意：我们可以使用 EasyExcel.write().registerWriteHandler(new Custemhandler()) 设置导出列的宽度为自适应，但是使用 registerWriteHandler(new Custemhandler()) 方法之后不能再使用注解@ColumnWidth，否则会导致自适应宽度不生效
写入的数据结果：
image.png
