locked
From SQL Server to Excel using C# RRS feed

  • Question

  • Hi,

    I have a Text SqlCommand and I would like to know how can I loop through the records to create a Micrsoft Excel output for the result?

    Thanks,

    Jassim

    Sunday, July 24, 2016 4:00 PM

Answers

All replies

  • Hi Jasim,

    You can read results from SQL using SqlDataReader and loop through results and insert into excel using OLESb

    Below is an example

    SqlCon.Open();
    System.Data.OleDb.OleDbConnection MyConnection ;
    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
    SqlDataReader reader=cmd.ExecuteReader ();
        MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
    MyConnection.Open();
    while (reader.Read ())
    {
       
                    
                    string sql = null;
                
                    myCommand.Connection = MyConnection;
                    sql = "Insert into [Sheet1$] (id,name) values('"+reader [0].ToString ()+"','"+reader [1].ToString ()+"')";
                    myCommand.CommandText = sql;
                    myCommand.ExecuteNonQuery();
                    
                             
    }
    MyConnection.Close();
    sqlCon.Close ();


    Thanks, Mahmoud

    Sunday, July 24, 2016 4:50 PM
  • Monday, July 25, 2016 1:43 AM