ASP.NET How to set the proper Data Type of MS Excel cells with EPPlus A short guide explaining how to properly format the data type for numeric and date cells and/or columns within a MS Excel Worksheet (XLSX file) using 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:

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!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

0 Comments on “ASP.NET How to set the proper Data Type of MS Excel cells with EPPlus A short guide explaining how to properly format the data type for numeric and date cells and/or columns within a MS Excel Worksheet (XLSX file) using EPPlus

  1. Pingback: ASP.NET - Generate MS Excel files programmatically with EPPlus

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.