/// <summary>
/// Binds the <see cref="MySqlTable"/> to the <see cref="ToolsExcelTable" /> so its data can be refreshed.
/// </summary>
public void BindMySqlDataTable()
{
if (MySqlTable == null || ToolsExcelTable == null)
{
return;
}
try
{
// In case the table is bound (it should not be) then disconnect it.
if (ToolsExcelTable.IsBinding)
{
ToolsExcelTable.Disconnect();
}
// Skip Worksheet events
Globals.ThisAddIn.SkipWorksheetChangeEvent = true;
Globals.ThisAddIn.SkipSelectedDataContentsDetection = true;
// Resize the ExcelTools.ListObject by giving it an ExcelInterop.Range calculated with the refreshed MySqlDataTable dimensions.
// Detection of a collision with another Excel object must be performed first and if any then shift rows and columns to fix the collision.
const int headerRows = 1;
int summaryRows = ExcelTable.ShowTotals ? 1 : 0;
ExcelInterop.Range newRange = ToolsExcelTable.Range.Cells[1, 1];
newRange = newRange.SafeResize(MySqlTable.Rows.Count + headerRows + summaryRows, MySqlTable.Columns.Count);
var intersectingRange = newRange.GetIntersectingRangeWithAnyExcelObject(true, true, true, _excelTable.Comment);
if (intersectingRange != null && intersectingRange.CountLarge != 0)
{
ExcelInterop.Range bottomRightCell = newRange.Cells[newRange.Rows.Count, newRange.Columns.Count];
// Determine if the collision is avoided by inserting either new columns or new rows.
if (intersectingRange.Columns.Count < intersectingRange.Rows.Count)
{
for (int colIdx = 0; colIdx <= intersectingRange.Columns.Count; colIdx++)
{
bottomRightCell.EntireColumn.Insert(ExcelInterop.XlInsertShiftDirection.xlShiftToRight, Type.Missing);
}
}
else
{
for (int rowIdx = 0; rowIdx <= intersectingRange.Rows.Count; rowIdx++)
{
bottomRightCell.EntireRow.Insert(ExcelInterop.XlInsertShiftDirection.xlShiftDown, Type.Missing);
}
}
// Redimension the new range. This is needed since the new rows or columns inserted are not present in the previously calculated one.
newRange = ToolsExcelTable.Range.Cells[1, 1];
newRange = newRange.SafeResize(MySqlTable.Rows.Count + headerRows + summaryRows, MySqlTable.Columns.Count);
}
// Redimension the ExcelTools.ListObject's range
ToolsExcelTable.Resize(newRange);
// Re-format the importing range
ExcelInterop.Range dataOnlyRange = newRange.Offset[headerRows];
dataOnlyRange = dataOnlyRange.Resize[newRange.Rows.Count - headerRows - summaryRows];
MySqlTable.FormatImportExcelRange(dataOnlyRange, true);
// Bind the redimensioned ExcelTools.ListObject to the MySqlDataTable
ToolsExcelTable.SetDataBinding(MySqlTable);
if (MySqlTable.ImportColumnNames)
{
foreach (MySqlDataColumn col in MySqlTable.Columns)
{
ToolsExcelTable.ListColumns[col.Ordinal + 1].Name = col.DisplayName;
}
}
ToolsExcelTable.Range.Columns.AutoFit();
// Disconnect the table so users can freely modify the data imported to the Excel table's range.
ToolsExcelTable.Disconnect();
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.ImportDataBindError, _excelTableName), ex.GetFormattedMessage(), true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
finally
{
Globals.ThisAddIn.SkipWorksheetChangeEvent = false;
Globals.ThisAddIn.SkipSelectedDataContentsDetection = false;
}
}