将System.Data.DataSet导入Excel的最简单方法是什么？

In .NET 2.0 (in this case VB), is there a standard API that'll serialize a DataSet object into a stream that can be saved as a tab-delimited file and opened directly in Excel? Or does one have to create the delimited file manually by iterating through the members of the table collection?

在.NET 2.0（在本例中为VB）中，是否有标准API将DataSet对象序列化为流，可以将其保存为制表符分隔文件并直接在Excel中打开？或者是否必须通过迭代表集合的成员手动创建分隔文件？

In this case, the DataSet's small, consisting of about 10 DataTables, each with between one and a few dozen rows. I'm just wondering whether there's a built-in mechanism for handling this scenario, since I imagine it's a relatively common one.

在这种情况下，DataSet很小，由大约10个DataTable组成，每个DataTable有一行到几十行。我只是想知道是否有一个处理这种情况的内置机制，因为我认为它是一个相对常见的。

Ideally, I'd like to be able to return it all in a single click as well -- e.g., the client clicks a Generate Report button, I assemble the report, and return a Response object containing the formatted data, prompting to save or open, etc. (I'd rather not have them download a file and then import it, as that seems a unnecessarily cumbersome usability-wise.)

理想情况下，我希望能够一键返回所有内容 - 例如，客户端单击Generate Report按钮，我汇编报告，并返回包含格式化数据的Response对象，提示保存或打开等等（我宁愿不让他们下载文件然后导入它，因为这似乎是一种不必要的麻烦的可用性。）

4 个解决方案

#1

There is an ADO.NET provider for Excel. This means, if you have a dataset, you can employ two DataAdapters to move data from one place to another: from Oracle to Excel, from SQL to Excel, from Excel to Oracle, etc.

有一个Excel的ADO.NET提供程序。这意味着，如果您有数据集，则可以使用两个DataAdapter将数据从一个地方移动到另一个地方：从Oracle到Excel，从SQL到Excel，从Excel到Oracle等。

Fill a DataSet from the source with the first DA, then Update the destination with the second DA. The DataAdapters do not need to be the same type - you can use any of OleDbDataAdapter, SqlDataAdapter, OracleDataAdapter, etc for the read as well as for the update.

使用第一个DA从源填充DataSet，然后使用第二个DA更新目标。 DataAdapter不需要是相同的类型 - 您可以使用任何OleDbDataAdapter，SqlDataAdapter，OracleDataAdapter等进行读取和更新。

No need to fiddle with CSV or XML formatting. No need to use Office Automation, so no PIAs and it works on Servers. It's just ADO.NET.

无需摆弄CSV或XML格式。无需使用Office Automation，因此没有PIA，它可以在服务器上运行。它只是ADO.NET。

To connect to Excel, use the Microsoft.Jet.OLEDB oledb provider.

若要连接到Excel，请使用Microsoft.Jet.OLEDB oledb提供程序。

Full Example source.

完整示例源。

Excerpt:

摘抄：

System.Data.DataSet ds1;

const string ConnStringSql= "Provider=sqloledb;Data Source=dinoch-8;Initial Catalog=Northwind;Integrated Security=SSPI;" ;
const string OutputFilename= "ExtractToExcel.xls";

const string ConnStringExcel= 
"Provider=Microsoft.Jet.OLEDB.4.0;" + 
"Data Source=" + OutputFilename + ";" + 
"Extended Properties=\"Excel 8.0;HDR=yes;\"";  // FIRSTROWHASNAMES=1;READONLY=false\"

const string sqlSelect="SELECT top 10 ProductId, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, GETDATE() as Extracted  from Products order by UnitPrice";
const string sqlInsert="INSERT INTO Extracto (ProductId, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Extracted) VALUES (@ProductId, @ProductName, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @Extracted)"; 

