Export dataset to the excel sheet in asp.net c#

Question

I tried to export the dataset which have 4 tables to the excel sheet, Unfortunately I can't. I have code to export data table to excel. So instead of dataset, I called the "ExportToExcel" function which I have in my code to export datatable to excel 4 times. But once it created the first sheet, it stops the control flow. Control doesn't call the second function ("ExportToExcel(dsResult.Tables[2], "AthleteSentCount");") Here is the code

protected void ExportToExcel(object sender, EventArgs e)
{
     ExportToExcel(dsResult.Tables[3], "AthleteInboxCount");
     ExportToExcel(dsResult.Tables[2], "AthleteSentCount");
     ExportToExcel(dsResult.Tables[0], "CoachInboxCount");
     ExportToExcel(dsResult.Tables[1], "CoachSentCount");
}

void ExportToExcel(DataTable dt, string FileName)
{
    if (dt.Rows.Count > 0)
    {
        string filename = FileName + ".xls";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();

        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", 
                              "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
}

If any body knows to export the dataset to the excel with the bar chart please help me. Else please give your solution to the problem.

Thanks for your response. Control stops its operation once it creates its first excel sheet ie once the "ExportToExcel(dsResult.Tables[3], "AthleteInboxCount");" function got excecuted — Arun Kumar T
– Arun Kumar T, Commented Oct 15, 2013 at 6:01
ys why not?try to pass datatabe in a loop till Data Set length — SANDEEP
– SANDEEP, Commented Oct 15, 2013 at 6:03
I tried, but it stops the flow after "Response.End();" method while executing the first loop — Arun Kumar T
– Arun Kumar T, Commented Oct 15, 2013 at 6:11

Community · Accepted Answer · 2017-05-23 12:13:54Z

4

First of all your function start streaming content

 Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");

to end user and END it with

Response.End();

Moreover if you fix this your code will product 4 excel files.

There is an existing code for creating Excel sheets on stack overflow: How to add additional worksheets to an Excel from DataTable The only change you will have to made will be saving xlsx to stream and transmit this stream to user.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Oct 15, 2013 at 6:15

Piotr Stapp

19.9k11 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arun Kumar T Over a year ago

Thank you @Garath, your post is very useful to me. I have to made some little changes like syntax. But it works great. Again thanks.

Arun Kumar T Over a year ago

Can I know why the control stops when it creates the first sheet in the above code?

Piotr Stapp Over a year ago

@ArunKumarT the code not excatly stops, but because you call Response.End(); the rest of code is not send to end user.

gopi krishna · Accepted Answer · 2016-12-16 04:44:52Z

1

Written in WCF & ASMX

But I used EPPuls. dlll file its Open Source dll. Please check below link for Reference. http://epplus.codeplex.com/.

Code :

string excelFileName = @"E:\GOPI_16122016.xls";
 DataTable dt = ds.Tables[0];
 string worksheetsName = "Report";
 ExcelPackage pack = new ExcelPackage();
 ExcelWorksheet wrkSht = pack.Workbook.Worksheets.Add(worksheetsName);
 wrkSht.Cells["A1"].LoadFromDataTable(dt, true);
 pack.SaveAs(new FileInfo(excelFileName));
  MailMessage msg = new MailMessage();
                msg.To.Add(new MailAddress("[email protected]", "GOPIKRISHNA"));

                msg.From = new MailAddress("[email protected]", "Domain");
                msg.Subject = "REPORTS";
                string str = "Dear Team,<br/> Please find the Reports Details <br/><br/>";
                Attachment attachment = new System.Net.Mail.Attachment(excelFileName);
                msg.Attachments.Add(attachment);
                msg.Body = str ;
                msg.IsBodyHtml = true;
                SmtpClient client = new SmtpClient();
                client.UseDefaultCredentials = false;
                client.Credentials = new System.Net.NetworkCredential("[email protected]", "");
                client.Port = 25; // You can use Port 25 if 587 is blocked (mine is!)
                client.Host = "[email protected]";
                client.DeliveryMethod = SmtpDeliveryMethod.Network;
                client.EnableSsl = true;
                client.Send(msg);

edited Dec 16, 2016 at 4:44

answered Dec 16, 2016 at 4:06

gopi krishna

112 bronze badges

1 Comment

Andrés Robinet Over a year ago

If you could elaborate a bit more on your answer, clean up the code, and adapt it a bit to the specific case of downloading an excel file rather than e-mail it (i.e. the Response part), I think you would not only help the OP but also many other people. EPPlus is SO much powerful and simple than many other alternatives

Mike Gledhill · Accepted Answer · 2014-01-09 07:59:21Z

0

You are welcome to download and use my free C# library to export a DataSet to a "real" Excel .xlsx file.

It uses the Microsoft OpenXML libraries, and takes just one line of code.

Export to Excel

It looks like your code is for an ASP.Net web application, so using my library, your code would just look like this:

protected void ExportToExcel(object sender, EventArgs e)
{
    CreateExcelFile.CreateExcelDocument(dsResult, "YourExcelfilename.xlsx", Response);
}

Just one line of code, and you'll get a real Excel file written to your page's Response.

It will contain one Worksheet per DataTable in your DataSet.

answered Jan 9, 2014 at 7:59

Mike Gledhill

29.4k8 gold badges160 silver badges163 bronze badges

Comments

alex.pulver · Accepted Answer · 2014-06-02 07:55:49Z

0

Here is some souce code sample about how to export dataset to Excel. You can use ExcelWorksheet.easy_insertDataSet method to insert multiple datatables.

About the other question, check this code for bar chart and adjust the code to generate bar chart like this:

xlsChart.easy_setChartType(Chart.CHART_TYPE_BAR_CLUSTERED);

answered Jun 2, 2014 at 7:55

alex.pulver

2,1232 gold badges34 silver badges33 bronze badges

Collectives™ on Stack Overflow

Export dataset to the excel sheet in asp.net c#

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related