Site icon Ryadel

ASP.NET How to set the proper Data Type of MS Excel cells with EPPlus

ASP.NET - Generate MS Excel files programmatically with EPPlus

If you've stumbled upon this post you most likely already know EPPlus, one of the best open-source libraries to programmatically read, create and edit MS Excel files in XLS and XLSX format: a great alternative to the Microsoft.Office.Interop.Excel official package, at least for web applications. We already talked about it in a couple posts on this blog, explaining how to programmatically create and/or edit a MS Excel XLSX file and how to apply style and format rules to it.

In this post we'll try to address a few issues experienced by some EPPlus users when trying to format the cell and/or column values using format-specific data types such as Numeric, Date, DateTime, Currency and so on. Here are some of these issues that can be found on StackOverflow:

Since most of the solutions and workarounds mentioned in these threads are not effective for all scenarios, we tried to give our two cents with the following lines of code. For the sake of simplicity, we used the source code used here as a boilerplate, changing only the main foreach statement in the following way:

ws is the EPPlus worksheet object, dt is the DataTable with the rows we're iterating, and r is the current row: for additional info, see the full code here.

Despite being a rather general purpose approach, these if-then-else conditional rules fixed all our issues with data types: all the cells will have their "proper" format: such method also supports NULL values in each cell.

Conclusion

That's it, at least for now. The code itself is quite self-explanatory, all you need to do if you want to use and/or change it to suit your needs is read the comments and act accordingly. Have a good time with ASP.NET and EPPlus!

 

Exit mobile version