Java EasyExcel使用记录

每周需要汇总进度表，把收集上来的几百个Excel中的某几列提取出来登记到一个汇总表中，手工一个一个打开copy，paste太慢，通过easyExcel工具写了个小功能，记录一下

首先pom引入jar包依赖

    4.0.0

    org.example
    easyExcelDemo
    1.0-SNAPSHOT

    
        8
        8
    

    
        
            com.alibaba
            easyexcel
            2.1.1
        
        
            org.apache.poi
            poi
            3.17
        
        
            org.apache.poi
            poi-ooxml
            3.17
        
    

定义要读Excel的ReadData类,通过index指定读取第2,3,4列

import com.alibaba.excel.annotation.ExcelProperty;

public class ReadData {

    @ExcelProperty(index = 1)
    private String itemCatagory;

    @ExcelProperty(index = 2)
    private String itemNo;

    @ExcelProperty(index = 3)
    private String itemName;

    public String getItemCatagory() {
        return itemCatagory;
    }

    public void setItemCatagory(String itemCatagory) {
        this.itemCatagory = itemCatagory;
    }

    public String getItemNo() {
        return itemNo;
    }

    public void setItemNo(String itemNo) {
        this.itemNo = itemNo;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }
}

定义要生成的Excel的WriteData类,自定义表头列名称

import com.alibaba.excel.annotation.ExcelProperty;

public class WriteData {

    @ExcelProperty("存货分类")
    private String itemCatagory;

    @ExcelProperty("存货编码")
    private String itemNo;

    @ExcelProperty("存货名称")
    private String itemName;

    public String getItemCatagory() {
        return itemCatagory;
    }

    public void setItemCatagory(String itemCatagory) {
        this.itemCatagory = itemCatagory;
    }

    public String getItemNo() {
        return itemNo;
    }

    public void setItemNo(String itemNo) {
        this.itemNo = itemNo;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }
}

封装读取数据

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelListener extends AnalysisEventListener {

    //创建list集合封装最终的数据
    List list = new ArrayList();

    //一行一行去读取excle内容
    @Override
    public void invoke(ReadData user, AnalysisContext analysisContext) {
        System.out.println("***"+user);
        list.add(user);
    }

    //读取excel表头信息
    @Override
    public void invokeHeadMap(Map headMap, AnalysisContext context) {
        System.out.println("表头信息："+headMap);
    }

    //读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

主程序写Excel

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class EasyExcel {

    //将读取的Excel对象数据转换为要写的Excel对象数据
    private static List data(List readDataList){

        List list = new ArrayList();
        for (ReadData readdata: readDataList) {
            WriteData data = new WriteData();
            data.setItemCatagory(readData.getItemCatagory());
            data.setItemNo(readData.getItemNo());
            data.setItemName(readData.getItemName());
            list.add(data);
        }
        return list;
    }

    public static void main(String[] args) {

        //所有Excel文件数据对象集合
        List readList = new ArrayList();

        //获取所有Excel文件列表
        List filesList = getFileList();

        for (File file : filesList) {
            String fileName = file.toString();
            System.out.println(fileName);
            //这里需要指定读用哪个class去读，然后读取第一个sheet文件流会自动关闭
            ExcelListener excelListener = new ExcelListener();
            com.alibaba.excel.EasyExcel.read(fileName, ReadData.class, excelListener).sheet().doRead();
            readList.addAll(excelListener.list);
        }

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        System.out.println(df.format(new Date()));// new Date()为获取当前系统时间
        String fileName="E:\分类表.xlsx";
        com.alibaba.excel.EasyExcel.write(fileName, WriteData.class).sheet("分类表111").doWrite(data(readList));
    }

    //获取指定路径下所有Excel文件
    private static List getFileList(){
        String filepath = "E:\demofiles";
        File file = new File(filepath);
        List list = Arrays.asList(file.listFiles());
        return list;
    }

}