Пример #1
0
        /// <summary>
        /// Checks if any of the <see cref="ExcelInterop.Range"/>s where result sets returned by this procedure's execution are imported would collide with another Excel object.
        /// </summary>
        /// <param name="importType">The <see cref="ProcedureResultSetsImportType"/> defining what result sets are imported and how they are laid out in the Excel worksheet. </param>
        /// <param name="selectedResultSetIndex">The index of the result set selected for import in case the <see cref="importType"/> is <see cref="ProcedureResultSetsImportType.SelectedResultSet"/>.</param>
        /// <param name="resultSetsDataSet">The <see cref="DataSet"/> containing all result sets returned by the stored procedure's execution.</param>
        /// <returns><c>true</c> if any of the <see cref="ExcelInterop.Range"/>s where result sets returned by this procedure's execution are imported would collide with another Excel object, <c>false</c> otherwise.</returns>
        private bool DetectDataForImportPossibleCollisions(ProcedureResultSetsImportType importType, int selectedResultSetIndex, DataSet resultSetsDataSet)
        {
            if (resultSetsDataSet == null)
            {
                return(false);
            }

            var createPivotTable  = ImportParameters.CreatePivotTable;
            var collisionDetected = false;
            var atCell            = Globals.ThisAddIn.Application.ActiveCell;
            var tableIdx          = 0;
            var pivotPosition     = importType == ProcedureResultSetsImportType.AllResultSetsHorizontally
        ? ExcelUtilities.PivotTablePosition.Below
        : ExcelUtilities.PivotTablePosition.Right;

            foreach (var mySqlTable in resultSetsDataSet.Tables.Cast <MySqlDataTable>().Where(mySqlTable => importType != ProcedureResultSetsImportType.SelectedResultSet || selectedResultSetIndex == tableIdx++))
            {
                var ranges = mySqlTable.GetExcelRangesToOccupy(atCell, ImportParameters.AddSummaryRow, ImportParameters.CreatePivotTable, pivotPosition);
                if (ranges == null)
                {
                    continue;
                }

                collisionDetected = ranges.Aggregate(false, (current, range) => current || range.IntersectsWithAnyExcelObject());
                if (collisionDetected)
                {
                    break;
                }

                atCell = ranges[0].GetNextResultSetTopLeftCell(importType, createPivotTable);
            }

            return(collisionDetected);
        }
Пример #2
0
        /// <summary>
        /// Imports the result sets of this stored procedure to a <see cref="ExcelInterop.Worksheet"/>.
        /// </summary>
        /// <param name="importType">The <see cref="ProcedureResultSetsImportType"/> defining what result sets are imported and how they are laid out in the Excel worksheet. </param>
        /// <param name="selectedResultSetIndex">The index of the result set selected for import in case the <see cref="importType"/> is <see cref="ProcedureResultSetsImportType.SelectedResultSet"/>.</param>
        /// <param name="resultSetsDataSet">The <see cref="DataSet"/> containing all result sets returned by the stored procedure's execution.</param>
        /// <returns><c>true</c> if the import is successful, <c>false</c> otherwise.</returns>
        public bool ImportData(ProcedureResultSetsImportType importType, int selectedResultSetIndex, DataSet resultSetsDataSet = null)
        {
            if (resultSetsDataSet == null)
            {
                resultSetsDataSet = Execute();
            }

            var success = true;

            try
            {
                var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;

                // Check if the data being imported does not overlap with the data of an existing Excel table.
                if (DetectDataForImportPossibleCollisions(importType, selectedResultSetIndex, resultSetsDataSet))
                {
                    var infoProperties = InfoDialogProperties.GetYesNoDialogProperties(
                        InfoDialog.InfoType.Warning,
                        Resources.ImportOverExcelObjectErrorTitle,
                        Resources.ImportOverExcelObjectErrorDetail,
                        Resources.ImportOverExcelObjectErrorSubDetail);
                    if (InfoDialog.ShowDialog(infoProperties).DialogResult == DialogResult.No)
                    {
                        return(false);
                    }

                    var newWorkSheet = activeWorkbook.CreateWorksheet(Name, true);
                    if (newWorkSheet == null)
                    {
                        return(false);
                    }
                }

                var tableIdx         = 0;
                var createPivotTable = ImportParameters.CreatePivotTable;
                var addSummaryRow    = ImportParameters.AddSummaryRow;
                var nextTopLeftCell  = Globals.ThisAddIn.Application.ActiveCell;
                foreach (var mySqlTable in resultSetsDataSet.Tables.Cast <MySqlDataTable>().Where(mySqlTable => importType != ProcedureResultSetsImportType.SelectedResultSet || selectedResultSetIndex == tableIdx++))
                {
                    var importingMySqlTable   = mySqlTable;
                    var excludedColumnIndexes = mySqlTable.Columns.Cast <MySqlDataColumn>().Where(mySqlColumn => mySqlColumn.ExcludeColumn).Select(mySqlColumn => mySqlColumn.Ordinal).ToList();
                    if (excludedColumnIndexes.Count > 0)
                    {
                        importingMySqlTable = new MySqlDataTable(Connection, mySqlTable, mySqlTable.SelectQuery, mySqlTable.ProcedureResultSetIndex);
                        excludedColumnIndexes.Reverse();
                        foreach (var excludedColumnIndex in excludedColumnIndexes)
                        {
                            importingMySqlTable.Columns.RemoveAt(excludedColumnIndex);
                        }
                    }

                    Globals.ThisAddIn.Application.Goto(nextTopLeftCell, false);
                    importingMySqlTable.ImportColumnNames = ImportParameters.IncludeColumnNames;
                    importingMySqlTable.TableName         = Name + "." + mySqlTable.TableName;
                    var excelObj = Settings.Default.ImportCreateExcelTable
            ? importingMySqlTable.ImportDataIntoExcelTable(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow)
            : importingMySqlTable.ImportDataIntoExcelRange(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow);
                    if (excelObj == null)
                    {
                        continue;
                    }

                    var fillingRange = excelObj is ExcelInterop.ListObject listObject
            ? listObject.Range
            : excelObj as ExcelInterop.Range;
                    nextTopLeftCell = fillingRange.GetNextResultSetTopLeftCell(importType, createPivotTable);
                }
            }
            catch (Exception ex)
            {
                success = false;
                Logger.LogException(ex, true, string.Format(Resources.UnableToRetrieveData, "procedure", Name));
            }

            return(success);
        }
