【开发指南】Spring Cloud集成POI完成Excel读写操作

·  阅读 1887

POI简介

Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft office格式档案读和写的功能,其中包括Excel,Word,PowerPoint等。

官方网站:poi.apache.org/

POI前置知识

1. 坐标

Excel中每一个单元格都是有一个坐标的,起始坐标为(0,0),也就是说起始行为第0行,起始列为第0列。

20211209003052.png

Excel中每一个Sheet也是有下标的,从0开始。

20211208191043.png

2. 数据类型

POI中定义了6种单元格数据类型,每一个数据类型使用一个数字常量来表示:

  • CELL_TYPE_NUMERIC:值是0,数字类型和日期类型(int,float,Date...)
  • CELL_TYPE_STRING:值是1,字符串类型(String)
  • CELL_TYPE_FORMULA:值是2,计算公式类型
  • CELL_TYPE_BLANK:值是3,空类型,表示单元格里什么都没有(null)
  • CELL_TYPE_BOOLEAN:值是4,布尔类型(boolean)
  • CELL_TYPE_ERROR:值是5,类型错误

存储在Excel中最常见的莫过于字符数据和数值数据了:

  • 字符数据类似于"abc"、"ab123"等,POI默认以CELL_TYPE_STRING类型存储,通过POI转成Java中的类型是String。

  • 数值和日期数据类似于"123"、"2021/12/8"(将日期使用特定算法计算成数字)等,POI默认以CELL_TYPE_NUMERIC类型存储,通过POI转成Java中的类型是double。

    如果Excel需要存储类似于手机号这样的数据,默认是CELL_TYPE_NUMERIC类型,但是可以自行修改类型为CELL_TYPE_STRING。

  • 布尔数据类似于"TRUE"、"true",POI默认以CELL_TYPE_BOOLEAN类型存储,通过POI转成Java中的类型是boolean。

3. 文件类型

Excel经历了两次大变革,就是在Excel 2003和Excel 2007。

在Excel 2003之前,只支持后缀为 .xls 的文件,而Excel 2007之后支持了后缀为 .xls.xlsx 两种后缀的文件。

因此POI针对 .xls.xlsx 两种文件分别提供了两套独立的读写接口,分别是HSSF和XSSF。

4. 前置工作

在xml文件中导入依赖:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
复制代码

Excel基础读写

1. HSSF

1.1 写操作

@Test
public void testHSSFWrite() throws IOException {
    // 创建文件
    Workbook workbook = new HSSFWorkbook();
    // 创建Sheet
    Sheet sheet = workbook.createSheet("sheet1");
    // 创建第0行
    Row row = sheet.createRow(0);
    // 在第0行上创建第0个单元格
    Cell cell = row.createCell(0);
    // 在(0,0)处写入内容
    cell.setCellValue("Hello POI");

    // 写入
    FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\hssf-write.xls");
    workbook.write(outputStream);

    // 关闭输出流
    outputStream.close();
}
复制代码

1.2 读操作

@Test
public void testHSSFRead() throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\hssf-read.xls");

    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    // 读出(0,0)处的字符内容
    String content = cell.getStringCellValue();
    System.out.println(content);

    inputStream.close();
}
复制代码

2. XSSF

2.1 写操作

@Test
public void testXSSFWrite() throws IOException {
    // 创建工作簿
    Workbook workbook = new XSSFWorkbook();
    // 创建Sheet
    Sheet sheet = workbook.createSheet("sheet1");
    // 创建第0行
    Row row = sheet.createRow(0);
    // 在第0行上创建第0个单元格
    Cell cell = row.createCell(0);
    // 在(0,0)处写入内容
    cell.setCellValue("Hello POI");

    // 写入
    FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\xssf-write.xlsx");
    workbook.write(outputStream);

    // 关闭输出流
    outputStream.close();
}
复制代码

2.2 读操作

@Test
public void testXSSFRead() throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\xssf-read.xlsx");

    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    // 读出(0,0)处的字符内容
    String content = cell.getStringCellValue();
    System.out.println(content);

    inputStream.close();
}
复制代码

Excel大数据读写

使用HSSF一次最多只能读写65535行记录(列不限),如果超过65535行,则无法读写。

使用XSSF一次最多能读写1048576行和16384列记录,理论上可以读写大数据,但是速度会非常慢,时间不可控,还可能会造成内存溢出。原因是只要没有读写结束,数据就会一直存储在内存中,无论该数据是否已经持久化,这样以来如果是大数据场景,就会造成内存溢出。

因此POI针对大数据提供了一套专门的读写接口,SXSSF,SXSSF只支持后缀为 .xlsx 文件。

1. 读操作

@Test
public void testHSSFWrite() throws IOException {
    // 创建工作簿
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
    // 创建Sheet
    Sheet sheet = sxssfWorkbook.createSheet("sheet1");
    // 创建第0行
    Row row = sheet.createRow(0);
    // 在第0行上创建第0个单元格
    Cell cell = row.createCell(0);
    // 在(0,0)处写入内容
    cell.setCellValue("Hello POI");

    // 写入
    FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\sxssf-write.xlsx");
    sxssfWorkbook.write(outputStream);

    // 清除内存中临时文件
    sxssfWorkbook.dispose();

    // 关闭输出流
    outputStream.close();
}
复制代码

2. 写操作

