ExportToExcel 一个通用的把dataset导出至EXCEL的类

ExportToExcel

一个通用的把dataset导出至EXCEL的类，优点是不需要安装OFFICE，只需要调用方法CreateExcelFile.CreateExcelDocument(myDataSet, myExcelPathFilename);如果是ASP.Net，也可以调用CreateExcelFile.CreateExcelDocument(myDataSet,myExcelFilename, Response);

源代码下载地址：

 http://yunpan.cn/QUTGhdQCsYTQp

 

原文大致翻译如下：

Export data from a DataSet into a realExcel 2007 file

把数据导出到EXCEL2007(2003也可以)

By Mike Gledhill

Ever wanted to add an "Export toExcel" function to your ASP.Net, WinForms or WPF application ?

你想要给你的ASP.Net, WinForms or WPF程序添加导出到EXCEL功能吗？

This free C# and VB.Netlibrary lets you export a DataTable or DataSet of data into a "real"Excel 2007 .xlsx file, using one line of code.

使用下面一行代码就可以实现导出到EXCEL的功能

CreateExcelFile.CreateExcelDocument(myDataSet,myExcelPathFilename);

...or if you're using ASP.Net...

如果你使用ASP.Net...，可以用下面的代码

CreateExcelFile.CreateExcelDocument(myDataSet,myExcelFilename, Response);

You can download the full source codeusing the links below, so you can extend it to add Excelformatting, etc.

你可以点击links below来下载源代码

It uses the OpenXML libraries,rather than Microsoft's Visual Studio Tools for Office (VSTO) libraries,so you don't need to have Excel installed on your server.

它使用了OpenXML 库，而不是微软的OFFICE库，所以你不必安装EXCEL

The CreateExcelFile library

All of the code you'll need is containedin one class, CreateExcelFile, which I've saved in the file CreateExcelFile.cs (or CreateExcelFile.vb forthe VB.Net version).

你只需要CreateExcelFile这一个类就可以了，它保存在CreateExcelFile.cs中

To use this class, you simply call its CreateExcelDocument function,passing it a DataSet variable (which contains the values you want writing intoExcel cells), and the path+filename of the Excel file you wish to create.

你只要使用这个类的CreateExcelDocument 方法，把包含你要导出的数据的dataset和你想要导出的完整文件名做为参数

Each DataTable within your DataSet will besaved into it's own Excel worksheet.

DataSet 中的每一个DataTable会被保存为一个工作薄

// Step 1: Create a DataSet, and put somesample data in it
DataSet ds = CreateSampleData();

// Step 2: Createthe Excel .xlsx file
try
{
    string excelFilename = "C:\\Sample.xlsx";
    CreateExcelFile.CreateExcelDocument(ds, excelFilename);
}
catch (Exception ex)
{ 
    MessageBox.Show("Couldn't create Excelfile.\r\nException: " + ex.Message);
    return;
}

Demo program

The attached Visual Studio 2010 C#and VB.Net WinForms demo shows how easy it is to use the CreateExcelFile library.

Demo说明CreateExcelFile 类库是多么的简单易用

It consists of a simple dialog with onebutton on it (shown below).

它只有一个简单的对话框按钮

When you click on the button, it'll createa DataSet and fill it with some sample data, and prompt you for a location tosave our example Excel file to. It will then call theCreateExcelDocument functionto create an Excel 2007 .xlsx file containing our DataSet's data, then open thefile in Excel for you to admire.

当你点击按钮的时候，将创建一个DataSet并且填充一些示例数据，然后提示你选择EXCEL的保存位置，然后调用 CreateExcelDocument 方法来创建一个EXCEL文件，并且打开这个创建好的文件。

 

Notice how our demo application created aDataSet containing three DataTables in it, called Drivers, Vehicles &Vehicle Owners. The CreateExcelFile library then created anExcel Worksheet for each of these DataTable names, and wrote each DataTable'sdata into it.

