/// <summary>
/// 将回路数据写入指定的EXCEL工作表
/// </summary>
/// <param name="excelService"></param>
/// <param name="models"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public bool ExportLoopDataToExcel(ref IExcelService excelService, List <LoopModel> models, string sheetName)
{
try
{
if (models.Count > 0)
{
IControllerConfig config = ControllerConfigManager.GetConfigObject(ControllerType.FT8000);
ColumnConfigInfo[] deviceColumnDefinitionArray = config.GetDeviceColumns(); //取得器件的列定义信息
List <MergeCellRange> lstMergeCellRange = new List <MergeCellRange>();
int startRowIndex = 0;
int currentRowIndex = 0;
foreach (var loop in models)
{
startRowIndex = currentRowIndex;
currentRowIndex++;
//回路标题
excelService.SetCellValue(sheetName, startRowIndex, 0, "回路:" + loop.Name, CellStyleType.SubCaption);
for (int devColumnCount = 0; devColumnCount < deviceColumnDefinitionArray.Length; devColumnCount++)
{
excelService.SetCellValue(sheetName, currentRowIndex, devColumnCount, deviceColumnDefinitionArray[devColumnCount].ColumnName, CellStyleType.TableHead);
}
//回路标题行合并
MergeCellRange mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = startRowIndex;
mergeCellRange.LastRowIndex = startRowIndex;
mergeCellRange.FirstColumnIndex = 0;
mergeCellRange.LastColumnIndex = deviceColumnDefinitionArray.Length - 1;
lstMergeCellRange.Add(mergeCellRange);
//写入器件信息
foreach (var device in loop.GetDevices <DeviceInfo8000>())
{
currentRowIndex++;
excelService.SetCellValue(sheetName, currentRowIndex, 0, device.Code, CellStyleType.Data); //器件编码
excelService.SetCellValue(sheetName, currentRowIndex, 1, config.GetDeviceTypeViaDeviceCode(device.TypeCode).Name, CellStyleType.Data); //器件类型
excelService.SetCellValue(sheetName, currentRowIndex, 2, device.Feature == null?null:device.Feature.ToString(), CellStyleType.Data); //特性
excelService.SetCellValue(sheetName, currentRowIndex, 3, device.Disable == null?null:device.Disable.ToString(), CellStyleType.Data); //屏蔽
excelService.SetCellValue(sheetName, currentRowIndex, 4, device.SensitiveLevel == null?null:device.SensitiveLevel.ToString(), CellStyleType.Data); //灵敏度
excelService.SetCellValue(sheetName, currentRowIndex, 5, device.LinkageGroup1, CellStyleType.Data); //输出组1
excelService.SetCellValue(sheetName, currentRowIndex, 6, device.LinkageGroup2, CellStyleType.Data); //输出组2
excelService.SetCellValue(sheetName, currentRowIndex, 7, device.LinkageGroup3, CellStyleType.Data); //输出组3
excelService.SetCellValue(sheetName, currentRowIndex, 8, device.DelayValue == null?null:device.DelayValue.ToString(), CellStyleType.Data); //延时
excelService.SetCellValue(sheetName, currentRowIndex, 9, device.sdpKey, CellStyleType.Data); //手操号
excelService.SetCellValue(sheetName, currentRowIndex, 10, device.ZoneNo == null?null:device.ZoneNo.ToString(), CellStyleType.Data); //分区
excelService.SetCellValue(sheetName, currentRowIndex, 11, device.BroadcastZone, CellStyleType.Data); //广播分区
excelService.SetCellValue(sheetName, currentRowIndex, 12, device.Location, CellStyleType.Data); //安装地点
}
currentRowIndex++;
}
excelService.SetMergeCells(sheetName, lstMergeCellRange);//设置"回路页签"合并单元格
excelService.SetColumnWidth(sheetName, 1, 15f);
excelService.SetColumnWidth(sheetName, 12, 50f);
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
/// <summary>
/// 将回路数据写入指定的EXCEL工作表
/// </summary>
/// <param name="excelService"></param>
/// <param name="models"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public bool ExportLoopDataToExcel(ref IExcelService excelService, List <LoopModel> models, string sheetName)
{
try
{
if (models.Count > 0)
{
IControllerConfig config = ControllerConfigManager.GetConfigObject(ControllerType.NT8021);
ColumnConfigInfo[] deviceColumnDefinitionArray = config.GetDeviceColumns(); //取得器件的列定义信息
List <MergeCellRange> lstMergeCellRange = new List <MergeCellRange>();
int startRowIndex = 0;
int currentRowIndex = 0;
foreach (var loop in models)
{
startRowIndex = currentRowIndex;
currentRowIndex++;
//回路标题
excelService.SetCellValue(sheetName, startRowIndex, 0, "回路:" + loop.Name, CellStyleType.SubCaption);
for (int devColumnCount = 0; devColumnCount < deviceColumnDefinitionArray.Length; devColumnCount++)
{
excelService.SetCellValue(sheetName, currentRowIndex, devColumnCount, deviceColumnDefinitionArray[devColumnCount].ColumnName, CellStyleType.TableHead);
}
//回路标题行合并
MergeCellRange mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = startRowIndex;
mergeCellRange.LastRowIndex = startRowIndex;
mergeCellRange.FirstColumnIndex = 0;
mergeCellRange.LastColumnIndex = deviceColumnDefinitionArray.Length - 1;
lstMergeCellRange.Add(mergeCellRange);
//写入器件信息
foreach (var device in loop.GetDevices <DeviceInfo8021>())
{
currentRowIndex++;
excelService.SetCellValue(sheetName, currentRowIndex, 0, device.Code, CellStyleType.Data); //器件编码
excelService.SetCellValue(sheetName, currentRowIndex, 1, config.GetDeviceTypeViaDeviceCode(device.TypeCode).Name, CellStyleType.Data); //器件类型
excelService.SetCellValue(sheetName, currentRowIndex, 2, device.Disable == null?null:device.Disable.ToString(), CellStyleType.Data); //屏蔽
excelService.SetCellValue(sheetName, currentRowIndex, 3, device.CurrentThreshold == null?null:device.CurrentThreshold.ToString(), CellStyleType.Data); //电流报警值
excelService.SetCellValue(sheetName, currentRowIndex, 4, device.TemperatureThreshold == null?null:device.TemperatureThreshold.ToString(), CellStyleType.Data); //温度报警值
excelService.SetCellValue(sheetName, currentRowIndex, 5, device.BuildingNo == null?null:device.BuildingNo.ToString(), CellStyleType.Data); //楼号
excelService.SetCellValue(sheetName, currentRowIndex, 6, device.ZoneNo == null?null:device.ZoneNo.ToString(), CellStyleType.Data); //区号
excelService.SetCellValue(sheetName, currentRowIndex, 7, device.FloorNo == null?null:device.FloorNo.ToString(), CellStyleType.Data); //层号
excelService.SetCellValue(sheetName, currentRowIndex, 8, device.RoomNo == null?null:device.RoomNo.ToString(), CellStyleType.Data); //房间号
excelService.SetCellValue(sheetName, currentRowIndex, 9, device.Location, CellStyleType.Data); //安装地点
}
}
excelService.SetMergeCells(sheetName, lstMergeCellRange);//设置"回路页签"合并单元格
excelService.SetColumnWidth(sheetName, 1, 15f);
excelService.SetColumnWidth(sheetName, 9, 50f);
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
protected override bool GenerateExcelTemplateLoopSheet(List <string> sheetNames, IControllerConfig config, Model.BusinessModel.ExcelTemplateCustomizedInfo summaryInfo, ref IExcelService excelService)
{
try
{
#region 生成所有回路页签模板
int currentLoopStartIndex = 2;
int defaultLoopCodeLength = 2; //默认回路编码长度
string loopSheetNamePrefix = ""; //回路页签名称前缀
short maxDeviceAmount = config.GetMaxDeviceAmountValue();
int maxLoopAmount = config.GetMaxLoopAmountValue(); //允许最大回路数量
int defaultDeviceTypeCode = summaryInfo.DefaultDeviceTypeCode; //默认器件编码
DeviceType defaultDeviceType = config.GetDeviceTypeViaDeviceCode(defaultDeviceTypeCode);
ColumnConfigInfo[] deviceColumnDefinitionArray = config.GetDeviceColumns(); //取得器件的列定义信息
List <MergeCellRange> lstMergeCellRange = new List <MergeCellRange>();
ControllerNodeModel[] nodes = config.GetNodes();
for (int i = 0; i < nodes.Length; i++)
{
switch (nodes[i].Type)
{
case ControllerNodeType.Loop:
loopSheetNamePrefix = nodes[i].Name;
break;
}
}
for (int i = currentLoopStartIndex; i <= summaryInfo.LoopSheetAmount + 1; i++)
{
lstMergeCellRange.Clear();
for (int j = 0; j < summaryInfo.LoopAmountPerSheet; j++)
{
if (maxLoopAmount < (j + 1 + (i - currentLoopStartIndex) * summaryInfo.LoopAmountPerSheet))//已经超出最大回路编号，退出循环
{
break;
}
string loopCode = summaryInfo.MachineNumberFormatted + (j + 1 + (i - currentLoopStartIndex) * summaryInfo.LoopAmountPerSheet).ToString().PadLeft(defaultLoopCodeLength, '0');
int extraLine = 0;
if (j != 0)
{
extraLine = 2;
}
//回路标题：回路1
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + j * extraLine, 0, loopSheetNamePrefix + ":" + loopCode, CellStyleType.SubCaption);
for (int devColumnCount = 0; devColumnCount < deviceColumnDefinitionArray.Length; devColumnCount++)
{
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + 1 + j * extraLine, devColumnCount, deviceColumnDefinitionArray[devColumnCount].ColumnName, CellStyleType.TableHead);
}
//回路标题行合并
MergeCellRange mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = j * maxDeviceAmount + j * extraLine;
mergeCellRange.LastRowIndex = j * maxDeviceAmount + j * extraLine;
mergeCellRange.FirstColumnIndex = 0;
mergeCellRange.LastColumnIndex = deviceColumnDefinitionArray.Length - 1;
lstMergeCellRange.Add(mergeCellRange);
//回路默认器件信息
for (int k = 0; k < maxDeviceAmount; k++)
{
string deviceCode = (k + 1).ToString().PadLeft(summaryInfo.SelectedDeviceCodeLength - defaultLoopCodeLength - summaryInfo.MachineNumberFormatted.Length, '0');
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 0, loopCode + deviceCode, CellStyleType.Data); //器件编码
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 1, defaultDeviceType.Name, CellStyleType.Data); //器件类型
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 2, null, CellStyleType.Data);
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 3, "0", CellStyleType.Data); //屏蔽
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 4, "2", CellStyleType.Data); //灵敏度
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 5, null, CellStyleType.Data);
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 6, null, CellStyleType.Data);
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 7, null, CellStyleType.Data);
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 8, null, CellStyleType.Data);
excelService.SetCellValue(sheetNames[i], j * maxDeviceAmount + k + (j * extraLine + 2), 9, null, CellStyleType.Data);
}
mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = j * maxDeviceAmount + (j * extraLine + 2);
mergeCellRange.LastRowIndex = j * maxDeviceAmount + (j * extraLine + 2) + maxDeviceAmount - 1;
mergeCellRange.FirstColumnIndex = 1;
mergeCellRange.LastColumnIndex = 1;
excelService.SetSheetValidationForListConstraint(sheetNames[i], RefereceRegionName.DeviceType.ToString(), mergeCellRange);
mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = j * maxDeviceAmount + (j * extraLine + 2);
mergeCellRange.LastRowIndex = j * maxDeviceAmount + (j * extraLine + 2) + maxDeviceAmount - 1;
mergeCellRange.FirstColumnIndex = 2;
mergeCellRange.LastColumnIndex = 2;
excelService.SetSheetValidationForListConstraint(sheetNames[i], RefereceRegionName.Feature.ToString(), mergeCellRange);
mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = j * maxDeviceAmount + (j * extraLine + 2);
mergeCellRange.LastRowIndex = j * maxDeviceAmount + (j * extraLine + 2) + maxDeviceAmount - 1;
mergeCellRange.FirstColumnIndex = 3;
mergeCellRange.LastColumnIndex = 3;
excelService.SetSheetValidationForListConstraint(sheetNames[i], RefereceRegionName.Disable.ToString(), mergeCellRange);
mergeCellRange = new MergeCellRange();
mergeCellRange.FirstRowIndex = j * maxDeviceAmount + (j * extraLine + 2);
mergeCellRange.LastRowIndex = j * maxDeviceAmount + (j * extraLine + 2) + maxDeviceAmount - 1;
mergeCellRange.FirstColumnIndex = 4;
mergeCellRange.LastColumnIndex = 4;
excelService.SetSheetValidationForListConstraint(sheetNames[i], RefereceRegionName.SensitiveLevel.ToString(), mergeCellRange);
}
excelService.SetColumnWidth(sheetNames[i], 1, 15f);
excelService.SetColumnWidth(sheetNames[i], 9, 50f);
excelService.SetMergeCells(sheetNames[i], lstMergeCellRange);//设置"回路页签"合并单元格
}
#endregion
}
catch (Exception ex)
{
return(false);
}
return(true);
}
