excel的导入与导出---通用版

excel的导入与导出---通用版,第1张

excel的导入导出---通用版 excel的导入与导出---通用版

web项目关于导入导出的业务场景很常见,最近我就又遇到了这个业务场景。


这次将最近半个月做的导入导出总结一下

使用的pom如下,主要还是阿里巴巴的easyexcel依赖。


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.lxiaol</groupId>
<artifactId>excel_demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excel_demo</name>
<description>excel_demo</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency> <dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency> <!--easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency> <dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.67</version>
</dependency>
</dependencies> <build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build> </project>

项目结构如下,一个很简单的小demo

导入

因为我的业务不只是一个模块用到导入,所以定义了一个泛型类。


CommonExcelListener具体代码如下

package cn.lxiaol.excel_demo.common.listeners;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j; import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects; /**
* T 是读取到的一行数据转换为的目标类
* @author lxiaol
* @date 2021年08月15日 14:14
*/
@Slf4j
public class CommonExcelListener<T> extends AnalysisEventListener<T> { /**存放解析到的数据,给了一个初始容量,为了避免list的频繁扩容带来的性能问题*/
private final List<T> list = new ArrayList<>(1000); /**解析每一行都会执行该方法*/
@Override
public void invoke(T data, AnalysisContext analysisContext) {
log.info("解析到一条数据:" + data.toString());
this.list.add(data);
} /**解析完成*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("解析完成:共解析到{}数据", this.list.size());
} /**解析表头*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("解析到一条头数据:{}, currentRowHolder: {}", headMap.toString(), context.readRowHolder().getRowIndex());
headMap.entrySet().removeIf((h) -> Objects.isNull(h.getValue()) || "".equals(h.getValue()));
log.info("表头列总数:{},列头为:{}", headMap.size(), headMap.values());
} public List<T> getList() {
return this.list;
} }

假设我这里是用户的账号和手机号的导入,对应的实体类如下:

package cn.lxiaol.excel_demo.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data; /**
* @author lxiaol
* @date 2021年08月15日 14:16
*/
@Data
public class UserExcelDto {
@ExcelProperty(value = "用户名", index = 0)
private String username; @ExcelProperty(value = "手机号", index = 1)
private String phone; @ExcelIgnore
private String password;
}

那么该如何使用呢?下面来简单讲一下,so easy~

首先创建导入所需要的controller

package cn.lxiaol.excel_demo.controller;

import cn.lxiaol.excel_demo.common.listeners.CommonExcelListener;
import cn.lxiaol.excel_demo.dto.UserExcelDto;
import cn.lxiaol.excel_demo.dto.UserExcelParamDto;
import cn.lxiaol.excel_demo.service.ExcelService;
import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List; /**
* @author lxiaol
* @date 2021年08月15日 14:07
*/
@RestController
@RequestMapping("/api/excel")
@Slf4j
public class ExcelController { /**导入*/
@PostMapping(value = "/dataImport", headers = "content-type=multipart/form-data")
public String dataImport(@RequestParam("file") MultipartFile file) {
String result = "success";
try {
// 实例化对象并传入泛型类型
CommonExcelListener<UserExcelDto> listener = new CommonExcelListener<>();
// 调用easyexcel的方法,传入文件流,目标类型,和read监听器,
// 设置表头所在行,自动去除空字符,设置读取第几个sheet页,并开始读取
EasyExcel.read(file.getInputStream(), UserExcelDto.class, listener)
.headRowNumber(1).autoTrim(true).sheet(0).doRead();
//读取结束,得到读取到的数据
List<UserExcelDto> list = listener.getList();
if (!list.isEmpty()) {
//.....具体业务逻辑
System.out.println("读取到数据,进行具体的后续 *** 作");
} else {
result = "excel内容不能为空";
} } catch (Exception e) {
log.error("xxxx导入 报错:", e);
result = "excel导入报错,请检查数据是否合规";
}
return result;
} }

简单两行代码,就读取完了excel的内容,相比原生的poi *** 作简单多了,果然是easyexcel

拿到读取来的数据,就可以做具体的业务了。


导出

好,下面再看一下导出,因为导出也是多个模块都有,所以也写了公用的工具类

package cn.lxiaol.excel_demo.common.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors; /**
* @author lxiaol
* @date 2021年08月15日 14:35
*/
@Data
public class ExcelExportUtil { //表头
private String title;
//各个列的表头
private String[] heardList;
//各个列的元素key值
private String[] heardKey;
//需要填充的数据信息
private List<Map> data;
//字体大小
private int fontSize = 14;
//行高
private int rowHeight = 30;
//列宽
private int columWidth = 200;
//工作表
private String sheetName = "sheet1"; /**
* 开始导出数据信息
*/
public void exportExport(HttpServletResponse response) throws IOException {
//创建工作簿
Workbook wb = new XSSFWorkbook();
//创建工作表
Sheet sheet = wb.createSheet(this.sheetName);
//设置默认行宽
sheet.setDefaultColumnWidth(20); //设置表头样式,表头居中
CellStyle titleStyle = wb.createCellStyle();
//设置字体
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) this.fontSize);
titleStyle.setFont(titleFont); //在第1行创建rows
Row titleRow = sheet.createRow(0);
//设置列头元素
Cell cellHead; for (int i = 0; i < heardList.length; i++) {
//背景填充色
titleStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//边框
titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
titleStyle.setBorderRight(BorderStyle.THIN);//右边框
cellHead = titleRow.createCell(i);
cellHead.setCellValue(heardList[i]);
cellHead.setCellStyle(titleStyle);
} //开始写入实体数据信息
//设置数据样式
CellStyle dataStyle = wb.createCellStyle();
//设置字体
Font dataFont = wb.createFont();
// font.setFontHeightInPoints((short) this.fontSize);
dataFont.setBold(false);
dataStyle.setFont(dataFont);
int count = 1;
for (Map datum : data) {
Row row = sheet.createRow(count);
Cell cell;
int len = heardKey.length;
for (int j = 0; j < len; j++) {
cell = row.createCell(j);
cell.setCellStyle(dataStyle);
Object valueObject = datum.get(heardKey[j]);
String value;
if (valueObject == null) {
valueObject = "";
}
if (valueObject instanceof String) {
//取出的数据是字符串直接赋值
value = (String) datum.get(heardKey[j]);
} else if (valueObject instanceof Integer) {
//取出的数据是Integer
value = String.valueOf(((Integer) (valueObject)).floatValue());
} else if (valueObject instanceof BigDecimal) {
//取出的数据是BigDecimal
value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
} else {
value = valueObject.toString();
}
cell.setCellValue(Objects.isNull(value) ? "" : value);
}
count++;
}
data.clear(); //导出数据
try (OutputStream os = response.getOutputStream()) {
String fileName = URLEncoder.encode(this.title, StandardCharsets.UTF_8);
//设置Http响应头告诉浏览器下载这个附件
response.setHeader("Content-Disposition", "attachment;Filename=" + fileName + ".xlsx");
wb.write(os);
} catch (Exception ex) {
ex.printStackTrace();
throw new IOException("导出Excel出现严重异常,异常信息:" + ex.getMessage());
} finally {
wb.close();
}
} /**
* 设置导出excel 的信息
* 主要用到了反射,获取类中标注的ExcelProperty注解的字段,
* 然后根据注解的index进行排序
* @param maps
* @return
*/
public static ExcelExportUtil getExcelExportUtil(List<Map> maps, Field[] fields) {
List<Field> fieldList = Arrays.stream(fields)
.filter(field -> {
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null && annotation.index() > -1) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int index = -1;
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
index = annotation.index();
}
return index;
})).collect(Collectors.toList()); List<String> title = new ArrayList<>();
List<String> properties = new ArrayList<>();
fieldList.forEach(field -> {
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
final String[] value = annotation.value();
String tit = value[0];
title.add(tit);
final String name = field.getName();
properties.add(name);
}
}); ExcelExportUtil excelExport = new ExcelExportUtil();
excelExport.setData(maps);
excelExport.setHeardKey(properties.toArray(new String[0]));
excelExport.setFontSize(14);
excelExport.setHeardList(title.toArray(new String[0]));
return excelExport;
}
}

