夫天地者,万物之逆旅;光阴者,百代之过客。而浮生若梦,为欢几何?
C#使用NPOI生成Excel级联下拉列表

前言

因实际需求要使用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中的级联下拉效果。

作者:一蓑烟雨

本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

0

支持

0

反对

posted @2020-4-10  拜读(312)

评论列表

评论内容:



喜欢请打赏

支付宝 微信

请放心支付