The Java POI implementation will import the sample code of the Excel file

Problem description

Now you need to import data in batches, and the data is imported in the form of Excel.

Introduction to POI

I chose to use apache POI. This is the Apache Software Foundation open function library, he will provide API to java, so that it can read and write office files.

I only need to use the Excel part here.

Realization

First of all, Excel has two formats, one is. xls (version 03) and the other is. xlsx (version 07). For two different table formats, POI provides two interfaces. HSSFWorkbook and XSSFWorkbook

Import dependencies

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

Processing version

Workbook workbook = null;
try {
  if (file.getPath().endsWith("xls")) {
    System.out.println(" This is 2003 Version ");
    workbook = new XSSFWorkbook(new FileInputStream(file));
  } else if (file.getPath().endsWith("xlsx")){
    workbook = new HSSFWorkbook(new FileInputStream(file));
    System.out.println(" This is 2007 Version ");
  }
      
} catch (IOException e) {
  e.printStackTrace();
}

Here we need to judge the version of Excel under 1, and use different classes to deal with files according to the extension.

Get tabular data

Getting the data in the table is divided into the following steps:

Step 1 Get the form
2. Get a 1 line
3. Get a cell in this 1 row

Code implementation:

//  Get the 1 A sheet 
Sheet sheet = workbook.getSheetAt(0);
   
//  Get the fields in each row 
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
  Row row = sheet.getRow(i);  //  Get Row 

  //  Get the value in the cell 
  String studentNum = row.getCell(0).getStringCellValue();  
  String name = row.getCell(1).getStringCellValue();
  String phone = row.getCell(2).getStringCellValue();
}

Persistence

After getting the data in the cell, the object is finally established with the data.

List<Student> studentList = new ArrayList<>();

for (int i = 0; i <= sheet.getLastRowNum(); i++) {
  Row row = sheet.getRow(i);  //  Get Row 

  //  Get the value in the cell 
  String studentNum = row.getCell(0).getStringCellValue();  
  String name = row.getCell(1).getStringCellValue();
  String phone = row.getCell(2).getStringCellValue();
  
  Student student = new Student();
  student.setStudentNumber(studentNum);
  student.setName(name);
  student.setPhoneNumber(phone);
  
  studentList.add(student);
}

//  Persistence 
studentRepository.saveAll(studentList);