Click here to Skip to main content
15,066,326 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Posted 21 Feb 2012


10 bookmarked

Export to Excel from GridView in C#

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
21 Feb 2012CPOL
Export to Excel from GridView in C#


This post shows that how you can export data from GridView to Excel and formatting Excel file in C#.

Follow the below process:

  1. Add the reference below in your project:
  2. C#
    Microsoft Office 12.0 Control Library

  3. Now add a reference to the namespace in the page in which you want to use this functionality:
  4. C#
    using Microsoft.Office.Interop.Excel;

  5. In the Click event of the button (Export to Excel), call this function:
  6. C#
    private void ExportToExcel()
        //First fetch all records from grid to dataset
        DataSet dset = new DataSet();
        //First Add Columns from gridview to excel
        for (int i = 0; i < gridView.Columns.Count; i++) //GridView is id of gridview
        //add rows to the table 
        System.Data.DataRow dr1;
        for (int i = 0; i < gridView.Rows.Count; i++)
            dr1 = dset.Tables[0].NewRow(); //For Example There are only 3 columns into gridview
            System.Web.UI.WebControls.Label lblCCName = 
            System.Web.UI.WebControls.Label lblItemName = 
            System.Web.UI.WebControls.Label lblItemCode = 
            dr1[0] = lblCCName.Text.ToString();
            dr1[1] = lblItemName.Text.ToString();
            dr1[2] = lblItemCode.Text.ToString(); 
        //below code is export dset to excel
        ApplicationClass excel = new ApplicationClass();
        Workbook wBook;
        Worksheet wSheet; 
        wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
        wSheet = (Worksheet)wBook.ActiveSheet;
        System.Data.DataTable dt = dset.Tables[0];
        System.Data.DataColumn dc = new DataColumn();
        int colIndex = 0;
        int rowIndex = 4;
        foreach (DataColumn dcol in dt.Columns)
            colIndex = colIndex + 1;
            excel.Cells[5, colIndex] = dcol.ColumnName;
        foreach (DataRow drow in dt.Rows) 
            rowIndex = rowIndex + 1;
            colIndex = 0;
            foreach (DataColumn dcol in dt.Columns)
                colIndex = colIndex + 1;
                excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
        // Server File Path Where you want to save excel file.
        String strFileName = Server.MapPath("~\\Images\\StockStatement.xls");
        Boolean blnFileOpen = false;
            System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
            blnFileOpen = false;
        if (System.IO.File.Exists(strFileName))
        //It checks if file exists then it delete that file.
    //For Saving excel file on Server
    wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, 
      System.Reflection.Missing.Value, System.Reflection.Missing.Value, 
      false, false, XlSaveAsAccessMode.xlShared, 
      XlSaveConflictResolution.xlLocalSessionChanges, false, 
      System.Reflection.Missing.Value, System.Reflection.Missing.Value, false);

  7. Before saving, you can format the Excel data using the below code:
  8. C#
    Range oRng;
    wSheet.Cells[1, 2] = lblOffice1.Text;
    wSheet.Cells[3, 2] = lblCostCenter1.Text;
    wSheet.Cells[4, 1] = lblOfficeName1.Text;
    wSheet.get_Range("B1", "B1").Font.Bold = true;
    wSheet.get_Range("B1", "B1").Font.ColorIndex = 55;
    wSheet.get_Range("B3", "B3").Font.ColorIndex = 55;
    wSheet.get_Range("A4", "A4").Font.ColorIndex = 55;
    wSheet.get_Range("B1", "E1").Merge(Type.Missing);
    wSheet.get_Range("B3", "E3").Merge(Type.Missing);
    wSheet.get_Range("B1", "B1").HorizontalAlignment = Constants.xlCenter;
    wSheet.get_Range("B3", "B3").HorizontalAlignment = Constants.xlCenter;
    wSheet.get_Range("B3", "B3").Font.Bold = true;
    wSheet.get_Range("A4", "A4").Font.Bold = true;
    wSheet.get_Range("A4", "A4").HorizontalAlignment = Constants.xlLeft;
    wSheet.get_Range("A5", "P5").Font.Bold = true;
    wSheet.get_Range("A5", "P5").Interior.ColorIndex = 43;
    wSheet.Name = "Stock Statement";
    //AutoFit columns
    oRng = wSheet.get_Range("A1", "P1"); 

You can use the above code for exporting data to Excel using a GridView in C#. It saves the Excel file to server. You can use the download code for saving an Excel file from server to the client system.

Point of Interest

Using this code, you will get a proper Excel file and formatted. You can use formulae on columns. There should be full permissions to IIS user and Network Service User.

When you use the above code, if there are issues, then add below code in the web.config file:

<identity impersonate="true" />

If anybody has any issues with this topic, send me comments. I will provide solutions.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Sarvesh Kumar Gupta
Software Developer (Senior) Edge Business Solution
India India
Working in Edge Business Solution as a Senior Software Developer. I have more than 7 years exp. in Microsoft Technologies. I have exp. in ASP.Net, C#, SQL 2000/2005/2008 R2, Javascript, Ajax, JQuery, MVC, LINQ etc.

Comments and Discussions

QuestionExported Excel but having some queries Pin
Krishna Chaitanya.S18-Sep-17 2:31
MemberKrishna Chaitanya.S18-Sep-17 2:31 
QuestionCannot Use this when I have Update Panel in Aspx Page. Pin
Ajith The Great24-Mar-12 1:53
MemberAjith The Great24-Mar-12 1:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.