• 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

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;
 }
}