Apache POI 及 alibaba EasyExcel使用

文章目录
  • java操作Excel数据
    • 使用场景
    • excel 03 和 07的区别
    • POI
    • easyExcel
    • 解析excel表中的对象
    • POI使用步骤
    • POI 写数据
    • POI 读数据
    • 计算公式
    • easyExcel读写数据
      • ·写数据
      • ·读数据

java操作Excel数据

在 平时 可以使用IO流对Excle进行操作

但是现在使用更加方便的第三方组件来实现

使用场景

1、将用户信息导出为Excel表格,导入数据

2、将Excel表中的数据录入到网站数据库 (习题上传) 减轻网站的录入量

3、开发中经常会设计到Excel的处理,导入Excel到数据库中

目前最流行的是 Apache POI以及阿里巴巴easyExcel

excel 03 和 07的区别

HSSF 对应excel中的03版本 该版本要求excel中最多只能写65536行

? 后缀名为 03.xls

XSSF 对应excel中的07版本 该版本对于行数没有要求

? 后缀名为 07.xlsx

POI

Apache提供的,会比较麻烦,比较原生

开放源码函式库,POI提供API给java程序对Office格式档案读和写的功能

但是存在内存问题 => POI将数据会先写入内存中,一旦写入的内容过多时会产生OOM,也叫做内存溢出

easyExcel

https://github.com/alibaba/easyexcel

对POI进行了一些优化,可以使开发者更加简单,读和写代码只需要1行

存在时间的问题 => easyExcel在写数据时是一行一行往磁盘中写,所以解决了POI的内存问题,但是带来了时间问题

解析excel表中的对象

由于java中万物皆对象,所以需要先观察一张excel表中有哪些对象~

POI使用步骤

第一步:创建Maven项目

第二步:导入依赖

<dependencies>
        <!-- xls 03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- xlsx 07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- 日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <!--测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
</dependencies>

POI 工具类

public class POIUtils {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static String DATE_FORMAT = "yyyy/MM/dd";
    /**
     * 读入excel文件,解析后返回
     * @param file
     * @throws IOException
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);
        //创建返回对象,把每行中的值作为一个数组,所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //获得当前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //获得当前sheet的开始行
                int firstRowNum  = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();
                //循环除了第一行的所有行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    //循环当前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
            }
            workbook.close();
        }
        return list;
    }

    //校验文件是否合法
    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在!");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 获得Workbook工作簿对象
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象,表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型,需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            return cellValue;
        }
        //把数字当成String来读,避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}

easyExcel

根据实体类自动生成表

第一步:导入依赖

该依赖中自带了很多种依赖,如lombok、spring-boot等,需要我们在引入依赖时将自己已经导入的依赖删除,不然会报依赖冲突的错误

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.1.4</version>
</dependency>
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.18.8</version>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>fastjson</artifactId>
	<version>1.2.75</version>
</dependency>

写数据

第二步:创建excel表对应的实体类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double aDouble;
    // 忽略该字段
    @ExcelIgnore
    private String ignore;
}

第四步:编写测试类

使用链式编写的方式

write(文件路径,excel表对应的java类)

sheet(设置表名)

doWrite(数据)

 @Test
    public void test1(){
        String fileName ="d:\JavaCode\Maven\excel-demo\easyEasyData.xlsx";
        EasyExcel.write(fileName,DemoData.class).sheet("表1").doWrite(easyTest1());
    }
·读数据

1、每执行一条excel表中的数据都会执行一次监听文件中的invoke方法,所以如果需要修改可以修改invoke方法中的内容

2、DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去

第二步:准备一个对应excel表中字段的类

与写操作中使用同一个类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double aDouble;
    // 忽略该字段
    @ExcelIgnore
    private String ignore;
}
@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double aDouble;
    // 忽略该字段
    @ExcelIgnore
    private String ignore;
}

第三步:创建数据层 Mapper || Dao

public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

第四步:创建监听器

package excel.readEasy;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import excel.easy.DemoData;
import lombok.extern.slf4j.Slf4j;

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

// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
    //每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
    private static final int BATCH_COUNT = 100;
    //缓存的数据
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);


    //假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用
    private DemoDAO demoDAO;

    // 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    //如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }


    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {

    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        System.out.println("111");
    }

    //这个每一条数据解析都会来调用
    @Override
    public void invoke(DemoData data, AnalysisContext analysisContext) {
        System.out.println("2222");
        System.out.println(JSON.toJSONString(data));
        //  log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {

    }

    //所有数据解析完成了 都会来调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有数据解析完成!");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return false;
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据,开始存储数据库!", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功!");
    }
}

第五步:测试

@Test
    public void test3() {
        String fileName = "E:\JavaCode\Maven\excel-demo\easyEasyData.xlsx";
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                System.out.println(JSON.toJSONString(demoData));
            }
        })).sheet().doRead();
    }