SpringBoot integrates Apache POI to implement the import and export of Excel

Preface

It is a common practice to implement the import and export functions of Excel using Apache POI in Spring Boot. Apache POI is a popular Java library for processing Microsoft Office format files, including Excel files. Combining Apache POI in Spring Boot can easily implement the read and write operations of Excel files. Below I will introduce in detail how to use Apache POI in Spring Boot to implement the import and export of Excel.

1. What is Apache POI?

Apache POI (Poor Obfuscation Implementation) is a popular Java library for processing Microsoft Office format files, including Word documents, Excel tables, and PowerPoint presentations. It provides a set of classes and methods that enable developers to read, create, and modify these Office format files.
Apache POI provides an abstract representation of Office format files, allowing developers to manipulate the content, formats, and styles of these files in a program. Apache POI allows developers to implement operations such as importing data from Excel, inserting tables into Word documents, and extracting text from PowerPoint.
Apache POI is maintained and released by the Apache Software Foundation and is an open source project. It provides Java developers with powerful tools for handling Office format files, making it easier and more flexible to integrate Office file operations in Java applications.

2. Use Apache POI to implement the import and export of Excel

① Import Excel

1. Add dependencies

First, add dependencies for Apache POI in the configuration file in a Maven or Gradle project.

Maven dependencies:

<dependency>
    <groupId></groupId>
    <artifactId>poi</artifactId>
    <version>{latest_version}</version>
</dependency>
<dependency>
    <groupId></groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>{latest_version}</version>
</dependency>

Gradle dependencies:

implementation ':poi:{latest_version}'
implementation ':poi-ooxml:{latest_version}'

2. Write import logic

Write a method that receives the uploaded Excel file and parses the data in it. Here we take importing user information as an example:

import .*;

import ;
import ;
import ;
import ;

@Service
public class ExcelImportService {

    public List&lt;User&gt; importUsers(InputStream inputStream) throws Exception {
        List&lt;User&gt; userList = new ArrayList&lt;&gt;();

        Workbook workbook = (inputStream);
        Sheet sheet = (0); // Assume that the user information is in the first sheet
        Iterator&lt;Row&gt; rowIterator = ();
        while (()) {
            Row row = ();
            if (() == 0) { // Skip the header                continue;
            }

            User user = new User();
            ((0).getStringCellValue());
            ((1).getStringCellValue());
            // parse more fields...
            (user);
        }

        ();
        return userList;
    }
}

3. Process upload request in Controller

import ;

@RestController
@RequestMapping("/import")
public class ExcelImportController {

    @Autowired
    private ExcelImportService excelImportService;

    @PostMapping("/users")
    public ResponseEntity&lt;String&gt; importUsers(@RequestParam("file") MultipartFile file) {
        try {
            List&lt;User&gt; userList = (());
            // Process imported user data, such as saving to database, etc.            return ("Imported successfully");
        } catch (Exception e) {
            ();
            return (HttpStatus.INTERNAL_SERVER_ERROR).body("Import failed");
        }
    }
}

② Export Excel

1. Add dependencies

The Apache POI dependency has been added before, and there is no need to be added repeatedly here.

2. Write export logic

Write a method that writes data into an Excel file and provides a download link. Here we also take the export of user information as an example:

import .*;

import ;
import ;

@Service
public class ExcelExportService {

    public void exportUsers(List&lt;User&gt; userList, HttpServletResponse response) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = ("User Information");

        // Create a header        Row headerRow = (0);
        (0).setCellValue("ID");
        (1).setCellValue("Name");
        // Add more fields...
        // Write data        int rowNum = 1;
        for (User user : userList) {
            Row row = (rowNum++);
            (0).setCellValue(());
            (1).setCellValue(());
            // Add more fields...        }

        // Set response header        ("application/");
        ("Content-disposition", "attachment; filename=");

        // Output to response stream        (());
        ();
    }
}

3. Process the export request in Controller

@RestController
@RequestMapping("/export")
public class ExcelExportController {

    @Autowired
    private ExcelExportService excelExportService;

    @GetMapping("/users")
    public void exportUsers(HttpServletResponse response) {
        try {
            List&lt;User&gt; userList = (); // Assuming a way to get all user information            (userList, response);
        } catch (Exception e) {
            ();
            // Handle exceptions        }
    }
}

Summarize

This article briefly describes the methods and steps for using Apache POI to implement Excel import and export in Spring Boot. Through Apache POI, we can easily process Excel files and complete data import and export operations. For more related SpringBoot POI Excel import and export content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!