コード例 #1
0
        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();
                }
            }
        }
コード例 #2
0
        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();
            }
        }