Pages

Tuesday, 19 February 2013

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";
        }

No comments:

Post a Comment