记一次EasyExcel简单使用

一. EasyExcel简单介绍

EasyExcel语雀官方文档
EasyExce是阿里巴巴开源的一款excel文档处理工具类, 可以很方便的对excel文档进行处理

二. 使用方式简介

pom文件

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

读取excel文件示例

• 文件内容
  
• 读取代码

/**
 *  EasyExcel 读取excel文件demo
 */
public class Demo1 {

    public static void main(String[] args) throws FileNotFoundException {
        // 文件的位置
        String filePath = "D:\\酒店设备SN.xlsx";
        File file = new File(filePath);

        // 使用EasyExcel读取文件流, 第一个参数是文件流 第二个参数是读取的sheet表参数(第几个sheet表[1下标开始], 从第几行开始读[0下标开始], 对应的实体类)
        List<Object> read = EasyExcelFactory.read(new FileInputStream(file), new Sheet(1, 1, ExcelModel.class));
        List<ExcelModel> list = null;
        if (CollectionUtils.isNotEmpty(read)) {
            list = read.stream().map(x -> {
                if (x instanceof ExcelModel) {
                    return (ExcelModel) x;
                }
                return null;
            }).collect(Collectors.toList());
        }
        System.out.println(JSON.toJSONString(list));
    }

    /**
     * [
     *     {
     *         "assetId": "efee3fgwe",
     *         "cellStyleMap": {},
     *         "clientId": "dqwdwqdqw",
     *         "deviceSn": "123454ww45",
     *         "productId": "sgsaerg",
     *         "rowHeight": 0,
     *         "uuid": "23234243242"
     *     },
     *     {
     *         "assetId": "fgwegwfg",
     *         "cellStyleMap": {},
     *         "clientId": "dqwqdq",
     *         "deviceSn": "4444ss321",
     *         "productId": "gaerga",
     *         "rowHeight": 0,
     *         "uuid": "42323252"
     *     },
     *     {
     *         "assetId": "fwewf",
     *         "cellStyleMap": {},
     *         "clientId": "dqwqwq",
     *         "deviceSn": "123323545sss7",
     *         "productId": "argearea",
     *         "rowHeight": 0,
     *         "uuid": "423525232"
     *     }
     * ]
     */

}

• demo代码

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString
// 需要继承BaseRowModel
public class ExcelModel extends BaseRowModel {

    /**
     * 使用注解设置下标,0开始
     */
    @ExcelProperty(value ="设备sn",index = 0)
    private String deviceSn;

    @ExcelProperty(value ="uuid",index = 1)
    private String uuid;

    @ExcelProperty(value ="assetId",index = 2)
    private String assetId;

    @ExcelProperty(value ="clientId",index = 3)
    private String clientId;

    @ExcelProperty(value ="productId",index = 4)
    private String productId;
}

生成excel文件示例

• 生成的Excel文件内容
  

• 生成的代码

/**
 * 生成excel文件
 */
public class Demo2 {

    public static void main(String[] args) {
        // 需要写入的内容
        List<ExcelDemo2> excelDemoList = new ArrayList<>(Arrays.asList(
                ExcelDemo2.builder().name("胡琦").age("24").build(),
                ExcelDemo2.builder().name("yymm").age("23").build(),
                ExcelDemo2.builder().name("想听").age("25").build()
        ));
        FileOutputStream fileOutputStream = null;
        try {
            // 使用EasyExcel生成excel放到D盘
            File file = new File("D:\\测试easyExcel.xlsx");
            fileOutputStream = new FileOutputStream(file);

            ExcelWriter writer = new ExcelWriter(fileOutputStream, ExcelTypeEnum.XLSX, true);
            Sheet sheet = new Sheet(1, 0, ExcelDemo2.class);
            writer.write(excelDemoList, sheet);
            writer.finish();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                fileOutputStream.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        System.out.println("生成成功");


    }
}

• model

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ExcelDemo2 extends BaseRowModel {

    @ExcelProperty(value = "姓名" ,index = 0)
    private String name;

    @ExcelProperty(value = "年龄" ,index = 1)
    private String age;
}