Silverlight Spreadsheet Gear - Issue in updating the filtered row

Question

We have build an utility using silverlight and spreadsheet gear. Using this utility, we allow user to read data from backend and allow them to modify the data and then the utility would update the modified field back to the backend.

The problem which i have now is , when user do a filter and try to paste some values in the filtered row, the values are getting copied in the row which are between the filtered row.

I am not sure how to restrict that when we do a copy/paste, it should get pasted only on the filtered row not on the row between the filtered row.

I did a search in spreadhseet gear website, but i couldn't find any post related to this issue.

Any help is much appreciated?

score 1 · Answer 1 · answered Oct 27 '17 at 21:08

This behavior is by design. Note that Excel also will paste into hidden cells under this sort of scenario. If you need copy/paste to skip over hidden rows, you'll need to write your own routine that does this. Below is a quick attempt at this which works well with the sample scenario I've constructed. It's possible you'll need to modify this to support your own more advanced use cases:

using SpreadsheetGear;
...
...

// Create a new workbook and some local variables for convenience.
IWorkbookSet workbookSet = Factory.GetWorkbookSet();
IWorkbook workbook = workbookSet.Workbooks.Add();
IWorksheet worksheet = workbook.ActiveWorksheet;
IRange cells = worksheet.Cells;

// Populate A1:A7 with some data used for the AutoFiltered area of the sheet.
cells["A1:A7"].Value = new object[,] { { "AutoFilter Header" }, { "a" }, { "b" }, { "c" }, { "d" }, { "e" }, { "f" } };

// This will be our "source" range when we do a copy.
cells["D10:D12"].Value = new object[,] { { 1 }, { 2 }, { 3 } };

// Filter out a, c and e, leaving b, d and f visible.
cells["A1:A7"].AutoFilter(0, new object[] { "b", "d", "f" }, AutoFilterOperator.Values, null, true);

IRange sourceRange = cells["D10:D12"];
IRange destination = cells["A2"];

// This will paste into hidden rows.
//sourceRange.Copy(destination);

// Instead, write our own copy routine to skip over hidden rows.
CopySkipHiddenRows(sourceRange, destination);

...
...
...

// One unaddressed edge case--exception could get thrown if hidden rows extend to the 
// very bottom of the worksheet (i.e., Row 1,048,576) and we still have data
// to paste.
public void CopySkipHiddenRows(IRange sourceRange, IRange topLeftDestinationCell)
{
    // Loop through each row of the source range.
    for (int row = 0; row < sourceRange.RowCount; row++)
    {
        // Get row from source range
        IRange sourceRow = sourceRange[row, 0, row, sourceRange.ColumnCount - 1];

        // Skip over hidden rows.
        while (topLeftDestinationCell.EntireRow.Hidden)
            topLeftDestinationCell = topLeftDestinationCell.Offset(1, 0);

        // Copy into next visible row
        sourceRow.Copy(topLeftDestinationCell);

        // Move to next row
        topLeftDestinationCell = topLeftDestinationCell.Offset(1, 0);
    }
}

Thank you So much for you response Tim. I am trying to incorporate this with my module. I would update you shortly. — Das, Oct 27 '17 at 21:56
Tim, your Idea helped me complete this. I coded in such a way that the values should not be pasted if the row is hidden. I had that in my condition and it worked. Thank you so much for throwing that idea. — Das, Nov 17 '17 at 18:13

Silverlight Spreadsheet Gear - Issue in updating the filtered row

1 Answers1