Login/Register
Import and Export Excel - What is the best library? [closed]
Asked Answered
Solved c#asp.netexcelexport-to-excelimport-from-excel
S

20

185

In one of our ASP.NET applications in C#, we take a certain data collection (SubSonic collection) and export it to Excel. We also want to import Excel files in a specific format. I'm looking for a library I can use for this purpose.

Requirements:

  • Excel 2007 files (Does Excel 2003 support over 64k rows? I need more than that.)
  • Does not require Excel on the server
  • Takes a typed collection and, if it can, tries to put numeric fields as numeric in Excel.
  • Works well with large files (100k to 10M) - fast enough.
  • Doesn't crash when exporting GUIDs!
  • Does not cost a crapload of money (no enterprise library like aspose). Free is always great, but can be a commercial library.

What library do you recommend? Have you used it for large quantities of data? Are there other solutions?

Right now, I am using a simple tool that generates HTML that is loaded by Excel later on, but I am losing some capabilities, plus Excel complains when we load it. I don't need to generate charts or anything like that, just export raw data.

I am thinking of flat CSV files, but Excel is a customer requirement. I can work with CSV directly, if I had a tool to convert to and from Excel. Given Excel 2007 is an xml-based (and zipped) file format, I am guessing this kind of library should be easy to find. However, what matters most to me are your comments and opinions.

EDIT: Ironically, in my opinion and following the answer with the most votes, the best Excel import&export library is no export at all. This is not the case for all scenarios, but it is for mine. XLS files support only 64k rows. XLSX supports up to 1M. The free libraries that I've tried feature bad performance (one second to load one row when you have 200k rows). I haven't tried the paid ones, as I feel they are overpriced for the value they deliver when all you need is a fast XLSX<->CSV conversion routine.

Selfhelp answered 14/1, 2009 at 20:20 Comment(5)
"Given Excel 2007 is an xml-based (and zipped) file format, I am guessing this kind of library should be easy to find" -- hah! That's like saying "Excel 97 is byte-based, so this kind of library should be easy to find". XML doesn't imply simplicity, and OOXML is the poster child for how to make XML underspecified and indecipherably complex. :-) – Watkins
Actually you not entirely correct in making that assumption. There are tools available that make building a well formed OOXML document at least for XLSX, very easy to do with C#.NET. – Blister
I have created a site proposal to give questions like this an official home away from Stack Overflow. It's called Code Recommendations Help make it a reality by joining and asking questions now! – Oxblood
What about Spire.xls. It is only $800 for the Pro Edition. You get all your conversions as well as your excel import or exporter. e-iceblue.com/Introduce/… – Monotint
GemBox.Spreadsheet is another solution that's worth checking out, super fast and has both free and pro versions. Also, the pro version is rather cheap and doesn't have any hidden crappy costs for deployment like most of them. – Thrill
O
40

I'm going to throw my hand in for flat csv files, if only because you've got the greatest control over the code. Just make sure that you read in the rows and process them one at a time (reading the document to the end and splitting will eat up all of your memory - same with writing, stream it out).

Yes, the user will have to save-as CSV in excel before you can process it, but perhaps this limitation can be overcome by training and providing clear instructions on the page?

Finally, when you export to the customer, if you set the mime type to text/csv, Excel is usually mapped to that type so it appears to the user to be 'an Excel file'.

Ollie answered 14/1, 2009 at 23:34 Comment(9)
I agree, but in my case it would be good to support xls(x) files directly. However, do you know of any csv <-> xls(x) tools? – Selfhelp
No, I've never needed to do xls. Sorry :-/ – Ollie
I tried CSV approach too, but there are several issues with it. For example, what if you want to have a multi-line text in a cell? I couldn't make Excel import such a CSV. – Ustulation
Actually, you can have mult-line text in a CSV file. Just try this as your file contents: cell 1,"this\nis\nmultiline",cell 3 – Kirshbaum
CSV has its place but the poster asked about Excel, I assume that he must want Excel, not CSV. – Illegible
CSV falls down when exporting columns like 0345. Excel automatically trims this out to 345. Which is not at all helpful when that leading digit is important. – Touzle
HTML table with an excel file extension seems to work quite well... it will parse some CSS in a peculiar way for stuff like formatting multiple lines, colors and so forth - without actually having to create a native Excel file – Slattern
"Save as"? No. If the customer is using Excel, then why should they save off a second, much more limited file to interact with your product? – Christianly
csv is no replacement to Excel/Open Office native format in many ways. I.e. number formatting, cell styling, charts, formulas are not possible. Mutiple sheets in a workbook are also not possible to implement with csv. csv works for very simple use cases but fails in many cases. – Tasiatasiana
D
42

