Geek Answers Handbook

What is the easiest way to import System.Data.DataSet into Excel?

In .NET 2.0 (in this case, VB) there is a standard API that will serialize the DataSet into a stream that can be saved as a tab delimited file and opened directly in Excel? Or do you need to create a delimited file manually, iterating through members of a table collection?

In this case, a small DataSet consisting of about 10 DataTables, each of which contains from one to several tens of rows. I'm just wondering if there is a built-in mechanism to handle this scenario, since I find this relatively common.

Ideally, I would like to return all this with one click, for example, the client clicks the Create Report button, I collect the report and return a Response object containing formatted data, asking for saving or opening, etc. (I would prefer that they do not download the file and then import it, as this seems unnecessarily cumbersome for usability.)

+2
source share
4 answers

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

DataSet DA, DA. DataAdapters - OleDbDataAdapter, SqlDataAdapter, OracleDataAdapter .. , .

CSV XML. Office Automation, PIA . ADO.NET.

Excel, Microsoft.Jet.OLEDB oledb.

.

:

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

Excel, , Excel Automation , .

XLS ( XLSX) . .., ilve. XLS .
XLS / XLS, , . XLS /. , , ..

+4

DataSet.writeXML() XML Excel

, .

0

HTML .aspx HTML- ( gridview), .load:

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

:)

0

Serialize the dataset in xml DataSet.WriteXMLand you can create Xsl that converts it to CSV (you can use XslTransform to convert xml using XSL)

EDIT: another option is to directly convert it to 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

-2
source

More articles:


All Articles