SpringBoot集成Apache POI对excel的基本操作

目录

前言

导入依赖

功能调用

1 创建Excel文件

2 创建sheet

3 创建sheet

4 读取sheet

5 创建行和单元格

6 读取行和单元格

7 设置单元格内容

8 读取单元格内容

全部示例代码

---

前言

Apache是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能,本文我们来介绍一下其对Excel的操作

注:本文仅做基本功能的简单演示,更多详细实现请浏览其官网:

Busy Developers' Guide to HSSF and XSSF Features (apache.org)

导入依赖

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

功能调用

1 创建Excel文件

 使用方法

Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
String fileName = "test.xls";
//String fileName = "test.xlsx";
OutputStream output = new FileOutputStream(fileName)
wb.write(output);

 示例代码

2 创建sheet

 使用方法

File file = new File("test.xlsx");
FileInputStream input = new FileInputStream(file);
WorkBook wb = new XSSFWorkbook(input);
//WorkBook wb = new HSSFWorkbook(input);

 示例代码

3 创建sheet

  使用方法

wb.createSheet();
wb.createSheet("sheet1");

  示例代码 

4 读取sheet

  使用方法

wb.getSheet("sheetname");

5 创建行和单元格

  使用方法

//创建行
Row row = sheet.create(0);
//创建单元格
Cell cell = row.create(0);

6 读取行和单元格

  使用方法

//读取行
Row row = sheet.getRow(0);
//读取单元格
Cell cell = row.getCell(0);

7 设置单元格内容

  使用方法

cell.setValue("value");

   示例代码

8 读取单元格内容

 使用方法

//读取文本类型的数据
String strValue = cell.getStringCellValue();
//读取数字类型的数据
double number = cell.getNumericCellValue();
//读取日期类型的数据
Date date = cell.getDateCellValue();

 

全部示例代码

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.*;
import java.util.Date;

@SpringBootTest(classes = ExcelTest.class)
public class ExcelTest {

    /**
     * 创建一个EXCEL文件
     */
    @Test
    public void createHSSFWorkbook(){
        Workbook wb1 = new HSSFWorkbook();
        String fileName1 = "C:\\Users\\rainkyzhong\\Desktop\\test.xls";
        try  (OutputStream fileOut = new FileOutputStream(fileName1)) {
            wb1.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }

        Workbook wb2 = new XSSFWorkbook();
        String fileName2 = "C:\\Users\\rainkyzhong\\Desktop\\test.xlsx";
        try  (OutputStream fileOut = new FileOutputStream(fileName2)) {
            wb2.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取一个EXCEL文件
     */
    private Workbook openExcel(FileInputStream input) throws IOException {
        //将数据解析成WordBook对象
        Workbook wb = new XSSFWorkbook(input);
        return wb;
    }

    /**
     * 创建一个sheet并保存
     */
    @Test
    public void createSheet() throws IOException {
        File file = new File("C:\\Users\\rainkyzhong\\Desktop\\test.xlsx");

        FileInputStream input = new FileInputStream(file);
        Workbook wb = openExcel(input);
        wb.createSheet();
        input.close();

        //保存
        FileOutputStream output = new FileOutputStream(file);
        wb.write(output);
        output.close();
        wb.close();
    }

    /**
     * 修改sheet
     */
    @Test
    public void writeSheet() throws IOException {
        File file = new File("C:\\Users\\rainkyzhong\\Desktop\\test.xlsx");
        FileInputStream input = new FileInputStream(file);
        Workbook wb = openExcel(input);

        Sheet sheet = wb.getSheet("sheet1");

        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        cell.setCellValue("Hello World");

        //保存
        FileOutputStream output = new FileOutputStream(file);
        wb.write(output);
        output.close();
        wb.close();
    }

    /**
     * 读取sheet
     */
    @Test
    public void readSheet() throws IOException {
        File file = new File("C:\\Users\\rainkyzhong\\Desktop\\test.xlsx");
        FileInputStream input = new FileInputStream(file);
        Workbook wb = openExcel(input);

        Sheet sheet = wb.getSheet("sheet1");

        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        
        String strValue = cell.getStringCellValue();
        
        System.out.println(strValue);
    }
}