第二节 POI集成Excel的导入导出

亮子 2021-09-07 09:56:35 17556 0 0 0

1、添加依赖

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>

为了测试,也可以加入测试依赖:

        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>

为了减少实体类的代码,也可以加入lombok的依赖:

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>

2、导入测试函数

    @Test
    void testImport() throws IOException, InvalidFormatException {
        String fileName = "F:\\test.xlsx";
        List<Object[]> list = new ArrayList<>();
        InputStream inputStream = new FileInputStream(fileName);
        Workbook workbook = WorkbookFactory.create(inputStream);

        int numberOfSheets = workbook.getNumberOfSheets();
        System.out.println("numberOfSheets="+numberOfSheets);

        Sheet sheet = workbook.getSheetAt(0);
        //获取sheet的行数
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            //过滤表头行
            if (i == 0) {
                continue;
            }
            //获取当前行的数据
            Row row = sheet.getRow(i);
            Object[] objects = new Object[row.getPhysicalNumberOfCells()];
            int index = 0;
            for (Cell cell : row) {
                if (cell.getCellType().equals(CellType.NUMERIC)) {
                    objects[index] = (int) cell.getNumericCellValue();
                }
                if (cell.getCellType().equals(CellType.STRING)) {
                    objects[index] = cell.getStringCellValue();
                }
                if (cell.getCellType().equals(CellType.BOOLEAN)) {
                    objects[index] = cell.getBooleanCellValue();
                }
                if (cell.getCellType().equals(CellType.ERROR)) {
                    objects[index] = cell.getErrorCellValue();
                }
                index++;
            }
            list.add(objects);
        }

        System.out.println(list);
    }

3、导出测试函数

    @Test
    void testPoiImport() throws IOException {

        //--1 创建一个不存在的excel文件
        String fileName = "";

        Workbook wb = null;
        if(fileName.endsWith(".xls")) {
            wb = new HSSFWorkbook();
        } else if(fileName.endsWith(".xlsx")) {
            wb = new XSSFWorkbook();
        } else {
            System.out.println("创建文件的格式错误");
            return;
        }

        try{
            OutputStream output = new FileOutputStream(fileName);
            wb.write(output);
        }catch(FileNotFoundException e) {
            System.out.println("文件创建失败,失败原因为:" + e.getMessage());
            throw new FileNotFoundException();
        }
        System.out.println(fileName + "文件创建成功!");

        //--2 创建sheet
        String sheetName = "学生信息";
        Sheet sheet = wb.getSheet(sheetName);

        if(sheet == null) {
            System.out.println("表单" + sheetName + "不存在,试图创建该sheet,请稍后……");
            sheet = wb.createSheet(sheetName);
            System.out.println("名为" + sheetName +"的sheet创建成功!");
        }

        //--3 创建行
        int rowNum = 1;
        Row row = sheet.getRow(rowNum);

        if(row == null) {
            System.out.println("行号为:" + rowNum + "的行不存在,正试图创建该行,请稍后……");
            row = sheet.createRow(rowNum);
            System.out.println("行号为:" + rowNum + "的行创建成功!");
        }

        //--4 创建单元格
        int cellNum = 1;
        Cell cell = row.getCell(cellNum);

        if(cell == null) {
            System.out.println("该单元格不存在,正在试图创建该单元格,请稍后……");
            cell = row.createCell(cellNum);
            System.out.println("该单元格创建成功!");
        }

        System.out.println("excel导出成功");
    }

参考文档