COM - Excel - Cell content = floating point? Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
drawback
Posts: 34
Joined: 11 Aug 2016, 11:31

COM - Excel - Cell content = floating point?

09 May 2019, 16:12

Hi,

some demo code:

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.Workbooks.Open("R:\test.xlsx")

xl.Sheets("Test").Select
xlContent := xl.ActiveSheet.UsedRange.Value
Msgbox, % xlContent[13, 6]

xl.Workbooks.Close()
xl.Quit
xl =
xlContent will be an array of rows, columns that contain the values of each Excel cell

Let's say that the cell at 13, 6 contains the text "2000".
My problem is, that the array at that position will not contain "2000"
now, but "2000.000000"...

It doesn't matter if the Excel cell is formatted as standard or text.

What am I missing here?
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: COM - Excel - Cell content = floating point?

10 May 2019, 03:50

interesting

here
my 2 cent tip

Code: Select all

v := 2000.000000
MsgBox % ""
.  v "`n"
.  RegExReplace(v, "\.0+$")
Odlanir
Posts: 606
Joined: 20 Oct 2016, 08:20

Re: COM - Excel - Cell content = floating point?

10 May 2019, 04:00

Fpr a single cell this works:

Code: Select all

Msgbox, % xl.ActiveSheet.cells(13,6).text
EDIT:
or you can use Format

Code: Select all

Msgbox, % format("{:d}",xlContent[13,6])
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
drawback
Posts: 34
Joined: 11 Aug 2016, 11:31

Re: COM - Excel - Cell content = floating point?

10 May 2019, 04:14

Yeah, I was already replacing that value
by using

Code: Select all

v := RegExReplace(v, "\.0{6}$")
but I was just wondering why the array is filled with floating point values when the real value is just an integer
(so that I can avoid the regexreplace).

There must be a reason for this...
Albireo
Posts: 899
Joined: 16 Oct 2013, 13:53

Re: COM - Excel - Cell content = floating point?

10 May 2019, 04:36

or (SetFormat was easier to use - but it's not recommended for new scripts.)
Now is Format an alternative.

Code: Select all

v := 2000.000000
String := Format("{:1.0f}", v)
MsgBox %String%
; or
MsgBox, % Format("{:1.0f}", v)
(From jeeswg's characters tutorial)
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: COM - Excel - Cell content = floating point?

10 May 2019, 04:51

my friend
that is not the point here
the problem is how do you handle the return value of property Value properly (as you wanted)
awel20
Posts: 184
Joined: 19 Mar 2018, 14:09

Re: COM - Excel - Cell content = floating point?  Topic is solved

10 May 2019, 08:58

drawback wrote:
10 May 2019, 04:14
but I was just wondering why the array is filled with floating point values when the real value is just an integer
No, the "real" value is floating point. That's how excel stores it internally, so that's why it's the "Value". The integer is just how you have formatted the cell to display, but the underlying value is floating point.

As was already mentioned above, if you want the value formatted as it is displayed then you need to used .Text instead of .Value.

Looping over a large number of individual cells will be a lot slower than using a safeArray, but this will get the text.

Code: Select all

xl := ComObjActive("Excel.Application")
xlContent := xl.Range("A1:B4")
Msgbox, % xlContent.Cells(1, 2).Text
for Cell in xlContent
	MsgBox % Cell.Value "`n" Cell.Text
xl := xlContent := "" 
If speed is an issue I would suggest using the Format function as was already suggested.
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: COM - Excel - Cell content = floating point?

10 May 2019, 11:24

I've just checked the real value of numbers in Excel
I do not know

I have dismantled the xlsx into zip and find it
It looks like just 2000 not floating point style 2000.000000

so, the Evil lives not in the real value, but somewhere in the middle point
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: COM - Excel - Cell content = floating point?

10 May 2019, 11:34

@drawback
I can not confirm one of your comment
I have changed the cell's format into Text so I got 2000 not 2000.000000 from COM_Excel_of_AHK
This is written in SharedString file so it is NOT a floating point nor integers nor decimal numbers.
It is just a string nothing else
awel20
Posts: 184
Joined: 19 Mar 2018, 14:09

Re: COM - Excel - Cell content = floating point?

10 May 2019, 14:05

awel20 wrote: That's how excel stores it internally,
Klarion wrote:
10 May 2019, 11:24
I have dismantled the xlsx into zip and find it
I was referring to the Excel program itself - not to be confused with the files that it opens.

Edit: Is the type of a numeric value of a cell in Excel ALWAYS considered as DOUBLE?
Klarion
Posts: 176
Joined: 26 Mar 2019, 10:02

Re: COM - Excel - Cell content = floating point?

10 May 2019, 16:39

Code: Select all

a := [123.123456
, 123.123450
, 123.123400
, 123.123000
, 123.120000
, 123.100000 
, 123.000000
, 123000000]
For Each, x In a
	r .= RegExReplace(RegExReplace(x, "(\.\d*?)0+$", "$1"), "\.$") "`n"
MsgBox % r
drawback
Posts: 34
Joined: 11 Aug 2016, 11:31

Re: COM - Excel - Cell content = floating point?

15 May 2019, 03:53

Thanks guys (for the explanations as well)!

I need to iterate over 20.000 cells so speed is crucial...

In the end I've used a two step regex process where I first removed
all trailing 000000 chars and reformatted the remaining ones (1232.03, 322.42 etc.)
into 1232,03 & 322,42 (german divider for numbers). If a single "0" remained after
these steps, it was removed (I don't need any "0" values)...

Best regards,
drawback

Return to “Ask For Help”

Who is online

Users browsing this forum: lmstearn and 65 guests