Excel create named sheets with populated data

Question

I have been trying to get this to work for almost 3 weeks. The code is suppose to create an excel document with a new named sheet for each item, transfer the object data to the sheet. Unfortunately it is duplicating the data into all of the sheets.

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create
                        (filepath, 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();

                // Add Sheets to the Workbook
                Sheets sheets = workbookpart.Workbook.
                    AppendChild(new Sheets());



                // Append a new worksheet and associate it with the workbook.
                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                foreach (Town temp in CampaignTowns)
                {

                    // Constructing header
                    Row row = new Row();


                    row = CreateContentRow(temp, sheetData);
                    // Insert the header row to the Sheet Data

                    //sheetData.AppendChild(row);
                    Sheet sheet = new Sheet()
                    {
                        Id = workbookpart.GetIdOfPart(worksheetPart),
                        SheetId = TempSheetId,
                        Name = temp.getName()
                    };
                    TempSheetId++;
                    sheets.Append(sheet);


                    sheetData = new SheetData();

                }
                //Save worksheet part
                worksheetPart.Worksheet.Save();
                workbookpart.Workbook.Save();
                // Close the document.
                spreadsheetDocument.Close();

CDot · Accepted Answer · 2018-09-16 17:48:04Z

So after doing some research I found my error and corrected it. Apparently you have to initialize the Worksheet part and SheetData in the loop. Here is a copy of my working corrected code for anyone having the same error. I also corrected my CreateContentRow function as a void.

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

                // Add a WorkbookPart to the document.
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();

                // Add Sheets to the Workbook
                Sheets sheets = workbookpart.Workbook.
                    AppendChild(new Sheets());

                foreach (Town temp in CampaignTowns)
                {
                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet();

                    worksheetPart.Worksheet.Save();

                    // Append a new worksheet and associate it with the workbook.
                    SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                    // Insert the header row to the Sheet Data
                    CreateContentRow(temp, sheetData);

                    Sheet sheet = new Sheet()
                    {
                        Id = workbookpart.GetIdOfPart(worksheetPart),
                        SheetId = TempSheetId,
                        Name = temp.getName()
                    };
                    TempSheetId++;
                    sheets.AppendChild(sheet);

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

Flydog57 · Accepted Answer · 2018-09-12 03:37:15Z

0

Create a workbook (in Excel) that has the three sheets you want. Put a little bit of data on each sheet. Save and close the workbook.

Open the Open XML SDK Productivity Tool (downloadable from Microsoft's site). Open your new workbook in the tool. Use the Reflect Code button to open the OpenXML code that represents your document.

Copy/paste the code you need. It's the easiest to get something like this to work. Good luck.

answered Sep 12, 2018 at 3:37

Flydog57

7,1712 gold badges22 silver badges22 bronze badges

3 Comments

CDot Over a year ago

Unfortunately the code generates random data for each sheet.

Flydog57 Over a year ago

Well, yeah. But what you get from the tool is the structure of the document. Once you get that, and you see how worksheet, sheets, sheet, row and cell (and the rest) fit together and interact, you can put in your code to insert your contents. I've done this on three major projects now, and much of what I did was straight up theft from that tool. The tool has two other great features: Finding errors and comparing two documents (which is amazingly useful)

CDot Over a year ago

I get the structure I am just not getting why my code is not working correctly.

Collectives™ on Stack Overflow

Excel create named sheets with populated data

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related