open excel spreadsheet with data from query

Posted on 2005-05-05
Last Modified: 2010-04-07
I need to open an excel application ( Like I want to see excel spreadsheet open ) with data from my executequery or datagrid. All this should happen on the web server and client user should be able to see the data from query in the excel sheet. If necessary should be able to save it as a local copy on client machine.

I have an application which opens excel instance ( I cannot see it open, but in Task Manager -> Processes, I see an excel instance running ) and creates a chart and saves it as jPG.  Can someone tell me how to actually open this excel file for me to see because if it saves on server and I am running it on client, I won't have path to access the excel file saved on the server.

  'Start Excel and create a new workbook
        oXL = New Excel.Application
        oBook = oXL.Workbooks.Add
        oSheet = oBook.Worksheets.Item(1)

        ' Insert Random data into Cells for the two Series:
        For iRow = 1 To cNumRows
            For iCol = 1 To cNumCols
                aTemp(iRow, iCol) = Int(Rnd() * 40) + 1
            Next iCol
        Next iRow
        oSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp

        'Add a chart object to the first worksheet
        oChart = oSheet.ChartObjects.Add(50, 40, 300, 200).Chart
        oChart.SetSourceData(Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols))
        oChart.Export(FileName:="c:\junk.jgp", FilterName:="JPG")

        ' Make Excel Visible:
        oXL.Visible = True
        Response.Write("  abc ")
        oXL.UserControl = True

        Response.Write("<IMG SRC= ""xlsSaveFile"" >")
        'Response.Write("<IMG SRC=""spacer.gif"" WIDTH=""100"" HEIGHT=""1"" BORDER=""0"">")
        Response.Write("End ")
Question by:AnuPutcha
    LVL 11

    Expert Comment

    I think you should change your logic a bit.

    You can pull required data to a dataset. Then you can simple export it to excel sheet then show it to user.

    protected override void Render(HtmlTextWriter writer)
                      Response.ContentType = "application/ms-word";
                    Response.AddHeader("Content-Disposition",  "inline;filename=test.doc");
                      DataSet ds=new DataSet();
                      ds = getsource();
                    MyDataGrid.DataSource = ds.Tables["Authors"];
                    base.Render (writer);

    Below is a sample how it can be done.

    LVL 11

    Accepted Solution

    If you are using web forms then you should make another button to view the excel sheet and put the following in the sub:

    'export to excel
            Dim filename = Viewstate("filmTitleLong") & "_" & Viewstate("FilterByMediaType")
            Response.Buffer = True
            Response.ContentType = "application/"
            '''' If you want a save dialogue box to appear instead of displaying the excel sheet then add this line & give filename
            ''''''Response.AddHeader("Content-Disposition", "attachment; filename=""" & filename & """")
            Response.ContentEncoding = System.Text.Encoding.UTF7
            Response.Charset = ""
            Me.EnableViewState = False

            Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
            Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)




    Expert Comment

    Hi AnuPutcha,

    You can try this, may be it can help you.

    After writing data into Excel, save the excel file by specifying the physical path and file name.
    After saving the file, write a javascript code to open that file in a popop window.

    strScript="<script language=javascript>"
    'If u want to open in a popop window.
    strScript &= "'" &  strFileName & '")"
    'giving the same physical path and file name that u gave while saving the excel file

    'If u want to open in a same window then
    strScript &= "window.location.href ='" &  strFileName & '""
    strScript &= "</script>"

    'register the javascript

    I hope this solves ur problem. If not, let me think of something else.



    Author Comment

    Thanks guys

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now