easyexcel导入导出多个sheet

EasyExcel对于导入导出的操作十分简洁,记录一下多个sheet且内容不一致的导入导出。

  1. 导出下载

提示:其中部分代码操作Dao层可以删除,可以自己创建ExportUserExcel 对象进行测试,思路数据映射到excel中。
这里ExportUserExcel .class 映射的模板替换下面代码中

@AllArgsConstructor
@NoArgsConstructorUser
@Builder
@HeadRowHeight(value = 20)
public class ExportUserExcel {

    @ExcelProperty(value = "姓名",index = 0)
    @ColumnWidth(value = 10)
    private String userName;

    @ExcelProperty(value = "年龄",index = 1)
    @ColumnWidth(value = 20)
    private String age;

    @ExcelProperty(value = "性别",index = 2)
    @ColumnWidth(value = 20)
    private String gender;
}

  1. 导出下载 ExportUserExcel.class 模板
 public void modelExport(HttpServletResponse response, Long id) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        try {
            String fileName = URLEncoder.encode("Physical", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //新建ExcelWriter
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            //获取sheet0对象
            WriteSheet mainSheet = EasyExcel.writerSheet(0, "模型信息").head(ExportUserExcel.class).build();
            //获取模型信息,向sheet0写入数据
            DmmPhysicalModel dmmPhysicalModel = dmmPhysicalModelMapper.selectById(id);
            List<DmmPhysicalModel> dmmPhysicalModels1 = Arrays.asList(dmmPhysicalModel);
            excelWriter.write(dmmPhysicalModels1, mainSheet);
            //获取sheet1对象
            WriteSheet detailSheet = EasyExcel.writerSheet(1, "词条信息").head(ExportUserExcel.class).build();
            HashMap entryHashMap = new HashMap<>();
            entryHashMap.put("model_id", id);
            //获取词条信息,向sheet1写入数据
            List<DmmPhysicalEntry> list = dmmPhysicalEntryMapper.selectByMap(entryHashMap);
            excelWriter.write(list, detailSheet);
            //关闭流
            excelWriter.finish();
        } catch (IOException e) {
            log.error("导出异常{}", e.getMessage());
        }
    }
  1. 导入excel 存入数据库,

这里很多博客的代码没有贴全,少了一个监视器读取excel的类!

package com.infinitus.dmm.service.impl;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

/**
 * excel表格读取监视器
 */
public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);
        doSomething(o);
    }

    private void doSomething(Object object) {
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

2.excel导入到数据库 多个sheet、
ModelImportVO.class这也是模板,自己可以定义,对应的值。

 public void modelImport(MultipartFile serviceFile, ContrastModelEntryDTO contrastModelEntryDTO) throws IOException {
        //输入流
        InputStream inputStream = serviceFile.getInputStream();
        //监视器
        ExcelListener listener = new ExcelListener();
        ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
        // 第一个sheet读取类型
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(ModelImportVO.class).build();
        // 第二个sheet读取类型
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(EntryImportVO.class).build();
        // 开始读取第一个sheet
        excelReader.read(readSheet1);
        //excel sheet0 信息
        List<Object> list = listener.getDatas();
      
        // 清空之前的数据
        listener.getDatas().clear();
        // 开始读取第二个sheet
        excelReader.read(readSheet2);
        //excel sheet1 信息
        List<Object> entry = listener.getDatas();
   
       
    }
上一篇:概述


下一篇:EasyExcel导出小结:动态标题、标题格式、相同值合并