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

有一个用于Excel的ADO.NET提供程序。

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

无需修改CSV或XML格式。无需使用办公自动化,因此无需PIA,它可以在服务器上工作。这只是ADO.NET。

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

Full Example source

摘录:

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");
}

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

DataSet.writeXML(Stream) 然后可以将XML文件导入Excel

你也可以看看 this utility 为你做这件事。

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

享受:)

将数据集序列化为xml DataSet.WriteXML 你可以创建一个 Xsl 这就把它变成了 CSV XslTransform 为了使用xsl转换xml)

编辑:另一个选项是直接将其转换为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