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:
- https://stackoverflow.com/questions/24933947/excel-date-column-returning-int-using-epplus
- https://stackoverflow.com/questions/31537981/using-epplus-how-can-i-generate-a-spreadsheet-where-numbers-are-numbers-not-text
- https://stackoverflow.com/questions/40209636/epplus-number-format
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
// [...] // [...] previous part of source code // [...] foreach (DataColumn dc in dt.Columns) { col++; // Cast each datarow value to its given data type, // so that each XLSX cell value will have the corresponding format. // IMPORTANT: DateTime values will need additional work // since we would want to have it properly formatted // and also properly support possible NULL values in the column if (dc.DataType == typeof(DateTime)) { if (!r.IsNull(dc)) { ws.SetValue(row, col, (DateTime)r[dc]); // Change the following line if you need a different DateTime format var dtFormat = "dd/MM/yyyy"; ws.Cells[row, col].Style.Numberformat.Format = dtFormat; } else ws.SetValue(row, col, null); } else if (dc.DataType == typeof(int)) ws.SetValue(row, col, !r.IsNull(dc) ? (int)r[dc] : (int?)null); else if (dc.DataType == typeof(decimal)) ws.SetValue(row, col, !r.IsNull(dc) ? (decimal)r[dc] : (decimal?)null); else if (dc.DataType == typeof(double)) ws.SetValue(row, col, !r.IsNull(dc) ? (double)r[dc] : (double?)null); else if (dc.DataType == typeof(float)) ws.SetValue(row, col, !r.IsNull(dc) ? (float)r[dc] : (float?)null); else if (dc.DataType == typeof(string)) ws.SetValue(row, col, !r.IsNull(dc) ? (string)r[dc] : null); // global fallback for unhandled data types else ws.SetValue(row, col, r[dc].ToString()); } // [...] // [...] next part of source code // [...] |
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!
