In all versions of NPOI including 1.2.4 alpha, someone may find that there is a weird issue which is different from the default behavior of Excel 2003/2007. If the xls is created by NPOI, no matter what you inputed, for example, 1-1, the cell will be converted to a formula cell. However, when you create a empty xls using Office 2003/Office 2007, this issue never happen.
After some research, I found that the issue is caused by two property in WSBoolRecord. They are AlternativeExpression and AlternativeFormula. To find the corresponding option in Office 2007, click Office icon – > Excel Options –> Advanced –> Lotus Compatibility settings:
Transition Formula Entry and Transition Formula Expression.
By default, both should not be checked. However, if the file is created by NPOI, both are checked. That’s why 1-1 is recognized as =1-1 instead of Jan 1.
We will fix this issue in NPOI 1.2.4 final release. However, to avoid this issue in previous releases, you can set AlternativeExpression and AlternativeFormula to false for each sheet. Here is the code example to modify sheet 0
((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false; ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