I discovered the Open XML SDK since my original answer. It provides strongly typed classes for spreadsheet objects, among other things, and seems to be fairly easy to work with. I am going to use it for reports in one of my projects. Alas, version 2.0 is not supposed to get released until late 2009 or 2010.

Drown answered 11/2, 2009 at 4:16 Comment(6)
Very interesting! Have you tested it using large quantities of data? – Selfhelp
I have not done any performance test. I will mostly charts and and single-page reports, so throughput is not an issue for me. It appears to be as fast as managed code can be, though. – Drown
@Jason Kealey: this is really the best answer on this post - export becomes irrelevant with SpreadsheetML. The data is all accessible from within the file. If you need the data in a different format, provide a transform via an XSLT or via Linq. – Mesocarp
This doesn't work for ".xls" files at all, though. – Roup
github.com/OfficeDev/Open-XML-SDK it's now opensource and github-hosted (I like where MS is headed recently) – Lapboard
Open XML SDK has a forward only writer API (similar to XmlWriter) what is fast and uses very little memory so is perfect for large amounts of data. All libraries what follow a DOM-like approach create the xlsx file in memory so if you have to export many rows they will kill your app servers - might not be an issue on standalone desktop applications. blogs.msdn.com/b/brian_jones/archive/2010/06/22/… And yes, like the name says it is for xlsx only, no xls. But the xlsx adapter is free for Excel and is downloaded automatically. – Tasiatasiana
O
40

I'm going to throw my hand in for flat csv files, if only because you've got the greatest control over the code. Just make sure that you read in the rows and process them one at a time (reading the document to the end and splitting will eat up all of your memory - same with writing, stream it out).

Yes, the user will have to save-as CSV in excel before you can process it, but perhaps this limitation can be overcome by training and providing clear instructions on the page?

Finally, when you export to the customer, if you set the mime type to text/csv, Excel is usually mapped to that type so it appears to the user to be 'an Excel file'.

Ollie answered 14/1, 2009 at 23:34 Comment(9)
I agree, but in my case it would be good to support xls(x) files directly. However, do you know of any csv <-> xls(x) tools? – Selfhelp
No, I've never needed to do xls. Sorry :-/ – Ollie
I tried CSV approach too, but there are several issues with it. For example, what if you want to have a multi-line text in a cell? I couldn't make Excel import such a CSV. – Ustulation
Actually, you can have mult-line text in a CSV file. Just try this as your file contents: cell 1,"this\nis\nmultiline",cell 3 – Kirshbaum
CSV has its place but the poster asked about Excel, I assume that he must want Excel, not CSV. – Illegible
CSV falls down when exporting columns like 0345. Excel automatically trims this out to 345. Which is not at all helpful when that leading digit is important. – Touzle
HTML table with an excel file extension seems to work quite well... it will parse some CSS in a peculiar way for stuff like formatting multiple lines, colors and so forth - without actually having to create a native Excel file – Slattern
"Save as"? No. If the customer is using Excel, then why should they save off a second, much more limited file to interact with your product? – Christianly
csv is no replacement to Excel/Open Office native format in many ways. I.e. number formatting, cell styling, charts, formulas are not possible. Mutiple sheets in a workbook are also not possible to implement with csv. csv works for very simple use cases but fails in many cases. – Tasiatasiana
U
36

the last version of ExcelPackage that is free under LGPL for commercial projects is, https://www.nuget.org/packages/EPPlus/4.5.3.3

If you need latest and greatest, Commercial license is available here: https://epplussoftware.com/en/LicenseOverview/

I'm still fighting with the export to excel function since my application should export some data to excel-template 2007

this project seems fine to me, and the developer is very responsive to bugs and issues.

