public override void RunCommand(object sender)
{
var engine = (AutomationEngineInstance)sender;
var vInstance = DateTime.Now.ToString();
var vFilePath = v_FilePath.ConvertUserVariableToString(engine);
var vSheetName = v_SheetName.ConvertUserVariableToString(engine);
var vKeyColumn = v_KeyColumn.ConvertUserVariableToString(engine);
var vValueColumn = v_ValueColumn.ConvertUserVariableToString(engine);
var newExcelSession = new Application {
Visible = false
};
newExcelSession.AddAppInstance(engine, vInstance);
var excelObject = vInstance.GetAppInstance(engine);
var excelInstance = (Application)excelObject;
var excelWorkbook = newExcelSession.Workbooks.Open(vFilePath);
var excelSheet = excelWorkbook.Sheets[vSheetName];
Range last = excelSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
Range cellValue = excelSheet.Range["A1", last];
int rw = cellValue.Rows.Count;
int cl = 2;
int rCnt;
int cCnt;
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 Range).Value2) != null)
{
if (!DT.Columns.Contains(cCnt.ToString()))
{
DT.Columns.Add(cCnt.ToString());
}
newRow[cCnt.ToString()] = ((cellValue.Cells[rCnt, cCnt] as Range).Value2).ToString();
}
}
DT.Rows.Add(newRow);
}
string cKeyName = ((cellValue.Cells[1, 1] as Range).Value2).ToString();
DT.Columns[0].ColumnName = cKeyName;
string cValueName = ((cellValue.Cells[1, 2] as Range).Value2).ToString();
DT.Columns[1].ColumnName = cValueName;
var dictlist = DT.AsEnumerable().Select(x => new
{
keys = (string)x[vKeyColumn],
values = (string)x[vValueColumn]
}).ToList();
Dictionary <string, string> outputDictionary = new Dictionary <string, string>();
foreach (var dict in dictlist)
{
outputDictionary.Add(dict.keys, dict.values);
}
//close excel
excelInstance.Quit();
//remove instance
vInstance.RemoveAppInstance(engine);
outputDictionary.StoreInUserVariable(engine, v_OutputUserVariableName);
}