Get Excel File Range using xl() Class

[Luis Pecht]

Hi,

Is there any "efficient" way to get the # of (rows,cols) of an excel file, besides looping thru it looking for a null, using xl( ) - Excel file I/O class ?

mata
b=xl()
b.load_book("test.xlsx")
b.set_sheet("sheet1")
b.get_string((1,1),(rows,cols))
...

thks

[Andrew Musau]

You could use import excel with the -describe- option and pick this from r().

qui import excel using test.xlsx, describe
return list

[Luis Pecht]

Thanks Andrew, see below your suggestion applied:

. qui import excel using "test.xlsx", describe
. return list

scalars:
        r(N_worksheet) =  2

macros:
        r(worksheet_1) : "Sheet1"
            r(range_1) : "A1:Z65000"
        r(worksheet_2) : "Sheet2"
            r(range_2) : "A1:Z2647"

. mata

: s=st_numscalar("r(N_worksheet)")

: m=J(s,2,"")

: for (i=1; i<=s; i++){
> m[i,1]=st_global("r(worksheet_"+strofreal(i)+")")
> m[i,2]=st_global("r(range_"+strofreal(i)+")")
> }

: m
               1           2
    +-------------------------+
  1 |     Sheet1   A1:Z65000  |
  2 |     Sheet2    A1:Z2647  |
    +-------------------------+