SpringBoot how to integrate POI to achieve Excel import and export
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?
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; // 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=pd[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!
4 Comments