SpringBoot integration file - Excel import and export of integrated 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. This article mainly introduces the import and export functions of Excel through the SpringBoot integrated POI tool. @pdai

• SpringBoot integration file - Excel import and export of integrated POI

  • Knowledge preparation

    • What is POI
    • Basic Concepts in POI

  • Implementation case

    • Pom dependencies
    • Export to Excel
    • Import into Excel
  • Sample source code
  • Reference article
  • more content

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 "Simple Version of 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. For more information, please refer to the official documentation .

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?

XLS	XLSX
Only the xls format can be opened, and the xlsx format cannot be opened directly	Can directly open xls, xlsx format
Only 65536 rows and 256 columns	Can have 1048576 rows and 16384 columns
Takes up a lot of space	Small footprint, faster operation speed

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

Excel	POI XLS	POI XLSX (Excel 2007+)
Excel file	HSSFWorkbook (xls)	XSSFWorkbook (xlsx)
Excel worksheet	HSSFSheet	XSSFSheet
Excel row	HSSFRow	XSSFRow
Excel cell	HSSFCell	XSSFCell
Excel cell styles	HSSFCellStyle	HSSFCellStyle
Excel Colors	HSSFColor	XSSFColor
Excel fonts	HSSFFont	XSSFFont

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;

    // 表头
    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]);
    }

    // 表内容
    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("[e-mail]").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); // 表头不算
        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] t.p.s.f.e.p.s.impl.UserServiceImpl       : User(id=1, userName=pdai, email=[e-mail], phoneNumber=121231231231, description=hello world)

Sample source code

https://github.com/realpdai/tech-pdai-spring-demos

Reference article

https://poi.apache.org/index.html

more content

Say goodbye to fragmented learning, one-stop systematic learning without routines Back-end development: Java full stack knowledge system (https://pdai.tech)