private static Excel.Range PutSampleData(Excel.Worksheet workSheet) { workSheet.Cells[2, 2].Value = "Datum"; workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString(); workSheet.Cells[2, 3].Value = "Columns1"; workSheet.Cells[3, 3].Value = 25; workSheet.Cells[4, 3].Value = 33; workSheet.Cells[5, 3].Value = 30; workSheet.Cells[6, 3].Value = 22; workSheet.Cells[2, 4].Value = "Column2"; workSheet.Cells[3, 4].Value = 25; workSheet.Cells[4, 4].Value = 33; workSheet.Cells[5, 4].Value = 30; workSheet.Cells[6, 4].Value = 22; workSheet.Cells[2, 5].Value = "Column3"; workSheet.Cells[3, 5].Value = 25; workSheet.Cells[4, 5].Value = 33; workSheet.Cells[5, 5].Value = 30; workSheet.Cells[6, 5].Value = 22; return workSheet.get_Range("$B2:$E6"); }
public static long FindRow(Excel.Worksheet sh, string searchtext, long columntosearch=1) { Excel.Range ur = (Excel.Range)sh.UsedRange; for (long r = 1; r < ur.Rows.Count; r++) { Excel.Range rng = sh.Cells[r, columntosearch]; var v = rng.Value; if (v == null) continue; if (v.Equals(searchtext) ) return r; } return 0; }
private void DeleteComment(Excel.Range Cell) { if(Cell.Comment != null){ Cell.Comment.Delete(); } }
public void SaveData(Excel.Worksheet xlSheet) { GetConfigFile(xlSheet.Application.ActiveWorkbook); var VariableListener = ChangeListener.Create(variable.xmlVariable); var AlarmListener = ChangeListener.Create(alarm.xmlAlarms); var StructureListener = ChangeListener.Create(structure.xmlStructure); var DriverListener = ChangeListener.Create(driver.DriverList); var ScaleListner = ChangeListener.Create(scale.xmlScaleElement); VariableListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); AlarmListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); StructureListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); DriverListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); ScaleListner.PropertyChanged += new PropertyChangedEventHandler( Listener_PropertyChanged); variable.xmlVariable.VariableList.CollectionChanged += new NotifyCollectionChangedEventHandler(OnAddingNew); alarm.xmlAlarms.AlarmList.CollectionChanged += new NotifyCollectionChangedEventHandler(OnAddingNew); driver.DriverList.CollectionChanged += new NotifyCollectionChangedEventHandler(OnAddingNew); var DriverList = driver.GetDriverList(); foreach (var element in DriverList) { driver.GetDriverTaskList(element).CollectionChanged += new NotifyCollectionChangedEventHandler(OnAddingNew);; } EditNewData(xlSheet); VariableListener.PropertyChanged -= new PropertyChangedEventHandler(Listener_PropertyChanged); AlarmListener.PropertyChanged -= new PropertyChangedEventHandler(Listener_PropertyChanged); StructureListener.PropertyChanged -= new PropertyChangedEventHandler(Listener_PropertyChanged); DriverListener.PropertyChanged -= new PropertyChangedEventHandler(Listener_PropertyChanged); ScaleListner.PropertyChanged -= new PropertyChangedEventHandler( Listener_PropertyChanged); driver.DriverList.CollectionChanged -= new NotifyCollectionChangedEventHandler(OnAddingNew); variable.xmlVariable.VariableList.CollectionChanged -= new NotifyCollectionChangedEventHandler(OnAddingNew); alarm.xmlAlarms.AlarmList.CollectionChanged -= new NotifyCollectionChangedEventHandler(OnAddingNew); driver.DriverList.CollectionChanged -= new NotifyCollectionChangedEventHandler(OnAddingNew); foreach (var element in DriverList) { driver.GetDriverTaskList(element).CollectionChanged -= new NotifyCollectionChangedEventHandler(OnAddingNew);; } Save(); }
public Dictionary<Row, Dictionary<string, CellInfo>> CommSettings(Excel.Workbook xlWb) { try { Dictionary<Row, Dictionary<string, CellInfo>> CommSettings = new Dictionary<Row, Dictionary<string, CellInfo>>(); Excel.Worksheet xlComm = (Excel.Worksheet)xlWb.Worksheets["ComSettings"]; Excel.Range xlCommRange = RealUsedRange(xlComm); for(int r = 2; r <= xlCommRange.Rows.Count; r++) { if (xlCommRange.Cells[r,1].Value2 != null){ Dictionary<string, CellInfo> myList = new Dictionary<string, CellInfo>(); for(int c = 1; c <= xlCommRange.Columns.Count; c++){ myList.Add( xlCommRange.Cells[1,c].Value2.ToString(), new CellInfo { Cell = xlCommRange.Cells[r,c] }); } CommSettings.Add(new Row {Name = xlCommRange.Cells[r,1].Value2.ToString()}, myList); } } return CommSettings; } catch (Exception) { throw new Exception("Configuration sheet not found"); } }
private void excelApplication_WorkbookDeactivate(Excel.Workbook Wb) { textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookDeactivate called." }); Wb.Dispose(); }
private XlFileFormat GetFileFormat(Excel.Application application) { double Version = Convert.ToDouble(application.Version, CultureInfo.InvariantCulture); if (Version >= 12.00) return XlFileFormat.xlOpenXMLWorkbookMacroEnabled; else return XlFileFormat.xlExcel7; }
public void SaveAllData(Excel.Workbook xlWorkbook) { GetConfigFile(xlWorkbook); var VariableListener = ChangeListener.Create(variable.xmlVariable); var AlarmListener = ChangeListener.Create(alarm.xmlAlarms); var StructureListener = ChangeListener.Create(structure.xmlStructure); var DriverListener = ChangeListener.Create(driver.DriverList); var ScaleListner = ChangeListener.Create(scale.xmlScaleElement); VariableListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); AlarmListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); StructureListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); DriverListener.PropertyChanged += new PropertyChangedEventHandler(Listener_PropertyChanged); ScaleListner.PropertyChanged += new PropertyChangedEventHandler( Listener_PropertyChanged); //driver.DriverList.CollectionChanged += new NotifyCollectionChangedEventHandler(OnAddingNew); foreach (Excel.Worksheet xlSheet in xlWorkbook.Sheets) { EditNewData(xlSheet); } Save(); }
public Excel.Range RealUsedRange(Excel.Worksheet xlWorksheet) { long FirstRow; long LastRow; int FirstColumn; int LastColumn; try{ FirstRow = xlWorksheet.Cells.Find("*",xlWorksheet.Range("IV65536"),Excel.Enums.XlFindLookIn.xlValues, Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByRows,Excel.Enums.XlSearchDirection.xlNext).Row; FirstColumn = xlWorksheet.Cells.Find("*", xlWorksheet.Range("IV65536"), Excel.Enums.XlFindLookIn.xlValues, Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByColumns,Excel.Enums.XlSearchDirection.xlNext).Column; LastRow = xlWorksheet.Cells.Find("*", xlWorksheet.Range("A1"), Excel.Enums.XlFindLookIn.xlValues, Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByRows,Excel.Enums.XlSearchDirection.xlPrevious).Row; LastColumn = xlWorksheet.Cells.Find("*", xlWorksheet.Range("A1"), Excel.Enums.XlFindLookIn.xlValues, Excel.Enums.XlLookAt.xlPart,Excel.Enums.XlSearchOrder.xlByColumns,Excel.Enums.XlSearchDirection.xlPrevious).Column; return xlWorksheet.Range(xlWorksheet.Cells[FirstRow, FirstColumn], xlWorksheet.Cells[LastRow, LastColumn]); }catch(Exception e){ throw new Exception("Sheet is empty", e); } }
public List<string> GetHeaders(Excel.Range xlRange) { List<string> headers = new List<string>(); for (int c = 1; c <= xlRange.Columns.Count; c++) { headers.Add(xlRange.Cells[1,c].Value2.ToString()); } this.Headers = headers; return headers; }
public List<string> GetDataTypes(Excel.Range xlRange) { List<string> dataTypes = new List<string>(); for (int c = 1; c <= xlRange.Columns.Count; c++) { dataTypes.Add(xlRange.Cells[2,c].Value2.ToString()); } this.DataTypes = dataTypes; return DataTypes; }
public Dictionary<string, List<Excel.Range>> GetDataFromExcelByHeader(Excel.Worksheet xlSheet) { try{ Excel.Range xlRange = RealUsedRange(xlSheet); NumberOfRows = xlRange.Rows.Count; NumberOfColumns = xlRange.Columns.Count; Dictionary<string, List<Excel.Range>> dictionary = new Dictionary<string, List<Excel.Range>>(); for(int c = 1; c <= NumberOfColumns; c++) { List<Excel.Range> myList =new List<Excel.Range>(); for(int r = 2; r <= NumberOfRows; r++){ myList.Add(xlRange.Cells[r,c]); } dictionary.Add(xlRange.Cells[1,c].Value2.ToString(), myList); } ExcelData = dictionary; return dictionary; }catch(Exception e){ Debug.WriteLine(e); return null; } }
public Dictionary<Row, Dictionary<string, CellInfo>> GetDataFromExcel(Excel.Range xlRange) { try{ Dictionary<Row, Dictionary<string, CellInfo>> dictionary = new Dictionary<Row,Dictionary<string, CellInfo>>(); for(int r = 2; r <= xlRange.Rows.Count; r++) { if (xlRange.Cells[r,1].Value2 != null){ Dictionary<string, CellInfo> myList = new Dictionary<string, CellInfo>(); for(int c = 1; c <= xlRange.Columns.Count; c++){ myList.Add( xlRange.Cells[1,c].Value2.ToString(), new CellInfo { Cell = xlRange.Cells[r,c] }); } dictionary.Add(new Row {Name = xlRange.Cells[r,1].Value2.ToString()}, myList); } } return dictionary; }catch(Exception e){ Debug.WriteLine(e); return null; } }
public List<Config> GetConfig(Excel.Workbook xlWb) { try { List<Config> config = new List<Config>(); Excel.Worksheet xlConfigSheet = (Excel.Worksheet)xlWb.Worksheets["Config"]; Excel.Range xlConfigRange = RealUsedRange(xlConfigSheet); for (int r = 2; r <= xlConfigRange.Rows.Count; r++){ config.Add(new Config {ProjectPath = xlConfigRange.Cells[r,1].Value2.ToString(), ProjectName = xlConfigRange.Cells[r,2].Value2.ToString()}); } return config; } catch (Exception) { throw new Exception("Configuration sheet not found"); } }
private void EditNewData(Excel.Worksheet xlSheet) { bool ExitInnerLoop; UsedRange = xlRead.RealUsedRange(xlSheet); DataFromExcel = xlRead.GetDataFromExcelByHeader(xlSheet); Headers = xlRead.GetHeaders(UsedRange); isChanged = false; var Variables = xlRead.GetCellsData("Tag"); var RemovedVariable = variable.RemoveUnusedItems(Variables, xlSheet.Name); alarm.RemoveUnusedItems(RemovedVariable); structure.RemoveUnusedItems(RemovedVariable); driver.RemoveUnusedItems(RemovedVariable); scale.RemoveUnusedItems(RemovedVariable); if (Variables != null) { for (RowIndex = 0; RowIndex < xlRead.NumberOfRows - 1; RowIndex++) { if(GetValuesByColumn("Tag") != ""){ var AlarmInfo = alarm.GetItemFromList(GetValuesByColumn("Tag")); var VariableInfo = variable.GetVariableFromList(GetValuesByColumn("Tag")); var StructureInfo = structure.GetVariableMemberFromList(GetValuesByColumn("Tag")); var TaskInfo = driver.GetDriverTask(GetValuesByColumn("Tag")); var ScaleElementInfo = scale.GetScaleElementFromList(GetValuesByColumn("Tag")); var ThresholdList = alarm.GetThresholdList(AlarmInfo); ExitInnerLoop = false; foreach (var header in Headers){ stHeader = header; structure.EditInitialValueForMembers(GetValuesByColumn("Tag"), GetValuesByColumn(header), header); switch(header){ case "Tag": if(VariableInfo == null){ ExitInnerLoop = variable.AddVariable(GetValuesByColumn("Tag"), xlSheet.Name, GetValuesByColumn("Name"), GetValuesByColumn("Area")); } if(xlSheet.Name == "AIA" || xlSheet.Name == "DIA"){ ExitInnerLoop = alarm.AddAlarm(GetValuesByColumn("Tag"), xlSheet.Name, GetValuesByColumn("Area"), GetValuesByColumn("Name"), GetValuesByColumn("Delay"), GetValuesByColumn("Condition"), GetValuesByColumn("StationName")); } else if (xlSheet.Name == "ModbusTCPIP" || xlSheet.Name == "S7TCP") { alarm.AddStationAlarm(GetValuesByColumn("Tag"), GetValuesByColumn("Area"), GetValuesByColumn("Name")); } if(xlSheet.Name == "AIA" || xlSheet.Name == "AI"){ if(ScaleElementInfo == null){ scale.AddNormalizer(GetValuesByColumn("Tag"), "-1", GetValuesByColumn("RawMin"), GetValuesByColumn("RawMax"), GetValuesByColumn("MinRange"),GetValuesByColumn("MaxRange"), "1"); } } var ProtoStructMembersName = variable.GetStructurePrototypeNameList(xlSheet.Name); var ProtoStructMembersType = variable.GetStructurePrototypeTypeList(xlSheet.Name); var MembersInitialValues = GetRowData(RowIndex); structure.EnableMemberProperties(GetValuesByColumn("Tag"), ProtoStructMembersName, ProtoStructMembersType, MembersInitialValues, Headers); if( GetValuesByColumn("StationName") != ""){ if (TaskInfo == null) { string DriverName = driver.GetDriverName(GetValuesByColumn("StationName")); string taskVarName = "Tag"; int FunctionCode = 2; int Type = 1; if(xlSheet.Name == "AIA" || xlSheet.Name == "AI"){ taskVarName = GetValuesByColumn("Tag") + ":Field"; if (DriverName == "ModbusTCPIP") { FunctionCode = (int)Enums.ModbusFunctionCode.SingleRegister; } Type = (int)Enums.TaskType.Input; } else if(xlSheet.Name == "DI" || xlSheet.Name == "DIA") { taskVarName = GetValuesByColumn("Tag") + ":IO"; if (DriverName == "ModbusTCPIP") { FunctionCode = (int)Enums.ModbusFunctionCode.SingleRegister; } Type = (int)Enums.TaskType.Input; } else if(xlSheet.Name == "DO") { taskVarName = GetValuesByColumn("Tag") + ":IO"; if (DriverName == "ModbusTCPIP") { FunctionCode = (int)Enums.ModbusFunctionCode.SingleRegister; } Type = (int)Enums.TaskType.UnconditionalOutput; } ExitInnerLoop = driver.AddNewTask(DriverName, GetValuesByColumn("StationName"), GetValuesByColumn("Tag"), taskVarName , GetValuesByColumn("Address"), Type, GetValuesByColumn("Tag") + ":Forced", GetValuesByColumn("UnitID"),FunctionCode); } } break; case "Name": VariableInfo.Name.Description = GetValuesByColumn("Name"); if (xlSheet.Name == "AIA") { alarm.GetThreshold(ThresholdList,"High").Name.Title = GetValuesByColumn("Name"); alarm.GetThreshold(ThresholdList,"HighHigh").Name.Title = GetValuesByColumn("Name"); alarm.GetThreshold(ThresholdList,"Low").Name.Title = GetValuesByColumn("Name"); alarm.GetThreshold(ThresholdList,"LowLow").Name.Title = GetValuesByColumn("Name"); } else if(xlSheet.Name == "DIA") { alarm.GetThreshold(ThresholdList,"Digital").Name.Title = GetValuesByColumn("Name"); } break; case "Area": VariableInfo.Name.Group = GetValuesByColumn("Area") + "." + xlSheet.Name; if(xlSheet.Name == "AIA" || xlSheet.Name == "DIA"){ AlarmInfo.Name.Area = GetValuesByColumn("Area"); } break; case "EU": structure.GetMemberFromList(StructureInfo, "IO").Name.EU = GetValuesByColumn("EU"); break; case "RawMin": ScaleElementInfo.Name.RawMin = GetValuesByColumn("RawMin"); break; case "RawMax": ScaleElementInfo.Name.RawMax = GetValuesByColumn("RawMax"); break; case "MaxRange": ScaleElementInfo.Name.ScaledMax = GetValuesByColumn("MaxRange"); break; case "MinRange": ScaleElementInfo.Name.ScaledMin = GetValuesByColumn("MinRange"); break; case "Delay": if(xlSheet.Name == "AIA"){ alarm.GetThreshold(ThresholdList,"High").Execution.SecDelay = GetValuesByColumn("Delay"); alarm.GetThreshold(ThresholdList,"HighHigh").Execution.SecDelay = GetValuesByColumn("Delay"); alarm.GetThreshold(ThresholdList,"Low").Execution.SecDelay = GetValuesByColumn("Delay"); alarm.GetThreshold(ThresholdList,"LowLow").Execution.SecDelay = GetValuesByColumn("Delay"); } else if(xlSheet.Name == "DIA") { alarm.GetThreshold(ThresholdList,"Digital").Execution.SecDelay = GetValuesByColumn("Delay"); } break; case "UnitID": if( GetValuesByColumn("StationName") != ""){ if(driver.GetDriverName(GetValuesByColumn("StationName")) == "ModbusTCPIP") { TaskInfo.ModbusTCP.UnitID = GetValuesByColumn("UnitID"); } } break; case "StationName": if( GetValuesByColumn("StationName") != ""){ TaskInfo.Name.Station = GetValuesByColumn("StationName"); AlarmInfo.Name.EnableVariable = "Not [" + GetValuesByColumn("Tag") + ":Enable]" + " And " + " Not [" + GetValuesByColumn("Tag") + ":GroupDisable]" + " And Not CBool([" + GetValuesByColumn("StationName") + ":StationState])"; } else { AlarmInfo.Name.EnableVariable = "Not [" + GetValuesByColumn("Tag") + ":Enable]" + " And " + " Not [" + GetValuesByColumn("Tag") + ":GroupDisable]" ; } break; case "Address": if( GetValuesByColumn("StationName") != ""){ switch(driver.GetDriverName(GetValuesByColumn("StationName"))) { case "S7TCP": TaskInfo.DeviceTaskSettings.DeviceAddress = GetValuesByColumn("Address"); break; case "ModbusTCPIP": TaskInfo.ModbusTCP.StartAddress = GetValuesByColumn("Address"); break; } } break; } if(ExitInnerLoop) break; } } } } if (xlSheet.Name == "ModbusTCPIP" || xlSheet.Name == "S7TCP"){ driver.CreateDriverXML(ProjectDirectory + @"\" + "RESOURCES" + @"\" + ProjectFileName.ToUpper() + @"\", xlSheet.Name); variable.AddDriver(xlSheet.Name); for (RowIndex = 0; RowIndex < xlRead.NumberOfRows - 1; RowIndex++) { foreach (var header in Headers){ stHeader = header; switch (header) { case "Tag": if(driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")) == null){ driver.AddNewStation(xlSheet.Name,GetValuesByColumn("Tag"), GetValuesByColumn("ServerAddress"), GetValuesByColumn("BackupServerAddress")); } driver.CheckIfStationsExist(xlSheet.Name,xlRead.GetCellsData("Tag")); break; case "ServerAddress": Debug.WriteLine(driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")).Name); driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")).Server.ServerAddress = GetValuesByColumn("ServerAddress"); break; case "BackupServerAddress": driver.GetStation(xlSheet.Name, GetValuesByColumn("Tag")).Server.BackupServerAddress = GetValuesByColumn("BackupServerAddress"); break; } } } } }
private void GetConfigFile(Excel.Workbook xlBook) { var config = xlRead.GetConfig(xlBook); ProjectDirectory = config[0].ProjectPath; ProjectFileName = config[0].ProjectName; variable.Load(ProjectDirectory + @"\" + ProjectFileName + ".movrealtimedb"); alarm.Load(ProjectDirectory + @"\" + ProjectFileName + ".movalr"); structure.Load(ProjectDirectory + @"\" + ProjectFileName + ".movrtmembers"); scale.Load(ProjectDirectory + @"\" + ProjectFileName + ".movscl"); var driverList = variable.GetDriversList(); driver.Load(ProjectDirectory + @"\" + "RESOURCES" + @"\" + ProjectFileName + @"\", driverList); }
void ExcelApplication_NewWorkbook(Excel.Workbook Wb) { _newWorkbookEvent = true; Wb.Dispose(); }
/// <summary> /// returns the valid file extension for the instance. for example ".xls" or ".xlsx" /// </summary> /// <param name="application">the instance</param> /// <returns>the extension</returns> private static string GetDefaultExtension(Excel.Application application) { double Version = Convert.ToDouble(application.Version, CultureInfo.InvariantCulture); if (Version >= 12.00) return ".xlsm"; else return ".xls"; }
void ExcelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel) { _workbookBeforeCloseEvent = true; Wb.Dispose(); }
private void excelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel) { textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookBeforeClose called." }); Wb.Dispose(); }
void ExcelApplication_WorkbookDeactivate(Excel.Workbook Wb) { _workbookDeactivateEvent = true; Wb.Dispose(); }
void excelApplication_NewWorkbook(Excel.Workbook Wb) { textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event NewWorkbook called." }); Wb.Dispose(); }
public void ClearWorksheet(Excel.Worksheet xlSheet) { Excel.Range UsedRange = RealUsedRange(xlSheet); foreach (Excel.Range xlCell in UsedRange.Cells) { if(xlCell.Comment != null){ xlCell.Comment.Delete(); } xlCell.Interior.ColorIndex = NetOffice.OfficeApi.Enums.XlColorIndex.xlColorIndexNone; } }