0

trying to just create a simple OpenXML document based on http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML i moved it over to asp.net (below) on my dev server but the file that is created is corrupted and will not open. am i doing something obviously stupid?

Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    Response.Clear()
    Response.Buffer = False
    Dim myoutputstream As New MemoryStream
    Dim myPackage As SpreadsheetDocument = CreateWorkbook(myoutputstream)

    AddWorksheet(myPackage, "Test")
    myPackage.WorkbookPart.Workbook.Save()

    Response.AddHeader("Content-Disposition", ("attachment") & "; filename=Report.xlsx")
    Response.ContentType = "application/octet-stream"
    Response.AddHeader("Accept-Header", myoutputstream.Length.ToString())
    Response.AddHeader("Content-Length", myoutputstream.Length.ToString())
    Response.BinaryWrite(myoutputstream.GetBuffer())
    Response.End()

End Sub

Public Shared Function CreateWorkbook(ByVal MyMemoryStream As MemoryStream) As SpreadsheetDocument
    Dim spreadSheet As SpreadsheetDocument = Nothing
    Dim sharedStringTablePart As SharedStringTablePart
    Dim workbookStylesPart As WorkbookStylesPart

    Try
        ' Create the Excel workbook
        spreadSheet = SpreadsheetDocument.Create(MyMemoryStream, SpreadsheetDocumentType.Workbook, False)

        ' Create the parts and the corresponding objects
        ' Workbook
        spreadSheet.AddWorkbookPart()
        spreadSheet.WorkbookPart.Workbook = New DocumentFormat.OpenXml.Spreadsheet.Workbook()
        spreadSheet.WorkbookPart.Workbook.Save()

        ' Shared string table
        sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
        sharedStringTablePart.SharedStringTable = New DocumentFormat.OpenXml.Spreadsheet.SharedStringTable()
        sharedStringTablePart.SharedStringTable.Save()

        ' Sheets collection
        spreadSheet.WorkbookPart.Workbook.Sheets = New DocumentFormat.OpenXml.Spreadsheet.Sheets()
        spreadSheet.WorkbookPart.Workbook.Save()

        ' Stylesheet
        workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart(Of WorkbookStylesPart)()
        workbookStylesPart.Stylesheet = New DocumentFormat.OpenXml.Spreadsheet.Stylesheet()
        workbookStylesPart.Stylesheet.Save()
    Catch exception As System.Exception

    End Try

    Return spreadSheet
End Function

Public Shared Function AddWorksheet(ByVal spreadsheet As SpreadsheetDocument, ByVal name As String) As Boolean
    Dim sheets As DocumentFormat.OpenXml.Spreadsheet.Sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Sheets)()
    Dim sheet As DocumentFormat.OpenXml.Spreadsheet.Sheet
    Dim worksheetPart As WorksheetPart

    ' Add the worksheetpart
    worksheetPart = spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
    worksheetPart.Worksheet = New DocumentFormat.OpenXml.Spreadsheet.Worksheet(New DocumentFormat.OpenXml.Spreadsheet.SheetData())
    worksheetPart.Worksheet.Save()

    ' Add the sheet and make relation to workbook
    sheet = New DocumentFormat.OpenXml.Spreadsheet.Sheet With {
       .Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
       .SheetId = (spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
       .Name = name}

    sheets.Append(sheet)
    spreadsheet.WorkbookPart.Workbook.Save()

    Return True
End Function
user1272386
  • 49
  • 2
  • 10

1 Answers1

0

Do you fill any data into spreadsheet? My main reason why my generated excel files gets corrupted is really simple : excel start count from 1 but not from 0, and this is really important to remember.

Here is my example, but on c#

    public void GetTemplate(Page page, DownloadExcelTemplateModel model)
    {
        // generating file
        byte[] bytes = GetFile(model);
        PutExcelFileToResponce(page, bytes);
    }

    private static void PutExcelFileToResponce(Page page, byte[] bytes)
    {
        // do clear
        page.Response.Clear();
        page.Response.ClearHeaders();
        page.Response.ClearContent();

        // add headers
        page.Response.AddHeader("Content-Length", bytes.Length.ToString(CultureInfo.InvariantCulture));
        page.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "Template.xlsx"));
        page.Response.AddHeader("Content-Type", "application/Excel");

        // set content type
        page.Response.ContentType = "application/vnd.xls";

        // do write binary data to responce stream
        page.Response.BinaryWrite(bytes);

        // finish process
        page.Response.Flush();
        page.Response.End();
    }

    private byte[] GetFile(DownloadExcelTemplateModel model)
    {
        return WebService.GetData(model.Id);
    }
Andriy Zakharko
  • 1,623
  • 2
  • 16
  • 37