Insert a new worksheet into a spreadsheet document OpenXml

c#, openxml-sdk, spreadsheet

Solution

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

Problem

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(); } ```

Original source