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:
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)
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("&", "&");
input = input.Replace("<", "<");
input = input.Replace(">", ">");
input = input.Replace("\"", """);
input = input.Replace("'", "'");
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:
Reviewed by Ravi Kumar
on
3:32 PM
Rating:
Its working .. but files created has warning of corrupt
ReplyDelete