protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
dynamic result = null;
bool excelFileVisible = false;
string workbookName = string.Empty;
Workbook xlWorkbook = null;
Worksheet xlWorksheet = null;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
if (NeedToClose == true)
{
workBookObject.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Row_Read", LogLevel.Error);
}
else
{
Range xlRows = null;
xlWorkbook = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
xlWorksheet = xlWorkbook.Sheets[workSheetName];
int totalColumns = xlWorksheet.UsedRange.Columns.Count;
int totalRows = xlWorksheet.UsedRange.Rows.Count;
int startingRow = StartingRow.Get(context);
int endingRow = EndingRow.Get(context);
string range = null;
if (0 != startingRow && 0 != endingRow && startingRow == endingRow)
{
Log.Logger.LogData("Starting Row and Ending Row must be different in activity Excel_Row_Read", LogLevel.Error);
}
else
{
if (0 == startingRow && 0 == endingRow)
{
range = 1 + ":" + totalRows;
}
else if (0 != startingRow && 0 == endingRow)
{
range = startingRow + ":" + totalRows;
}
else if (0 == startingRow && 0 != endingRow)
{
range = 1 + ":" + endingRow;
}
else
{
range = startingRow + ":" + endingRow;
}
xlRows = xlWorksheet.Rows[range];
}
result = xlRows.Value;
Value.Set(context, result);
if (NeedToClose == true)
{
workBookObject.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Row_Read", LogLevel.Error);
}
if (!ContinueOnError)
{
context.Abort();
}
}
catch (Exception ex)
{
Value.Set(context, null);
Log.Logger.LogData(ex.Message + " in activity Excel_Row_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
dynamic result = null;
bool excelFileVisible = false;
string workbookName = string.Empty;
Range xlRange = null;
Worksheet xlWorksheet = null;
Range xlRows = null;
DataTable outputDT = null;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
if (NeedToClose == true)
{
workBookObject.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Row_Read", LogLevel.Error);
}
else
{
xlWorksheet = workBookObject.Sheets[workSheetName];
int totalColumns = xlWorksheet.UsedRange.Columns.Count;
int totalRows = xlWorksheet.UsedRange.Rows.Count;
int startingRow = StartingRow.Get(context);
int endingRow = EndingRow.Get(context);
string range = null;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(totalColumns);
string startingCell = null;
string endingCell = null;
if (0 != startingRow && 0 != endingRow && startingRow == endingRow)
{
Log.Logger.LogData("Starting Row and Ending Row must be different in activity Excel_Row_Read", LogLevel.Error);
}
else
{
if (0 == startingRow && 0 == endingRow)
{
// range = 1 + ":" + totalRows;
startingCell = "A1";
endingCell = colLetter + totalRows;
}
else if (0 != startingRow && 0 == endingRow)
{
//range = startingRow + ":" + totalRows;
startingCell = "A" + startingRow;
endingCell = colLetter + totalRows;
}
else if (0 == startingRow && 0 != endingRow)
{
//range = 1 + ":" + endingRow;
startingCell = "A1";
endingCell = colLetter + endingRow;
}
else
{
// range = startingRow + ":" + endingRow;
startingCell = "A" + startingRow;
endingCell = colLetter + endingRow;
}
//xlRows = xlWorksheet.Rows[range];
xlRange = xlWorksheet.Range[startingCell, endingCell];
result = xlRange.Value;
outputDT = ExcelHelper.Shared.Convert2DArraytoDatatable(result);
}
Value.Set(context, outputDT);
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
workBookObject.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Row_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Value.Set(context, null);
Log.Logger.LogData(ex.Message + " in activity Excel_Row_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
ReleaseObject(xlRows);
}
}