Import and Export Excel - What is the best library?

user_20893 0
Viewed 203598

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.

export-to-excel import-from-excel excel asp.net c#
20 Answers

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'.

Trav_16850

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.

cdonner_588800
user_2801570

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

user_71400

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.

cdonner_588800

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.

user_2959670

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

CMS_54450

You can use Microsoft.Jet.OLEDB.4.0

user_1138860

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

user_133020

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.

ProgramX_760370
Related