Excel spreadsheet in browser instead of saving to hard drive - how?

[JulesBos]

I have some code that dumps a datagrid into an excel spreadsheet and saves it to a C drive. Whilst testing locally this worked fine because I was on my own machine and it saved locally. I've just tested on our intranet server and didn't realise that it would save on the intranet C drive - what I wanted was to save on the user's C drive. I've checked around and believe that this is now going to be nigh on impossible to do, so what I need to do is display the spreadsheet instead of save it. Here's my code, how do I need to amend it?:

'this sends the datagrid and other information to an excel spreadsheet
Dim strFilePath As String
Dim strw As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(strw)
Dim objStreamWriter As System.IO.StreamWriter
Dim strStyle As String = "<style>.text { mso-number-format:\@; } </style>"
strFilePath = "C:\Temp\GSCapabilitySearch.xls"
objStreamWriter = System.IO.File.AppendText(strFilePath)
objStreamWriter.WriteLine(strStyle)

'now get the file ready for export
strw.GetStringBuilder.Append("<B>")
strw.GetStringBuilder.Append("<U>")
strw.GetStringBuilder.Append("<Font Face='Verdana' Size ='2'>")
strw.WriteLine("GS Capability Search Report")
strw.GetStringBuilder.Append("</U>")
strw.GetStringBuilder.Append("</B>")
strw.GetStringBuilder.Append("<br>")
Dim todayDate As String
todayDate = CStr(Today)
strw.WriteLine("Report dated: " + todayDate)
strw.GetStringBuilder.Append("<br>")
strw.GetStringBuilder.Append("</Font>")
strw.GetStringBuilder.Append("<table>")
strw.GetStringBuilder.Append("<tr>")
strw.GetStringBuilder.Append("<td colspan=7 align=left")
strw.GetStringBuilder.Append("</td>")
strw.GetStringBuilder.Append("</tr>")
ResultsDataGrid.RenderControl(oHtmlTextWriter)
strw.GetStringBuilder.Append("</table>")

objStreamWriter.WriteLine(strw.ToString())
objStreamWriter.Close()

Thanks in advance!
Julia

 

[ca8msm]

Yes, you're right that it's practically impossible (as you use an ActiveX component and have your users use IE and accept the component). The reason for this is that you are using Active Server Pages which means that the code runs on the server not the client.

If you want to send the file to the client, you can do so by streaming the file using the Response.BinaryWrite method.




____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[JulesBos]

Thanks for this, could you give me a bit more info on the response.binarywrite method please?

Thanks

Julia

 

[ca8msm]

Have you looked at the help files for the method? If not, I suggest doing so. Here's an online version:

http://msdn2.microsoft.com/en-us/library/system.web.httpresponse.binarywrite.aspx

____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[JulesBos]

Thanks. I had looked at help and, like most help, it seemed to be written for people who already understand the topic and, as I'm new to web development, it went over my head! However, the online version you've given me, is better and I think I may be able to work it out from there.

Julia

 

[DotNetBlocks]

why don't you change the content type of the page and have the user save it where ever they want?

//export to excel
DataGrid_Dimension.AllowPaging = false;
BindGrid();

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(DataGrid_Dimension);
DataGrid_Dimension.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());

Response.End();

This code was found at:

http://www.devx.com/tips/Tip/31237