英文:
empty cells are showing .Text as "NaN" using NPPlus in C#
问题
Our client provides Excel files as input, and we extract the text for further processing. However, some cells have the .Text property set to NaN, yet they appear blank in Excel itself.
My helper class performs a simple loop through the populated cells (loop omitted from the sample code):
var package = new ExcelPackage(stream);
var workSheet = package.Workbook.Worksheets[parameters.workSheetNumber];
var cell = workSheet.Cells[row, column];
value = cell.Text;
I initially thought that .Text would display the text representation seen in Excel, but in this case, it does not. Is there a way to obtain the actual text representation? Alternatively, if not, is there a way to determine whether NaN is present because it's not a number or because the cell contains the literal text "NaN"?
英文:
Our client gives Excel files as input and we rip the text out to do stuff with. Whatever they've done is causing some cells to have the .Text property of NaN, but show as blank when viewed through Excel itself.
My helper class does a very simple loop through the populated cells (loop omitted from sample code)
var package = new ExcelPackage(stream)
var workSheet = package.Workbook.Worksheets[parameters.workSheetNumber];
var cell = workSheet.Cells[row, column];
value = cell.Text;
Here I was thinking .Text meant it would show the text representation shown by Excel when viewed through the app, but for this case it does not. Is there a way to get the actual text representation? Or, if not, is there a way to confirm if NaN is there because it's not a number, or there because the cell has the literal text value of NaN?
答案1
得分: 1
以下是您要翻译的内容：
问题最终在于区分文本值为 "NaN" 和表示为 NaN 的数值对象值。前者表明单元格的文本值为 NaN，而后者表明单元格认为它既是数字又不是数字，并且出现了问题。
在调试模式下运行我的输入文件时，单元格的 .Text 属性在每个实例中都显示为 "NaN"，但 .Value 属性会发生变化。有问题的单元格都将值类型设置为 double。
保守的解决方案是检查 NaN 的 double 值：
if (cell.Text == "NaN" && cell.Value.GetType() == typeof(double))
value = "";
else
value = cell.Text;
上述代码可能由于其他数值类型的 NaN 而失败：
if (cell.Text == "NaN" && cell.Value.GetType() != typeof(string))
value = "";
else
value = cell.Text;
上述代码将修复任何情况下的 NaN，只要 NaN 不是作为单元格中的文本字符串提供。至少就原始问题而言，这种解决方案的缺点是可能存在单元格值类型，其中 NaN 会在 Excel 显示中显示，导致我们改变值的另一个方向。
我已经对来自许多不同客户的数据运行了我的代码，没有遇到这个问题，所以虽然可能存在两种解决方案的假设问题，但这个客户所做的事情足够罕见，以至于这两种解决方案都应该足够。
英文:
The problem, ultimately, is differentiating between a text value of "NaN" and a numerical object value that's represented as NaN. The prior would indicate the cells text value is NaN, the latter would indicate the cell thinks that it both is and isn't a number and is having a fit.
Running my input file through the code in debug mode, the cell's .Text property showed "NaN" in every instance, but the .Value property did change. The faulty cells all had the value type as double.
The conservative solution is to check for NaN doubles
if (cell.Text == "NaN" && cell.Value.GetType() == typeof(double))
value = "";
else
value = cell.Text;
The above might fail from NaN due to other numeric types
if (cell.Text == "NaN" && cell.Value.GetType() != typeof(string))
value = "";
else
value = cell.Text;
The above would fix NaN for any case where NaN wasn't provided as a literal string in the cell. The downside to this solution, at least with regards to the original problem, is that there might possibly be cell value types where NaN would display in the Excel Display causing us to alter the value in the other direction.
I've run my code against data from many different clients without encountering this issue though, so while there might be hypothetical problems with both solutions, whatever this client's doing is rare enough that both solutions should be sufficient.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论