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