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;
}
}
