Java使用SXSSFWorkbook导出Excel工具类

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();
        }
    }

}