使用easyexcel实现导入导出
项目地址：https://gitee.com/libeimaningg/beizhilib
（一）前提配置
添加依赖：
需要4.0.3版本，使用3版本会报：java.lang.NoClassDefFoundError: com/alibaba/excel/support/util/CollectionUtils
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>4.0.3</version>
</dependency>
（二）使用示例
1、导入
Controller层
/**
* 导入用户
*
* @param file
* @return
*/
@PostMapping("/importUser")
public RestResult importUser(MultipartFile file) {
sysUserService.importUser(file);
return new RestResult<>(RestResult.SUCCESS_CODE, "导入成功");
}
业务层
@Override
public void importUser(MultipartFile file) {
try {
List<SysUser> data = EasyExcel.read(file.getInputStream())
.head(SysUser.class).doReadAllSync();
log.info("data = {}", data);
log.info("#############用户信息导入：{}条###############", data.size());
if (CollectionUtil.isEmpty(data)) {
throw new BusinessException(StstusEnums.FAIL, BusinessEnums.FAIL.getType(), "用户信息导入失导失败，数据为空");
}
List<SysUser> result = new ArrayList<>();
for (SysUser sysUser : data) {
if (StringUtils.isEmpty(sysUser.getUserName())) {
throw new BusinessException(StstusEnums.FAIL, BusinessEnums.FAIL.getType(), "用户信息导入失导失败，用户账号不可为空");
}
SysUser user = new SysUser();
BeanUtils.copyProperties(sysUser, user);
user.setCreateTime(LocalDateTime.now());
user.setUpdateTime(LocalDateTime.now());
result.add(user);
}
sysUserMapper.batchInsert(result);
} catch (IOException e) {
throw new BusinessException(StstusEnums.FAIL, BusinessEnums.FAIL.getType(), e.getMessage());
}
}
2、导出
Controller层
/**
* 导出用户
*
* @param response
* @param user
* @throws IOException
*/
@PostMapping("/exportUser")
public void exportUser(HttpServletResponse response, @RequestBody UserDto user) throws IOException {
sysUserService.exportUser(response, user);
}
业务层
@Override
public void exportUser(HttpServletResponse response, UserDto user) throws IOException {
log.info("开始导出，导出条件为：{}", user);
List<SysUser> list = sysUserMapper.selectAll(user);
//文件名
String fileName = "用户信息_" + DateUtil.format(LocalDateTime.now(), "yyyyMMddHHmmss") + ".xlsx";
//用UUID命名
String fileName2 = UUID.randomUUID().toString() + ".xlsx";
//将文件保存本地
File file = null;
try {
if (!FileUtil.exist(filePath)) {
FileUtil.mkdir(filePath);
log.info("创建文件夹成功：" + filePath);
}
file = new File(filePath + "/" + fileName2);
EasyExcel.write(file, SysUser.class).sheet("用户信息").doWrite(list);//.registerWriteHandler(new CellWriteWidthConfig())
} catch (Exception e) {
e.printStackTrace();
} finally {
// FileUtil.del(file);
}
try (OutputStream os = response.getOutputStream()) {
response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, StandardCharsets.UTF_8));
//设置content-type
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
EasyExcel.write(os, SysUser.class).sheet("用户信息").doWrite(list);//.registerWriteHandler(new CellWriteWidthConfig())
response.addHeader("code", RestResult.SUCCESS_CODE);
response.addHeader("msg", URLEncoder.encode("导出表格完成", StandardCharsets.UTF_8));
} catch (Exception e) {
log.error("导出表格失败，失败原因：{}", e.getMessage());
response.addHeader("code", RestResult.FAIL_CODE);
response.addHeader("msg", URLEncoder.encode("导出失败", StandardCharsets.UTF_8));
}
}
3、公共部分
实体类注解示例
/**
* 部门ID
*/
@ExcelProperty(value = "部门ID", index = 0)//设置别名、列的位置
@ColumnWidth(50)//设置列宽
private Long deptId;
/**
* 用户性别（0男 1女 2未知）
*/
@ExcelProperty(value = "用户性别", index = 6, converter = ExcelConverter.GenderConverter.class)
private String sex;
转换器
package org.yaoyao.yaoyaocommon.utils;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.yaoyao.yaoyaocommon.enums.BusinessEnums;
import org.yaoyao.yaoyaocommon.enums.StstusEnums;
import org.yaoyao.yaoyaocommon.exception.BusinessException;
/**
* TODO 类作用描述
*
* @author Bryson
* @date 2025/3/12
*/
public class ExcelConverter {
public static class GenderConverter implements Converter<String> {
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Class<?> supportJavaTypeKey() {
return String.class;
}
@Override
public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
if (StringUtils.equals("男", cellData.getStringValue())) {
return "0";
} else if (StringUtils.equals("女", cellData.getStringValue())) {
return "1";
} else {
throw new BusinessException(StstusEnums.FAIL, BusinessEnums.GENDER_EXCEPTION.getType(), "性别字段只能输入“男”或“女”");
}
}
@Override
public WriteCellData<Object> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
if (StringUtils.equals("0", value)) {
return new WriteCellData<>("男");
} else if (StringUtils.equals("1", value)) {
return new WriteCellData<>("女");
} else {
return new WriteCellData<>("");
}
}
}
}
4、导出添加表头
效果图：
导出添加registerWriteHandler：
EasyExcel.write(os, SysUser.class).sheet("用户信息").registerWriteHandler(new CellWriteWidthConfig("202400328--20250325")).doWrite(list);
CellWriteWidthConfig完整代码：
package org.yaoyao.yaoyaocommon.config;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.*;
/**
* TODO 类作用描述
*
* @author Bryson
* @date 2025/3/11
*/
public class CellWriteWidthConfig extends AbstractColumnWidthStyleStrategy implements CellWriteHandler {
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
private String timeScope;
public CellWriteWidthConfig(String timeScope) {
this.timeScope = timeScope;
}
private Integer defaultWidth = 18;
@Override
public void beforeCellCreate(CellWriteHandlerContext context) {
if (Objects.nonNull(context.getHeadData())) {
List<String> headNameList = context.getHeadData().getHeadNameList();
Properties properties = new Properties();
if (StringUtils.isEmpty(timeScope)) {
timeScope = "标题";
}
properties.setProperty("标题", timeScope);
if (CollectionUtils.isNotEmpty(headNameList)) {
for (int i = 0; i < headNameList.size(); i++) {
if (headNameList.get(i).contains("[标题]")) {
headNameList.set(i, headNameList.get(i).replaceAll("\\[标题\\]", timeScope));
}
}
}
}
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead || CollectionUtils.isNotEmpty(cellDataList)) {
Map<Integer, Integer> widthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = dataLength(cellDataList, cell, isHead);
if (columnWidth > 0) {
if (columnWidth > defaultWidth) {
columnWidth = defaultWidth;
}
Integer maxColumnWidth = widthMap.get(cell.getColumnIndex());
if (null == maxColumnWidth || columnWidth > maxColumnWidth) {
widthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead || CollectionUtils.isNotEmpty(cellDataList)) {
Map<Integer, Integer> widthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = dataLength(cellDataList, cell, isHead);
if (columnWidth > 0) {
if (columnWidth > defaultWidth) {
columnWidth = defaultWidth;
}
Integer maxColumnWidth = widthMap.get(cell.getColumnIndex());
if (null == maxColumnWidth || columnWidth > maxColumnWidth) {
widthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
WriteCellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (null == type) {
return -1;
} else {
switch (type) {
case STRING:
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ? cellData.getStringValue().substring(0, index).getBytes().length + 1 :
cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
SysUser实体类ExcelProperty添加表头：
/**
* 部门ID
*/
@ExcelProperty(value = {"[标题]", "部门ID"}, index = 0)
@ColumnWidth(30)//设置列宽
private Long deptId;
/**
* 用户账号
*/
@ExcelProperty(value = {"[标题]", "用户账号"}, index = 1)
private String userName;
浙公网安备 33010602011771号