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