Facing error while opening excel after using OpenXML

Question

We are using OpenXML to generate Excel files, and tried with this code, but we're getting an error while trying to open the Excel file:

There is problem with some content in 'test.xslx'. Please let us know if you want to recover.

Code:

public byte[] ExportDatatoExcel()
{
    MemoryStream memoryStream = new MemoryStream();

    SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();

    workbookPart.Workbook = new Workbook();
    Sheets sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());

    // OpenSheet
    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet();
    var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "test" };
    sheets.Append(sheet);

    OpenXmlWriter DataWriter = OpenXmlWriter.Create(worksheetPart);

    // getting error because of writing to worksheet
    DataWriter.WriteStartElement(worksheetPart.Worksheet);
 
    DataWriter.WriteStartElement(new SheetData());
    DataWriter.WriteEndElement();

    DataWriter.Close();

    worksheetPart = null;
    workbookPart.Workbook.Save();

    spreadsheet.Save();
    spreadsheet.Close();

    memoryStream.Position = 0;

    return memoryStream.ToArray();
}

I am getting an issue while trying to open the downloaded file. When I tried to change this line

DataWriter.WriteStartElement(worksheetPart.Worksheet); 

to

DataWriter.WriteStartElement(new Worksheet());

it's working fine, but when I try to check worksheetpart instance, it's showing Worksheet property as "System.IO exception".

Please suggest what the issue might be

Answer 1

I figured out the issue. If we change the line with worksheet,

worksheetPart.Worksheet = new Worksheet();
to worksheetPart.Worksheet = new Worksheet(new SheetData());