How to open file in excel without saving to harddrive (VB)

Hi ,

This is a similar question to <A href="https://forum.aspose.com/t/87137</A></P> <P>I want to generate a spreadsheet (100% generated - no template) and when it has been generated I then want it to open in excel with a random filename WITHOUT saving any files onto the hard drive.</P> <P>My users can generate more than one report at a time so if they don;t have random names then excel with throw an error due to duplicate filenames. This allows means that the hard drive would fill up with lots of XLS files.</P> <P>I don't want to include a tidy up routine to delete the files fom the hard drive .</P> <P>I have tried playing with memory streams but got no where !</P> <P>The code I use at the moment is (I use VB.Net)</P><FONT size=2> <P>workbook.Save(fileName, FileFormatType.Excel2003)</P> <P>Process.Start(fileName)</P> <P> </P> <P>Thanks</P> <P>Kevin</P> <P>FYI - My trail is almost complete and Im really happy with the results ! The speed increase from my origianl excel automation routine is increadable !</P></FONT>

Hi,

Thank you for considering Aspose.

Please see the following code to save the file in memory stream and opens it to the excel directly,


Sample Code:

Dim wb As Workbook = New Workbook()
Dim worksheet As Worksheet = wb.Worksheets(0)
worksheet.Cells("B5").PutValue("a")
worksheet.Cells("B6").PutValue("b")
worksheet.Cells("B7").PutValue("c")
worksheet.Cells("A11").PutValue("d")
worksheet.Cells("A12").PutValue("e")
'Setting the name of the newly added worksheet
worksheet.Name = "My Worksheet"

Dim fs As New MemoryStream()

fs = wb.SaveToStream()
Response.Clear()
'Specify the document type
Response.ContentType = "application/vnd.ms-excel"
'Other options:
'Response.ContentType = "text/plain"
'Response.ContentType = "text/html"

'Specify how the document is sent to the browser.
Response.AddHeader("content-disposition", "attachment; filename=MyBook.xls")

'Another option could be:
'Response.AddHeader "content-disposition","inline; filename=MyBook.xls";

'Get data bytes from the stream and send it to the response.
Dim bytes As Byte()
bytes = fs.ToArray()

Response.BinaryWrite(bytes)
Response.End()



Thank You & Best Regards,




Forgive me for being stupid but which class is Response a member of ??

I get name 'Response' is not declared.

I qualified Memorystream as system.io.memory stream but response is not a member of system.io.

Thanks

Kev

Hi,

Thank you for considering Aspose.

Response belongs to System.Web.HttpContext Class.

Thank You & Best Regards,

Hi ,

I referenced system.web and then used the following code but I it throws an exception with the following error message : ' Object reference not set to an instance of an object' !

Help !

Dim fs As New System.IO.MemoryStream

fs = workbook.SaveToStream()

System.Web.HttpContext.Current.Response.Clear()

'Specify the document type

System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"

'Other options:

'System.Web.HttpContext.Current.Response.ContentType = "text/plain"

'System.Web.HttpContext.Current.Response.ContentType = "text/html"

'Specify how the document is sent to the browser.

System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=MyBook.xls")

'Another option could be:'Response.AddHeader "content-disposition","inline; filename=MyBook.xls";

'Get data bytes from the stream and send it to the response.

Dim bytes As Byte()

bytes = fs.ToArray()

System.Web.HttpContext.Current.Response.BinaryWrite(bytes)

System.Web.HttpContext.Current.Response.End()

Hi Kevin,

Thank you for considering Aspose.

Well, your code works fine with me. This error is caused when some object in the code is used without instantiating it. Please add breakpoint in you code (may be at the start of your shared code) and check in the debug mode, which object is actually causing the problem. Then we can figure out what can be the problem.

Thank You & Best Regards,

The exception was caused by this line of code

System.Web.HttpContext.Current.Response.Clear()

Thanks

Kevin

Just in case here is the whole sub routine:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim fileName As String = "test.xls"

Dim workbook As Workbook = New Workbook

workbook.Worksheets.Add()

Dim worksheet As Worksheet = workbook.Worksheets(0)

'Setting the foreground color of the "A1" cell to yellow

worksheet.Cells("A1").PutValue("Test")

worksheet.Cells("A1").Style.Font.IsBold = True

worksheet.Cells("B1").PutValue("Test2")

worksheet.Cells("B1").Style.Font.IsItalic = True

worksheet.Cells("B1").Style.Font.Color = Color.Red

worksheet.Cells("B1").Style.Pattern = BackgroundType.Solid

worksheet.Cells("B1").Style.ForegroundColor = Color.Yellow

worksheet.Cells("A1").Style.ForegroundColor = Color.Yellow

'Setting the background pattern of the "A1" cell to vertical stripe

worksheet.Cells("A1").Style.Pattern = BackgroundType.None

'Setting the foreground color of the "A2" cell to blue

'worksheet.Cells("A2").Style.ForegroundColor = Color.Blue

'Setting the background color of the "A2" cell to yellow

worksheet.Cells("A2").Style.BackgroundColor = Color.Yellow

'Setting the background pattern of the "A2" cell to vertical stripe

worksheet.Cells("A2").Style.Pattern = BackgroundType.DiagonalCrosshatch

'Saving the Excel file

'workbook.Save(fileName, FileFormatType.Excel2003)

'Process.Start(fileName)

Dim fs As New System.IO.MemoryStream

fs = workbook.SaveToStream()

System.Web.HttpContext.Current.Response.Clear()

'Specify the document type

System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"

'Other options:

'System.Web.HttpContext.Current.Response.ContentType = "text/plain"

'System.Web.HttpContext.Current.Response.ContentType = "text/html"

'Specify how the document is sent to the browser.

System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=MyBook.xls")

'Another option could be:'Response.AddHeader "content-disposition","inline; filename=MyBook.xls";

'Get data bytes from the stream and send it to the response.

Dim bytes As Byte()

bytes = fs.ToArray()

System.Web.HttpContext.Current.Response.BinaryWrite(bytes)

System.Web.HttpContext.Current.Response.End()

End Sub

Hi,

Thank you for considering Aspose.

Well, I have tested your provided code and it works perfectly fine. It seems that your Response object is not working. Please confirm that you are using this code in a web page and class is inherited from System.Web.UI.Page. If the web page is inherited from System.Web.UI.Page then please post your sample application here, so we can check what the actual cause is.

Thank You & Best Regards,

My apologies , i thought I had mentioned on my firt post that this is a webforms application but I haven't

So that explains why it doesn't work but how do i make it work for a webforms application.

Thanks

Kev

Hi,

Thank you for considering Aspose.

Well, I am not very clear about what you meant by “Web Form Application”, do you mean you are using Web Project application. In a web project application you create web forms (aspx forms). If that is the case then your web form (in which you are using your mentioned code) will be inherited from System.Web.UI.Page and the solution provided should work. Please confirm that you are using Web application project. Also, if possible, please create a sample application and post it here or share your source files (aspx & aspx.vb) , so we can look into your issue.

Thank You & Best Regards,

Nausherwan, I must apologize again ! Yesterday was a bad day !!

Its a WINforms application !!

I must try and get some more sleep before I type !! :-) LOL

Sorry for the confusion.

Kev

Hi Kevin,

Thank you for considering Aspose.

Well, In case of WinForms you cannot use the Response object. You have to save the file (temporarily) to your hard disk then you can view it using System.Diagnostics.Process.Start API. After that you can delete it from the disk using System.IO.File.Delete() API.

Thank You & Best Regards,