Excel导入导出demo笔记
前言
excel 导入导出的算是项目里很常见的一个功能，以前都是使用POI来，一直知道阿里有个easyExcel 官网 这次尝试去了解了下，感觉确实很方便，上手容易。这里记录下方便后续来捞代码。
pom.xml 依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<!-- swagger -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<!-- swaggwe增强ui -->
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>swagger-bootstrap-ui</artifactId>
<version>1.9.6</version>
</dependency>
代码案例
主要涉及：Excel的更具指定标题进行的导入导出
- 需要有 实体基类使用
easyExcel的相关注解进行修饰- 导入：需要使用监听器，可以在监听器里的
invoke做业务处理- 导出：组装数据然后通过Excel附件的形式进行下载
（一）基础类
要导出的数据类
ExportBusinessMeta,官网的DemoData
/** 官网的 DemoData
* @author lvzb
* @date 2022/11/23 15:45
**/
@Getter
@Setter
@EqualsAndHashCode
public class DemoData implements Serializable {
private static final long serialVersionUID = 7382510541716722119L;
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
/**
* @author lvzb
* @date 2022/11/23 15:34
**/
@Data
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 24)
public class ExportBusinessMeta {
@ExcelProperty("数据库类型")
@ColumnWidth(value = 20)
@HeadStyle(fillForegroundColor = 13)
private String dataBaseType;
@ExcelProperty("所属库")
@ColumnWidth(value = 20)
@HeadStyle(fillForegroundColor = 13)
private String dataBase;
@ExcelProperty("所属表")
@ColumnWidth(value = 20)
@HeadStyle(fillForegroundColor = 13)
private String table;
@ExcelProperty("字段名")
@ColumnWidth(value = 20)
@HeadStyle(fillForegroundColor = 13)
private String column;
@ExcelProperty(value = "英文全称")
@ColumnWidth(value = 25)
private String english_full_name;
/**
* 英文简称
*/
@ExcelProperty("英文简称")
@ColumnWidth(value = 25)
private String english_abbr;
/**
* 业务术语
*/
@ExcelProperty("业务术语")
@ColumnWidth(value = 25)
private String business_term;
/**
* 变量属性
*/
@ExcelProperty("变量属性")
@ColumnWidth(value = 25)
private String variable_properties;
/**
* 含义及用法
*/
@ExcelProperty("含义及用法")
@ColumnWidth(value = 30)
private String meaning_usage;
/**
* 报文域或标签
*/
@ExcelProperty("报文域或标签")
@ColumnWidth(value = 30)
private String messagedomain_label;
/**
* 备注
*/
@ExcelProperty("备注")
@ColumnWidth(value = 15)
private String remark;
/**
* 分类
*/
@ExcelProperty(value = "分类")
private String classification;
/**
* 分级
*/
@ExcelProperty(value = "分级")
private String grading;
}
@Slf4j
public class DemoDAO {
public void save(List<ExportBusinessMeta> list) {
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
if (!CollectionUtils.isEmpty(list)) {
for (ExportBusinessMeta data : list) {
log.warn(">>>>>> data::{}", data);
}
}
}
}
（二）读监听器
读监听器在执行导入解析时会被用到
/**
* @author lvzb
* @date 2022/10/24 17:57
**/
@Slf4j
public class IndexOrNameDataListener implements ReadListener<ExportBusinessMeta> {
private static final int BATCH_COUNT = 100;
private List<ExportBusinessMeta> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private DemoDAO demoDAO;
public IndexOrNameDataListener() {
this.demoDAO = new DemoDAO();
}
public IndexOrNameDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* @param analysisContext
*/
@Override
public void invoke(ExportBusinessMeta data, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", cachedDataList.size());
demoDAO.save(cachedDataList);
log.info("存储数据库成功！");
}
}
（三）导出/下载/导入 controller
/**
* @author lvzb
* @date 2022/11/15 17:37
**/
@Slf4j
@RestController
@Api(tags = "控制层")
@RequestMapping("/swagger")
public class SwaggerTestController {
/**
* demo Data
*
* @return
*/
private List<DemoData> data() {
List<DemoData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
private List<ExportBusinessMeta> busiData() {
List<ExportBusinessMeta> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
ExportBusinessMeta data = new ExportBusinessMeta();
data.setBusiness_term("字符串" + i);
data.setGrading(new Date() + "分级：" + i);
data.setEnglish_full_name("英文全称：" + i);
list.add(data);
}
return list;
}
/**
* 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
*
* @since 2.1.1
*/
@GetMapping("downloadFailedUsingJson")
@ApiOperation(value = "EasyExcel文件下载失败时/JSON返回", tags = "V1")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
EasyExcel.write(response.getOutputStream(), ExportBusinessMeta.class).autoCloseStream(Boolean.FALSE).sheet("模板")
.doWrite(data());
throw new BusinessException("aaaa");
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
@GetMapping("/downLoad")
@ApiOperation(value = "EasyExcel文件下载/导出案例", tags = "V1")
public void downLoad(EchoRequest echoRequest, HttpServletRequest request, HttpServletResponse response) throws IOException {
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("业务元数据导入模板", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// filename*=utf-8'' 是一种协议规范 标识fileName的value也进行编码 具体参考 ：https://juejin.cn/post/6890692829719298061
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
/// 直接的 filename= 则标识fileName的value不进行编码，那么就会在不同的浏览器上导致附件名乱码
// response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
// 设置文件头
WriteSheet sheet = EasyExcel.writerSheet(0, "业务元数据11").head(ExportBusinessMeta.class).build();
// busiData() 换成 new ArrayList()就是变成模版导出了
excelWriter.write(busiData(), sheet);
excelWriter.finish();
}
}
@GetMapping("/downLoadTxt")
@ApiOperation(value = "Txt文件下载案例", tags = "V1")
public void downLoadTxt(HttpServletRequest request, HttpServletResponse response) {
String content = "Hello world \n 你好世界!";
String fileName = URLEncoder.encode("生成的文本", StandardCharsets.UTF_8);
response.setContentType("text/plain");
response.setCharacterEncoding("utf-8");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-disposition", "attachment; filename*=utf8''" + fileName + ".txt");
try (ServletOutputStream ots = response.getOutputStream();
BufferedOutputStream bufferOts = new BufferedOutputStream(ots)) {
bufferOts.write(content.getBytes(StandardCharsets.UTF_8));
bufferOts.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
@PostMapping("/upload")
@ApiOperation(value = "excel上傳解析案例", tags = "V1")
public String upload(MultipartFile file) throws IOException {
// 解析之前做前置校验： 文件大小、文件类型等
// .......
EasyExcel.read(file.getInputStream(), ExportBusinessMeta.class, new IndexOrNameDataListener(new DemoDAO())).sheet().doRead();
return "success";
}
}
（四）扩展(项目内的文件获取方式)
涉及从项目内下载文件，例如某些模板，这里也记录一下。最好使用类似这种方式找。
DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath()拿的是target/classes 开始往下找。
/**
*
* @author lvzb
* @date 2022/10/24 17:16
**/
@Slf4j
public class ReadTest {
public static final String fileName = "业务元数据1.xlsx";
@Test
void testPath() {
// 只到项目的目录上
String workDir = System.getProperty("user.dir");
// C:\Users\zlv11\IdeaProjects\dem-backend
System.out.println(workDir);
}
@SneakyThrows
@Test
void classLoaderGetFilePath() {
// 拿的是target/classes 开始往下找 我们预存在项目的 resources/static/excel
String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/%e4%b8%9a%e5%8a%a1%e5%85%83%e6%95%b0%e6%8d%ae1.xlsx
System.out.println(path);
// 如果路径中带有中文会被URLEncoder,因此这里需要解码
// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/业务元数据1.xlsx
String filePath = URLDecoder.decode(path, "UTF-8");
System.out.println(filePath);
}
@SneakyThrows
@Test
void getFilePath() {
String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/%e4%b8%9a%e5%8a%a1%e5%85%83%e6%95%b0%e6%8d%ae1.xlsx
System.out.println(path);
// toURI.getPath()也能解码中文
// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/业务元数据1.xlsx
System.out.println(DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath());
}
@Test
void readTestMethodOne() {
// 未处理路径上的中文字符会导致如下报错
// com.alibaba.excel.exception.ExcelCommonException: Convert excel format exception.You can try specifying the 'excelType' yourself
String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
System.out.println(path);
String fileName = path;
EasyExcel.read(fileName, ColumnEntityWithBusiness.class, new PageReadListener<ColumnEntityWithBusiness>(dataList -> {
for (ColumnEntityWithBusiness c : dataList) {
log.info("读取到一条数据{}", JSON.toJSONString(c));
}
})).sheet().doRead();
}
@SneakyThrows
@Test
void readTestMethodTwo() {
// String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath();
try (ExcelReader reader = EasyExcel.read(path, ColumnEntityWithBusiness.class, new IndexOrNameDataListener()).build()) {
ReadSheet readSheet = EasyExcel.readSheet(0).build();
reader.read(readSheet);
}
// EasyExcel.read(fileName, ColumnEntityWithBusiness.class, new DemoDataListener()).sheet().doRead();
}
}
扫一扫
4244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
