Poi导入导出

1. POI介绍

官网：Apache POI - the Java API for Microsoft Documents

Excel概念:

工作簿:一张excel文件就是一个工作簿

工作表:excel中的一个sheet就是一个工作表

行:sheet中的一行就是一个单元行

单元格:一行中的一个列就是一个单元格

POI中的对象介绍:

        工作簿:使用WorkBook对象进行操作，实现类主要有HSSFWorkBook、XSSFWorkBook、SXSSFWorkBook

工作表:使用Sheet对象操作工作表，一个excel中有多个sheet

行:使用Row对象操作工作表中的一行

单元格:使用Cell对象操作一行中的某一列

1. POI操作

pom.xml:

<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>

1. POI Excel写

/**

 * xls写

 *  使用 HSSFWorkbook 进行操作

 */

@Test

public void poiWrite() {

    // 工作簿

    try(Workbook workbook = new HSSFWorkbook();

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03simple.xls")) {

        // 工作表

        Sheet sheet1 = workbook.createSheet("sheet1");

        // 行

        Row row1 = sheet1.createRow(0);

        // 列

        Cell cell11 = row1.createCell(0);

        Cell cell12 = row1.createCell(1);

        // 1-1 1-2

        cell11.setCellValue("1-1");

        cell12.setCellValue("1-2");

        Row row2 = sheet1.createRow(1);

        Cell cell21 = row2.createCell(0);

        Cell cell22 = row2.createCell(1);

        // 2-1 2-2

        cell21.setCellValue("2-1");

        cell22.setCellValue("2-2");

        // 写出文件

        workbook.write(fileOutputStream);

    } catch (Exception e) {

        e.printStackTrace();

    }

}

结果:

/**

 * xlsx 写

 *  使用 SXSSFWorkbook 进行操作(优化之后的)

 */

@Test

public void poiWriteSXSSF() {

    // 工作簿

    try(Workbook workbook = new SXSSFWorkbook();

        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07simple.xlsx")) {

        // 工作表

        Sheet sheet1 = workbook.createSheet("sheet1");

        // 行

        Row row1 = sheet1.createRow(0);

        // 列

        Cell cell11 = row1.createCell(0);

        Cell cell12 = row1.createCell(1);

        // 1-1 1-2

        cell11.setCellValue("1-1");

        cell12.setCellValue("1-2");

        Row row2 = sheet1.createRow(1);

        Cell cell21 = row2.createCell(0);

        Cell cell22 = row2.createCell(1);

        // 2-1 2-2

        cell21.setCellValue("2-1");

        cell22.setCellValue("2-2");

        // 写出文件

        workbook.write(fileOutputStream);

    } catch (Exception e) {

        e.printStackTrace();

    }

}

1. POI Excel 读

/**

 * xls读

 *  使用 HSSFWorkbook 进行操作

 */

@Test

public void poiRead() {

    // 工作簿

    try(Workbook workbook = new HSSFWorkbook(new FileInputStream(PATH + "03simple.xls"))) {

        // 工作表

        Sheet sheet1 = workbook.getSheet("sheet1");

        // 行

        Row row1 = sheet1.getRow(0);

        // 列

        Cell cell11 = row1.getCell(0);

        Cell cell12 = row1.getCell(1);

        // 1-1 1-2

        System.out.println("1-1" + ": " + cell11.getStringCellValue());

        System.out.println("1-2" + ": " + cell12.getStringCellValue());

        Row row2 = sheet1.getRow(1);

        Cell cell21 = row2.getCell(0);

        Cell cell22 = row2.getCell(1);

        // 2-1 2-2

        System.out.println("2-1" + ": " + cell21.getStringCellValue());

        System.out.println("2-2" + ": " + cell22.getStringCellValue());

    } catch (Exception e) {

        e.printStackTrace();

    }

}

结果:

/**

 * xlsx 读

 *  使用 SXSSFWorkbook 进行操作(优化之后的)

 */

@Test

public void poiReadSXSSF() {

    // 工作簿

    try(Workbook workbook = new XSSFWorkbook(new FileInputStream(PATH + "07simple.xlsx"))) {

        // 工作表

        Sheet sheet1 = workbook.getSheet("sheet1");

        // 行

        Row row1 = sheet1.getRow(0);

        // 列

        Cell cell11 = row1.getCell(0);

        Cell cell12 = row1.getCell(1);

        // 1-1 1-2

        System.out.println("1-1" + ": " + cell11.getStringCellValue());

        System.out.println("1-2" + ": " + cell12.getStringCellValue());

        Row row2 = sheet1.getRow(1);

        Cell cell21 = row2.getCell(0);

        Cell cell22 = row2.getCell(1);

        // 2-1 2-2

        System.out.println("2-1" + ": " + cell21.getStringCellValue());

        System.out.println("2-2" + ": " + cell22.getStringCellValue());

    } catch (Exception e) {

        e.printStackTrace();

    }

}

1. POI Cell多格式读取

/**

 * 解析列

 * @param cell 单元格

 * @param workbook 工作簿

 * @return 解析完的数据

 */

private Object getCellData(Cell cell, Workbook workbook) {

    Object result = null;

    CellType cellType = cell.getCellTypeEnum();

    switch (cellType) {

        case STRING:

            result = cell.getStringCellValue();

            break;

        case BOOLEAN:

            result = cell.getBooleanCellValue();

            break;

        case NUMERIC:

            if (DateUtil.isCellDateFormatted(cell)) {

                result = cell.getDateCellValue();

            } else {

                result = cell.getNumericCellValue();

            }

            break;

        case FORMULA:

            FormulaEvaluator formulaEvaluator = null;

            if (workbook instanceof HSSFWorkbook) {

                formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

            } else if (workbook instanceof XSSFWorkbook) {

                formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);

            } else if (workbook instanceof SXSSFWorkbook) {

                formulaEvaluator = new SXSSFFormulaEvaluator((SXSSFWorkbook) workbook);

            }

            if (formulaEvaluator != null) {

                CellValue evaluate = formulaEvaluator.evaluate(cell);

                result = getCellData(evaluate, workbook);

            }

            break;

        default:

            break;

    }

    return result;

}

/**

 * 获取CellValue的值

 * @param cellValue CellValue

 * @param workbook 工作薄

 * @return 解析完的数据

 */

private Object getCellData(CellValue cellValue, Workbook workbook) {

    Object result = null;

    CellType cellType = cellValue.getCellTypeEnum();

    switch (cellType) {

        case STRING:

            result = cellValue.getStringValue();

            break;

        case BOOLEAN:

            result = cellValue.getBooleanValue();

            break;

        case NUMERIC:

            result = cellValue.getNumberValue();

            break;

        default:

            break;

    }

    return result;

}