public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var dataSetVariable = LookupVariable(engine); DataTable dataTable = (DataTable)dataSetVariable.VariableValue; var rowIndex = v_DataRowIndex.ConvertToUserVariable(sender); int index = int.Parse(rowIndex); DataRow row = dataTable.Rows[index]; Script.ScriptVariable newDataRow = new Script.ScriptVariable { VariableName = v_UserVariableName, VariableValue = row }; //Overwrites variable if it already exists if (engine.VariableList.Exists(x => x.VariableName == newDataRow.VariableName)) { Script.ScriptVariable temp = engine.VariableList.Where(x => x.VariableName == newDataRow.VariableName).FirstOrDefault(); engine.VariableList.Remove(temp); } engine.VariableList.Add(newDataRow); }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var dictionaryName = v_DictionaryName.ConvertToUserVariable(sender); Dictionary <string, string> outputDictionary = new Dictionary <string, string>(); foreach (DataRow rwColumnName in v_ColumnNameDataTable.Rows) { outputDictionary.Add(rwColumnName.Field <string>("Keys"), rwColumnName.Field <string>("Values")); } //add or override existing variable if (engine.VariableList.Any(f => f.VariableName == dictionaryName)) { var selectedVariable = engine.VariableList.Where(f => f.VariableName == dictionaryName).FirstOrDefault(); selectedVariable.VariableValue = outputDictionary; } else { Script.ScriptVariable newDictionary = new Script.ScriptVariable { VariableName = dictionaryName, VariableValue = outputDictionary }; engine.VariableList.Add(newDictionary); } }
public override void RunCommand(object sender) { //Retrieve Dictionary by name var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var vKey = v_Key.ConvertToUserVariable(sender); var dataSetVariable = LookupVariable(engine); //Declare local dictionary and assign output Dictionary <string, string> dict = (Dictionary <string, string>)dataSetVariable.VariableValue; Script.ScriptVariable Output = new Script.ScriptVariable { VariableName = v_OutputVariable, VariableValue = dict[vKey] }; //Overwrites variable if it already exists if (engine.VariableList.Exists(x => x.VariableName == Output.VariableName)) { Script.ScriptVariable temp = engine.VariableList.Where(x => x.VariableName == Output.VariableName).FirstOrDefault(); engine.VariableList.Remove(temp); } //Add to variable list engine.VariableList.Add(Output); }
public void AddVariable(string variableName, object variableValue) { if (VariableList.Any(f => f.VariableName == variableName)) { //update existing variable var existingVariable = VariableList.FirstOrDefault(f => f.VariableName == variableName); existingVariable.VariableName = variableName; existingVariable.VariableValue = variableValue; } else if (VariableList.Any(f => "{" + f.VariableName + "}" == variableName)) { //update existing edge-case variable due to user error var existingVariable = VariableList.FirstOrDefault(f => "{" + f.VariableName + "}" == variableName); existingVariable.VariableName = variableName; existingVariable.VariableValue = variableValue; } else { //add new variable var newVariable = new Script.ScriptVariable(); newVariable.VariableName = variableName; newVariable.VariableValue = variableValue; VariableList.Add(newVariable); } }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var dataTableName = v_DataTableName.ConvertToUserVariable(sender); DataTable Dt = new DataTable(); foreach (DataRow rwColumnName in v_ColumnNameDataTable.Rows) { Dt.Columns.Add(rwColumnName.Field <string>("Column Name")); } //add or override existing variable if (engine.VariableList.Any(f => f.VariableName == dataTableName)) { var selectedVariable = engine.VariableList.Where(f => f.VariableName == dataTableName).FirstOrDefault(); selectedVariable.VariableValue = Dt; } else { Script.ScriptVariable newDataTable = new Script.ScriptVariable { VariableName = dataTableName, VariableValue = Dt }; engine.VariableList.Add(newDataTable); } }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; //get variable by regular name Script.ScriptVariable listVariable = engine.VariableList.Where(x => x.VariableName == v_ListName).FirstOrDefault(); //user may potentially include brackets [] if (listVariable == null) { listVariable = engine.VariableList.Where(x => x.VariableName.ApplyVariableFormatting() == v_ListName).FirstOrDefault(); } //if still null then throw exception if (listVariable == null) { throw new System.Exception("Complex Variable '" + v_ListName + "' or '" + v_ListName.ApplyVariableFormatting() + "' not found. Ensure the variable exists before attempting to modify it."); } dynamic listToCount; if (listVariable.VariableValue is List <string> ) { listToCount = (List <string>)listVariable.VariableValue; } else if (listVariable.VariableValue is List <MailItem> ) { listToCount = (List <MailItem>)listVariable.VariableValue; } else if (listVariable.VariableValue is List <OpenQA.Selenium.IWebElement> ) { listToCount = (List <OpenQA.Selenium.IWebElement>)listVariable.VariableValue; } else if ((listVariable.VariableValue.ToString().StartsWith("[")) && (listVariable.VariableValue.ToString().EndsWith("]")) && (listVariable.VariableValue.ToString().Contains(","))) { //automatically handle if user has given a json array Newtonsoft.Json.Linq.JArray jsonArray = Newtonsoft.Json.JsonConvert.DeserializeObject(listVariable.VariableValue.ToString()) as Newtonsoft.Json.Linq.JArray; var itemList = new List <string>(); foreach (var item in jsonArray) { var value = (Newtonsoft.Json.Linq.JValue)item; itemList.Add(value.ToString()); } listVariable.VariableValue = itemList; listToCount = itemList; } else { throw new System.Exception("Complex Variable List Type<T> Not Supported"); } string count = listToCount.Count.ToString(); count.StoreInUserVariable(sender, v_UserVariableName); }
public override void RunCommand(object sender, Core.Script.ScriptAction parentCommand) { Core.Automation.Commands.BeginNumberOfTimesLoopCommand loopCommand = (Core.Automation.Commands.BeginNumberOfTimesLoopCommand)parentCommand.ScriptCommand; var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; if (!engine.VariableList.Any(f => f.VariableName == "Loop.CurrentIndex")) { engine.VariableList.Add(new Script.ScriptVariable() { VariableName = "Loop.CurrentIndex", VariableValue = "0" }); } int loopTimes; Script.ScriptVariable complexVarible = null; var loopParameter = loopCommand.v_LoopParameter.ConvertToUserVariable(sender); loopTimes = int.Parse(loopParameter); for (int i = 0; i < loopTimes; i++) { if (complexVarible != null) { complexVarible.CurrentPosition = i; } (i + 1).ToString().StoreInUserVariable(engine, "Loop.CurrentIndex"); engine.ReportProgress("Starting Loop Number " + (i + 1) + "/" + loopTimes + " From Line " + loopCommand.LineNumber); foreach (var cmd in parentCommand.AdditionalScriptCommands) { if (engine.IsCancellationPending) { return; } engine.ExecuteCommand(cmd); if (engine.CurrentLoopCancelled) { engine.ReportProgress("Exiting Loop From Line " + loopCommand.LineNumber); engine.CurrentLoopCancelled = false; return; } } engine.ReportProgress("Finished Loop From Line " + loopCommand.LineNumber); } }
public override void RunCommand(object sender) { DatasetCommands dataSetCommand = new DatasetCommands(); DataTable requiredData = dataSetCommand.CreateDataTable(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + v_FilePath + @";Extended Properties=""Excel 12.0;HDR=No;IMEX=1""", "Select * From [" + v_SheetName + "$]"); var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; Script.ScriptVariable newDataset = new Script.ScriptVariable { VariableName = v_DataSetName, VariableValue = requiredData }; engine.VariableList.Add(newDataset); }
public override void RunCommand(object sender) { DatasetCommands dataSetCommand = new DatasetCommands(); DataTable requiredData = dataSetCommand.CreateDataTable(v_ConnectionString.ConvertToUserVariable(sender), v_UserQuery); var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; Script.ScriptVariable newDataset = new Script.ScriptVariable { VariableName = v_DataSetName, VariableValue = requiredData }; engine.VariableList.Add(newDataset); }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var vInstance = DateTime.Now.ToString(); var vFilePath = v_FilePath.ConvertToUserVariable(sender); var newExcelSession = new Microsoft.Office.Interop.Excel.Application { Visible = false }; engine.AddAppInstance(vInstance, newExcelSession); var excelObject = engine.GetAppInstance(vInstance); Microsoft.Office.Interop.Excel.Application excelInstance = (Microsoft.Office.Interop.Excel.Application)excelObject; //Query required from workbook using OLEDB DatasetCommands dataSetCommand = new DatasetCommands(); DataTable requiredData = dataSetCommand.CreateDataTable(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + v_FilePath + @";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""", "Select " + v_KeyColumn + "," + v_ValueColumn + " From [" + v_SheetName + "$]"); var dictlist = requiredData.AsEnumerable().Select(x => new { keys = (string)x[v_KeyColumn], values = (string)x[v_ValueColumn] }).ToList(); Dictionary <string, string> outputDictionary = new Dictionary <string, string>(); foreach (var dict in dictlist) { outputDictionary.Add(dict.keys, dict.values); } Script.ScriptVariable newDictionary = new Script.ScriptVariable { VariableName = v_DictionaryName, VariableValue = outputDictionary }; //close excel excelInstance.Quit(); //remove instance engine.RemoveAppInstance(vInstance); engine.VariableList.Add(newDictionary); }
public override void RunCommand(object sender) { //Retrieve Dictionary by name var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var dataSetVariable = LookupVariable(engine); //Declare local dictionary and assign output Dictionary <string, string> dict = (Dictionary <string, string>)dataSetVariable.VariableValue; Script.ScriptVariable Output = new Script.ScriptVariable { VariableName = v_OutputVariable, VariableValue = dict[v_Key] }; //Add to variable list engine.VariableList.Add(Output); }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var vInputData = v_InputData.ConvertToUserVariable(engine); string vRegex = v_Regex.ConvertToUserVariable(engine); Match[] matches = Regex.Matches(vInputData, vRegex) .Cast <Match>() .ToArray(); var arr = string.Join(",", matches.AsEnumerable()); Script.ScriptVariable outputMatches = new Script.ScriptVariable { VariableName = v_OutputVariable, VariableValue = arr }; engine.VariableList.Add(outputMatches); }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; //apply variable logic var sourceFolder = v_SourceFolderPath.ConvertToUserVariable(sender); //delete folder //System.IO.Directory.Delete(sourceFolder, true); var filesList = System.IO.Directory.GetFiles(sourceFolder).ToList(); Script.ScriptVariable newFilesList = new Script.ScriptVariable { VariableName = v_UserVariableName, VariableValue = filesList }; //Overwrites variable if it already exists if (engine.VariableList.Exists(x => x.VariableName == newFilesList.VariableName)) { Script.ScriptVariable temp = engine.VariableList.Where(x => x.VariableName == newFilesList.VariableName).FirstOrDefault(); engine.VariableList.Remove(temp); } engine.VariableList.Add(newFilesList); }
public override void RunCommand(object sender) { var engine = (Engine.AutomationEngineInstance)sender; var vFolder = v_Folder.ConvertToUserVariable(sender); var vFilter = v_Filter.ConvertToUserVariable(sender); var vAttachmentDirectory = v_AttachmentDirectory.ConvertToUserVariable(sender); var vMessageDirectory = v_MessageDirectory.ConvertToUserVariable(sender); if (vFolder == "") { vFolder = "Inbox"; } Application outlookApp = new Application(); AddressEntry currentUser = outlookApp.Session.CurrentUser.AddressEntry; NameSpace test = outlookApp.GetNamespace("MAPI"); if (currentUser.Type == "EX") { MAPIFolder inboxFolder = test.GetDefaultFolder(OlDefaultFolders.olFolderInbox).Parent; MAPIFolder userFolder = inboxFolder.Folders[vFolder]; Items filteredItems = null; if (vFilter != "") { filteredItems = userFolder.Items.Restrict(vFilter); } else { filteredItems = userFolder.Items; } List <MailItem> outMail = new List <MailItem>(); foreach (object _obj in filteredItems) { if (_obj is MailItem) { MailItem tempMail = (MailItem)_obj; if (v_GetUnreadOnly == "Yes") { if (tempMail.UnRead == true) { ProcessEmail(tempMail, vMessageDirectory, vAttachmentDirectory); outMail.Add(tempMail); } } else { ProcessEmail(tempMail, vMessageDirectory, vAttachmentDirectory); outMail.Add(tempMail); } } } //add list of datatables to output variable Script.ScriptVariable mailItemList = new Script.ScriptVariable { VariableName = v_userVariableName, VariableValue = outMail }; engine.VariableList.Add(mailItemList); } }
public override void RunCommand(object sender, Core.Script.ScriptAction parentCommand) { Core.Automation.Commands.BeginListLoopCommand loopCommand = (Core.Automation.Commands.BeginListLoopCommand)parentCommand.ScriptCommand; var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; int loopTimes; Script.ScriptVariable complexVariable = null; //get variable by regular name complexVariable = engine.VariableList.Where(x => x.VariableName == v_LoopParameter).FirstOrDefault(); if (!engine.VariableList.Any(f => f.VariableName == "Loop.CurrentIndex")) { engine.VariableList.Add(new Script.ScriptVariable() { VariableName = "Loop.CurrentIndex", VariableValue = "0" }); } //user may potentially include brackets [] if (complexVariable == null) { complexVariable = engine.VariableList.Where(x => x.VariableName.ApplyVariableFormatting() == v_LoopParameter).FirstOrDefault(); } //if still null then throw exception if (complexVariable == null) { throw new Exception("Complex Variable '" + v_LoopParameter + "' or '" + v_LoopParameter.ApplyVariableFormatting() + "' not found. Ensure the variable exists before attempting to modify it."); } dynamic listToLoop; if (complexVariable.VariableValue is List <string> ) { listToLoop = (List <string>)complexVariable.VariableValue; } else if (complexVariable.VariableValue is List <OpenQA.Selenium.IWebElement> ) { listToLoop = (List <OpenQA.Selenium.IWebElement>)complexVariable.VariableValue; } else if (complexVariable.VariableValue is DataTable) { listToLoop = ((DataTable)complexVariable.VariableValue).Rows; } else if ((complexVariable.VariableValue.ToString().StartsWith("[")) && (complexVariable.VariableValue.ToString().EndsWith("]")) && (complexVariable.VariableValue.ToString().Contains(","))) { //automatically handle if user has given a json array Newtonsoft.Json.Linq.JArray jsonArray = Newtonsoft.Json.JsonConvert.DeserializeObject(complexVariable.VariableValue.ToString()) as Newtonsoft.Json.Linq.JArray; var itemList = new List <string>(); foreach (var item in jsonArray) { var value = (Newtonsoft.Json.Linq.JValue)item; itemList.Add(value.ToString()); } complexVariable.VariableValue = itemList; listToLoop = itemList; } else { throw new Exception("Complex Variable List Type<T> Not Supported"); } loopTimes = listToLoop.Count; for (int i = 0; i < loopTimes; i++) { if (complexVariable != null) { complexVariable.CurrentPosition = i; } engine.ReportProgress("Starting Loop Number " + (i + 1) + "/" + loopTimes + " From Line " + loopCommand.LineNumber); foreach (var cmd in parentCommand.AdditionalScriptCommands) { if (engine.IsCancellationPending) { return; } (i + 1).ToString().StoreInUserVariable(engine, "Loop.CurrentIndex"); engine.ExecuteCommand(cmd); if (engine.CurrentLoopCancelled) { engine.ReportProgress("Exiting Loop From Line " + loopCommand.LineNumber); engine.CurrentLoopCancelled = false; return; } if (engine.CurrentLoopContinuing) { engine.ReportProgress("Continuing Next Loop From Line " + loopCommand.LineNumber); engine.CurrentLoopContinuing = false; break; } } engine.ReportProgress("Finished Loop From Line " + loopCommand.LineNumber); } }
public override void RunCommand(object sender) { Core.Automation.Engine.AutomationEngineInstance currentScriptEngine = (Core.Automation.Engine.AutomationEngineInstance)sender; var startFile = v_taskPath.ConvertToUserVariable(sender); //create variable list var variableList = new List <Script.ScriptVariable>(); var variableReturnList = new List <Script.ScriptVariable>(); foreach (DataRow rw in v_VariableAssignments.Rows) { var variableName = (string)rw.ItemArray[0]; object variableValue; try { variableValue = LookupVariable(currentScriptEngine, (string)rw.ItemArray[1]).VariableValue; } catch (Exception ex) { variableValue = ((string)rw.ItemArray[1]).ConvertToUserVariable(sender); } var variableReturn = (string)rw.ItemArray[2]; variableList.Add(new Script.ScriptVariable { VariableName = variableName, VariableValue = variableValue }); if (variableReturn == "Yes") { variableReturnList.Add(new Script.ScriptVariable { VariableName = variableName, VariableValue = variableValue }); } } UI.Forms.frmScriptEngine newEngine = new UI.Forms.frmScriptEngine(startFile, null, variableList, true); //Core.Automation.Engine.AutomationEngineInstance currentScriptEngine = (Core.Automation.Engine.AutomationEngineInstance) sender; currentScriptEngine.tasktEngineUI.Invoke((Action) delegate() { currentScriptEngine.tasktEngineUI.TopMost = false; }); Application.Run(newEngine); //get new variable list from the new task engine after it finishes running var newVariableList = newEngine.engineInstance.VariableList; foreach (var variable in variableReturnList) { //check if the variables we wish to return are in the new variable list if (newVariableList.Exists(x => x.VariableName == variable.VariableName)) { //if yes, get that variable from the new list Script.ScriptVariable newTemp = newVariableList.Where(x => x.VariableName == variable.VariableName).FirstOrDefault(); //check if that variable previously existed in the current engine if (currentScriptEngine.VariableList.Exists(x => x.VariableName == newTemp.VariableName)) { //if yes, overwrite it Script.ScriptVariable currentTemp = currentScriptEngine.VariableList.Where(x => x.VariableName == newTemp.VariableName).FirstOrDefault(); currentScriptEngine.VariableList.Remove(currentTemp); } //Add to current engine variable list currentScriptEngine.VariableList.Add(newTemp); } } //currentScriptEngine.tasktEngineUI.TopMost = false; currentScriptEngine.tasktEngineUI.Invoke((Action) delegate() { currentScriptEngine.tasktEngineUI.TopMost = true; }); }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var dataSetVariable = LookupVariable(engine); var vSearchItem = v_SearchItem.ConvertToUserVariable(engine); DataTable Dt = new DataTable(); Dt = (DataTable)dataSetVariable.VariableValue; var t = new List <Tuple <string, string> >(); var listPairs = vSearchItem.Split('}'); int i = 0; listPairs = listPairs.Take(listPairs.Count() - 1).ToArray(); foreach (string item in listPairs) { string temp; temp = item.Trim('{'); var tempList = temp.Split(','); t.Insert(i, Tuple.Create(tempList[0], tempList[1])); i++; } List <DataRow> listrows = Dt.AsEnumerable().ToList(); List <DataRow> templist = new List <DataRow>(); foreach (Tuple <string, string> tuple in t) { foreach (DataRow item in listrows) { if (item[tuple.Item1] != null) { if (item[tuple.Item1].ToString() == tuple.Item2.ToString()) { templist.Add(item); } } } } DataTable outputDT = new DataTable(); int x = 0; foreach (DataColumn column in Dt.Columns) { outputDT.Columns.Add(Dt.Columns[x].ToString()); x++; } foreach (DataRow item in templist) { outputDT.Rows.Add(item.ItemArray); } Dt.AcceptChanges(); Script.ScriptVariable newDatatable = new Script.ScriptVariable { VariableName = v_OutputDTName, VariableValue = outputDT }; //Overwrites variable if it already exists if (engine.VariableList.Exists(y => y.VariableName == newDatatable.VariableName)) { Script.ScriptVariable temp = engine.VariableList.Where(y => y.VariableName == newDatatable.VariableName).FirstOrDefault(); engine.VariableList.Remove(temp); } engine.VariableList.Add(newDatatable); dataSetVariable.VariableValue = Dt; }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var vInstance = v_InstanceName.ConvertToUserVariable(engine); var excelObject = engine.GetAppInstance(vInstance); var targetAddress1 = v_ExcelCellAddress1.ConvertToUserVariable(sender); var targetAddress2 = v_ExcelCellAddress2.ConvertToUserVariable(sender); Microsoft.Office.Interop.Excel.Application excelInstance = (Microsoft.Office.Interop.Excel.Application)excelObject; Microsoft.Office.Interop.Excel.Worksheet excelSheet = excelInstance.ActiveSheet; var cellValue = excelSheet.Range[targetAddress1, targetAddress2]; if (v_Output == "Datatable") { List <object> lst = new List <object>(); int rw = cellValue.Rows.Count; int cl = cellValue.Columns.Count; int rCnt; int cCnt; string str; DataTable DT = new DataTable(); for (rCnt = 1; rCnt <= rw; rCnt++) { DataRow newRow = DT.NewRow(); for (cCnt = 1; cCnt <= cl; cCnt++) { if (((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) != null) { if (!DT.Columns.Contains(cCnt.ToString())) { DT.Columns.Add(cCnt.ToString()); } newRow[cCnt.ToString()] = ((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); } } DT.Rows.Add(newRow); } Script.ScriptVariable newDataset = new Script.ScriptVariable { VariableName = v_userVariableName, VariableValue = DT }; engine.VariableList.Add(newDataset); } if (v_Output == "Delimited String") { List <object> lst = new List <object>(); int rw = cellValue.Rows.Count; int cl = cellValue.Columns.Count; int rCnt; int cCnt; string str; //Get Range from Excel sheet and add to list of strings. for (rCnt = 1; rCnt <= rw; rCnt++) { for (cCnt = 1; cCnt <= cl; cCnt++) { if (((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) != null) { str = ((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); lst.Add(str); } } } string output = String.Join(",", lst); v_ExcelCellAddress1 = output; //Store Strings of comma seperated values into user variable output.StoreInUserVariable(sender, v_userVariableName); } }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var vInstance = v_InstanceName.ConvertToUserVariable(engine); var excelObject = engine.GetAppInstance(vInstance); var targetAddress1 = v_ExcelCellAddress1.ConvertToUserVariable(sender); var targetAddress2 = v_ExcelCellAddress2.ConvertToUserVariable(sender); Microsoft.Office.Interop.Excel.Application excelInstance = (Microsoft.Office.Interop.Excel.Application)excelObject; Microsoft.Office.Interop.Excel.Worksheet excelSheet = excelInstance.ActiveSheet; Microsoft.Office.Interop.Excel.Range cellValue; if (targetAddress2 != "") { cellValue = excelSheet.Range[targetAddress1, targetAddress2]; } else { Microsoft.Office.Interop.Excel.Range last = excelSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); cellValue = excelSheet.Range[targetAddress1, last]; } if (v_Output == "Datatable") { List <object> lst = new List <object>(); int rw = cellValue.Rows.Count; int cl = cellValue.Columns.Count; int rCnt; int cCnt; string cName; DataTable DT = new DataTable(); for (rCnt = 2; rCnt <= rw; rCnt++) { DataRow newRow = DT.NewRow(); for (cCnt = 1; cCnt <= cl; cCnt++) { if (((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) != null) { if (!DT.Columns.Contains(cCnt.ToString())) { DT.Columns.Add(cCnt.ToString()); } newRow[cCnt.ToString()] = ((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); } } DT.Rows.Add(newRow); } if (v_AddHeaders == "Yes") { //Set column names for (cCnt = 1; cCnt <= cl; cCnt++) { cName = ((cellValue.Cells[1, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); DT.Columns[cCnt - 1].ColumnName = cName; } } Script.ScriptVariable newDataset = new Script.ScriptVariable { VariableName = v_userVariableName, VariableValue = DT }; //Overwrites variable if it already exists if (engine.VariableList.Exists(x => x.VariableName == newDataset.VariableName)) { Script.ScriptVariable temp = engine.VariableList.Where(x => x.VariableName == newDataset.VariableName).FirstOrDefault(); engine.VariableList.Remove(temp); } engine.VariableList.Add(newDataset); } if (v_Output == "Delimited String") { List <object> lst = new List <object>(); int rw = cellValue.Rows.Count; int cl = cellValue.Columns.Count; int rCnt; int cCnt; string str; //Get Range from Excel sheet and add to list of strings. for (rCnt = 1; rCnt <= rw; rCnt++) { for (cCnt = 1; cCnt <= cl; cCnt++) { if (((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) != null) { str = ((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); lst.Add(str); } } } string output = String.Join(",", lst); //Store Strings of comma seperated values into user variable output.StoreInUserVariable(sender, v_userVariableName); } }
public override void RunCommand(object sender) { var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; var vInstance = v_InstanceName.ConvertToUserVariable(engine); var vExcelObject = engine.GetAppInstance(vInstance); var vTargetAddress1 = v_ExcelCellAddress1.ConvertToUserVariable(sender); var vTargetAddress2 = v_ExcelCellAddress2.ConvertToUserVariable(sender); var vColumnName = v_ColumnName.ConvertToUserVariable(sender); var vOutputDirectory = v_OutputDirectory.ConvertToUserVariable(sender); Microsoft.Office.Interop.Excel.Application excelInstance = (Microsoft.Office.Interop.Excel.Application)vExcelObject; excelInstance.Visible = false; excelInstance.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Worksheet excelSheet = excelInstance.ActiveSheet; Microsoft.Office.Interop.Excel.Range cellValue; if (vTargetAddress2 != "") { cellValue = excelSheet.Range[vTargetAddress1, vTargetAddress2]; } else { Microsoft.Office.Interop.Excel.Range last = excelSheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing); cellValue = excelSheet.Range[vTargetAddress1, last]; } //Convert Range to DataTable List <object> lst = new List <object>(); int rw = cellValue.Rows.Count; int cl = cellValue.Columns.Count; int rCnt; int cCnt; string cName; DataTable DT = new DataTable(); //start from row 2 for (rCnt = 2; rCnt <= rw; rCnt++) { DataRow newRow = DT.NewRow(); for (cCnt = 1; cCnt <= cl; cCnt++) { if (((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) != null) { if (!DT.Columns.Contains(cCnt.ToString())) { DT.Columns.Add(cCnt.ToString()); } newRow[cCnt.ToString()] = ((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); } else if (((cellValue.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) == null && ((cellValue.Cells[1, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2) != null) { if (!DT.Columns.Contains(cCnt.ToString())) { DT.Columns.Add(cCnt.ToString()); } newRow[cCnt.ToString()] = string.Empty; } } DT.Rows.Add(newRow); } //Set column names for (cCnt = 1; cCnt <= cl; cCnt++) { cName = ((cellValue.Cells[1, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2).ToString(); DT.Columns[cCnt - 1].ColumnName = cName; } //split table by column List <DataTable> result = DT.AsEnumerable() .GroupBy(row => row.Field <string>(vColumnName)) .Select(g => g.CopyToDataTable()) .ToList(); //add list of datatables to output variable Script.ScriptVariable splitDataset = new Script.ScriptVariable { VariableName = v_userVariableName, VariableValue = result }; engine.VariableList.Add(splitDataset); //save split datatables in individual workbooks labeled by selected column data if (Directory.Exists(vOutputDirectory)) { string newName; foreach (DataTable newDT in result) { try { newName = newDT.Rows[0].Field <string>(vColumnName).ToString(); } catch (Exception ex) { continue; } Console.WriteLine(newName); Microsoft.Office.Interop.Excel.Workbook newWorkBook = excelInstance.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet newSheet = newWorkBook.ActiveSheet; for (int i = 1; i < newDT.Columns.Count + 1; i++) { newSheet.Cells[1, i] = newDT.Columns[i - 1].ColumnName; } for (int j = 0; j < newDT.Rows.Count; j++) { for (int k = 0; k < newDT.Columns.Count; k++) { newSheet.Cells[j + 2, k + 1] = newDT.Rows[j].ItemArray[k].ToString(); } } if (newName.Contains(".")) { newName = newName.Replace(".", string.Empty); } if (v_FileType == "csv" && !newName.Equals(string.Empty)) { newWorkBook.SaveAs(Path.Combine(vOutputDirectory, newName), Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else if (!newName.Equals(string.Empty)) { newWorkBook.SaveAs(Path.Combine(vOutputDirectory, newName + ".xlsx")); } } } }
public virtual void ScriptFinished(ScriptFinishedEventArgs.ScriptFinishedResult result, string error = null) { engineLogger.Information("Result Code: " + result.ToString()); //add result variable if missing var resultVar = VariableList.Where(f => f.VariableName == "taskt.Result").FirstOrDefault(); //handle if variable is missing if (resultVar == null) { resultVar = new Script.ScriptVariable() { VariableName = "taskt.Result", VariableValue = "" }; } //check value var resultValue = resultVar.VariableValue.ToString(); if (error == null) { engineLogger.Information("Error: None"); if (taskModel != null && serverSettings.ServerConnectionEnabled) { HttpServerClient.UpdateTask(taskModel.TaskID, "Completed", "Script Completed Successfully"); } if (string.IsNullOrEmpty(resultValue)) { TasktResult = "Successfully Completed Script"; } else { TasktResult = resultValue; } } else { engineLogger.Information("Error: " + error); if (taskModel != null) { HttpServerClient.UpdateTask(taskModel.TaskID, "Error", error); } TasktResult = error; } engineLogger.Dispose(); CurrentStatus = EngineStatus.Finished; ScriptFinishedEventArgs args = new ScriptFinishedEventArgs(); args.LoggedOn = DateTime.Now; args.Result = result; args.Error = error; args.ExecutionTime = sw.Elapsed; args.FileName = FileName; Core.Server.SocketClient.SendExecutionLog("Result Code: " + result.ToString()); Core.Server.SocketClient.SendExecutionLog("Total Execution Time: " + sw.Elapsed); //convert to json var serializedArguments = Newtonsoft.Json.JsonConvert.SerializeObject(args); //write execution metrics if ((engineSettings.TrackExecutionMetrics) && (FileName != null)) { var summaryLogger = new Logging().CreateJsonLogger("Execution Summary", Serilog.RollingInterval.Infinite); summaryLogger.Information(serializedArguments); summaryLogger.Dispose(); } Core.Client.EngineBusy = false; if (serverSettings.ServerConnectionEnabled) { HttpServerClient.CheckIn(); } ScriptFinishedEvent?.Invoke(this, args); }
public override void RunCommand(object sender, Core.Script.ScriptAction parentCommand) { Core.Automation.Commands.BeginListLoopCommand loopCommand = (Core.Automation.Commands.BeginListLoopCommand)parentCommand.ScriptCommand; var engine = (Core.Automation.Engine.AutomationEngineInstance)sender; int loopTimes; Script.ScriptVariable complexVariable = null; //get variable by regular name complexVariable = engine.VariableList.Where(x => x.VariableName == v_LoopParameter).FirstOrDefault(); if (!engine.VariableList.Any(f => f.VariableName == "Loop.CurrentIndex")) { engine.VariableList.Add(new Script.ScriptVariable() { VariableName = "Loop.CurrentIndex", VariableValue = "0" }); } //user may potentially include brackets [] if (complexVariable == null) { complexVariable = engine.VariableList.Where(x => x.VariableName.ApplyVariableFormatting() == v_LoopParameter).FirstOrDefault(); } //if still null then throw exception if (complexVariable == null) { throw new Exception("Complex Variable '" + v_LoopParameter + "' or '" + v_LoopParameter.ApplyVariableFormatting() + "' not found. Ensure the variable exists before attempting to modify it."); } dynamic listToLoop; if (complexVariable.VariableValue is List <string> ) { listToLoop = (List <string>)complexVariable.VariableValue; } else if (complexVariable.VariableValue is List <OpenQA.Selenium.IWebElement> ) { listToLoop = (List <OpenQA.Selenium.IWebElement>)complexVariable.VariableValue; } else { throw new Exception("Complex Variable List Type<T> Not Supported"); } loopTimes = listToLoop.Count; for (int i = 0; i < loopTimes; i++) { if (complexVariable != null) { complexVariable.CurrentPosition = i; } (i + 1).ToString().StoreInUserVariable(engine, "Loop.CurrentIndex"); engine.ReportProgress("Starting Loop Number " + (i + 1) + "/" + loopTimes + " From Line " + loopCommand.LineNumber); foreach (var cmd in parentCommand.AdditionalScriptCommands) { if (engine.IsCancellationPending) { return; } engine.ExecuteCommand(cmd); if (engine.CurrentLoopCancelled) { return; } } engine.ReportProgress("Finished Loop From Line " + loopCommand.LineNumber); } }