EasyExcel导入导出多sheet页

EasyExcel导入导出多sheet页

依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
    <scope>compile</scope>
</dependency>

导入导出类

@Data
public class Export implements Serializable {
    private static final long serialVersionUID = 1L;
​
    @ExcelProperty(value = "value")
    @ApiModelProperty(value = "value")
    private String value;
​
    @ExcelProperty(value = "id")
    @ApiModelProperty(value = "id")
    private String id;
}

监听器

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) {
    }
}

导入

@GetMapping("/import")
public void import(MultipartFile file) {
    imports(file);
}
​
public void import(MultipartFile file) {
    InputStream inputStream = null;
    try {
        inputStream = file.getInputStream();
        ExcelListener listener = new ExcelListener();
        ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
        // 第一个sheet读取类型
        ReadSheet sheet0 = EasyExcel.readSheet(0).head(Export.class).build();
        // 第二个sheet读取类型
        ReadSheet sheet1 = EasyExcel.readSheet(1).head(Export.class).build();
        // 开始读取第一个sheet数据
        excelReader.read(sheet0);
        List<Object> sheetData0 = listener.getDatas();
        log.info("sheetData0:{}", JSONObject.toJSONString(sheetData0));
        // 清空上次读取数据
        listener.getDatas().clear();
        // 开始读取第二个sheet数据
        excelReader.read(sheet1);
        List<Object> sheetData1 = listener.getDatas();
        log.info("sheetData1:{}", JSONObject.toJSONString(sheetData1));
    } catch (Exception e) {
        log.error("导入异常：{}", e.getMessage());
    } finally {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

导出

@GetMapping("/export")
public void export(HttpServletResponse response, @RequestParam("ids") String ids) {
   export(response, ids);
}
​
public void export(HttpServletResponse response, String ids) {
   List<String> idList = Arrays.asList(ids.split(","));
   List<Export> datas = service.queryByIds(ids);
   OutputStream outputStream = null;
   try {
       outputStream = response.getOutputStream();
       ExcelWriter writer = EasyExcel.write(outputStream).build();
 
       WriteSheet sheet0 = EasyExcel.writerSheet(0, "第一页").head(Export.class).build();
       writer.write(datas, sheet0);
​
       WriteSheet sheet1 = EasyExcel.writerSheet(1, "第二页").head(Export.class).build();
       writer.write(datas, sheet1);
       
       String fileName = URLEncoder.encode("导出", String.valueOf(StandardCharsets.UTF_8));
       response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(String.valueOf(StandardCharsets.UTF_8));
        writer.finish();
        outputStream.flush();
  
    } catch (Exception e) {
        log.error("导出异常：{}", e.getMessage());
    } finally {
        if (outputStream != null) {
            try {
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

参考自：https://blog.csdn.net/weixin_42771651/article/details/116490685