@Test
public void testSXSSFRead() throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\sxssf-read.xlsx");

    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
    // 内存窗口大小为1000,表示能从Sheet窗口最多看到1000跳新创建的数据
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, 1000);

    Sheet sheet = xssfWorkbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    // 读出(0,0)处的字符内容
    String content = cell.getStringCellValue();
    System.out.println(content);

    inputStream.close();
}
复制代码

POI实战

将文件名为student-grade.xlsx的Excel表中的数据全部读入到程序中。

20211209170439.png

  1. xml文件中引入POI依赖

    <!--  POI  -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.14</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.14</version>
    </dependency>
    复制代码
  2. 自定义单元类型异常CellTypeException

  3. 在ResultCode中建立单元类型异常枚举对象

  4. 将单元类型异常配置进全局异常

  5. 创建存储读入数据的对象

    @Data
    public class Student {
    
        private String university;
    
        private String studentId;
    
        private String name;
    
        private Integer score;
    
        private Integer credit;
    
        private Date gmtCreate;
    
    }
    复制代码
  6. 创建POIUtils

    /**
     * POI工具类
     * @author admin
     */
    public class POIUtils {
    
        /**
         * 获取单元格的值
         * @param cell 单元格
         * @return 单元格的值
         */
        public static Object getCellValue(XSSFCell cell) {
            Object value = null;
    
            if (cell != null) {
                // 获取cell类型
                int type = cell.getCellType();
                // Numeric类型
                if (type == XSSFCell.CELL_TYPE_NUMERIC) {
                    // 判断是日期类型还是数值类型
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        value = cell.getDateCellValue();
                    } else {
                        value = cell.getNumericCellValue();
                    }
                }
                // String类型
                else if (type == XSSFCell.CELL_TYPE_STRING) {
                    value = cell.getStringCellValue();
                }
                // Boolean类型
                else if (type == XSSFCell.CELL_TYPE_BOOLEAN) {
                    value = cell.getBooleanCellValue();
                }
                // Error类型
                else if (type == XSSFCell.CELL_TYPE_ERROR) {
                    throw new CellTypeException(ResultCode.CELL_TYPE_ERROR);
                }
            }
    
            return value;
        }
    
        /**
         * CELL_TYPE_STRING ——> String
         * @param cell 单元
         * @return 数据
         */
        public static String getStringValue(XSSFCell cell) {
            return (String) getCellValue(cell);
        }
    
        /**
         * CELL_TYPE_NUMERIC(数字,科学计数法) ——> String(正常)
         * @param cell 单元
         * @return 数据
         */
        public static String getDouble2StringValue(XSSFCell cell) {
            // 将科学计数法表示的double数据四舍五入成整形数据
            BigDecimal bigDecimal = BigDecimal.valueOf((Double) getCellValue(cell)).setScale(0, BigDecimal.ROUND_DOWN);
    
            return bigDecimal.toString();
        }
    
        /**
         * CELL_TYPE_NUMERIC(数字) ——> Integer
         * @param cell 单元
         * @return 数据
         */
        public static Integer getDouble2IntegerValue(XSSFCell cell) {
            // 将double数据四舍五入成整形数据
            return (int) Math.round((double) getCellValue(cell));
        }
    
        /**
         * CELL_TYPE_NUMERIC(日期) ——> Date
         * @param cell 单元
         * @return 数据
         */
        public static Date getDateValue(XSSFCell cell) {
            return (Date) getCellValue(cell);
        }
    
        /**
         * 填充Student属性
         * @param columnIndex 单元列下标
         * @param cell 单元
         * @param student Student对象
         */
        public static void setStudentProperty(int columnIndex, XSSFCell cell, Student student) {
            if (columnIndex == 0) {
                student.setUniversity(getStringValue(cell));
            } else if (columnIndex == 1) {
                student.setStudentId(getDouble2StringValue(cell));
            } else if (columnIndex == 2) {
                student.setName(getStringValue(cell));
            } else if (columnIndex == 3) {
                student.setScore(getDouble2IntegerValue(cell));
            } else if (columnIndex == 4) {
                student.setCredit(getDouble2IntegerValue(cell));
            } else {
                student.setGmtCreate(getDateValue(cell));
            }
        }
    
    }
    复制代码

    工具类中除了第一个方法固定不变外,其他方法都需要视具体要导入的数据而定。

    比如现在需要导入student-grade.xlsx中的数据,在其每一行数据中,学校、姓名字段数据使用getStringValue方法接收,学号字段数据使用getDouble2StringValue方法接收,成绩、学分字段数据使用getDouble2IntegerValue方法接收,上传日期使用getDateValue方法接收,接收到数据之后需要使用setStudentProperty方法装配到PO中来持久化到数据库。

  7. 创建读操作方法

    @Test
    public void read() throws IOException {
        FileInputStream inputStream = new FileInputStream("G:\\file\\student-grade.xlsx");
    
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
    
        // 获取行数
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rowCount; i ++) {
            XSSFRow row = sheet.getRow(i);
    
            Student student = new Student();
            // 获取列数
            int cellCount = row.getPhysicalNumberOfCells();
            for (int j = 0; j < cellCount; j ++) {
                XSSFCell cell = row.getCell(j);
                // 装载数据
                POIUtils.setStudentProperty(j, cell, student);
            }
    
            // 将student持久化到数据库
        }
    
        inputStream.close();
    }
    复制代码
分类:
后端
分类:
后端
收藏成功!
已添加到「」, 点击更改