Convert a DataSet to a String Variable
Coverts a DataTable into an Excel string. Use this method to save a datatable as an Excel file. It loops through each of the DataTables in the DataSet. There is also an option of whether to print the table headers (column names).
public static string DataSetToString(DataSet dataSet, string title , bool printHeaders)
{
StringBuilder sb = new StringBuilder();
// start the excel file headers
sb.Append("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
sb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"><head><meta http-equiv=Content-Type content=\"text/html; charset=windows-1252\">");
sb.Append("<meta name=ProgId content=Excel.Sheet><meta name=Generator content=\"Microsoft Excel 9\"><!--[if gte mso 9]>");
sb.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + title + "</x:Name><x:WorksheetOptions>");
sb.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents><x:ProtectObjects>False</x:ProtectObjects>");
sb.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets>");
sb.Append("<x:ProtectStructure>False</x:ProtectStructure><x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>");
sb.Append("<![endif]--></head><body><table>");
// Start the excel worksheet
sb.Append("</table><table><tr><td colspan=\"4\"><h3><b>" + title + "</b></h3></td></tr></table>");
sb.Append("</table><table><tr><td></td></tr></table>");
for (int table = 0; table < dataSet.Tables.Count; table++)
{
sb.Append("<table>");
if (printHeaders)
{
sb.Append(string.Format("<TR><td colspan = \"{0}\"><B>{1}</b></td></tr>",
dataSet.Tables[table].Columns.Count,dataSet.Tables[table].TableName.ToUpper()));
sb.Append("<TR BgColor = \"#CCCCCC\">");
for (int iCol = 0; iCol < dataSet.Tables[table].Columns.Count; iCol++)
{
sb.Append("<td><b>" + dataSet.Tables[table].Columns[iCol].ColumnName + "</b></td>");
}
sb.Append("</TR>");
}
for (int iRow = 0; iRow < dataSet.Tables[table].Rows.Count; iRow++)
{
sb.Append("<TR>");
for (int iCol = 0; iCol < dataSet.Tables[table].Columns.Count; iCol++)
{
sb.Append("<td>" + dataSet.Tables[table].Rows[iRow][iCol] + "</b></td>");
}
sb.Append("<TR>");
}
sb.Append("</table><table><tr><td></td></tr></table>");
}
return sb.ToString();
}
Tools 
