You're just writing a file to the response buffer, clearing the buffer, and re-writing the same thing into the response buffer. You need multiple sheets in one file, so you need to pass all the datasets that you want into the function and add them all as individual sheets.
public static void Exportds2Multiplesheets(IEnumerable<dataset> ds, string Filename)
{
if (ds == null || ds.Tables.Count == 0)
{
throw new ArgumentException("DataSet needs to have at least one DataTable",
"dataset");
}
HttpResponse Response = System.Web.HttpContext.Current.Response;
string Fname = Filename + ".xlsx";
using (XLWorkbook wb = new XLWorkbook())
{
foreach(var set in ds)
{
wb.Worksheets.Add(ds);
}
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType =
"application/vnd.openxmlformats- officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + Fname);
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
</dataset>