How to I create and return an Excel File with a controller in MVC 4 in vb.net?

Question

I am using ExcelLibrary enter link description here because I don't want to install Microsoft Office Excel (microsoft.interop.office.excel)

 Public Function ObtenerExcel() As ActionResult
      Dim workbook As New Workbook()
      Dim worksheet As New Worksheet("Sheet1")
      worksheet.Cells(5, 5) = New Cell(999999)
      worksheet.Cells(10, 10) = New Cell(12354)
      workbook.Worksheets.Add(worksheet)

      Dim stream As New System.IO.MemoryStream
      workbook.SaveToStream(stream)
      stream.Position = 0

      Dim buffer(4096) As Byte
      stream.Read(buffer, 0, buffer.Length)

      Return File(buffer, "application/vnd.ms-excel", "mytestfile.xls")
    End Function

This code return an excel file, but when I try to open this file, It shows an error message (Excel found unreadable content in 'text.xls'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.) and it doesn't shows anything.

I working on Windows 8.1 (64 bits) and Microsoft Office 2013

what is wrong with your code? are you getting an error? are you not getting an excel file back? can you provide more information as to what the issue is? — Matt Bodily
– Matt Bodily, Commented Jul 30, 2014 at 21:55
It looks like you're only returning the first 4096 bytes of the excel file, not the whole thing. — Paul Abbott
– Paul Abbott, Commented Jul 30, 2014 at 22:37
The site you reference includes a wiki entry on how to do what you're asking to do: code.google.com/p/excel-generator/wiki/MVC_ExcelResult — Michael Dunlap
– Michael Dunlap, Commented Jul 30, 2014 at 22:55

Michael Dunlap · Accepted Answer · 2014-07-30 22:39:06Z

5

You should use the Stream overload of File(...). The code you have written appears to only return the first 4096 bytes of the file, the amount you copied into the buffer. You should use the stream directly instead.

Dim stream As New System.IO.MemoryStream
workbook.SaveToStream(stream)
stream.Position = 0

Return File(stream, "application/vnd.ms-excel", "mytestfile.xls")

answered Jul 30, 2014 at 22:39

Michael Dunlap

4,31028 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EduardoUstarez Over a year ago

This is part of the solution. I'll give you a point later.(When I'll get the 15 points). Thanks.

EduardoUstarez · Accepted Answer · 2014-07-31 14:51:09Z

Well. I figured out solution, I think this problem is the size of the excel file, but I am not sure it. So I found this "solution": when I create my workbook, I fill 200 cells of the first sheet with null values to reach this size.

Public Function ObtenerExcel() As ActionResult
  Dim stream As New System.IO.MemoryStream
  Dim doc As CompoundDocument = CompoundDocument.Create(stream)
  Dim memStream As New MemoryStream
  Dim workbook As New Workbook()
  Dim worksheet As New Worksheet("Hoja")
  For Index As Integer = 0 To 200
    worksheet.Cells(Index, 0) = New Cell(Nothing)
  Next
  workbook.Worksheets.Add(worksheet)
  workbook.SaveToStream(stream)
    stream.Position = 0

  Return File(stream, "application/vnd.ms-excel", "mytestfile.xls")
End Function

Collectives™ on Stack Overflow

How to I create and return an Excel File with a controller in MVC 4 in vb.net?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related