Exemplo n.º 1
0
 /// <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);
 }
Exemplo n.º 2
0
 /// <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);
 }
Exemplo n.º 3
0
 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);
 }