导出Excel（ResponseEntity＜byte[]＞方式）

package com.lexue.mall.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayOutputStream;

/**
 * @ClassName: ExcelUtil
 * @Description: Excel导出工具类
 * @author: Created by xushuyi <a href="xu_shuyi1002@163.com">Contact author</a>
 * @date: 2019/10/15 9:31
 * @Version: V1.0
 */
@Slf4j
public class ExcelUtil {

    /**
     * 下载文件
     *
     * @param fileName  文件名
     * @param sheetName sheet页
     * @param title     标题集合
     * @param values    单元值集合
     * @return ResponseEntity
     */
    public static ResponseEntity<byte[]> exportExcel(
            String fileName,
            String sheetName,
            String[] title,
            String[][] values
    ) {
        try {
            // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet(sheetName);
            HSSFRow row = sheet.createRow(0);
            HSSFCellStyle style = wb.createCellStyle();
            // 创建一个居中格式
            style.setAlignment(HorizontalAlignment.CENTER);
            HSSFCell cell = null;
            //创建标题
            for (int i = 0; i < title.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(title[i]);
                cell.setCellStyle(style);
            }

            for (int i = 0; i < title.length; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }

            // 创建内容
            for (int i = 0; i < values.length; i++) {
                row = sheet.createRow(i + 1);
                for (int j = 0; j < values[i].length; j++) {
                    //将内容按顺序赋给对应的列对象
                    if (null == values[i][j] || values[i][j].trim().length() == 0) {
                        row.createCell(j).setCellValue("");
                    } else {
                        row.createCell(j).setCellValue(values[i][j]);
                    }
                }
            }
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            HttpHeaders headers = new HttpHeaders();
            // headers.add("Content-Type", "application/octet-stream;charset=utf-8");
            headers.add("Content-Type", "application/vnd.ms-excel");
            headers.add("Connection", "close");
            headers.add("Accept-Ranges", "bytes");
            headers.add("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("gbk"), "ISO-8859-1"));
            wb.write(baos);
            return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
        } catch (Exception e) {
            log.error("exportExcel出现异常，原因：{}", e);
            throw new RuntimeException("export excel exception");
        }
    }
}

需要引入的包：

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

详细例子：

/**
     * 课中管理 - 商品购买 - 导出
     *
     * @param cmsBuyGoodsRequest 请求数据
     * @return ResponseEntity
     */
    @PostMapping("/buy_goods_export")
    public ResponseEntity<byte[]> buyGoodsExport(LxCmsWebUserInfo lxCmsWebUserInfo, @RequestBody CmsBuyGoodsRequest cmsBuyGoodsRequest) {
        return iCmsCourseInManagerService.buyGoodsExport(lxCmsWebUserInfo, cmsBuyGoodsRequest);
    }


    /**
     * 课中管理 - 商品购买 - 导出
     *
     * @param lxCmsWebUserInfo   登录用户信息
     * @param cmsBuyGoodsRequest 请求数据
     * @return ResponseEntity
     */
    @Override
    public ResponseEntity<byte[]> buyGoodsExport(LxCmsWebUserInfo lxCmsWebUserInfo, CmsBuyGoodsRequest cmsBuyGoodsRequest) {
        // 手工设置每页条数，目的是为了全量导出筛选出的数据
        cmsBuyGoodsRequest.setPageNum(1);
        cmsBuyGoodsRequest.setPageSize(100000);
        PageDataObject<CmsBuyGoodsResponse> buyGoodsResponsePageDataObject = this.buyGoodsPage(lxCmsWebUserInfo, cmsBuyGoodsRequest);
        if (Objects.nonNull(buyGoodsResponsePageDataObject) && CollectionUtils.isNotEmpty(buyGoodsResponsePageDataObject.getContent())) {
            List<CmsBuyGoodsResponse> buyGoodsResponses = buyGoodsResponsePageDataObject.getContent();
            String fileName = "课中管理_商品购买_" + DateUtils.getDateToFormat(new Date(), DateUtils.DATE_FORMAT_STR7) + ".xls";
            String sheetName = "商品购买记录";
            // 定义导出Excel文件列名
            String[] title = {"乐学号", "昵称", "用户年级", "购买方式", "订单号", "订单金额", "订单完成时间", "授权时间", "有效截止时间", "授权状态"};
            String[][] content = new String[buyGoodsResponses.size()][];
            for (int i = 0; i < buyGoodsResponses.size(); i++) {
                CmsBuyGoodsResponse buyGoodsResponse = buyGoodsResponses.get(i);
                content[i] = new String[title.length];
                content[i][0] = buyGoodsResponse.getLeId();
                content[i][1] = buyGoodsResponse.getNick();
                content[i][2] = buyGoodsResponse.getUserGrade();
                content[i][3] = buyGoodsResponse.getBuyTypeName();
                content[i][4] = buyGoodsResponse.getOrderId();
                content[i][5] = buyGoodsResponse.getMallPrice();
                content[i][6] = buyGoodsResponse.getEffectTime();
                content[i][7] = buyGoodsResponse.getAuthorizeTime();
                content[i][8] = buyGoodsResponse.getInvalidTime();
                content[i][9] = buyGoodsResponse.getAuthorizeStatus();
            }
            return ExcelUtil.exportExcel(fileName, sheetName, title, content);
        }
        throw new RuntimeException("商品购买，暂无要导出的数据.");
    }

转：https://www.cnblogs.com/xushuyi/articles/11677334.html