前言

因实际需求要使用NPOI导出Excel，生成下拉列表，同时还要求部分列可以级联选择。虽然有前人的经验总结，但自己实现的过程中也折腾了好久，所以特此记录一下。

生成普通的下拉列表

在导出的Excel中有些列不需要级联，因此只需要生成下拉即可，这个功能实现较为简单，首先通过图来演示效果，然后在附上代码。

核心代码

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(tempstr.TrimEnd(',').Split(','));
CellRangeAddressList addressList = new CellRangeAddressList(1, 500, 0, 0);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);

踩坑

这里看似简单，但其实在实际操作的时候，如果下拉数据源的长度过长的话，会出现如下错误而导致导出失败：

String literals in formulas can't be bigger than 255 Chars ASCII

这是由于Excel本身的限制，因此我们需要特殊处理一下才可以正常导出，核心代码如下（Excel2003的用法）：

private void CreateDropDownListForExcel(HSSFSheet sheet, IList<string> dropDownValues, int startRow, int lastRow, int column)
{
    if (sheet == null)
    {
        return;
    }
    IWorkbook workbook = sheet.Workbook;
    string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
    ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
    for (int i = 0, length = dropDownValues.Count; i < length; i++)
    {
        string name = dropDownValues[i];
        IRow row = hiddenSheet.CreateRow(i);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue(name);
    }
    IName namedCell = workbook.CreateName();
    namedCell.NameName = dropDownName;
    namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Count);
    DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(dropDownName);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
    workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
    sheet.AddValidationData(validation);
}

Excel2007及以上的用法：

private void CreateDropDownListForExcel2(XSSFSheet sheet, IList<string> dropDownValues, int startRow, int lastRow, int column)
{
    if (sheet == null)
    {
        return;
    }
    IWorkbook workbook = sheet.Workbook;
    string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
    ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
    for (int i = 0, length = dropDownValues.Count; i < length; i++)
    {
        string name = dropDownValues[i];
        IRow row = hiddenSheet.CreateRow(i);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue(name);
    }
    IName namedCell = workbook.CreateName();
    namedCell.NameName = dropDownName;
    namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Count);
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(dropDownName);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
    XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
    int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
    workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
    sheet.AddValidationData(validation);
}

然后生成指定的列时，如下方式调用：

List<string> floorCodes = new List<string>();
if (dtFloors != null && dtFloors.Rows.Count > 0)
{
    for (int i = 0; i < dtFloors.Rows.Count; i++)
    {
        string phaseFloor = dtFloors.Rows[i]["Code"].ToString() + "【" + dtFloors.Rows[i]["Name"].ToString() + "】";
        floorCodes.Add(phaseFloor);
    }
    CreateDropDownListForExcel2(sheet, floorCodes, 1, 500, 12);
}

生成级联下拉列表

前面图中展示的是两列互不关联的列，下拉数据源是各自生成的，其实这两者是有关联关系的（类似省市县三级联动），选择一个分期下有多个楼栋，所以选择一个分期后，需要自动带出此分期下的楼栋，作为第二列的下拉数据源。效果如下：

下面将直接贴上核心代码：

/// <summary>
/// 格式化数据，并建立名称管理
/// </summary>
/// <param name="sheet">表</param>
/// <param name="model">数据源(数据库)</param>
/// <param name="firstCellName">第一列的名称</param>
/// <param name="rowNo">当前操作的行号</param>
/// <param name="workbook">工作簿</param>
/// <param name="sheetName">工作表名</param>
private static void FormatData(ISheet sheet, List<DataEntity> model, string firstCellName, ref int rowNo, XSSFWorkbook workbook, string sheetName)
{
    //按行写入类型数据
    IRow row = sheet.CreateRow(rowNo);
    row.CreateCell(0).SetCellValue(firstCellName);
    int rowCell = 1;
    foreach (DataEntity item in model)
    {
        row.CreateCell(rowCell).SetCellValue(item.name);
        rowCell++;
    }
    //建立名称管理
    rowNo++;
    IName range = workbook.CreateName();
    range.NameName = firstCellName;
    string colName = GetExcelColumnName(model.Count + 1);
    range.RefersToFormula = string.Format("{0}!$B${1}:${2}${1}", sheetName, rowNo, colName);
    range.Comment = rowNo.ToString("00");
}

/// <summary>
/// 建立级联关系
/// </summary>
/// <param name="sheet">表</param>
/// <param name="source">数据源(EXCEL表)</param>
/// <param name="minRow">起始行</param>
/// <param name="maxRow">终止行</param>
/// <param name="minCell">起始列</param>
/// <param name="maxCell">终止列</param>
private static void ExcelLevelRelation(ISheet sheet, string source, int minRow, int maxRow, int minCell, int maxCell)
{
    //第一层绑定下拉的时候，可以一次性选择多个单元格进行绑定
    //第是从第二层开始，就只能一对一的绑定，如果目标单元格要与哪一个一级单元格进行关联
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet as XSSFSheet);
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(source);
    CellRangeAddressList cellRegions = new CellRangeAddressList(minRow, maxRow, minCell, maxCell);
    XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, cellRegions);
    validation.SuppressDropDownArrow = true;
    validation.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
    validation.ShowErrorBox = true;
    sheet.AddValidationData(validation);
}

/// <summary>
/// 获取Excel列名
/// </summary>
/// <param name="columnNumber">列的序号，如：A、B、C、AA、BB</param>
/// <returns></returns>
static string GetExcelColumnName(int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;
    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }
    return columnName;
}

为了导出级联列表，我们还需要初始化满足其需要的数据格式，因此定义这样一个实体对象：

public class DataEntity
{
    /// <summary>
    /// 名称
    /// </summary>
    internal string name { get; set; }
    /// <summary>
    /// 子级
    /// </summary>
    internal List<DataEntity> child { get; set; }
}

构造数据实体

for (int i = 0; i < dtPhases.Rows.Count; i++)
{
    DataTable dtFloors = GetFloors();
    DataEntity entity = new DataEntity();
    entity.name = dtPhases.Rows[i]["name"].ToString();
    entity.child = new List<DataEntity>();
    if (dtFloors != null && dtFloors.Rows.Count > 0)
    {
        for (int j = 0; j < dtFloors.Rows.Count; j++)
        {
            DataEntity childEntity = new DataEntity();
            childEntity.name = dtFloors.Rows[j]["Code"].ToString() + "【" + dtFloors.Rows[j]["Name"].ToString() + "】";
            entity.child.Add(childEntity);
        }
    }
    result.Add(entity);
}

最后就是调用上面的方法生成所需的Excel

string sheetName = "Type";
//创建sheet，用于制作数据源
ISheet typeSheet = workbook.CreateSheet(sheetName);
//隐藏数据源表
workbook.SetSheetHidden(workbook.GetSheetIndex(typeSheet), true);
//行号，起始为0
int rowNo = 0;
//整理一级数据
FormatData(typeSheet, result, "分期楼栋", ref rowNo, workbook, sheetName);
//整理二级数据
foreach (DataEntity item in result)
{
    FormatData(typeSheet, item.child, item.name, ref rowNo, workbook, sheetName);
}
//给500行创建下拉级别关系
int rowCount = 500;
//最小开始列
int minCell = 0;
//最大结束列
int maxCell = 0;
//第一级制作下拉
ExcelLevelRelation(sheet, "分期楼栋", 1, rowCount - 1, minCell, maxCell);
for (int j = 1; j <= rowCount; j++)
{
    int beginCell = minCell;
    int endCell = maxCell;
    //第二级绑定与第一级的级联关系(EXCEL中叫引用)
    ExcelLevelRelation(sheet, string.Format("INDIRECT(${0}${1})", "A", j + 1), j, j, ++beginCell, ++endCell);
}

到这里，我们就实现了图2中的级联下拉效果。