Apache POI组件在SpringBoot工程中使用

一、首先导入POI依赖

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

二、将数据写入到Excel

@Test
    public void PoiExportTest() throws Exception {
        //创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //添加sheet页
        XSSFSheet sheet = workbook.createSheet("用户列表");
        //添加第一行数据
        XSSFRow row0 = sheet.createRow(0);
        //添加数据列
        XSSFCell cellName1 = row0.createCell(1);
        cellName1.setCellValue("姓名");
        XSSFCell cellAddr = row0.createCell(2);
        cellAddr.setCellValue("城市");
        //添加第二行数据
        XSSFRow row1 = sheet.createRow(1);
        //添加列数据
        XSSFCell cellName2 = row1.createCell(1);
        cellName2.setCellValue("张三");
        XSSFCell cellAddr2 = row1.createCell(2);
        cellAddr2.setCellValue("北京");
        //添加第三行数据
        XSSFRow row2 = sheet.createRow(2);
        XSSFCell cellName3 = row2.createCell(1);
        cellName3.setCellValue("王五");
        XSSFCell cellAddr3 = row2.createCell(2);
        cellAddr3.setCellValue("上海");

        FileOutputStream out = new FileOutputStream(new File("D:\\itcast.xlsx"));
        workbook.write(out);

        out.close();
        workbook.close();
    }

三、读取Excel文件中的数据

@Test
    public void PoiImportTest() throws Exception {
        InputStream in = new FileInputStream("D:\\itcast.xlsx");
        //根据文件的输入流创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(in);
        //读取sheet页
        XSSFSheet sheet = workbook.getSheetAt(0);
        //读取每一行
        XSSFRow row = sheet.getRow(0);
        //读取第一列
        XSSFCell cellNameHead = row.getCell(1);
        String nameHead = cellNameHead.getStringCellValue();


        //读取第二列
        XSSFCell cellAddrHead = row.getCell(2);
        String addrHead = cellAddrHead.getStringCellValue();
        System.out.println(nameHead + ":" + addrHead);
        //获取sheet页中的最后一行的行号
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
            //获取行
            XSSFRow dataRow = sheet.getRow(i);
            XSSFCell cellName = dataRow.getCell(1);
            String name = cellName.getStringCellValue();
            XSSFCell cellAddr = dataRow.getCell(2);
            String addr = cellAddr.getStringCellValue();
            System.out.println(name + ":" + addr);
        }
        in.close();
        workbook.close();
    }