POI import and export in SpringBoot

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 -->
<!-- excel2007+Package used -->

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 {

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
	//Create format
	CellStyle cellStyle = wb.createCellStyle();
	//Give cellStyle to cell
	//7 save (Java se project adopts save)
	FileOutputStream stream = new FileOutputStream(new File("d://a.xls"));
	wb.write(stream);//Write objects into flow
	//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
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
              // When an empty line is encountered, skip
              if(row.getCell(0)==null || StringUtils.isBlank(row.getCell(1).getStringCellValue())){
              // print contents

For example, provincial and municipal tables

public ResponseEntity<Void> uploadAndBatchImport(@RequestParam("file") MultipartFile file,
                                                 HttpServletRequest request) {
    try {
        File myFile = new File("d:\\"+UUID.randomUUID()+file.getOriginalFilename());
        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
            // Skip the line with null value and require it to be voided
            if (row.getCell(0) == null
                    || StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
            Area area = new Area();
            area.setId(row.getCell(0).getStringCellValue());//Area code
            area.setPostcode(row.getCell(4).getStringCellValue());//Zip code
        // Call business layer

        //Delete files

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

Keywords: Apache Excel Java Database

Added by jonners on Tue, 03 Dec 2019 19:10:21 +0200