/// <summary>
/// Event delegate method fired when any value in a cell within the <see cref="EditingWorksheet"/> changes.
/// </summary>
/// <remarks>
/// This method is used to record any changes done by users to the data and prepare corresponding changes within a data table object
/// that later will generate queries to commit the data changes to the MySQL server.
/// </remarks>
/// <param name="target"></param>
private void EditingWorksheet_Change(ExcelInterop.Range target)
{
if (Globals.ThisAddIn.SkipWorksheetChangeEvent)
{
return;
}
var rowWasDeleted = EditingWorksheet.UsedRange.Rows.Count < _editingRowsQuantity && target.Columns.Count == EditingWorksheet.Columns.Count;
var undoChanges = false;
string operationSummary = null;
string operationDetails = null;
var intersectRange = _editDataRange.IntersectWith(target);
if (intersectRange == null || intersectRange.CountLarge == 0)
{
undoChanges = true;
if (rowWasDeleted)
{
// The row for insertions is attempted to be deleted, we need to undo
operationSummary = Resources.EditDataDeleteLastRowNotPermittedErrorTitle;
operationDetails = Resources.EditDataDeleteLastRowNotPermittedErrorDetail;
}
else
{
// It is a modification and outside the permitted range
operationSummary = Resources.EditDataOutsideEditingRangeNotPermittedErrorTitle;
operationDetails = Resources.EditDataOutsideEditingRangeNotPermittedErrorDetail;
}
}
if (undoChanges)
{
MiscUtilities.ShowCustomizedErrorDialog(operationSummary, operationDetails, true);
UndoChanges();
if (!rowWasDeleted)
{
return;
}
_editDataRange = EditingWorksheet.UsedRange;
return;
}
if (intersectRange.Item[1, 1] is ExcelInterop.Range startCell)
{
// Subtract from the Excel indexes since they start at 1, ExcelRow is subtracted by 2 if we imported headers.
var startDataTableRow = startCell.Row - 2;
var startDataTableCol = startCell.Column - 1;
// Detect if a row was deleted and if so flag it for deletion
if (rowWasDeleted)
{
var skipDeletedRowsList = new List <int>();
foreach (ExcelInterop.Range deletedRow in target.Rows)
{
startDataTableRow = deletedRow.Row - 2;
startDataTableRow = _mySqlTable.SearchRowIndexNotDeleted(startDataTableRow, skipDeletedRowsList, _editDataRange.Rows.Count);
var dr = _mySqlTable.Rows[startDataTableRow];
dr.Delete();
skipDeletedRowsList.Add(startDataTableRow);
}
_editingRowsQuantity = _editDataRange.Rows.Count;
}
else
{
// The change was a modification of cell values
MySqlDataColumn currCol = null;
try
{
for (var rowIdx = 1; rowIdx <= intersectRange.Rows.Count; rowIdx++)
{
for (var colIdx = 1; colIdx <= intersectRange.Columns.Count; colIdx++)
{
ExcelInterop.Range cell = intersectRange.Cells[rowIdx, colIdx];
if (cell == null)
{
continue;
}
// Detect if a data row has been added by the user and if so flag it for addition
if (cell.Row == _editDataRange.Rows.Count)
{
if (cell.Value == null)
{
continue;
}
var insertingRowRange = AddNewRowToEditingRange(true);
if (_mySqlTable.NewRow() is MySqlDataRow newRow)
{
newRow.ExcelRange = insertingRowRange;
_mySqlTable.Rows.Add(newRow);
}
}
var absRow = startDataTableRow + rowIdx - 1;
absRow = _mySqlTable.SearchRowIndexNotDeleted(absRow, null, _editDataRange.Rows.Count);
var absCol = startDataTableCol + colIdx - 1;
currCol = _mySqlTable.GetColumnAtIndex(absCol);
var cellValue = cell.GetCellPackedValue(true);
object insertingValue = DBNull.Value;
if (cellValue != null)
{
insertingValue = currCol.GetInsertingValueForType(cellValue, false);
}
if (insertingValue == null)
{
_mySqlTable.Rows[absRow][absCol] = DBNull.Value;
if (!(cellValue is DateTime))
{
continue;
}
Globals.ThisAddIn.SkipWorksheetChangeEvent = true;
cellValue = null;
Globals.ThisAddIn.SkipWorksheetChangeEvent = false;
}
else
{
_mySqlTable.Rows[absRow][absCol] = insertingValue;
}
}
}
}
catch (ArgumentException argEx)
{
undoChanges = true;
operationSummary = string.Format(Resources.EditDataInvalidValueError, currCol != null ? currCol.MySqlDataType.FullType : "Unknown");
operationDetails = argEx.Message;
}
catch (Exception ex)
{
undoChanges = true;
operationSummary = Resources.EditDataCellModificationError;
operationDetails = ex.Message;
Logger.LogException(ex);
}
finally
{
if (undoChanges)
{
MiscUtilities.ShowCustomizedErrorDialog(operationSummary, operationDetails, true);
UndoChanges();
}
}
}
}
CommitChangesButton.Enabled = !AutoCommitCheckBox.Checked && UncommittedDataExists;
if (AutoCommitCheckBox.Checked && UncommittedDataExists)
{
PushDataChanges();
}
}