private void ReadFromSql()
{
    var ConnSql= new System.Data.OleDb.OleDbConnection(ConnStringSql);

    var da1 = new System.Data.OleDb.OleDbDataAdapter();
    da1.SelectCommand=  new System.Data.OleDb.OleDbCommand(sqlSelect);
    da1.SelectCommand.Connection= ConnSql;

    ds1= new System.Data.DataSet();
    da1.Fill(ds1, "Extracto");
}



private void InsertIntoExcel()
{
    // need to update the row so the DA does the insert...
    foreach (System.Data.DataRow r in ds1.Tables[0].Rows)
    { 
      r.SetModified(); // mark the row as updated to force an insert
    }

    var da2 = new System.Data.OleDb.OleDbDataAdapter();

    da2.UpdateCommand= new System.Data.OleDb.OleDbCommand(sqlInsert);
    da2.UpdateCommand.Connection= ConnExcel;

    da2.UpdateCommand.Parameters.Add("@ProductId", System.Data.OleDb.OleDbType.Integer, 4, "ProductId");
    da2.UpdateCommand.Parameters.Add("@ProductName", System.Data.OleDb.OleDbType.VarWChar, 40, "ProductName");
    da2.UpdateCommand.Parameters.Add("@QuantityPerUnit", System.Data.OleDb.OleDbType.VarWChar, 20, "QuantityPerUnit");
    da2.UpdateCommand.Parameters.Add("@UnitPrice", System.Data.OleDb.OleDbType.Currency, 8, "UnitPrice");
    da2.UpdateCommand.Parameters.Add("@UnitsInStock", System.Data.OleDb.OleDbType.SmallInt, 2, "UnitsInStock");
    da2.UpdateCommand.Parameters.Add("@Extracted", System.Data.OleDb.OleDbType.Date, 8, "Extracted");

    da2.Update(ds1, "Extracto");
}

This is not Excel automation, so the caveats that apply to using Excel Automation on the server do not apply.

这不是Excel自动化，因此适用于在服务器上使用Excel Automation的警告不适用。

You can start with a existing XLS file (or XLSX), and just fill in a named range. This gives you the opportunity to apply formatting and so on, prior to inserting the ilve data. In essence the existing XLS file is a template.
Or, you can start from nothing and create the XLS file, and the table/range within the XLS file, completely dynamically, at runtime. In this case the XLS file will be pretty vanilla / plain. No formatting, colors, formulas, and so on.

您可以从现有的XLS文件（或XLSX）开始，只需填写命名范围即可。这使您有机会在插入ilve数据之前应用格式等。实质上，现有的XLS文件是一个模板。或者，您可以从零开始，在运行时完全动态地创建XLS文件和XLS文件中的表/范围。在这种情况下，XLS文件将非常普通/普通。没有格式，颜色，公式等。

#2

DataSet.writeXML(Stream) Then you can import the XML file into Excel

DataSet.writeXML（Stream）然后，您可以将XML文件导入Excel

You can also look at this utility to do it for you.

您也可以查看此实用程序来为您执行此操作。

#3

You can also create a table in your HTML on your .aspx page using simple HTML table tags (or a gridview) and then in your codebehind include this line in your page.load:

您还可以使用简单的HTML表格标签（或gridview）在.aspx页面上的HTML中创建表格，然后在您的代码隐藏中将此行包含在page.load中：

Response.ContentType = "application/vnd.ms-excel"

enjoy :)

请享用：）

#4

-2

Serilize the Dataset to xml DataSet.WriteXML and the you can create an Xsl that transforms it to CSV (you can use XslTransform in order to transform the xml with the xsl)

将数据集Serilize为xml DataSet.WriteXML，您可以创建一个将其转换为CSV的Xsl（您可以使用XslTransform以使用xsl转换xml）

EDIT: other option is to directly transform it to CSV

编辑：其他选项是直接将其转换为CSV

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

Unless you really want it in pure Excel format

除非你真的想要纯Excel格式

#1