Table of Contents for SpringBoot Tutorial Series Articles

1. SpringBoot Picture Tutorial 1 "Concepts + Case Mind Maps" "Foundation Text"
2. SpringBoot Tutorial 2 - Use of Logbacks and log4j for Logs
3. SpringBoot Tutorial 3 "First Love Complex" Integrated Jsp
4. SpringBoot Tutorial 4 - File upload and download with SpringBoot
5. SpringBoot Tutorial 5 - Using Aop in SpringBoot
6. Use of filters in SpringBoot tutorial 6-SpringBoot
7. SpringBoot Graphics Tutorial 7 - Position of the SpringBoot interceptor
8. SpringBoot Tutorial 8 - SpringBoot Integrated MBG Code Generator

Preface

The import and export function of Excel file is a very common function in the project. There are many business scenarios using this technology, such as import and export of customer information, import and export of operation data, import and export of order data, etc.

So how do you use Poi in SpringBoot?From the beginning of this article, we will talk about three positions using Poi: Apache Poi, EasyPoi, and Ali's open source EasyExcel.

Say nothing and start right away.

Integrated Poi

What is Poi

Apache Poi is an open source project for Apache that uses Java code to read and generate Excel documents using poi technology.

Poi's abstracted object from Excel

I've just said a few things about Excel files, but that's what you need to use in your computer. If you want to import Excel files through SpringBoot, you have to do it through classes and methods.

So all the above mentioned nouns are encapsulated in Poi.The corresponding relationships are as follows:

Basic use of Poi

1. Import dependencies into the project

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

2. Write the exported code in the test class as follows:

    /**
     * Export Data
     */
    @Test
    public void test2() throws IOException {
//        0. Preparing data
        User user = new User();
        user.setUserId(1);
        user.setUserName("hhhh");
        user.setUserSex("male");

        String[] titles = {"number","Name","Gender"};

        /**
         * Write title bar data first
         */
//        1. Create File Object Create HSSFWorkbook can only write Excel in xls format
//        Writing xlsx requires creating an XSSFWorkbook in the same way as the two basic Api uses
        HSSFWorkbook workbook = new HSSFWorkbook();

//        2. Create table objects
        HSSFSheet sheet = workbook.createSheet("users");

//        3. Create title bar (first line) parameter as line subscript line subscript from 0
        HSSFRow titleRow = sheet.createRow(0);

//        4. Write data in the title bar
        for (int i = 0; i < titles.length; i++) {
//            Creating Cells
            HSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
        }
        /**
         * Writing user data
         */
//       5 Create rows that need to be traversed if they are a collection of user data
         HSSFRow row = sheet.createRow(1);

//       6 Write user data to rows
        row.createCell(0).setCellValue(user.getUserId());
        row.createCell(1).setCellValue(user.getUserName());
        row.createCell(2).setCellValue(user.getUserSex());

//        Save the file to a location where the local parameter is to be written out
        workbook.write(new FileOutputStream("/Users/k/Desktop/hhh.xls"));

    }

The resulting Excel file is as follows

3. Excel Import

  /**
     * File import is actually a reverse operation of file export
     */
    @Test
    public void test3() throws Exception {
//        1. Read Excel files by streaming
        FileInputStream inputStream = new FileInputStream("/Users/k/Desktop/hhh.xls");
//        2. All the data of the Excel file is encapsulated in the object from the poi parsing stream HSSFWorkbook processing stream
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//        3. Get table object getSheetAt s from file by subscript
        HSSFSheet sheet = workbook.getSheetAt(0);
//        4. Get row data from table Get subscript of last row from second row to last row getLastRowNum()
        int lastRowNum = sheet.getLastRowNum();

        for (int i = 1; i <= lastRowNum; i++) {
//            Get rows from Subscripts
            HSSFRow row = sheet.getRow(i);
//            Get data from rows

            /**
             * getNumericCellValue() Get Numbers
             * getStringCellValue Get String
             */
            double id = row.getCell(0).getNumericCellValue();
            String name = row.getCell(1).getStringCellValue();
            String sex = row.getCell(2).getStringCellValue();

//            Encapsulate in Object
            User user = new User();
            user.setUserId((int) id);
            user.setUserName(name);
            user.setUserSex(sex);

//            Add Object to Database
            System.out.println(user);
        }
    }

summary

Congratulations on completing this chapter and applauding you!If this article is helpful to you, please comment on it and forward it. It is very important for the author. Thank you.

Let's review the objectives of this article again

To learn more about SpringBoot usage, keep an eye on this series of tutorials.

Ask for attention, favor and forwarding