This article mainly explains "How SpringBoot integrates POI to implement Excel import and export", interested friends may wish to take a look. The method introduced in this paper is simple, fast, and practical. Let the editor take you to learn "How SpringBoot integrates POI to realize Excel import and export"!

Knowledge preparation

You need to understand POI tools and the corresponding relationship between POI's encapsulation of objects in Excel.

What is POI

Apache POI is a free and open source cross-platform Java API written in Java. Apache POI provides APIs for Java programs to read and write Microsoft Office format files. POI is an acronym for "Poor Obfuscation Implementation", which means "Concise Obfuscation Implementation".

Apache POI is a Java API for creating and maintaining operations that conform to the Office Open XML (OOXML) standard and Microsoft's OLE 2 composite document format (OLE2). With it, you can use Java to read and create, modify MS Excel files. Moreover, you can also use Java to read and create MS Word and MSPowerPoint files.

Basic Concepts in POI

What is the difference between generate xls and xlsx? The encapsulation correspondence of POI to objects in Excel?

What is the difference between generating xls and xlsx?

The corresponding relationship between POI's encapsulation of objects in Excel is as follows:

Implementation case

Here is an example of SpringBoot integrating POI to export user lists and import user lists.

Pom dependencies

Import the dependency package of poi

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

Export to Excel

Methods exported in UserController

@ApiOperation("Download Excel")
  @GetMapping ( "/excel/download" )
  public void download (HttpServletResponse response) {
      try {
         SXSSFWorkbook workbook = userService.generateExcelWorkbook();
         response.reset();
         response.setContentType( "application/vnd.ms-excel" );
         response.setHeader( "Content-disposition" ,
                  "attachment;filename=user_excel_" + System.currentTimeMillis() + ".xlsx" );
         OutputStream os = response.getOutputStream();
         workbook.write(os);
         workbook.dispose();
     } catch (Exception e) {
         e.printStackTrace();
     }
}

The main method of exporting Excel in UserServiceImple

private static final int POSITION_ROW = 1 ;
 private static final int POSITION_COL = 1 ;

/**
  * @return SXSSFWorkbook
  */
@Override
public SXSSFWorkbook generateExcelWorkbook () {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();

    int rows = POSITION_ROW;
     int cols = POSITION_COL;

    // header
    Row head = sheet.createRow(rows++);
    String[] columns = new String[]{ "ID" , "Name" , "Email" , "Phone" , "Description" };
     int [] colWidths = new int []{ 2000 , 3000 , 5000 , 5000 , 8000 };
    CellStyle headStyle = getHeadCellStyle(workbook);
    for ( int i = 0 ; i < columns.length; ++i) {
        sheet.setColumnWidth(cols, colWidths[i]);
        addCellWithStyle(head, cols++, headStyle).setCellValue(columns[i]);
    }

    // table content
    CellStyle bodyStyle = getBodyCellStyle(workbook);
    for (User user : getUserList()) {
        cols = POSITION_COL;
        Row row = sheet.createRow(rows++);
        addCellWithStyle(row, cols++, bodyStyle).setCellValue(user.getId());
        addCellWithStyle(row, cols++, bodyStyle).setCellValue(user.getUserName());
        addCellWithStyle(row, cols++, bodyStyle).setCellValue(user.getEmail());
        addCellWithStyle(row, cols++, bodyStyle).setCellValue(String.valueOf(user.getPhoneNumber()));
        addCellWithStyle(row, cols++, bodyStyle).setCellValue(user.getDescription());
    }
    return workbook;
}

private Cell addCellWithStyle (Row row, int colPosition, CellStyle cellStyle) {
    Cell cell = row.createCell(colPosition);
    cell.setCellStyle(cellStyle);
    return cell;
}

private List<User> getUserList () {
     return Collections.singletonList(User.builder()
            .id( 1L ).userName( "pdai" ).email( "[email protected]" ).phoneNumber( 121231231231L )
            .description( "hello world" )
            .build());
}

private CellStyle getHeadCellStyle (Workbook workbook) {
    CellStyle style = getBaseCellStyle(workbook);

    // fill
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    return style;
}

private CellStyle getBodyCellStyle (Workbook workbook) {
     return getBaseCellStyle(workbook);
}

private CellStyle getBaseCellStyle (Workbook workbook) {
    CellStyle style = workbook.createCellStyle();

    // font
    Font font = workbook.createFont();
    font.setBold( true );
    style.setFont(font);

    // align
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);

    // border
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(BorderStyle.THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    return style;
}

The exported excel is as follows

Import into Excel

We import the excel file exported above.

Methods imported in UserController

@ApiOperation("Upload Excel")
  @PostMapping ( "/excel/upload" )
public ResponseResult<String> upload( @RequestParam (value = "file" , required = true) MultipartFile file) {
      try {
          userService.upload(file.getInputStream());
     } catch (Exception e) {
          e.printStackTrace();
          return ResponseResult.fail(e.getMessage());
     }
     return ResponseResult.success( );
}

The main method of importing Excel in UserServiceImple

@Override
public void upload (InputStream inputStream) throws IOException {
    XSSFWorkbook book = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = book.getSheetAt( 0 );
     // add some validation here

    // parse data
    int cols;
     for ( int i = POSITION_ROW; i < sheet.getLastRowNum(); i++) {
        XSSFRow row = sheet.getRow(i + 1 ); // the header does not count
        cols = POSITION_COL;
        User user = User.builder()
                .id(getCellLongValue(row.getCell(cols++)))
                .userName(getCellStringValue(row.getCell(cols++)))
                .email(getCellStringValue(row.getCell(cols++)))
                .phoneNumber(Long.parseLong(getCellStringValue(row.getCell(cols++))))
                .description(getCellStringValue(row.getCell(cols++)))
                .build();
        log.info(user.toString());
    }

    book.close();
}

private String getCellStringValue (XSSFCell cell) {
     try {
         if ( null !=cell) {
             return String.valueOf(cell.getStringCellValue());
        }
    } catch (Exception e) {
         return String.valueOf(getCellIntValue(cell));
    }
    return "" ;
}

private long getCellLongValue (XSSFCell cell) {
     try {
         if ( null !=cell) {
             return Long.parseLong( "" + ( long ) cell.getNumericCellValue());
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return 0L ;
}

private int getCellIntValue (XSSFCell cell) {
     try {
         if ( null !=cell) {
             return Integer.parseInt( "" + ( int ) cell.getNumericCellValue());
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return 0 ;
}

Interface testing with PostMan

After executing the interface, the background log is as follows

2022-06-10 21:36:01.720 INFO 15100 --- [nio-8080-exec-2] tpsfepsimpl.UserServiceImpl : User(id=1, userName=pdai, [email protected], phoneNumber=121231231231, description=hello world)

At this point, I believe that everyone has a deeper understanding of "How SpringBoot integrates POI to implement Excel import and export", so let's do it in practice!