How to Export a Data Table to Excel - Asp.Net, XML

random

How to Export a Data Table to Excel - Asp.Net, XML

Export to EXCEL File from DataTable in C#.Net


In the previous articles I explained about Export GridView to EXCEL - Asp.Net, How to export DataSet to Excel in C#? 

Many times we need to save or export data table to excel spreadsheet on server. Here I will explain how to export data from a DataTable to Excel file using ASP.Net. Find the source code below:

data table to excel, c# excel,download excel,save as to excel,exporting to Excel using Open XML,ASP.NET, Export the Datatable, table of data in Excel

Default.cs
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    const int rowLimit = 65000;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnexport_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        string statusfileName = "Upload_Site_Status_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
        string varErrorLineNumber = "0";
        dt.Columns.Add("EmpId", typeof(int));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Address", typeof(string));
        dt.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        dt.Rows.Add(25, "Rk", "Gurgaon", DateTime.Now);
        dt.Rows.Add(50, "Sachin", "Noida", DateTime.Now);
        dt.Rows.Add(10, "Nitin", "Noida", DateTime.Now);
        dt.Rows.Add(21, "Aditya", "Meerut", DateTime.Now);
        dt.Rows.Add(100, "Mohan", "Banglore", DateTime.Now);
        try
        {
            ToExcel(dt, statusfileName, Response, varErrorLineNumber.Substring(0, varErrorLineNumber.Length - 1));
        }
        catch (Exception ex)
        {
            Label1.Text = ex.ToString();
        }
    }

    #region Export To Excel

    private string getWorkbookTemplate()
    {
        var sb = new StringBuilder(818);
        sb.AppendFormat(@"<?xml version=""1.0""?>{0}", Environment.NewLine);
        sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine);
        sb.AppendFormat(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
        sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);
        sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);
        sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);
        sb.AppendFormat(@" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine);
        sb.AppendFormat(@" <Styles>{0}", Environment.NewLine);
        sb.AppendFormat(@"  <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);
        sb.AppendFormat(@"   <Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);
        sb.AppendFormat(@"   <Borders/>{0}", Environment.NewLine);
        sb.AppendFormat(@"   <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>{0}", Environment.NewLine);
        sb.AppendFormat(@"   <Interior/>{0}", Environment.NewLine);
        sb.AppendFormat(@"   <NumberFormat/>{0}", Environment.NewLine);
        sb.AppendFormat(@"   <Protection/>{0}", Environment.NewLine);
        sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
        sb.AppendFormat(@"  <Style ss:ID=""s62"">{0}", Environment.NewLine);
        sb.AppendFormat(@"   <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine);
        sb.AppendFormat(@"    ss:Bold=""1""/>{0}", Environment.NewLine);
        sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
        sb.AppendFormat(@"  <Style ss:ID=""s63"">{0}", Environment.NewLine);
        sb.AppendFormat(@"   <NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine);
        sb.AppendFormat(@"  </Style>{0}", Environment.NewLine);
        sb.AppendFormat(@"  <Style ss:ID=""s64""><Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#FF0000""/></Style>{0}", Environment.NewLine);
        //<Style ss:ID="s64">

        sb.AppendFormat(@" </Styles>{0}", Environment.NewLine);
        sb.Append(@"{0}\r\n</Workbook>");
        return sb.ToString();
    }

    private string replaceXmlChar(string input)
    {
        input = input.Replace("&", "&amp");
        input = input.Replace("<", "&lt;");
        input = input.Replace(">", "&gt;");
        input = input.Replace("\"", "&quot;");
        input = input.Replace("'", "&apos;");
        return input;
    }

    private string getCell(Type type, object cellData)
    {
        var data = (cellData is DBNull) ? "" : cellData;
        if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", data);
        if (type.Name.Contains("Date") && data.ToString() != string.Empty)
        {
            return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));
        }
        return string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(data.ToString()));
    }

    private string getWorksheets(DataSet source, string ColorLineNumber)
    {
        string[] varColorLineNumber = ColorLineNumber.Split(',');
        var sw = new StringWriter();
        if (source == null || source.Tables.Count == 0)
        {
            sw.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            return sw.ToString();
        }
        foreach (DataTable dt in source.Tables)
        {
            if (dt.Rows.Count == 0)

                sw.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            else
            {
                var sheetCount = 0;
                int y = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if ((i % rowLimit) == 0)
                    {
                        if ((i / rowLimit) > sheetCount)
                        {
                            sw.Write("\r\n</Table>\r\n</Worksheet>");
                            sheetCount = (i / rowLimit);
                        }
                        sw.Write("\r\n<Worksheet ss:Name=\"Sheet1" +
                                 (((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n<Table>");
                        sw.Write("\r\n<Row>");
                        foreach (DataColumn dc in dt.Columns)
                            sw.Write(string.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(dc.ColumnName)));
                        sw.Write("</Row>");
                    }
                    int colorChange = 0;
                    if (varColorLineNumber.Length > 1)
                    {
                        for (int varcounterColor = 0; varcounterColor < varColorLineNumber.Length; varcounterColor++)
                        {
                            if (Convert.ToInt32(varColorLineNumber[varcounterColor]) == i)
                            {
                                sw.Write("\r\n<Row ss:StyleID=\"s64\">");
                                foreach (DataColumn dc in dt.Columns)
                                {
                                    sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));
                                }
                                colorChange = 1;
                            }
                        }
                    }
                    if (colorChange == 0)
                    {
                        sw.Write("\r\n<Row>");
                        foreach (DataColumn dc in dt.Columns)
                            sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));
                    }
                    sw.Write("</Row>");
                }
                sw.Write("\r\n</Table>\r\n</Worksheet>");
            }
        }
        return sw.ToString();
    }

    public string GetExcelXml(DataTable dtInput, string filename, string ColorLineNumber)
    {
        var excelTemplate = getWorkbookTemplate();
        var ds = new DataSet();
        ds.Tables.Add(dtInput.Copy());
        var worksheets = getWorksheets(ds, ColorLineNumber);
        var excelXml = string.Format(excelTemplate, worksheets);
        return excelXml;
    }

    public string GetExcelXml(DataSet dsInput, string filename, string ColorLineNumber)
    {
        var excelTemplate = getWorkbookTemplate();
        var worksheets = getWorksheets(dsInput, ColorLineNumber);
        var excelXml = string.Format(excelTemplate, worksheets);
        return excelXml;
    }

    public void ToExcel(DataSet dsInput, string filename, HttpResponse response, string ColorLineNumber)
    {
        try
        {
            var excelXml = GetExcelXml(dsInput, filename, ColorLineNumber);
            response.Clear();
            response.Buffer = false;
            response.BufferOutput = false;
            response.Charset = "UTF-8";
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            response.AppendHeader("Content-disposition", "attachment; filename=" + filename);
            response.Write(excelXml);
            response.Flush();
            response.End();
        }
        catch (Exception ex)
        {
        }
        HttpContext.Current.Response.End();
    }

    public void ToExcelInFolder(DataSet dsInput, string filename, HttpResponse response, string ColorLineNumber)
    {
        try
        {
            var excelXml = GetExcelXml(dsInput, filename, ColorLineNumber);
            System.IO.File.WriteAllText(filename, excelXml);
        }
        catch (Exception ex)
        {
        }
        // HttpContext.Current.Response.End();
    }

    public void ToExcelInFolder(DataTable dtInput, string filename, HttpResponse response, string ColorLineNumber)
    {
        var ds = new DataSet();
        ds.Tables.Add(dtInput.Copy());
        ToExcelInFolder(ds, filename, response, ColorLineNumber);
    }
    public void ToExcel(DataTable dtInput, string filename, HttpResponse response, string ColorLineNumber)
    {
        var ds = new DataSet();
        ds.Tables.Add(dtInput.Copy());
        ToExcel(ds, filename, response, ColorLineNumber);
    }
    #endregion
}

Download the source code, click here
Please leave your comments, suggestions and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading! :)

How to Export a Data Table to Excel - Asp.Net, XML Reviewed by Ravi Kumar on 3:32 PM Rating: 5

1 comment:

  1. Its working .. but files created has warning of corrupt

    ReplyDelete

All Rights Reserved by Etechpulse © 2012 - 2017

Contact Form

Name

Email *

Message *

Powered by Blogger.