protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = WorkbookFullName.Get(context);
string workbookName = string.Empty;
ExcelHelper.Shared.GetApp().DisplayAlerts = false;
// bool needToOpen = NeedToOpen.Get(context);
if (File.Exists(workbookFullName))
{
workbookName = Path.GetFileName(workbookFullName);
}
else
{
Log.Logger.LogData("Workbook file do not exist, Error in activity CellValue_Copy", LogLevel.Error);
context.Abort();
}
if (NeedToOpen == true)
{
ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
}
string worksheetName = WorksheetName.Get(context);
string cell = Cell.Get(context);
ExcelHelper.Shared.GetRange(workbookName, worksheetName, cell).Copy();
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity CellValue_Copy", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
object misValue = System.Reflection.Missing.Value;
string workbookFullName = WorkbookFullName.Get(context);
string workbookName = string.Empty;
//bool needToSave = NeedToSave.Get(context);
//bool needToClose = NeedToClose.Get(context);
ExcelHelper.Shared.GetApp().DisplayAlerts = false;
if (File.Exists(workbookFullName))
{
workbookName = Path.GetFileName(workbookFullName);
}
else
{
Log.Logger.LogData("Workbook file do not exist, Error in activity CellValue_Paste", LogLevel.Error);
context.Abort();
}
string worksheetName = WorksheetName.Get(context);
string cell = Cell.Get(context);
ExcelHelper.Shared.GetRange(workbookName, worksheetName, cell).PasteSpecial(PasteType);
//if (PasteValuesOnly)
//{
// ExcelHelper.Shared.GetRange(WorkbookName, WorksheetName, Cell).PasteSpecial(XlPasteType.xlPasteValuesAndNumberFormats);
//}
//else
//{
// ExcelHelper.Shared.GetRange(WorkbookName, WorksheetName, Cell).PasteSpecial();
//}
if (NeedToSave == true)
{
ExcelHelper.Shared.GetWorkbookByName(workbookName, true).Save();
}
if (NeedToClose == true)
{
ExcelHelper.Shared.GetWorkbookByName(workbookName, true).Close();
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Execute", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = FilePath.Get(context);
string worksheetName = WorksheetName.Get(context);
if (!workbookFullName.Contains("."))
{
workbookFullName = workbookFullName + ".xlsx";
}
if (!File.Exists(workbookFullName))
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Worksheet_Exists", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
else
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
string workbookName = Path.GetFileName(workbookFullName);
dynamic workBookObject = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
dynamic worksheets = workBookObject.Worksheets;
bool value = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
Exists.Set(context, value);
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Worksheet_Exists", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
//Need to download and install https://www.microsoft.com/en-us/download/confirmation.aspx?id=23734
protected override void Execute(NativeActivityContext context)
{
string workSheetName = WorksheetName.Get(context);
string strFilePath = FilePath.Get(context);
//string strFilePath = FilePath;
string workbookName = FilePath.Get(context);
// string workbookName = FilePath;
// bool bIsHeader = IsHeader.Get(context);
ReadExcelData(workSheetName, strFilePath, workbookName, IsHeader, context);
}
protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
int NoRowsCounts = NoRows.Get(context);
int PostionsCounts = Position.Get(context);
string workbookName = string.Empty;
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp().DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
Workbook workBookObject = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, workSheetName, false) != null;
if (false == sheetExist)
{
workBookObject.Close();
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("\"" + workSheetName + "\" worksheet does not exist in activity Excel_Insert_Delete_Rows", LogLevel.Error);
}
else
{
Worksheet xlWorksheet = workBookObject.Sheets[workSheetName];
if (mode == Mode.Add)
{
for (int i = 0; i < NoRowsCounts; i++)
{
xlWorksheet.Rows[PostionsCounts].Insert(XlInsertShiftDirection.xlShiftDown);
}
}
else if (mode == Mode.Delete)
{
for (int i = 0; i < NoRowsCounts; i++)
{
xlWorksheet.Rows[PostionsCounts].Delete(XlDeleteShiftDirection.xlShiftUp);
}
}
workBookObject.Save();
workBookObject.Close();
ExcelHelper.Shared.Dispose();
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Insert_Delete_Rows", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Insert_Delete_Rows", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
Range xlRange = null;
bool excelFileVisible = false;
object[,] TwoDimensionalArray = null;
DataTable inputDt = null;
try
{
string workbookName = string.Empty;
// Workbook xlWorkbook = null;
Worksheet xlWorksheet = null;
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)
{
Status.Set(context, false);
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_Write", LogLevel.Error);
}
else
{
xlWorksheet = workBookObject.Sheets[workSheetName];
inputDt = Value.Get(context);
int totalRows = inputDt.Rows.Count;
int totalColumns = inputDt.Columns.Count;
string startingCell = StartingCell.Get(context);
if (null == startingCell)
{
startingCell = "A1";
}
String endingNum = startingCell.Substring(1);
int rangeEnd = totalRows - 1 + int.Parse(endingNum);
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(totalColumns);
string endingCell = colLetter + rangeEnd;
TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(inputDt);
if (null == inputDt)
{
Status.Set(context, false);
}
else
{
xlRange = xlWorksheet.Range[startingCell, endingCell];
xlRange.Value = TwoDimensionalArray;
workBookObject.Save();
Status.Set(context, true);
}
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
{
Status.Set(context, false);
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Row_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Status.Set(context, false);
Log.Logger.LogData(ex.Message + " in activity Excel_Row_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
ExcelObject.Range xlRange = null;
dynamic workBookObject = null;
DataTable inputDt = null;
try
{
string workbookName = string.Empty;
ExcelObject._Worksheet xlWorksheet = null;
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp().DisplayAlerts = false;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
workBookObject = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
Result.Set(context, false);
workBookObject.Close();
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_Range_Write", LogLevel.Error);
}
else
{
inputDt = Value.Get(context);
xlWorksheet = workBookObject.Sheets[workSheetName];
string startingCell = StartingCell.Get(context);
if (startingCell == null)
{
startingCell = "A1";
}
int totalRows = inputDt.Rows.Count;
int totalColumns = inputDt.Columns.Count;
//String endAlpa = startingCell.Substring(0);
String endAlpa = new String(startingCell.Where(c => Char.IsLetter(c)).ToArray()).ToUpper();
int rangeEndColNum = ExcelHelper.Shared.NumberFromExcelColumn(endAlpa) - 1 + totalColumns;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(rangeEndColNum);
String endNum = new String(startingCell.Where(c => Char.IsDigit(c)).ToArray());
int rangeEndNum = int.Parse(endNum) - 1 + totalRows;
string endingCell = colLetter + rangeEndNum;
if (null == inputDt)
{
Result.Set(context, false);
}
else
{
object[,] TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(inputDt);
xlRange = xlWorksheet.Range[startingCell, endingCell];
xlRange.Value = TwoDimensionalArray;
Result.Set(context, true);
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
workBookObject.Save();
}
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Result.Set(context, false);
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Range_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Result.Set(context, false);
Log.Logger.LogData(ex.Message + " in activity Excel_Range_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
ReleaseObject(xlRange);
//if (NeedToClose == true)
//{
// ReleaseObject(workBookObject);
//}
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
bool excelFileVisible = false;
string cutCopyModeFilePath = CutCopyModeFilePath.Get(context);
if (File.Exists(cutCopyModeFilePath))
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
ExcelHelper.Shared.Close_OpenedFile(cutCopyModeFilePath);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
string workbookNamecutCopyMode = string.Empty;
workbookNamecutCopyMode = Path.GetFileName(cutCopyModeFilePath);
string worksheetNamecutCopyMode = CutCopyModeWorksheetName.Get(context);
dynamic workBookObjectcutCopyMode = null;
workBookObjectcutCopyMode = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(cutCopyModeFilePath);
bool sheetExistcutCopyMode = ExcelHelper.Shared.GetWorksheetByName(workbookNamecutCopyMode, worksheetNamecutCopyMode, false) != null;
if (false == sheetExistcutCopyMode)
{
if (NeedToClose == true)
{
workBookObjectcutCopyMode.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetNamecutCopyMode + "\" does not exist in activity Excel_CopyPaste", LogLevel.Error);
}
else
{
string workbookFullName = FilePath.Get(context);
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
string workbookName = string.Empty;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
dynamic workBookObject = null;
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();
workBookObjectcutCopyMode.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_CopyPaste", LogLevel.Error);
}
else
{
string cells = Cells.Get(context);
string cellsCutCopyMode = CutCopyModeCells.Get(context);
Range xlRange = null;
Worksheet xlWorksheet = null;
Worksheet xlWorksheetCutCopyMode = null;
bool result = false;
workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
xlWorksheet = workBookObject.Sheets[worksheetName];
xlRange = xlWorksheet.Range[cells];
xlWorksheetCutCopyMode = workBookObjectcutCopyMode.Sheets[worksheetNamecutCopyMode];
Range xlRangeCutCopyMode = xlWorksheetCutCopyMode.Range[cellsCutCopyMode];
Range cutCopyRange = xlRangeCutCopyMode;
Range insertRange = xlRange;
cutCopyRange.Copy();
string pt = ToDescriptionString(PasteType);
XlPasteType ptEnum = (XlPasteType)Enum.Parse(typeof(XlPasteType), pt);
result = insertRange.PasteSpecial(ptEnum, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
if (cutCopyModeFilePath.Equals(workbookFullName))
{
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
workBookObject.Save();
}
if (!cutCopyModeFilePath.Equals(workbookFullName))
{
workBookObjectcutCopyMode.Close();
workBookObject.Save();
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
workBookObject.Save();
}
Result.Set(context, result);
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
{
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_CopyPaste", LogLevel.Error);
}
}
}
else
{
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Excel file does not exist:\"" + cutCopyModeFilePath + "\" in activity Excel_CopyPaste", LogLevel.Error);
}
//if (!ContinueOnError)
//{
// context.Abort();
//}
}
catch (Exception ex)
{
Result.Set(context, false);
Log.Logger.LogData(ex.Message + " in activity Excel_CopyPaste", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
string strFilePath = FilePath.Get(context);
string workbookName = FilePath.Get(context);
Worksheet xlWorksheet = null;
Range xlRange = null;
bool excelFileVisible = false;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(strFilePath))
{
ExcelHelper.Shared.Close_OpenedFile(strFilePath);
workbookName = Path.GetFileName(strFilePath);
string workSheetName = WorksheetName.Get(context);
Workbook xlWorkbook = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, workSheetName, false) != null;
dynamic worksheets = xlWorkbook.Worksheets;
dynamic worksheetObject = null;
DataTable dt = TableToWrite.Get(context);
if (dt == null)
{
xlWorkbook.Close();
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
Log.Logger.LogData("Table To Write parameter(Datatable) is null in activity Excel_Append", LogLevel.Error);
}
else
{
if (false == sheetExist)
{
worksheetObject = worksheets.Add();
worksheetObject.Name = workSheetName;
}
object[,] TwoDimensionalArray = null;
string colLetter = String.Empty;
string endingCell = String.Empty;
int rangeEnd = 0;
xlWorksheet = xlWorkbook.Sheets[workSheetName];
Range xlRangeExcel = xlWorksheet.UsedRange;
int rowCountExcel = xlRangeExcel.Rows.Count;
if (1 != rowCountExcel)
{
rowCountExcel = rowCountExcel + 1;
}
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
if (IsHeader == true)
{
rangeEnd = rowCount + rowCountExcel;
TwoDimensionalArray = ConvertDataTableToArray(dt);
}
else
{
rangeEnd = rowCount + rowCountExcel - 1;
TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(dt);
}
colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(colCount);
endingCell = colLetter + rangeEnd;
string startingCell = "A" + rowCountExcel;
xlRange = xlWorksheet.Range[startingCell, endingCell];
xlRange.Value = TwoDimensionalArray;
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
xlWorkbook.Save();
if (NeedToClose == true)
{
xlWorkbook.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
xlWorkbook.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
xlWorkbook.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + strFilePath + "\" in activity Excel_Append", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Append", 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;
Worksheet xlWorksheet = null;
Range xlRange = 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);
string startingCell = StartingCell.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_Read_Single_Row", LogLevel.Error);
}
else
{
// xlWorkbook = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
xlWorksheet = workBookObject.Sheets[workSheetName];
int totalColumns = xlWorksheet.UsedRange.Columns.Count;
int totalRows = xlWorksheet.UsedRange.Rows.Count;
string rowLetter = startingCell.Substring(1);
String newStr = startingCell.Substring(1);
Range lastCell = xlWorksheet.Cells[int.Parse(newStr), totalColumns];
if (lastCell.Value2 == null)
{
lastCell = lastCell.End[XlDirection.xlToLeft];
}
int lastColumn = lastCell.Column;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(lastColumn);
string endingCell = colLetter + newStr;
xlRange = xlWorksheet.Range[startingCell, endingCell];
result = xlRange.Value;
IEnumerable <object> rs = ((System.Collections.IEnumerable)result).Cast <object>();
Value.Set(context, rs);
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_Read_Single_Row", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Value.Set(context, null);
Log.Logger.LogData(ex.Message + " in activity Excel_Read_Single_Row", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
ReleaseObject(xlRange);
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = FilePath.Get(context);
string workbookName = string.Empty;
bool excelFileVisible = false;
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);
string cell = Cell.Get(context);
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
workBookObject.Close();
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("\"" + worksheetName + "\" worksheet does not exist in activity Excel_CellValue_Write", LogLevel.Error);
}
else
{
ExcelHelper.Shared.GetRange(workbookName, worksheetName, cell).Value = Value.Get(context);
ExcelHelper.Shared.GetWorkbookByName(workbookName, true).Save();
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_CellValue_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_CellValue_Write", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = FilePath.Get(context);
string worksheetName = WorksheetName.Get(context);
string workbookName = string.Empty;
bool excelFileVisible = false;
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
workbookName = Path.GetFileName(workbookFullName);
////
//FileStream stream = null;
//try
//{
// stream = File.Open(workbookFullName, FileMode.Open, FileAccess.ReadWrite, FileShare.None);
//}
//catch (IOException ex)
//{
// ExcelHelper.Shared.Close_OpenedWorkbook(workbookName);
//}
//finally
//{
// if (stream != null)
// stream.Close();
//}
////
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
dynamic workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
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_CellValue_Clear", LogLevel.Error);
}
else
{
string cell = Cell.Get(context);
ExcelHelper.Shared.GetRange(workbookName, worksheetName, cell).ClearContents();
ExcelHelper.Shared.GetRange(workbookName, worksheetName, cell).Clear();
workBookObject.Save();
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_CellValue_Clear", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_CellValue_Clear", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
protected override void Execute(NativeActivityContext context)
{
string strFilePath = FilePath.Get(context);
string workbookName = FilePath.Get(context);
Worksheet xlWorksheet = null;
Range xlRange = null;
bool excelFileVisible = false;
try
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
if (File.Exists(strFilePath))
{
ExcelHelper.Shared.Close_OpenedFile(strFilePath);
workbookName = Path.GetFileName(strFilePath);
string workSheetName = WorksheetName.Get(context);
Workbook xlWorkbook = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, workSheetName, false) != null;
dynamic worksheets = xlWorkbook.Worksheets;
dynamic worksheetObject = null;
DataTable dt = TableToWrite.Get(context);
if (dt == null)
{
xlWorkbook.Close();
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Table To Write parameter(Datatable) is null in activity Excel_WriteFile", LogLevel.Error);
}
else
{
if (false == sheetExist)
{
worksheetObject = worksheets.Add();
worksheetObject.Name = workSheetName;
}
object[,] TwoDimensionalArray = null;
string colLetter = String.Empty;
string endingCell = String.Empty;
int rangeEnd = 0;
xlWorksheet = xlWorkbook.Sheets[workSheetName];
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
if (IsHeader == true)
{
rangeEnd = 1 + rowCount;
TwoDimensionalArray = ConvertDataTableToArray(dt);
}
else
{
rangeEnd = rowCount;
TwoDimensionalArray = ExcelHelper.Shared.ConvertDataTableToArray(dt);
}
colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(colCount);
endingCell = colLetter + rangeEnd;
xlRange = xlWorksheet.Range["A1", endingCell];
xlRange.Value = TwoDimensionalArray;
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
xlWorkbook.Save();
if (true == NeedToClose)
{
xlWorkbook.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
xlWorkbook.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
xlWorkbook.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(strFilePath);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + strFilePath + "\" in activity Excel_WriteFile", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_WriteFile", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
finally
{
//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad
//release com objects to fully kill excel process from running in the background
//if(xlRange != null)
// Marshal.ReleaseComObject(xlRange);
//if (xlWorksheet != null)
// Marshal.ReleaseComObject(xlWorksheet);
//if (xlWorkbook != null)
//{
// //close and release
// xlWorkbook.Close();
// Marshal.ReleaseComObject(xlWorkbook);
//}
//if (xlApp != null)
//{
// //quit and release
// xlApp.Quit();
// Marshal.ReleaseComObject(xlApp);
//}
//GC.Collect();
//GC.WaitForPendingFinalizers();
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = FilePath.Get(context);
if (!workbookFullName.Contains("."))
{
workbookFullName = workbookFullName + ".xlsx";
}
// string before = BeforeWorksheet.Get(context);
string after = AfterWorksheet.Get(context);
if (!File.Exists(workbookFullName))
{
Logger.Log.Logger.LogData("Excel file does not exist :" + workbookFullName + " in activity Excel_Worksheet_Create", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
else
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
string worksheetName = string.Empty;
string workbookName = Path.GetFileName(workbookFullName);
dynamic workBookObject = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
dynamic worksheets = workBookObject.Worksheets;
dynamic worksheetObject = null;
if (WorksheetName != null)
{
worksheetName = WorksheetName.Get(context);
}
// if (string.IsNullOrEmpty(after))
//{
bool AfterWorkSheetfound = false;
// Loop through all worksheets in the workbook
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in workBookObject.Worksheets)
{
// Check the name of the current sheet
if (sheet.Name == after)
{
AfterWorkSheetfound = true;
break; // Exit the loop now
}
}
if (false == AfterWorkSheetfound)
{
after = null;
}
// }
bool value = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (value == false)
{
if (string.IsNullOrEmpty(after) || false == AfterWorkSheetfound)
{
worksheetObject = worksheets.Add();
}
else if (!string.IsNullOrEmpty(after) || false == AfterWorkSheetfound)
{
dynamic afterworksheet = worksheets.Item(after);
worksheetObject = worksheets.Add(Type.Missing, afterworksheet, 1, Type.Missing);
}
if (!String.IsNullOrEmpty(worksheetName))
{
worksheetObject.Name = worksheetName;
}
workBookObject.Save();
}
else
{
Log.Logger.LogData("Worksheet is already exists in activity Excel_Worksheet_Create", LogLevel.Info);
if (!ContinueOnError)
{
context.Abort();
}
}
//bool value = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
//if (value == false)
//{
// //if ((string.IsNullOrEmpty(before))&&(string.IsNullOrEmpty(after)))
// //{
// // worksheetObject = worksheets.Add();
// //}
// if (string.IsNullOrEmpty(after))
// {
// worksheetObject = worksheets.Add();
// }
// //else if((string.IsNullOrEmpty(before)) && (!string.IsNullOrEmpty(after)))
// //{
// // dynamic afterworksheet = worksheets.Item(after);
// // //if(afterworksheet == null)
// // //{
// // // Log.Logger.LogData("Error in getting after worksheet " + " in activity Worksheet_Create", LogLevel.Error);
// // // context.Abort();
// // //}
// // worksheetObject = worksheets.Add(Type.Missing, afterworksheet, 1, Type.Missing);
// //}
// else if (!string.IsNullOrEmpty(after))
// {
// dynamic afterworksheet = worksheets.Item(after);
// //if(afterworksheet == null)
// //{
// // Log.Logger.LogData("Error in getting after worksheet " + " in activity Worksheet_Create", LogLevel.Error);
// // context.Abort();
// //}
// worksheetObject = worksheets.Add(Type.Missing, afterworksheet, 1, Type.Missing);
// }
// //else if ((!string.IsNullOrEmpty(before)) && (string.IsNullOrEmpty(after)))
// //{
// // dynamic beforeworksheet = worksheets.Item(before);
// // //if (beforeworksheet == null)
// // //{
// // // Log.Logger.LogData("Error in getting before worksheet " + " in activity Worksheet_Create", LogLevel.Error);
// // // context.Abort();
// // //}
// // worksheetObject = worksheets.Add(beforeworksheet, Type.Missing, 1, Type.Missing);
// //}
// //else if ((!string.IsNullOrEmpty(before)) && (!string.IsNullOrEmpty(after)))
// //{
// // dynamic beforeworksheet = worksheets.Item(before);
// // //if (beforeworksheet == null)
// // //{
// // // Log.Logger.LogData("Error in getting before worksheet " + " in activity Worksheet_Create", LogLevel.Error);
// // //}
// // dynamic afterworksheet = worksheets.Item(after);
// // //if (afterworksheet == null)
// // //{
// // // Log.Logger.LogData("Error in getting after worksheet " + " in activity Worksheet_Create", LogLevel.Error);
// // // context.Abort();
// // //}
// // worksheetObject = worksheets.Add(beforeworksheet, afterworksheet, 1, Type.Missing);
// //}
// if (!String.IsNullOrEmpty(worksheetName))
// {
// worksheetObject.Name = worksheetName;
// }
// //if (NeedToSave == true)
// //{
// // if ((workBookObject != null) && (worksheetObject != null))
// // {
// workBookObject.Save();
// // }
// //}
//}
//else
//{
// Log.Logger.LogData("Worksheet is already exists in activity Excel_Worksheet_Create", LogLevel.Info);
//}
//if (NeedToClose == true)
//{
// if ((workBookObject != null) && (worksheetObject != null))
// {
// workBookObject.Close();
// if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
// {
// ExcelHelper.Shared.Dispose();
// }
// }
//}
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Worksheet_Create", 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);
}
}
protected override void Execute(NativeActivityContext context)
{
try
{
string workbookFullName = FilePath.Get(context);
string workSheetName = WorksheetName.Get(context);
ExcelObject.Range xlRange = null;
DataTable resultDataTable = null;
bool excelFileVisible = false;
dynamic workBookObject = null;
ExcelObject.Range xlRangeHeader = null;
string workbookName = string.Empty;
ExcelObject._Worksheet xlWorksheet = null;
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);
string range = Range.Get(context);
string[] cells = null;
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_Range_Read", LogLevel.Error);
}
else
{
if (range.Contains(":"))
{
cells = range.Split(':');
if (cells.Count() != 2)
{
Log.Logger.LogData("Please provide valid range in activity Excel_Range_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
else
{
dynamic result = null;
dynamic resultHeader = null;
xlWorksheet = workBookObject.Sheets[workSheetName];
xlRange = xlWorksheet.Range[cells[0], cells[1]];
result = xlRange.Value;
if (!cells[0].Contains("1") && true == IsHeader)
{
String startAlpa = new String(cells[0].Where(c => Char.IsLetter(c)).ToArray()).ToUpper();
String endAlpa = new String(cells[1].Where(c => Char.IsLetter(c)).ToArray()).ToUpper();
startAlpa = startAlpa + "1";
endAlpa = endAlpa + "1";
xlRangeHeader = xlWorksheet.Range[startAlpa, endAlpa];
resultHeader = xlRangeHeader.Value;
resultDataTable = Convert2DArraytoDatatableHeader(resultHeader, result);
}
else
{
resultDataTable = Convert2DArraytoDatatable(result);
}
Value.Set(context, resultDataTable);
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("Please provide valid range in activity Excel_Range_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Range_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
catch (Exception ex)
{
Log.Logger.LogData(ex.Message + " in activity Excel_Range_Read", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
//finally
//{
// ReleaseObject(xlRange);
//}
}
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;
Workbook xlWorkbook = null;
Worksheet xlWorksheet = null;
Range xlRange = 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);
string startingCell = StartingCell.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_Read_Single_Row", LogLevel.Error);
}
else
{
xlWorkbook = ExcelHelper.Shared.GetApp().Workbooks.Open(workbookFullName);
xlWorksheet = xlWorkbook.Sheets[workSheetName];
int totalColumns = xlWorksheet.UsedRange.Columns.Count;
int totalRows = xlWorksheet.UsedRange.Rows.Count;
string colLetter = ExcelHelper.Shared.ColumnIndexToColumnLetter(totalColumns);
string rowLetter = startingCell.Substring(1);
string endingCell = colLetter + rowLetter;
xlRange = xlWorksheet.Range[startingCell, endingCell];
result = xlRange.Value;
Value.Set(context, result);
if (NeedToClose == true)
{
ExcelHelper.Shared.GetWorkbookByName(workbookName, true).Close();
}
if (ExcelHelper.Shared.GetApp().Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_Read_Single_Row", LogLevel.Error);
}
if (!ContinueOnError)
{
context.Abort();
}
}
catch (Exception ex)
{
Value.Set(context, null);
Log.Logger.LogData(ex.Message + " in activity Excel_Read_Single_Row", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
