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