C# Open XML : We found a problem with some content. Newly added sheet doesn't show any data

Question

Below is the code iam using to ADD a new sheet into already existing spreadsheet.

Iam passing a list<T> in the input , this T has only 2 properties "code" and "description". I am looping through each of the T properties and putting them in sheetdata and finally saving the spreadhseet.

private static void PutInExcel(List<RulesEngineOutput> output)
    {
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:\ATP\Sprints\PA\RE\IO.xlsx", true))
        {


            // Add a blank WorksheetPart.
            WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());

            Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new worksheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            // Give the new worksheet a name.
            string sheetName = "NewRole" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);

            SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
            // Constructing header
            Row row = new Row();

            row.Append(
                ConstructCell("Code", CellValues.String),
                ConstructCell("Description", CellValues.String));

            // Insert the header row to the Sheet Data
            sheetData.AppendChild(row);


            foreach (var reItem in output)
            {
                row = new Row();

                row.Append(
                    ConstructCell(reItem.Code.ToString(), CellValues.Number),
                    ConstructCell(reItem.Description, CellValues.String)
                    );

                sheetData.AppendChild(row);
            }


            newWorksheetPart.Worksheet.Save();
            document.WorkbookPart.Workbook.Save();
            document.Save();

        }

    }

The problem is everything happens without error, i mean i can see the added worksheet in the debugging window, i am saving everything as well but when i open that spreadhseet, i see an error msg

We found some problem with some content

and finally the sheets show up without any content as below :

Blank sheet with sheetname

Answer 1

I think the problem is where you are declaring sheetdata, As SheetData should already exist.

try

SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild<SheetData>();

Answer 2

later on i made 2 changes ( see the comments in below code or compare it with the original ) -- I did that becz the xml was erroring out so i found that converting the code into number is the case for erroring out of xml. Also, Alan H suggested to try and eliminate the new SheetData() passed inside worksheet, so i used default empty constructor.

private static void PutInExcel(List<RulesEngineOutput> output)
    {
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:\ATP\Sprints\PA\RE\IO.xlsx", true))
        {


            // Add a blank WorksheetPart.
            WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(); // Change 1

            Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new worksheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            // Give the new worksheet a name.
            string sheetName = "NewRole" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);

            SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
            // Constructing header
            Row row = new Row();

            row.Append(
                ConstructCell("Code", CellValues.String), // Change 2
                ConstructCell("Description", CellValues.String));

            // Insert the header row to the Sheet Data
            sheetData.AppendChild(row);


            foreach (var reItem in output)
            {
                row = new Row();

                row.Append(
                    ConstructCell(reItem.Code.ToString(), **CellValues.String**),
                    ConstructCell(reItem.Description, CellValues.String)
                    );

                sheetData.AppendChild(row);
            }


            newWorksheetPart.Worksheet.Save();
            document.WorkbookPart.Workbook.Save();
            document.Save();


        }

        //string csv = String.Join(",", output.Select(x => x.ToString()).ToArray());


    }