数据表导出到Excel文件保存对话框 [英] Exporting datatable to excel file with save dialog

问题描述

我有各种输入创建一个DataTable的存在。有时结果表是35000+行。目前，数据表获取显示到一个gridview。它几分钟后加载罚款。然后，那里有到GridView导出到Excel文件的选项。每次我们有一个大的表输出，转换失败。

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.

我的目标是绕过gridview的步骤，并采取格式化的表格，并把它直接插入Excel文件。也可能是一个CSV文件如果多数民众赞成更快的写入/负载，只要数据表类似于GridView的输出。

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.

我尝试以下code这里导出数据表到Excel文件。我尽我所能将其转换为VB，在这里...

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（）是获取在内存中创建的表。然后theres调用它填补了gridview的其他按钮。现在，它成功地符合并运行，然后成功地创建了文件。虽然，当文件试图打开我得到这个错误...

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...

这发生在我尝试两种XLS和CSV文件。自己是不是会被翻译权。任何解决方案？

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

推荐答案

（书面与谷歌的帮助）创建一个使用的的StringWriter类：

(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

您只需要通过功能的DataSet和文件名。如果你不想修改的createDataSource（）功能，你可以先合并到DataSet中，像这样：

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)

这篇关于数据表导出到Excel文件保存对话框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！