在我们的DEMO中，DataSet有三个表Drivers, Vehicles & Vehicle Owners，，有我们创建的EXCEL中，同样有三个相应的工作薄。

Using the "Export to Excel"library in your own application

The CreateExcelFile libraryhas two dependences:

CreateExcelFile 有两个依赖项

• DocumentFormat.OpenXml.dll
  From the Microsoft Open XML SDK library
• WindowsBase.dll
  From the Microsoft .Net Framework library

I have included a copy of these two filesin the source code's lib folder, but ideally, you shoulddownload the most recent version from the Microsoft website.

我的源代码包含有这两个库，你也可以去Microsoft官网上下载最近的。

To use the library in your own code, simplyadd these two files, and the CreateExcelFile.cs file to yourVisual Studio project, then add the two .dll files to the References section ofyour project.

要应用我的代码，你只要添加对这个DLL的引用，把 CreateExcelFile.cs 文件添加到你的项目中就可以了。

Also, you need to make sure that these twofiles have the "Copy Local" field set to true. Without this, when youdeploy your app, it won't copy the .dlls to your bin directory,and IIS will complain that it can't find the OpenXML libraries.

你需要确认OpenXML的 “复制到本地”属性设置为true

Then just call the staticCreateExcelDocument function, as shown above.

然后就像上面讲的那样调用 CreateExcelDocument

CreateExcelFile.CreateExcelDocument(ds,excelFilename);

I have deliberately left this library'ssource code available for you to view and alter, as desired.

我已经把源代码上传，代大家查看和修改。

Exporting from a List<> or DataTable导出集合或表

With a little help from a discussion onCodeGuru, I added two extra functions, so you can now call the CreateExcelDocument functionin three ways:

由于在CodeGuru上的讨论，我添加了额外的方法，

public static boolCreateExcelDocument<T>(List<T> list, string xlsxFilePath)
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
public static bool CreateExcelDocument(DataSet ds, string xlsxFilePath)

..so you should find it really easy toexport your data to Excel from any of these formats.

现在你能发现导致以上三种数据到EXCEL是多么的简单。

Using the library in an ASP.Netapplication在ASP.Net中使用类库

November 2013: With help from my fellowdevelopers, I have made the C# library even easier to use in an ASP.Netapplication. The ExportToExcel class now lets you create anExcel file without writing to a temporary file first. You just need to pass the"Response" as a parameter.

在同事的帮助下，在ASP.Net项目中应用变得更中简单，你只需要把Response作为参数传递即可。

// In this example, I have a defined a Listof my Employee objects.
class Employee;
List<Employee> listOfEmployees = new List<Employee>();

...

// The followingASP.Net code gets run when I click on my "Export to Excel" button.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
    // It doesn't get much easier than this...
    CreateExcelFile.CreateExcelDocument(listOfEmployees,"Employees.xlsx", Response);
}

By default, this functionality is disabledin the C# file, so that non-ASP.Net developers don't get build errors whenattempting to use the library. To enable the functionality, you need tomanually make two small changes:

这个方法在使用c#语言的项目中默认是被禁用的。因此非asp.net开发者使用时并不会出错。如果要解除禁用的话，你需要做两个小的修改：

First, uncomment the first line of code inthe CreateExcelFile.cs file, so it looks like this:

首先，解除 CreateExcelFile.cs 中的第一行注释

#define INCLUDE_WEB_FUNCTIONS

Then add a new Reference, and select theSystem.Web library.

然后，添加对System.Web的引用

And that's it. You can now use the threenew web-friendly functions for exporting to Excel.

你现在可以使用这三个方法了。

public static boolCreateExcelDocument<T>(List<T> list, string filename,System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataTable dt, string filename,System.Web.HttpResponse Response)
public static bool CreateExcelDocument(DataSet ds, string filename,System.Web.HttpResponse Response)

Cool, hey ?

You're welcome to use and adapt this codeas you like, but - please - if you like it, leave me a commentbelow.

欢迎你使用和修改这些代码，并请给我做下评论，如果你愿意的话。

原文地址：http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm#