【Excel】使用 SpringBoot 实现 Excel 文件的导入与导出

最新推荐文章于 2024-08-15 00:07:53 发布

竹林幽深

最新推荐文章于 2024-08-15 00:07:53 发布

阅读量1.7k

点赞数

文章标签： excel spring boot java

原文链接：https://blog.csdn.net/sco5282/article/details/121303881

版权

本文详细介绍了如何在SpringBoot应用中使用EasyExcel库处理Excel文件的导入与导出。导入过程包括选择文件、上传到服务器、解析数据并存入数据库；导出则涉及查询数据、写入Excel并提供下载。文章还涉及到线程池的使用，以异步处理数据库操作，并提供了文件上传和删除的辅助方法。

摘要由CSDN通过智能技术生成

sco5282

已于 2023-03-10 10:48:56 修改

9825
收藏 63
分类专栏：课外趣学 SpringBoot 文章标签： spring boot java spring
版权

华为云开发者联盟
该内容已被华为云开发者联盟社区收录
加入社区

课外趣学
同时被 2 个专栏收录
19 篇文章2 订阅
订阅专栏

SpringBoot
30 篇文章0 订阅
订阅专栏
为了大家能更好地理解这篇博文，在看之前，建议去看看我的上一篇博文：【File】使用 SpringBoot 实现文件的上传与下载。因为，这篇博文是在上一篇博文基础之上继续开发的。

这两篇博文互有联系：文件的导入就是文件的上传；文件的导出就是文件的下载。

至于为什么是导入/导出 Excel 文件呢？因为 Excel 文件用的频率更多吧。顺便来复习下通过 EasyExcel 来操作 Excel 文件。

编码思路：

Excel 导入：

浏览文件夹，选择需要上传的 Excel 文件，这里使用 POSTMAN 工具；
将本地文件上传至服务器指定位置；
服务器解析Excel文件；
将Excel中解析的数据存入数据库中。
Excel 导出

设定查询条件；
数据库中查询相应的数据；
将数据写入Excel；
将 Excel 下载至本地。
导入 POM 依赖：

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

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.5</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
1. Excel 导入
FileController：添加一个导入接口

