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