springboot使用EasyExcel进行导入导出
1、添加依赖
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2、创建导入导出的映射实体类
导出
package com.zl.model.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Builder;
import lombok.Data;
/**
* 导出VO
*
* @author z
* @date 2022-03-14 18:01
*/
@Data
@HeadRowHeight(value = 20)
public class ExportModeSheetOneVO {
/**
* id
*/
@ExcelProperty(value = "编号",index = 0)
@ColumnWidth(value = 20)
private String id;
/**
* 名称
*/
@ExcelProperty(value = "名称",index = 1)
@ColumnWidth(value = 20)
private String name;
/**
* 类型
*/
@ExcelProperty(value = "类型",index = 2)
@ColumnWidth(value = 20)
private String type;
/**
* 描述
*/
@ExcelProperty(value = "描述",index = 3)
@ColumnWidth(value = 20)
private String des;
/**
* 创建日期
*/
@ExcelProperty(value = "创建日期",index = 4)
@ColumnWidth(value = 20)
private String createDt;
/**
* 创建者
*/
@ExcelProperty(value = "创建者",index = 5)
@ColumnWidth(value = 20)
private String createBy;
/**
* 更新日期
*/
@ExcelProperty(value = "更新日期",index = 6)
@ColumnWidth(value = 20)
private String updateDt;
/**
* 更新者
*/
@ExcelProperty(value = "更新者",index = 7)
@ColumnWidth(value = 20)
private String updateBy;
}
导入
package com.zl.model.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* 导入VO
*
* @author z
* @date 2022-03-15 16:08
*/
@Data
public class ImportModeVO {
/**
* 模型名称
*/
@ExcelProperty("名称")
private String name;
/**
* 类型
*/
@ExcelProperty("类别")
private String type;
/**
* 描述
*/
@ExcelProperty("描述")
private String des;
}
建议类型都写为String,可以减少不必要的类型转换
3、实现
Controller层代码实现:
/**
* 导入导出测试
*
* @author z
* @since 2022-03-12
*/
@RestController
@RequestMapping("/myTest")
@Api(tags = "数据导入导出")
@Slf4j
public class MyInfoController {
@Autowired
private MyInfoService myInfoService;
/**
* 模型导入
* @param file
* @return
*/
@PostMapping("/importMode")
@ApiOperation("导入")
@ApiImplicitParam(name = "file",value = "上传的数据",required = true)
public Result importMode(@RequestParam("file") MultipartFile file){
if (file==null||file.isEmpty()) {
return Result.error();
}
int flag= 0;
try {
flag = myInfoService.importMode(file);
} catch (IOException e) {
e.printStackTrace();
}
return flag==1?Result.success():Result.error();
}
/**
* 导出
* @param type 导出类别
*/
@GetMapping("/exportMode")
@ApiOperation("导出")
@ApiImplicitParam(name="type",value="导出类别",required = true)
public void exportMode(String type, HttpServletResponse response){
try {
myInfoService.exportMode(type,response);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 导出
* @param type 导出类别
*/
@GetMapping("/export")
@ApiOperation("导出1")
@ApiImplicitParam(name="type",value="导出类别",required = true)
public void export(String type, HttpServletResponse response){
try {
myInfoService.export(type,response);
} catch (Exception e) {
e.printStackTrace();
}
}
Service层实现：
/**
* 单个sheet导入
* @param file
* @return
*/
@Override
@Transactional(rollbackFor = Exception.class)
public int importMode(MultipartFile file) throws IOException {
InputStream in =file.getInputStream();
Integer readHeadIndex = 7;
ImportExcelListenerUtil importExcelListenerUtil = new ImportExcelListenerUtil();
importExcelListenerUtil.setHeadReadIndex(readHeadIndex);
importExcelListenerUtil.setHeadEntity(new ImportModeVO());
EasyExcel.read(in, ImportModeVO.class,importExcelListenerUtil).headRowNumber(readHeadIndex) // 从第几行导入，此处为从第7行导入，即第7行是表头
.sheet("Sheet1")
.doRead();
// 每次EasyExcel的read方法读取完之后都会关闭流，我这里为了试验doReadAll方法，所以重新获取了一次
// in = file.getInputStream();
// SyncReadListener syncReadListener = new SyncReadListener();
// EasyExcel.read(in,ImportModeVO.class,syncReadListener).doReadAll();
List<ImportModeVO> list = importExcelListenerUtil.list;
for(int i=0;i<list.size();i++){
ImportModeEntity importModeEntity =new ImportModeEntity();
BeanUtils.copyProperties(list.get(i),importModeEntity);
importModeEntity.setType(Integer.parseInt(list.get(i).getType()));
String modeId=UUID.randomUUID().toString().replace("-","").toUpperCase();
importModeEntity.setId(modeId);
this.save(importModeEntity);
}
in.close();
return 1;
}
/**
* 多个sheet导入
* @param file
* @return
*/
@Override
@Transactional(rollbackFor = Exception.class)
public int importMode(MultipartFile file) throws IOException {
InputStream in = file.getInputStream();
List<ReadSheet> sheets = EasyExcel.read(in).build().excelExecutor().sheetList();
for (ReadSheet sheet : sheets) {
//因为在获取sheetList的时候改变了通过file获取的流类型，在EasyExcel.read读的时候，采用的同一个流，会导致excel类型出错。
//解决方法：重新创建一个excel流，与获取sheet的流区别使用
InputStream io = file.getInputStream();
ImportExcelListenerUtil importExcelListenerUtil = new ImportExcelListenerUtil()
.setHeadReadIndex(readHeadIndex)
.setHeadEntity(new ImportModeVO());
EasyExcel.read(io, ImportModeVO.class,importExcelListenerUtil)
.sheet(sheet.getSheetName())
.doRead();
List<ImportModeVO> list = ImportExcelListenerUtil.list;
for (int j = 0; j < list.size(); j++) {
System.out.println(list.get(j));
}
System.out.println("这是" + sheet.getSheetName() + "的所有数据");
ImportExcelListenerUtil.list.clear();
io.close();
}
return 1;
}
/**
* 模型导出
* @param type 导出类别
*/
@Override
public void exportMode(String type, HttpServletResponse response) throws IOException {
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
// 导出文件名
String fileName="文件导出"+time+".xlsx";
// sheet名
String sheetNameOne="导出sheet1";
String sheetNameTwo="导出sheet2";
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition","attachment;filename=" + fileName);
// 查询到数据
List<ExportModeSheetOneVO> exportModeSheetOneVOS = this.selectModelShowOne(type);
List<ExportModeSheetTwoVO> exportModeSheetTwoVOS = this.selectModelShowTwo(type);
// 写出
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
// ExportModeSheetOneVO 是导出的映射实体类，exportModeSheetOneVOS 是导出的数据集合，registerWriteHandler(new CustomCellWriteHandlerUtil()) 设置表头自适应，这是多个sheet导出，只有一个只需导出需要的sheet即可
// 不设置自适应表头：EasyExcel.writerSheet(0,sheetNameOne).head(ExportModeSheetOneVO.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetNameOne).head(ExportModeSheetOneVO.class).registerWriteHandler(new CustomCellWriteHandlerUtil()).build();
excelWriter.write(exportModeSheetOneVOS,writeSheet);
writeSheet = EasyExcel.writerSheet(1,sheetNameTwo).head(ExportModeSheetTwoVO.class).registerWriteHandler(new CustomCellWriteHandlerUtil()).build();
excelWriter.write(exportModeSheetTwoVOS,writeSheet);
//千万别忘记关流,finish会帮忙关流
excelWriter.finish();
}
/**
* 模型导出
* @param type 导出类别
*/
@Override
public void export(String type, HttpServletResponse response) throws IOException {
// 查询数据
List<ExportModeSheetOneVO> data= this.selectModelShowOne(type);
// 设置响应
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = "文件导出";
try {
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls");
// 添加过滤的列
Set<String> columnNames = new HashSet<String>();
columnNames.add("id");
...
EasyExcel.write(response.getOutputStream(), ExportModeSheetOneVO.class).excludeColumnFiledNames(columnNames).sheet("导出sheet1").doWrite(data);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 模型批量导出
* @param type 导出类别
*/
@Override
public void exportModes(String type, HttpServletResponse response) throws IOException {
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
// 导出文件名
String fileName="文件导出"+time+".xlsx";
// sheet名
String sheetNameOne="导出sheet1";
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName,"UTF-8");
response.setHeader("Content-disposition","attachment;filename=" + fileName);
// 写出
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
Page page=new Page(1,500);
// 查询到数据
Page<ExportModeSheetOneVO> pageData = this.selectModelShowOne(page,type);
while(page.getPageNum()<=pageData.getPages()){
excelWriter.write(pageData.getRecords(),EasyExcel.writerSheet(0,sheetNameOne).head(ExportModeSheetTwoVO.class).build());
page.setPageNum(page.getPageNum()+1);
pageData=this.selectModelShowOne(page,type);
}
//千万别忘记关流,finish会帮忙关流，刷新数据
excelWriter.finish();
}
直接导出文件：
ExcelWriter excelWriter = EasyExcel.write(filePath).build();其中 filePath 为导出文件的目录 eg：String filePath = "/home/数据导出.xlsx";
解释：
-
Content-disposition
这个是MIME协议的一种扩展，主要目的就是显示待下载的文件，当浏览器接收到请求头后，就会去激活一个下载框，他们的名字需要自己塞值到header里面，从而我们下载的文件名要放这个里面。没有设置它就根本不能触发浏览器下载的那个动作。 -
URLEncoder.encode
URLEncoder.encode后其中fileName如下所示是带有%字母加数字的组合编码形式，其实这个你就可以这么去理解，后端采用这种编码格式，机器能识别，也能塞到头里面有助于传输，但是切记前端收到的时候用decodeURI进行解密哟，不然下载下来的是%E9%BB%之类的乱串。 -
导入需要设置一个自定义Easyexcel的监听类
package com.zl.util;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import java.lang.reflect.Field;
import java.util.*;
public class ImportExcelListenerUtil extends AnalysisEventListener<Object> {
private List<Object> list = new ArrayList<>();
private Object headEntity;
private Integer headReadIndex = 1;
public List<Object> getList() {
return list;
}
public ImportExcelListenerUtil setList(List<Object> list) {
this.list = list;
return this;
}
public Integer getHeadReadIndex() {
return headReadIndex;
}
public ImportExcelListenerUtil setHeadReadIndex(Integer headReadIndex) {
this.headReadIndex = headReadIndex;
return this;
}
public Object getHeadEntity() {
return headEntity;
}
public ImportExcelListenerUtil setHeadEntity(Object headEntity) {
this.headEntity = headEntity;
return this;
}
@Override
public void invoke(Object excelEntity, AnalysisContext context) {
list.add(excelEntity);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// String[] headList = {"序号", "姓名", "性别", "年龄", "文化程度", "民族", "身份证号", "工作单位/学校", "手机/电话", "备注"};
if (ObjectUtil.isEmpty(headEntity)) {
throw new ExcelAnalysisException("读取系统模板实体为：null");
}
// 验证表头 获取实体类注解@ExcelProperty标识的表头，以index标识的顺序进行比较
if (context.readRowHolder().getRowIndex() == headReadIndex - 1) {
// 获取实体类表头信息
Map<Integer, String> entityHeadMap = new LinkedHashMap<>();
List<Field> fieldList = TableInfoHelper.getAllFields(this.getHeadEntity().getClass());
for (Field field : fieldList) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
ExcelProperty excel = field.getAnnotation(ExcelProperty.class);
//英文为key,中文为value
Integer key = excel.index();
String[] value = excel.value();
entityHeadMap.put(key, value.length > 0 ? value[value.length - 1] : "");
}
}
// 判断读取表头是否和实体表头一致
Set<Integer> heads = entityHeadMap.keySet();
for (Integer headIndex : heads) {
if (!entityHeadMap.get(headIndex).equals(headMap.get(headIndex))) {
throw new ExcelAnalysisException("上传模板与系统模板不匹配，请使用平台模板上传数据");
}
}
}
super.invokeHeadMap(headMap, context);
}
}
- 自定义表头需要的工具类
package com.zl.util;
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.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 导出自适应列宽
*
* @author z
* @date 2022-03-15 14:57
*/
public class CustomCellWriteHandlerUtil 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> 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth> maxColumnWidth) {
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 = 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;
}
}
}
}
}
4、测试
- 使用 postman 测试导入导出接口
SpringBoot 整合 EasyExcel 实现自由导入导出功能
依赖:
<!--EasyExcel相关依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
EasyExcel 的导出导入支持两种方式进行处理
- 第一种是通过实体类注解方式来生成文件和反解析文件数据映射成对象
- 第二种是通过动态参数化生成文件和反解析文件数据
简单导出
首先，我们只需要创建一个UserEntity用户实体类，然后添加对应的注解字段即可，示例代码如下：
public class UserWriteEntity {
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "年龄")
private int age;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "操作时间")
private Date time;
//set、get...
}
然后，使用 EasyExcel 提供的EasyExcel工具类，即可实现文件的导出。
public static void main(String[] args) throws FileNotFoundException {
List<UserWriteEntity> dataList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserWriteEntity userEntity = new UserWriteEntity();
userEntity.setName("张三" + i);
userEntity.setAge(20 + i);
userEntity.setTime(new Date(System.currentTimeMillis() + i));
dataList.add(userEntity);
}
//定义文件输出位置
FileOutputStream outputStream = new FileOutputStream(new File("/Users/panzhi/Documents/easyexcel-export-user1.xlsx"));
EasyExcel.write(outputStream, UserWriteEntity.class).sheet("用户信息").doWrite(dataList);
}
运行程序，打开文件内容结果！
简单导入
这种简单固定表头的 Excel 文件，如果想要读取文件数据，操作也很简单。
以上面的导出文件为例，使用 EasyExcel 提供的EasyExcel工具类，即可来实现文件内容数据的快速读取，示例代码如下：
首先创建读取实体类
/**
* 读取实体类
*/
public class UserReadEntity {
@ExcelProperty(value = "姓名")
private String name;
/**
* 强制读取第三个 这里不建议 index 和 name 同时用，要么一个对象只用index，要么一个对象只用name去匹配
*/
@ExcelProperty(index = 1)
private int age;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@ExcelProperty(value = "操作时间")
private Date time;
//set、get...
}
然后读取文件数据，并封装到对象里面
public static void main(String[] args) throws FileNotFoundException {
//同步读取文件内容
FileInputStream inputStream = new FileInputStream(new File("/Users/panzhi/Documents/easyexcel-user1.xls"));
List<UserReadEntity> list = EasyExcel.read(inputStream).head(UserReadEntity.class).sheet().doReadSync();
System.out.println(JSONArray.toJSONString(list));
}
运行程序，输出结果如下：
[{"age":20,"name":"张三0","time":1616920360000},{"age":21,"name":"张三1","time":1616920360000},{"age":22,"name":"张三2","time":1616920360000},{"age":23,"name":"张三3","time":1616920360000},{"age":24,"name":"张三4","time":1616920360000},{"age":25,"name":"张三5","time":1616920360000},{"age":26,"name":"张三6","time":1616920360000},{"age":27,"name":"张三7","time":1616920360000},{"age":28,"name":"张三8","time":1616920360000},{"age":29,"name":"张三9","time":1616920360000}]
在实际使用开发中，我们不可能每来一个 excel 导入导出需求，就编写一个实体类，很多业务需求需要根据不同的字段来动态导入导出，没办法基于实体类注解的方式来读取文件或者写入文件。
因此，基于EasyExcel提供的动态参数化生成文件和动态监听器读取文件方法，我们可以单独封装一套动态导出导出工具类，省的我们每次都需要重新编写大量重复工作。
动态导出工具类
package com.fengyun.udf.property.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.nacos.shaded.com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* easyExcel 导出工具类
*/
@Slf4j
public class DynamicEasyExcelExportUtils {
private static final String DEFAULT_SHEET_NAME = "sheet1";
/**
* 动态生成导出模版(单表头)
*
* @param headColumns 列名称
* @return excel文件流
*/
public static byte[] exportTemplateExcelFile(List<String> headColumns) {
List<List<String>> excelHead = Lists.newArrayList();
headColumns.forEach(columnName -> {
excelHead.add(Lists.newArrayList(columnName));
});
byte[] stream = createExcelFile(excelHead, new ArrayList<>());
return stream;
}
/**
* 动态生成模版(复杂表头)
*
* @param excelHead 列名称
* @return
*/
public static byte[] exportTemplateExcelFileCustomHead(List<List<String>> excelHead) {
byte[] stream = createExcelFile(excelHead, new ArrayList<>());
return stream;
}
/**
* 动态导出文件（通过map方式计算）
*
* @param headColumnMap 有序列头部
* @param dataList 数据体
* @return
*/
public static byte[] exportExcelFile(LinkedHashMap<String, String> headColumnMap, List<Map<String, Object>> dataList) {
//获取列名称
List<List<String>> excelHead = new ArrayList<>();
if (MapUtils.isNotEmpty(headColumnMap)) {
//key为匹配符，value为列名，如果多级列名用逗号隔开
headColumnMap.entrySet().forEach(entry -> {
excelHead.add(Lists.newArrayList(entry.getValue().split(",")));
});
}
List<List<Object>> excelRows = new ArrayList<>();
if (MapUtils.isNotEmpty(headColumnMap) && CollectionUtils.isNotEmpty(dataList)) {
for (Map<String, Object> dataMap : dataList) {
List<Object> rows = new ArrayList<>();
headColumnMap.entrySet().forEach(headColumnEntry -> {
if (dataMap.containsKey(headColumnEntry.getKey())) {
Object data = dataMap.get(headColumnEntry.getKey());
rows.add(data);
}
});
excelRows.add(rows);
}
}
byte[] stream = createExcelFile(excelHead, excelRows);
return stream;
}
/**
* 生成文件（自定义头部排列）
*
* @param rowHeads
* @param excelRows
* @return
*/
public static byte[] customerExportExcelFile(List<List<String>> rowHeads, List<List<Object>> excelRows) {
//将行头部转成easyexcel能识别的部分
List<List<String>> excelHead = transferHead(rowHeads);
return createExcelFile(excelHead, excelRows);
}
/**
* 生成文件
*
* @param excelHead
* @param excelRows
* @return
*/
private static byte[] createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows) {
try {
if (CollectionUtils.isNotEmpty(excelHead)) {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.head(excelHead)
.sheet(DEFAULT_SHEET_NAME)
.doWrite(excelRows);
return outputStream.toByteArray();
}
} catch (Exception e) {
log.error("动态生成excel文件失败，headColumns：" + JSONArray.toJSONString(excelHead) + "，excelRows：" + JSONArray.toJSONString(excelRows), e);
}
return null;
}
/**
* 将行头部转成easyexcel能识别的部分
*
* @param rowHeads
* @return
*/
public static List<List<String>> transferHead(List<List<String>> rowHeads) {
//将头部列进行反转
List<List<String>> realHead = new ArrayList<>();
if (CollectionUtils.isNotEmpty(rowHeads)) {
Map<Integer, List<String>> cellMap = new LinkedHashMap<>();
//遍历行
for (List<String> cells : rowHeads) {
//遍历列
for (int i = 0; i < cells.size(); i++) {
if (cellMap.containsKey(i)) {
cellMap.get(i).add(cells.get(i));
} else {
cellMap.put(i, Lists.newArrayList(cells.get(i)));
}
}
}
//将列一行一行加入realHead
cellMap.entrySet().forEach(item -> realHead.add(item.getValue()));
}
return realHead;
}
/**
* 导出文件测试
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
//导出包含数据内容的文件（方式一）
LinkedHashMap<String, String> headColumnMap = Maps.newLinkedHashMap();
headColumnMap.put("className","班级");
headColumnMap.put("name","学生信息,姓名");
headColumnMap.put("sex","学生信息,性别");
List<Map<String, Object>> dataList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Map<String, Object> dataMap = Maps.newHashMap();
dataMap.put("className", "一年级");
dataMap.put("name", "张三" + i);
dataMap.put("sex", "男");
dataList.add(dataMap);
}
byte[] stream1 = exportExcelFile(headColumnMap, dataList);
FileOutputStream outputStream1 = new FileOutputStream(new File("/Users/panzhi/Documents/easyexcel-export-user5.xlsx"));
outputStream1.write(stream1);
outputStream1.close();
//导出包含数据内容的文件（方式二）
//头部，第一层
List<String> head1 = new ArrayList<>();
head1.add("第一行头部列1");
head1.add("第一行头部列1");
head1.add("第一行头部列1");
head1.add("第一行头部列1");
//头部，第二层
List<String> head2 = new ArrayList<>();
head2.add("第二行头部列1");
head2.add("第二行头部列1");
head2.add("第二行头部列2");
head2.add("第二行头部列2");
//头部，第三层
List<String> head3 = new ArrayList<>();
head3.add("第三行头部列1");
head3.add("第三行头部列2");
head3.add("第三行头部列3");
head3.add("第三行头部列4");
//封装头部
List<List<String>> allHead = new ArrayList<>();
allHead.add(head1);
allHead.add(head2);
allHead.add(head3);
//封装数据体
//第一行数据
List<Object> data1 = Lists.newArrayList(1,1,1,1);
//第二行数据
List<Object> data2 = Lists.newArrayList(2,2,2,2);
List<List<Object>> allData = Lists.newArrayList(data1, data2);
byte[] stream2 = customerExportExcelFile(allHead, allData);
FileOutputStream outputStream2 = new FileOutputStream(new File("/Users/panzhi/Documents/easyexcel-export-user6.xlsx"));
outputStream2.write(stream2);
outputStream2.close();
}
}
动态导入工具类
/**
* 创建一个文件读取监听器
*/
public class DynamicEasyExcelListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class);
/**
* 表头数据（存储所有的表头数据）
*/
private List<Map<Integer, String>> headList = new ArrayList<>();
/**
* 数据体
*/
private List<Map<Integer, String>> dataList = new ArrayList<>();
/**
* 这里会一行行的返回头
*
* @param headMap
* @param context
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
LOGGER.info("解析到一条头数据:{}", JSON.toJSONString(headMap));
//存储全部表头数据
headList.add(headMap);
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
dataList.add(data);
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
LOGGER.info("所有数据解析完成！");
}
public List<Map<Integer, String>> getHeadList() {
return headList;
}
public List<Map<Integer, String>> getDataList() {
return dataList;
}
}
动态导入工具类
/**
* 编写导入工具类
*/
public class DynamicEasyExcelImportUtils {
/**
* 动态获取全部列和数据体，默认从第一行开始解析数据
* @param stream
* @return
*/
public static List<Map<String,String>> parseExcelToView(byte[] stream) {
return parseExcelToView(stream, 1);
}
/**
* 动态获取全部列和数据体
* @param stream excel文件流
* @param parseRowNumber 指定读取行
* @return
*/
public static List<Map<String,String>> parseExcelToView(byte[] stream, Integer parseRowNumber) {
DynamicEasyExcelListener readListener = new DynamicEasyExcelListener();
EasyExcelFactory.read(new ByteArrayInputStream(stream)).registerReadListener(readListener).headRowNumber(parseRowNumber).sheet(0).doRead();
List<Map<Integer, String>> headList = readListener.getHeadList();
if(CollectionUtils.isEmpty(headList)){
throw new RuntimeException("Excel未包含表头");
}
List<Map<Integer, String>> dataList = readListener.getDataList();
if(CollectionUtils.isEmpty(dataList)){
throw new RuntimeException("Excel未包含数据");
}
//获取头部,取最后一次解析的列头数据
Map<Integer, String> excelHeadIdxNameMap = headList.get(headList.size() -1);
//封装数据体
List<Map<String,String>> excelDataList = Lists.newArrayList();
for (Map<Integer, String> dataRow : dataList) {
Map<String,String> rowData = new LinkedHashMap<>();
excelHeadIdxNameMap.entrySet().forEach(columnHead -> {
rowData.put(columnHead.getValue(), dataRow.get(columnHead.getKey()));
});
excelDataList.add(rowData);
}
return excelDataList;
}
/**
* 文件导入测试
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
FileInputStream inputStream = new FileInputStream(new File("/Users/panzhi/Documents/easyexcel-export-user5.xlsx"));
byte[] stream = IoUtils.toByteArray(inputStream);
List<Map<String,String>> dataList = parseExcelToView(stream, 2);
System.out.println(JSONArray.toJSONString(dataList));
inputStream.close();
}
}
在解析数据的时候，会将列名作为key
测试
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.example.springboottest.entity.UserEntity;
import com.example.springboottest.util.ExcelUtil;
import org.ehcache.shadow.org.terracotta.offheapstore.HashingMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
@RestController
public class ExcelExportController {
//需要实体类
@GetMapping("/load")
public void downloadReportData(HttpServletResponse response) {
String fileName = "测试数据.xls";
List<UserEntity> userEntities = new ArrayList<>();
UserEntity userEntity = new UserEntity();
userEntity.setName("张无忌");
userEntity.setAccount("9999999999999");
userEntity.setAddress("光明顶");
userEntity.setAge(18);
userEntity.setOccupation("明教教主");
userEntities.add(userEntity);
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
EasyExcel.write(response.getOutputStream(), UserEntity.class).excelType(ExcelTypeEnum.XLS).autoCloseStream(Boolean.TRUE)
.sheet("人员信息").doWrite(userEntities);
} catch (IOException e) {
e.printStackTrace();
}
}
// 不需要实体类
@GetMapping(value = "/export")
public void export(HttpServletResponse response) throws IOException {
List<List<String>> heads = new ArrayList<>();
heads.add(Arrays.asList("姓名"));
heads.add(Arrays.asList("年龄"));
heads.add(Arrays.asList("地址"));
//业务数据存放
Map<String, Object> m = new HashMap<String, Object>();
m.put("name", "zwm");
m.put("age", "18");
m.put("address", "武汉");
List<Map<String, Object>> list = new ArrayList<>();
list.add(m);
list.add(m);
list.add(m);
List<List<Object>> total = new ArrayList<>();
for (Map<String, Object> map : list) {
List<Object> info = new ArrayList<>();
info.add(map.get("name"));
info.add(map.get("age"));
info.add(map.get("address"));
total.add(info);
}
String fileName = URLEncoder.encode("人员信息", "UTF-8").replaceAll("\\+", "%20");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//设置响应头
response.setHeader("mime", "application/vnd.ms-excel");
EasyExcel.write(response.getOutputStream())
.head(heads)
.sheet("人员信息")
.doWrite(total);
}
}
