java操作Excel 添加下拉列表

概述

ExcelDropdownUtil 是一个通用工具类，用于在指定的 Excel 文件中为某一列添加下拉列表。此工具类可以根据传入的模板路径、输出路径、工作表索引、列索引、起始行和下拉选项列表来动态生成带有下拉列表的 Excel 文件。适用于任何需要在 Excel 表格中进行数据验证的场景。

类结构

public class ExcelDropdownUtil {
    public static void main(String[] args);
    public static File generateExcelWithDropdown(String templatePath, String outputFileName, Integer sheetIndex, Integer columnIndex, Integer startRow, List<String> dropdownOptions);
    private static void addDropdownToColumn(List<String> dropdownOptions, Sheet sheet, int columnIndex, int startRow);
}

方法描述

1. main(String[] args)

概述

示例方法，用于演示如何使用 ExcelDropdownUtil 类。它加载一个 Excel 模板，在指定列添加下拉列表，并生成一个新的 Excel 文件。

示例代码

public static void main(String[] args) {
    List<String> dropdownOptions = Arrays.asList("Option A", "Option B", "Option C");
    int columnIndex = 6; // 第7列 (索引从0开始)
    int startRow = 1; // 从第2行开始
    Integer sheetIndex = 0;

    String templatePath = "/path/to/excelTemplate.xlsx";
    String outputFileName = "/path/to/outputFile.xlsx";

    generateExcelWithDropdown(templatePath, outputFileName, sheetIndex, columnIndex, startRow, dropdownOptions);
}

2. generateExcelWithDropdown(String templatePath, String outputFileName, Integer sheetIndex, Integer columnIndex, Integer startRow, List<String> dropdownOptions)

概述

生成包含指定列下拉列表的 Excel 文件。

参数

• templatePath：String，Excel 模板文件的路径。
• outputFileName：String，生成的 Excel 文件的输出路径。
• sheetIndex：Integer，要修改的工作表的索引（从0开始）。
• columnIndex：Integer，要应用下拉列表的列索引（从0开始）。
• startRow：Integer，要应用下拉列表的起始行（从0开始）。
• dropdownOptions：List<String>，下拉列表的选项列表。

返回值

• File：生成的 Excel 文件。

3. addDropdownToColumn(List<String> dropdownOptions, Sheet sheet, int columnIndex, int startRow)

概述

为指定工作表的某一列添加下拉列表。

参数

• dropdownOptions：List<String>，下拉列表的选项列表。
• sheet：Sheet，要添加下拉列表的工作表。
• columnIndex：int，要应用下拉列表的列索引（从0开始）。
• startRow：int，要应用下拉列表的起始行（从0开始）。

完整代码

package com.example.excelutils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

/**
 * 用于生成Excel文件，并在指定列添加下拉列表的工具类。
 */
@Slf4j
public class ExcelDropdownUtil {

    public static void main(String[] args) {
        List<String> dropdownOptions = Arrays.asList("Option A", "Option B", "Option C");
        int columnIndex = 6; // 第7列 (索引从0开始)
        int startRow = 1; // 从第2行开始
        Integer sheetIndex = 0;

        String templatePath = "/path/to/excelTemplate.xlsx";
        String outputFileName = "/path/to/outputFile.xlsx";

        generateExcelWithDropdown(templatePath, outputFileName, sheetIndex, columnIndex, startRow, dropdownOptions);
    }

    /**
     * 生成包含指定列下拉列表的Excel文件。
     *
     * @param templatePath   Excel模板文件路径
     * @param outputFileName 输出Excel文件路径
     * @param sheetIndex     要修改的工作表索引
     * @param columnIndex    要应用下拉列表的列索引
     * @param startRow       要应用下拉列表的起始行
     * @param dropdownOptions 下拉列表的选项列表
     * @return               生成的Excel文件
     */
    public static File generateExcelWithDropdown(String templatePath, String outputFileName, Integer sheetIndex, Integer columnIndex, Integer startRow, List<String> dropdownOptions) {
        Workbook workbook;

        try {
            // 加载Excel模板
            workbook = new XSSFWorkbook(new File(templatePath));
        } catch (IOException | InvalidFormatException e) {
            log.error("加载Excel模板文件失败: {}", e.getMessage());
            throw new RuntimeException("加载Excel模板文件失败", e);
        }

        // 获取指定的工作表
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        // 为指定列设置下拉列表
        addDropdownToColumn(dropdownOptions, sheet, columnIndex, startRow);

        // 生成新的Excel文件
        File outputFile = new File(outputFileName);
        try (FileOutputStream out = new FileOutputStream(outputFile)) {
            // 写入更改的数据到文件
            workbook.write(out);
            log.info("Excel文件生成成功, 文件路径: {}", outputFile.getAbsolutePath());
        } catch (IOException e) {
            log.error("写入Excel文件失败: {}", e.getMessage());
            throw new RuntimeException("写入Excel文件失败", e);
        }

        return outputFile;
    }

    /**
     * 为指定工作表的某一列添加下拉列表。
     *
     * @param dropdownOptions 下拉列表的选项列表
     * @param sheet 要添加下拉列表的工作表
     * @param columnIndex 要应用下拉列表的列索引
     * @param startRow 要应用下拉列表的起始行
     */
    private static void addDropdownToColumn(List<String> dropdownOptions, Sheet sheet, int columnIndex, int startRow) {

        int endRow = 1048575; // Excel的最大行数（适用于较新版本的Excel）

        // 创建数据验证助手
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(dropdownOptions.toArray(new String[0]));
        CellRangeAddressList addressList = new CellRangeAddressList(startRow, endRow, columnIndex, columnIndex);
        DataValidation validation = validationHelper.createValidation(constraint, addressList);

        // 设置数据验证以显示下拉箭头并启用错误警告
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true); // 启用错误框，如果输入无效

        // 可以进一步定制错误消息
        if (validation instanceof XSSFDataValidation) {
            XSSFDataValidation xssfValidation = (XSSFDataValidation) validation;
            xssfValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            xssfValidation.createErrorBox("输入错误", "只能从下拉列表中选择一个有效的选项");
        }

        // 将验证添加到工作表中
        sheet.addValidationData(validation);
    }
}

依赖项

该工具类依赖于 Apache POI 库来处理 Excel 文件。你需要在你的项目中包含以下 Maven 依赖：

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
    </dependency>
</dependencies>

注意事项

1. 确保模板文件路径和输出文件路径有效，并且有适当的读写权限。
2. 根据实际场景调整版本号。(一些常用的excel包可能已经包含了poi)
3. 使用 Arrays.asList() 来创建列表，这在 JDK 1.8 中是兼容的。

通过这些代码和依赖配置，你可以轻松地在 Excel 文件中生成带有下拉列表的数据验证功能。