Java POI读写Excel文档

简介

• 由apache公司提供
• Java编写的免费开源的跨平台的Java API
• 提供API给Java程序对Microsoft Office格式档案读和写的功能

包结构

• HSSF读写Microsoft Excel XLS(2003版本的Excel)
• XSSF读写Microsoft Excel OOXML XLSX
• HWPF读写Microsoft Word Doc
• HSLF提供读写Microsoft PowerPoint

基础使用

• 创建maven项目

• 导入依赖

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

入门案例

• 理解

  

• 从Excel文件读取数据

  • 创建工作簿

  • 获取工作表

  • 遍历工作表获得对象

  • 遍历对象获取单元格对象

  • 获得单元格中的值

    注意: 被读取的文件是要被关闭的

    /**
     * @author coder-itl
     * @description 读取2007版本以上Excel的数据
     * @createDate 2023/12/5 9:47
     * @Entity com.example.poi.XLSXDemo
     */
    @Test
    public void readXLSX() {
        try {
            // 获取文件
            File file = new File("D:\\hello.xlsx");
            FileInputStream fs = new FileInputStream(file);
            // 创建工作簿
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fs);
            // 获取工作表(根据工作表名称获取)
            int getSheetByName = xssfWorkbook.getSheetIndex("Sheet1");
            // 获取工作表(根据索引获取,索引从 0 开始)
            XSSFSheet getSheetByIndex = xssfWorkbook.getSheetAt(0);
            // 从工作表中获取每一行
            for (Row row : getSheetByIndex) {
                // 在每一行中获取每一个单元格
                for (Cell cell : row) {
                    // 获取单元格中的内容
                    String stringCellValue = cell.getStringCellValue();
                    System.out.println(stringCellValue);
                }
            }
            // 关闭资源
            xssfWorkbook.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    

    /**
     * @author coder-itl
     * @description 读取2007版本以上Excel的数据
     * @createDate 2023/12/5 9:47
     * @Entity com.example.poi.XLSXDemo
     */
    
    package com.example.poi;
    
    import org.apache.poi.xssf.usermodel.*;
    import org.junit.jupiter.api.Test;
    
    import java.io.File;
    import java.io.FileInputStream;
    
    public class XLSXDemo {
        @Test
        public void readXLSX() {
            try {
                // 获取文件
                File file = new File("D:\\hello.xlsx");
                FileInputStream fs = new FileInputStream(file);
                // 创建工作簿
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fs);
                // 获取工作表(根据工作表名称获取)
                int getSheetByName = xssfWorkbook.getSheetIndex("Sheet1");
                // 获取工作表(根据索引获取,索引从 0 开始)
                XSSFSheet getSheetByIndex = xssfWorkbook.getSheetAt(0);
    
                // 开始索引: 0,结束索引: 最后一行
                int lastRowNum = getSheetByIndex.getLastRowNum();
                // 遍历的次数的确定
                for (int i = 0; i <= lastRowNum; i++) {
                    // 获取行
                    XSSFRow row = getSheetByIndex.getRow(i);
                    if (row != null) {
                        // 获取单元格
                        short lastCellNum = row.getLastCellNum();
                        for (int j = 0; j <= lastCellNum; j++) {
                            XSSFCell cell = row.getCell(j);
                            // 单元格不为空的时候,获取他的值
                            if (cell != null) {
                                // 单元格的值
                                String stringCellValue = cell.getStringCellValue();
                                System.out.println(stringCellValue);
                            }
                        }
                    }
                }
                // 关闭资源
                xssfWorkbook.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }
    
    

• 向Excel中写入文件

  • 创建一个Excel文件

  • 创建工作表

  • 创建行

  • 创建单元格赋值

  • 通过输出流将对象下载到磁盘

    @Test
    public void writeToXLSX() {
        // 1. 创建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // 2. 根据工作簿对象创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("工作表");
        // 3. 根据工作表创建行
        XSSFRow row = sheet.createRow(0);
        // 4. 根据行创建列,并写入数据
        row.createCell(0).setCellValue("测试纸1");
        row.createCell(1).setCellValue("测试纸2");
        row.createCell(2).setCellValue("测试纸3");
        // 输出流
        try {
            FileOutputStream os = new FileOutputStream("D:\\aa.xlsx");
            xssfWorkbook.write(os);
            os.flush();
            os.close();
            xssfWorkbook.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    

实战练习

• 读取Excel数据到数据库

• 将数据库数据写入到Excel

• 准备测试数据模型

  

• 创建实体

  /**
   * @author coder-itl
   * @description 产品表实体
   * @createDate 2023/12/5 11:14
   * @Entity com.example.entity.Product
   */
  
  package com.example.entity;
  
  import lombok.*;
  
  @Data
  @NoArgsConstructor
  @AllArgsConstructor
  public class Product {
      private Integer pid;
      private String pname;
      private Double price;
      private Integer pstock;
  }
  
  

• 创建mapper

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.example.mapper.ProductMapper">
      <!-- int batchSave(Product product); -->
      <insert id="batchSave">
          insert into product
          values (#{pid}, #{pname}, #{price}, #{pstock})
      </insert>
      <!-- List<Product> findAll();-->
      <select id="findAll" resultType="product">
          select *
          from product
      </select>
  </mapper>
  

• 创建service

  /**
   * @author coder-itl
   * @description
   * @createDate 2023/12/5 12:17
   * @Entity com.example.service.impl.PrProductServiceImpl
   */
  
  package com.example.service.impl;
  
  import com.example.entity.Product;
  import com.example.mapper.ProductMapper;
  import com.example.service.ProductService;
  import com.example.utils.MybatisUtils;
  import org.apache.ibatis.session.SqlSession;
  
  import java.util.List;
  
  public class PrProductServiceImpl implements ProductService {
      @Override
      public int saveXLSX(List<Product> products) {
          SqlSession connection = MybatisUtils.getConnection();
          ProductMapper productMapper = connection.getMapper(ProductMapper.class);
          Integer i = null;
          for (Product product : products) {
              i = productMapper.batchSave(product);
          }
          return i;
      }
  
      @Override
      public List<Product> findAll() {
          SqlSession connection = MybatisUtils.getConnection();
          ProductMapper productMapper = connection.getMapper(ProductMapper.class);
          return productMapper.findAll();
      }
  }
  
  

• 创建mybatis工具类

  /**
   * @author coder-itl
   * @description Mybatis的工具包
   * @createDate 2023/12/5 11:16
   * @Entity com.example.utils.MybatisUtils
   */
  
  package com.example.utils;
  
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.*;
  
  import java.io.InputStream;
  
  public class MybatisUtils {
      private static SqlSessionFactory factory = null;
  
      static {
          InputStream in = null;
          try {
              in = Resources.getResourceAsStream("mybatis-config.xml");
              factory = new SqlSessionFactoryBuilder().build(in);
          } catch (Exception e) {
              throw new RuntimeException(e);
          }
      }
  
      public static SqlSession getConnection() {
          SqlSession sqlSession = null;
          if (factory != null) {
              sqlSession = factory.openSession(true);
          }
          return sqlSession;
      }
  }
  
  

• 创建控制器

  /**
   * 作者：coder-itl
   * 描述：用户交互程序
   * 创建日期：2023/12/5 11:37
   * 实体：com.example.web.Show
   */
  package com.example.web;
  
  import com.example.entity.Product;
  import com.example.service.ProductService;
  import com.example.service.impl.PrProductServiceImpl;
  import org.apache.poi.ss.usermodel.Cell;
  import org.apache.poi.ss.usermodel.CellType;
  import org.apache.poi.xssf.usermodel.*;
  
  import java.io.*;
  import java.util.*;
  
  public class Show {
      // 创建 ProductService 实例
      private static ProductService productService = new PrProductServiceImpl();
  
      public static void main(String[] args) {
          // 创建一个用于读取用户输入的 Scanner 对象
          Scanner scanner = new Scanner(System.in);
  
          // 提示用户选择功能
          System.out.print("请选择你要执行的功能[1.导入,2:导出]: ");
          // 获取用户输入的选择
          int select = scanner.nextInt();
  
          if (select == 1) {
              // 导入功能
              // 1.1 读取 Excel 文件内容
              // 1.2 将读取到的数据写入到数据库
              // 调用 readXLSX 方法读取 Excel 文件内容
              System.out.print("请输入文件的位置: ");
              String path = scanner.next();
              List<Product> products = readXLSX(path);
              // 打印读取到的数据
              printList(products);
              // TODO 将数据写入到数据库中
              int isSave = productService.saveXLSX(products);
              if (isSave > 0) {
                  System.out.println("数据已经存储到数据库中....");
              }
          } else if (select == 2) {
              // 导出功能
              // 读取数据库中的内容
              List<Product> productList = productService.findAll();
              System.out.println(productList);
              // 写入到 Excel 文件中
              // TODO: 添加导出功能的实现
              System.out.print("请输入要写入的文件的位置: ");
              String path = scanner.next();
              writeToXSLX(productList, path);
              System.out.println("数据已经写入完毕,文件位置在: " + path);
          } else {
              // 选择无效，提示重新启动
              System.out.println("请重新启动!");
          }
      }
  
      // 将产品列表写入到 Excel 文件中
      private static void writeToXSLX(List<Product> productList, String path) {
          try {
              File file = new File(path);
              // 如果文件不存在，则创建文件
              if (!file.exists()) {
                  file.createNewFile();
              }
              XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
              XSSFSheet sheet = xssfWorkbook.createSheet("product");
              // 创建行
              XSSFRow row = sheet.createRow(0); // 表头行
              // 列
              row.createCell(0).setCellValue("商品编号"); // 第一列
              row.createCell(1).setCellValue("商品名称"); // 第二列
              row.createCell(2).setCellValue("商品价格(单位:元/斤)"); // 第三列
              row.createCell(3).setCellValue("商品库存(单位:吨)"); // 第四列
              // 从第二行开始,填充数据库读取到的数据
              for (int i = 0; i < productList.size(); i++) {
                  XSSFRow writeDbRow = sheet.createRow(i + 1);
                  writeDbRow.createCell(0).setCellValue(productList.get(i).getPid());
                  writeDbRow.createCell(1).setCellValue(productList.get(i).getPname());
                  writeDbRow.createCell(2).setCellValue(productList.get(i).getPrice());
                  writeDbRow.createCell(3).setCellValue(productList.get(i).getPstock());
              }
              // 通过输出流写出到磁盘中
              FileOutputStream os = new FileOutputStream(file);
              xssfWorkbook.write(os);
              os.flush();
              os.close();
              xssfWorkbook.close();
          } catch (Exception e) {
              throw new RuntimeException(e);
          }
      }
  
      // 打印产品列表
      private static void printList(List<Product> products) {
          System.out.println("读取Excel表格数据中..............");
          // 遍历产品列表并打印每个产品的信息
          for (int i = 0; i < products.size(); i++) {
              System.out.println(products.get(i));
          }
          System.out.println("读取结束..............");
      }
  
      // 从 Excel 文件中读取数据并返回产品列表
      public static List<Product> readXLSX(String path) {
          List<Product> productList = new ArrayList<>();
          try {
              // Excel 文件位置
              FileInputStream fs = new FileInputStream(new File(path));
              // 获取工作簿
              XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fs);
              // 获取工作表
              XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
  
              // 获取总行数
              int lastRowNum = sheet.getLastRowNum();
              // 从第二行开始遍历每一行数据
              for (int i = 1; i <= lastRowNum; i++) {
                  XSSFRow row = sheet.getRow(i);
                  if (row != null) {
                      List<String> list = new ArrayList<>();
                      // 遍历每一列数据
                      for (Cell cell : row) {
                          if (cell != null) {
                              // 将列的格式设置为字符串
                              cell.setCellType(CellType.STRING);
                              // 获取单元格的字符串值并添加到列表中
                              String stringCellValue = cell.getStringCellValue();
                              if (stringCellValue != null && !"".equals(stringCellValue)) {
                                  list.add(stringCellValue);
                              }
                          }
                      }
                      // 如果列表中有数据，创建 Product 对象并添加到产品列表中
                      if (list.size() > 0) {
                          Product product = new Product(Integer.valueOf(list.get(0)), list.get(1),
                                  Double.parseDouble(list.get(2)), Integer.valueOf(list.get(3)));
                          productList.add(product);
                      }
                  }
              }
          } catch (Exception e) {
              // 发生异常时抛出运行时异常
              throw new RuntimeException(e);
          }
          // 返回产品列表
          return productList;
      }
  }