CODEWITHSUNDEEP
c#openxmlopenxml-sdk
Horrible Programmer
Horrible Programmer

Reputation: 105

Creating multiple excel spreadsheet

I use the following code 

// Create a spreadsheet document
            SpreadsheetDocument spreadsheetDocument  = SpreadsheetDocument.Create(excelLocation + @"\example1.xlsx", 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());
            UInt32Value sheetCount = 0;

            foreach (string value in Holder.country) 
            {
                // Append a new worksheet and associate it with the workbook.

                Sheet sheet = new Sheet() { 
                    Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), 
                    SheetId = sheetCount, 
                    Name = value
                };
                sheets.Append(sheet);
                sheetCount++;
            }


            workbookpart.Workbook.Save();

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

But it always told me

Excel found unreadable content in"example1.xlsx". Do you want to recover the contents of this workbook? ...

I read something on the internet and they say it's due to the sheetCount. How possible is it in this as I had add 1 every loop.

Upvotes: 1

Views: 2719

Answers (1)

Chris
Chris

Reputation: 8647

Some confusion here.

  • You need to add a worksheetpart for each sheet you create. Here, you refer at only one worksheetpart for all sheets.
  • The minimum value for sheetId is 1, not 0.
// Create a spreadsheet document
SpreadsheetDocument spreadsheetDocument  = SpreadsheetDocument.Create(excelLocation + @"\example1.xlsx", SpreadsheetDocumentType.Workbook);

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

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

UInt32Value sheetCount = 1; // Mimimum value is 1

foreach (string value in Holder.country) 
{

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

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { 
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), 
                SheetId = sheetCount, 
                Name = value
            };
    sheets.Append(sheet);
    sheetCount++;
}

workbookpart.Workbook.Save();

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

Upvotes: 4

Related Questions