将数据集导出到asp.net c#中的excel表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19374216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export dataset to the excel sheet in asp.net c#
提问by Arun Kumar T
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
我试图将具有 4 个表的数据集导出到 Excel 表，不幸的是我不能。我有将数据表导出到excel的代码。因此，我没有调用数据集，而是调用了代码中的“ExportToExcel”函数，将数据表导出到 excel 4 次。但是一旦它创建了第一个工作表，它就会停止控制流。控件不调用第二个函数 ("ExportToExcel(dsResult.Tables[2], "AthleteSentCount");") 这里是代码
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.
如果有人知道使用条形图将数据集导出到 excel，请帮助我。否则，请给出您对问题的解决方案。
采纳答案by Piotr Stapp
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.
此外，如果您解决此问题，您的代码将生成 4 个 excel 文件。
There is an existing code for creating Excel sheets on stack overflow: How to add additional worksheets to an Excel from DataTableThe only change you will have to made will be saving xlsx to stream and transmit this stream to user.
有一个用于在堆栈溢出时创建 Excel 工作表的现有代码：如何从 DataTable 向 Excel 添加额外的工作表您必须进行的唯一更改是保存 xlsx 以流式传输并将此流传输给用户。
回答by Mike Gledhill
You are welcome to download and use my free C# library to export a DataSet to a "real" Excel .xlsx file.
欢迎您下载并使用我的免费 C# 库将数据集导出到“真正的”Excel .xlsx 文件。
It uses the Microsoft OpenXML libraries, and takes just one line of code.
它使用 Microsoft OpenXML 库，并且只需要一行代码。
It looks like your code is for an ASP.Net web application, so using my library, your code would just look like this:
看起来您的代码是针对 ASP.Net Web 应用程序的，因此使用我的库，您的代码将如下所示：
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.
只需一行代码，您就会得到一个真正的 Excel 文件，它会写入您页面的Response。
It will contain one Worksheet per DataTable in your DataSet.
它将在您的数据集中每个数据表包含一个工作表。
回答by alex.pulver
Here is some souce code sample about how to export dataset to Excel. You can use ExcelWorksheet.easy_insertDataSet method to insert multiple datatables.
这是一些关于如何将数据集导出到 Excel 的源代码示例。您可以使用 ExcelWorksheet.easy_insertDataSet 方法插入多个数据表。
About the other question, check this code for bar chartand adjust the code to generate bar chart like this:
关于另一个问题，请检查条形图的此代码并调整代码以生成这样的条形图：
xlsChart.easy_setChartType(Chart.CHART_TYPE_BAR_CLUSTERED);
回答by gopi krishna
Written in WCF & ASMX
用 WCF 和 ASMX 编写
But I used EPPuls. dlll file its Open Source dll. Please check below link for Reference. http://epplus.codeplex.com/.
但我使用了 EPPuls。dll 文件它的开源 dll。请查看以下链接以供参考。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);
