Read Excel column data

[HarishChand]

QM : How I can read excel column data one bye one and set new data to another crosponding column?

I have
A B C D
------------------------------------
1 xyz 21 OK
2 abc 00 ?
3 23 2 ?
4 lmn 2
5 yz w

READ
xyz and set "OK " in (1,c)
abc and set "NO" in (2,c)
;;;;
;;;

;;;

How I can do this?

[Gintaras]

Macro Macro1431

Code:

Copy      Help

act "Microsoft Excel"
spe 10
key CH ;;Ctrl+Home to select A:1
str s s2
rep 5
,;get cell A
,s.getsel
,s.trim
,out s
,
,;set cell C
,key RR ;;Rigt Right
,s2="OK"
,s2.setsel
,
,;next row
,key HD ;;Home Down

or

Macro Macro1438

Code:

Copy      Help

;/exe 1
ExcelSheet es.Init
str s s2
int i nRows
nRows=es.NumRows
for i 1 nRows+1
,;get cell A
,es.GetCell(s 1 i)
,out s
,
,;set cell C
,s2="NO"
,es.SetCell(s2 3 i)

[HarishChand]

Thanks,

How I can check Excel Column cell data is string or numberic

I required only numeric data

Example
suppose
str data;
,es.GetCell(data 1 i)
'****** I required If Data is numeric value then process next step otherwise continue

I know other language
if IsNumeric(Data)
{
out "Numeric"
}
else
{
out "Character"
}

CELL VALUE IS
1234
sdsd
123
sd23

JHJHS
131

I required only :: 1234 123 131 for next step

[Gintaras]

Function IsNumeric

Code:

Copy      Help

;/
function! $s

;Returns 1 if s begins with a number. Returns 0 if not.
;Before the number can be spaces and - or +.

;EXAMPLE
;str s="10"
;if IsNumeric(s)
,;out "Numeric"
;else
,;out "Text"


val s 2 _i
ret _i!0

[HarishChand]

Yes Its working...
If we have 12xyz . Its start to digit but Its a string. Its not required.
If cell data is only number, then I can process.. Otherwise skip
100w means skip;
1 process this
Thanks, I also trying to solve this problem..

[Gintaras]

Function IsNumeric

Code:

Copy      Help

;/
function! $s [flags] ;;flags: 1 full

;Returns 1 if s begins with a number. Returns 0 if not.
;Before the number can be spaces and - or +.
;The number can be decimal (like 567), hexadecimal (like 0x567) or double (like 1.45 or 5E8).

;s - string.
;flags:
;;;1 - return 0 if the string begins with a number but contains text after it.

;EXAMPLE
;str s="10"
;if IsNumeric(s)
,;out "Numeric"
;else
,;out "Text"


val s 2 _i
if(!_i) ret
if(flags&1) if(s[_i]) ret
ret 1

[Gintaras]

With Excel better use this function.
It interprets numbers more like Excel than QM. Supports thousand separators.

Function IsNumeric2

Code:

Copy      Help

;/
function! $s

;Returns 1 if s is a number. Returns 0 if not. Returns 0 if s is a number followed by text.
;The function interprets numbers more like Excel or Visual Basic than QM.
;Before the number can be spaces and - or +. After can be spaces.
;The number can be decimal (like 567), VB hexadecimal (like &H567) or double (like 1.45 or 5E8).
;The number can contain thousand separators (like 1,000,000).
;Decimal point and thousand separator character depends on user's locale (like in Excel).

;EXAMPLE
;str s="10"
;if IsNumeric2(s)
,;out "Numeric"
;else
,;out "Text"


double k
ret !VarR8FromStr(@s LOCALE_USER_DEFAULT 0 &k)

[HarishChand]

Thank you very much....