使用保存对话框将datatable导出到excel文件 [英] Exporting datatable to excel file with save dialog

问题描述

我有一个使用各种输入创建的数据表。有时生成的表格是35000行。目前，datatable显示在gridview上。几分钟后它会加载好。然后，theres是将gridview导出到excel文件的选项。每次我们有一个大的表导出，转换失败。

我的目标是绕过gridview步骤，并将格式化的表直接放入excel文件。也可以是一个csv文件，如果这样更快的写/加载，只要数据表类似于gridview输出。

我在这里尝试了以下代码将数据表导出到Excel文件。我尽力将其转换为vb，这里...

 受保护的子btnExportData_Click（发件人作为对象，e作为EventArgs） Handle 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 =
对于每个dc As DataColumn在dt.Columns 
 Response.Write（（tab + dc.ColumnName））
 tab =& vbTab 
 Next 
 Response.Write（& vbLf）
 Dim i As Integer 
对于每个dr As DataRow在dt.Rows 
 tab =
i = 0 
 Do While（i< dt.Columns.Count）
 Response.Write（（tab + dr（i）.ToString））
 tab =& vbTab 
i =（i + 1）
循环
 Response.Write（& vbLf）
下一个
 Response.End（）
 End Sub 
  

CreateDataSource（）是在内存中创建的表。然后theres其他按钮，称之为填充gridview。现在，它成功地符合并运行，然后成功创建文件。虽然，当文件尝试打开我得到这个错误...

Excel错误http://i48.tinypic.com/6y3bkk.png

当我尝试xls和csv文件时，会发生这种情况。有些东西没有被翻译正确。任何解决方案？

解决方案

（由Google的帮助撰写）使用 StringWriter类：

  Public Shared Sub ExportDataSetToExcel（ds As DataSet，filename As String）
 Dim response作为HttpResponse = HttpContext.Current.Response 
 
'清除响应对象
响应.Clear（）
 response.Charset =
 
'设置响应头
 response.ContentType =application / vnd.ms-excel
 response.AddHeader （Content-Disposition，attachment; filename =&）
 
'创建StringWriter并用于创建CSV 
使用sw作为新的StringWriter （）
使用htw作为新的HtmlTextWriter（sw）
'实例化DataGrid 
 Dim dg作为新的DataGrid（）
 dg.DataSource = ds.Tables（0）
dg.DataBind（）
 dg.RenderControl（htw）
 response.Write（sw.ToString（））
响应[结束]（）
结束使用
结束使用
 End Sub 
  

你只需要传递函数DataSet和文件名。如果您不想编辑您的 CreateDataSource（）函数，则可以首先将其合并到DataSet中：

  Dim dt As DataTable = CreateDataSource（）
 Dim ds As New DataSet 
 ds.Merge（dt）
  / pre> 
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...

Excel Error http://i48.tinypic.com/6y3bkk.png

This happens when I try both xls and csv files. Something is not getting translated right. Any solutions?
解决方案
(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)


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