ExcelPackage is a free .NET library that can be found here .
While checking for ways to create and read excel files for an ASP .NET application, I came up with several options on the web:
- Microsoft.Office.Interop.Excel
- Generating an excel file from a DataTable
- Excel Package / Excel Package Plus
After further checking this options, there were several cons:
Microsoft.Office.Interop.Excel in ASP .NET requires the server to have Microsoft Office installed, may throw unexplained exceptions and negatively impact performance. I have not yet used this library, however, for what I've read, it is only recommended for Windows Form Apps.
I did test generating an excel file from a DataTable, however, the file generated is not a 'true' excel file, which both complicates the file managing afterwards if reading is required and shows an "are you sure you want to open this file" every time the user opens the file with Excel (really annoying actually).
Most people recommend using either Excel Package or Excel Package Plus (which I've not yet tested due to time constraints). Excel Package Plus is actually an extension of Excel Package and is advertised to allow for easier excel data manipulation for graphs generation and content handling, however at this time, I did not require any of this stuff.
So in order to use Excel Package, just proceed to download the binary package and reference it in your project. The "Where to Start Guide" provided by the site is really well explained, and the library is really simple to use.
This is all perfect, unless like me, you come across the need of generating an excel file in which the apostrophe (') character is pretty much a standard. If this happens, the current library will throw an invalid character exception every time you load a cell which contains any value with the character.
You can avoid this replacing the character with some other character, however this doesn't sound much like a solution. I tried escaping the character with some string handling, but this required previous knowledge of which cells would require this "special handling" and to be honest, both messed up the code and didn't really work.
Luckily, the solution was already documented in the excelpackage.codeplex discussion forums.
But in order to save some of your time, the solution is as follows (provided in the forum by user b_o_o_n)
Download and build the source code (as a class library) with the below change. If your project can't find System.IO.Packaging add a reference to WindowsBase.
If the project wont load from the .sln or .csproj make a new project and copy in the .cs files.
In ExcelCell.cs find the string
* XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value), _xlWorksheet.NameSpaceManager);
* replace with
* XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", System.Security.SecurityElement.Escape(Value)), _xlWorksheet.NameSpaceManager);
Reference the built .dll in your project.
Although this appears in their discussion panel, it is weird that no official solution to this issue has been addressed or implemented by the team in any of their releases. Maybe this was fixed in the Plus release, but at the time of this post, I've not verified that yet.
Good thing is this solution has worked fine for me... at least for now (will keep you posted if not), hope this is somewhat useful. There are several more options to handling Excel files, if you have tried any other solutions, please do share :)
With nothing more to say for now, happy coding!
Entry dismissed.
