标签云

微信群

扫码加入我们

WeChat QR Code

How can I create an Excel Spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?


The "...without installing Ms Office?" part of the question sounds very unprofessional. You can generate any type of file from a C# program (with an xls or xlsx file being one of them). It's not a requirement that there is a program on your computer that can read it (say, binary).

2019年06月25日56分57秒

Mike The "without requiring Excel to be installed" piece has nothing to do with being professional.It's about dependencies.The original text of the question was worded as: "Ideally, I would like open source so I don't have to add any third party dependencies to my code, and I would like to avoid using Excel directly to create the file (using OLE Automation.)"It's unfortunate the question was drastically simplified.

2019年06月25日56分57秒

ExcelLibrary has been superseded by the exceptional EPPlus - epplus.codeplex.com.Jan updates it regularly.Have been using it and it is one of the finest open source projects we've worked with.

2019年06月25日56分57秒

It should be noted that ExcelLibrary has a lot of performance issues when dealing with large datasets(larger than 5000 rows with lots of columns). Currently doing a heavy modification of the code base at work so we can use it in a project.

2019年06月26日56分57秒

EPPlus seems far less buggy than ExcelLibrary, BUT it is GPL and therefore only a solution for open source projects.

2019年06月25日56分57秒

-1 If you're going to post sample code you might as well make sure it's correct. Use the Dispose method of this interface to explicitly release unmanaged resources in conjunction with the garbage collector. The consumer of an object can call this method when the object is no longer needed.

2019年06月25日56分57秒

What about ClosedXML? I may prove to be useful in your projects.

2019年06月26日56分57秒

My completely-free library also lets you export any DataSet, DataTable or List<> directly into an Excel 2007 .xlsx file, using Open XML.Full source code, and demo, available here: mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

2019年06月25日56分57秒

License is now LGPL, release notes here: epplus.codeplex.com/releases/view/79802

2019年06月25日56分57秒

The examples were helpful. I was able to change my code from using Microsoft interop library (horribly slow) to this library (version 4.x) in a couple hours. My benchmark writes a file with two tabs and about 750,000 cells. Using MS interop it took 13 minutes. Using EPPlus it took 10 seconds, a roughly 80x speedup. Very happy!

2019年06月25日56分57秒

For clarity in this thread, the LGPL allows the software to be linked to without the infective part of the GPL occuring. You only need to open source changes you make to ClosedXml or if you directly put the source code (as opposed to referencing the ClosedXml assemblies) inside of your application then you need to open source your application.

2019年06月25日56分57秒

Paul Chernoch:We populate large Excel sheets with interop very quickly.The secret is to do a bulk update.Create a object [,] block, populate that, then write that matrix to Excel at one time:excelWorksheet.get_Range(range).Value2 = block;

2019年06月25日56分57秒

A note on NPOI - Row and Column references are zero-based. Does work well for populating an existing template.

2019年06月26日56分57秒

Important to note that the DLL for this is just over 5 MB and limited to Office 2007 formats.But certainly the easiest and fastest solution which works for me.

2019年06月26日56分57秒

Just a heads up that v2.5 is out and can be downloaded here.

2019年06月26日56分57秒

The SDK models the XML into classes, so that each XML tag is mapped to a tag, and then you have to build the class hierarchy (each instance has a collection of child instances/tags) correctly. This means you have to know the XML structure of an Excel file, which is very complicated. It's much easier to use a wrapper such as EPPlus, mentioned above, which simplifies things.

2019年06月25日56分57秒

A great sample of Microsoft Open XML SDK - Open XML Writer can be found at polymathprogrammer.com/2012/08/06/…Or see Stack Overflow solution stackoverflow.com/questions/11370672/…

2019年06月26日56分57秒

I found Microsoft Open XML SDK's Open XML Writer to be great.Using the solutions above, (Especially Vincent Tom's sample (Poly Math)), it's easy to build a writer that streams through big sets of data, and writes records in a manner similiar and not too much more complex to what you'd do for CSV; but that you're instead writing xml.Open XML is the mindset that Microsoft considers it's new Office formats in.And you can always rename them from .xslx to .zip files if you feel like poking at their XML contents.

2019年06月26日56分57秒

Can someone confirm if this works when running in x64?I am pretty sure Jet only works if your app is compiled or running in 32-bit mode.

2019年06月25日56分57秒

I've just tested this connection and it failed on a Windows Server 2008 R2 x64 RC, seems like one have to install the 2007 Office System Driver: Data Connectivity Components [microsoft.com/downloads/…

1970年01月01日00分03秒

Be very careful with this -- it's a big ugly cludge (for example, sometimes it guesses a column type and discards all the data that does not fit).

2019年06月26日56分57秒

One should be very careful if using this method. I've found it very flaky for data that isn't in a perfect format.

2019年06月25日56分57秒

