Is there is any way to export multiple dataset into multiple excel files using C#

Question

1.00/5 (1 vote)

See more:

I have a code for exporting multiple dataset to multiple sheets of single excel file. But I want for eg. I have 2 datasets and on button click I want to export first dataset value in test1.xlsx and 2nd dataset to test2.xlsx

What I have tried:

Tried to creat two instances of XLWorkbook wb = new XLWorkbook() on button click but still not working getting single file only

Posted 14-Apr-16 0:16am

Member 10678104

Updated 14-Apr-16 2:00am

Add a Solution

Comments

Michael_Davies 14-Apr-16 6:23am

Please show your code, when you say create two instances are they immediately after each other or are you using a new variable for the second book?

Member 10678104 14-Apr-16 6:29am

public static void Exportds2Multiplesheets(DataSet ds, string Filename)
{
if (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())
{
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();
}
}
using (XLWorkbook wb1 = new XLWorkbook())
{
wb1.Worksheets.Add(ds);
wb1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb1.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())
{
wb1.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);

Response.Flush();
Response.End();
}
}
}

Member 10678104 14-Apr-16 6:29am

Tried this code. but on button click it opens only on file

Moleter 17-May-16 8:43am

Refer to this article for exporting DataSet into an Excel file in C# and also this code for exporting that excel file in ASP.NET.
I hope this helps, note that those sample codes use this C# library for excel files.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nathan Minier · Answer 1 · 2016-04-14T02:00:00

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.

C#

public static void Exportds2Multiplesheets(IEnumerable<dataset> ds, string Filename)
{
   if (ds == null || ds.Tables.Count == 0) //possible null case handled
   {
      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) // Add a sheet per data set
      {
         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>