Java-poi实现Excel表格生成与读取

//引入依赖
<!--    poi-->
    <!--xls(03)-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <!--xlsx(07)-->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
      <groupId>joda-time</groupId>
      <artifactId>joda-time</artifactId>
      <version>2.10.1</version>
    </dependency>

package com.gg.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;


/**
 * @title: CreateExcelTest
 * @Author: NightRain
 * @Date: 2022/6/1 10:18
 * @Version 1.0
 * @Description:  使用java生成和解析excel文件
 */
public class CreateExcelTest {
    public static void main(String[] args) throws IOException {
          //createExcel();
            parseExcel();
        
    }

    /**
     * 生成excel表格
     * @throws IOException
     */
    public static void createExcel() throws IOException {
        //创建一个工作簿
        Workbook workbook=new XSSFWorkbook();
        //创建一个工作表
        Sheet sheet = workbook.createSheet("班级信息");

        //创建第一行
        Row row1 = sheet.createRow(0);
        //创建单元格11
        Cell cell11 = row1.createCell(0);
        //写入数据
        cell11.setCellValue("性别");
        //创建单元格12
        Cell cell12 = row1.createCell(1);
        //写入数据
        cell12.setCellValue("数量");

        //创建第二行
        Row row2=sheet.createRow(1);
        //创建单元格21
        Cell cell21 = row2.createCell(0);
        //写入数据
        cell21.setCellValue("男");
        //创建单元格22
        Cell cell22 = row2.createCell(1);
        //写入数据
        cell22.setCellValue(31);

        //创建第三行
        Row row3=sheet.createRow(2);
        //创建单元格31
        Cell cell31 = row3.createCell(0);
        //写入数据
        cell31.setCellValue("女");
        //创建单元格32
        Cell cell32 = row3.createCell(1);
        //写入数据
        cell32.setCellValue(1);

        //创建一个工作表 07版本使用xlsx结尾
        FileOutputStream fileOutputStream=new FileOutputStream("C:\\Users\\35125\\Desktop\\1.xlsx");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("excel表生成完毕");
    }

    /**
     * 解析Excel表格
     * @throws IOException
     */
    public static void  parseExcel() throws IOException {
        FileInputStream fileInputStream=new FileInputStream("C:\\Users\\35125\\Desktop\\1.xlsx");
        //获取工作簿
        Workbook workbook=new XSSFWorkbook(fileInputStream);
        //获取第一张表
        Sheet sheetAt = workbook.getSheetAt(0);

        //这里能否自动获取excel表中的行数和每行的单元格数呢？
        //获取所有行数
        int rows=sheetAt.getPhysicalNumberOfRows();

        for(int  i=0;i<rows;i++){
            //获取第i行
            Row row = sheetAt.getRow(i);

            //获取每行的单元格数
            int cells=sheetAt.getRow(0).getPhysicalNumberOfCells();
            for (int j = 0; j < cells; j++) {
                Cell cell = row.getCell(j);
                //读取数据
                switch (cell.getCellType()){
                    case   Cell.CELL_TYPE_NUMERIC :
                        System.out.print(cell.getNumericCellValue()+"  ");
                        break;
                    case   Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue()+"  ");
                        break;
                    case   Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue()+"  ");
                        break;
                    case   Cell.CELL_TYPE_FORMULA:
                        System.out.print(cell.getDateCellValue()+"  ");
                        break;
                    case   Cell.CELL_TYPE_BLANK:
                        System.out.println("null");
                        break;
                }
            }
            //打印换行
            System.out.println();
        }

        //关闭流
        fileInputStream.close();
    }
}