Click here to Skip to main content
15,883,558 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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();
 //Either of these 2 calls should work.  I'm using insert because of AutoFilter
 lastRow.InsertAfterSelf(row);
 //sd.AppendChild(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.
Posted
Comments
[no name] 6-Dec-23 14:36pm    
Sometimes it's better to replace the whole thing with a "memory copy" you've updated instead of trying to insert, etc. One assumes it's not an OpenXMl issue. You need an OpenXML "expert" in any case.
Sandy Jeakins 6-Dec-23 14:59pm    
Thanks Gerry, it's a big spreadsheet with a bunch of pivot tables on other tabs. Otherwise I would. Thanks for the suggestion.
[no name] 6-Dec-23 17:05pm    
If it "almost works", then the concept of "refresh" comes to mind.

https://stackoverflow.com/questions/16818508/openxml-refresh-excel-sheet-after-cell-update
Maciej Los 7-Dec-23 2:18am    
I'd suggest to use EPPlus NuGet package. It's very fast and light version of OpenXML/Excel.
Sandy Jeakins 7-Dec-23 7:44am    
The client is already paying for Telerik and won't pay for another license. Telerik doesn't support Pivot Tables and wipes them out on load.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900