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("导入设备");
// 级联:https://www.limitcode.com/detail/5e908ec69581af02a004a91e.html
List<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);
//行号,起始为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, 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);
}
}
参考了:https://www.limitcode.com/detail/5e908ec69581af02a004a91e.html
这博主大哥给出了所有源码,我只是在此基础根据自己业务修改的