[RESOLVED] Export To Excel

Printable View

Jan 26th, 2012, 09:30 AM
ravininave

[RESOLVED] Export To Excel

Hi, I'm exporting data to Excel. I've to put some headings, some data from datatable and again some footer text. For that I'm using following Code. It works fine locally but when I publish it it gives error while saving excel file as I'm hosting it on shared server. Can someone Help?

Code:

'--SOME HEADERS Dim HtmlStream As String HtmlStream = "<html><body> <div style='padding:15px;font-family:Tahoma;font-size:11px;'>" HtmlStream += "<table cellpadding='0' cellspacing='0' border='1' style='line-height:20px; border:solid 1px;font-size:11px;' width='100%'><tr><td colspan='8' style='height:20px;'></td></tr> <tr><Td colspan='8'>" HtmlStream += "<div style='padding-left:20px;'>MULTIPLE TRANSACTIONS REQUEST FORM Date -" + DateTime.Now + " Name of Applicant - ABC CO. LTD.NEFT " '--CODE FROM DATATABLE For i = 0 To mSeta.Rows.Count - 1 If Len(Trim(mSeta.Rows(i)(3))) > 0 Then If mSeta.Rows(i)(8) > 500 Then HtmlStream += "<tr style='line-height:30px;'><td style='width:10px;' align='right'>" + Convert.ToString(sNo) + "</td>" HtmlStream += "<td align='right'>" + Convert.ToString(mSeta.Rows(i)(8)) + "</td><td align='left'>804-0-507995-6</td>" HtmlStream += "<td align='left'>" + mSeta.Rows(i)(6) + "</td>" HtmlStream += "<td align='left'>" + mSeta.Rows(i)(3) + "," + mSeta.Rows(i)(4) + "</td>" HtmlStream += "<td align='left'>" + mSeta.Rows(i)(5) + "</td>" HtmlStream += "<td align='left'>" + mSeta.Rows(i)(1) + "</td><td align='left'>INCENTIVE</td></tr>" MyTotal = MyTotal + mSeta.Rows(i)(8) sNo = sNo + 1 End If End If Next '--SOME FOOTER HtmlStream += "<tr style='line-height:20px;'><td colspan='8' style='padding-left:50px;'>Total Amount to be transferred. " + Convert.ToString(MyTotal) + " " HtmlStream += "Total Number Of Items " + Convert.ToString(sNo - 1) + " " '--NOW SAVING Dim file As New System.IO.StreamWriter("Commission.xls") file.WriteLine(HtmlStream) ';// now writing the stream to the file file.Close() ';// closing the streamwriter '// Provide the actual path if not using this default path Process.Start("Commission.xls") ';// Opening the excel sheet

It gives error while saving
Jan 26th, 2012, 10:24 AM
Fishcake

Re: Export To Excel

With this line Process.Start("Commission.xls") you are trying to start a process on the web server. This works fine locally as your development machine IS the webserver.

What are you trying to achieve with this? Even if you had permissions and the server had Excel installed on it the .xls file would open on a server in a datacentre somewhere that no-one may ever see.

Are you really wanting to save the excel file on the server or are you wanting to add it to the response stream to deliver it to the end user?
Jan 26th, 2012, 12:08 PM
ravininave

Re: Export To Excel

Quote:

Originally Posted by Fishcake

With this line Process.Start("Commission.xls") you are trying to start a process on the web server. This works fine locally as your development machine IS the webserver.

What are you trying to achieve with this? Even if you had permissions and the server had Excel installed on it the .xls file would open on a server in a datacentre somewhere that no-one may ever see.

Are you really wanting to save the excel file on the server or are you wanting to add it to the response stream to deliver it to the end user?

No I don't want to save it on server. I just wish to save it on client's machine or it should ask where to save. What should I write for that.?
Jan 27th, 2012, 02:51 AM
gep13

Re: Export To Excel

Hello,

Have a look at this thread here:

http://www.vbforums.com/showthread.php?t=556335

Hope that helps!

Gary
Jan 27th, 2012, 04:50 AM
ravininave

Re: Export To Excel

Will reply you shortly. Thanx
Jan 28th, 2012, 09:33 AM
ravininave

Re: Export To Excel

I'm trying this

Try
Dim strFileName As String = "Commission.xls"
Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment;filename=" & strFileName)
Response.AddHeader("Pragma", "no-cache")
Response.Write(HtmlStream.ToString)
Response.End()
Catch ex As Exception

End Try
Feb 1st, 2012, 02:01 AM
gep13

Re: Export To Excel

Hello ravininave,

Did this work for you? Or are you still having issues?

If you are having issues, can you let us know what they are?

Gary
Feb 2nd, 2012, 05:02 AM
ravininave

Re: Export To Excel

Oh Gep 13, Sorry for not replying. Yes it's working very well now.Thanks Again.
Feb 2nd, 2012, 02:53 PM
gep13

Re: Export To Excel

Quote:

Originally Posted by ravininave

Oh Gep 13, Sorry for not replying. Yes it's working very well now.Thanks Again.

Glad to hear it! :)