功能这么全的excel导出，你确定不需要？

一只牛博

已于 2024-09-12 12:48:29 修改

阅读量633

点赞数 13

分类专栏： # springboot 文章标签： excel

于 2024-09-12 12:47:13 首次发布

本文链接：https://blog.csdn.net/Mrxiao_bo/article/details/142172332

版权

springboot 专栏收录该内容

68 篇文章 7 订阅

订阅专栏

欢迎来到我的博客，代码的世界里，每一行都是一个故事

🎏：你只管努力，剩下的交给时间

🏠 ：小破站

前言

“导出一个 Excel 文件难吗？可能你已经使用了许多第三方工具来实现这个功能，但今天我要向你介绍 Apache POI。它不仅仅是导出 Excel，它还能让你精确控制每一个单元格的格式、数据类型和样式。想象一下，几行代码就能生成一个定制化的表格！接下来，让我们一起来看看这个工具有多么强大。”

Maven坐标

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

HSSFWorkbook、SXSSFWorkbook和XSSFWorkbook的区别

HSSFWorkbook、XSSFWorkbook 和 SXSSFWorkbook 是 Apache POI 中用于处理不同类型 Excel 文件的三种工作簿对象，它们之间的区别主要体现在对文件格式的支持和性能处理方面。

1. HSSFWorkbook

支持的文件格式：Excel 97-2003（.xls）
文件格式限制：.xls 文件格式是二进制文件格式，最大支持 65,536 行和 256 列。
内存消耗：所有的 Excel 数据都会加载到内存中，处理大文件时会出现内存不足的情况。
适用场景：适合处理较小的旧版 .xls 文件，当数据量不大时可以使用。

示例：

Workbook workbook = new HSSFWorkbook(); // 创建 .xls 文件

2. XSSFWorkbook

支持的文件格式：Excel 2007 及以上版本（.xlsx）
文件格式限制：.xlsx 是基于 XML 的文件格式，支持 1,048,576 行和 16,384 列，远大于 .xls 的限制。
内存消耗：和 HSSFWorkbook 类似，所有数据都会加载到内存中。如果数据量很大，可能导致 OutOfMemoryError。
适用场景：用于生成或操作较新的 .xlsx 文件，适合数据量较小或中等的场景。

示例：

Workbook workbook = new XSSFWorkbook(); // 创建 .xlsx 文件

3. SXSSFWorkbook

支持的文件格式：Excel 2007 及以上版本（.xlsx）
文件格式限制：和 XSSFWorkbook 一样，支持 1,048,576 行和 16,384 列。
内存消耗：SXSSFWorkbook 是基于 XSSFWorkbook 的流式处理实现，使用临时文件来存储不常用的行，避免将所有数据加载到内存中。适合处理大数据量的场景。
适用场景：特别适合导出大数据量的 Excel 文件，因为它不会消耗太多内存。默认只保留一定数量的行在内存中，其他行会写入到磁盘的临时文件中。

示例：

Workbook workbook = new SXSSFWorkbook(); // 创建流式写入的 .xlsx 文件

注意事项：

在写入完成后，需要调用 dispose() 方法清理临时文件。

性能比较：

HSSFWorkbook 和 XSSFWorkbook 适合较小的数据集，所有内容都保存在内存中，容易导致内存溢出。
SXSSFWorkbook 是大数据集的最佳选择，通过使用流式写入机制，有效控制内存使用，避免内存溢出问题。

总结：

HSSFWorkbook：处理旧版 .xls 文件，适合小数据量。
XSSFWorkbook：处理新版 .xlsx 文件，适合小到中等数据量。
SXSSFWorkbook：处理大数据量的 .xlsx 文件，采用流式写入方式节省内存。

SXSSFWorkbook相关实现

创建两个sheet

// 执行两次即可
SXSSFSheet sheet = workbook.createSheet(sheetName);

实现单元格合并

这里对于行合并与列合并都进行讲解

// 创建表头行的方法
private void createHeaderRows(Sheet sheet, Map<String, Object> headers) {
  // 创建第一行，用于存放一级表头
  Row row1 = sheet.createRow(0);
  // 创建第二行，用于存放嵌套表头
  Row row2 = sheet.createRow(1);

  int colIdx = 0; // 定义列索引
  // 遍历表头结构的每个条目
  for (Map.Entry<String, Object> entry : headers.entrySet()) {
    if (entry.getValue() instanceof String) {
      // 如果表头项是字符串类型（单行表头）
      Cell cell = row1.createCell(colIdx); // 在第一行创建单元格
      cell.setCellValue((String) entry.getValue()); // 设置单元格的值为表头项的值
      // 合并单元格，使该单元格占据两行
      sheet.addMergedRegion(new CellRangeAddress(0, 1, colIdx, colIdx));
      colIdx++; // 列索引加1，移动到下一个列位置
    } else if (entry.getValue() instanceof Map) {
      // 如果表头项是Map类型（多行表头）
      Cell cell = row1.createCell(colIdx); // 在第一行创建单元格
      cell.setCellValue(entry.getKey()); // 设置单元格的值为表头项的键（即第一层表头）

      // 将表头项的值转换为Map<String, String>类型，用于表示嵌套的子表头
      Map<String, String> subHeaders = (Map<String, String>) entry.getValue();
      int subColIdx = colIdx; // 定义子列索引，初始值为当前列索引
      // 遍历子表头结构的每个条目
      for (Map.Entry<String, String> subEntry : subHeaders.entrySet()) {
        Cell subCell = row2.createCell(subColIdx); // 在第二行创建单元格
        subCell.setCellValue(subEntry.getValue()); // 设置单元格的值为子表头的值
        subColIdx++; // 子列索引加1，移动到下一个列位置
      }
      // 合并单元格，使第一行的表头项跨越多列（与子表头对应）
      sheet.addMergedRegion(new CellRangeAddress(0, 0, colIdx, subColIdx - 1));
      colIdx = subColIdx; // 更新主列索引，使其指向下一个表头位置
    }
  }
}

基础样式实现

实现水平居中、垂直居中、上边框、下边框、左边框、右边框

/**
     * description: 基础样式创建
     * @param workbook workbook
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @since 2024/9/5
     */
public static CellStyle baseStyle(SXSSFWorkbook workbook) {
  CellStyle style = workbook.createCellStyle();
  // 水平居中
  style.setAlignment(HorizontalAlignment.CENTER);
  // 垂直居中
  style.setVerticalAlignment(VerticalAlignment.CENTER);
  // 上边框
  style.setBorderTop(BorderStyle.THIN);
  // 下边框
  style.setBorderBottom(BorderStyle.THIN);
  // 左边框
  style.setBorderLeft(BorderStyle.THIN);
  // 右边框
  style.setBorderRight(BorderStyle.THIN);
  return style;
}

设置固定列

sheet.createFreezePane(5, 0);

实现列的下拉选项

public static void exportExcelWithDropdown(SXSSFWorkbook workbook, SXSSFSheet sheet, int columnIndexWithValues) {
        Set<String> uniqueValues = new HashSet<>();
        // 从第3行开始遍历（跳过表头）
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 2; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                Cell cell = row.getCell(columnIndexWithValues);
                if (cell != null) {
                    uniqueValues.add(cell.getStringCellValue());
                }
            }
        }

        // Step 2: 将这些唯一值转换为下拉选项列表
        List<String> dropdownOptions = new ArrayList<>(uniqueValues);
        // 1. 创建隐藏的 Sheet 来存储下拉选项
        // 根据列号命名
        String hiddenSheetName = "hidden" + columnIndexWithValues;
        SXSSFSheet hiddenSheet = workbook.createSheet(hiddenSheetName);

        // 2. 将下拉选项写入隐藏的 Sheet（从第 0 行开始写入）
        for (int i = 0; i < dropdownOptions.size(); i++) {
            hiddenSheet.createRow(i).createCell(0).setCellValue(dropdownOptions.get(i));
        }

        // 3. 设置下拉选项范围公式：比如 hiddenSheetName!$A$1:$A$N
        String formula = hiddenSheetName + "!$A$1:$A$" + dropdownOptions.size();

        // 4. 创建数据验证帮助器
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);

        // 5. 将数据验证应用到指定的单元格区域
        CellRangeAddressList addressList = new CellRangeAddressList(3, lastRowNum, columnIndexWithValues, columnIndexWithValues);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);

        // 6. 设置验证的一些兼容性选项
        // 隐藏下拉箭头（兼容性设置）
        // 设置是否显示错误提示
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);

        // 7. 添加数据验证到表格中
        sheet.addValidationData(validation);

        // 8. 将隐藏的 Sheet 设置为不可见
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);
    }

