[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

open excel spreadsheet with data from query

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:
        Randomize(0)
        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 ")
0
AnuPutcha
Asked:
AnuPutcha
1 Solution
 
sachiekCommented:
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"];
                MyDataGrid.DataBind();
                MyDataGrid.RenderControl(writer);
                base.Render (writer);
            }

Below is a sample how it can be done.


Sachi
0
 
Ramesh SrinivasTechnical ConsultantCommented:
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.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        '''' 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)

        Me.ClearControls(DataGrid1)
        DataGrid1.RenderControl(oHtmlTextWriter)
        Response.Write(oStringWriter.ToString())
        Response.End()

regards,

KS
0
 
nitinkprCommented:
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 &= "window.open('" &  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
RegisterStartupScript("OpenExcelFile",strScript)

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

Thanks,
Nitin


0
 
AnuPutchaAuthor Commented:
Thanks guys
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now