/// <summary>
/// Unbinds the <see cref="ToolsExcelTable"/>, refreshes the data on the <see cref="MySqlTable"/> and binds it again to the <see cref="ToolsExcelTable"/>.
/// </summary>
public void Refresh()
{
if (MySqlTable == null || ToolsExcelTable == null)
{
return;
}
// Test the connection before attempting the data refresh.
if (!TestConnection())
{
if (ConnectionInfoError != ConnectionInfoErrorType.WorkbenchConnectionDoesNotExist)
{
return;
}
// If the Workbench connection does not exist anymore, log a message to the log, remove this object from the global connections collection and exit.
MySqlSourceTrace.WriteToLog(string.Format(Resources.ImportConnectionInfoRemovedConnectionText, WorkbookName, WorksheetName, ExcelTableName), SourceLevels.Warning);
Globals.ThisAddIn.StoredImportConnectionInfos.Remove(this);
return;
}
try
{
// In case the table is bound (it should not be) then disconnect it.
if (ToolsExcelTable.IsBinding)
{
ToolsExcelTable.Disconnect();
}
// Refresh the data on the MySqlDataTable and bind it so the Excel table is refreshed.
MySqlTable.RefreshData();
// Bind the table again after it was refreshed.
BindMySqlDataTable();
}
catch (Exception ex)
{
MiscUtilities.ShowCustomizedErrorDialog(string.Format(Resources.ImportDataRefreshError, _excelTableName), ex.GetFormattedMessage(), true);
MySqlSourceTrace.WriteAppErrorToLog(ex);
}
}
/// <summary>
/// Releases all resources used by the <see cref="ImportConnectionInfo"/> class
/// </summary>
/// <param name="disposing">If true this is called by Dispose(), otherwise it is called by the finalizer</param>
protected virtual void Dispose(bool disposing)
{
if (_disposed)
{
return;
}
// Free managed resources
if (disposing)
{
if (ToolsExcelTable != null)
{
if (ToolsExcelTable.IsBinding)
{
ToolsExcelTable.Disconnect();
}
ToolsExcelTable.DeleteSafely(false);
}
if (MySqlTable != null)
{
MySqlTable.Dispose();
}
// Set variables to null so this object does not hold references to them and the GC disposes of them sooner.
_connection = null;
MySqlTable = null;
ExcelTable = null;
ToolsExcelTable = null;
}
// Add class finalizer if unmanaged resources are added to the class
// Free unmanaged resources if there are any
_disposed = true;
}
/// <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;
}
}
