c# - Simple excel creation does not open file is corrupt

Question

I am trying to create a simple excel file with multiple sheets using open xml, unfortunately the file does not open after it's being created.

After the file is generated, when I open it with Microsoft Excel it says

We found a problem, do you want to recover as much as we can?

using (SpreadsheetDocument spreedDoc = SpreadsheetDocument.Create(filePath,
    DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
    WorkbookPart wbPart = spreedDoc.WorkbookPart;

    wbPart = spreedDoc.AddWorkbookPart();
    wbPart.Workbook = new Workbook();

    Sheets sheets = wbPart.Workbook.AppendChild(new Sheets());

    foreach (var sheetData in excelSheetData)
    {
        // Add a blank WorksheetPart.
        WorksheetPart worksheetPart = wbPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        string relationshipId = wbPart.GetIdOfPart(worksheetPart);

        // 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 sheetNameToWrite = sheetName;
        if (string.IsNullOrWhiteSpace(sheetNameToWrite))
        {
            sheetNameToWrite = "Sheet"+sheetId;
        }
        // Append the new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
        sheets.AppendChild(sheet);
    }

    //wbPart.Workbook.Sheets.AppendChild(sheet);
    wbPart.Workbook.Save();
}

On trying to Repair in excel gives below message

-<repairedRecords summary="Following is a list of repairs:">

<repairedRecord>Repaired Records: Worksheet properties from /xl/workbook.xml part (Workbook)</repairedRecord>

</repairedRecords>

</recoveryLog>

标签： c#error-handlingopenxmlopenxml-sdk

Answer 1

Have you seen this? http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

The necessary steps to create a functional excel file with multiple worksheets in OpenXML (that work for me) are as follows:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
        {
            spreadsheet.AddWorkbookPart();
            spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

            WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
            Stylesheet stylesheet = new Stylesheet();
            workbookStylesPart.Stylesheet = stylesheet;
            workbookStylesPart.Stylesheet.Save();

            for (int worksheetNo = 1; worksheetNo < worksheetCountYouWantToCreate; worksheetNo++)
            {
                string workSheetID = "rId" + worksheetNo;
                string worksheetName = "worksheet" + worksheetNo;

                WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

                newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

                // write data here
                // ...
                // ...

                newWorksheetPart.Worksheet.Save();

                if (worksheetNo == 1)
                    spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
                {
                    Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
                    SheetId = (uint)worksheetNo,
                    Name = worksheetName
                });
            }
            spreadsheet.WorkbookPart.Workbook.Save();
        }