CODEWITHSUNDEEP
c#asp.netexport-to-excel
Arun Kumar T
Arun Kumar T

Reputation: 640

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

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.

Upvotes: 8

Views: 55673

Answers (4)

gopi krishna
gopi krishna

Reputation: 11

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

Upvotes: 1

alex.pulver
alex.pulver

Reputation: 2125

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

Upvotes: 0

Mike Gledhill
Mike Gledhill

Reputation: 29161

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.

Upvotes: 0

Piotr Stapp
Piotr Stapp

Reputation: 19830

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.

Upvotes: 4

Related Questions