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:
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.
This question is related to
c#
asp.net
excel
export-to-excel
import-from-excel
I've worked with excel jetcell for a long time and can really recommend it. http://www.devtriogroup.com/exceljetcell
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.
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
I've used Flexcel in the past and it was great. But this was more for programmatically creating and updating excel worksheets.
You can use Microsoft.Jet.OLEDB.4.0
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:
... workflow
Add-in Express allows you to create a button within Excel without all that tedious mucking about with VSTO. Then the workflow becomes:
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.
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.
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.
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!
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.
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
Check the ExcelPackage project, it uses the Office Open XML file format of Excel 2007, it's lightweight and open source...
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:
... workflow
Add-in Express allows you to create a button within Excel without all that tedious mucking about with VSTO. Then the workflow becomes:
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.
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.
the new version of ExcelPackage is here http://EPPlus.codeplex.com
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.
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.
I've used Flexcel in the past and it was great. But this was more for programmatically creating and updating excel worksheets.
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.
I've used Flexcel in the past and it was great. But this was more for programmatically creating and updating excel worksheets.
You can use Microsoft.Jet.OLEDB.4.0
Check the ExcelPackage project, it uses the Office Open XML file format of Excel 2007, it's lightweight and open source...
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.
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
Check the ExcelPackage project, it uses the Office Open XML file format of Excel 2007, it's lightweight and open source...
NPOI For Excel 2003 Open Source http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html
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!
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
I've used Flexcel in the past and it was great. But this was more for programmatically creating and updating excel worksheets.
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.
Check the ExcelPackage project, it uses the Office Open XML file format of Excel 2007, it's lightweight and open source...
How about the apache POI java library. I havent used it for Excel , but did use it for Word 2007.
I've worked with excel jetcell for a long time and can really recommend it. http://www.devtriogroup.com/exceljetcell
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.
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.
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
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.
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
How about the apache POI java library. I havent used it for Excel , but did use it for Word 2007.
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
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).
the new version of ExcelPackage is here http://EPPlus.codeplex.com
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.
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.
NPOI For Excel 2003 Open Source http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html
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).
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
Source: Stackoverflow.com