DataTable to File

Question

Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String)
DataTable2CSV(table, filename, vbTab)
End Sub

Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String, _
ByVal sepChar As String)
Dim writer As System.IO.StreamWriter
Try
    writer = New System.IO.StreamWriter(filename)

    ' first write a line with the columns name
    Dim sep As String = ""
    Dim builder As New System.Text.StringBuilder
    For Each col As DataColumn In table.Columns
        builder.Append(sep).Append(col.ColumnName)
        sep = sepChar
    Next
    writer.WriteLine(builder.ToString())

    ' then write all the rows
    For Each row As DataRow In table.Rows
        sep = ""
        builder = New System.Text.StringBuilder

        For Each col As DataColumn In table.Columns
            builder.Append(sep).Append(row(col.ColumnName))
            sep = sepChar
        Next
        writer.WriteLine(builder.ToString())
    Next
Finally
    If Not writer Is Nothing Then writer.Close()
End Try
End Sub

I found this piece of coding on the internet after searching for "DataTable to File VB.net". What I would like to do is output this in filestream, is it possible to set up a response outputstream? Thanks

EDIT: To Clarify (Change up) Here is what I am working with now:

    Protected Sub DoExportFinancials()
    Dim ef As New ExportFinancials()
    ef.Behavior = ExportFinancials.ObjectBehavior.Export
    If ef.Load() = True Then

        Me.GridView4.DataSource = ef.DT
        Me.GridView4.DataBind()

        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
        ' Response.Charset = ""
        'Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Dim stringWrite As StringWriter = New StringWriter
        Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWrite)
        GridView4.RenderControl(htmlWrite)
        Response.Write(stringWrite.ToString())
        Response.End()

    End If

Please elaborate a little on what it is that you're looking to do. Add some pseudocode, if anything, which shows what else you want this code to do. — rory.ap, Apr 07 '15 at 16:59
At the moment this code just adds a file onto my webserver, I want it to output a file to whoever clicks the button to export on their computer. I've read around that says you can only do this with Response.* but havent found anything suitable for this code. When I click the button right now, the file attempts to write itself onto the server where it does not have access. — Solarplex, Apr 07 '15 at 17:02

ASN · Accepted Answer · 2015-04-08T07:17:01.963

1

The below should work,

    Dim FilePath As String = serverPath & "\App_Data\" & FileName
    Response.Clear()
    Response.ContentType = "text/csv"
    Response.AddHeader("Content-Disposition", "attachment; filename=" & FileName)
    Response.TransmitFile(FilePath)
    Response.End()

EDIT: Based on more details of Question, [Change] : How to pass DataTable to Response Stream as CSV/Excel file, without writing it to Disk on server.

One way of accomplishing the above is first to Bind DataTable to a GridView and then the contents of GridView can be passed to the response stream using the below,

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
Response.Charset = "" Response.Cache.SetCacheability(HttpCacheability.NoCache)
Dim stringWrite As StringWriter = New StringWriter
Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWrite)
grid.RenderControl(htmlWrite) Response.Write(stringWrite.ToString())
Response.End()

edited Apr 08 '15 at 07:17

answered Apr 07 '15 at 17:03

ASN

588
4
15

This looks for a file on the webserver, doesnt it? I am trying to go from a datatable to downloaded file – Solarplex Apr 07 '15 at 17:06
1

If I'm right you are using DataTable2CSV to write the contents of the datatable to CSV file. This will create a new CSV file. Pass the same filepath of the CSV file in the above code snippet, your new file should get downloaded. If this is not what you're looking for please elaborate more in detail. – ASN Apr 07 '15 at 17:12
If I had write access to the webserver, this would be the solution. I would really need it to write from DataTable directly to user's download. Maybe I'll just ask the admins for write access, I appreciate the help. – Solarplex Apr 07 '15 at 17:15
1

Thanks for further details if write access is the problem there is a quick solution that comes to mind, you can bind the contents of DataTable to a GridView(May be keep it hidden) and use the below code to export – ASN Apr 07 '15 at 17:19
1

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Response.AddHeader("content-disposition", "attachment;filename=FileName.xls") Response.Charset = "" Response.Cache.SetCacheability(HttpCacheability.NoCache) Dim stringWrite As StringWriter = New StringWriter Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWrite) grid.RenderControl(htmlWrite) Response.Write(stringWrite.ToString()) Response.End() – ASN Apr 07 '15 at 17:20
a-ha! Were getting somewhere, so it downloads a file but nothing in the Excel. I debugged and it shows that the DataTable does have 148 records being placed on the GridView. Any idea why its not transfferring over? edit: updated initial posting to show you what ive done. – Solarplex Apr 07 '15 at 17:47
1

Can't figure out why the Excel is going blank. Please follow the below article http://www.c-sharpcorner.com/UploadFile/0c1bb2/export-gridview-to-excel/ – ASN Apr 07 '15 at 18:09
I figured it out :) it was because I put the GridView as Visible=False. Everything works properly now! Thank You Anand Seniyar! – Solarplex Apr 07 '15 at 19:21

DataTable to File

1 Answers1

Linked