Microsoft Excel - How to export datagridview to excel 2003 and 2007 in windows application using c#

Asked By Manivannan G on 22-Aug-08 01:30 AM
  I want to export the datagridview to excel 2003 as well as excel 2007. Now I can able to export to excel 2003. But I cant do it as xlsx. Any idea?

Use this - Atul Shinde replied to Manivannan G on 22-Aug-08 01:34 AM

Introduction:

In this article, we will see how to Export an ASP.Net 2.0 GridView to Excel. 

The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality.

The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Step 1: Setup your web page with the Gridview

In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario.

ContactPhone Table Structure:

Column Name

Type

ContactID

Int (Identity)

FName

Varchar(50)

LName

Varchar(50)

ContactPhone

Varchar(20)

Step: The Actual Export

The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType. 

string attachment = "attachment; filename=Contacts.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

Response.Write(sw.ToString());

Response.End(); 

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server." 

To avoid this error, add the following code:  

public override void VerifyRenderingInServerForm(Control control)

{

 

}

 

Step : Convert the contents

 

If the GridView contains any controls, such as Checkboxes, Dropdownlists, we need to replace the contents with their relevant values. The following recursive function uses Reflection to determine the type of control. The control is deleted in preparation for the Excel export and the relevant value of the control is added.

 

private void PrepareGridViewForExport(Control gv)

{

 

    LinkButton lb = new LinkButton();

    Literal l = new Literal();

    string name = String.Empty;

    for (int i = 0; i < gv.Controls.Count; i++)

    {

        if (gv.Controls[i].GetType() == typeof(LinkButton))

        {

            l.Text = (gv.Controls[i] as LinkButton).Text;

  gv.Controls.Remove(gv.Controls[i]);

  gv.Controls.AddAt(i, l);

        }

        else if (gv.Controls[i].GetType() == typeof(DropDownList))

        {

            l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

            gv.Controls.Remove(gv.Controls[i]);

            gv.Controls.AddAt(i, l);

        }

        else if (gv.Controls[i].GetType() == typeof(CheckBox))

        {

            l.Text = (gv.Controls[i] as CheckBox).Checked? "True" : "False";

            gv.Controls.Remove(gv.Controls[i]);

            gv.Controls.AddAt(i, l);

        }

        if (gv.Controls[i].HasControls())

        {

            PrepareGridViewForExport(gv.Controls[i]);

        }

}

Code Listing:

Image: Page Design

  

Image : Sample in action

Image: Export to Excel button is clicked

Image: GridView contents exported to Excel

ExcelExport.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="DeleteConfirm" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Contacts Listing</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<strong><span style="font-size: small; font-family: Arial; text-decoration: underline">

Contacts Listing

    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" /></span></strong><br />

<br />

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID"

DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">

<Columns>

<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" />

<asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" />

<asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" />

<asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" />

<asp:TemplateField HeaderText="Favorites">

<ItemTemplate>

    &nbsp;

    <asp:CheckBox ID="CheckBox1" runat="server" />

</ItemTemplate></asp:TemplateField>

</Columns>

<FooterStyle BackColor="#CCCC99" />

<RowStyle BackColor="#F7F7DE" />

<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

<AlternatingRowStyle BackColor="White" />

</asp:GridView>

 

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>"

DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)"

ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>"

SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]"

UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID">

<InsertParameters>

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

</InsertParameters>

<UpdateParameters>

<asp:Parameter Name="FName" Type="String" />

<asp:Parameter Name="LName" Type="String" />

<asp:Parameter Name="ContactPhone" Type="String" />

<asp:Parameter Name="ContactID" Type="Int32" />

</UpdateParameters>

<DeleteParameters>

<asp:Parameter Name="ContactID" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource>

&nbsp;

<br />

</div>

</form>

</body>

</html>

ExcelExport.aspx.cs 

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text;

using System.IO;

 

public partial class DeleteConfirm : System.Web.UI.Page

{

 

    protected void Page_Load(object sender, EventArgs e)

    {

    }

 

    protected void Button1_Click(object sender, EventArgs e)

    {

        //Export the GridView to Excel

        PrepareGridViewForExport(GridView1);

        ExportGridView();

    }

 

    private void ExportGridView()

