最新springboot 导出excel 工具类 (spring boot 2.5 + poi 5.0导出xlsx)

目录

1, 项目结构

2，完整demo

2.1, pom

2.2, controller

2.3, 导出工具类

2.4，vo

3，运行效果:

---

轻松搞定excel导出

1, 项目结构

2，完整demo

2.1, pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.demo</groupId>
    <artifactId>winding-util</artifactId>
    <version>1.0-SNAPSHOT</version>


    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
        <relativePath/>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>

    <dependencies>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

2.2, controller

package com.demo.controller;

import com.demo.util.ExcelUtil;
import com.demo.vo.UserVO;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * @author winding
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {


    /**
     * 导出excel
     *
     * @author winding
     */
    @RequestMapping("/export")
    public void export(HttpServletResponse response) {
        //创建模拟数据
        List<UserVO> users = mockUsers();
        //设置导出excel头
        String userExportTitle = "员工id,员工姓名,创建时间";
        //设置导出内容，注意我们全部转成String； 逗号分割； 和标题一一对应，如果空值也需要设置空值;
        List<String> userExportContents = new ArrayList<>();
        users.forEach(v -> {
            userExportContents.add(v.getId() + "," + v.getName() + "," + v.getCreateTime());
        });

        ExcelUtil.export(response, userExportTitle, userExportContents, "员工列表");
    }

    private List<UserVO> mockUsers() {
        List<UserVO> userVos = new ArrayList<>();
        userVos.add(new UserVO("张三", System.currentTimeMillis(), "2022-05-11 18:00:00"));
        userVos.add(new UserVO("张三张三", System.currentTimeMillis(), "2022-05-11 18:10:00"));
        userVos.add(new UserVO("张三张三张三", System.currentTimeMillis(), "2022-05-11 18:20:00"));
        return userVos;
    }
}

2.3, 导出工具类

package com.demo.util;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author winding
 */
public final class ExcelUtil {


    private ExcelUtil() {
    }


    /**
     * 导出 excel  xlsx
     *
     * @param response
     * @param title    标题如: "员工id,员工姓名,创建时间" (逗号分割)
     * @param values   值列表(逗号分割)
     * @param fileName 文件名字
     * @author winding
     */
    public static void export(HttpServletResponse response, String title, List<String> values, String fileName) {

        // 设置日期格式
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
        // 设置默认文件名
        if (!StringUtils.hasLength(fileName)) {
            fileName = df.format(new Date());
        } else {
            fileName += df.format(new Date());
        }

        XSSFWorkbook workbook = new XSSFWorkbook();
        //在Workbook中，创建一个sheet，对应Excel中的工作薄（sheet）
        XSSFSheet sheet = workbook.createSheet();

        //第一行
        XSSFRow row = sheet.createRow(0);
        //第一行设置标题
        setCell(row, title);

        //设置内容
        for (int i = 0; i < values.size(); i++) {
            //从第二行开始
            XSSFRow contentRow = sheet.createRow(i + 1);
            //填充表格内容
            setCell(contentRow, values.get(i));
        }

        // 设置response头信息
        OutputStream outputStream = null;
        try {
            response.reset();
            // 改成输出excel文件
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
            //将文件输出
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 设备行里面的单元格内容
     *
     * @param row     行
     * @param content 内容， 多个单元格以逗号分割
     */
    private static void setCell(XSSFRow row, String content) {
        // 填充表头
        String[] contents = content.split(",");
        for (int i = 0; i < contents.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(contents[i]);
        }
    }
}

2.4，vo

package com.demo.vo;

public class UserVO {

    private String name;
    private Long id;
    private String createTime;

    public UserVO() {
    }


    public UserVO(String name, Long id, String time) {
        this.name = name;
        this.id = id;
        this.createTime = time;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }
}

3，运行效果: