功能这么全的excel导出,你确定不需要?

举报
一只牛博 发表于 2024/10/22 12:24:11 2024/10/22
【摘要】 实现单元格的合并,表头的过滤,列的选择,样式的设计等等

前言

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

Maven坐标

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

HSSFWorkbook、SXSSFWorkbook和XSSFWorkbook的区别

HSSFWorkbookXSSFWorkbookSXSSFWorkbook 是 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() 方法清理临时文件。

性能比较:

  • HSSFWorkbookXSSFWorkbook 适合较小的数据集,所有内容都保存在内存中,容易导致内存溢出。
  • 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);
      }
    }
  }
}
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。