实现表头过滤

/**
     * description: 实现表头过滤
     * @param sheet sheet
     * @param headerRowIndex 表头行
     * @since 2024/9/5
     */
public static void exportExcelWithFilter(SXSSFSheet sheet, int headerRowIndex) {
  // 获取表头行
  Row headerRow = sheet.getRow(headerRowIndex);

  // 检查表头行是否存在
  if (headerRow != null) {
    // 获取表头的最后一列索引
    int lastColumnIndex = headerRow.getLastCellNum() - 1;

    // 设置自动筛选，应用于整个表头所在的所有列
    sheet.setAutoFilter(new CellRangeAddress(headerRowIndex, sheet.getLastRowNum(), 0, lastColumnIndex));
  } else {
    log.error("表头行不存在，请检查 headerRowIndex 的值！");
  }

  // 清理资源或继续处理逻辑
}

设置合并区域中单元格边框

/**
     * description: 设置合并区域中所有单元格的边框
     * @param sheet sheet
     * @param startRow 开始行
     * @param endRow 结束行
     * @param startCol 开始列
     * @param endCol 结束列
     * @param style 样式
     * @since 2024/9/5
     */
public static void setBorders(Sheet sheet, int startRow, int endRow, int startCol, int endCol, CellStyle style) {
  for (int row = startRow; row <= endRow; row++) {
    Row sheetRow = sheet.getRow(row);
    if (sheetRow == null) {
      sheetRow = sheet.createRow(row);
    }
    for (int col = startCol; col <= endCol; col++) {
      Cell cell = sheetRow.getCell(col);
      if (cell == null) {
        cell = sheetRow.createCell(col);
      }
      cell.setCellStyle(style);
    }
  }
}

根据下标填充数据

/**
     * description: 填充数据
     * @param sheet sheet
     * @param data 数据
     * @param dataCellStyle 样式
     * @since 2024/9/5
     */
public static void fillDataRows(SXSSFSheet sheet, List<Map<String, Object>> data, CellStyle dataCellStyle) {
  // 数据从第三行开始（索引从3开始）
  int rowIdx = 3;
  // 遍历数据列表的每个数据行（Map结构）
  for (Map<String, Object> dataRow : data) {
    // 创建新行，并将行索引递增
    Row row = sheet.createRow(rowIdx++);
    // 列索引重置为0
    int colIdx = 0;
    // 遍历数据行中的每个值
    for (Object value : dataRow.values()) {
      // 在当前行创建新单元格，并将列索引递增
      Cell cell = row.createCell(colIdx++);
      // 设置单元格的值，若值为null则设置为空字符串
      cell.setCellValue((Double) (value != null ? value: 0));
      cell.setCellStyle(dataCellStyle);
    }
  }
}

根据别名填充数据

/**
     * description: fillDataRowsToAlisa
     * @param sheet sheet
     * @param data 数据
     * @param dataStyle 数据样式
     * @param columnAliasMap 列别名映射
     * @since 2024/9/9
     */
private static void fillDataRowsToAlisa(SXSSFSheet sheet, List<Map<String, Object>> data, CellStyle dataStyle,Map<String, Integer> columnAliasMap) {
  int rowIdx = 3;
  for (Map<String, Object> dataRow : data) {
    Row row = sheet.createRow(rowIdx++);
    for (Map.Entry<String, Object> entry : dataRow.entrySet()) {
      String alias = entry.getKey();
      Object value = entry.getValue();
      // 使用别名查找列索引
      Integer colIdx = columnAliasMap.get(alias);
      if (colIdx != null) {
        Cell cell = row.createCell(colIdx);
        cell.setCellValue(value != null ? value.toString() : "");
        cell.setCellStyle(dataStyle);
      }
    }
  }
}