Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 2005 > C# 2005
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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..
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 03:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.