As a person who had to use OleDb in a big project, I say STAY AWAY FROM IT! It sometimes is not able to retrieve a cell value just because it couldn't understand the format. It doesn't have a delete operation. It works totally different and unpredictable even with a slightest provider change. I'd say go for a proven commercial solution.

2019年06月25日56分57秒

You have a great product but I think a lot of people here are expecting free solutions. That might explain the down votes.

2019年06月25日56分57秒

Be careful with ExcelPackage if you need to support XLS. I had a hard time with it and eventually switched to ExcelLibrary.

2019年06月25日56分57秒

Definitely true. ExcelPackage/ExML is only a good option if you need the XLSX support.

2019年06月25日56分57秒

Note that ExcelPackage has a successor: EPPlus (epplus.codeplex.com) which supports XLSX. My only concern, compared to NPOI for example, is performance, e.g. when there is a lot of columns.

2019年06月25日56分57秒

It's so ad hoc but it works (not to mention excel issuing a warning on opening) and is so simple, it deserves to have a place as a solution. Though only for showing that you can export an excel file :))

2019年06月25日56分57秒

This solution worked fine for me, just note you cannot use .xlsx extension

2019年06月26日56分57秒

Some people at my organization can't open excel files made this way in Office 2010 and above.Don't know what the problem is, but I had to roll my own OpenXML implementation. (see Sogger's answer)

2019年06月25日56分57秒

I tried using this in a project that builds pretty large Excel sheets. Excellent library, but extremely poor in performance. I just did a comparison for the project I'm working on: ClosedXML (v 0.53.3) took 92,489 ms whereas EPPlus (v 2.9.03, for testing - we can't use because it's GPL) took 16,500 ms.

2019年06月26日56分57秒

Druid the license is LGPL assuming you don't modify the source code to ClosedXML it is free to use epplus.codeplex.com/license

2019年06月25日56分57秒

But you have to make sure that you dispose of everything manually, otherwise you will leak memory

2019年06月25日56分57秒

Ricky B: Also, in my experience with the interop is that it does use excel.Every time we used it, if Excel wasn't installed on the machine, we would get COM exceptions.

2019年06月25日56分57秒

With the OLE, even with very careful disposals, it eventually leaks memory or crashes. This is argueably OK for attended applications/ workstations, but for servers is not recommended (MS has a KB stating this).For our server, we just reboot it nightly. Again, that works OK.

2019年06月25日56分57秒

Geoffrey: ah OK you are going to make me work for it :) -->support.microsoft.com/kb/257757Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application...

2019年06月25日56分57秒

I'm coming to this discussion after struggling more than a week on interop, and unless your needs are very simple, this is not gonna work. The support for formatting your spreadsheet is abysmal, which is arguably the reason for generating an .xls file and not just a flat .csv file. For example, have you tried outputting more than 911 characters in a cell, or have you tried setting the width of merged cells in a consistent manner? I have, and I can't tell you how much I hate this crap now... Do yourself a favor and go with one of the free libraries mentioned on this discussion.

1970年01月01日00分03秒

This seems a bit misleading, as you are asking for a donation to get all of the features.

2019年06月26日56分57秒

That's partly true: The completely free version will generate a perfect .xlsx file for you, and all source code is provided.If you donate $10 or more to one of those two charities (of which I receive absolutely nothing), then you get a "better" version showing how to do formatting, dates, etc. Given the cost of third-party products, I reckon donating $10 to a good cause instead is well worth it !

2019年06月26日56分57秒

NET::ERR_CERT_COMMON_NAME_INVALID as at January 2019

2019年06月25日56分57秒

To be more precise, you can use Aspose.Cells for .NET in order to create Excel (XLS, XLSX) files in your .NET application.

2019年06月25日56分57秒

Yes you can, if you don't mind paying a minimum license fee of $999.Try the MikesKnowledgeBase library... which is $999 cheaper than this !!

2019年06月25日56分57秒

This one works on both .net and java,and is not expensive. SmartXLSsmartxls.com

2019年06月26日56分57秒

No, this generates HTML marked as an Excel file rather than a true Excel file. Yes, Excel itself will open that OK but other programs that consume spreadsheets - including Microsoft's free Excel viewer, for example - won't accept it. You'd do better to create a real Excel file using one of the libraries here.

2019年06月25日56分57秒

You should also use System.Net.Mime.ContentDisposition to generate the content-disposition header text rather than a string append - that'll cope with filenames that contains spaces etc. correctly.

2019年06月25日56分57秒

Like the article says though, that's XML that Excel will read rather than actually being an XLS file, which means that it might only work in Excel and not other programs that read spreadsheets. But it's probably better than the equivalent HTML table answers here!

2019年06月25日56分57秒

Supports xlsx ? OpenXML ?

2019年06月26日56分57秒

I haven't tried this Open XML SDK solution yet but Wow, I will definitely check it out. I've worked with tools like this for many years and didn't know about this one. I've published my own simple FOSS for converting files to XLSX with .NET: github.com/TonyGravagno/NebulaXConvert

2019年06月25日56分57秒