Sunday, September 18, 2011

Export Database table Data to Excel With Column Names


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using System.IO;


public partial class DataBasetoExcell : System.Web.UI.Page
{
    //OleDbConnection excelConnection = new OleDbConnection("database=Prasad;uid=sa;pwd=sa123;server=.;");
    SqlConnection strConnection = new SqlConnection("database=Prasad;uid=sa;pwd=sa123;server=.;");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnDBtoExcell_Click(object sender, EventArgs e)
    {
        SqlDataAdapter daa = new SqlDataAdapter("select * from tblemp", strConnection);
        DataSet ds = new DataSet();
        daa.Fill(ds);

        Microsoft.Office.Interop.Excel.Application xlApp;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        string tablename = string.Empty;
        for (int b = 0; b < ds.Tables.Count; b++)
        {
            tablename = ds.Tables[0].TableName.ToString();
        }
        for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; )
        {
            for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; )
            {
                //Below 2 Lines With Data and Columns
                //string data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                //xlWorkSheet.Cells[i + 1, j + 1] = data.ToString();
                // Below Foreach Only Column Names
                foreach (DataColumn dc in ds.Tables[0].Columns)
                {
                    xlWorkSheet.Cells[0 + 1, j + 1] = dc.ColumnName.ToString();
                    i++;
                    j++;
                }
               
            }
        }

        xlWorkBook.SaveAs(@"C:\" + tablename, 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);



        SqlDataAdapter da = new SqlDataAdapter("insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\\" + tablename + ";','SELECT * FROM [Sheet1$]') select * from tblemp ", strConnection);
        //SqlDataAdapter da = new SqlDataAdapter("select * from tblemp", strConnection);
        DataTable dt = new DataTable();
        da.Fill(dt);
    }



    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
}

No comments:

Post a Comment