Wednesday, October 27, 2010

Export to Excel

Hey flocks Just now I faced a problem in converting a grid view to Excel. Just now I came out with a solution
First step is add reference's with the excel by right click on the solution explorer and click on add reference also yo will find out .net, com tabs click on tab then scroll down for some distance yo vl find the microsoft Execel some thing jst select tat 
Just add a grid and input all the values as your wish the next thing you got to do is
hav a button belove the grid nd on button click even try following code seriously tis vl work out

protected void Button1_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();


        // creating new WorkBook within Excel application
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);


        // creating new Excelsheet in workbook
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
      
        // see the excel sheet behind the program
        app.Visible = true;

        // get the reference of first sheet. By default its name is Sheet1.
        // store its reference to worksheet
        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
        worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;

        // changing the name of active sheet
        worksheet.Name = "Exported from gridview";


        // storing header part in Excel
        for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
        {
            worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
        }



        // storing Each row and column value to excel sheet
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Text.ToString();
            }
        }

        workbook.SaveAs(@"D:\aa.xsl", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        // save the application
        //workbook.SaveAs("c:\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // Exit from the application
        app.Quit();
    }

No comments: