How can I create an Excel Spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?
如何使用c#创建一个Excel电子表格,而不需要在运行代码的机器上安装Excel ?
40 个解决方案
#1
857
You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:
你可以使用一个叫做ExcelLibrary的图书馆。这是一个免费的开源库,发布在谷歌代码上:
ExcelLibrary
This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.
这看起来是您上面提到的PHP ExcelWriter的一个端口。它还不会写入新的.xlsx格式,但是他们正在努力添加这个功能。
It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.
它非常简单,小巧,易于使用。此外,它还有一个DataSetHelper,允许您使用数据集和DataTables轻松处理Excel数据。
ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.
ExcelLibrary似乎仍然只适用于旧的Excel格式(。xls文件),但可能会为新的2007/2010格式提供支持。
You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files).
你也可以使用EPPlus,它只适用于Excel 2007/2010格式文件(。xlsx文件)。
There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.
如注释所示,每个库都有一些已知的bug。总而言之,随着时间的推移,EPPlus似乎是最好的选择。它似乎更积极地更新和记录。
Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)
如下@АртёмЦарионов所指出的,EPPlus支持数据透视表和ExcelLibrary可能有一些支持(ExcelLibrary数据透视表的问题)
Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU Lesser General Public License (LGPL)
这里有一些快速参考的链接:ExcelLibrary - GNU Lesser GPL EPPlus - GNU较小通用公共许可证(LGPL)
Here some example code for ExcelLibrary:
下面是ExcelLibrary的一些示例代码:
Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:
下面是一个从数据库中获取数据并从中创建工作簿的示例。请注意,ExcelLibrary代码是底部的一行:
//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
//Add the table to the data set
ds.Tables.Add(dt);
//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.
创建Excel文件就这么简单。您也可以手工创建Excel文件,但是上面的功能让我印象深刻。
#2
483
If you are happy with the xlsx format, try my codeplex GitHub project. EPPlus. Started it with the source from ExcelPackage, but today it's a total rewrite. Supports ranges, cell styling, charts, shapes, pictures, namesranges, autofilter and a lot of other stuff.
如果您对xlsx格式感到满意,请尝试我的codeplex GitHub项目。EPPlus。从ExcelPackage的源代码开始,但是现在已经完全重写了。支持范围,单元样式,图表,形状,图片,名称范围,自动过滤器和许多其他东西。
#3
142
I've used with success the following open source projects:
我成功地使用了以下开源项目:
-
ExcelPackage for OOXML formats (Office 2007)
OOXML格式的ExcelPackage (Office 2007)
-
NPOI for .XLS format (Office 2003). NPOI 2.0 (Alpha) also supports XLSX.
NPOI用于. xls格式(Office 2003)。NPOI 2.0 (Alpha)也支持XLSX。
Take a look at my blog posts:
看看我的博客:
Creating Excel spreadsheets .XLS and .XLSX in C#
在c#中创建Excel电子表格。xls和。xlsx。
NPOI with Excel Table and dynamic Chart
具有Excel表和动态图表的NPOI。
#4
135
And what about using Open XML SDK 2.0 for Microsoft Office?
对于Microsoft Office使用Open XML SDK 2.0怎么样?
A few benefits:
几个好处:
- Doesn't require Office installed
- 不需要安装
- Made by Microsoft = decent MSDN documentation
- 由Microsoft =像样的MSDN文档制作
- Just one .Net dll to use in project
- 只有一个。net dll在项目中使用。
- SDK comes with many tools like diff, validator, etc
- SDK附带了许多工具,如diff、validator等
Links:
链接:
- Download SDK
- 下载SDK
- Main MSDN Landing
- 主要MSDN着陆
- "How Do I..." starter page
- “我怎么…”启动页。
- blogs.MSDN brian_jones announcing SDK
- 博客。MSDN brian_jones宣布SDK
- blogs.MSDN brian_jones describing SDK handling large files without crashing (unlike DOM method)
- 博客。MSDN brian_jones描述了SDK,它可以在不崩溃的情况下处理大型文件(与DOM方法不同)
#5
91
You can use OLEDB to create and manipulate Excel files. Check this: Reading and Writing Excel using OLEDB.
您可以使用OLEDB来创建和操作Excel文件。检查这个:使用OLEDB来读写Excel。
Typical example:
典型的例子:
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\temp\\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] string, [Column2] string)", conn);
cmd.ExecuteNonQuery();
}
EDIT - Some more links:
编辑-更多链接:
- Hey, Scripting Guy! How Can I Read from Excel Without Using Excel?
- 嘿,脚本的家伙!不使用Excel怎么看Excel?
- How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
- 如何使用ADO。使用Visual Basic .NET检索和修改Excel工作簿中的记录
- Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory
- 使用ADO阅读和编写Excel电子表格。净c# DbProviderFactory
#6
70
The commercial solution, SpreadsheetGear for .NET will do it.
这个商业解决方案就是。net的SpreadsheetGear。
You can see live ASP.NET (C# and VB) samples here and download an evaluation version here.
你可以看到活ASP。NET (c#和VB)的样本在这里下载一个评估版本。
Disclaimer: I own SpreadsheetGear LLC
免责声明:我拥有SpreadsheetGear LLC公司
#7
53
A few options I have used:
我有几个选择:
If XLSX is a must: ExcelPackage is a good start but died off when the developer quit working on it. ExML picked up from there and added a few features. ExML isn't a bad option, I'm still using it in a couple of production websites.
如果XLSX是必须的:ExcelPackage是一个好的开始,但是当开发人员停止开发它时,它就消失了。ExML从那里获得并添加了一些特性。ExML不是一个坏的选择,我仍然在一些产品网站上使用它。
For all of my new projects, though, I'm using NPOI, the .NET port of Apache POI. NPOI 2.0 (Alpha) also supports XLSX.
不过,对于我所有的新项目,我都在使用NPOI, Apache POI的。net端口。NPOI 2.0 (Alpha)也支持XLSX。
#8
51
An extremely lightweight option may be to use HTML tables. Just create head, body, and table tags in a file, and save it as a file with an .xls extension. There are Microsoft specific attributes that you can use to style the output, including formulas.
一个非常轻量级的选项可能是使用HTML表。只需在文件中创建head、body和表标记,并将其保存为扩展名为.xls的文件。您可以使用Microsoft特定的属性来样式化输出,包括公式。
I realize that you may not be coding this in a web application, but here is an example of the composition of an Excel file via an HTML table. This technique could be used if you were coding a console app, desktop app, or service.
我意识到您可能不是在web应用程序中编写这个代码,但是这里有一个通过HTML表组成Excel文件的示例。如果您正在编写一个控制台应用程序、桌面应用程序或服务,则可以使用该技术。
#9
39
You can use ExcelXmlWriter http://www.carlosag.net/Tools/ExcelXmlWriter/
您可以使用ExcelXmlWriter http://www.carlosag.net/Tools/ExcelXmlWriter/
It works fine.
它将正常工作。
#10
36
You actually might want to check out the interop classes. You say no OLE (which this isn't), but the interop classes are very easy to use.
实际上,您可能想要检查interop类。您说没有OLE(这不是),但是interop类非常容易使用。
You might be impressed if you haven't tried them.
如果你没有尝试过,你可能会对它们印象深刻。
Please be warned of Microsoft's stance on this:
请注意微软对此的立场:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
微软目前不建议也不支持Microsoft Office应用程序从任何无人参与的、非交互式客户端应用程序或组件(包括ASP、ASP)实现自动化。NET、DCOM和NT服务),因为在此环境中运行Office时,Office可能会显示不稳定的行为和/或死锁。
#11
34
If you're creating Excel 2007/2010 files give this open source project a try: http://closedxml.codeplex.com
如果您正在创建Excel 2007/2010文件,请尝试一下这个开源项目:http://closedxml.codeplex.com
It provides an object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).
它提供了一种面向对象的方法来操作文件(类似于VBA),而无需处理XML文档的麻烦。它可以被任何。net语言使用,比如c#和Visual Basic (VB)。
ClosedXML allows you to create Excel 2007/2010 files without the Excel application. The typical example is creating Excel reports on a web server:
ClosedXML允许您在没有Excel应用程序的情况下创建Excel 2007/2010文件。典型的例子是在web服务器上创建Excel报告:
var workbook = new XLWorkbook(); var worksheet = workbook.Worksheets.Add("Sample Sheet"); worksheet.Cell("A1").Value = "Hello World!"; workbook.SaveAs("HelloWorld.xlsx");
#12
25
Here's a completely free C# library, which lets you export from a DataSet
, DataTable
or List<>
into a genuine Excel 2007 .xlsx file, using the OpenXML libraries:
这里有一个完全免费的c#库,您可以使用OpenXML库从数据集、DataTable或List<>导出到真正的Excel 2007 .xlsx文件:
http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
Full source code is provided - free of charge - along with instructions, and a demo application.
完整的源代码是免费提供的,并附有说明和演示应用程序。
After adding this class to your application, you can export your DataSet to Excel in just one line of code:
在将这个类添加到应用程序后,您可以将数据集导出为Excel,只需一行代码:
CreateExcelFile.CreateExcelDocument(myDataSet, "C:\\Sample.xlsx");
It doesn't get much simpler than that...
没有比这更简单的了……
And it doesn't even require Excel to be present on your server.
它甚至不需要Excel出现在服务器上。
#13
18
You could consider creating your files using the XML Spreadsheet 2003 format. This is a simple XML format using a well documented schema.
您可以考虑使用XML电子表格2003格式创建文件。这是一种使用文档良好的模式的简单XML格式。
#14
14
Syncfusion Essential XlsIO can do this. It has no dependency on Microsoft office and also has specific support for different platforms.
Syncfusion Essential XlsIO可以做到这一点。它不依赖于Microsoft office,也对不同的平台有特定的支持。
- ASP.NET
- ASP.NET
- ASP.NET MVC
- ASP。NET MVC
- UWP
- UWP
- Xamarin
- Xamarin的
- WPF and Windows Forms
- WPF和Windows窗体
- Windows Service and batch based operations
- Windows服务和基于批处理的操作。
Code sample:
代码示例:
//Creates a new instance for ExcelEngine.
ExcelEngine excelEngine = new ExcelEngine();
//Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileName);
//To-Do some manipulation|
//To-Do some manipulation
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
//Save the workbook in file system as xlsx format
workbook.SaveAs(outputFileName);
The whole suite of controls is available for free through the community license program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.
如果您符合条件,整个控件套件可以通过社区许可计划免费获得(收入少于100万美元)。注意:我在Syncfusion公司工作。
#15
13
You may want to take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.
您可能想看看http://www.gemboxsoftware.com/GBSpreadsheetFree.htm。
They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs.
他们有一个免费的版本,有所有的功能,但是如果你需要的话,每页150行,每工作簿5张。
I haven't had need to use it myself yet, but does look interesting.
我自己还不需要使用它,但是看起来很有趣。
#16
11
The various Office 2003 XML libraries avaliable work pretty well for smaller excel files. However, I find the sheer size of a large workbook saved in the XML format to be a problem. For example, a workbook I work with that would be 40MB in the new (and admittedly more tightly packed) XLSX format becomes a 360MB XML file.
各种Office 2003 XML库对于更小的excel文件都非常适用。但是,我发现以XML格式保存的大型工作簿的大小是个问题。例如,我使用的工作簿在新的XLSX格式(当然应该是更紧凑的格式)中会有40MB成为一个360MB的XML文件。
As far as my research has taken me, there are two commercial packages that allow output to the older binary file formats. They are:
就我的研究而言,有两个商业包允许输出到旧的二进制文件格式。它们是:
- Gembox
- Gembox
- ComponentOne Excel
- ComponentOne Excel
Neither are cheap (500USD and 800USD respectively, I think). but both work independant of Excel itself.
也不便宜(我想分别是500美元和800美元)。但两者都独立于Excel本身。
What I would be curious about is the Excel output module for the likes of OpenOffice.org. I wonder if they can be ported from Java to .Net.
我好奇的是OpenOffice.org之类的Excel输出模块。我想知道它们是否可以从Java移植到。net。
#17
10
I agree about generating XML Spreadsheets, here's an example on how to do it for C# 3 (everyone just blogs about it in VB 9 :P) http://www.aaron-powell.com/linq-to-xml-to-excel
我同意生成XML电子表格,这里有一个关于如何为c# 3做这个的例子(每个人都在VB 9中写关于它的博客)http://www.aaron- powell.com/linqto XML -to-excel
#18
10
Just want to add another reference to a third party solution that directly addresses your issue: http://www.officewriter.com
只需添加另一个对第三方解决方案的引用,该解决方案可以直接解决您的问题:http://www.officewriter.com。
(Disclaimer: I work for SoftArtisans, the company that makes OfficeWriter)
(免责声明:我为SoftArtisans公司工作,该公司专门生产OfficeWriter)
#19
10
Well,
好吧,
you can also use a third party library like Aspose.
您还可以使用第三方库,如Aspose。
This library has the benefit that it does not require Excel to be installed on your machine which would be ideal in your case.
这个库的优点是,它不需要在您的计算机上安装Excel,这在您的情况下是理想的。
#20
10
OpenXML is also a good alternative that helps avoid installing MS Excel on Server.The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.
OpenXML也是一个很好的选择,可以帮助避免在服务器上安装MS Excel。Microsoft提供的Open XML SDK 2.0简化了操作Open XML包和包中的底层Open XML模式元素的任务。Open XML应用程序编程接口(API)封装了开发人员在Open XML包上执行的许多常见任务。
Check this out OpenXML: Alternative that helps avoid installing MS Excel on Server
请查看OpenXML: Alternative,这有助于避免在服务器上安装MS Excel
#22
8
I've just recently used FlexCel.NET and found it to be an excellent library! I don't say that about too many software products. No point in giving the whole sales pitch here, you can read all the features on their website.
我最近用了FlexCel。发现它是一个优秀的图书馆!我没有说太多的软件产品。没有必要在这里做整个推销,你可以阅读他们网站上的所有功能。
It is a commercial product, but you get the full source if you buy it. So I suppose you could compile it into your assembly if you really wanted to. Otherwise it's just one extra assembly to xcopy - no configuration or installation or anything like that.
这是一种商业产品,但如果你买了它,你会得到全部的资源。如果你真的想的话,我想你可以把它编译成你的程序集。否则,它只是xcopy的一个额外程序集——没有配置或安装,或者类似的东西。
I don't think you'll find any way to do this without third-party libraries as .NET framework, obviously, does not have built in support for it and OLE Automation is just a whole world of pain.
我不认为没有第三方库就可以实现这一点,因为。net框架显然没有内置对它的支持,而且OLE自动化是一个痛苦的世界。
#23
8
Here's a way to do it with LINQ to XML, complete with sample code:
这里有一种使用LINQ到XML的方法,并附带示例代码:
Quickly Import and Export Excel Data with LINQ to XML
使用LINQ快速导入和导出Excel数据到XML
It's a little complex, since you have to import namespaces and so forth, but it does let you avoid any external dependencies.
这有点复杂,因为您需要导入名称空间等等,但是它确实允许您避免任何外部依赖。
(Also, of course, it's VB .NET, not C#, but you can always isolate the VB .NET stuff in its own project to use XML Literals, and do everything else in C#.)
(当然,它是VB . net,而不是c#,但是您总是可以在自己的项目中隔离VB . net,以使用XML文字,并在c#中执行其他所有操作)。
#24
8
Some 3rd party component vendors like Infragistics or Syncfusion provide very good Excel export capabilities that do not require Microsoft Excel to be installed.
一些第三方组件供应商,如infrag或Syncfusion提供了非常好的Excel导出功能,不需要安装Microsoft Excel。
Since these vendors also provide advanced UI grid components, these components are particularly handy if you want the style and layout of an excel export to mimic the current state of a grid in the user interface of your application.
由于这些供应商还提供高级的UI网格组件,如果您希望excel导出的样式和布局能够模拟应用程序的用户界面中网格的当前状态,那么这些组件就特别方便。
If your export is intended to be executed server side with emphasis on the data to be exported and with no link to the UI, then I would go for one of the free open source options (e.g. ExcelLibrary).
如果您的导出要在服务器端执行,重点是要导出的数据,并且没有与UI的链接,那么我将使用一个免费的开放源码选项(例如ExcelLibrary)。
I have previously been involved with projects that attempted to use server side automation on the Microsoft Office suite. Based on this experience I would strongly recommend against that approach.
我之前参与过一些项目,试图在Microsoft Office套件中使用服务器端自动化。基于这一经验,我强烈建议反对这种做法。
#25
8
public class GridViewExportUtil
{
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
GridViewExportUtil.PrepareControlForExport(current);
}
}
}
}
Hi this solution is to export your grid view to your excel file it might help you out
你好,这个解决方案是将网格视图导出到您的excel文件中,它可能会帮助您解决这个问题
#26
8
You can create nicely formatted Excel files using this library: http://officehelper.codeplex.com/documentation
See below sample:
您可以使用这个库创建格式良好的Excel文件:http://officehelper.codeplex.com/documentation,参见下面的示例:
using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
helper.CurrentSheetName = "Sheet1";
helper.CurrentPosition = new CellRef("C3");
//the template xlsx should contains the named range "header"; use the command "insert"/"name".
helper.InsertRange("header");
//the template xlsx should contains the named range "sample1";
//inside this range you should have cells with these values:
//<name> , <value> and <comment>, which will be replaced by the values from the getSample()
CellRangeTemplate sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> {"name", "value", "comment"});
helper.InsertRange(sample1, getSample());
//you could use here other named ranges to insert new cells and call InsertRange as many times you want,
//it will be copied one after another;
//even you can change direction or the current cell/sheet before you insert
//typically you put all your "template ranges" (the names) on the same sheet and then you just delete it
helper.DeleteSheet("Sheet3");
}
where sample look like this:
样例如下:
private IEnumerable<List<object>> getSample()
{
var random = new Random();
for (int loop = 0; loop < 3000; loop++)
{
yield return new List<object> {"test", DateTime.Now.AddDays(random.NextDouble()*100 - 50), loop};
}
}
#27
6
Some useful Excel automation in C# , u can find from the following link.
一些有用的Excel自动化在c#,你可以从以下链接找到。
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm
bolton.
博尔顿。
#28
6
Look at samples how to create Excel files.
查看示例如何创建Excel文件。
There are examples in C# and VB.NET
c#和VB.NET中有一些例子
It manages XSL XSLX and CSV Excel files.
它管理XSL XSLX和CSV Excel文件。
http://www.devtriogroup.com/ExcelJetcell/Samples
http://www.devtriogroup.com/ExcelJetcell/Samples
#29
6
The simplest and fastest way to create an Excel file from C# is to use the Open XML Productivity Tool. The Open XML Productivity Tool comes with the Open XML SDK installation. The tool reverse engineers any Excel file into C# code. The C# code can then be used to re-generate that file.
从c#创建Excel文件最简单、最快的方法是使用Open XML Productivity工具。Open XML生产率工具附带了Open XML SDK安装。该工具将任何Excel文件转换为c#代码。然后可以使用c#代码重新生成该文件。
An overview of the process involved is:
有关过程的概述如下:
- Install the Open XML SDK with the tool.
- 使用该工具安装打开的XML SDK。
- Create an Excel file using the latest Excel client with desired look. Name it
DesiredLook.xlsx
. - 使用最新的Excel客户端创建一个Excel文件,并显示所需的外观。它DesiredLook.xlsx名称。
- With the tool open
DesiredLook.xlsx
and click the Reflect Code button near the top. - 用工具打开需要的外观。xlsx并单击顶部附近的反射代码按钮。
- The C# code for your file will be generated in the right pane of the tool. Add this to your C# solution and generate files with that desired look.
- 您的文件的c#代码将在工具的右边窗格中生成。将它添加到c#解决方案中,并生成具有所需外观的文件。
As a bonus, this method works for any Word and PowerPoint files. As the C# developer, you will then make changes to the code to fit your needs.
另外,这种方法适用于任何Word和PowerPoint文件。作为c#开发人员,您将更改代码以满足您的需求。
I have developed a simple WPF app on github which will run on Windows for this purpose. There is a placeholder class called GeneratedClass
where you can paste the generated code. If you go back one version of the file, it will generate an excel file like this:
我在github上开发了一个简单的WPF应用程序,可以在Windows上运行。有一个占位符类名为GeneratedClass,您可以在其中粘贴生成的代码。如果返回文件的一个版本,它将生成这样的excel文件:
#30
5
Have you ever tried sylk?
你试过sylk吗?
We used to generate excelsheets in classic asp as sylk and right now we're searching for an excelgenerater too.
我们曾经在经典的asp中生成excelsheets作为sylk,现在我们也在寻找一个优秀的生成器。
The advantages for sylk are, you can format the cells.
sylk的优点是,可以格式化单元格。
#1
857
You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:
你可以使用一个叫做ExcelLibrary的图书馆。这是一个免费的开源库,发布在谷歌代码上:
ExcelLibrary
This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.
这看起来是您上面提到的PHP ExcelWriter的一个端口。它还不会写入新的.xlsx格式,但是他们正在努力添加这个功能。
It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.
它非常简单,小巧,易于使用。此外,它还有一个DataSetHelper,允许您使用数据集和DataTables轻松处理Excel数据。
ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.
ExcelLibrary似乎仍然只适用于旧的Excel格式(。xls文件),但可能会为新的2007/2010格式提供支持。
You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files).
你也可以使用EPPlus,它只适用于Excel 2007/2010格式文件(。xlsx文件)。
There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.
如注释所示,每个库都有一些已知的bug。总而言之,随着时间的推移,EPPlus似乎是最好的选择。它似乎更积极地更新和记录。
Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)
如下@АртёмЦарионов所指出的,EPPlus支持数据透视表和ExcelLibrary可能有一些支持(ExcelLibrary数据透视表的问题)
Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU Lesser General Public License (LGPL)
这里有一些快速参考的链接:ExcelLibrary - GNU Lesser GPL EPPlus - GNU较小通用公共许可证(LGPL)
Here some example code for ExcelLibrary:
下面是ExcelLibrary的一些示例代码:
Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:
下面是一个从数据库中获取数据并从中创建工作簿的示例。请注意,ExcelLibrary代码是底部的一行:
//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
//Add the table to the data set
ds.Tables.Add(dt);
//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.
创建Excel文件就这么简单。您也可以手工创建Excel文件,但是上面的功能让我印象深刻。
#2
483
If you are happy with the xlsx format, try my codeplex GitHub project. EPPlus. Started it with the source from ExcelPackage, but today it's a total rewrite. Supports ranges, cell styling, charts, shapes, pictures, namesranges, autofilter and a lot of other stuff.
如果您对xlsx格式感到满意,请尝试我的codeplex GitHub项目。EPPlus。从ExcelPackage的源代码开始,但是现在已经完全重写了。支持范围,单元样式,图表,形状,图片,名称范围,自动过滤器和许多其他东西。
#3
142
I've used with success the following open source projects:
我成功地使用了以下开源项目:
-
ExcelPackage for OOXML formats (Office 2007)
OOXML格式的ExcelPackage (Office 2007)
-
NPOI for .XLS format (Office 2003). NPOI 2.0 (Alpha) also supports XLSX.
NPOI用于. xls格式(Office 2003)。NPOI 2.0 (Alpha)也支持XLSX。
Take a look at my blog posts:
看看我的博客:
Creating Excel spreadsheets .XLS and .XLSX in C#
在c#中创建Excel电子表格。xls和。xlsx。
NPOI with Excel Table and dynamic Chart
具有Excel表和动态图表的NPOI。
#4
135
And what about using Open XML SDK 2.0 for Microsoft Office?
对于Microsoft Office使用Open XML SDK 2.0怎么样?
A few benefits:
几个好处:
- Doesn't require Office installed
- 不需要安装
- Made by Microsoft = decent MSDN documentation
- 由Microsoft =像样的MSDN文档制作
- Just one .Net dll to use in project
- 只有一个。net dll在项目中使用。
- SDK comes with many tools like diff, validator, etc
- SDK附带了许多工具,如diff、validator等
Links:
链接:
- Download SDK
- 下载SDK
- Main MSDN Landing
- 主要MSDN着陆
- "How Do I..." starter page
- “我怎么…”启动页。
- blogs.MSDN brian_jones announcing SDK
- 博客。MSDN brian_jones宣布SDK
- blogs.MSDN brian_jones describing SDK handling large files without crashing (unlike DOM method)
- 博客。MSDN brian_jones描述了SDK,它可以在不崩溃的情况下处理大型文件(与DOM方法不同)
#5
91
You can use OLEDB to create and manipulate Excel files. Check this: Reading and Writing Excel using OLEDB.
您可以使用OLEDB来创建和操作Excel文件。检查这个:使用OLEDB来读写Excel。
Typical example:
典型的例子:
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\temp\\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] string, [Column2] string)", conn);
cmd.ExecuteNonQuery();
}
EDIT - Some more links:
编辑-更多链接:
- Hey, Scripting Guy! How Can I Read from Excel Without Using Excel?
- 嘿,脚本的家伙!不使用Excel怎么看Excel?
- How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
- 如何使用ADO。使用Visual Basic .NET检索和修改Excel工作簿中的记录
- Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory
- 使用ADO阅读和编写Excel电子表格。净c# DbProviderFactory
#6
70
The commercial solution, SpreadsheetGear for .NET will do it.
这个商业解决方案就是。net的SpreadsheetGear。
You can see live ASP.NET (C# and VB) samples here and download an evaluation version here.
你可以看到活ASP。NET (c#和VB)的样本在这里下载一个评估版本。
Disclaimer: I own SpreadsheetGear LLC
免责声明:我拥有SpreadsheetGear LLC公司
#7
53
A few options I have used:
我有几个选择:
If XLSX is a must: ExcelPackage is a good start but died off when the developer quit working on it. ExML picked up from there and added a few features. ExML isn't a bad option, I'm still using it in a couple of production websites.
如果XLSX是必须的:ExcelPackage是一个好的开始,但是当开发人员停止开发它时,它就消失了。ExML从那里获得并添加了一些特性。ExML不是一个坏的选择,我仍然在一些产品网站上使用它。
For all of my new projects, though, I'm using NPOI, the .NET port of Apache POI. NPOI 2.0 (Alpha) also supports XLSX.
不过,对于我所有的新项目,我都在使用NPOI, Apache POI的。net端口。NPOI 2.0 (Alpha)也支持XLSX。
#8
51
An extremely lightweight option may be to use HTML tables. Just create head, body, and table tags in a file, and save it as a file with an .xls extension. There are Microsoft specific attributes that you can use to style the output, including formulas.
一个非常轻量级的选项可能是使用HTML表。只需在文件中创建head、body和表标记,并将其保存为扩展名为.xls的文件。您可以使用Microsoft特定的属性来样式化输出,包括公式。
I realize that you may not be coding this in a web application, but here is an example of the composition of an Excel file via an HTML table. This technique could be used if you were coding a console app, desktop app, or service.
我意识到您可能不是在web应用程序中编写这个代码,但是这里有一个通过HTML表组成Excel文件的示例。如果您正在编写一个控制台应用程序、桌面应用程序或服务,则可以使用该技术。
#9
39
You can use ExcelXmlWriter http://www.carlosag.net/Tools/ExcelXmlWriter/
您可以使用ExcelXmlWriter http://www.carlosag.net/Tools/ExcelXmlWriter/
It works fine.
它将正常工作。
#10
36
You actually might want to check out the interop classes. You say no OLE (which this isn't), but the interop classes are very easy to use.
实际上,您可能想要检查interop类。您说没有OLE(这不是),但是interop类非常容易使用。
You might be impressed if you haven't tried them.
如果你没有尝试过,你可能会对它们印象深刻。
Please be warned of Microsoft's stance on this:
请注意微软对此的立场:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
微软目前不建议也不支持Microsoft Office应用程序从任何无人参与的、非交互式客户端应用程序或组件(包括ASP、ASP)实现自动化。NET、DCOM和NT服务),因为在此环境中运行Office时,Office可能会显示不稳定的行为和/或死锁。
#11
34
If you're creating Excel 2007/2010 files give this open source project a try: http://closedxml.codeplex.com
如果您正在创建Excel 2007/2010文件,请尝试一下这个开源项目:http://closedxml.codeplex.com
It provides an object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).
它提供了一种面向对象的方法来操作文件(类似于VBA),而无需处理XML文档的麻烦。它可以被任何。net语言使用,比如c#和Visual Basic (VB)。
ClosedXML allows you to create Excel 2007/2010 files without the Excel application. The typical example is creating Excel reports on a web server:
ClosedXML允许您在没有Excel应用程序的情况下创建Excel 2007/2010文件。典型的例子是在web服务器上创建Excel报告:
var workbook = new XLWorkbook(); var worksheet = workbook.Worksheets.Add("Sample Sheet"); worksheet.Cell("A1").Value = "Hello World!"; workbook.SaveAs("HelloWorld.xlsx");
#12
25
Here's a completely free C# library, which lets you export from a DataSet
, DataTable
or List<>
into a genuine Excel 2007 .xlsx file, using the OpenXML libraries:
这里有一个完全免费的c#库,您可以使用OpenXML库从数据集、DataTable或List<>导出到真正的Excel 2007 .xlsx文件:
http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm
Full source code is provided - free of charge - along with instructions, and a demo application.
完整的源代码是免费提供的,并附有说明和演示应用程序。
After adding this class to your application, you can export your DataSet to Excel in just one line of code:
在将这个类添加到应用程序后,您可以将数据集导出为Excel,只需一行代码:
CreateExcelFile.CreateExcelDocument(myDataSet, "C:\\Sample.xlsx");
It doesn't get much simpler than that...
没有比这更简单的了……
And it doesn't even require Excel to be present on your server.
它甚至不需要Excel出现在服务器上。
#13
18
You could consider creating your files using the XML Spreadsheet 2003 format. This is a simple XML format using a well documented schema.
您可以考虑使用XML电子表格2003格式创建文件。这是一种使用文档良好的模式的简单XML格式。
#14
14
Syncfusion Essential XlsIO can do this. It has no dependency on Microsoft office and also has specific support for different platforms.
Syncfusion Essential XlsIO可以做到这一点。它不依赖于Microsoft office,也对不同的平台有特定的支持。
- ASP.NET
- ASP.NET
- ASP.NET MVC
- ASP。NET MVC
- UWP
- UWP
- Xamarin
- Xamarin的
- WPF and Windows Forms
- WPF和Windows窗体
- Windows Service and batch based operations
- Windows服务和基于批处理的操作。
Code sample:
代码示例:
//Creates a new instance for ExcelEngine.
ExcelEngine excelEngine = new ExcelEngine();
//Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileName);
//To-Do some manipulation|
//To-Do some manipulation
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
//Save the workbook in file system as xlsx format
workbook.SaveAs(outputFileName);
The whole suite of controls is available for free through the community license program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.
如果您符合条件,整个控件套件可以通过社区许可计划免费获得(收入少于100万美元)。注意:我在Syncfusion公司工作。
#15
13
You may want to take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.
您可能想看看http://www.gemboxsoftware.com/GBSpreadsheetFree.htm。
They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs.
他们有一个免费的版本,有所有的功能,但是如果你需要的话,每页150行,每工作簿5张。
I haven't had need to use it myself yet, but does look interesting.
我自己还不需要使用它,但是看起来很有趣。
#16
11
The various Office 2003 XML libraries avaliable work pretty well for smaller excel files. However, I find the sheer size of a large workbook saved in the XML format to be a problem. For example, a workbook I work with that would be 40MB in the new (and admittedly more tightly packed) XLSX format becomes a 360MB XML file.
各种Office 2003 XML库对于更小的excel文件都非常适用。但是,我发现以XML格式保存的大型工作簿的大小是个问题。例如,我使用的工作簿在新的XLSX格式(当然应该是更紧凑的格式)中会有40MB成为一个360MB的XML文件。
As far as my research has taken me, there are two commercial packages that allow output to the older binary file formats. They are:
就我的研究而言,有两个商业包允许输出到旧的二进制文件格式。它们是:
- Gembox
- Gembox
- ComponentOne Excel
- ComponentOne Excel
Neither are cheap (500USD and 800USD respectively, I think). but both work independant of Excel itself.
也不便宜(我想分别是500美元和800美元)。但两者都独立于Excel本身。
What I would be curious about is the Excel output module for the likes of OpenOffice.org. I wonder if they can be ported from Java to .Net.
我好奇的是OpenOffice.org之类的Excel输出模块。我想知道它们是否可以从Java移植到。net。
#17
10
I agree about generating XML Spreadsheets, here's an example on how to do it for C# 3 (everyone just blogs about it in VB 9 :P) http://www.aaron-powell.com/linq-to-xml-to-excel
我同意生成XML电子表格,这里有一个关于如何为c# 3做这个的例子(每个人都在VB 9中写关于它的博客)http://www.aaron- powell.com/linqto XML -to-excel
#18
10
Just want to add another reference to a third party solution that directly addresses your issue: http://www.officewriter.com
只需添加另一个对第三方解决方案的引用,该解决方案可以直接解决您的问题:http://www.officewriter.com。
(Disclaimer: I work for SoftArtisans, the company that makes OfficeWriter)
(免责声明:我为SoftArtisans公司工作,该公司专门生产OfficeWriter)
#19
10
Well,
好吧,
you can also use a third party library like Aspose.
您还可以使用第三方库,如Aspose。
This library has the benefit that it does not require Excel to be installed on your machine which would be ideal in your case.
这个库的优点是,它不需要在您的计算机上安装Excel,这在您的情况下是理想的。
#20
10
OpenXML is also a good alternative that helps avoid installing MS Excel on Server.The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.
OpenXML也是一个很好的选择,可以帮助避免在服务器上安装MS Excel。Microsoft提供的Open XML SDK 2.0简化了操作Open XML包和包中的底层Open XML模式元素的任务。Open XML应用程序编程接口(API)封装了开发人员在Open XML包上执行的许多常见任务。
Check this out OpenXML: Alternative that helps avoid installing MS Excel on Server
请查看OpenXML: Alternative,这有助于避免在服务器上安装MS Excel
#21
#22
8
I've just recently used FlexCel.NET and found it to be an excellent library! I don't say that about too many software products. No point in giving the whole sales pitch here, you can read all the features on their website.
我最近用了FlexCel。发现它是一个优秀的图书馆!我没有说太多的软件产品。没有必要在这里做整个推销,你可以阅读他们网站上的所有功能。
It is a commercial product, but you get the full source if you buy it. So I suppose you could compile it into your assembly if you really wanted to. Otherwise it's just one extra assembly to xcopy - no configuration or installation or anything like that.
这是一种商业产品,但如果你买了它,你会得到全部的资源。如果你真的想的话,我想你可以把它编译成你的程序集。否则,它只是xcopy的一个额外程序集——没有配置或安装,或者类似的东西。
I don't think you'll find any way to do this without third-party libraries as .NET framework, obviously, does not have built in support for it and OLE Automation is just a whole world of pain.
我不认为没有第三方库就可以实现这一点,因为。net框架显然没有内置对它的支持,而且OLE自动化是一个痛苦的世界。
#23
8
Here's a way to do it with LINQ to XML, complete with sample code:
这里有一种使用LINQ到XML的方法,并附带示例代码:
Quickly Import and Export Excel Data with LINQ to XML
使用LINQ快速导入和导出Excel数据到XML
It's a little complex, since you have to import namespaces and so forth, but it does let you avoid any external dependencies.
这有点复杂,因为您需要导入名称空间等等,但是它确实允许您避免任何外部依赖。
(Also, of course, it's VB .NET, not C#, but you can always isolate the VB .NET stuff in its own project to use XML Literals, and do everything else in C#.)
(当然,它是VB . net,而不是c#,但是您总是可以在自己的项目中隔离VB . net,以使用XML文字,并在c#中执行其他所有操作)。
#24
8
Some 3rd party component vendors like Infragistics or Syncfusion provide very good Excel export capabilities that do not require Microsoft Excel to be installed.
一些第三方组件供应商,如infrag或Syncfusion提供了非常好的Excel导出功能,不需要安装Microsoft Excel。
Since these vendors also provide advanced UI grid components, these components are particularly handy if you want the style and layout of an excel export to mimic the current state of a grid in the user interface of your application.
由于这些供应商还提供高级的UI网格组件,如果您希望excel导出的样式和布局能够模拟应用程序的用户界面中网格的当前状态,那么这些组件就特别方便。
If your export is intended to be executed server side with emphasis on the data to be exported and with no link to the UI, then I would go for one of the free open source options (e.g. ExcelLibrary).
如果您的导出要在服务器端执行,重点是要导出的数据,并且没有与UI的链接,那么我将使用一个免费的开放源码选项(例如ExcelLibrary)。
I have previously been involved with projects that attempted to use server side automation on the Microsoft Office suite. Based on this experience I would strongly recommend against that approach.
我之前参与过一些项目,试图在Microsoft Office套件中使用服务器端自动化。基于这一经验,我强烈建议反对这种做法。
#25
8
public class GridViewExportUtil
{
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a form to contain the grid
Table table = new Table();
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
/// <summary>
/// Replace any of the contained controls with literals
/// </summary>
/// <param name="control"></param>
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
GridViewExportUtil.PrepareControlForExport(current);
}
}
}
}
Hi this solution is to export your grid view to your excel file it might help you out
你好,这个解决方案是将网格视图导出到您的excel文件中,它可能会帮助您解决这个问题
#26
8
You can create nicely formatted Excel files using this library: http://officehelper.codeplex.com/documentation
See below sample:
您可以使用这个库创建格式良好的Excel文件:http://officehelper.codeplex.com/documentation,参见下面的示例:
using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
helper.CurrentSheetName = "Sheet1";
helper.CurrentPosition = new CellRef("C3");
//the template xlsx should contains the named range "header"; use the command "insert"/"name".
helper.InsertRange("header");
//the template xlsx should contains the named range "sample1";
//inside this range you should have cells with these values:
//<name> , <value> and <comment>, which will be replaced by the values from the getSample()
CellRangeTemplate sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> {"name", "value", "comment"});
helper.InsertRange(sample1, getSample());
//you could use here other named ranges to insert new cells and call InsertRange as many times you want,
//it will be copied one after another;
//even you can change direction or the current cell/sheet before you insert
//typically you put all your "template ranges" (the names) on the same sheet and then you just delete it
helper.DeleteSheet("Sheet3");
}
where sample look like this:
样例如下:
private IEnumerable<List<object>> getSample()
{
var random = new Random();
for (int loop = 0; loop < 3000; loop++)
{
yield return new List<object> {"test", DateTime.Now.AddDays(random.NextDouble()*100 - 50), loop};
}
}
#27
6
Some useful Excel automation in C# , u can find from the following link.
一些有用的Excel自动化在c#,你可以从以下链接找到。
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm
bolton.
博尔顿。
#28
6
Look at samples how to create Excel files.
查看示例如何创建Excel文件。
There are examples in C# and VB.NET
c#和VB.NET中有一些例子
It manages XSL XSLX and CSV Excel files.
它管理XSL XSLX和CSV Excel文件。
http://www.devtriogroup.com/ExcelJetcell/Samples
http://www.devtriogroup.com/ExcelJetcell/Samples
#29
6
The simplest and fastest way to create an Excel file from C# is to use the Open XML Productivity Tool. The Open XML Productivity Tool comes with the Open XML SDK installation. The tool reverse engineers any Excel file into C# code. The C# code can then be used to re-generate that file.
从c#创建Excel文件最简单、最快的方法是使用Open XML Productivity工具。Open XML生产率工具附带了Open XML SDK安装。该工具将任何Excel文件转换为c#代码。然后可以使用c#代码重新生成该文件。
An overview of the process involved is:
有关过程的概述如下:
- Install the Open XML SDK with the tool.
- 使用该工具安装打开的XML SDK。
- Create an Excel file using the latest Excel client with desired look. Name it
DesiredLook.xlsx
. - 使用最新的Excel客户端创建一个Excel文件,并显示所需的外观。它DesiredLook.xlsx名称。
- With the tool open
DesiredLook.xlsx
and click the Reflect Code button near the top. - 用工具打开需要的外观。xlsx并单击顶部附近的反射代码按钮。
- The C# code for your file will be generated in the right pane of the tool. Add this to your C# solution and generate files with that desired look.
- 您的文件的c#代码将在工具的右边窗格中生成。将它添加到c#解决方案中,并生成具有所需外观的文件。
As a bonus, this method works for any Word and PowerPoint files. As the C# developer, you will then make changes to the code to fit your needs.
另外,这种方法适用于任何Word和PowerPoint文件。作为c#开发人员,您将更改代码以满足您的需求。
I have developed a simple WPF app on github which will run on Windows for this purpose. There is a placeholder class called GeneratedClass
where you can paste the generated code. If you go back one version of the file, it will generate an excel file like this:
我在github上开发了一个简单的WPF应用程序,可以在Windows上运行。有一个占位符类名为GeneratedClass,您可以在其中粘贴生成的代码。如果返回文件的一个版本,它将生成这样的excel文件:
#30
5
Have you ever tried sylk?
你试过sylk吗?
We used to generate excelsheets in classic asp as sylk and right now we're searching for an excelgenerater too.
我们曾经在经典的asp中生成excelsheets作为sylk,现在我们也在寻找一个优秀的生成器。
The advantages for sylk are, you can format the cells.
sylk的优点是,可以格式化单元格。