C# NPOI 导出Excel模板 下拉框 级联下拉框 级联选择， 导出模板下载
源码在后面，前面稍微说一下咋回事
首先明白Excel 中 的数据引用咋回事，然后知道INDIRECT 这个函数。 自行搜索
我的需求是
设备类型 分为塔机和升降机
塔机下面又有一堆型号
这是数据源表
/// <summary>/// 导出模板/// </summary>/// <returns></returns>[RemoteService(false)]public ExportExcelResultClass ExportDeviceTemplate(){ExportExcelResultClass exportExcelResultClass = new ExportExcelResultClass();exportExcelResultClass.excelFileName = "";首先创建Excel文件对象var workbook = new XSSFWorkbook();创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)var sheet = workbook.CreateSheet("导入设备");// 级联：/detail/5e908ec69581af02a004a91e.htmlList<DataEntity> result = new List<DataEntity>();//设备类型数据源var deviceTypes = _deviceTypeManager.GetAllDeviceTypes();foreach (var deviceType in deviceTypes){// 根据设备类型取具体的类型var deviceModels = _deviceModelManager.GetDeviceModelByTypeId(deviceType.Id);DataEntity entity = new DataEntity();entity.name = deviceType.Name;entity.child = new List<DataEntity>();if (deviceModels != null && deviceModels.Count > 0){foreach (var deviceModel in deviceModels){DataEntity childEntity = new DataEntity();childEntity.name = deviceModel.TypeName;entity.child.Add(childEntity);}}result.Add(entity);}string sheetName = "Type";//创建sheet，用于制作数据源ISheet typeSheet = workbook.CreateSheet(sheetName);//隐藏数据源表workbook.SetSheetHidden(workbook.GetSheetIndex(typeSheet), true);//行号，起始为0int 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, string.Format("=Type!$1:$1"), 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);}//excel保存文件名string excelFileName = "导入设备.xlsx";var fileStream = new MemoryStream();//向Excel文件对象写入文件流，生成Excel文件workbook.Write(fileStream);exportExcelResultClass.excelFileName = excelFileName;exportExcelResultClass.fileStream = fileStream;return exportExcelResultClass;}
public class ExportExcelResultClass
{
public string excelFileName { get; set; }
public MemoryStream fileStream { get; set; }
}
我这个是为了下载模板，所以最后导出了一个下载Excel
基于Abp vNext的，下载Controller在 Web.host 中的 HomeController
/// <summary>/// 下载设备导入模板/// </summary>/// <returns></returns>[HttpGet][DontWrapResult][AbpAuthorize]public FileResult DownloadExportDeviceTemplate(){ExportExcelResultClass exportExcelResultClass = _deviceAppService.ExportDeviceTemplate();using (exportExcelResultClass.fileStream){var content = exportExcelResultClass.fileStream.ToArray();return File(content,"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",exportExcelResultClass.excelFileName);}}
参考了：/detail/5e908ec69581af02a004a91e.html
这博主大哥给出了所有源码，我只是在此基础根据自己业务修改的
