# Numeric values â€‹â€‹in a cell

Hello!

When I reading large numbers of cells I` m not getting the correct valuesâ€‹â€‹. For example, if I read the number of cell A1, I get the value 81033172103352, that is true, but if I read the value in cell A2, then get value 8,10861515062236 E15 instead of 81033172103352. How to act in this situation?

Read from the cells as follows:

f := xlBook.GetCellVisibleFormatDef(ARow, ACol);
if f.Format <> '' then
s := xlBook.GetStringFromCell(ARow, ACol)
else
s := (xlBook.GetCellValue(ARow, ACol)).ToString;

File "File.xlsx" sent to you via email with subject "Numeric values â€‹â€‹in a cell".

Hi,

I got the email, but if I open the file in Excel I see the same as I see in Excel (A2 has 8,10862E15)
This is an screenshot of how I see it in Excel and FlexCel (custom preview demo)

You're absolutely right, but if you look in the formula bar, then we will see the correct values â€‹â€‹for A1 and A2.

File "Screen.jpg" sent to you via email with subject "Numeric values â€‹â€‹in a cell".

I still don't see it. You spoke about different numbers:

But I see both Excel and FlexCel have 8108615150622360 in either your screenshot or mine.

For the rest, 8,10861515062236 E15 is the same as 8108615150622360, those are 2 representations for the same value,  so I am not sure on what the error is?

GetStringFromCell in FlexCel will return a string that shows the number as Excel would show it. In this case, it returns:
8,10862E+15

And this is also what Excel shows in the cell (see the first screenshot), so GetStringFromCell is working fine.

GetCellValue will return a floating point number, which is 8108615150622360

Now, this is floating point number, it isn't a string, and you can represent it as 8.1E15 or 8...<15 0s>
You can represent it in a string in many ways. By default, Delphi "StringToFloat" will convert very big numbers to exponential notation, and smaller numbers to normal notation.

But you can change how the string is converted. For example:
showmessage(FloatToStr(xls.GetCellValue(2, 1).AsNumber));
will be converted to
8,10861515062236E15

since this is the default Delphi representation for big numbers. On the other hand, if you explicitly say you want a "fixed" notation:
showmessage(FloatToStrF(xls.GetCellValue(2, 1).AsNumber, ffFixed, 18, 0));

This will show:
8108615150622360

Both numbers are the same, just shown in different representations.

Can it be that you are confusing by the way Delphi converts numbers by default? I mean, if you write (no FlexCel at all):
showmessage(FloatToStr(81033172103352));
showmessage(FloatToStr(8108615150622360));

You'll see the results are:
81033172103352
8,10861515062236E15

And this is ok, since when you use a "general" formatting, FloatToString uses ffGeneral, which will use the format it believes is best for the number.

The "ToString()" in "GetCellValue(...).ToString() just uses FloatToString under the hood, so it behaves the same way.

Just in case, this are the different possibilities (from delphi help):
 ffGeneral General number format. The value is converted to the shortest possible decimal string using scientific format. Trailing zeros are removed from the resulting string. ffExponent Scientific format. The value is converted to a string of the form "-d.ddd...E+dddd". The resulting string starts with a minus sign if the number is negative, and one digit always precedes the decimal point. The total number of digits before the exponent in the resulting string (including the one before the decimal point) is given by the Precision parameter. The "E" exponent character in the resulting string is always followed by a plus or minus sign and up to four digits. The Digits parameter specifies the minimum number of digits in the exponent (between 0 and 4). ffFixed Fixed point format. The value is converted to a string of the form "-ddd.ddd...". The resulting string starts with a minus sign if the number is negative, and at least one digit always precedes the decimal point. The number of digits after the decimal point is given by the Digits parameter--it must be between 0 and 18. If the number of digits to the left of the decimal point is greater than the specified precision, the resulting value will use scientific format. The resulting values is padded with zeros when the Digits parameter is higher than the number of digits dictated by precision. For example, given ffFixed with Precision set to 5 and digits set to 3, fomatting 345.6789 gives the string '345.680', padding with zeros after the precision is met. ffNumber Number format. The value is converted to a string of the form "-d,ddd,ddd.ddd...". The ffNumber format corresponds to the ffFixed format, except that the resulting string contains thousandth separators. The resulting value is padded with zeros when the Digits parameter is higher than the number of digits dictated by precision. For example, given ffNumber with Precision set to 5 and digits set to 3, fomatting 345.6789 gives the string '345.680', padding with zeros after the precision is met. ffCurrency Currency format. The value is converted to a string that represents a currency amount. The conversion is controlled by the CurrencyString, CurrencyFormat, NegCurrFormat, ThousandSeparator, and DecimalSeparator global variables, all of which are initialized from Currency Format in the International section of the Windows Control Panel. The number of digits after the decimal point is given by the Digits parameter--it must be between 0 and 18. The resulting value is padded with zeros when the Digits parameter is higher than the number of digits dictated by precision. For example, given ffCurrency with Precision set to 5 and digits set to 3, fomatting 345.6789 gives the string '345.680', padding with zeros after the precision is met.

Oh :-)

All earned!!!!!

Thanks a lot!