EasyExcel读写Excel表格

EasyExcel使用

一、读取Excel

1.1 读取Excel，转换为map

1. pom 文件

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

2. 监听器
   读取Excel中的内容转换为map

   import com.alibaba.excel.context.AnalysisContext;
   import com.alibaba.excel.event.AnalysisEventListener;
   import com.alibaba.fastjson.JSON;
   
   
   import lombok.extern.slf4j.Slf4j;
   import org.springframework.stereotype.Component;
   
   import java.util.*;
   
   /**
    * @author zxg
    * @date Created in 2021/08/26 10:50
    * Description
    */
   @Component
   @Slf4j
   public class ExcelUploadListener extends AnalysisEventListener<LinkedHashMap> {
   
       public ExcelUploadListener() {}
   
       //读取表头
       @Override
       public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
           // 读取第一行
           log.info("解析到一条数据：{}", JSON.toJSONString(headMap));
       }
   
       @Override
       public void invoke(LinkedHashMap data, AnalysisContext context) {
           log.info("解析到一条数据：{}", JSON.toJSONString(data));
       }
   
       @Override
       public void doAfterAllAnalysed(AnalysisContext context) {
           log.info("所有数据解析完成！开始保存数据库");
           saveData();
       }
   
       /**
        * 数据入库
        */
       private void saveData() {
          // 入库
       }
   }
   
   

3. 文件读取

   private void excelUpload( MultipartFile file) {
    
       try (InputStream stream = file.getInputStream()) {
           ExcelReaderBuilder read = read(stream, new ExcelUploadListener());
           ExcelReaderSheetBuilder sheet = read.sheet();
           sheet.doRead();
       } catch (IOException e) {
           log.error("excel file upload error, error msg <{}>", e.getMessage(), e);
       }
   }
   

1.2、读取Excel，每行转换为实体类

1. 实体类

   @Data
   @TableName("td_user")
   public class Person implements Serializable {
   
       // Excel 首行表头名称
       @ExcelProperty("姓名")
       private String name;
       
       @ExcelProperty("年龄")
       private Integer age;
       
       @ExcelProperty("性别")
       private String gender;
   }
   

2. 监听器

   @Slf4j
   public class ExcelUploadLister extends AnalysisEventListener<Person> {
   
       ArrayList<Person> dataList = new ArrayList<>();
       
       private final static Integer EXCEL_BATCH_INSERT_DB_NUMBER = 1000;
   
       public SubSmsPortInputLister() {
      
       }
   
       @Override
       public void invoke(Person data, AnalysisContext context) {
   
           log.info("data:{}",data);
           dataList.add(data);
           fileRow++;
           if (dataList.size() >= EXCEL_BATCH_INSERT_DB_NUMBER) {
               saveData();
               dataList.clear();
           }
       }
       
       @Override
       public void doAfterAllAnalysed(AnalysisContext context) {
           saveData();
           dataList.clear();
           log.warn("所有数据解析完成！");
       }
   
       private void saveData() {
           log.warn("{}条数据，开始存储数据库！", dataList.size());
           PersonService.saveOrUpdateBatch(dataList);
           log.warn("存储数据库成功！");
       }
   }
   

二、写入Excel

2.1、写入Excel文件

String fileName = "/tempData/test.xlsx";

ExcelWriter excelWriter=null;
try{
    // 写入Excel文件
    excelWriter = EasyExcel.write(fileName, Person.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet("用户信息").build();
    
        //data()为要写入的数据
        excelWriter.write(data(),writeSheet);
}catch (Exception e) {
    log.error("download fail data fail，msg <{}>",e.getMessage(),e);
    throw new CustomException(CustomEnum.FILE_DOWNLOAD_FAIL);
}
finally {
    if(excelWriter != null){
        excelWriter.finish();
    }
}

2.2 写入response直接下载

/**
 * 导出Excel文件
 *
 * @param response HttpServletResponse
 * @param name 文件名
 * @param type 导出数据对象
 * @param data 导出数据
 */
public static void excelExport(HttpServletResponse response, String name, Class<?> type, List<?> data) {
    ExcelWriter excelWriter = null;
    try {
        String time = StringUtil.getTimeString(Constants.FILE_NAME_DATE_PATTERN_YYYY_MM_DD, new Date());
        // 文件名编码，防止乱码
        String fileName = URLEncoder.encode(name, Constants.CHAR_SET_UTF_8);
        response.setCharacterEncoding(Constants.CHAR_SET_UTF_8);
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + "_" + time + ".xls");

        // 写入Excel文件
        excelWriter = EasyExcelFactory.write(response.getOutputStream(), type).build();
        WriteSheet writeSheet = EasyExcelFactory.writerSheet(name)
                .registerWriteHandler(new ExcelWidthStyleStrategy())
                .build();
        for (int index = 0; index < data.size(); index += BATCH_LINE) {
            // 写入流
            excelWriter.write(data.subList(index, Integer.min(index + BATCH_LINE, data.size())), writeSheet);
        }
    } catch (Exception e) {
        log.error("download fail data fail，msg <{}>", e.getMessage(), e);
        throw new CustomException(CustomEnum.FILE_DOWNLOAD_FAIL);
    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}