最新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

    4.0.0

    com.demo
    winding-util
    1.0-SNAPSHOT


    
        org.springframework.boot
        spring-boot-starter-parent
        2.5.0
        
    

    
        UTF-8
        UTF-8
    

    

        
        
            org.apache.poi
            poi
            5.0.0
        

        
        
            org.apache.poi
            poi-ooxml
            5.0.0
        


        
            org.springframework.boot
            spring-boot-starter-web
        

        
            org.springframework.boot
            spring-boot-starter-test
            test
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
        
    

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;


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


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

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

    private List mockUsers() {
        List 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;


public final class ExcelUtil {


    private ExcelUtil() {
    }


    
    public static void export(HttpServletResponse response, String title, List 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();
                }
            }
        }

    }

    
    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，运行效果: