C# NPOI 导出Excel模板 下拉框 级联下拉框 级联选择

C#  NPOI 导出Excel模板  下拉框  级联下拉框 级联选择， 导出模板下载

 

主方法部分：

/// <summary>
/// 级联下拉测试-省市区
/// </summary>    
/// <returns></returns>    
[HttpGet]
public async Task<IActionResult> GetExcelLevelRelationTest()
{
    //模板路径：读取你的模板【采用你自己的方式读取即可】            
    var path = Path.Combine(_exportConfiguration.BasePath, "template", "省市区.xlsx");

    //首先创建Excel文件对象    
    try
    {
        XSSFWorkbook workbook = new XSSFWorkbook(path);
        //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
        var sheet = workbook.GetSheet("导入数据");
        // 级联：https://gitee.com/vess/npoi_exports_excel/blob/master/Vess.NPOI.Excel/Program.cs


        //****0、所属楼宇及楼层****//                
        var buildingData = await _buildingService.GetAllBuildingListAsync();
        var buildingList = buildingData.Items.OrderBy(x => x.Name).ToList();
        List<DataEntity> model2 = new List<DataEntity>();
        foreach (var building in buildingList)
        {
            DataEntity entity = new DataEntity()
            {
                Id = building.Id.ToString(),
                name = building.Name
            };
            entity.child = new List<DataEntity>();
            var dictChildren = building.BuildingLevelList.OrderBy(x => x.Name).ToList();
            if (dictChildren != null && dictChildren.Count > 0)
            {
                foreach (var curChildren in dictChildren)
                {
                    DataEntity childEntity = new DataEntity()
                    {
                        Id = curChildren.Id.ToString(),
                        name = curChildren.Name
                    };
                    entity.child.Add(childEntity);
                }
            }
            else
            {
                DataEntity childEntity = new DataEntity()
                {
                    Id = "",
                    name = ""
                };
                entity.child.Add(childEntity);
            }
            model2.Add(entity);
        }

        #region 省市区
        //获取数据源，就当是从数据库或其它地方取出来的
        List<DataEntity> model = DEntitys();

        #region 创建数据源格式表
        string sheetName = "ssq";
        //创建sheet，用于制作数据源
        ISheet typeSheet = workbook.CreateSheet(sheetName);
        //隐藏数据源表
        //开发环境时先注掉，以便随时查看数据源的效果，生产环境时打开，以便保护数据源
        //注意，需要隐藏的表，一定要最后建立，否则无法隐藏，因此这里先建立了Using表，然后再建立了Type表这个顺序一定不能反
        workbook.SetSheetHidden(workbook.GetSheetIndex(typeSheet), true);
        
        //行号，起始为0
        int rowNo = 0;//数据源表行号
        
        //整理一级数据
        FormatData(typeSheet, model, "省市区", ref rowNo, workbook, sheetName);
        //整理二级数据
        foreach (DataEntity item in model)
        {
            FormatData(typeSheet, item.child, item.name, ref rowNo, workbook, sheetName);
        }
        //整理三级数据
        foreach (DataEntity item in model)
        {
            foreach (DataEntity child in item.child)
            {
                FormatData(typeSheet, child.child, child.name, ref rowNo, workbook, sheetName);
            }
        }
        #endregion

        #region 创建级联关系       
        //给500行创建下拉级别关系
        int rowCount = 500;
        //最小开始列
        int minCell = 0;
        //最大结束列
        int maxCell = 0;
        //第一级制作下拉            
        ExcelLevelRelation(sheet, string.Format($"={sheetName}!$1:$1"), 1, rowCount + 1, minCell, maxCell);//OK

        for (int j = 0; j < rowCount; j++)
        {
            int beginCell = minCell;
            int endCell = maxCell;
            //第二级绑定与第一级的级联关系(EXCEL中叫引用)
            ExcelLevelRelation(sheet, string.Format("INDIRECT(${0}${1})", "A", j + 1), j, j, ++beginCell, ++endCell);//A，第二级数据的引用位置列名称
            //第三级绑定与第二级的级联关系(EXCEL中叫引用)
            ExcelLevelRelation(sheet, string.Format("INDIRECT(${0}${1})", "B", j + 1), j, j, ++beginCell, ++endCell);//B，第三级数据的引用位置列名称
        }
        #endregion 
        #endregion 
        
        var fileStream = new MemoryStream();
        //向Excel文件对象写入文件流，生成Excel文件
        workbook.Write(fileStream);
        using (fileStream)
        {
            var content = fileStream.ToArray();
            var memoryStream = new MemoryStream(content);
            var fileName = HttpUtility.UrlEncode($"省市区{DateTime.Now.ToString("yyyyMMddHHmmss")}", Encoding.GetEncoding("UTF-8"));
            var fileName1 = string.Format($"省市区{DateTime.Now.ToString("yyyyMMddHHmmss")}");
            var ret = new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                FileDownloadName = fileName1
            };
            return ret;
        }
    }
    catch (Exception e)
    {
        throw new UserFriendlyException(message: $"{e.Message} ");
    }        
}

 

 

 

级联数据实体定义：

/// <summary>
/// 级联数据实体
/// </summary>
public class DataEntity
{        
    /// <summary>
    /// Id
    /// </summary>
    public string Id { get; set; }

    /// <summary>
    /// 名称 
    /// </summary>
    public string name { get; set; }

    /// <summary>
    /// 下级
    /// </summary>
    public List<DataEntity> child { get; set; }
}

 

测试数据：

/// <summary>
/// 省市区-测试数据
/// </summary>
/// <returns></returns>
static List<DataEntity> DEntitys()
{
    List<DataEntity> result = new List<DataEntity>()
    {
        new DataEntity()
        {
            name ="江苏",
            child =new List<DataEntity>()
            {
                new DataEntity()
                {
                    name = "南京",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "鼓楼区" },
                        new DataEntity(){ name = "玄武区" }
                    }
                },
                new DataEntity()
                {
                    name = "苏州",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "吴中区" },
                        new DataEntity(){ name = "姑苏区" },
                        new DataEntity(){ name = "相城区" }
                    }
                },
                new DataEntity()
                {
                    name = "无锡",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "梁溪区" }
                    }
                },
            }
        },
        new DataEntity()
        {
            name ="山东",
            child =new List<DataEntity>()
            {
                new DataEntity()
                {
                    name = "济南",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "历下区" },
                        new DataEntity(){ name = "市中区" }
                    }
                },
                new DataEntity()
                {
                    name = "青岛",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "市南区" },
                        new DataEntity(){ name = "市北区" },
                        new DataEntity(){ name = "崂山区" },
                        new DataEntity(){ name = "李沧区" }
                    }
                }
            }
        },
        new DataEntity()
        {
            name ="浙江",
            child = new List<DataEntity>()
            {
                new DataEntity()
                {
                    name = "杭州",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "上城区" },
                        new DataEntity(){ name = "下城区" },
                        new DataEntity(){ name = "萧山区" }
                    }
                },
                new DataEntity()
                {
                    name = "宁波",
                    child = new List<DataEntity>()
                    {
                        new DataEntity(){ name = "江北区" },
                        new DataEntity(){ name = "海曙区" }
                    }
                }
            }
        }
    };
    return result;
}

 

级联数据格式化：

/// <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 = 0;
    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}!$A${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;
}