@RestController
@RequestMapping("/file")
public class FileController {

@Autowired
private FileService fileService;

@PostMapping("/importExcel")
public ResultVo importExcel(@RequestParam("file") MultipartFile excel) {
return fileService.importExcel(excel);
}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
FileServiceImpl：

@Service
@Slf4j
public class FileServiceImpl implements FileService {

@Autowired
private ExcelUtil excelUtil;

@Override
public ResultVo importExcel(MultipartFile file) {
// 1.入参校验
ResultVo<String> checkExcelParam = checkExcelParam(file);
if (!checkExcelParam.checkSuccess()) {
log.error(checkExcelParam.getMsg());
return checkExcelParam;
}
// 2.上传至服务器某路径下
ResultVo resultVo = uploadFile(file);
if (!resultVo.checkSuccess()) {
return resultVo;
}
String filePath = (String)resultVo.getData();
if (StringUtil.isBlank(filePath)) {
return ResultVoUtil.error("【导入Excel文件】生成的Excel文件的路径为空");
}
// 3.读取excel文件
List<ExcelVo> excelVos = excelUtil.simpleExcelRead(filePath, ExcelVo.class);
if (CollectionUtil.isEmpty(excelVos) || excelVos.size() < 2) {
log.error("【导入Excel文件】上传Excel文件{}为空", file.getOriginalFilename());
return ResultVoUtil.error("上传Excel文件为空");
}
// 4.通过线程池开启一个线程去执行数据库操作，主线程继续往下执行
// 4.1开启一个线程
TaskCenterUtil taskCenterUtil = TaskCenterUtil.getTaskCenterUtil();
taskCenterUtil.submitTask(() -> {
log.info("【批量添加】批量添加数据：{}", JSON.toJSONString(excelVos));
return null;
});
// 4.2删除临时文件
boolean deleteFile = FileUtil.deleteFile(new File(filePath));
if (!deleteFile) {
log.error("【导入Excel文件】删除临时文件失败，临时文件路径为{}", filePath);
return ResultVoUtil.error("删除临时文件失败");
}
log.info("【导入Excel文件】删除临时文件成功，临时文件路径为：{}", filePath);
return ResultVoUtil.success(excelVos);
}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
说明：

List<ExcelVo> excelVos = excelUtil.simpleExcelRead(filePath, ExcelVo.class); 你给我一个Excel文件路径，和一个数据类型，我就能将这个Excel文件中的数据封装成一个集合，并返回给你；
TaskCenterUtil taskCenterUtil = TaskCenterUtil.getTaskCenterUtil();获取了一个线程池；taskCenterUtil.submitTask() 另开启了一个新线程去执行其它操作；原来的线程继续往下执行
checkExcelParam()：校验入参

public ResultVo<String> checkExcelParam(MultipartFile file) {
log.info("【上传Excel文件】进入到上传Excel文件方法...");
if (null == file || file.isEmpty()) {
log.error("【上传Excel文件】上传的文件为空，file={}", file);
throw new ParamErrorException();
}
boolean b = ExcelUtil.checkExcelExtension(file);
if (!b) {
return ResultVoUtil.error("上传的不是Excel文件，请上传正确格式的Excel文件");
}
return ResultVoUtil.success();
}
1
2
3
4
5
6
7
8
9
10
11
12
uploadFile()：这个上传文件的方法，还是使用的上一篇博文的方法

ExcelUtil：

@Component
@Slf4j
public class ExcelUtil<T> {

// excel文件后缀
private final static String EXCE_L2003 = "xls";
private final static String EXCEL_2007 = "xlsx";

// 校验文件后缀是否为 xls、xlsx
public static boolean checkExcelExtension(MultipartFile excel) {
String filename = excel.getOriginalFilename();
if (StringUtil.isBlank(filename)) {
log.info("【校验Excel文件后缀】Excel文件名为空");
return false;
}
int index = filename.lastIndexOf(".");
if (index == -1) {
log.info("【校验Excel文件后缀】Excel文件名中没有点号");
return false;
}
String extension = filename.substring(index + 1);
return Arrays.asList(EXCE_L2003, EXCEL_2007).contains(extension);
}

// 读取excel文件
public List<T> simpleExcelRead(String filePath, Class<T> clazz) {
ExcelListener<T> excelListener = new ExcelListener();
EasyExcel.read(filePath, clazz, excelListener).sheet().doRead();
List<T> dataList = excelListener.getDataList();
return dataList;
}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
说明：

EasyExcel.read(filePath, clazz, excelListener).sheet().doRead(); 会调用 ExcelListener 类中的方法，将它读取到的数据存储在 dataList 集合中；
这里 ExcelUtil 类用的是泛型，你要导入什么类型的数据，就传什么类型的数据。我这里导入的是 ExcelVo 类型。
ExcelListener：

@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {

// 返回读取到的excel中的数据
List<T> dataList = new ArrayList<>();

public ExcelListener() {
}

// 每一条数据解析都会来调用
@Override
public void invoke(T t, AnalysisContext analysisContext) {
log.info("【Excel文件】解析到一条数据{}:", JSON.toJSONString(t));
dataList.add(t);
}

// 所有数据解析完成了才会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("【Excel文件】Excel所有数据解析完毕！");
}

public List<T> getDataList() {
return dataList;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
ExcelVo：Excel 中数据信息的模板

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelVo {
// 姓名
private String name;
// 性别 1：女 0：男
@ExcelProperty(converter = SexConvert.class)
private Integer sex;
// 创建时间
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
private String createTime;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
说明：

ExcelVo 中的属性和 Excel 文件对应
性别 sex 属性，我们使用了一个转换器。Excel 文件中性别对应的是男、女，但我们要用整形0、1 去接收它，所以，需要自定义一个性别转换器
Excel 内容：

SexConvert：性别转换器

public class SexConvert implements Converter<Integer> {

@Override
public Class supportJavaTypeKey() {
return Integer.class;
}

@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}

// 这里读的时候会调用
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
switch (cellData.getStringValue()) {
case "男":
return 0;
case "女":
return 1;
default:
return 0;
}
}

// 这里写的时候会调用
@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
switch (integer) {
case 1:
return new CellData("女");
case 0:
return new CellData("男");
default:
return new CellData(String.valueOf(integer));
}
}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
TaskCenterUtil：线程池工具类

public class TaskCenterUtil {

public static Integer CORE_POOL_SIZE = 10;
public static Integer MAX_NUM_POOL_SIZE = 10;
public static Integer MAX_MESSAGE_SIZE = 100;
public static Long KEEP_ALIVE_TIME = 60L;

private ThreadPoolExecutor poolExecutor = new ThreadPoolExecutor(CORE_POOL_SIZE, MAX_NUM_POOL_SIZE, KEEP_ALIVE_TIME,
TimeUnit.SECONDS, new LinkedBlockingQueue<>(MAX_MESSAGE_SIZE), new ThreadPoolExecutor.CallerRunsPolicy());

private TaskCenterUtil() {}

private static TaskCenterUtil taskCenterUtil = new TaskCenterUtil();

public static TaskCenterUtil getTaskCenterUtil() {
return taskCenterUtil;
}

public void submitTask(Callable task) {
poolExecutor.submit(task);
}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
FileUtil.deleteFile()：删除临时文件

// 递归删除目录下的所有文件及子目录下所有文件
public static boolean deleteFile(File file) {
if (!file.exists()) {
return false;
}
if (file.isDirectory()) {
String[] children = file.list();
//递归删除目录中的子目录下
for (int i=0; i<children.length; i++) {
boolean success = deleteFile(new File(file, children[i]));
if (!success) {
return false;
}
}
}
// 目录此时为空，可以删除
return file.delete();
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2. Excel 导出
FileController：添加一个文件导出接口

@PostMapping("/exportExcel")
public ResultVo exportExcel(final HttpServletResponse response) {
return fileService.exportExcel(response);
}
1
2
3
4
FileServiceImpl：

@Override
public ResultVo<String> exportExcel(HttpServletResponse response) {
// 1.根据查询条件获取结果集
List<ExcelWriteVo> excelWriteVos = getExcelWriteVoListByCondition();
if (CollectionUtil.isEmpty(excelWriteVos)) {
log.info("【导出Excel文件】要导出的数据为空，无法导出！");
return ResultVoUtil.success("数据为空");
}
// 2.获取要下载Excel文件的路径
ResultVo<String> resultVo = getDownLoadPath(ExcelWriteVo.class, excelWriteVos);
if (!resultVo.checkSuccess()) {
log.error("【导出Excel文件】获取要下载Excel文件的路径失败");
return resultVo;
}
// 3.下载Excel文件
String fileDownLoadPath = resultVo.getData();
ResultVo<String> downLoadResultVo = downloadFile(fileDownLoadPath, response);
if (null != downLoadResultVo && !downLoadResultVo.checkSuccess()) {
log.error("【导出Excel文件】下载文件失败");
return downLoadResultVo;
}
// 4.删除临时文件
boolean deleteFile = FileUtil.deleteFile(new File(fileDownLoadPath));
if (!deleteFile) {
log.error("【导入Excel文件】删除临时文件失败，临时文件路径为{}", fileDownLoadPath);
return ResultVoUtil.error("删除临时文件失败");
}
log.info("【导入Excel文件】删除临时文件成功，临时文件路径为：{}", fileDownLoadPath);
return null;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
getExcelWriteVoListByCondition()：根据条件获取要导出的数据

这里本应该通过数据库查询的，我这里直接用假数据了。

public List<ExcelWriteVo> getExcelWriteVoListByCondition() {
List<ExcelWriteVo> excelWriteVos = new ArrayList<>(5);
excelWriteVos.add(new ExcelWriteVo("zzc", "男", "2021-11-14 20:00:00"));
excelWriteVos.add(new ExcelWriteVo("wzc", "女", "2021-11-14 20:00:00"));
excelWriteVos.add(new ExcelWriteVo("wxc", "男", "2021-11-14 20:00:00"));
return excelWriteVos;
}
1
2
3
4
5
6
7
ExcelWriteVo ：Excel 文件导出对应的Vo类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelWriteVo {

// 姓名
@ExcelProperty("姓名")
private String name;

// 性别 1：女 0：男
@ExcelProperty("性别")
private String sex;

// 创建时间
@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
@ExcelProperty("创建时间")
private String createTime;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
导出的 Excel：

getDownLoadPath()：获取要下载Excel文件的路径

public ResultVo<String> getDownLoadPath(Class<ExcelWriteVo> clazz, List<ExcelWriteVo> excelWriteVos) {
String downLoadPath = FileUtil.getDownLoadPath();
if (StringUtil.isBlank(downLoadPath)) {
log.error("【导出Excel文件】生成临时文件失败");
return ResultVoUtil.error("生成临时文件失败");
}
// 1.创建一个临时目录
FileUtil.mkdirs(downLoadPath);
String fullFilePath = downLoadPath + File.separator + System.currentTimeMillis() + "." + ExcelUtil.EXCEL_2007;
log.info("【导出Excel文件】文件的临时路径为：{}", fullFilePath);
// 2.写入数据
excelUtil.simpleExcelWrite(fullFilePath, clazz, excelWriteVos);
return ResultVoUtil.success(fullFilePath);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
application.yml：添加了一个文件下载时生成文件的路径

file:
uploadPath: E:/upload
downloadPath: E:/download
1
2
3
FileUtil：

// 获取文件下载时生成文件的路径
public static String getDownLoadPath() {
return fileConfig.getDownloadPath();
}
1
2
3
4
ExcelUtil：

@Slf4j
public class ExcelUtil<T> {

// excel文件后缀
public final static String EXCE_L2003 = "xls";
public final static String EXCEL_2007 = "xlsx";

// sheet名字
public final static String SHEET_NAME = "模板";

   // 写Excel文件
   public void simpleExcelWrite(String filePath, Class<T> clazz, List<T> dataList) {
EasyExcel.write(filePath, clazz).sheet(SHEET_NAME).doWrite(dataList);
}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
好了，通过 EasyExcel 文件操作 Excel 就到这了哈。

===========================================================================================
2021-12-07 更：

使用 easyexcel 设置超链接或附件地址

参考地址

修改 POM 依赖：修改 easyexcel 的版本。2.0.0-beta2 版本无法引入 AbstractCellWriteHandler

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
1
2
3
4
5
ExcelWriteVo：添加一个字段，表示附件

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelWriteVo {
   // ...

   // 附件
private String photo;
}
1
2
3
4
5
6
7
8
9
FileServiceImpl#getExcelWriteVoListByCondition()：修改构造方法

public List<ExcelWriteVo> getExcelWriteVoListByCondition() {
List<ExcelWriteVo> excelWriteVos = new ArrayList<>(5);
excelWriteVos.add(new ExcelWriteVo("zzc", "男", "2021-11-14 20:00:00", "附件1"));
excelWriteVos.add(new ExcelWriteVo("wzc", "女", "2021-11-14 20:00:00", "附件1"));
excelWriteVos.add(new ExcelWriteVo("wxc", "男", "2021-11-14 20:00:00", "附件1"));
return excelWriteVos;
}
1
2
3
4
5
6
7
添加一个处理器 PhotoHandler：

@Slf4j
public class PhotoHandler extends AbstractCellWriteHandler {

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 这里可以对cell进行任何操作
log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
if (isHead && cell.getColumnIndex() == 0) { // 对第一行第一列的头超链接
CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://github.com/alibaba/easyexcel");
cell.setHyperlink(hyperlink);
}
if (!isHead && head.getFieldName().equals("photo")) { // 头为 photo 的列设置附件地址
CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.FILE); // 类型设置为 FILE
hyperlink.setAddress("C:/Users/07979/Pictures/1.jpg"); // 附件的地址（相对地址、绝对地址都行）
cell.setHyperlink(hyperlink);
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
导出 Excel 时，需要注册这个处理器：

ExcelUtil#()：

@Component
@Slf4j
public class ExcelUtil<T> {
// ...

   public void simpleExcelWrite(String filePath, Class<T> clazz, List<T> dataList) {
EasyExcel.write(filePath, clazz)
       .registerWriteHandler(new PhotoHandler())
       .sheet(SHEET_NAME).doWrite(dataList);
}
}
1
2
3
4
5
6
7
8
9
10
11
说明：

可以注册多个处理器。如：
EasyExcel.write(filePath, clazz)
       .registerWriteHandler(new PhotoHandler())
       .registerWriteHandler(new PhotoHandler2())
       .sheet(SHEET_NAME).doWrite(dataList);
1
2
3
4
运行代码后：

鼠标点击，第一列的头，可跳转；

鼠标点击“photo” 这一列，也可跳转。

===========================================================================================
2023-01-09 更：

EasyExcel 导出 Excel 设置单元格文本格式：EasyExcel导出Excel设置单元格文本格式(亲测有效)

只对有数据的行有效：

法1：

@ExcelProperty(value = "创建时间")
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, dataFormat = 49)
private String createTime;
1
2
3
法2：

public class ExcelCellWriteHandler implements CellWriteHandler {

@Override
public void afterCellDispose(CellWriteHandlerContext context) {
// 3.0 设置单元格为文本
WriteCellData<?> cellData = context.getFirstCellData();
WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
DataFormatData dataFormatData = new DataFormatData();
dataFormatData.setIndex((short) 49);
writeCellStyle.setDataFormatData(dataFormatData);
}

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 3.0 设置单元格为文本
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
DataFormat dataFormat = workbook.createDataFormat();
for (WriteCellData<?> writeCellData : cellDataList) {
WriteCellStyle writeCellStyle = writeCellData.getOrCreateStyle();
DataFormatData dataFormatData = new DataFormatData();
dataFormatData.setIndex(dataFormat.getFormat("@"));
writeCellStyle.setDataFormatData(dataFormatData);
}
}

}

public static <T> void si
mpleExcelWrite(String filePath, Class<?> clazz, List<T> dataList, String sheetName) {
EasyExcel.write(filePath, clazz).sheet(sheetName).registerWriteHandler(new ExcelCellWriteHandler()).doWrite(dataList);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
文章知识点与官方知识档案匹配，可进一步学习相关知识
————————————————
版权声明：本文为CSDN博主「sco5282」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/sco5282/article/details/121303881