Java POI操作excel

最新推荐文章于 2024-08-13 15:45:17 发布

毕竟尹稳健

最新推荐文章于 2024-08-13 15:45:17 发布

阅读量2.2k

点赞数

分类专栏： SpringBoot 文章标签： java

本文链接：https://blog.csdn.net/weixin_46073538/article/details/126849303

版权

SpringBoot 专栏收录该内容

29 篇文章 1 订阅

订阅专栏

Java POI

Java poi
- poi

Java poi

概述

1、将用户信息导出为excel表格（导出数据…)
2、将Excel表中的信息录入到网站数据库（习题上传…)
开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中!

操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel !

poi

1、导入依赖

  <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

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

        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.6</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>
    </dependencies>

2、POI-Excel写入

xls 是03版的excel 对象是HSSFWorkbook

@Test
public void testWrite01() throws IOException {
    // 1、创建一个工作簿
    Workbook workbook = new HSSFWorkbook();

    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("员工信息表");
    // 3、创建一行，0代表从第一行开始
    Row row1 = sheet.createRow(0);

    // 4、创建一个单元格
    Cell cell11 = row1.createCell(0);
    // 将坐标[0,0]的单元格写入一个数据
    cell11.setCellValue("姓名");
    // 将坐标[0,1]的单元格写入一个数据
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("年龄");

    // 将坐标[0,1]的单元格写入一个数据
    Cell cell13 = row1.createCell(2);
    cell13.setCellValue("出生日期");

    // 创建第二行
    Row row2 = sheet.createRow(1);
    // 将坐标[1,0]的单元格写入一个数据
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("张三");

    // 将坐标[1,1]的单元格写入一个数据
    Cell cell22 = row2.createCell(1);
    cell22.setCellValue(21);

    // 将坐标[1,1]的单元格写入一个数据
    Cell cell23 = row2.createCell(2);
    String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell23.setCellValue(dateTime);

    // 生成一张表
    FileOutputStream fileOutputStream = new FileOutputStream(path+"员工信息.xls");
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();
    workbook.close();

    System.out.println("生成完毕");

}

xlsx 是07版的excel 使用XSSFWorkbook

使用的对象不一样，但是因为多态的，代码可以不用变化太大

@Test
public void testWrite02() throws IOException {
    // 1、创建一个工作簿
    Workbook workbook = new XSSFWorkbook();

    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("员工信息表");
    // 3、创建一行，0代表从第一行开始
    Row row1 = sheet.createRow(0);

    // 4、创建一个单元格
    Cell cell11 = row1.createCell(0);
    // 将坐标[0,0]的单元格写入一个数据
    cell11.setCellValue("姓名");
    // 将坐标[0,1]的单元格写入一个数据
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("年龄");

    // 将坐标[0,1]的单元格写入一个数据
    Cell cell13 = row1.createCell(2);
    cell13.setCellValue("出生日期");

    // 创建第二行
    Row row2 = sheet.createRow(1);
    // 将坐标[1,0]的单元格写入一个数据
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("张三");

    // 将坐标[1,1]的单元格写入一个数据
    Cell cell22 = row2.createCell(1);
    cell22.setCellValue(21);

    // 将坐标[1,1]的单元格写入一个数据
    Cell cell23 = row2.createCell(2);
    String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell23.setCellValue(dateTime);

    // 生成一张表
    FileOutputStream fileOutputStream = new FileOutputStream(path+"员工信息.xlsx");
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();
    workbook.close();

    System.out.println("生成完毕");

}

大数据量的情况下写入：

@Test
public void testWriteData03() throws IOException {

    long start = System.currentTimeMillis();

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }

    workbook.write(new FileOutputStream(path+"testData.xls"));

    long end = System.currentTimeMillis();
    System.out.println(end-start);

}

@Test
public void testWriteData04() throws IOException {

    long start = System.currentTimeMillis();

    Workbook workbook = new XSSFWorkbook();

    Sheet sheet = workbook.createSheet();

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

    workbook.write(new FileOutputStream(path+"testData.xlsx"));

    long end = System.currentTimeMillis();
    System.out.println(end-start);

}

使用流的方式创建更快

@Test
public void testWriteData05() throws IOException {

    long start = System.currentTimeMillis();

    Workbook workbook = new SXSSFWorkbook();

    Sheet sheet = workbook.createSheet();

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

    workbook.write(new FileOutputStream(path+"testDataUp.xlsx"));

    long end = System.currentTimeMillis();
    System.out.println(end-start);

}

3、POI-Excel读取

