Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 2005 > C# 2005
| Search | Today's Posts | Mark Forums Read
C# 2005 For discussion of Visual C# 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 28th, 2011, 03:59 AM
Registered User
 
Join Date: Mar 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Conclusion on Usual Data Export to Excel Methods with C#

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.

Last edited by Yaths; April 1st, 2011 at 03:31 AM..




Similar Threads
Thread Thread Starter Forum Replies Last Post
Export to Excel (Data only) nikks9 ASP.NET 1.0 and 1.1 Professional 3 February 9th, 2011 05:01 AM
Export Data to Excel msbsam ASP.NET 2.0 Basics 0 November 8th, 2006 02:09 AM
How to export data into Excel lily611 General .NET 8 January 11th, 2006 10:02 PM
export data to excel tulincim Classic ASP Databases 0 August 22nd, 2005 03:34 PM
export data to Excel?? amy_mgfe Excel VBA 0 January 15th, 2005 10:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.