Export Data from Database to Excel in C# windows

See more:

ASP.NET

C#3.5

Hi Friends,

I try to export Data from Database to Excel using C# windows application.if i have few columns the process is done quickly .otherwise it is taking more time .

So can you please suggest any other way to export data to excel in fastest way.

<pre lang="c#">Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            //SqlConnection cnn;

            string sql, sql1;
            string data = null;

            string connectionString;
            ConnectionManager cm = Dts.Connections["SRC"];
            IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100;
            OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

            //connectionString = "data source=10.64.98.144;initial catalog=QSCHINADEV;user id=qssh_Dev;password=china123;";

            //cnn = new SqlConnection(connectionString);
            conn.Close();

            conn.Open();
            //cnn.Open();
            sql = "SELECT DISTINCT CAMPAIGNCODE FROM TMP1";//"SELECT DISTINCT CITY,ORIGINAL_LEAD_SOURCE FROM D_LEAD";

            OleDbConnection ole;
            ole = new OleDbConnection(conn.ConnectionString);
            OleDbDataAdapter oladp = new OleDbDataAdapter(sql, conn);


            // SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);


            DataSet ds = new DataSet();
            oladp.Fill(ds);

            // dscmd.Fill(ds);
           
            //DataTable dtIn=new System.Data.DataTable()

            Hashtable htOut = new Hashtable();
            int s = 1;
            foreach (DataRow drIn in ds.Tables[0].Rows)
            {

                htOut.Add(s, drIn["CAMPAIGNCODE"].ToString());

                s = s + 1;

            }

            //string Vendor = "", City = "";
            string Campaign = "";
            string destpath = Dts.Variables["User::DestFilePath"].Value.ToString();
            // foreach (XmlNode node in nodeList)
            foreach (DictionaryEntry gg in htOut)
            {
                int i, j,autoseq;
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                //autoseq = gg.Key;
                autoseq = 1;
                Campaign = gg.Value.ToString();

                sql1 = "SELECT  * FROM TMP1 WHERE  CAMPAIGNCODE= '" + Campaign + "'";

                OleDbDataAdapter oladp1 = new OleDbDataAdapter(sql1, conn);

                //SqlDataAdapter dscmd1 = new SqlDataAdapter(sql1, cnn);
                DataSet ds1 = new DataSet();
                //dscmd1.Fill(ds1);
                oladp1.Fill(ds1);
             
                for (i = 1; i <= ds1.Tables[0].Rows.Count - 1; i++)
                {
                    for (j = 0; j <= ds1.Tables[0].Columns.Count - 1; j++)
                    {
                        data = ds1.Tables[0].Rows[i].ItemArray[j].ToString();
                        xlWorkSheet.Cells[i + 1, j + 1] = data;
                    }
                }

                xlWorkBook.SaveAs(destpath + "\\" + Campaign + "_" + autoseq + "_" + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

Regards

Arul