net core--Excel 三个列级联下拉查询（国家，省，市）

用到的组件是：NPOI

1.main中的方法

string filePathAndName = "D:\" + Guid.NewGuid().ToString() + ".xls";
            using (FileStream fs = new FileStream(filePathAndName, FileMode.Create, FileAccess.Write))
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("sheet1");
                SetCityCellDropdownList(workbook, sheet, "CityDictionary", 1, 2, 1);
                workbook.Write(fs);
                fs.Flush(true);
            }

2.主要实现代码

  private static void SetCellDropdownList(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string name)
        {
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(name);
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
            dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
            sheet.AddValidationData(dataValidate);
        }

private static void SetCityCellDropdownList(HSSFWorkbook workbook, ISheet sheet, string dictionaryName, int citycol, int areacol, int sheetIndex)
        {
            var gList = new List<KeyValue>();
            var pList1 = new List<KeyValue>();
            var pList2 = new List<KeyValue>();
            var citylist1 = new List<KeyValue>();
            var citylist2 = new List<KeyValue>();
            var citylist3 = new List<KeyValue>();
            citylist1.Add(new KeyValue { Name = "杭州", AreaList = new List<KeyValue> { new KeyValue { Name = "上城区" }, new KeyValue { Name = "古区" } } });
            citylist2.Add(new KeyValue { Name = "苏州", AreaList = new List<KeyValue> { new KeyValue { Name = "高新区" }, new KeyValue { Name = "园区" } } });
            citylist2.Add(new KeyValue { Name = "南京", AreaList = new List<KeyValue> { new KeyValue { Name = "雨花台" } } });
            citylist3.Add(new KeyValue { Name = "纽约", AreaList = new List<KeyValue> { new KeyValue { Name = "高新区" }, new KeyValue { Name = "园区" } } });
            pList1.Add(new KeyValue { Name = "浙江", AreaList = citylist1 });
            pList1.Add(new KeyValue { Name = "江苏", AreaList = citylist2 });
            pList2.Add(new KeyValue { Name = "纽约州", AreaList = citylist3 });
            gList.Add(new KeyValue { Name = "中国", AreaList = pList1 });
            gList.Add(new KeyValue { Name = "美国", AreaList = pList2 });

            int pcount = gList.Count;
            ISheet sheet2 = workbook.CreateSheet(dictionaryName);
            //隐藏
            // workbook.SetSheetHidden(sheetIndex, true);

            #region 城市区域数据构造
            //国家
            int rowIndex = 0;
            foreach (var item in gList)
            {
                IRow row = sheet2.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue(item.Name);
                rowIndex++;
            }
            //省
            int n_rowIndex = 0;
            foreach (var item in gList)
            {
                int areaIndex = 0;
                foreach (var city in item.AreaList)
                {
                    IRow row = sheet2.GetRow(areaIndex);
                    if (row == null)
                    {
                        row = sheet2.CreateRow(areaIndex);
                    }
                    row.CreateCell(n_rowIndex + 1).SetCellValue(city.Name);
                    areaIndex++;
                }
                n_rowIndex++;
            }
            //市
            foreach (var contoury in gList)
            {
                foreach (var p in contoury.AreaList)
                {
                    int cityIndex = 0;
                    if (p.AreaList.Count > 0)
                    {
                        foreach (var city in p.AreaList)
                        {
                            IRow row = sheet2.GetRow(cityIndex);
                            if (row == null)
                            {
                                row = sheet2.CreateRow(cityIndex);
                            }
                            row.CreateCell(n_rowIndex + 1).SetCellValue(city.Name);
                            cityIndex++;
                        }
                        n_rowIndex++;
                    }
                }
            }
            #endregion

            #region 设置数据字段范围
            //定义国家
            int columnIndex = 1;
            IName range_Country = workbook.CreateName();
            range_Country.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), pcount);
            range_Country.NameName = "国家";

            //定义省
            foreach (var item in gList)
            {
                int areacount = item.AreaList.Count;
                columnIndex++;
                IName range_area = workbook.CreateName();
                range_area.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), areacount);
                range_area.NameName = item.Name;
            }
            //定义市
            foreach (var item in gList)
            {
                if (item.AreaList.Count > 0)
                {
                    foreach (var city in item.AreaList)
                    {
                        int areacount = city.AreaList.Count;
                        columnIndex++;
                        IName range_area = workbook.CreateName();
                        range_area.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", dictionaryName, GetExcelColumnName(columnIndex), areacount);
                        range_area.NameName = city.Name;
                    }
                }
            }

            //城市列表下拉绑定
            SetCellDropdownList(sheet, 1, 65535, citycol, citycol, "国家");
 
            //第二列，跟随第一列联动
            string colName = GetExcelColumnName(areacol);
            for (int j = 1; j < 500; j++)
            {
                SetCellDropdownList(sheet, j, j, areacol, areacol, string.Format("INDIRECT(${0}${1})", colName, j + 1));
            }
            //第三列跟第二列联动
            string colNameCity = GetExcelColumnName(3);
            for (int j = 1; j < 500; j++)
            {
                SetCellDropdownList(sheet, j, j, 3, 3, string.Format("INDIRECT(${0}${1})", colNameCity, j + 1));
            }
            #endregion
        }

 private 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;
        }