How to obtain old and new value of the cells while performing drag fill operation in WinForms Spreadsheet?
Drag fill operation
WinForms Spreadsheet (SfSpreadsheet) allows you to create a new class which is derived from FillSeriesController and
override the FillSeries & CopyCells methods to get the old and new value of
the cells while performing drag and fill operation.
The
FillSeries method is invoked when the cell values are filled in a series by
dragging, and the CopyCells method is invoked when cell values are copied after
dragging. On both methods, you can get the filled range from the argument
newRange. Get the old value of the cells before calling the base method and get
the new value of the cells after calling the base method like below code
example.
protected override void FillSeries(GridRangeInfo oldRange, GridRangeInfo newRange)
{
//You can get the old values of each cell by looping the excelRange.Row, excelRange.Column, excelRange.LastRow and excelRange.LastColumn
var excelRangeString = newRange.ConvertGridRangeToExcelRange(grid);
var excelRange = Worksheet[excelRangeString];
base.FillSeries(oldRange, newRange);
//You can get the new values (after fill) of each cell by looping the excelRange.
excelRangeString = newRange.ConvertGridRangeToExcelRange(grid);
excelRange = Worksheet[excelRangeString];
}
protected override void CopyCells(GridRangeInfo oldRange, GridRangeInfo newRange)
{
//You can get the old values of each cell by looping the excelRange.Row, excelRange.Column, excelRange.LastRow and excelRange.LastColumn
var excelRangeString = newRange.ConvertGridRangeToExcelRange(grid);
var excelRange = Worksheet[excelRangeString];
base.CopyCells(oldRange, newRange);
//You can get the new values (after fill) of each cell by looping the excelRange.
excelRangeString = newRange.ConvertGridRangeToExcelRange(grid);
excelRange = Worksheet[excelRangeString];
} Then assign the instance of that custom FillSeriesController to the FillSeriesController property of each SpreadsheetGrid in the WorkbookLoaded and WorksheetAdded event of Spreadsheet.
spreadsheet.WorkbookLoaded += spreadsheet_WorkbookLoaded;
spreadsheet.WorksheetAdded += spreadsheet_WorksheetAdded;
void spreadsheet_WorksheetAdded(object sender, WorksheetAddedEventArgs args)
{
var grid = spreadsheet.ActiveGrid;
grid.FillSeriesController = new FillSeriesControllerExt(grid);
}
void spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{
foreach (var grid in args.GridCollection)
{
grid.FillSeriesController = new FillSeriesControllerExt(grid);
}
}Sample Link: DragFillOperationDemo
