简单解析excel文件

一. apache-poi

1.添加依赖

        <!--xls(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--xlsm(07)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

2. 读取文件

        //获取文件流
        FileInputStream inputStream = new FileInputStream(PATH+"合同统计03.xls");
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        //Workbook workbook = new XSSFWorkbook(inputStream);//07版(.xlsx文件)
        //2.得到表sheet
        Sheet sheet1 = workbook.getSheetAt(0);
        //3.得到行
        Row row2 = sheet1.getRow(1);
        //4.得到列
        Cell cell21 = row2.getCell(0);
        Cell cell22 = row2.getCell(1);
        Cell cell23 = row2.getCell(2);
        Cell cell24 = row2.getCell(3);
        System.out.println(cell21.getStringCellValue());
        System.out.println(cell22.getStringCellValue());
        System.out.println(cell23.getNumericCellValue());
        System.out.println(cell24.getStringCellValue());
        inputStream.close();

二.alibaba easyexcel

1.添加依赖

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

2.编写DataListener(此为excel回调监听器)

主要思路:

1. EasyExcel在读取excel文件时,会回调监听器的invoke()方法
2. EasyExcel在写出excel文件时,会将list数组直接转换成实体类

public class DataListener<T> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(UserDataListener.class);
    /**
     * 每隔 x 条存储数据库,实际使用中可以3000条,然后清理list,方便内存回收
     */
    private static final int BATCH_COUNT = 5;

    private ExcelService excelService ;

    public DataListener(ExcelService excelService) {
        this.excelService = excelService;
    }

    List<T> list = new ArrayList();
    /**
     * 读取数据会执行 invoke 方法
     * @param  T  读取的数据类型
     * @param analysisContext 分析上下文
     */
    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT){
            saveData();//持久化逻辑
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        LOGGER.info("所有数据解析完成!");
    }

    private void saveData(){
        LOGGER.info("{}条数据,开始存储数据库!",list.size());
        excelService.save(list);
        LOGGER.info("存数据库成功!");
    }
}

创建对应实体类

@Data
public class User {
    @ExcelProperty("用户名")
    private String userName ;
    @ExcelProperty("用户密码")
    private String password ;
    @ExcelProperty("创建日期")
    private Date createdDate ;
    @ExcelProperty("用户等级")
    private Integer level ;
    /**
     * 使用这个注解之后,生成excel时会忽略该字段
     */
    @ExcelIgnore
    private Date invalidDate ;
}

1.读

public class ReadTest {
    public  static final String PATH = "E:\\workspace\\myproject\\easy-excel-test\\easy-excel\\src\\main\\resources\\";
    @Test
    public void simpleRead(){
        String fileName = PATH + "简单excel.xlsx";
        //此时会调用监听器的doAfterAllAnalysed()方法,执行service.save()逻辑
        EasyExcel.read(fileName, User.class, new UserDataListener()).sheet().doRead();
    }
    @Test
    public void simpleRead02(){
        String fileName = PATH + "简单excel.xlsx";
        ExcelService excelService = new UserServiceImpl();
        EasyExcel.read(fileName,new DataListener<User>(excelService)).sheet().doRead();
    }
}

2.写

public class WriteTest {
    public  static final String PATH = "E:\\workspace\\myproject\\easy-excel-test\\easy-excel\\src\\main\\resources\\";

    @Test
    public void simpleWrite() throws ParseException {
        String fileName = PATH + "简单excel.xlsx";
        EasyExcel.write(fileName, User.class).sheet("模板一").doWrite(data());
    }

    private List<User> data() throws ParseException {
        List<User> list = new ArrayList<User>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUserName("张三"+i);
            user.setPassword(""+i+i+i+i+i+i);
            user.setCreatedDate(new Date());
            user.setLevel(i);
            user.setInvalidDate(sdf.parse("2020-10-12"));
            list.add(user);
        }
        return list;
    }
}

将实体类转excel时,会遇到的问题

1. LocalDateTime 格式问题

    @ColumnWidth(20)
    @ExcelProperty(converter = LocalDateTimeConverter.class)
    private LocalDateTime createTime;

2. 数值转文本格式

    @ContentStyle(dataFormat = 0x31)
    private Long id;