SoFunction
Updated on 2025-04-23

SpringMVC framework uses Apache POI to export Excel

In enterprise-level application development, import and export of data is a common requirement. Excel is often used to store and display data as one of the most commonly used data processing tools. This article will explain how to use the Apache POI library in the SpringMVC framework to implement the export function of Excel files.

1.Introduction

1.1 Add dependencies

First, add the dependencies of Apache POI in the project's ​​​​ file:

<dependencies>
    <!-- Other dependencies -->
    <dependency>
        <groupId></groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

1.2 Create a Maven project

Make sure your project is a Maven project and has the SpringMVC framework configured.

2. Create a controller

Next, we create a controller to handle the request to export Excel.

package ;
 
import .*;
import ;
import ;
import ;
import ;
 
import ;
import ;
import ;
import ;
import ;
 
@Controller
@RequestMapping("/excel")
public class ExcelController {
 
    @GetMapping("/export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        // Set response header        ("application/");
        ("utf-8");
        String fileName = ("User Information", "UTF-8").replaceAll("\\+", "%20");
        ("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
 
        // Create a workbook        Workbook workbook = new XSSFWorkbook();
        // Create worksheets        Sheet sheet = ("User Information");
        // Create a title line        Row headerRow = (0);
        List<String> headers = ("ID", "Name", "age", "Mail");
        for (int i = 0; i < (); i++) {
            Cell cell = (i);
            ((i));
        }
 
        // Fill in data        List<User> users = getUsers();
        int rowNum = 1;
        for (User user : users) {
            Row row = (rowNum++);
            (0).setCellValue(());
            (1).setCellValue(());
            (2).setCellValue(());
            (3).setCellValue(());
        }
 
        // Output Excel file        (());
        ();
    }
 
    private List<User> getUsers() {
        return (
                new User(1, "Zhang San", 28, "zhangsan@"),
                new User(2, "Li Si", 30, "lisi@"),
                new User(3, "Wang Wu", 25, "wangwu@")
        );
    }
}
 
class User {
    private int id;
    private String name;
    private int age;
    private String email;
 
    public User(int id, String name, int age, String email) {
         = id;
         = name;
         = age;
         = email;
    }
 
    public int getId() {
        return id;
    }
 
    public String getName() {
        return name;
    }
 
    public int getAge() {
        return age;
    }
 
    public String getEmail() {
        return email;
    }
}

3. Configure SpringMVC

Make sure that the DispatcherServlet and related components are already configured in your ​​​​​​ or SpringMVC configuration file.

<servlet>
    <servlet-name>dispatcher</servlet-name>
    <servlet-class></servlet-class>
    <init-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/spring/appServlet/</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
</servlet>
 
<servlet-mapping>
    <servlet-name>dispatcher</servlet-name>
    <url-pattern>/</url-pattern>
</servlet-mapping>

4. Test the export function

Start your application server and access the following URL to test the export function:

http://localhost:8080/your-app/excel/export

If everything works, you should see a browser prompting you to download an Excel file called "User Information.xlsx".

5. Method supplement

Here is a sample code for exporting Excel files using Spring MVC and Apache POI. This example assumes that you already have a Spring Boot project and need to get the data from the database and export it as an Excel file.

1. Add dependencies

First, add the dependencies of Apache POI in your `` file:

<dependency>
    <groupId></groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

2. Create entity class

Suppose you have a User entity class that represents user information:

public class User {
    private Long id;
    private String name;
    private String email;
    private int age;
 
    // Getters and Setters
}

3. Create a service class

Create a service class to handle data acquisition and Excel file generation:

import .*;
import ;
import ;
 
import ;
import ;
import ;
import ;
 
@Service
public class UserService {
 
    // Suppose there is a way to get the user list from the database    public List&lt;User&gt; getUsers() {
        // Here you can call DAO or Repository to get data        return (
            new User(1L, "Zhang San", "zhangsan@", 28),
            new User(2L, "Li Si", "lisi@", 30)
        );
    }
 
    public ByteArrayInputStream exportUsersToExcel() throws IOException {
        List&lt;User&gt; users = getUsers();
 
        try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = ("Users");
 
            // Create a header            Row headerRow = (0);
            (0).setCellValue("ID");
            (1).setCellValue("Name");
            (2).setCellValue("Email");
            (3).setCellValue("Age");
 
            // Fill in data            int rowNum = 1;
            for (User user : users) {
                Row row = (rowNum++);
                (0).setCellValue(());
                (1).setCellValue(());
                (2).setCellValue(());
                (3).setCellValue(());
            }
 
            (out);
            return new ByteArrayInputStream(());
        }
    }
}

