Problems exporting to CSV on the server

[Radu]

Hi. I have the following problem: The user needs to setup a request on
a website, by deciding on a lot of questions. I want the website to
dump an excel (CSV) file into a shared folder somewhere on the
network, so that other tools can read these request files and act on
them.

So on my confirmation page I have a "PREVIOUS" and a "NEXT" button. On
the NEXT I currently have code which does many things (fills SQL
Server tables, sends confirmation emails, etc). I want to add some
more code which would export this request, as described above.

At some point, after I determine strNewFileName which would be like
"H:\PEOPLE\EPS\EPS 2nd version\RequestZZZ.xls"
I call:
Call ExportToExcel(strNewFileName)
where

Private Function ExportToExcel(ByVal strFileName As String) As Boolean

Dim stringWrite As System.IO.StringWriter = New
System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New
HtmlTextWriter(stringWrite)

Try
Response.Clear()
Response.AddHeader("content-disposition", "inline;filename='" &
strFileName & "'")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"

'Get the HTML for the control:
dataGridSettingsLocations.RenderControl(htmlWrite)
'Write the HTML back to the browser:
Response.Write(stringWrite.ToString())
Response.End()
Catch ex As Exception
Throw ex
Finally
stringWrite.Dispose()
stringWrite = Nothing
htmlWrite.Dispose()
htmlWrite = Nothing
End Try
End Function

and after this call, I continue with other function calls, and in the
end, last line for cmdNext_Click, I say:

'I use this overloaded form because otherwise I get a
ThreadAbortException error - see article http://support.microsoft.com/kb/312629

Response.Redirect("Acknowledgement.aspx", False)



I have a number of problems:
1. If I use
Response.End()
in ExportToExcel, I get a 'thread ended' error - logical enough, I
think.

2. If I don't use it, the code above doesn't give me errors, but does
not save my file anywhere either...

3. Anyway, I'm not sure, with my VERY LIMITED ASP knowledge, that for
saving on the server and not on the client I should write code in
"RESPONSE", as you can see above. If not, how could I do what I have
to do ?

I would appreciate very much any answer - I have lost too much time on
this one already.

Thank you.
Alex.

 

[Alexey Smirnov]

Hi. I have the following problem: The user needs to setup a request on
a website, by deciding on a lot of questions. I want the website to
dump an excel (CSV) file into a shared folder somewhere on the
network, so that other tools can read these request files and act on
them.

So on my confirmation page I have a "PREVIOUS" and a "NEXT" button. On
the NEXT I currently have code which does many things (fills SQL
Server tables, sends confirmation emails, etc). I want to add some
more code which would export this request, as described above.

At some point, after I determine strNewFileName which would be like
"H:\PEOPLE\EPS\EPS 2nd version\RequestZZZ.xls"
I call:
Call ExportToExcel(strNewFileName)
where

Private Function ExportToExcel(ByVal strFileName As String) As Boolean

Dim stringWrite As System.IO.StringWriter = New
System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New
HtmlTextWriter(stringWrite)

Try
Response.Clear()
Response.AddHeader("content-disposition", "inline;filename='" &
strFileName & "'")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"

'Get the HTML for the control:
dataGridSettingsLocations.RenderControl(htmlWrite)
'Write the HTML back to the browser:
Response.Write(stringWrite.ToString())
Response.End()
Catch ex As Exception
Throw ex
Finally
stringWrite.Dispose()
stringWrite = Nothing
htmlWrite.Dispose()
htmlWrite = Nothing
End Try
End Function

and after this call, I continue with other function calls, and in the
end, last line for cmdNext_Click, I say:

'I use this overloaded form because otherwise I get a
ThreadAbortException error - see articlehttp://support.microsoft.com/kb/312629

Response.Redirect("Acknowledgement.aspx", False)

I have a number of problems:
1. If I use
Response.End()
in ExportToExcel, I get a 'thread ended' error - logical enough, I
think.

2. If I don't use it, the code above doesn't give me errors, but does
not save my file anywhere either...

3. Anyway, I'm not sure, with my VERY LIMITED ASP knowledge, that for
saving on the server and not on the client I should write code in
"RESPONSE", as you can see above. If not, how could I do what I have
to do ?

I would appreciate very much any answer - I have lost too much time on
this one already.

Thank you.
Alex.

Alex, you are wrong about that. It will not "dump a file into a
folder", it does output in browser. Remove Try...Catch block to see
that the code is not executed.

