如何在c#中从数据集或数据表中导出excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9478351/
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
How to export excel from dataset or datatable in c#?
提问by Kathirvel
I want to export data from Dataset or Data Table to Excel file in C# without using Gridview.
我想在不使用 Gridview 的情况下将数据从数据集或数据表导出到 C# 中的 Excel 文件。
采纳答案by Pranay Rana
Get more ways : 9 Solutions to Export Data to Excel for ASP.NET
获取更多方法：将数据导出到 Excel for ASP.NET 的 9 种解决方案
I have this code but for this you need to include Excel Com Component
我有这个代码，但为此你需要包含 Excel Com 组件
Add reference of Microsoft.Office.Interop.Excel.dllin your project will do task for you.
Microsoft.Office.Interop.Excel.dll在您的项目中添加引用将为您完成任务。
using Excel = Microsoft.Office.Interop.Excel;
public static bool ExportDataTableToExcel(DataTable dt, string filepath)
{
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;
try
{
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
// Get the Active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "Data";
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
// Resize the columns
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
// Save the sheet and close
oSheet = null;
oRange = null;
oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlExclusive,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
}
catch
{
throw;
}
finally
{
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
return true;
}
回答by silverfighter
you can use
您可以使用
using Microsoft.Office.Interop.Excel;
to work with excel documents "native"...
使用“本机”excel 文档...
Using C# to Create an Excel Document http://www.codeproject.com/Articles/20228/Using-C-to-Create-an-Excel-Document
使用 C# 创建 Excel 文档 http://www.codeproject.com/Articles/20228/Using-C-to-Create-an-Excel-Document
But most of the common report Generator/Designers can easily export to excel also check for Reporting Servicesif you are using SQL SERVER
但是大多数常见的报告生成器/设计器都可以轻松导出到 excel如果您使用的是SQL SERVER，还可以检查Reporting Services
回答by Jakub Konecki
I would recommend EPPlus- this solution doesn't require COM nor interop dlls and is very fast. Perfectly suited for web scenarios.
我会推荐EPPlus- 这个解决方案不需要 COM 也不需要互操作 dll，而且速度非常快。非常适合网络场景。
http://nuget.org/packages/EPPlus
http://nuget.org/packages/EPPlus
private void DumpExcel(DataTable tbl)
{
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:C1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
}
}
}
回答by Oliver
Just in case somebody else used the chosen solution and run into the "object does not contain a definition for get_Range" exception. I found the solution here: Worksheet get_Range throws exception. I hope this will save your time.
以防万一其他人使用所选的解决方案并遇到“对象不包含 get_Range 的定义”异常。我在这里找到了解决方案：Worksheet get_Range throws exception。我希望这会节省您的时间。
