Java使用SXSSFWorkbook导出Excel工具类

列 1	col2 （列2）	col3

前引

在项目中经常有导出Excel的需求，为了方便就封装了一个导出Excel到前端的工具类，使用下来还比较好用

使用JDK版本：1.8
spring boot版本：2.6.15
前端请求：axios

Java代码

Excel所需Maven依赖

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

完整代码如下：

public class excelUtils {

    public static CellStyle headStyle(Workbook workbook) {
        // 创建单元格样式
        CellStyle headerStyle = workbook.createCellStyle();

        // 设置字体样式
        Font headerFont = workbook.createFont();
        headerFont.setBold(true); // 加粗
        headerFont.setFontHeightInPoints((short) 12); // 字体大小
        headerStyle.setFont(headerFont);

        // 可选：设置其他样式属性
        headerStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        return headerStyle;
    }

    public static CellStyle tableStyle(Workbook workbook) {
        CellStyle tableStyle = workbook.createCellStyle();

        tableStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        tableStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        return tableStyle;
    }

    /**
     * @author Mango
     *
     * 获取对象的字段值
     * @param object 对象
     * @param key 字段名
     * @return 字段值
     */
    public static String getFieldString(Object object, String key) {
        if(object instanceof Map){
            return  String.valueOf(((Map<?, ?>) object).get(key));
        }

        Field field = null;
        String value = "";
        try {
            field = object.getClass().getDeclaredField(key);
            field.setAccessible(true);
            String orgStr = String.valueOf(field.get(object));
            if (orgStr != null) {
                value = orgStr;
                // 处理数组类型
                if (orgStr.startsWith("[") && orgStr.endsWith("]")) {
                    value = orgStr
                            .replace("[", "")
                            .replace("]", "")
                            .replace("\"", "");
                }
            }
        } catch (NoSuchFieldException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        return value;
    }

    /**
     * @author Mango
     * 导出Excel
     * @param response HttpServletResponse
     * @param srcList 数据源
     * @param keys 数据源对应字段列表
     * @param titles 表头
     * @param excelName 文件名
     */
    public static void exportExcel(HttpServletResponse response, List<?> srcList, List<String> keys, List<String> titles, String excelName) {
        // 创建workbook
        // 使用 SXSSFWorkbook 比 XSSFWorkbook 内存占用少
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 根据workbook创建sheet
        Sheet sheet = workbook.createSheet(excelName);
        //设置表头
        Row titleRoe = sheet.createRow(0);
        // 设置表头样式
        CellStyle headStyle = headStyle(workbook);
        // 向单元格中添加表头数据
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = titleRoe.createCell(i);
            cell.setCellValue(titles.get(i));
            cell.setCellStyle(headStyle);
        }
        // 定义数据样式
        CellStyle tableStyle = tableStyle(workbook);
        // 处理表格数据
        for (int i = 0; i < srcList.size(); i++) {
            // 获取一组数据
            Object object = srcList.get(i);
            // 生成行
            Row rowData = sheet.createRow(i + 1);
            // 设置行高
            rowData.setHeight((short) 400);
            // 向每一行中的单元格添加数据
            // keys 为对象字段列表
            for (int j = 0; j < keys.size(); j++) {
                String key = keys.get(j);
                Cell cell = rowData.createCell(j);
                cell.setCellStyle(tableStyle);
                String value = getFieldString(object, key);
                cell.setCellValue(value);
            }

            // 每100行就写一次磁盘，减少内存消耗
            if (i % 100 == 0) {
                try {
                    ((SXSSFSheet) sheet).flushRows(100);  // 将缓存中的数据写入磁盘
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        // 获取当前时间作为文件名
        LocalDateTime localDateTime = LocalDateTime.now();
        String fileName = excelName + localDateTime.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + ".xlsx";


        // 设置响应头
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            // 发生异常不处理中文文件名
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            throw new RuntimeException(e);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Accept-Ranges", "bytes");

        // 返回数据 返回进度条
        try (ByteArrayOutputStream fileOutputStream = new ByteArrayOutputStream();
             ServletOutputStream out = response.getOutputStream()) {

            // 这里假设 workbook 是生成的 Excel 文件对象
            workbook.write(fileOutputStream);

            // 获取文件的总大小，并设置响应头
            byte[] fileBytes = fileOutputStream.toByteArray();
            response.setHeader("Content-Length", String.valueOf(fileBytes.length));

            // 分块传输文件内容
            int chunkSize = 256; // 每次写入数据字节
            int totalSize = fileBytes.length;
            int currentPosition = 0;

            while (currentPosition < totalSize) {
                int remaining = totalSize - currentPosition;
                int chunk = Math.min(chunkSize, remaining);

                // 写入一块数据
                out.write(fileBytes, currentPosition, chunk);
                out.flush();  // 刷新输出流，确保数据传送给客户端

                // 更新当前下载位置
                currentPosition += chunk;
            }
            out.close();
            workbook.close(); // 关闭工作簿
        } catch (IOException e) {
            // 处理异常
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>();
            map.put("message", "下载文件失败: " + e.getMessage());

            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException ee) {
                ee.printStackTrace();
            }
            e.printStackTrace();
        }
    }

}

前端请求示例

请求接口

export const outExcel = async (
    data: any,
    downloadProgress: Function,
) => {
  return await request({
    url: "/test/outExcel",
    method: "post",
    data,
    responseType: "blob",
    onDownloadProgress: (event: any) => {
      downloadProgress(event);
    },
  });
}

注：

这里的代码是已经封装过的统一封装的axios
responseType： “blob” 指定文件为二进制
onDownloadProgress利用回调函数返回下载进度

下载示例

const exportExcel = async () => {
  isProcessing.value = true;
  let response = null;
  try {
    response = await props.downloadMethod(props.searchForm, (event: any) => {
      progress.value = (event.loaded / event.total) * 100;
      if (progress.value > 0) {
        isProcessing.value = false;
      }
    });
  } catch (e: any) {
    isProcessing.value = false; //出错时结束处理状态
    isError.value = true; //下载失败
  }

  if (response == null) return; // 没有返回数据
  if (!response.status) {
    isError.value = true; //接口返回错误设置为下载失败
  }

  const fileName =
    "教师参与情况" + formatDate(new Date(), "yy-MM-dd hh:mm:ss") + ".xlsx";
  const blob = new Blob([response.data], { type: "application/vnd.ms-excel" });

  // 创建一个链接来下载 Blob 对象
  const downloadLink = document.createElement("a");
  downloadLink.href = URL.createObjectURL(blob);
  downloadLink.download = decodeURI(fileName);
  downloadLink.style.display = "none";
  document.body.appendChild(downloadLink);

  // 触发点击事件以下载文件
  downloadLink.click();

  // 清理创建的链接对象
  document.body.removeChild(downloadLink);
};

将props.downloadMethod替换为下载接口

props.searchFormt替换为筛选数据