export to excel in asp.net multiple sheets

public void dataexport()

{

  ToExcel(ds, "filename.xls", Page.Response);

}

private static stringgetWorkbookTemplate()

{

var sb = new StringBuilder(818);

sb.AppendFormat(@"{0}", Environment.NewLine);

sb.AppendFormat(@"{0}", Environment.NewLine);

sb.AppendFormat(@", 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(@" {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@" {0}", Environment.NewLine);

sb.Append(@"{0}\r\n");

returnsb.ToString();

}

private static stringreplaceXmlChar(string input)

{

  input = input.Replace("&", "&amp");

  input = input.Replace("<", "<");

  input = input.Replace(">", ">");

  input = input.Replace("\"", """);

  input = input.Replace("'", "'");

  return input;

}

private static stringgetCell(Type type, objectcellData)

{

var data = (cellData is DBNull) ? "" : cellData;

if(type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("{0}", data);

if(type.Name.Contains("Date") && data.ToString() != string.Empty)

{

return string.Format("{0}", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));

}

return string.Format("{0}", replaceXmlChar(data.ToString()));

}

private static stringgetWorksheets(DataSet source)

{

var sw = new StringWriter();

if (source == null || source.Tables.Count == 0)

{

sw.Write("\r\n

\r\n\r\n\r\n");

returnsw.ToString();

}

foreach (DataTable dt insource.Tables)

{

if(dt.Rows.Count == 0)

sw.Write(" + replaceXmlChar(dt.TableName) + "\">\r\n

\r\n  ss:StyleID=\"s62\">\r\n\r\n");

else

{

//write each row data

var sheetCount = 0;

for (int i = 0; i < dt.Rows.Count; i++)

{

if ((i % rowLimit) == 0)

{

//add close tags for previous sheet of the same data table

if ((i / rowLimit) > sheetCount)

{

sw.Write("\r\n\r\n");

sheetCount = (i / rowLimit);

}

sw.Write("\r\n + replaceXmlChar(dt.TableName) +

(((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n

");

//write column name row

sw.Write("\r\n");

foreach (DataColumn dc indt.Columns)

sw.Write(string.Format("{0}", replaceXmlChar(dc.ColumnName)));

sw.Write("");

}

sw.Write("\r\n");

foreach (DataColumn dc indt.Columns)

sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));

sw.Write("");

}

sw.Write("\r\n

\r\n");

}

}

returnsw.ToString();

}

public static stringGetExcelXml(DataTable dtInput, string filename)

{

  var excelTemplate = getWorkbookTemplate();

  var ds = newDataSet();

  ds.Tables.Add(dtInput.Copy());

  var worksheets = getWorksheets(ds);

  var excelXml = string.Format(excelTemplate, worksheets);

  return excelXml;

}

public static stringGetExcelXml(DataSet dsInput, string filename)

{

  var excelTemplate = getWorkbookTemplate();

  var worksheets = getWorksheets(dsInput);

  var excelXml = string.Format(excelTemplate, worksheets);

  return excelXml;

}

public static void ToExcel(DataSet dsInput, stringfilename, HttpResponse response)

{

  var excelXml = GetExcelXml(dsInput, filename);

  response.Clear();

  response.Buffer = true;

  response.BufferOutput = true;

  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();

}

public static void ToExcel(DataTable dtInput, stringfilename, HttpResponse response)

{

  var ds = newDataSet();

  ds.Tables.Add(dtInput.Copy());

  ToExcel(ds, filename, response);

}