Foreword: when I work, I always use an excel tool class to complete the import and export of Excel. The fool's powerful tool class makes me use API calls carelessly, but in fact, POI doesn't understand how to operate excel. If I change the project or architecture, I don't know how to read Excel with POI, or the complex excel table style won't be exported, so I decided to learn to record it.
catalogue
Formally read and write data in Excel
Read Excel document (to be updated)
What is POI?
Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. This is an open source library developed by the Apache Software Foundation that uses Java to design or modify Microsoft Office files distributed. It contains classes and methods to decode user input data or files into MS Office documents.
Use premise
POI dependency is introduced. POI-OOXML is an upgraded version of POI and provides XSSF Object, and the object that POI provides to operate EXCEL is HSSF.
(because online tutorials only import objects that depend on POI, and then find objects that do not provide xssf workbooks, so look at the differences)
HSSF: Excel97-2003 version with extension xls. The maximum number of rows in a sheet is 65536 and the maximum number of columns is 256.
XSSF: started with Excel 2007 version with extension xlsx. The maximum number of rows in a sheet is 1048576 and the maximum number of columns is 16384.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>POI package structure
HSSF provides the function of reading and writing files in Microsoft Excel XLS format.
XSSF provides the function of reading and writing Microsoft Excel OOXML XLSX format files.
Read data from Excel file
POI API Documentation POI API documentation can be found in
Busy Developers' Guide to HSSF and XSSF Features Quick guide
How does Poi operate Excel files?
Poi encapsulates the XSSFworkbook workbook object, that is, as shown in the figure, the whole excel document, XSSFSheet worksheet object, that is, the lower sheet. An excel document is composed of sheets. Each Row of data is a Row object, and the object of each Cell is a Cell object.
To read data from an Excel document:
- Create Workbook
- Get worksheet
- Traverse the worksheet to get the row object
- Traverse the row object to get the cell object
- Gets the value in the cell
I create a simple excel document locally and try to read it with code, but I need to close the document when reading with code, otherwise an exception will be thrown:
java.io.FileNotFoundException: C:\Users\Administrator\Documents\test2.xlsx (this file is being used by another program and cannot be accessed by the process.)
/**
* @author Claw
* @date 2022/1/4 21:13.
*/
public class readDemo {
public static void main(String[] args) {
// Create workbook object
XSSFWorkbook xssfWorkbook = null;
try {
xssfWorkbook = new XSSFWorkbook("C:\\Users\\Administrator\\Documents\\test2.xlsx");
// Get worksheet getSheet specify sheet name
// XSSFSheet sheet = xssfWorkbook.getSheet("sheet1");
// Get worksheet getSheetAt get from the first sheet, usually using this
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
// Get row
for (Row cells : sheet) {
// Get cell
for (Cell cell : cells) {
// Get the cell content. Assuming that the cell content is in text format at this time, get it with getStringCellValue
String value = cell.getStringCellValue();
System.out.println(value);
}
}
xssfWorkbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}Simply read the EXCEL file:
Write data from Excel file
- Create Workbook
- Create worksheet
- Create row
- Assign values to cells
- Export objects to disk via output stream
Through the example of writing Poi object to excel, we can understand that exporting data to excel is nothing more than the process of creating XSSFWorkbook object, creating sheet () through XSSFWorkbook object, creating row (row) through sheet, and creating cell (cell) through row.
public class writeDemo {
public static void main(String[] args) {
// Create Workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create worksheet
XSSFSheet sheet = workbook.createSheet();
// Create first row
XSSFRow row = sheet.createRow(0);
// Creating Cells
row.createCell(0).setCellValue("puppyCoding");
row.createCell(1).setCellValue("studying");
row.createCell(2).setCellValue("how to use poi");
// Create second row
XSSFRow row2 = sheet.createRow(1);
row2.createCell(0).setCellValue("puppyCoding");
row2.createCell(1).setCellValue("studying");
row2.createCell(2).setCellValue("how to use poi");
try {
// Output stream
FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Documents\\writeDemo1.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.flush();
// Release resources
fileOutputStream.close();
workbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}Formally read and write data in Excel
The Excel document is as shown in the figure:
