This project is read-only.

How to set xlsx cell format to Short Date, i.e. standard Excel date format?

Feb 16, 2016 at 4:39 PM
In excel > Format Cells box > Date category > there are two options at the start of the list that are prefixed with '*' to note they change based on regional settings. How can I use NPOI to format a cell with one of these styles?

Specifically I want to format a cell as a date and ensure if it's opened on a machine with UK regional settings it'll show like '31/01/2016' but if it's on a machine with USA regional settings it'll show like '01/31/2016'.

I don't see anything obvious in NPOI.SS.UserModel.DataFormatter to help with this, but probably I'm missing something simple.

Currently I'm using something like this, but it doesn't change based on regional settings:
var format = workbook.CreateDataFormat();
var style = workbook.CreateCellStyle();
style.DataFormat = format.GetFormat("dd/mm/yyyy");
cell.CellStyle = style;
thanks,

Rory
Feb 17, 2016 at 12:26 AM
After a few attempts it seems if I use the format string "m/d/yy" that magically translates to the Short Date format. Opening a workbook on a machine with UK regional settings displays like '31/01/2016' and on opening the Format Cells dialog the first format in the Date category list is selected.

Is this documented somewhere? Might be handy to put in the docs on the built-in formats, if indeed I'm right.

Rory