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