@Test
public void readTest01() throws IOException {
    FileInputStream fileInputStream = new FileInputStream(path + "员工信息.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(1);
    Cell cell10 = row.getCell(0);
    String name = cell10.getStringCellValue();

    Cell cell12 = row.getCell(1);
    Double age = cell12.getNumericCellValue();

    System.out.println(name);
    System.out.println(age);

    fileInputStream.close();

}

@Test
public void readTest02() throws IOException {
    FileInputStream fileInputStream = new FileInputStream(path + "员工信息.xlsx");
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(1);
    Cell cell10 = row.getCell(0);
    String name = cell10.getStringCellValue();

    Cell cell12 = row.getCell(1);
    Double age = cell12.getNumericCellValue();

    System.out.println(name);
    System.out.println(age);

    fileInputStream.close();

}

读取不同的数据类型数据

@Test
    public void readTest03() throws IOException {
        FileInputStream fileInputStream = new FileInputStream(path + "data.xlsx");
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 获取标题内容 , 获取第一行
        Row title = sheet.getRow(0);
        if (!Objects.isNull(title)){
//            获取第一行中的列数
            int cells = title.getPhysicalNumberOfCells();
            for (int i = 0; i < cells; i++) {
                Cell cell = title.getCell(i);
                if (!Objects.isNull(cell)) {
//                    获取某一列的类型
                    CellType cellType = cell.getCellType();
                    System.out.println(cellType);
                    String value = cell.getStringCellValue();
                    System.out.print(value + ",");
                }
            }
        }

        System.out.println("================================");
//        获取所有的行
        int rows = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rows; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (!Objects.isNull(row)){
                int cells = row.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cells; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (!Objects.isNull(cell)) {
                        CellType cellType = cell.getCellType();
                        switch (cellType){
                            case _NONE:
                                System.out.println("_NONE");
                                break;

                            case BLANK:
                                System.out.println("BLANK");
                                break;

                            case ERROR:
                                byte errorCellValue = cell.getErrorCellValue();
                                System.out.println(errorCellValue);
                                System.out.println("ERROR");
                                break;

                            case STRING:
                                String value = cell.getStringCellValue();
                                System.out.println(value);
                                System.out.println("STRING");
                                break;

                            case BOOLEAN:
                                boolean booleanCellValue = cell.getBooleanCellValue();
                                System.out.println(booleanCellValue);
                                System.out.println("BOOLEAN");
                                break;

                            case FORMULA:
                                Date dateCellValue = cell.getDateCellValue();
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                String date = simpleDateFormat.format(dateCellValue);
                                System.out.println(date);
                                System.out.println("FORMULA");
                                break;

                            case NUMERIC:
                                double numericCellValue = cell.getNumericCellValue();
                                System.out.println(numericCellValue);
                                System.out.println("NUMERIC");
                                break;
                        }


                    }
                }
            }


        }

        fileInputStream.close();
    }

提取匹配方法：

    public void dataType(InputStream inputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
//        获取所有的行
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (!Objects.isNull(row)){
//                获取所有的列
                int cellCount = row.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (!Objects.isNull(cell)) {
                        CellType cellType = cell.getCellType();
                        switch (cellType){
                            case _NONE:
                                System.out.println("_NONE");
                                break;

                            case BLANK:
                                System.out.println("BLANK");
                                break;

                            case ERROR:
                                byte errorCellValue = cell.getErrorCellValue();
                                System.out.println(errorCellValue);
                                System.out.println("ERROR");
                                break;

                            case STRING:
                                String value = cell.getStringCellValue();
                                System.out.println(value);
                                System.out.println("STRING");
                                break;

                            case BOOLEAN:
                                boolean booleanCellValue = cell.getBooleanCellValue();
                                System.out.println(booleanCellValue);
                                System.out.println("BOOLEAN");
                                break;

                            case FORMULA:
                                Date dateCellValue = cell.getDateCellValue();
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                String date = simpleDateFormat.format(dateCellValue);
                                System.out.println(date);
                                System.out.println("FORMULA");
                                break;

                            case NUMERIC:
                                double numericCellValue = cell.getNumericCellValue();
                                System.out.println(numericCellValue);
                                System.out.println("NUMERIC");
                                break;
                        }
                    }
                }
            }

        }
    }

计算公式

@Test
public  void test04() throws IOException {
    FileInputStream fileInputStream = new FileInputStream(path+"math.xlsx");
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);
    FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    String cellFormula = cell.getCellFormula();
    System.out.println(cellFormula);
    CellValue evaluate = formulaEvaluator.evaluate(cell);
    String value = evaluate.formatAsString();
    System.out.println(value);
}