Click here to Skip to main content
14,243,202 members

Export to Excel from GridView in C#

Rate this:
4.14 (7 votes)
Please Sign up or sign in to vote.
4.14 (7 votes)
11 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 reference below in your project:

    Microsoft Office 12.0 Control Library

  2. Now, add NameSpace in your Page at which you want to use this functionality:

    using Microsoft.Office.Interop.Excel;

  3. At click event of button (Export to Excel), call below function:

    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)gridView.Rows[i].Cells[0].FindControl("lblCCName");
    System.Web.UI.WebControls.Label lblItemName = (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("lblItemName");
    System.Web.UI.WebControls.Label lblItemCode = (System.Web.UI.WebControls.Label)gridView.Rows[i].Cells[0].FindControl("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];
    String strFileName = Server.MapPath("~\\Images\\StockStatement.xls"); // Server File Path Where you want to save excel file.
    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);

  4. Before saving, you can format the Excel data using the below code:

    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 Export data to Excel using gridview in C#. It saves Excel file to server. You can use download code for save Excel file from server to client system.

Point of Interest
Using this code, you will get proper Excel file and formatted. You can use formula on columns.

Using there should be full permissions to IIS user and Network Service User.

When you use the above code, then there can be some issue, then add below code in web.config file

<identity impersonate="true"/>

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


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


About the Author

Comments and Discussions

QuestionMy vote of 5 Pin
fresh_girl21-May-14 1:31
memberfresh_girl21-May-14 1:31 
QuestionCan I realize this task without using MS Excel Pin
pandaloveu29-Aug-12 22:41
memberpandaloveu29-Aug-12 22:41 
AnswerRe: Can I realize this task without using MS Excel Pin
Lacy0030-Aug-12 15:09
memberLacy0030-Aug-12 15:09 
GeneralRe: Can I realize this task without using MS Excel Pin
pandaloveu5-Sep-12 17:40
memberpandaloveu5-Sep-12 17:40 
GeneralMy vote of 5 Pin
suresh tv25-Jul-12 21:04
membersuresh tv25-Jul-12 21:04 
GeneralMy vote of 1 Pin
hsnfrz24-May-12 21:15
memberhsnfrz24-May-12 21:15 
QuestionHow to run? Pin
Member 87961717-Apr-12 21:22
memberMember 87961717-Apr-12 21:22 
GeneralAn alternative method Pin
Mike Gledhill3-Apr-12 2:32
memberMike Gledhill3-Apr-12 2:32 
GeneralReason for my vote of 4 Good procedure of export to excel in... Pin
Varun Sareen12-Feb-12 17:12
professionalVarun Sareen12-Feb-12 17:12 

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.

Posted 23 Jan 2012


15 bookmarked