hi shailendra just use this code this will solve your problem ExportToExcel method required two parameters just pass it you will get a excel file
private static void ExportToExcel(DataTable dtSource, string FileName)
{
XmlDocument objDoc = new XmlDocument();
XmlProcessingInstruction objInstructionXml =
objDoc.CreateProcessingInstruction("xml", " version=\"1.0\" encoding=\"utf-8\"");
objDoc.AppendChild(objInstructionXml);
XmlProcessingInstruction objInstruction =
objDoc.CreateProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
objDoc.AppendChild(objInstruction);
XmlElement objRoot = objDoc.CreateElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
XmlAttribute objHtml = objDoc.CreateAttribute("xmlns:html");
objHtml.Value = "
http://www.w3.org/TR/REC-html40";
objRoot.Attributes.Append(objHtml);
XmlAttribute objO = objDoc.CreateAttribute("xmlns:o");
objO.Value = "urn:schemas-microsoft-com:office:office";
objRoot.Attributes.Append(objO);
XmlAttribute objX = objDoc.CreateAttribute("xmlns:x");
objX.Value = "urn:schemas-microsoft-com:office:excel";
objRoot.Attributes.Append(objX);
XmlAttribute objMs = objDoc.CreateAttribute("xmlns:ms");
objMs.Value = "urn:schemas-microsoft-com:xslt";
objRoot.Attributes.Append(objMs);
XmlAttribute objSs = objDoc.CreateAttribute("xmlns:ss");
objSs.Value = "urn:schemas-microsoft-com:office:spreadsheet";
objRoot.Attributes.Append(objSs);
XmlElement objWorksheet =
objDoc.CreateElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
XmlAttribute objWorksheetName =
objDoc.CreateAttribute(null, "Name", "urn:schemas-microsoft-com:office:spreadsheet");
if (FileName.Length > 30)
{
objWorksheetName.Value = FileName.Substring(0, 30);
}
else
{
objWorksheetName.Value = FileName;
}
objWorksheet.Attributes.Append(objWorksheetName);
XmlElement objTable = objDoc.CreateElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
//Header
XmlElement objTitleRow = objDoc.CreateElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
foreach (DataColumn dc in dtSource.Columns)
{
XmlElement objTitleCell = objDoc.CreateElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
XmlElement objTitleData =
objDoc.CreateElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
XmlAttribute objTitleDataType =
objDoc.CreateAttribute(null, "Type", "urn:schemas-microsoft-com:office:spreadsheet");
objTitleDataType.Value = "String";
objTitleData.InnerXml = dc.ColumnName;
objTitleData.Attributes.Append(objTitleDataType);
objTitleCell.AppendChild(objTitleData);
objTitleRow.AppendChild(objTitleCell);
}
objTable.AppendChild(objTitleRow);
//Data
foreach (DataRow dr in dtSource.Rows)
{
try
{
XmlElement objRow = objDoc.CreateElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
foreach (DataColumn dc in dtSource.Columns)
{
XmlElement objCell =
objDoc.CreateElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
XmlElement objData =
objDoc.CreateElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
XmlAttribute objDataType =
objDoc.CreateAttribute(null, "Type", "urn:schemas-microsoft-com:office:spreadsheet");
objDataType.Value = "String";
objData.InnerXml = string.Format("{0}", dr[dc.ColumnName]);
objData.Attributes.Append(objDataType);
objCell.AppendChild(objData);
objRow.AppendChild(objCell);
}
objTable.AppendChild(objRow);
}
catch
{ }
}
objWorksheet.AppendChild(objTable);
objRoot.AppendChild(objWorksheet);
objDoc.AppendChild(objRoot);
ResponseWrite(objDoc.InnerXml, string.Format("{0}.xls", FileName), "application/excel");
}
private static void ExportToXML(DataTable dtSource, string FileName)
{
XmlDocument objDoc = new XmlDocument();
XmlElement objRoot = objDoc.CreateElement("root");
for (int i = 0; i < dtSource.Rows.Count; i++)
{
try
{
XmlElement objItem = objDoc.CreateElement(FileName);
DataRow dr = dtSource.Rows[i];
for (int j = 0; j < dtSource.Columns.Count; j++)
{
XmlAttribute objAttr = objDoc.CreateAttribute(dtSource.Columns[j].ColumnName);
objAttr.Value = string.Format("{0}", dr[j]);
objItem.Attributes.Append(objAttr);
}
objRoot.AppendChild(objItem);
}
catch
{ }
}
objDoc.AppendChild(objRoot);
ResponseWrite(objDoc.InnerXml, string.Format("{0}.xml", FileName), "text/xml");
}
private static string FixCSVString(string Source)
{
return Source.Replace("\"", "\"\"");
}
private static void ExportToCSV(DataTable dtSource, string FileName)
{
string Row = "";
foreach (DataColumn dc in dtSource.Columns)
{
Row = string.Format("{0}{2}\"{1}\"",
Row,
FixCSVString(dc.ColumnName),
Row == "" ? "" : ",");
}
string Result = string.Format("{0}\r\n", Row);
for (int i = 0; i < dtSource.Rows.Count; i++)
{
try
{
DataRow dr = dtSource.Rows[i];
Row = "";
for (int j = 0; j < dtSource.Columns.Count; j++)
{
Row = string.Format("{0}{2}\"{1}\"",
Row,
FixCSVString(string.Format("{0}", dr[j])),
Row == "" ? "" : ",");
}
Result = string.Format("{0}{1}\r\n", Result, Row);
}
catch
{ }
}
ResponseWrite(Result, string.Format("{0}.csv", FileName), "text/csv");
}
private static void ResponseWrite(string Result, string FileName, string ContentType)
{
System.Web.HttpResponse Response = System.Web.HttpContext.Current.Response;
byte[] lstByte = System.Text.Encoding.UTF8.GetBytes(Result);
Response.ClearHeaders();
Response.ClearContent();
Response.ContentType = string.Format("{0}; charset=utf-8", ContentType);
Response.AppendHeader("Content-disposition", string.Format("attachment; filename=\"{0}\"", FileName));
Response.AppendHeader("Content-Length", lstByte.Length.ToString());
Response.BinaryWrite(lstByte);
Response.Flush();
Response.End();
}
#endregion