导出到Excel(OpenXML)导致内容不可读 [英] Exporting to Excel (OpenXML) results in Unreadable Content

问题描述

我一直在努力为我的xlsx文件创建工作表.添加第一个工作表不是问题，但是当我想要第二个工作表时，导出的xlsx似乎已损坏.谁能向我指出我做错了什么? 注意:我已经尝试过也调用'workbookpart，Workbook.Save();'.在创建第一个工作簿之后，但没有得到所需的结果.

I've been struggeling with creating worksheets for my xlsx file. Adding the first worksheet isn't a problem, but when I want a second sheet, the exported xlsx seems to be corrupt. Who can point out to me what I'm doing wrong? Note: I already tried to also call 'workbookpart,Workbook.Save();' right after creating the first Workbook, but without the required result.

protected void export_Click(object sender, EventArgs e)
{
    ExportToExcel(@"D:\dev\Dotnet4\Excel\test.xlsx");
}

private void ExportToExcel(string filepath)
{
    SpreadsheetDocument spreadsheetDocument;
    WorkbookPart workbookpart;
    CreateSpreadsheet(filepath, out spreadsheetDocument, out workbookpart);

    CreateWorksheet(spreadsheetDocument, workbookpart, "My sheet 1");
    CreateWorksheet(spreadsheetDocument, workbookpart, "My sheet 2");

    workbookpart.Workbook.Save();

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

private static void CreateWorksheet(SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookpart, string worksheetName)
{
    // 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());

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

private static void CreateSpreadsheet(string filepath, out SpreadsheetDocument spreadsheetDocument, out WorkbookPart workbookpart)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

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

推荐答案

我认为您有2个小问题:

I think you have 2 small issues:

1. 您添加的两张纸的SheetId相同，但无效.
2. 每次调用CreateWorksheet时，您都会添加一个新的Sheets元素，但是XML中应该只有一个这样的元素(有很多Sheet，但没有很多Sheets！).

1. The SheetId is the same for both sheets you are adding which is invalid.
2. You are adding a new Sheets element each time you call CreateWorksheet but there should only be one such element in the XML (there are many Sheet's but not many Sheets!).

要求解1，可以使用sheets.ChildElements的计数.由于这是第一次0，第二次是第二次，因此需要在其中添加1.如果愿意，可以将其作为参数添加到CreateWorksheet中.只要它们从1开始并且不同就没有关系.

To solve 1 you could use the count of the sheets.ChildElements. As this is 0 first time round and one the second you'll need to add 1 to it. If you prefer you could take it in to CreateWorksheet as a parameter; it doesn't really matter as long as they start at 1 and are different.

要解决2，您可以对Workbook的Sheets属性执行空检查，并仅在不存在的情况下创建它.

To solve 2 you can perform a null check on the Sheets property of the Workbook and only create it if it doesn't already exist.

下面应该做的是你想做的.

The below should do what you're after.

private static void CreateWorksheet(SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookpart, string worksheetName)
{
    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    if (spreadsheetDocument.WorkbookPart.Workbook.Sheets == null)
    {
        //spreadsheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
        spreadsheetDocument.WorkbookPart.Workbook
                                        .AppendChild<Sheets>(new Sheets());
    }

    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;

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

最后一点是SpreadsheetDocument实现了IDisposable，因此应在使用后丢弃.

One final point is that SpreadsheetDocument implements IDisposable so it should be disposed after use.

这篇关于导出到Excel(OpenXML)导致内容不可读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！