define column width in xl() "open" depending on values (reals & strings)?

[Franz Gerbig]

Hi!

How may I define the column width for each column depending on the maximum length of the characters written to its cells in an xl() still open?

For data like

input var1 var2 var3
1 2 "sth"
3 4 "sth else"
1 2 "guess what"
3 5 "sth"

lab var var1 "varlabel1"
lab var var2 "varlabel2"
lab var var3 "varlabel3"

I want to do something like the following (note: I don't know, how many rows there will be, and want the code be flexible to use for other datasets with a different number of variables):

mata: mata clear
mata
b= xl()
b.create_book("filename")
b.set_mode("open")
colnames = ("varname", "varlabel", "values", "vartype")
b.put_string(1,1, colnames)
end

local row = 2 //enter varname, varlabel, distinct values, and vartype starting in row 2

qui: desc *
mata: rows = (2,  r(k)')
mata: b.set_number(rows, 3, "number")

foreach v of var * {
mata: b.put_string(`row', 1, "`v'")
mata: b.put_string(`row', 2, "`: var lab `v''")
if !regexm("`: type `v''", "str") {
qui: distinct `v' if `v' >= 0
mata: b.put_number(`row', 3, `r(ndistinct)')
}

mata: b.put_string(`row', 4, "`vartype'")
local ++row
}

foreach col in 1 2 3 4 {
forval r = 1/`row' {
local width = max(0, length(`r'))
mata: b.set_column_width(1, `col', `width')
}
}

Help appreciated!

[Niels Henrik Bruun]

Dear Franz

Take a look at the following code.
A classical mistake in Stata/Mata is to mix the two.
You have for-loops in Mata. It is often better to split into Mata blocks which are called from Stata.

Also take a look into "help m4_stata".

Enjoy your coding.

cls
clear
input var1 var2 strL var3
1 2 "sth"
3 4 "sth else"
1 2 "guess what"
3 5 "sth"
end
lab var var1 "varlabel1"
lab var var2 "varlabel2"
lab var var3 "varlabel3"

capture rm "fn.xlsx"
mata: mata clear
mata
    b= xl()
    b.create_book("fn.xlsx", "mysheet")
    b.put_string(1,1, ("varname", "varlabel", "values", "vartype"))
    b.set_column_width(1,1,30)
    for(r=1;r<=st_nvar();r++) {
        b.put_string(r+1, 1, ((vn=st_varname(r)), st_varlabel(vn), st_varvaluelabel(vn), st_vartype(vn)))
        b.set_column_width(r+1,r+1,20)
    }
    b.close_book()
end
shell "fn.xlsx"