asked on

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 ")

sachiek

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

ASKER CERTIFIED SOLUTION

Ramesh Srinivas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nitinkpr

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

AnuPutcha

ASKER

Thanks guys