openXML spreadsheetdocument return byte array for MVC file download

Question

I'm trying to return a openXML spreadsheetdocument as a byte[] which I can then use to allow my MVC to send that file to a user. here is my spreadsheetdocument method to return the byte array

using (MemoryStream mem = new MemoryStream())
{
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(mem, 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());

    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    //row start
    for (int id = 0; id <= reports.Count(); id++)
    {
        if (id == 0)
        {
            Row contentRow = CreateContentRow(reports.ElementAt(id), true, 2 + id);
            sheetData.AppendChild(contentRow);
        }
        else
        {
            Row contentRow = CreateContentRow(reports.ElementAt((id - 1)), false, 2 + id);
            sheetData.AppendChild(contentRow);
        }
    }

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.
            GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "mySheet"
    };
    sheets.Append(sheet);
    workbookpart.Workbook.Save();
    return mem.ToArray();
}

then in my MVC controller i call it like this

return File(crs.CreateReportSpreadSheet(reports),"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reports.xlsx");

the file downloads, but when you go to open it shows an error message that the file is corrupted. Is there any other way to allow them to download this file?

Cordite

Did you checked the generated file separately? I mean , is your file opens correctly when you save it on disk at "mem.ToArray()" position? I think there should be a problem with your generated file not the way you return it to client. – Hardily

Answer 1

I know that this is late, but I believe it is because you are not calling close on your spreadsheet document after you are finished with it. Make sure you close the document before returning the memory stream to commit all underlying streams used by the document.

// code omitted for clarity...
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
return mem.ToArray()

http://msdn.microsoft.com/en-gb/library/documentformat.openxml.packaging.spreadsheetdocument(v=office.14).aspx

Bananas

Yep that was the answer indeed – Cordite

Answer 2

I had a similar problem with a Word document. I solved this by using the byte array outsite the using, like:

byte[] returnBytes = null;
using (MemoryStream mem = new MemoryStream())
  {
    // your code
    returnBytes = mem.ToArray();
  }

return returnBytes;

Marathi

i'm still getting "excel found unreadable content" I click yes to recover the contents and then it states "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt", I even stop populating the spreadsheet so it was completely empty, but still getting the "is corrupt" – Cordite