    {

        string attachment = "attachment; filename=Contacts.xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/ms-excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter htw = new HtmlTextWriter(sw);

        GridView1.RenderControl(htw);

        Response.Write(sw.ToString());

        Response.End();

    }

 

    public override void VerifyRenderingInServerForm(Control control)

    {

    }

 

    private void PrepareGridViewForExport(Control gv)

    {

        LinkButton lb = new LinkButton();

        Literal l = new Literal();

        string name = String.Empty;

        for (int i = 0; i < gv.Controls.Count; i++)

        {

            if (gv.Controls[i].GetType() == typeof(LinkButton))

            {

                l.Text = (gv.Controls[i] as LinkButton).Text;

                gv.Controls.Remove(gv.Controls[i]);

                gv.Controls.AddAt(i, l);

            }

            else if (gv.Controls[i].GetType() == typeof(DropDownList))

            {

                l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

                gv.Controls.Remove(gv.Controls[i]);

                gv.Controls.AddAt(i, l);

            }

            else if (gv.Controls[i].GetType() == typeof(CheckBox))

            {

                l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";

                gv.Controls.Remove(gv.Controls[i]);

                gv.Controls.AddAt(i, l);

            }

            if (gv.Controls[i].HasControls())

            {

                PrepareGridViewForExport(gv.Controls[i]);

            }

        }

    }

}

 

Implementation Options:

 

In quite a few cases, developers face an error in the Export functionality - typically the error message is "RegisterForEventValidation can only be called during Render();".

 

Our website readers have contributed some good suggestions in the article comments below. I would particularly like to highlight the suggestion by Marianna, who provides an alternative implementation to the VerifyRenderingInServerForm override. This approach is described below: 

  • Step 1: Implement the Export functionality as described above.
  • Step 2: Remove the code to override the VerifyRenderingInServerForm method.
  • Step 3: Modify the code for the ExportGridView function as below. The code highlighted in green creates and HtmlForm on the fly, before exporting the gridview, adds the gridview to this new form and renders the form (instead of rendering the gridview in our original implementation) 

private void ExportGridView()

{

          string attachment = "attachment; filename=Contacts.xls";

          Response.ClearContent();

          Response.AddHeader("content-disposition", attachment);

          Response.ContentType = "application/ms-excel";

          StringWriter sw = new StringWriter();

          HtmlTextWriter htw = new HtmlTextWriter(sw);

 

          // Create a form to contain the grid

          HtmlForm frm = new HtmlForm();

          GridView1.Parent.Controls.Add(frm);

          frm.Attributes["runat"] = "server";

          frm.Controls.Add(GridView1);

 

          frm.RenderControl(htw);

          //GridView1.RenderControl(htw);

          Response.Write(sw.ToString());

          Response.End();

}

 

This implementation has the advantage that it can be setup as re-usable code in a separate library, without having to override the base class method each time.

 

Link: http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx

http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

See this code to export data into Excel - Sagar P replied to Manivannan G on 22-Aug-08 01:46 AM

private Excel.ApplicationClass ExcelApp = new Excel.ApplicationClass();

Now can access all the nice collections, objects, properties, and methods that are contained within the ExcelApp.  The code for exporting the GridView into the Excel SpreadSheet is shown below.  The first step to export the GridView is to copy the column headers over to Excel.  This is accomplished in Listing 1 which opens the spreadsheet and sets up the columns.  We've created a nice method called GetExcelIndexToLetter which takes a column number in the GridView and converts it to a corresponding column letter in the Excel Spreadsheet.  Remember that putting information in an Excel spreadsheet is sort of like playing the game of Battleship.  The cells are defined by a row of letters and column of numbers such as:  "A1", "B2","G3", (except in this case we aren't trying to sink any battleships.)

Listing 1 - Setting up the GridView columns in Excel

private void OpenSpreadsheet()
{
// Make Excel the Main Window Visible
ExcelApp.Visible = true
;
object
missing = System.Reflection.Missing.Value;
// Add a Worksheet to Excel
Excel.Workbook theWorkbook = ExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
TheSheet = (Excel.Worksheet)theWorkbook.Worksheets[1];
// Get the first row of cells to populate the header
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(0) + "1",
GetExcelIndexToLetter(NumberOfColumns - 1) + "1");
// draw the column headers in the excel spreadsheet from the GridView
for (int i = 0; i < this
.NumberOfColumns; i++)
{
// Copy the text of the GridView Header into the Excel Cell
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).Value = listView1.Columns[i].Text;
// Set the width of the column in the Excel Spreadsheet to the Width of the GridView column
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).ColumnWidth =
listView1.Columns[i].Width/listView1.Font.SizeInPoints;
// Set the Excel Cell To Bold Font
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).Font.Bold = true
;
}

