[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5093
  • Last Modified:

Asp.Net 2.0 Vb.Net View/Save Excel Worksheet from Infragistics Excel Workbook object, not file

i Have an excel spreadsheet I create programatically with infragistics controls, ASP.Net 2.0 VB.Net.  After the workbook is created, I want to send it to the client giving them the option to open or save it.  All clients have Office 2007 installed.  I would like to do this without saving the file to a disk on the server.  Is there a way to go from the workbook object to saving the file onthe client side?
0
legmaker
Asked:
legmaker
  • 4
  • 2
  • 2
  • +1
1 Solution
 
samtran0331Commented:
without seeing some of your code, it's difficult to say for sure...but usually...if a file is being saved on the server and then sent back to the client, there is a filestream object involved...and usually...instead of using a filestream, you can use a memorystream and bypass the step of having a physical file saved on the server first....if you google "asp.net memorystream" you can see if the technique fits your needs...else post some code and we can help you further...the infragistics control might throw a monkeywrench into what I just said...
0
 
legmakerAuthor Commented:
That is the approach I started to take.  I need to send the memory stream to the client so they can open it as a workbook.::::
 
    Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
            Dim wb As New Excel.Workbook
            Dim productHeading As Integer = 1
            Dim mstr As New MemoryStream()
            wb.Worksheets.Add("Sheet1")
            Dim ws As Excel.Worksheet = wb.Worksheets("Sheet1")
            With ws
                .Rows.Item(0).Cells(1).Value = "Example Value"
            End With

            Excel.BIFF8Writer.WriteWorkbookToStream(wb, mstr)

           'Put code to open stream on client here.

        End Sub
0
 
surajgupthaCommented:
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();

This is an example of how you could write the excel file to the response that way not having to store the file anywhere in the server. In the above case, what is written is present as a text but in your case a Stream.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
legmakerAuthor Commented:
the file is not in a datagrid.  it is in an infragistics.excel object.  Also, vb.net although I speak C#.
0
 
surajgupthaCommented:
Yes i know it is a Infragistics excel object :). I just put it over there as an example
I was thinking more in the lines of

Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;

Excel.BIFF8Writer.WriteWorkbookToStream(wb, mstr)

Response.Write(mstr.ToString()); << This is the part you have to work on trying to convert the memory stream to a enconding that will let your excel data across
Response.End();
0
 
legmakerAuthor Commented:
I have used that code in a previous version for exporting regular datagrids and I can't get it to work... Thanks for the effort but that doesn't help me at all.....
0
 
samtran0331Commented:
maybe this:

        Dim wb As New Excel.Workbook
        Dim productHeading As Integer = 1
        Dim mstr As New IO.MemoryStream()
        wb.Worksheets.Add("Sheet1")
        Dim ws As Excel.Worksheet = wb.Worksheets("Sheet1")
        With ws
            .Rows.Item(0).Cells(1).Value = "Example Value"
        End With

        Excel.BIFF8Writer.WriteWorkbookToStream(wb, mstr)
        'Put code to open stream on client here.
        Dim arByte As Byte() = mstr.ToArray() 'Byte array to hold stream to write
        With Context.Response
            .BufferOutput = True
            .Clear()
            .ClearHeaders()
            .ClearContent()
            .ContentType = "application/vnd.ms-excel"
            .Expires = 0
            .Cache.SetCacheability(HttpCacheability.NoCache)
            .Cache.SetNoServerCaching()
            .Cache.SetNoStore()
            .Cache.SetMaxAge(System.TimeSpan.Zero)
            .OutputStream.Write(arByte, 0, arByte.Length) 'send the byte array to client
            .End()
        End With
0
 
hneagleCommented:
I have used this in VB.NET 2003 with good results:

Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment;filename=DocTypeInfo.xls")
Infragistics.Excel.BIFF8Writer.WriteWorkbookToStream(wkBook, Response.OutputStream)
Response.End()
0
 
legmakerAuthor Commented:
Worked great!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now