easypoi excel 文件导入导出

 

一、POM 引入

 

         <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>     

 

二、excel 文件导出

 

2.1 实体 设计

public class Sku {
    @Excel(name = "skuNo",orderNum="0")
    private String skuNo;
    
    @Excel(name = "soldTo",orderNum="1")
    private String soldTo;
    
    @Excel(name = "hubId",orderNum="2")
    private String hubId;
    
    @Excel(name = "soldAllocation",orderNum="3")
    private String soldAllocation;

    @Excel(name = "hubAllocation",orderNum="4")
    private String hubAllocation;

 

在需要生成到excel 的字段上加上注解，然后设置num ， 从0开始

 @Excel(name = "生日", orderNum = "8", importFormat = "yyyy-MM-dd") 

 

2.2 导出

@Test
    public void test() throws Exception{
        
        List<Sku> list = new ArrayList<>();
        for (int i=1; i<10000; i++) {
            Sku s = new Sku();
            s.setHubAllocation("hubAllocation" + i);
            s.setHubId("hubId" + i);
            s.setSkuNo("skuNo"+ i);
            s.setSoldAllocation("soldAllocation" + i);
            s.setSoldTo("soldTo"+ i);
            list.add(s);
        }
        Date start = new Date();
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
                Sku.class, list);
        System.out.println(new Date().getTime() - start.getTime());
        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/excel/SKU.xlsx");
        workbook.write(fos);
        fos.close();
    }

 

2.3 测试结果

 

 

三、文件导入 解析到数据库

3.1 实体设计

 

public class Sku {
    @Excel(name = "skuNo")
    private String skuNo;
    
    @Excel(name = "soldTo")
    private String soldTo;
    
    @Excel(name = "hubId")
    private String hubId;
    
    @Excel(name = "soldAllocation")
    private String soldAllocation;

    @Excel(name = "hubAllocation")
    private String hubAllocation;

 

说明：导入不需要指定num 列号

 

3.2 导入

@Test
    public void test2() {
        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        long start = new Date().getTime();
        List<Sku> list = ExcelImportUtil.importExcel(
           new File("D:/excel/SKU.xlsx"),
           Sku.class, params);
       
         System.out.println(new Date().getTime() - start);
        System.out.println(list.size());
}

 

说明：setTitleRows(0) 设置列标题的位置，比如

 

 rows 就要设置成0 

 

官方文档位置：

http://easypoi.mydoc.io/#text_197818