java-easyExcel导入导出Demo

[ichengzi]

https://github.com/alibaba/easyexcel

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

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;

@Data
public class Student{
// index 可以指定导出时的排序
// 导入excel时，excel 的标题就是ID。 
// 简单点，可以导出excel后，移除数据当作excel模板，填写数据再导入
@ExcelProperty(value = "ID",index = 0)
private Iong id;

@ExcelProperty(value = "add_time", converter = TimeStampConverter.class)
private Timestamp addTime;

// 0女， 1男 . 可以自定义转换器
@ExcelProperty("sex")
private Integer sex;

// ignore 后不导出
@ExcelIgnore
private String pwd;
}

// 自定义转换器
public class TimeStampConverter implements Converter<Timestamp> {
    @Override
    public Class supportJavaTypeKey() {
        return Timestamp.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Timestamp convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        String stringValue = cellData.getStringValue();
        if (StringUtils.isBlank(stringValue)) {
            return null;
        }
        DateTime dateTime = MyDateUtils.parseNormal(stringValue);
        return MyDateUtils.asTimeStamp(dateTime);
    }

    @Override
    public CellData convertToExcelData(Timestamp value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        DateTime dateTime = MyDateUtils.asDateTime(value);
        String str = dateTime.toString("yyyy-MM-dd HH:mm:ss");
        return new CellData(str);
    }
}

@RestController
@RequestMapping("/demo")
public class DemoController{
    
    // 导出
    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("demo_export_student", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        List<Student> items = Lists.newArrayList();
        EasyExcel.write(response.getOutputStream(), Student.class)
                // 自动列宽
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("sheet1")
                .doWrite(items);
    }

    //上传
    @PostMapping("/upload")
    public void upload(MultipartFile file) {
        EasyExcel.read(file.getInputStream(), Student.class, new AnalysisEventListener<Student>() {
            List<Student> items = Lists.newArrayList();
            final int batch_max = 1000;

            @Override
            public void invoke(Student data, AnalysisContext context) {
                // 校验和初始化代码
                // 当数据量太大时， 可以在这里 batch insert
                items.add(data);
            }

            @SneakyThrows
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                Assert.isTrue(items.size() <= batch_max, "one file max items: " + batch_max);
                // do batch insert
            }
        }).sheet("sheet1").doRead();
    }
}