United answered 28/4, 2010 at 5:54 Comment(5)
Works fine, but it's licensed under GPL - e.g. any software using it must be available in plain source code, too.... not always a good choice... – Cryptocrystalline
Export in a native format that is convenient to you, write a prog. that uses EPPlus to convert to Excel, make that free. Make your main prog use that as default, but allow other "plugins", hey presto your real code is free from the GPL. – Granicus
It appears that this is now licensed under the LGPL, so you are fine to use it as a linked library without the copyleft restrictions. – Plating
Note that if you need to produce an excel file that has big strings in it, this library has a tendency to randomly produce 'unreadable content' errors in excel. – Gallery
Beware that EPPlus leaks memory, isn't really good for large amounts of data. – Tasiatasiana
H
20

I've been using ClosedXML and it works great!

ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice 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).

Hakan answered 7/11, 2011 at 13:31 Comment(2)
I love the irony in the name... – Baillie
Used it as well and same experience. Works well and is very flexible. – Nadbus
C
14

SpreadsheetGear for .NET reads and writes CSV / XLS / XLSX and does more.

You can see live ASP.NET samples with C# and VB source code here and download a free trial here.

Of course I think SpreadsheetGear is the best library to import / export Excel workbooks in ASP.NET - but I am biased. You can see what some of our customers say on the right hand side of this page.

Disclaimer: I own SpreadsheetGear LLC

Columbium answered 29/8, 2009 at 18:26 Comment(1)
@Joe Erickson : Can you tell as to how does one go about reading a CSV and then producing an XML out of the CSV just read using Spreadsheet gear and using that XLS producing a resultant XML file which contains that structure? Or can we use Spreadsheetgear to produce an XML from a CSV directly? – Takashi
K
13

NPOI For Excel 2003 Open Source http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html

Kalamazoo answered 24/2, 2010 at 8:35 Comment(1)
I used this library the other day looking into this issue. It is an excellent library! – Guidotti
D
5

I've used Flexcel in the past and it was great. But this was more for programmatically creating and updating excel worksheets.

Dichromatism answered 14/1, 2009 at 20:33 Comment(2)
I can't see that this supports Excel 2007 (xlsx). As xls only supports 64k rows, this is a limitation for me. – Selfhelp
@Jason Kealey - Flexcel does now support Excel 2007 and 2010. – Abercrombie
D
5

CSV export is simple, easy to implement, and fast. There is one potential issue worth noting, though. Excel (up to 2007) does not preserve leading zeros in CSV files. This will garble ZIP codes, product ids, and other textual data containing numeric values. There is one trick that will make Excel import the values correctly (using delimiters and prefix values with the = sign, if I remember correctly, e.g. ..,="02052",...). If you have users who will do post-processing tasks with the CSV, they need to be aware that they need to change the format to XLS and not save the file back to CSV. If they do, leading zeros will be lost for good.

Drown answered 31/1, 2009 at 4:26 Comment(2)
For anything that should be preserved as text, simply put an ' (apostrophe) at the beginning – Holsinger
Another fun fact: I cannot open a a comma seperated file in a lot of locales, like german. Which makes csv a poor format to share data with international contacts – Howzell
P
4

For years, I have used JExcel for this, an excellent open-source Java project. It was also .NET-able by using J# to compile it, and I have also had great success with it in this incarnation. However, recently I needed to migrate the code to native .NET to support a 64-bit IIS application in which I create Excel output. The 32-bit J# version would not load.

The code for CSharpJExcel is LGPL and is available currently at this page, while we prepare to deploy it on the JExcel SourceForge site. It will compile with VS2005 or VS2008. The examples in the original JExcel documentation will pretty well move over intact to the .NET version.

Hope it is helpful to someone out here.

Pascale answered 17/3, 2010 at 19:2 Comment(1)
Both links no longer work... but I did find some snippets of it and it looks like it's more Java friendly, not so much for .NET developers. However, I did find another library that's opposite, it is a native .NET library (GemBox.Spreadsheet), that was also ported to Java (GemBox.Spreadsheet for Java). – Rhombic
P
3

I've worked with excel jetcell for a long time and can really recommend it. http://www.devtriogroup.com/exceljetcell

  • Commercial product
  • Excel files XLS & XLSX
  • Based on own engine in pure net.
Papistry answered 13/4, 2011 at 7:20 Comment(0)
D
3

The following site demonstrates how to export a DataTable, DataSet or List<> into a "proper" Excel 2007 .xlsx file (rather than exporting a .csv file, and getting Excel to open it).

It uses the OpenXML libraries, so you don't need to have Excel installed on your server.

