Insert a new worksheet into a spreadsheet document OpenXml

Question

What im trying to do is create multiple worksheets within a work book using datasets the code i have to create a sheet data object from a data set is:

        public static SheetData CreateDataSheet(DataSet ds)
    {
        var xlSheetData = new SheetData();
        if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
        {
            var tbl = ds.Tables[0];
            foreach (DataRow row in tbl.Rows)
            {
                var xlRow = new Row();
                foreach (DataColumn col in tbl.Columns)
                {
                    var cellData = row[col];
                    Cell xlCell = null;
                    if (cellData != null)
                    {
                        xlCell = new Cell(new InlineString(new Text(cellData.ToString())))
                        {
                            DataType = CellValues.InlineString
                        };
                    }
                    else
                    {
                        xlCell = new Cell(new InlineString(new Text(String.Empty)))
                        {
                            DataType = CellValues.InlineString
                        };
                    }
                    xlRow.Append(xlCell);
                }
                xlSheetData.Append(xlRow);
            }
        }
        return xlSheetData;
    }

Then to create the spreadsheet and append the above to the spreadsheet i have:

 public static void CreateSpreadsheetWorkbook(string filepath, List<SheetData> sd)
    {


        var spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
        var workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();


        foreach (var x in sd)
        {
            var newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(x);
            var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Any())
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            var sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = "mySheet" + sheetId };
            sheets.Append(sheet);
            workbookpart.Workbook.Save();

        }

        spreadsheetDocument.Close();

    }

This runs with out any errors however when i come to open the document its unable to be opened because it is corrupt.

EDIT - final working version:

public static void CreateSpreadsheetWorkbook(string filepath, List<SheetData> sd)
    {


        var spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
        var workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        foreach (var x in sd)
        {
            var newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(x);

            sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();

            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Any())
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            var sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = "mySheet" + sheetId };
            sheets.Append(sheet);
            workbookpart.Workbook.Save();

        }

        spreadsheetDocument.Close();

    }

Answer 1

After comparing with some similar code of mine I found these possible problems:

var spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

//should be 

var spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.DocumentType);

And

var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

//should be

var sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();

after the first time since by the second time you already have a Sheets element.

See this blogpost for more details:
http://blogs.msdn.com/b/brian_jones/archive/2009/02/19/how-to-copy-a-worksheet-within-a-workbook.aspx