The next step is to copy the cells of the GridView into the cells of the Excel Spreadsheet.  The code for doing this looks similar to the previous listing accept in listing 2 we are accessing individual cells for our range inside of the Excel spreadsheet instead of a single row as in listing 1.

Listing 2 - Populating the cells in Excel from the GridView

private void WriteExcelData()
{
try
{
for (int i = 1; i < this
.NumberOfRows; i++)
{
for (int j = 0; j < this
.NumberOfColumns; j++)
{
// Get the current cell in Excel
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(j) + (i + 1).ToString(),GetExcelIndexToLetter(j) + (i + 1).ToString());
// Copy the GridView cell's Text Value, background color and text color into the Excel Cell
theRange.Value = GetCell(i, j+1);
theRange.Interior.Color =
this
.GetCellColor(i, j+1).ToArgb();
theRange.Font.Color =
this
.GetCellTextColor (i, j+1).ToArgb();
// Redraw the cell borders, they seem to disappear after coloring in the cell
theRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
theRange.Borders.Color = Color.LightGray.ToArgb();
}
}
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

For more details abt this code just go thr this link;

http://www.c-sharpcorner.com/UploadFile/mgold/GridViewIV11242005012341AM/GridViewIV.aspx

Also see this;

http://aspalliance.com/771

http://www.gridviewguy.com/ArticleDetails.aspx?articleID=86

http://www.123aspx.com/redir.aspx?res=33792

Best Luck!!!!!!!!!!!!!!!!
Sujit.

this will help - Binny ch replied to Manivannan G on 22-Aug-08 01:56 AM

  public static void ExportToExcel(DataSet dataSet, string outputPath)
        {

            #region Export To Excel Function
            try
            {
                // Create the Excel Application object
                Excel.ApplicationClass excelApp = new Excel.ApplicationClass();

                // Create a new Excel Workbook
                Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

                int sheetIndex = 0;

                // Copy each DataTable
                foreach (System.Data.DataTable dt in dataSet.Tables)
                {

                    // Copy the DataTable to an object array
                    object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                    // Copy the column names to the first row of the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        rawData[0, col] = dt.Columns[col].ColumnName;
                    }

                    // Copy the values to the object array
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        for (int row = 0; row < dt.Rows.Count; row++)
                        {
                            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
                        }
                    }

                    // Calculate the final column letter
                    string finalColLetter = string.Empty;
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                    int colCharsetLen = colCharset.Length;

                    if (dt.Columns.Count > colCharsetLen)
                    {
                        finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                    }

                    finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);

                    // Create a new Sheet
                    Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
                    excelSheet.Name = dt.TableName;

                    // Fast data export to Excel
                    string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);

                    excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                    // Mark the first row as BOLD
                    ((Excel.Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
                }



                // Save and Close the Workbook
                if (File.Exists(@outputPath))
                {
                    //File.Replace(@outputPath,@outputPath,@outputPath);
                    File.Delete(@outputPath);
                    excelWorkbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
                else
                {
                    excelWorkbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }

                excelWorkbook.Close(true, Type.Missing, Type.Missing);
                excelWorkbook = null;

                // Release the Application object
                excelApp.Quit();
                excelApp = null;

                // Collect the unreferenced objects
                GC.Collect();
                GC.WaitForPendingFinalizers();
                MessageBox.Show("The Search Result Has Been Stored TO Location " + outputPath, " Resume Parser", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (System.Exception ex)
            {
                Errordetector.Class1 EM = new Errordetector.Class1();
                EM.writeLog("ERROR FORUND AT" + DateTime.Now.ToString());
                MessageBox.Show("Exception While Writing To Excel", "Resume Parser", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            #endregion
        }
Reply to Answer - Manivannan G replied to Binny ch on 22-Aug-08 02:18 AM
Hi Binny ch and Atul shinde!

Thanks for your immediate reply

 I am working in windows application by using c#. I have the coding for export datagridview (not from datatable(s)) to excel 2003. But I cant do that to excel 2007. My requirement is the exported data must open in both xls and xlsx.

Thank u
Try this... - Atul Shinde replied to Manivannan G on 25-Aug-08 01:32 AM

private Excel.ApplicationClass ExcelApp = new Excel.ApplicationClass();

Now can access all the nice collections, objects, properties, and methods that are contained within the ExcelApp.  The code for exporting the GridView into the Excel SpreadSheet is shown below.  The first step to export the GridView is to copy the column headers over to Excel.  This is accomplished in Listing 1 which opens the spreadsheet and sets up the columns.  We've created a nice method called GetExcelIndexToLetter which takes a column number in the GridView and converts it to a corresponding column letter in the Excel Spreadsheet.  Remember that putting information in an Excel spreadsheet is sort of like playing the game of Battleship.  The cells are defined by a row of letters and column of numbers such as:  "A1", "B2","G3", (except in this case we aren't trying to sink any battleships.)

Listing 1 - Setting up the GridView columns in Excel

private void OpenSpreadsheet()
{
// Make Excel the Main Window Visible
ExcelApp.Visible = true
;
object
missing = System.Reflection.Missing.Value;
// Add a Worksheet to Excel
Excel.Workbook theWorkbook = ExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
TheSheet = (Excel.Worksheet)theWorkbook.Worksheets[1];
// Get the first row of cells to populate the header
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(0) + "1",
GetExcelIndexToLetter(NumberOfColumns - 1) + "1");
// draw the column headers in the excel spreadsheet from the GridView
for (int i = 0; i < this
.NumberOfColumns; i++)
{
// Copy the text of the GridView Header into the Excel Cell
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).Value = listView1.Columns[i].Text;
// Set the width of the column in the Excel Spreadsheet to the Width of the GridView column
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).ColumnWidth =
listView1.Columns[i].Width/listView1.Font.SizeInPoints;
// Set the Excel Cell To Bold Font
((Excel.Range)theRange["1", GetExcelIndexToLetter(i)]).Font.Bold = true
;
}

The next step is to copy the cells of the GridView into the cells of the Excel Spreadsheet.  The code for doing this looks similar to the previous listing accept in listing 2 we are accessing individual cells for our range inside of the Excel spreadsheet instead of a single row as in listing 1.

Listing 2 - Populating the cells in Excel from the GridView

private void WriteExcelData()
{
try
{
for (int i = 1; i < this
.NumberOfRows; i++)
{
for (int j = 0; j < this
.NumberOfColumns; j++)
{
// Get the current cell in Excel
Excel.Range theRange = TheSheet.Cells.get_Range(GetExcelIndexToLetter(j) + (i + 1).ToString(),GetExcelIndexToLetter(j) + (i + 1).ToString());
// Copy the GridView cell's Text Value, background color and text color into the Excel Cell
theRange.Value = GetCell(i, j+1);
theRange.Interior.Color =
this
.GetCellColor(i, j+1).ToArgb();
theRange.Font.Color =
this
.GetCellTextColor (i, j+1).ToArgb();
// Redraw the cell borders, they seem to disappear after coloring in the cell
theRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
theRange.Borders.Color = Color.LightGray.ToArgb();
}
}
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}

For more details abt this code just go thr this link;

http://www.c-sharpcorner.com/UploadFile/mgold/GridViewIV11242005012341AM/GridViewIV.aspx

Also see this;

http://aspalliance.com/771

http://www.gridviewguy.com/ArticleDetails.aspx?articleID=86

http://www.123aspx.com/redir.aspx?res=33792

John Glenn replied to Manivannan G on 05-Dec-11 03:42 AM
Hi,

with this Excel C# / VB.NET component you can easily export DataGridView to Excel (XLS and XLSX formats), if DataGridView is data-bound to DataTable.

Here is a sample Excel C# code:

var bindingSource = (BindingSource)this.dataGridView.DataSource;

 

var dataTable = ((DataView)bindingSource.List).Table;

 

var ef = new ExcelFile();

 

// Insert data table in worksheet, starting from worksheet's first row and column and include column headers

ef.Worksheets.Add(dataTable.TableName).InsertDataTable(dataTable, 0, 0, true);

 

// Save to XLS format.

ef.SaveXls(dataTable.TableName + ".xls");

// Save to XLSX format.

ef.SaveXlsx(dataTable.TableName + ".xlsx");