再来看一下controller的导出代码

    @Resource
private ExcelService excelService; /**导出*/
@PostMapping("/dataExport")
public String dataExport(@RequestBody UserExcelParamDto dto, HttpServletRequest request, HttpServletResponse response) {
String result = "success";
try {
// .....调用具体的业务方法
excelService.export(dto, request, response);
} catch (Exception e) {
e.printStackTrace();
log.error("安装上线导出 接口报错:", e);
result = "excel导出报错,请检查数据是否合规";
}
return result;
}

根据前台传过来的筛选条件,调用service层的业务方法

package cn.lxiaol.excel_demo.service;

import cn.lxiaol.excel_demo.common.utils.ExcelExportUtil;
import cn.lxiaol.excel_demo.dto.UserExcelDto;
import cn.lxiaol.excel_demo.dto.UserExcelParamDto;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map; /**
* @author lxiaol
* @date 2021年08月15日 14:31
*/
@Service
@Slf4j
public class ExcelService { /**
* 导出根据筛选条件查询到的数据
*
* @param dto 筛选条件
* @param request
* @param response
* @throws IOException
*/
public void export(UserExcelParamDto dto, HttpServletRequest request,
HttpServletResponse response) throws IOException { // 模拟从数据库查询到10条数据,真是业务可将这段改为查询数据库获取到list
List<UserExcelDto> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
UserExcelDto userExcelDto = new UserExcelDto();
userExcelDto.setUsername("用户:" + i);
userExcelDto.setUsername("手机号:188****8888");
list.add(userExcelDto);
}
// 将 list 数据存放到maps中,主要是为了导出时根据表头填写对应的值
List<Map> maps = new ArrayList<>();
list.forEach(po -> maps.add(JSONObject.parseObject(JSONObject.toJSONString(po), Map.class))); // 获取到excel导出工具类,并根据传入class设置了表头信息
ExcelExportUtil excelExportUtil =
ExcelExportUtil.getExcelExportUtil(maps, UserExcelDto.class.getDeclaredFields()); excelExportUtil.setTitle("Excel导出_" + DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now()));
excelExportUtil.exportExport(response);
}
}

重点是理解excelExportUtil.exportExport(response)方法。


总的来说 有了easyexcel的加持,excel 的导入到处还是很简单的。


示例代码在码云上,传送门--->ゝ李大龙

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/586634.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-04-12
下一篇 2022-04-12

发表评论

登录后才能评论

评论列表(0条)

保存