To store tables in memory, is convenient to use two-dimensional array.
This function gets data from Excel window.
Function ExcelSheetToArray
;/
function ARRAY(str)&a
;Stores Excel selected cells into two-dimensional array.
;EXAMPLE
;ARRAY(str) a
;ExcelSheetToArray a
;int r c
;for r 0 a.len
,;out "-----Row %i-----" r+1
,;for c 0 a.len(1)
,,;out a[c r]
typelib Excel {00020813-0000-0000-C000-000000000046} 1.2
#opt dispatch 1 ;;call functions through IDispatch::Invoke (may not work otherwise)
Excel.Application xlApp._getactive; err act; act; xlApp._getactive
Excel.Range r=xlApp.Selection
int i j nr(r.Rows.Count) nc(r.Columns.Count)
a.create(nc nr)
for i 0 nr
,for j 0 nc
,,a[j i]=r.Item(i+1 j+1)
This function gets data from csv file.
Function CsvFileToArray
;/
function# $file_ ARRAY(str)&a
;Parses csv file and creates array of two dimensions.
;First dimension - column, second - row.
;Returns number of columns. Number of rows is a.len.
;A csv file is text file that contains table where
;rows are stored in separate lines, and columns are
;separated by semicolons. Supported by Excel.
;This function does not support semicolons and quotes
;within cells.
;EXAMPLE
;ARRAY(str) a
;int nc=CsvFileToArray("$desktop$\id.csv" a)
;int i j
;for i 0 a.len
,;out "-----Row %i-----" i+1
,;for j 0 nc
,,;out a[j i]
str s ss.getfile(file_) sss
int i j k li nc(1) nl(numlines(ss))
if(!nl) a.redim; ret
foreach s ss
,if(!a.len)
,,;find number of columns
,,for(j 0 9999999) j=findc(s ';' j); if(j>=0) nc+1; else break
,,a.create(nc nl)
,
,j=0; k=0
,for(i 0 nc)
,,j=findc(s ';' k)
,,if(j!k) a[i li].get(s k j-k)
,,if(j<0) break
,,k=j+1
,
,li+1
ret nc
err+ end _error
This example gets selection in Excel (must be selected 4 columns and any number of rows), for each row populates 4 search fields in Google advanced search page, clicks Google Search button, and waits 5 seconds.
act " Internet Explorer"
ARRAY(str) a
ExcelSheetToArray a
int r c
for r 0 a.len
,out "-----Row %i-----" r+1
,MSHTML.IHTMLElement el=htm("INPUT" "as_q" "" " Internet Explorer" 0 0 0x221 5)
,el.innerText=a[0 r]
,el=htm("INPUT" "as_epq" "" " Internet Explorer" 0 3 0x221)
,el.innerText=a[1 r]
,el=htm("INPUT" "as_oq" "" " Internet Explorer" 0 4 0x221)
,el.innerText=a[2 r]
,el=htm("INPUT" "as_eq" "" " Internet Explorer" 0 5 0x221)
,el.innerText=a[3 r]
,el=htm("INPUT" "Google Search" "" " Internet Explorer" 0 2 0x421)
,el.click
,5
,web "Back"
,
