Import and Export Excel - What is the best library

Question

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 amp 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 lt - CSV conversion routine

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use Microsoft Jet OLEDB 4 0

User · Answer

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

User · Answer

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  amp  XLSX Based on own engine in pure net

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

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    Prepare work in spreadsheet file Save file  Import file  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    Prepare work in spreadsheet file Import file  using button inside Excel  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

User · Answer

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  amp  XLSX Based on own engine in pure net

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Prepare work in spreadsheet file Save file  Import file  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    Prepare work in spreadsheet file Import file  using button inside Excel  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The following site demonstrates how to export a DataTable  DataSet or List lt   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

User · Answer

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

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

The following site demonstrates how to export a DataTable  DataSet or List lt   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

User · Answer

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 · Answer

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

User · Answer

You can use Microsoft Jet OLEDB 4 0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[c#] Import and Export Excel - What is the best library?

Examples related to c#

Examples related to asp.net

Examples related to excel

Examples related to export-to-excel

Examples related to import-from-excel