How to export DataTable or Data Grid View data into Excel file ?

Method to convert DataTable rows into Excel file:

using SWF = System.Windows.Forms;using IO = System.IO;
 public static string pPopulateExcel(DataTable myTable)
{  StringBuilder sb = new StringBuilder();
sb.AppendLine("<table cellspacing='0' cellpadding='4' rules='all' bordercolor='#CCCCCC' border='1' style='color:Black;background-color:White;border-color:#CCCCCC;border-width:1px;border-style:Solid;font-family:Tahoma;font-size:10pt;height:24px;border-collapse:collapse;'>");
sb.AppendLine("<tr style='color:Blue;background-color:aliceblue;font-weight:bold;'>");
sb.AppendLine("<td align='center'>Sl.No.</td>");
for (int llngCol = 0; llngCol < myTable.Columns.Count; llngCol++)
sb.AppendLine("<td align='center'>" + myTable.Columns[llngCol].ColumnName + "</td>");
sb.AppendLine("</tr>");
if (myTable.Rows.Count > 0)

{ int i = 1;
foreach (DataRow objDR in myTable.Rows)
{
 sb.AppendLine("<tr class='body'>");

sb.AppendLine("<td align='right'>" + i + "</td>");
for (int llngCol = 0; llngCol < myTable.Columns.Count; llngCol++)
{
switch (myTable.Columns[llngCol].DataType.ToString())

{
 case "System.Int32":
case "System.Decimal":
case "System.Double":
sb.AppendLine("<td align='right'>" + objDR[llngCol]);
break;
case "System.DateTime":
sb.AppendLine("<td align='center'>");
if (Convert.ToDateTime(objDR[llngCol]) != DateTime.MinValue)
sb.AppendLine((objDR[llngCol].ToString().Length == 0 ? "" : Convert.ToDateTime(objDR[llngCol].ToString()).ToString("dd-MMM-yyyy")) + "");
else
sb.AppendLine("&nbsp;");
break;
case "System.String":
sb.AppendLine("<td align='left'>" + objDR[llngCol]);
break;
default:
sb.AppendLine("<td align='center'>" + objDR[llngCol]);
break;

}
sb.AppendLine("</td>");

}
sb.AppendLine("</tr>");

i++;

}
sb.AppendLine("</table>");

}
else
sb.AppendLine("<tr class='body'><td colspan='" + (myTable.Columns.Count + 1) + "' align='left'>No Records found!</td></tr></table>");
return sb.ToString();

}

Export To Excel Button:

private void btnExportToExcel_Click(object sender, EventArgs e)
{ string XLSFileName = IO.Path.Combine(CurrentDirectory(), "Status Report " + DateTime.Now.ToString("ddMMMyyyyHHmm") + ".xls");

StringBuilder sbExcelText = new StringBuilder();

sbExcelText.AppendLine(ExcelUtil.pPopulateExcel((DataTable)dataGridResults.DataSource));

sbExcelText.AppendLine("<br />");

IO.File.WriteAllText(XLSFileName, sbExcelText.ToString());

SWF.MessageBox.Show("Completed! File: " + XLSFileName, "Status Report");

}


Method to get current directory path:

internal static string CurrentDirectory()

{string lstrPath = System.IO.Path.GetDirectoryName(SWF.Application.ExecutablePath).ToLower();

if (lstrPath.Contains(@"\bin\debug") || lstrPath.Contains(@"\bin\release") || lstrPath.Contains(@"\bin\x86"))

{lstrPath = System.IO.Path.GetDirectoryName(SWF.Application.ExecutablePath).Substring(0, lstrPath.IndexOf(@"\bin"));

}return lstrPath + @"\Reports";
}