How to parse Execl using multipartFile object in Java

Updated: February 11, 2025 09:42:00 Author: Only the longest forget

This article introduces how to use Spring's MultipartFile class to parse Excel files (.xls and .xlsx), including detailed steps for file upload, data verification, input stream acquisition, file parsing, data storage and exception handling.

Java uses multipartFile object to parse Execl

1. Need to use the multipartFile package

package ;

2. Data verification

public String exportVehicleViol(MultipartFile multipartFile) {
        try {
            //Check the files passed by the front end            if (multipartFile == null &amp;&amp; () == 0) {
                return "File upload error, upload again";
            }
            //Get the file name to determine whether the file is Execl            String filename = ();
            if (!((".xls") || (".xlsx"))) {
                return "The file upload format is incorrect, please upload it again";
            }
            List&lt;EhicleViolation&gt; ehicleViolations = null;
            InputStream inputStream = ();
            //Analysis according to file format            if ((".xlsx")) {
                ehicleViolations = readXlsx(inputStream);
            } else {
                ehicleViolations = readXls(inputStream);
            }
            //Save data            saveBatch(ehicleViolations);
        } catch (IOException e) {
            ();
        }
        return ("Imported successfully");
    }

MultipartFile: This class is provided by Spring and is used to handle file uploads. It represents the uploaded file.
Empty file check: Check firstmultipartFileWhethernull, and whether the file size is 0. If so, return an error message.
File Type Verification:usegetOriginalFilename()Get the name of the uploaded file and check if its suffix is.xlsor.xlsx. If the criteria do not meet the criteria, the prompt of wrong format will be returned.
Input stream acquisition: Called()Get the input stream of the file and prepare for subsequent parsing.
File parsing: Call different parsing methods according to the file suffix name:
- in the case of.xlsx, callreadXlsx()method.
- in the case of.xls, callreadXls()method.
Data saving: After the analysis is completed, callsaveBatch()Store parsed data to the database.
Exception handling:captureIOExceptionand print the stack information. Consider adding logging or user-friendly error messages here.

3. Main analysis of business logic

①Analysis xls

//Parse xls    private List&lt;EhicleViolation&gt; readXls(InputStream inputStream) throws IOException {
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);

        //Read the first sheet        HSSFSheet sheetAt = (0);
        List&lt;EhicleViolation&gt; ehicleViolatsion = new ArrayList&lt;&gt;();
        //rowNum = 3 Get the value from the third row        for (int rowNum = 3; rowNum &lt; (); rowNum++) {
            EhicleViolation ehicleViolation = new EhicleViolation();
            HSSFRow row = (rowNum);

            if (row != null) {
                //The getStringCellValue() method is used to get the value. The POI will judge the cell type. If it is not a string type, the above exception will be thrown.                //So first use the setCellType() method to set the type of the cell to STRING                //Then poi will read it according to the string                (0).setCellType();
                (1).setCellType();
                (2).setCellType();
                (3).setCellType();
                (4).setCellType();
                (5).setCellType();
                (6).setCellType();
                (7).setCellType();
                (8).setCellType();
                
                String stringCellValue0 = (0).getStringCellValue();
               
                String stringCellValue1 = (1).getStringCellValue();
                if ((stringCellValue1)) {
                    (stringCellValue1);
                }
                //Get data in the table according to your needs                String stringCellValue2 = (2).getStringCellValue();
                if ((stringCellValue2)) {
                    (stringCellValue2);
                } else {
                    continue;
                }
                
            }
            (EehicleViolation);
        }
        return EehicleViolations;
    }

HSSFWorkbook: used for parsing.xlsExcel file format. By passingInputStreamCreate a workbook object.
Get a worksheet:usegetSheetAt(0)Method to get the first worksheet.
Create a list: Initialize aArrayList<EhicleViolation>Used to store parsed data.
Read row data:useforThe loop starts reading data from the third line (assuming the first two lines are titles or irrelevant information) until the last line:
- Get row object:pass(rowNum)Get the current row.
- Perform non-empty checks: Confirm that the line object is not empty.
- Set cell type: Looping to set the type of each cell to a string to avoid exceptions caused by different data types (for example: trying to read a numeric cell as a string).
Get cell values：
- Read the value of column 2 (index 1) and callsetUserDeptMethod setting department information.
- Read the value of column 3 (index 2) and callsetVehicleNumberMethod Set the vehicle number. If the vehicle number is empty, the current line is skipped and the next line is processed.
Data storage: Will be parsedEhicleViolationObject added toehicleViolationsin the list.
Return data: Finally return a list containing all parsed data.

②Analysis xlsx

