none
From SQL to Excel 12 via a DataSet - exports in binary (xlsb) (cross-post after no reply on the SQL forum...) RRS feed

  • Question

  • Hi,
    I am experimenting with taking a dataset from SQL and putting it directly into an excel via a DataSet. It works but the exported Excel is in "binary" format. Does anyone know of a way to get it directly into xlsx? I *may* end up doing it in a foreach anyway, but it would be too cool to be able to just transfer my dataset from one connection to another :-).
    Thanks, here's my code:

    DataSet dsXLData = new DataSet("XL");

    string myQuery = "SELECT table1.Email,table2.FirstName FROM Contacts table1, Users table2 where table2.UserID = table1._UserID";

    SqlConnection sqlcon = new SqlConnection(DataAcquire.ConnectionString);

    SqlCommand comStatus = new SqlCommand(myQuery, sqlcon);
    SqlDataAdapter dasql = new SqlDataAdapter();

    DataSet ds = new DataSet();

    dasql.AcceptChangesDuringFill = false;

    dasql.SelectCommand = comStatus;
    dasql.Fill(ds, "Export");

    string connectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;
                        Data Source=D:\xls\Book6.xlsb;Extended Properties=
                        ""Excel 12.0;HDR=YES;""";

    OleDbConnection maconn = new OleDbConnection(connectionString);
    maconn.Open();

    OleDbDataAdapter da = new OleDbDataAdapter();

    OleDbCommand comOleDBCommand = new OleDbCommand("CREATE TABLE [Export] (Email char(255), FirstName char(255));", maconn);
    comOleDBCommand.ExecuteNonQuery();

    OleDbCommand comExport = new OleDbCommand(
            "INSERT INTO Export (Email, FirstName) VALUES (@Email, @FirstName)", maconn);


    comExport.Parameters.Add("@Email", OleDbType.Char, 255, "Email");
    comExport.Parameters.Add("@FirstName", OleDbType.Char, 255, "FirstName");
    comExport.UpdatedRowSource = UpdateRowSource.None;

    da.InsertCommand = comExport;
    da.Update(ds, "Export");
    da.Dispose();
    comExport.Dispose();
    maconn.Dispose();

    dasql.Dispose();

    sqlcon.Dispose();

    Friday, February 1, 2008 11:09 AM

All replies

  • There is no direct bulk copy from DataSet into any Excel format and you would need to write your own code to provide bulk copy functionality and insert records one-by-one using provider. When I faced situation that I need fast bulk export I have created component for bulk transfer of data into Excel xls and xlsx file format and you could try it from my website.

    Saturday, February 2, 2008 11:48 AM
    Moderator
  • Hi, and thanks for the input (I really should check my msn more often...sorry!). However, while you might be correct in stating "there is no bulk copy...", the code I wrote does work, so in a way there IS a bulk copy function. So my assumption is that you are referring to a functionality that is "not supported by Microsoft", as opposed to "it doesn't work", as it clearly does work.

    Thanks anyway, I'll just go with with a loop.

    Cheers

    Anton

    Wednesday, March 5, 2008 2:08 PM
  • Wow, EVERY SINGLE SITE ON THE INTERNET HAS THE WRONG INSTRUCTIONS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

     

    After many, many hours of searching, I finally got the info I needed, in the @^[#&ing registry.

     

    The connection string is NOT

     

    string connectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;
                        Data Source=D:\xls\Book6.xlsb;Extended Properties=
                        ""Excel 12.0;HDR=YES;""";

     

    but

     

    string connectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;
                        Data Source=D:\xls\Book6.xlsx;Extended Properties=
                        ""Excel 12.0 Xml;HDR=YES;""";

     

    The first deals with binary data (xlsb), the second with xml (xlsx).

    Wednesday, March 12, 2008 10:24 AM