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