//Parse xlsx    private List&lt;VmsVehicleViolation&gt; readXlsx(InputStream inputStream) throws IOException {
        XSSFWorkbook sheets1 = new XSSFWorkbook(inputStream);

        XSSFSheet sheetAt1 = (0);

        List&lt;EhicleViolation&gt; ehicleViolatsion = new ArrayList&lt;&gt;();
        //rowNum = 3 Get the value from the third row        for (int rowNum = 3; rowNum &lt; (); rowNum++) {
            EhicleViolation ehicleViolation = new EhicleViolation();
            HSSFRow row = (rowNum);

            if (row != null) {
                //The getStringCellValue() method is used to get the value. The POI will judge the cell type. If it is not a string type, the above exception will be thrown.                //So first use the setCellType() method to set the type of the cell to STRING                //Then poi will read it according to the string                (0).setCellType();
                (1).setCellType();
                (2).setCellType();
                (3).setCellType();
                (4).setCellType();
                (5).setCellType();
                (6).setCellType();
                (7).setCellType();
                (8).setCellType();
                
                String stringCellValue0 = (0).getStringCellValue();
               
                String stringCellValue1 = (1).getStringCellValue();
                if ((stringCellValue1)) {
                    (stringCellValue1);
                }
                //Get data in the table according to your needs                String stringCellValue2 = (2).getStringCellValue();
                if ((stringCellValue2)) {
                    (stringCellValue2);
                } else {
                    continue;
                }
                
            }
            (EehicleViolation);
        }
        return EehicleViolations;
    }

XSSFWorkbook: used for parsing.xlsxExcel file format. andHSSFWorkbookSimilar, just a new format for processing.
Get a worksheet: Use the samegetSheetAt(0)Get the first worksheet.
List initialization: Create a new oneArrayList<EhicleViolation>Used to store parsed data.
Read row data:andreadXlsThe same logic is used to loop through all rows from the third row to the last row, reading the data.
Set cell type and get value: Set the cell type in the same way and read the desired column.
Data storage: Add the parsed object to the list and return it.

Note: Different parsing objects are provided for different Execl Java

xls uses HSSFWorkbook object for parsing
xlsx uses XSSWorkbook object for parsing

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.

Java
multipartFile
Execl

Springboot Integration TKMapper and Basic Use Tutorial

It can save most of the time for programmers. For programmers, the operation of a table is nothing more than adding, deleting, modifying and checking. It provides some basic operations, such as querying, deleting and other basic operations based on the table. Let's introduce some springboot integrated tkmapper and basic usage.
2022-11-11
SpringBoot Replace if Parameter verification sample code

Spring Validation is a secondary package of hibernate validation, used to support automatic verification of spring mvc parameters. Next, we will use the spring-boot project as an example to introduce the use of Spring Validation. Friends who need it can refer to it.
2022-12-12
Springboot integrated MongoDB authentication without authentication and enable authentication configuration

This article mainly introduces the configuration method of Springboot integrated MongoDB without authentication and enable authentication. The example code is introduced in this article in detail, which has certain reference learning value for everyone's learning or work. Friends who need it, please learn with the editor below.
2024-03-03
Example of usage of JAVA POI setting EXCEL cell format

This article mainly introduces relevant information about how to set the EXCEL cell format of JAVA POI. Some operations that require setting the EXCEL cell format may be used in POI. Friends who need it can refer to it.
2023-08-08
Detailed explanation of the steps to Dockerize Spring Boot application

This article mainly introduces relevant materials about the Dockerization of Spring Boot application. The example code is introduced in this article in detail, which has certain reference learning value for everyone's study or work. Friends who need it, please learn with the editor below.
2018-04-04
A brief introduction to the beginning of Java synchronization

The CountDownLatch, Semaphore, and CyclicBarrier in Java do not belong to locks. They have many things in common with locks. They are all for collaborative multi-threaded execution. They are all synchronizers, so here we borrow synchronization to name it, which is the source of the "synchronization series". Let me briefly introduce it below
2019-05-05
5 ways to notify in Spring @Aspect

This article mainly introduces the 5 ways to inform the Spring @Aspect. The example code is introduced in detail in this article, which has a certain reference learning value for everyone's study or work. Friends who need it, please learn with the editor below.
2022-05-05
Detailed explanation of using Jenkins to deploy Spring Boot projects

This article mainly introduces a detailed explanation of using Jenkins to deploy Spring Boot. The editor thinks it is quite good. I will share it with you now and give you a reference. Let's take a look with the editor
2017-11-11
Three solutions to maven to resolve dependency conflict

Dependency conflict refers to a certain jar package that the project depends on, with multiple different versions, which causes package version conflict. This article mainly introduces three solutions to Maven to resolve dependency conflicts, which have certain reference value. If you are interested, you can learn about it.
2024-03-03
Java modify PowerPoint slide annotation information

This article mainly introduces Java to modify PowerPoint slide annotation information. The example code is introduced in this article in detail, which has certain reference value for everyone's study or work. Friends who need it can refer to it.
2020-05-05