4. Create a controller

Create a controller to process HTTP requests and return an Excel file:

import ;
import ;
import ;
import ;
import ;
import ;
import ;
 
import ;
import ;
 
@RestController
@RequestMapping("/api/users")
public class UserController {
 
    @Autowired
    private UserService userService;
 
    @GetMapping("/export-excel")
    public ResponseEntity&lt;ByteArrayResource&gt; exportUsersToExcel() throws IOException {
        ByteArrayInputStream bis = ();
 
        // Set response header        HttpHeaders headers = new HttpHeaders();
        ("Content-Disposition", "attachment; filename=");
 
        return ResponseEntity
                .ok()
                .headers(headers)
                .contentLength(())
                .body(new ByteArrayResource(()));
    }
}

5. Configure the application

Make sure there are no conflicting configurations in your ​​​​​ or ​​​​ files.

6. Test

Launch your Spring Boot app and visit http://localhost:8080/api/users/export-excel and you should download an Excel file named ​​​​ with user data.

The above code shows how to export Excel files using Apache POI in Spring MVC. You can adjust the data source and service logic according to actual needs. When using the Spring MVC framework combined with the Apache POI library to implement the export function of Excel files, the following steps are usually required:

Add dependencies: Make sure that Spring MVC and Apache POI related dependencies have been introduced into the project.

Create an Excel processing class: Write a class to process the generation logic of Excel files.

Controller layer: Call the methods of the above class in the controller of Spring MVC and set the response header to trigger the file download.

1. Add dependencies

First, add the dependencies of Apache POI in your file:

<dependency>
    <groupId></groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

2. Create Excel processing class

Next, create a class to handle the generation of Excel files. For example, create a class called ExcelExporter:

import .*;
import ;
 
import ;
import ;
import ;
import ;
 
public class ExcelExporter {
 
    public void export(List&lt;Map&lt;String, Object&gt;&gt; data, HttpServletResponse response) throws IOException {
        // Create a new workbook        Workbook workbook = new XSSFWorkbook();
        // Create a worksheet        Sheet sheet = ("Data");
 
        // Create a title line        Row headerRow = (0);
        int columnCount = 0;
        for (String key : (0).keySet()) {
            Cell cell = (columnCount++);
            (key);
        }
 
        // Fill in data        int rowCount = 1;
        for (Map&lt;String, Object&gt; record : data) {
            Row row = (rowCount++);
            columnCount = 0;
            for (String key : ()) {
                Cell cell = (columnCount++);
                Object value = (key);
                if (value instanceof String) {
                    ((String) value);
                } else if (value instanceof Integer) {
                    ((Integer) value);
                } else if (value instanceof Double) {
                    ((Double) value);
                }
                // More types can be added as needed            }
        }
 
        // Set response header        ("application/");
        ("Content-Disposition", "attachment; filename=");
 
        // Write response output stream        (());
        ();
    }
}

3. Controller layer

In the controller of Spring MVC, call the export method of ExcelExporter and set the response header to trigger the file download:

import ;
import ;
 
import ;
import ;
import ;
import ;
import ;
import ;
 
@Controller
public class ExcelController {
 
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        List&lt;Map&lt;String, Object&gt;&gt; data = new ArrayList&lt;&gt;();
        // Sample data        Map&lt;String, Object&gt; record1 = new HashMap&lt;&gt;();
        ("Name", "John Doe");
        ("Age", 30);
        ("Email", "@");
 
        Map&lt;String, Object&gt; record2 = new HashMap&lt;&gt;();
        ("Name", "Jane Smith");
        ("Age", 25);
        ("Email", "@");
 
        (record1);
        (record2);
 
        ExcelExporter exporter = new ExcelExporter();
        (data, response);
    }
}

Summarize

Through the above steps, you can implement the export function of Excel files in Spring MVC applications. This process includes adding necessary dependencies, creating classes that process Excel files, calling methods of the class in the controller and setting response headers to trigger file downloads.

The above is the detailed content of the SpringMVC framework using Apache POI to export Excel. For more information about SpringMVC POI exporting Excel, please pay attention to my other related articles!