Where do you get the CSV-data from? If user has to upload the file,
you will need the Upload Control to save the file, if it's a dump from
the database, use the System.IO.File Class to create the file.

 

[Radu]

Alex, you are wrong about that. It will not "dump a file into a
folder", it does output in browser. Remove Try...Catch block to see
that the code is not executed.

Where do you get the CSV-data from? If user has to upload the file,
you will need the Upload Control to save the file, if it's a dump from
the database, use the System.IO.File Class to create the file.- Hide quoted text -

- Show quoted text -

Alexey, thanks for answering.

No. the user has nothing to do with this - he only sets up his request
by using the website. The details of this request are presented to him
for approval one last time in a datagrid named
dataGridSettingsLocations, and if he agrees, he clicks on the NEXT
button, which, among many other things, has to export the details from
the grid into a CSV/Excel file in a shared folder on the intranet,
therefore I have the line:
dataGridSettingsLocations.RenderControl(htmlWrite)
in my function.

I took this code from the net and adapted it, and it seems to be
alright to me, but it does not do anything.... No file is saved.

Please note that I need to save that file WITHOUT any Open/SaveAs
dialog !

Thanks a lot.
Alex.

 

[Alexey Smirnov]

I took this code from the net and adapted it, and it seems to be
alright to me, but it does not do anything.... No file is saved.

It's not alright to you, believe me.

You function has to be changed in the following way:

Private Function ExportToExcel(ByVal strFileName As String) As
Boolean

' #1 Get data out of the datagrid/datasource
' #2 Split the "columns" by the delimeter
' #3 Save data to the text file

End Function

and last but not least - Functions return a value, in you case it
should return a boolean value. In the same time you return no value
and use the Call Statement. Change it into Sub. Sub procedures are
methods which do not return a value.

Now, what part of the function/sub is not clear for you?

 

[Radu]

It's not alright to you, believe me.

You function has to be changed in the following way:

Private Function ExportToExcel(ByVal strFileName As String) As
Boolean

' #1 Get data out of the datagrid/datasource
' #2 Split the "columns" by the delimeter
' #3 Save data to the text file

End Function

and last but not least - Functions return a value, in you case it
should return a boolean value. In the same time you return no value
and use the Call Statement. Change it into Sub. Sub procedures are
methods which do not return a value.

Now, what part of the function/sub is not clear for you?

Sorry, Alexey - this is my very first web application, and it's not
easy, coming from a desktop programming mindset. Especially that this
is my first real app in NET as well.

When I say

'Get the HTML for the control:
dataGridSettingsLocations.RenderControl(htmlWrite)
'Write the HTML back to the browser:
Response.Write(stringWrite.ToString())

this is what I get in stringWrite (sample):

<table title="Your settings" id="dataGridSettingsLocations">
<tr style="background-color:Blue;font-weight:bold;">
<td>Asset Utilization</td>
<td>DSO</td>
<td>DSO-1</td>
<td>Default for location</td>
</tr>
</table>

which is correct. It seems to me that I don't have to go through the
contents of the datagrid and build the csv "by hand", I already have
it here...

I did the following test - I have put the
Response.End()
back. This, of course, gave me a run-time error, but at least it did
the following:
1. It prompted me to save/open (not what I wanted, but hey, it's on
the right track)
2. It saved the excel file - exactly what I needed, albeit with
formatting, but that's not bad, since these excel files are not meant
to be read by humans anyway - they will act as datasources for another
(MSAccess) program.

So:
- With Response.End() it almost works (although it does not take into
account the filename I specify, and prompts me instead), and gives a
run-time error (System.Threading.ThreadAbortException)
- Without Response.End() , it does not trigger any error, but does not
do anything either.

Thanks again.
Alex.

 

[Alexey Smirnov]

<table title="Your settings" id="dataGridSettingsLocations">
<tr style="background-color:Blue;font-weight:bold;">
<td>Asset Utilization</td>
<td>DSO</td>
<td>DSO-1</td>
<td>Default for location</td>
</tr>
</table>

which is correct. It seems to me that I don't have to go through the
contents of the datagrid and build the csv "by hand", I already have
it here...

Well, do think that the HTML code above is a CSV/Excel compatible?

Open Notepad, save the code as *.txt/*.csv/*.xls/etc and try to open
it in Excel to see what I mean.

Then save any well-formed Excel file as a CSV and open it in Notepad
to see how the CSV-file is looks like.

I did the following test - I have put the
Response.End()

The problem isn't in Response.End(). Your code (above) does a file
download (from server to your local computer).

This is the reason why it prompted to save/open.