I have code that reads in an Excel file (used as a template). The first row of this template is formatted properly but empty. I copy that row.
For each row that I want to add, I pasted a clone of the copied row and fill in the data.
It almost works. All of the data is there. But 1) something is corrupt about the new file, I get an error saying it needs to repair data, 2) the background of the added rows isn't the same as the first row and 3) more importantly the AutoFilter seems to only see one row, no matter what I do.
In my test data I have 6 rows and the column filters only have checkboxes for data that exists in the first row.
If I manually insert a row above the first row and change the data, then the autofilter corrects itself and shows options for the 2 rows.
Here is my code for adding the row:
row = (Row)copyRow.CloneNode(true);
row.RowIndex = rowCount;
foreach (var child in row.ChildElements)
((Cell)child).CellReference = Regex.Replace(((Cell)child).CellReference, @"\d+", rowCount.ToString());
var lastRow = sd.Elements<Row>().LastOrDefault();
lastRow.InsertAfterSelf(row);
And here is my code for correcting AutoFilter
foreach ( var td in ((WorksheetPart)wp).TableDefinitionParts)
{
var af = td.Table.AutoFilter;
if (af != null)
af.Reference = $"A1:W{rowCount}";
}
What I have tried:
I've spent 3 days trying different options and nothing has worked. Basically I've had to reverse-engineer because I'm unable to find documentation that addresses this.