Open XML: How to add rows and cell value after appending new sheet in existing Excel file using c#

Question

I inserted/appended new sheet in existing Excel file using open XML but I am not able to add rows and cell values. Following is my code:

  using (var workbook = SpreadsheetDocument.Open(excelFilePath, true))
            {
                var workbookPart = workbook.WorkbookPart;
                var wb = workbookPart.Workbook;
                int rowIndex = 0;
                // Add a blank WorksheetPart.
                WorksheetPart newWorksheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbook.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 = "Data";

                // Append the new worksheet and associate it with the workbook.
                Sheet dataSheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
                var sharedStringPart = workbookPart.SharedStringTablePart;
                var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
                Row headerRow = new Row();
                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue("EmpId");
                headerRow.AppendChild<Cell>(cell);
                cell = new Cell();
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue("Name");
                headerRow.AppendChild<Cell>(cell);

                headerRow.AppendChild<Cell>(cell);
                dataSheet.InsertAt<Row>(headerRow, rowIndex++);
                workbookPart.Workbook.Save();
            }
        }

It's throwing an exception:

Non-composite elements do not have child elements.

Answer 1

When i got this error ("Non-composite elements do not have child elements.") was about my sheet object. I got the worng object to Apeend, so you need change the use .Append to something like that:

Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
sheetData.Append(row);

A Full Example

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(tmp, SpreadsheetDocumentType.Workbook))
            {
                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());

                // Add Sheets to the Workbook.
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                    AppendChild<Sheets>(new Sheets());

                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet()
                {
                    Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = fileName,

                };
                sheets.Append(sheet);


                var firstChild = sheet.FirstChild;

                //sheet.Append(row);

                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();

                UInt32 rowIndex = 1;
                var row = new Row() { RowIndex = rowIndex };

                var firstNameCell = new Cell() { CellReference = "A" + rowIndex };
                firstNameCell.CellValue = new CellValue("FirstName");
                firstNameCell.DataType = CellValues.String;

                row.Append(firstNameCell);

                Cell lastNameCell = new Cell() { CellReference = "B" + rowIndex };
                lastNameCell.CellValue = new CellValue("LastName");
                lastNameCell.DataType = new EnumValue<CellValues>(CellValues.String);

                row.Append(lastNameCell);

                sheetData.Append(row);

                workbookpart.Workbook.Save();

                // Close the document.
                spreadsheetDocument.Close();

            }