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