C# unable to update spreadsheet table header values dynamically

Question

Hi ,

I have a spreadsheet template which has a table and when the user clicks on the export button these header values of the table should be updated by new values dynamically . For this I'm using c# and DocumentFormat.OpenXml library .
After exporting header values are getting updated in the spreadsheet table but when i open the excel document it says "We found problem with some content in '<>.xlsx' . Do you want us to recover as much as we can ? ".

Also , when I tried updating table rows other than header row , then was not getting above popup.
I have tried below approaches to update table header values.

1) Approach 1

using (SpreadsheetDocument document=   
                     SpreadsheetDocument.Open("test.xlsx", true))  
            {  

WorkbookPart wbPart = document.WorkbookPart;  

Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.name =="Sheet1").FirstOrDefault();  

 WorksheetPart wsPart = (WorksheetPart) (wbPart.GetPartById(sheet.Id));  

WorkSheet wSheet = wsPart.WorkSheet;  

Row row = wSheet.Elements<Row>().Where(r => r.RowIndex ==1 ).FirstOrDefault();  

Cell cell = row.Elements<Cell>().Where(c => string.Compare  
                   (c.CellReference.Value, "A1" , true) == 0).First();  

cell.CellValue = new CellValue("new Header");  
cell.DataType = CellValues.String;  

wbPart.WorkBook.save();  

}  

2) Approach 2

foreach(TableDefinitionPart tdp in wsPart.TableDefinitionParts ){  

QueryTablePart qtp= tdp .QueryTableParts.FirstOrDefault();  

Table excelTable = tdp .Table;  

int i = 0;  

foreach(TableColumn col in excelTable.TableColumns){  

col.name.Value = "Header"+i;  
i++;  
}  
}  

Please help me.
Thank you.

Answer 1

@vaibhavd-9419, Welcome to Microsoft Q&A, based on my research and test, you could try the following code to update spreadsheet table header values.

using (SpreadsheetDocument document = SpreadsheetDocument.Open("1.xlsx", true))  
            {  
  
                WorkbookPart wbPart = document.WorkbookPart;  
  
                Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();  
  
                WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));  
  
                Cell theCell = wsPart.Worksheet.Descendants<Cell>().  
                 Where(c => c.CellReference == "A1").FirstOrDefault();  
                string updatetext = "new Header";  
                if (theCell != null)  
                {  
                    if (theCell.DataType != null && theCell.DataType == CellValues.SharedString)  
                    {  
                        var sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;  
                        var element = sharedStringTable.ChildElements[int.Parse(theCell.InnerText)];  
                        if (string.IsNullOrEmpty(element.InnerText))  
                        {  
                            SharedStringItem sst = new SharedStringItem(new Text(updatetext));  
                            sharedStringTable.AppendChild<SharedStringItem>(sst);  
                            int value = sharedStringTable.ChildElements.ToList().IndexOf(sst);  
                            theCell.CellValue = new CellValue(value.ToString());  
                            sharedStringTable.Save();  
                        }  
                        else  
                        {  
                            element.InnerXml = element.InnerXml.Replace(element.InnerText, updatetext);  
                            sharedStringTable.Save();  
                        }  
                    }  
                    else  
                    {  
                        theCell.CellValue = new CellValue(updatetext);  
                        theCell.DataType = new EnumValue<CellValues>(CellValues.String);  
                    }  
                }  
                wbPart.Workbook.Save();  
            }  

Based on my test, I could update the A1 value to "New Header" successfully.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.