Exporting datatable to excel file with save dialog

Question

I have a datatable being created with various inputs. Sometimes the resulting table is 35000+ rows. Currently, the datatable gets displayed onto a gridview. It loads fine after a couple minutes. Then, theres an option to export the gridview to an excel file. Everytime we have a large table to export, the conversion fails.

My goal is to bypass the gridview step and take the formatted table and put it directly into an excel file. Could also be a csv file if thats faster to write/load, as long as the data table is similar to the gridview output.

I tried the following code here Export DataTable to Excel File. I did my best to convert it to vb, here...

Protected Sub btnExportData_Click(sender As Object, e As EventArgs) Handles btnExportData.Click
    Dim dt As DataTable
    dt = CreateDataSource()
    Dim filename As String = "attachment; filename=DistComplain.xls"
    Response.ClearContent()
    Response.AddHeader("content-disposition", filename)
    Response.ContentType = "application/vnd.ms-excel"
    Dim tab As String = ""
    For Each dc As DataColumn In dt.Columns
        Response.Write((tab + dc.ColumnName))
        tab = "" & vbTab
    Next
    Response.Write("" & vbLf)
    Dim i As Integer
    For Each dr As DataRow In dt.Rows
        tab = ""
        i = 0
        Do While (i < dt.Columns.Count)
            Response.Write((tab + dr(i).ToString))
            tab = "" & vbTab
            i = (i + 1)
        Loop
        Response.Write("" & vbLf)
    Next
    Response.End()
End Sub

CreateDataSource() is the table that gets created in memory. Then theres other buttons that call it to fill the gridview. Right now it successfully complies and runs, and then it successfully creates the file. Although, when the file tries to open I get this error...

This happens when I try both xls and csv files. Something is not getting translated right. Any solutions?

8 columns, I also just tried the excel fix [Here](http://support.microsoft.com/kb/211494) — jcc, Sep 14 '12 at 19:27

Chalise · Accepted Answer · 2014-10-27T18:13:27.193

1

(Written with help from Google) Create an export using the StringWriter class:

Public Shared Sub ExportDataSetToExcel(ds As DataSet, filename As String)
    Dim response As HttpResponse = HttpContext.Current.Response

    'Clean response object
    response.Clear()
    response.Charset = ""

    'Set response header
    response.ContentType = "application/vnd.ms-excel"
    response.AddHeader("Content-Disposition", "attachment;filename=""" & filename & """")

    'Create StringWriter and use to create CSV
    Using sw As New StringWriter()
        Using htw As New HtmlTextWriter(sw)
            'Instantiate DataGrid
            Dim dg As New DataGrid()
            dg.DataSource = ds.Tables(0)
            dg.DataBind()
            dg.RenderControl(htw)
            response.Write(sw.ToString())
            response.[End]()
        End Using
    End Using
End Sub

You just need to pass the function the DataSet and the File Name. If you do not want to edit your CreateDataSource() function, you can merge it into a DataSet first like so:

Dim dt As DataTable = CreateDataSource()
Dim ds As New DataSet
ds.Merge(dt)

edited Oct 27 '14 at 18:13

answered Sep 14 '12 at 20:03

Chalise

3,647
1
21
36

That looks like it creates the table structure I want. Its using html tags when I view in notepad, but still unable to open in excel – jcc Sep 14 '12 at 20:50
Also, have you tried saving the file somewhere other than your Temporary folder, such as in your Documents instead? – Chalise Sep 17 '12 at 13:37
Currently it is opening in the IE9 save dialog at the bottom of the screen, I am able to save it from there to the downloads folder. Whenever I click open from this dialog, the error occurs. – jcc Sep 17 '12 at 16:26
Ok ok, I just tried it on another computer and it seems to work, but i am now getting this error [Here](http://support.microsoft.com/kb/948615) – jcc Sep 17 '12 at 16:44
What filename are you passing to the ExportDataSetToExcel function? Make sure it is "FileNameHere.xls" – Chalise Sep 17 '12 at 19:29
Yes, that is the format I have been using. When I click yes on the format error that pops up, it seems to work just fine. Results seem to be what I'm looking for. Im just wondering if this [ERROR](http://support.microsoft.com/kb/948615) can cause any problems down the road? – jcc Sep 18 '12 at 15:48
Just make sure your response.ContentType is set to the correct MIME type for .xls (long list is here: http://webdesign.about.com/od/multimedia/a/mime-types-by-content-type.htm). If your application is for many users, you may want to fix up that error before deploying so there is no question about the veracity of your exported file. – Chalise Sep 18 '12 at 16:18
Yea, that is correct, I copied your example above word for word, the syntax is all correct, yes? Only 2 users, shouldnt be too much of a problem, but just wondering in case it causes other problems later. – jcc Sep 18 '12 at 19:09

score 0 · Answer 2 · answered Sep 14 '12 at 21:46

0

Your question is about why you're getting the message about being unable to open the file, correct?

According to Microsoft, this occurs when you have the "Ignore other applications that use Dynamic Data Exchange (DDE)" setting turned on. (See here). The link includes instructions to change the setting.

answered Sep 14 '12 at 21:46

Ann L.

12,802
5
30
60

In the second comment I said I already tried this to no avail, but thank you. – jcc Sep 15 '12 at 00:17

Exporting datatable to excel file with save dialog

2 Answers2

Linked