• notice
  • Congratulations on the launch of the Sought Tech site

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?

    XLSXLSX
    Only the xls format can be opened, and the xlsx format cannot be opened directlyCan directly open xls, xlsx format
    Only 65536 rows and 256 columnsCan have 1048576 rows and 16384 columns
    Takes up a lot of spaceSmall footprint, faster operation speed

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

    ExcelPOI XLSPOI XLSX (Excel 2007+)
    Excel fileHSSFWorkbook (xls)XSSFWorkbook (xlsx)
    Excel worksheetHSSFSheetXSSFSheet
    Excel rowHSSFRowXSSFRow
    Excel cellHSSFCellXSSFCell
    Excel cell stylesHSSFCellStyleHSSFCellStyle
    Excel ColorsHSSFColorXSSFColor
    Excel fontsHSSFFontXSSFFont

    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

    How SpringBoot integrates POI to implement Excel import and export

    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

    How SpringBoot integrates POI to implement Excel import and export

    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!


    Tags

    Technical otaku

    Sought technology together

    Related Topic

    4 Comments

    Leave a Reply

    +