2.1 POI overview
Official website: http://poi.apache.org/
Can analyze word, ppt, excel
Application of POI
1. Export data: backup the data in the database
2. Import data: batch import data from excel into database
POI support based on maven coordinate import

<!-- excel2003 Package used -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.11</version>
</dependency>
<!-- excel2007+Package used -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.11</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.11</version>
</dependency>

2.2 export test
Export steps:
1. Create workbook
2. Create worksheet sheet
3. Create row object (subscript start value is 0)
4. Create cell object cell (subscript start value is 0)
5. Set content for cells
6. Set cell style, font and font size
7. Save and close the flow object
8. Download (only web projects can be downloaded)
Test code:

package cn.itcast.jx.poi;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class POITest {

@SuppressWarnings("resource")
public static void main(String[] args) throws Exception {
	//1 create Workbook: Workbook is an interface. It has an implementation class HSSFWorkbook object. This object operates excel 97-03. The suffix of Excel is xls
	Workbook wb = new HSSFWorkbook();
	//2 create sheet sheet: sheet
	Sheet sheet = wb.createSheet();
	//3 Create row object, count from 0 in java
	Row row = sheet.createRow(3);
	//4 create column object
	Cell cell = row.createCell(3);
	//5 setting content
	cell.setCellValue("czxy,Unification of rivers and lakes");
	//6 format content
	Font font = wb.createFont();
	font.setFontHeightInPoints((short)24);//Set font size as pixels
	font.setFontName("Hua Wencaiyun");//Set font
	
	//System.out.println(Short.MIN_VALUE+"-"+Short.MAX_VALUE);
	//Create format
	CellStyle cellStyle = wb.createCellStyle();
	cellStyle.setFont(font);
	
	//Give cellStyle to cell
	cell.setCellStyle(cellStyle);
	//7 save (Java se project adopts save)
	FileOutputStream stream = new FileOutputStream(new File("d://a.xls"));
	wb.write(stream);//Write objects into flow
	
	stream.flush();
	stream.close();
	//8 Download (only web project can be downloaded)
	
	System.out.println("End of operation");
}

}

2.3 import test
During the import test, the imported xls template must be strictly in accordance with the format, and no content can be empty, otherwise an exception may be thrown
Requirement:
1. The template of excel has been fixed, and the specific content of each column has been confirmed, without any change
2. Only when the table ends, can the content be empty, otherwise there can be no empty content

• Import steps

  1. Read workbook
  2. Read sheet
  3. Read row object row (subscript start value is 0)
  4. Read cell (subscript start value is 0)
  5. Read cell content
  6. Save read data

• Test code

• 

    public static void main(String[] args) throws  Exception{
        // Stream read file
        FileInputStream is = new FileInputStream(new File("d:\\area.xls"));
        // Create file from stream
        Workbook wb = new HSSFWorkbook(is);
        // Get sheet
        Sheet sheet = wb.getSheetAt(0);
        // Traversing row
        for(Row row:sheet){
            // The first line is the title and does not need to be read
            if(row.getRowNum()==0){
                continue;
            }
            // When an empty line is encountered, skip
            if(row.getCell(0)==null || StringUtils.isBlank(row.getCell(1).getStringCellValue())){
                continue;
            }
            // print contents
            System.out.print(row.getCell(0).getStringCellValue()+":");
            System.out.print(row.getCell(1).getStringCellValue()+":");
            System.out.print(row.getCell(2).getStringCellValue()+":");
            System.out.print(row.getCell(3).getStringCellValue()+":");
            System.out.println(row.getCell(4).getStringCellValue());
        }
    }
  

For example, provincial and municipal tables

	@PostMapping("batchImport")
public ResponseEntity<Void> uploadAndBatchImport(@RequestParam("file") MultipartFile file,
                                                 HttpServletRequest request) {
    try {
        File myFile = new File("d:\\"+UUID.randomUUID()+file.getOriginalFilename());
        file.transferTo(myFile);
        FileInputStream is = new FileInputStream(myFile);
        
        List<Area> areas = new ArrayList<Area>();
        // Write parsing code logic
        // Analysis of HSSF based on. xls format
        // 1. Load Excel file object
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        // 2. Read a sheet
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);//Get the first sheet object
        // 3. Read each line in the sheet, and one line of data corresponds to one area object
        for (Row row : sheet) {
            // First row header skip
            if (row.getRowNum() == 0) {
                // First line skip
                continue;
            }
            // Skip the line with null value and require it to be voided
            if (row.getCell(0) == null
                    || StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
                continue;
            }
            Area area = new Area();
            area.setId(row.getCell(0).getStringCellValue());//Area code
            area.setProvince(row.getCell(1).getStringCellValue());//Province
            area.setCity(row.getCell(2).getStringCellValue());//City
            area.setDistrict(row.getCell(3).getStringCellValue());//region
            area.setPostcode(row.getCell(4).getStringCellValue());//Zip code
            areas.add(area);
        }
        // Call business layer
        areaService.saveAreas(areas);

        //Delete files
        myFile.delete();

        return new ResponseEntity<>(HttpStatus.OK);
    } catch (Exception e) {
        //Server error
        return new ResponseEntity<Void>(HttpStatus.INTERNAL_SERVER_ERROR);
    }
}