How to display records from the Excel Sheet..

 OleDbConnection conn = new OleDbConnection("Provider= Microsoft.ACE.OLEDB.12.0;Data Source=D:/Raj Collection For ASP Examples/Raj Practise Examples/Excel InsertData/Test3.xls; Extended Properties=\"Excel 12.0;HDR=YES;\"");
        conn.Open();
        string s = "Select * from [Sheet1$]";
        OleDbCommand cmd = new OleDbCommand(s, conn);
        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        conn.Close();

How to insert data from the User Panel to Excel Sheet

Take Two text boxes like id,name and take one button
Copy these code in Button Click event..


 if (TextBox1.Text != string.Empty && TextBox2.Text != string.Empty)
        {
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string sql = null;
            MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:/Raj Collection For ASP Examples/Raj Practise Examples/Excel InsertData/Test3.xls';Extended Properties=Excel 8.0;");
            MyConnection.Open();
            myCommand.Connection = MyConnection;
            sql = "Insert into [Sheet1$] (TitleId,TitleName) values('" + TextBox1.Text + "','" + TextBox2.Text + "')";
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();
            MyConnection.Close();
            TextBox1.Text = string.Empty;
            TextBox2.Text = string.Empty;
        }

How to Store Excel Data into Sql Server..

Create one table in Sql server with What ever excel sheet have field names...


 //Create connection string to Excel work book
        string excelConnectionString =
        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/Raj Collection For ASP Examples/Raj Practise Examples/Excel InsertData/Test3.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

        //Create Connection to Excel work book
        OleDbConnection excelConnection =
        new OleDbConnection(excelConnectionString);

        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand
        ("Select [TitleId],[TitleName] from [Sheet1$]",
        excelConnection);

        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();

        SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
        con.Open();
        sqlBulk.DestinationTableName = "ExcelData";
        sqlBulk.ColumnMappings.Add("TitleId", "TitleId");
        sqlBulk.ColumnMappings.Add("TitleName", "TitleName");
        sqlBulk.WriteToServer(dReader);
        con.Close();

How to Retrieve data From the Excel

Copy These code and paste in to Button Event...Take One grid View then Bind the data....




OleDbConnection conn = new OleDbConnection("Provider= Microsoft.ACE.OLEDB.12.0;Data Source=D:/Raj Collection For ASP Examples/Raj Practise Examples/Excel InsertData/Test4.xls; Extended Properties=\"Excel 12.0;HDR=YES;\"");
        conn.Open();
        string s = "Select * from [Sheet1$] where TitleId='" + TextBox1.Text + "'";
        OleDbCommand cmd = new OleDbCommand(s, conn);
        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);

        da.Fill(ds);

        dt.Columns.Add(new System.Data.DataColumn("TitleId", typeof(String)));
        dt.Columns.Add(new System.Data.DataColumn("TitleName", typeof(String)));
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            dr = dt.NewRow();
            dr[0] = ds.Tables[0].Rows[i]["TitleId"].ToString();
            dr[1] = ds.Tables[0].Rows[i]["TitleName"].ToString();
            dt.Rows.Add(dr);
        }

        GridView1.DataSource = dt;
        GridView1.DataBind();
        conn.Close();

How to Write Date into Excel From Sql Server

First Download Microsoft.Office.Interop.Excel.dll From the Internet...
While Programming on Excel we need to use these dll.
In Button Event Copy These Code...


Excel.Application excelApp = new Excel.Application();
        excelApp.Visible = true;
        Excel.Workbook newWorkbook = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
        string workbookPath = @"D:/Raj Collection For ASP Examples/Raj Practise Examples/Excel InsertData/Test1.xls";
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
            0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
            true, false, 0, true, false, false);
        //-----jusqu ici il marche, il a ouvert excel------
        Excel.Sheets excelSheets = excelWorkbook.Worksheets;
        string currentSheet = "Sheet1";
        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
        // ici c'est l'etablissement de la conx avc la base

        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter("SELECT MainMenuId,MainMenu FROM MainMenu", con);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        //recuperer resultat requete
        for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
        {
            String resultatreq = ds.Tables[0].Rows[i - 1]["MainMenuId"].ToString().Trim();
            String resultatreq1 = ds.Tables[0].Rows[i - 1]["MainMenu"].ToString().Trim();
            //ecrire le resultat dans excel

            Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A" + (i + 1), "A" + (i + 1));
            excelCell.Value2 = resultatreq;
            Excel.Range excelCell1 = (Excel.Range)excelWorksheet.get_Range("B" + (i + 1), "B" + (i + 1));
            excelCell1.Value2 = resultatreq1;

            Excel.Range excelCell2 = (Excel.Range)excelWorksheet.get_Range("A" + 1, "A" + 1);
            excelCell2.Value2 = "TitleId";

            Excel.Range excelCell3 = (Excel.Range)excelWorksheet.get_Range("B" + 1, "B" + 1);
            excelCell3.Value2 = "TitleName";
        }