Conclusion on Usual Data Export to Excel Methods with C#

Programmers often need to export data to Excel during programming. In this article, I will share three methods about how to export data to Excel from DataTable, GridView and Database. All these methods come from materials I collect. Therefore, this article is just a conclusion.

Method 1: Export Data to Excel from DataTable

         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;

                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

        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;  

                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.

        public override void VerifyRenderingInServerForm(Control control)

        {

            //OverRide

        }

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

        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:@”);

            }

        }

The third method is based on a component, Spire.DataExport. Because it shows method to export data from database, so we need to get data information in database firstly. In this example, the data information which will be exported is customers table.

Method 3: Export Data from DataBase

            OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection();

            oleDbConnection.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DB1.mdb”;

            OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();

            oleDbCommand.CommandText = “select * from Customers”;

            oleDbCommand.Connection = oleDbConnection;

            Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();

            Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();

            cellExport.FileName = “Customers.xls”;

            cellExport.SheetOptions.AggregateFormat.Font.Name = “Calibri”;

            cellExport.SheetOptions.CustomDataFormat.Font.Name = “Calibri”;

            cellExport.SheetOptions.TitlesFormat.Font.Name = “Calibri”;

            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.Bold = true;

            workSheet1.Options.TitlesFormat.Font.Size = 11F;

            workSheet1.SheetName = “Customers”;

            workSheet1.SQLCommand = oleDbCommand;

            cellExport.Sheets.Add(workSheet1);

            workSheet1.ItemType = Spire.DataExport.XLS.CellItemType.Row;

            oleDbConnection.Open();

            cellExport.SaveToFile(“Customers.xls”);

            oleDbConnection.Close();

            System.Diagnostics.Process.Start(“Customers.xls”);

Conclusion

This article concludes three useful methods on exporting data to Excel. I hope that each of the three methods will be helpful for you.

About the component used in the third method, learn more by click here and download here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s