Freeze and Filter First Row helper

[jzabroski]

It looks like you support filtering a row, although don't have a specific way to say "first row up to the last populated column". Using your source generator object model, I'd want the range to be the first index and last index written to the excel document.

I'd similarly want to write a whole collection to the spreadsheet.

Here are some possible interfaces:

void SetAutoFilter(string sheetName, CellRelativeReferenceRange range);

// In the general case, this would require a way to get the Max Display Range to compute the start cell and end cell.
void FreezeAndAutoFilterFirstRow(string sheetName);

Additionally, freeze panes would be a bonus although I honestly rarely use these:

/// <summary>Freezes panes at the specified cell in the worksheet.</summary>
/// <param name="row">Row index.</param>
/// <param name="column">Column index.</param>
/// <param name="freezedRows">Number of visible rows in top pane, no more than row index.</param>
/// <param name="freezedColumns">Number of visible columns in left pane, no more than column index.</param>
/// <remarks>
///   <p>Row index and column index cannot all be zero. Number of rows and number of columns
///       also cannot all be zero.</p>
///   <p>The first two parameters specify the froze position and the last two parameters specify the area frozen on the left top pane.</p>
/// </remarks>
void FreezePanes(int row, int column, int freezedRows, int freezedColumns);

[sveinungf]

It looks like you support filtering a row, although don't have a specific way to say "first row up to the last populated column". Using your source generator object model, I'd want the range to be the first index and last index written to the excel document.

That's correct, it's not possible right now. You can only pass the cell range reference to AutoFilterOptions when you create the worksheet. However, I played a bit around with this now, and it seems that Excel might not really care if all the rows are included in the cell range. In Excel the filter seems to include all rows anyway. Have you experienced a different behavior?

I'd similarly want to write a whole collection to the spreadsheet.

In addition to spreadsheet.AddAsRowAsync(), there is also spreadsheet.AddRangeAsRowsAsync() that takes an IEnumerable of your type. Perhaps it's what you're looking for?

Additionally, freeze panes would be a bonus although I honestly rarely use these:

You can freeze rows and columns by setting FrozenRows and FrozenColumns on WorksheetOptions.

[jzabroski]

In Excel the filter seems to include all rows anyway. Have you experienced a different behavior?

Sort of. One scenario to consider is when you select a range in Excel and click the Auto Filter button in the ribbon. If you don't select a final Subtotal row, it will be smart and won't include that row, although I'm not sure what algorithm it uses to decide this behavior.

I can provide a small test case spreadsheet before and after comparison as a gold copy test if helpful.

Thanks for your patience waiting for my reply!