poi和easyexcel性能区别

poi

xls（03） 65536 poi HSSF
xlsx（07） 无限制 poi-ooxml XSSF

工作簿：Workbook
工作表：Sheet
行：Row
列：Cell

poi写：03和07

@Test
    public void testWrite03() throws IOException {
        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet("03表限制65536");

        for (int i = 0; i < 10; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "03表限制65536.xls");

        workbook.write(outputStream);

        outputStream.close();
    }

 @Test
    public void testWrite07() throws IOException {
        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet("07表无限制");

        for (int i = 0; i < 10; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i + j);
            }
        }

        FileOutputStream outputStream = new FileOutputStream(PATH + "07表无限制.xlsx");

        workbook.write(outputStream);

        outputStream.close();
    }

	/**
     * 2.99
     * @throws IOException
     */
    @Test
    public void testWrite03BigData() throws IOException {
        long begin = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet("03插入大量数据");

        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03插入大量数据.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();

        System.out.println((double) (end - begin) / 1000);
    }

    /**
     * 10.78（65536）
     * 15.70(100000)
     * @throws IOException
     */
    @Test
    public void testWrite07BigData() throws IOException {
        long begin = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet("07插入大量数据");

        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07插入大量数据.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();

        System.out.println((double) (end - begin) / 1000);
    }

    /**
     * 4.26(100000)
     * 需要清楚零时文件
     * @throws IOException
     */
    @Test
    public void testWrite07BigDataS() throws IOException {
        long begin = System.currentTimeMillis();

        Workbook workbook = new SXSSFWorkbook();

        Sheet sheet = workbook.createSheet("07插入大量数据S");

        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(j);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07插入大量数据S.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();

        //清楚零时文件
        ((SXSSFWorkbook) workbook).dispose();

        System.out.println((double) (end - begin) / 1000);
    }

错误：java.lang.noclassdeffounderror:
org/apache/poi/ss/usermodel/date1904support

解决：poi版本选择：一致

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

poi读：03和07

 @Test
    public void testRead03() throws IOException {

        FileInputStream fileInputStream = new FileInputStream(PATH + "03表限制65536.xls");

        Workbook workbook = new HSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(0);

        Cell cell = row.getCell(0);

        System.out.println(cell.getStringCellValue());

        fileInputStream.close();
    }

    @Test
    public void testRead07() throws IOException {

        FileInputStream fileInputStream = new FileInputStream(PATH + "07插入大量数据.xlsx");

        Workbook workbook = new XSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(0);

        Cell cell = row.getCell(0);

        System.out.println(cell.getNumericCellValue());

        fileInputStream.close();

    }

poi读取问题：后缀名修改实际上可能没改变文件属性，要实际看看是什么类型的看表格有没有大于65535
大于65535.xslx用XSSF

**注意：**读取单元格要注意不同类型选择

/**
     * 读取excel工具类
     * @param fileInputStream 文件输入流
     * @throws IOException
     */
    public void readExcelUtil(FileInputStream fileInputStream) throws IOException {

        Workbook workbook = new XSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);
        //获取表头内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
//                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "|");
                }
            }
            System.out.println();
        }

        //获取表格数据
        int rowCount = sheet.getPhysicalNumberOfRows();

        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowData.getCell(cellNum);
                    String cellValue = "";
                    if (cell != null) {
                        switch (cell.getCellTypeEnum()) {
                            case STRING:
                                System.out.print("STRING ");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                System.out.print("BOOLEAN ");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK:
                                System.out.print("BLANK ");
                                break;
                            case NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    System.out.print("日期 ");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy/MM/dd");
                                } else {
                                    //防止数字过长，先转化为string
                                    System.out.print("数字过长，先转化为string ");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case ERROR:
                                System.out.print("ERROR ");
                                break;
                            case FORMULA:
                                System.out.print("FORMULA ");
                                String formula = cell.getCellFormula();
                                System.out.print(formula);
                                FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                                CellValue evaluate = formulaEvaluator.evaluate(cell);
                                cellValue = evaluate.formatAsString();
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }

        fileInputStream.close();

    }

easyexcel

https://www.yuque.com/easyexcel