[NPOI] 解決Cell Formula 計算錯誤

用NPOI 時, 當存取的cell 為方程式時, 會有機會因為它將小數當做double 計算而出錯. 在 C# 中, double 與 decimal 最大分別是double 為浮位數 (Floating point), 即是它會用byte 進在儲存和運算. 而decimal 則是用十位數進行. 

在專案中, 因為要保存格式, 故用cell的dataformat 進行轉換, 若轉換不到才用回原本的CellNumericValue. 部份程式碼如下:

 // NPOI issue, it will calculate value in double format, so need to change in string format with cell data format and convert back to double.
                                                // Append below if any specif char. found.
                                                double castValue;
                                                bool castResult=  double.TryParse(cell.NumericCellValue.ToString(cell.CellStyle.GetDataFormatString())
                                                    .Replace(",", "")
                                                    .Replace("_", "")
                                                    .Replace("(", "")
                                                    .Replace(")", "")
                                                    .Replace("$", "")
                                                    , out castValue);
                                                if (castResult == false)
                                                    castValue = cell.NumericCellValue;
                                                dr[i] = castValue;

 

About C.H. Ling 262 Articles
a .net / Java developer from Hong Kong and currently located in United Kingdom. Thanks for Google because it solve many technical problems so I build this blog as return. Besides coding and trying advance technology, hiking and traveling is other favorite to me, so I will write down something what I see and what I feel during it. Happy reading!!!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


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