Java 中使用EasyExcel讲实体类导出成Excel，以及导入Excel

导出

一、引用pom.xml

  1.引入依赖

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

二、编写工具类

  1.在工具类上加注解  @ExcelProperty(value = "开始日期")

@Data
public class DateVO{
    @ExcelProperty(value = "开始日期")
    private String startDate;
    @ExcelProperty(value = "开始时间")
    private String startTime;
    @ExcelProperty(value = "结束日期")
    private String endDate;
    @ExcelProperty(value = "结束时间")
    private String endTime;
    @ExcelProperty(value = "姓名")
    private String name;
    @ExcelProperty(value = "年龄")
    private String age;
    
}

三、可以设置导出自动列宽

  1.编写配置类

//Excel文档的自动列宽设置
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    //因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
    private static final int COLUMN_WIDTH = 2;
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);


    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                } else {
                    if (columnWidth < COLUMN_WIDTH) {
                        columnWidth = columnWidth * 2;
                    }
                }

                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

四、导出成Excel

  1.调用自动列宽，以及导出的实体类导出成Excel

//导出成Excel
public void writeDateExcel(HttpServletResponse response) throws IOException {
        try {
            // 设置响应输出的格式为xlsx文件
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 防止中文乱码
            LocalDate currentDate = LocalDate.now();
            String fileName = URLEncoder.encode("实体类表", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=UTF-8''" + fileName + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "customer-header");
            response.setHeader("customer-header", fileName + ".xlsx");
            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为用户表 然后文件流会自动关闭
           
            List<DateVO> dateExcelList = dateBiz.getDateExcelList();
            //写入数据到响应流
            EasyExcel.write(response.getOutputStream(), DateVO.class)
                    .sheet("实体类表").registerWriteHandler(new ExcelWidthStyleStrategy())
                    .doWrite(dateExcelList);
        }catch (IOException e){
           e.printStackTrace();
        }
    }

五、导出结果

导入

五、导入Excel先创建监听器

  1.创建监听器监听Excel

public class ExcelListener<T> extends AnalysisEventListener<T> {

    //可以通过实例获取该值
    private List<T> datas = new ArrayList<>();

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        datas.add(t);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
    }

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

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // excel解析完毕以后需要执行的代码
    }
}

六、导入Excel进行处理

  1.传入Excel用已有注解的实体类进行接收

public R uploadLuoZi(@RequestParam("file") MultipartFile multipartFile) throws Exception {
		ExcelListener<DateVO> ExcelListener1 = new ExcelListener<DateVO>();
		EasyExcel.read(multipartFile.getInputStream(), DateVO.class,ExcelListener1 ).sheet(0).doRead();
		// 获取数据
		List<DateVO> List1 = ExcelListener1 .getDatas();
		ExcelListener<DateVO2> ExcelListener2 = new ExcelListener<LuoZiRoadShowVo>();
		EasyExcel.read(multipartFile.getInputStream(), DateVO2.class,ExcelListener2 ).sheet(1).doRead();
		// 获取数据
		List<DateVO2> List2 = luoZiRoadShowVoExcelListener2.getDatas();
		//处理数据
		HashMap<String, Object> hm = excelService.uploadLuoZi(List1,List2);
		return R.ok(hm);
}