Пример #3
0
        /// <summary>
        /// Imports the result sets of this stored procedure to a <see cref="ExcelInterop.Worksheet"/>.
        /// </summary>
        /// <param name="importType">The <see cref="ProcedureResultSetsImportType"/> defining what result sets are imported and how they are laid out in the Excel worksheet. </param>
        /// <param name="selectedResultSetIndex">The index of the result set selected for import in case the <see cref="importType"/> is <see cref="ProcedureResultSetsImportType.SelectedResultSet"/>.</param>
        /// <param name="resultSetsDataSet">The <see cref="DataSet"/> containing all result sets returned by the stored procedure's execution.</param>
        /// <returns><c>true</c> if the import is successful, <c>false</c> otherwise.</returns>
        public bool ImportData(ProcedureResultSetsImportType importType, int selectedResultSetIndex, DataSet resultSetsDataSet = null)
        {
            if (resultSetsDataSet == null)
            {
                resultSetsDataSet = Execute();
            }

            bool success = true;

            try
            {
                var activeWorkbook = Globals.ThisAddIn.ActiveWorkbook;

                // Check if the data being imported does not overlap with the data of an existing Excel table.
                if (DetectDataForImportPossibleCollisions(importType, selectedResultSetIndex, resultSetsDataSet))
                {
                    var infoProperties = InfoDialogProperties.GetYesNoDialogProperties(
                        InfoDialog.InfoType.Warning,
                        Resources.ImportOverExcelObjectErrorTitle,
                        Resources.ImportOverExcelObjectErrorDetail,
                        Resources.ImportOverExcelObjectErrorSubDetail);
                    if (InfoDialog.ShowDialog(infoProperties).DialogResult == DialogResult.No)
                    {
                        return(false);
                    }

                    var newWorkSheet = activeWorkbook.CreateWorksheet(Name, true);
                    if (newWorkSheet == null)
                    {
                        return(false);
                    }
                }

                int  tableIdx         = 0;
                bool createPivotTable = ImportParameters.CreatePivotTable;
                bool addSummaryRow    = ImportParameters.AddSummaryRow;
                ExcelInterop.Range nextTopLeftCell = Globals.ThisAddIn.Application.ActiveCell;
                foreach (var mySqlTable in resultSetsDataSet.Tables.Cast <MySqlDataTable>().Where(mySqlTable => importType != ProcedureResultSetsImportType.SelectedResultSet || selectedResultSetIndex == tableIdx++))
                {
                    Globals.ThisAddIn.Application.Goto(nextTopLeftCell, false);
                    mySqlTable.ImportColumnNames = ImportParameters.IncludeColumnNames;
                    mySqlTable.TableName         = Name + "." + mySqlTable.TableName;
                    var excelObj = Settings.Default.ImportCreateExcelTable
            ? mySqlTable.ImportDataIntoExcelTable(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow)
            : mySqlTable.ImportDataIntoExcelRange(createPivotTable, ExcelUtilities.PivotTablePosition.Right, addSummaryRow);
                    if (excelObj == null)
                    {
                        continue;
                    }

                    var fillingRange = excelObj is ExcelInterop.ListObject
            ? (excelObj as ExcelInterop.ListObject).Range
            : excelObj as ExcelInterop.Range;
                    nextTopLeftCell = fillingRange.GetNextResultSetTopLeftCell(importType, createPivotTable);
                }
            }
            catch (Exception ex)
            {
                success = false;
                MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.UnableToRetrieveData, "procedure", Name), ex.Message);
                MySqlSourceTrace.WriteAppErrorToLog(ex);
            }

            return(success);
        }