Detailed explanation of the basic use of Apache POI

Basic Introduction
Getting Started Test (Reading Data from Excel Files)

Step 1: Import Maven coordinates
Step 2: Create an Excel file
Step 3: Write test code
Code description and extension

Getting Started Test (Writing Data to Excel File)

Basic Introduction

POI

Apache POI is a free, open source, cross-platform Java API written in Java. Apache POI provides an API for Java programs to read and write Microsoft Office format files.
The most commonly used method is to use POI to operate Excel files.
It can also operate other forms of documents such as word

jxl: Specialized in Excel operations, specially used to operate Excel

To use POI, you need to import Maven coordinates

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

POI structure: When operating in different document formats, corresponding classes will be provided

HSSF - Provides the ability to read and write Microsoft Excel XLS format files
XSSF - provides the ability to read and write Microsoft Excel OOXML XLSX format files
HWPF - provides the ability to read and write Microsoft Word DOC format files
HSLF - Provides the ability to read and write Microsoft PowerPoint format files
HDGF - Provides the ability to read Microsoft Visio format files
HPBF - Provides the ability to read Microsoft Publisher format files
HSMF - Provides the ability to read Microsoft Outlook format files

Getting Started Test (Reading Data from Excel Files)

Using POI you can read data from an existing Excel file

Step 1: Import Maven coordinates

Here is the second step

Step 2: Create an Excel file

insert image description here

Step 3: Write test code

package com.yy.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;

/**
 * @author Marston
 * @date 2021/10/29
 */
public class POITest {
    @Test
    public void test() throws Exception {
        // Pass in an input stream, load the specified file, and create an Excel object (workbook)
        XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File("E:\\testNomal\\poi.xlsx")));
        //Read the first Sheet tab in the Excel file XSSFSheet sheet = excel.getSheetAt(0);
        //There are many rows in a sheet page. Traverse the sheet tab page and get each row of data for (Row row : sheet) {
            //Traverse the rows and get each cell object for (Cell cell : row) {
                //cell represents the cell object System.out.println(cell.getStringCellValue()); //getStringCellValue The second column is a numeric value and cannot be converted to a String type, so an error is reported //Just change the content of the second column in the Excel table to a string type. }
        }
        //Close the Excel fileexcel.close();
    }
}

Running results:

insert image description here

Because this is an introductory case, I will change the type to the following and modify the contents of the Excel file:

insert image description here

Code description and extension

From the above introductory case, we can see that POI encapsulates several core objects for operating Excel tables:

XSSFWorkbook: Workbook
XSSFSheet: Worksheet
Row: Row
Cell: cell

The above case is to traverse the worksheet to obtain the rows, traverse the rows to obtain the cells, and finally obtain the values in the cells.

Another way is to get the last row number of the worksheet, and then get the row object according to the row number, get the last cell index through the row, and then get a cell object for each row according to the cell index. The code is as follows:

package com.yy.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;

/**
 * @author Marston
 * @date 2021/10/29
 */
public class POITest {
    @Test
    public void test2() throws Exception {
        // Pass in an input stream, load the specified file, and create an Excel object (workbook)
        XSSFWorkbook excel = new XSSFWorkbook(new FileInputStream(new File("E:\\testNomal\\poi.xlsx")));
        //Read the first Sheet tab in the Excel file XSSFSheet sheet = excel.getSheetAt(0);
        //Get the row number of the last row of the current worksheet. The row number starts from 0 int lastRowNum = sheet.getLastRowNum();
        System.out.println("lastRowNum: "+lastRowNum);
        for(int i=0;i<=lastRowNum;i++){
            //Get each row according to the row number XSSFRow row = sheet.getRow(i);
            //Get the last cell index of the current row short lastCellNum = row.getLastCellNum();
            System.out.println("lastCellNum: "+lastCellNum);
            for(short j=0;j<lastCellNum;j++){
                //Get cell content based on cell index String value = row.getCell(j).getStringCellValue();
                System.out.println(value);
            }
        }
        //Close the Excel fileexcel.close();
    }
}

insert image description here

Getting Started Test (Writing Data to Excel File)

Test code:

//Use POI to write data to the Excel file, and save the created Excel file to the local disk through the output stream //@Test
    public void test3() throws Exception{
        //Create an Excel file (workbook) in memory
        XSSFWorkbook excel = new XSSFWorkbook();
        //Create a worksheet object named: POI write test XSSFSheet sheet = excel.createSheet("POI write test");
        //Create row objects in the worksheet and create XSSFRow in the first row title = sheet.createRow(0);
        //Create a cell object in the row title.createCell(0).setCellValue("Name"); //Content of the first column title.createCell(1).setCellValue("Address");
        title.createCell(2).setCellValue("age");

		//Create XSSFRow in the second row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue("Xiao Ming");
        dataRow.createCell(1).setCellValue("Beijing");
        dataRow.createCell(2).setCellValue("20");

        //Create an output stream and write the Excel file in memory to disk through the output stream FileOutputStream out = new FileOutputStream(new File("e:\\hello.xlsx"));
        excel.write(out);//write out.flush();//refresh excel.close();
    }

insert image description here

This is the end of this article about the basic use of Apache POI. For more information about the use of Apache POI, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Apache POI converts PPT into image example code
Implementation method of exporting excel according to template based on apache poi
Generate Word document sample code using Java Apache POI
Java backend uses Apache poi to generate excel documents and provide front-end download examples
Java Apache poi reads and writes word doc files
Java example of using Apache POI library to read Excel spreadsheet documents
Simple example of using Apache POI to read word files in Java

<<: CSS3 uses transform deformation combined with events to complete fan-shaped navigation

>>: How to fix the footer at the bottom of the page (multiple methods)