Mikes Knowledge Base - ExportToExcel

All of the source code is given, free of charge, aswell as a demo application.

It's very easy to add to your own applications, you just need to call one function, passing in an Excel filename, and your data source:

DataSet ds = CreateSampleData();
string excelFilename = "C:\\Sample.xlsx";
CreateExcelFile.CreateExcelDocument(ds, excelFilename);

Hope this helps.

Dopester answered 1/12, 2011 at 12:25 Comment(1)
This link is dead. – Diplomacy
P
2

Check the ExcelPackage project, it uses the Office Open XML file format of Excel 2007, it's lightweight and open source...

Plascencia answered 14/1, 2009 at 20:24 Comment(3)
Looked good but one comment says it is bad with large files (my scenario) – Selfhelp
Interesting - it is licensed as GPL, not LGPL. Therefore, it must be used in GPL applications. (Also, unfortunate that development seems to have stopped.) – Selfhelp
I tried ExcelPackage, but had to abandon it - it fails when you try put single quotes (') in a cell. – Ustulation
G
2

You can use Microsoft.Jet.OLEDB.4.0

Guyot answered 28/5, 2009 at 17:17 Comment(4)
One of my requirements is not to have Excel running on the server. – Selfhelp
i don't think so it's running!! – Arabinose
MS office is not required for "Microsoft.Jet.OLEDB.4.0"( for xls) "Microsoft.ACE.OLEDB.12.0 for xlsx". you have to use only Drivers , so no chance to run Excel on server @Jason Kealey – Earthwork
I used this for XLS (not the original requirement), and for completeness... There are some important issues: (1) It's 32-bit only, so you will have to set IIS to allow that. (2) It is dreadfully slow on export. (3) It has poorly documented row and column limits. (4) It insists on "type sniffing" imported columns unless you have registry access, and it generally gets it wrong. – Acinaciform
C
2

I've tried CSharpJExcel and wouldn't recommend it, at least not until there is some documentation available. Contrary to the developers comments it is not a straight native port.

Chinchilla answered 22/10, 2010 at 1:26 Comment(0)
W
2

I know this is quite late, but I feel compelled to answer xPorter (writing) and xlReader (reading) from xPortTools.Net. We tested quite a few libraries and nothing came close in the way of performance (I'm talking about writing millions of rows in seconds here). Can't say enough good things about these products!

Wilburn answered 3/6, 2011 at 0:8 Comment(0)
C
1

There's a pretty good article and library on CodeProject by Yogesh Jagota:

Excel XML Import-Export Library

I've used it to export data from SQL queries and other data sources to Excel - works just fine for me.

Cheers

Cryptocrystalline answered 14/1, 2009 at 20:40 Comment(1)
Interesting, but requires XML files. Can't read/write xls or xlsx files. – Selfhelp
S
1

We have just identified a similar need. And I think it's important to consider the user experience.

We nearly got sidetracked along the same:

  1. Prepare/work in spreadsheet file
  2. Save file
  3. Import file
  4. Work with data in system

... workflow

Add-in Express allows you to create a button within Excel without all that tedious mucking about with VSTO. Then the workflow becomes:

  1. Prepare/work in spreadsheet file
  2. Import file (using button inside Excel)
  3. Work with data in system

Have the code behind the button use the "native" Excel API (via Add-in Express) and push direct into the recipient system. You can't get much more transparent for the developer or the user. Worth considering.

Smarm answered 26/1, 2011 at 9:46 Comment(0)
C
1

You could try the following library, it is easy enough and it is just a light wrapper over Microsoft's Open XML SDK (you can even reuse formatting, styles and even entire worksheets from secondary Excel file) : http://officehelper.codeplex.com

Connell answered 24/2, 2012 at 16:21 Comment(1)
New release of the library was recently published. – Connell
E
0

Spreadsheetgear is the best commercial library we have found and are using. Our company does a lot of advanced excel import and export and Spreadsheetgear supports lots of advanced excel features far beyond anything you can do with simple CSV, and it's fast. It isn't free or very cheap though but worth it because the support is excellent. The developers will actually respond to you if you run into an issue.

Entice answered 20/7, 2010 at 16:35 Comment(0)
T
0

How about the apache POI java library. I havent used it for Excel , but did use it for Word 2007.

Tipster answered 22/10, 2010 at 1:30 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.