0x01_ Directly upload the code and convert the data into Java objects
0x02_POI Introduction
0x03_How does Poi read and operate the table?
0x04_ encapsulated into a tool class
How to convert Excel data into java objects? Use of POI
In recent projects, the business of importing excel and parsing excel is used, so here is a special record .
0x01_ Directly upload the code and convert the data into Java objects
< p data-tool='mdnice editor' > Prepare an excel sheet in advance, and then prepare some data:
Then prepare the entity class Good
import lombok.AllArgsConstructor;
import lombok. Data;
@Data
@AllArgsConstructor
public class Good {
private Integer id;
private String name;
private< /span> Integer count;
private Double price;
private String description;
}
import org.apache.poi.ss.usermodel.Cell;
import< /span> org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org .apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java. io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
public class PoiRead {
public static void main(String[] args) {
try {
List<Good> ; goods = read('path to xlsx');
goods.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
}
}
public static List<Good> read(String path) throws Exception {
List<Good> goods = new ArrayList<> ;();
// 1. Create input stream
FileInputStream fip = new FileInputStream(path);
// 2. Get the workbook from the input stream
XSSFWorkbook workbook = new XSSFWorkbook(fip);
// 3. Get the target worksheet in the workbook
Sheet sheet = workbook.getSheetAt(0);
// 4. Get the number of rows in the worksheet (with data)
int rowNum = sheet.getPhysicalNumberOfRows();
// 5. Traverse all the rows, but note that the title of the first row is not obtained, so start from subscript 1< /span>
for(int i = 1;i<rowNum;i++){
// get All rows
Row row = sheet.getRow(i);
if(row!=null){
//The collection used to save each piece of data
List<String> list = new ArrayList<>();
for ( Cell cell : row) {
if(cell!=null){
//Set cells are all string type
cell.setCellType(CellType.STRING);
//Get all cell data
String value = cell.getStringCellValue();
if(value!=null&&!value.equals('')){
//Store the data of each cell to the collection Middle
list.add(value);
}
}
}
//Encapsulate each piece of data obtained into a Product type
if(list.size()>0){
Good good = new Good(Integer.parseInt( list.get(0)),list.get(1),Integer.parseInt(list.get(2)),Double. parseDouble(list.get(3)), list.get(4));
//Encapsulate the product into the list collection
goods.add(good);
}
}
}
return goods;
}
}
Execution effect:
converts the row-by-row data of excel into objects.
0x02_POI introduction
Poi is a free open source cross-platform Java API written in Java provided by Apache, which provides the function of allowing Java programs to read and write Microsoft Office files. It is also a commonly used implementation solution for Excel reading and writing.
Prerequisites, you need to import the corresponding dependencies, in pom.xml
<!--poi related dependencies-->
<< span >dependency>
<groupId>org.apache.poi</groupId >
<artifactId>poi</artifactId>< /span>
<version>5.2.3</version>
</dependency>
<dependency>
< ;groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>< /span>
<dependency>
<groupId>org.projectlombok </groupId>
<artifactId>lombok</< span >artifactId>
<version>1.18.24</version span>>
<scope>provided</scope>
</dependency>
current maven repository of the latest.
Poi package structure:
HSSF - read and write Microsoft Excel xls (Excel before version 07)
XSSF - read and write Microsoft Excel OOXML XLSX (Excel after version 07)
HWPF - read and write Word
HSLF - read and write PowerPoint (PPT)
In fact, the main use is XSSF
0x03_Poi How to read the operation table?
In fact, some objects are encapsulated inside Poi, and it is easy to understand the following concepts after using excel:
XSSFWorkbook: workbook
XSSFSheet: Worksheet
Row: Row
Cell: cell
0x04_Encapsulated into a tool class
The above methods are applicable to any entity class It should be able to read and encapsulate, so consider encapsulating it into a tool class, so that it can be used everywhere.
package com.bones.util;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache. poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf. usermodel.XSSFWorkbook;
import org.springframework.cglib.beans.BeanMap;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
< span >import java.util.Map;
/**
* 2 corresponding dependencies: poi 5.2.3 poi-ooxml: 5.2.3
* /
public class ExcelUtil {
/ **
* Parsing table method
* @param stream file input stream
* @param clazz entity class type
* @return Result of parsing the table
* @throws Exception
*/
public static <T> List<T> readExcel(FileInputStream stream, Class<T> clazz) throws Exception {
List<T> result = new ArrayList<>();
// 1. Get the workbook from the input stream
XSSFWorkbook workbook = new XSSFWorkbook(stream);
// 2. Get the target worksheet in the workbook
Sheet sheet = workbook.getSheetAt( 0);
// 3. Get the number of rows in the worksheet (get the first row of data first, because the first row of data in the template contains corresponding fields)
int rowNum = sheet.getPhysicalNumberOfRows();
Row row = sheet.getRow(0);
// 4. Store all entities A collection of attributes corresponding to the class (for mapping)
List<String> key = new ArrayList<>();
// 5. Traverse the A row of data, traverse all the attributes to add data, and put them into the key collection
for (Cell cell : row) {
cell.setCellType( CellType.STRING);
key.add(cell.getStringCellValue());
}
//6. Traversing all formal data, but note that the title of the second line is not obtained, so Get from subscript 2
for(int i = 2;i<rowNum;i++){
// 7. Get all rows
row = sheet.getRow(i);
if(row!=null ){
//8. Map used to save each piece of data, and establish a mapping relationship between attributes and data in the Map
Map<String,String> excelMap = new HashMap<>();
// counter is used to map data use
int j = 0;
// 9. Traverse the data in all cells, and put the key and value (cell data) into excelMap for mapping
for (Cell cell : row) {
if(cell!=null){
//10. Put the cell All data format is set to String
cell.setCellType(CellType.STRING);
//11. Get all cell data
String value = cell. getStringCellValue();
if(value!=null&&!value.equals('')){
//12. Store the data of each cell into a collection
excelMap.put(key.get(j),value);
j++;
}
}
}
// 12. Create the corresponding entity class type
T t = clazz.newInstance();
/**
* Provided by Spring BeanMap, which maps the data in the Map to the entity class through reflection
*/
BeanMap beanMap = BeanMap.create(t);
beanMap.putAll(excelMap);
br> result.add(t);
}
}
return result;
}
}
Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/How%20to%20convert%20Excel%20data%20into%20java%20objects%20Use%20of%20POIs.html
支付宝扫一扫 
评论列表（196条）
测试