In among our ASP.NET applications in C#, we take a particular data collection (SubSonic collection) and export it to Excel. We likewise wish to import Excel files in a particular format. I’m trying to find a library I can use for this purpose.
Excel 2007 files (Does Excel 2003 assistance over 64k rows? I need more than that.).
Does not need Excel on the server.
Takes a typed collection and, if it can, attempts to put numeric fields as numerical in Excel.
Works well with big files (100k to 10M) – fast enough.
When exporting GUIDs, does not crash!
Does not cost a crapload of cash . Free is always excellent, however can be a commercial library.
What library do you suggest? Have you utilized it for big quantities of information? Are there other services?
Now, I am utilizing a simple tool that generates HTML that is filled by Excel later on, but I am losing some capabilities, plus Excel complains when we load it. I do not have to generate charts or anything like that, just export raw information.
I am thinking about flat CSV files, however Excel is a customer requirement. I can deal with CSV straight, if I had a tool to transform to and from Excel. Provided Excel 2007 is an xml-based (and zipped) file format, I am guessing this kind of library should be easy to discover. However, what matters most to me are your comments and viewpoints.
Ironically, in my opinion and following the response with the most votes, the finest Excel import & export library is no export at all. This is not the case for all scenarios, however it is for mine. XLS submits support just 64k rows. XLSX supports up to 1M. When you have 200k rows), the complimentary libraries that I’ve tried feature bad performance (one second to fill one row. I have not attempted the paid ones, as I feel they are overpriced for the value they provide when all you require is a fast XLSX<->CSV conversion regimen.
I’m going to throw my hand in for flat csv files, if just since you’ve got the best control over the code. Simply make certain that you check out in the rows and process them one at a time (checking out the file to the end and splitting will eat up all of your memory – exact same with writing, stream it out).
Yes, the user will have to save-as CSV in stand out before you can process it, but possibly this restriction can be conquered by training and offering clear instructions on the page?
When you export to the client, 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’.
I found the Open XML SDK given that my original answer. It offers strongly typed classes for spreadsheet objects, to name a few things, and appears to be relatively simple to deal with. I am going to use it for reports in one of my tasks. Unfortunately, variation 2.0 is not expected to obtain released up until late 2009 or 2010.
CSV export is basic, simple to execute, and fast. There is one potential issue worth noting. Excel (as much as 2007) does not maintain leading absolutely nos in CSV files. This will garble ZIP codes, item ids, and other textual information containing numeric values. There is one trick that will make Excel import the worths properly (using delimiters and prefix worths with the = indication). If you have users who will do post-processing jobs with the CSV, they require to be mindful that they require to change the format to XLS and not save the file back to CSV. If they do, leading nos will be lost for excellent.
For many years, I have utilized JExcel for this, an exceptional open-source Java job. It was also.NET-able by utilizing J # to assemble it, and I have actually likewise had great success with it in this incarnation. Recently I required to move the code to native.NET to support a 64-bit IIS application in which I develop Excel output. The 32-bit J # variation would not fill.
The code for CSharpJExcel is LGPL and is available currently at this page, while we prepare to deploy it on the JExcel SourceForge website. It will put together with VS2005 or VS2008. The examples in the original JExcel paperwork will pretty well move over intact to the.NET version.
I know this is rather late, but I feel compelled to respond to xPorter (writing) and xlReader (reading) from xPortTools.Net. We tested many libraries and absolutely nothing came close in the method of performance (I’m speaking about writing countless rows in seconds here). Cannot say enough good ideas about these products!