예제 #1
0
        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();
                }
            }
        }
예제 #2
0
        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);
            }
        }