Conclusion on Usual Data Export to Excel Methods with C#

Yaths · March 28th, 2011, 03:59 AM

Programmers often need to export data to Excel during programming. In this article, I share three frequently used data exportation methods by searching for many materials and based on my own experience.

Method 1: Export Data to Excel from DataTable

Code:

public static void ExportExcel(string fileName, DataTable dataSource)
        {
            System.Web.UI.WebControls.GridView dgExport = null;
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            //IO is used to export and return Excel
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;
  
            if (dataSource != null)
            {
                //Set encoding and attachment format                
                curContext.Response.Clear();
                curContext.Response.Buffer = true;
                //System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) 

Note: The above code line is used to avoid unreadable code to appearing in file.

                curContext.Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls");
                curContext.Response.ContentType = "application/vnd.ms-excel";
                //Avoid unreadable code to appearing in contents exported.
                curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");
                //Export to Excel
                strWriter = new System.IO.StringWriter(); 
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
                //Redefine one GridView without paging to solve the problem that there is paging in daData.
                dgExport = new System.Web.UI.WebControls.GridView();
                dgExport.DataSource = dataSource;
                dgExport.AllowPaging = false;
                dgExport.DataBind(); 
                //Download to server.
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }

Method 2: Export Data to Excel from GridView

Code:

public static void ExportExcel(string fileName, GridView gvMain)
        {
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;
            //IO is used to export and return Excel.
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;
 
            if (gvMain.DataSource != null)
            {
                //Set encoding and attachment format.
                curContext.Response.Clear();   
                curContext.Response.Buffer = true;   
                //System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)

Note: The above code line is used to avoid unreadable code to appearing in file.
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls");
                curContext.Response.ContentType = "application/vnd.ms-excel";
                //Avoid unreadable code to appearing in contents exported.
                curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");
                //Export to Excel                
                strWriter = new 
                System.IO.StringWriter();
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
                //Download to Server
                gvMain.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }

Problems and Solutions

We can find that method 2 is similar to method 1. But method 2 can customize format for Excel column, for example: ID card number, currency format.
However, there is one problem in method 2, wrong with the command: GridView widget must put in Form Marker which has the sentence: runat = server.

Solution: Override for VerifyRenderingInServerForm method of WebForm.

Code:

public override void VerifyRenderingInServerForm(Control control)
{
       //OverRide
}

The following code shows how to display ID card number and currency format after exporting data.

Code:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
     if (e.Row.RowType == DataControlRowType.DataRow)
     {
         e.Row.Cells[ID Number].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
     }
}

Beside Method 1 and 2, I find one another method which can export data with high-speed. And with this method, we don’t need to install Microsoft Excel.

Method 3: Export Data from DataBase without Excel installed

Note: We need to install one free component: free Spire.DataExport to realize data exporting.

In addition, the example data in this method is about some VIPs information.

Code:

using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using Spire.DataExport;
using Spire.DataExport.Common;
using System.Data.OleDb;

namespace DataExport
{
    class Program
    {
        static void Main(string[] args)
        {
            OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection();
            oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=VIP.mdb";
            OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
            oleDbCommand.CommandText = "select * from VIP";
            oleDbCommand.Connection = oleDbConnection;

            Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
            Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();
            //Export Style
            cellExport.ActionAfterExport = ActionType.OpenView;
            cellExport.AutoFitColWidth = true;
            cellExport.DataFormats.CultureName = "zh-us";
            cellExport.FileName = "VIP.xls";
            cellExport.SheetOptions.AggregateFormat.Font.Name = "Times New Roman";
            cellExport.SheetOptions.CustomDataFormat.Font.Name = "Times New Roman";
            cellExport.SheetOptions.DefaultFont.Name = "Times New Roman";
            cellExport.SheetOptions.TitlesFormat.Font.Name = "Times New Roman";
workSheet1.AutoFitColWidth = true;
            workSheet1.Options.TitlesFormat.Font.Color = Spire.DataExport.XLS.CellColor.Pink;
            workSheet1.Options.TitlesFormat.Alignment.Horizontal = Spire.DataExport.XLS.HorizontalAlignment.Center;
            workSheet1.Options.TitlesFormat.Font.Italic = true;
            workSheet1.Options.TitlesFormat.Font.Size = 12F;
            workSheet1.Options.TitlesFormat.FillStyle.Pattern = Spire.DataExport.XLS.Pattern.Solid;
            //Data export
            workSheet1.SheetName = "VIP";
            workSheet1.SQLCommand = oleDbCommand;
            cellExport.Sheets.Add(workSheet1);
            workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;
            oleDbConnection.Open();
            cellExport.SaveToFile("VIP.xls");
            oleDbConnection.Close();
        }
    }
}

Conclusion

I think the conclusion of these methods can be helpful for programmers who need to export data to Excel. The third method is very suitable for programmers who don’t install Microsoft Excel on computer. If you are interested in the component Method 3 used, you can download it from here.