如何从Excel单元格获取全部值，而不是显示的(四舍五入)值? [英] How to get the full value from an excel cell, not the displayed (rounded) value?
问题描述
我在从工作表的单元格中检索确切值时遇到问题.如果我打开文件，则该单元格具有一个十进制数字，仅显示四个十进制小数，但是如果我单击特定的单元格，则该值将有所不同，具有六个十进制小数.我知道这是应用于单元格的设置，以便仅显示4个小数.
现在，我正在尝试使用ClosedXML.Excel(而不是Microsoft.Office.Interop.Excel)在C#中检索单元格的数据，但是我唯一能获得的是4位小数点的值，而不是"一个，这是一个很大的问题，因为稍后我会进行计算，由于缺少2个小数位，我之间存在很大的差异.
我尝试使用inputSheet.Worksheet.Cell(row,col).GetDouble()或Convert.ToDouble(inputSheet.Worksheet.Cell(row, col).Value)，甚至引用"RichText"属性或... ToString("0.000000")，但是无论我使用什么，我都只能检索4位小数，而不是全部6位小数.
我看了一下库的源代码，这是Value getter的作用:
get
{
string str = this.FormulaA1;
if (!XLHelper.IsNullOrWhiteSpace(str))
{
string str2;
string sName;
if (str[0] == '{')
{
str = str.Substring(1, str.Length - 2);
}
if (str.Contains<char>('!'))
{
sName = str.Substring(0, str.IndexOf('!'));
if (sName[0] == '\'')
{
sName = sName.Substring(1, sName.Length - 2);
}
str2 = str.Substring(str.IndexOf('!') + 1);
}
else
{
sName = this.Worksheet.Name;
str2 = str;
}
if (this._worksheet.Workbook.WorksheetsInternal.Any<XLWorksheet>(w => (string.Compare(w.Name, sName, true) == 0)) && XLHelper.IsValidA1Address(str2))
{
return this._worksheet.Workbook.Worksheet(sName).Cell(str2).Value;
}
object obj2 = this.Worksheet.Evaluate(str);
IEnumerable enumerable = obj2 as IEnumerable;
if ((enumerable != null) && !(obj2 is string))
{
using (IEnumerator enumerator = enumerable.GetEnumerator())
{
while (enumerator.MoveNext())
{
return enumerator.Current;
}
}
}
return obj2;
}
string s = this.HasRichText ? this._richText.ToString() : this._cellValue;
if (this._dataType == XLCellValues.Boolean)
{
return (s != "0");
}
if (this._dataType == XLCellValues.DateTime)
{
return DateTime.FromOADate(double.Parse(s));
}
if (this._dataType == XLCellValues.Number)
{
return double.Parse(s);
}
if (this._dataType == XLCellValues.TimeSpan)
{
return TimeSpan.Parse(s);
}
return s;
}
我注意到，当它到达string s = this.HasRichText ? this._richText.ToString() : this._cellValue;时，如果您从未调用过cell.RichText(因此也没有用调试器查看它)，您将获得正确的值，因为this.HasRichText为false，因为该类仍未分配正确的价值.
当它(this.HasRichText)是true时，得到this._richText.ToString()，即格式化的数字.
因此，如果在访问RichText之前访问Value属性，则应该获得正确的值，无论如何，您都可以使用反射来获取_cellValue，然后将其转换为double，如下所示:
var theRealDoubleValue = Convert.ToDouble(yourCell.GetType().GetField("_cellValue", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(yourCell));
I'm having an issue retrieving the exact value from an cell from a worksheet. If I open the file the cell has a decimal number which it's shown only with 4 decimals, but if I click on the particular cell, the value is different, having 6 decimals. I know it's a setting applied to the cell in order to show only 4 decimals.
Now I'm trying to retrieve the cell's data in C#, using ClosedXML.Excel, not Microsoft.Office.Interop.Excel, but the only thing I'm able to get is the 4 decimals value, not the "whole" one, which is a great issue as I have calculation later on and I have big differences due to the missing 2 decimals.
I have tried using inputSheet.Worksheet.Cell(row,col).GetDouble(), or Convert.ToDouble(inputSheet.Worksheet.Cell(row, col).Value), or even to refer to the "RichText" property, or ...ToString("0.000000"), but no matter what I use, I can only retrieve the 4 decimal value instead of the full 6 decimals one.
I got a look at the source code of the library, here is what the Value getter does:
get
{
string str = this.FormulaA1;
if (!XLHelper.IsNullOrWhiteSpace(str))
{
string str2;
string sName;
if (str[0] == '{')
{
str = str.Substring(1, str.Length - 2);
}
if (str.Contains<char>('!'))
{
sName = str.Substring(0, str.IndexOf('!'));
if (sName[0] == '\'')
{
sName = sName.Substring(1, sName.Length - 2);
}
str2 = str.Substring(str.IndexOf('!') + 1);
}
else
{
sName = this.Worksheet.Name;
str2 = str;
}
if (this._worksheet.Workbook.WorksheetsInternal.Any<XLWorksheet>(w => (string.Compare(w.Name, sName, true) == 0)) && XLHelper.IsValidA1Address(str2))
{
return this._worksheet.Workbook.Worksheet(sName).Cell(str2).Value;
}
object obj2 = this.Worksheet.Evaluate(str);
IEnumerable enumerable = obj2 as IEnumerable;
if ((enumerable != null) && !(obj2 is string))
{
using (IEnumerator enumerator = enumerable.GetEnumerator())
{
while (enumerator.MoveNext())
{
return enumerator.Current;
}
}
}
return obj2;
}
string s = this.HasRichText ? this._richText.ToString() : this._cellValue;
if (this._dataType == XLCellValues.Boolean)
{
return (s != "0");
}
if (this._dataType == XLCellValues.DateTime)
{
return DateTime.FromOADate(double.Parse(s));
}
if (this._dataType == XLCellValues.Number)
{
return double.Parse(s);
}
if (this._dataType == XLCellValues.TimeSpan)
{
return TimeSpan.Parse(s);
}
return s;
}
I noticed, that when it reach string s = this.HasRichText ? this._richText.ToString() : this._cellValue;, if you have never called cell.RichText (so neither viewing it with the debugger) you get the correct value, because this.HasRichText is false because the class has still not assigned the correct value to it.
When it(this.HasRichText) is true, you get this._richText.ToString(), that is the formatted number.
So, if you access the Value property before accessing RichText you should get the correct value, anyway you can get _cellValue using reflection, then convert it to double like this:
var theRealDoubleValue = Convert.ToDouble(yourCell.GetType().GetField("_cellValue", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(yourCell));
这篇关于如何从Excel单元格获取全部值，而不是显示的(四舍五入)值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
