putexcel -- loop through colunms

[Laurence Lester]

Dear All
Looping through rows in putexcel is straightforward (local counter which is increased with, eg, ++localName). I wonder if anyone has an idea how to do the same thing with the column -- loop to C & D, etc from A and B?

... set things up ...    
    matrix b=r(table)
    local a=b[1,1]
    local b=b[2,1]
    putexcel A`row'=(`a') B`row'=(`b')
    local ++row
...continue with loop ...

Regards
Laurence

[Clyde Schechter]

Well, there is no direct way to loop through A, B, C, D, directly, but you could do something like this:

local ncol = 1
local col: word `ncol' of `c(ALPHA)'
putexcel `col'`row' = (`whatever')
... other stuff perhaps
local ++ncol
... continue with loop

A substantial limitation of this solution is that if you need to go beyond column Z it will break. Depending on how far into double-letter column you need to go, you can modify this code (at the expense of a bit of complication) to deal with that, but it's cumbersome. Writing a fully general solution to this is possible, but it's for me hard to imagine it comes up often enough to be worth the trouble.

[Laurence Lester]

Thanks Clyde,
That's puts substance to the vague feeling I had; it will do the job for me.
Regards
Laurence

[Niels Henrik Bruun]

Hi
Also if going beyond z is a problem use -xl- from Mata.
There are methods put_string and put_number.
Their arguments are reals for both row and column id.
And you can loop like

mata: xl.put_string(`++row', 1, "`Some text")

Typically I build a row and then go to the next row to avoid mistakes.

This is what I usually do.
Also I almost always convert cell content to strings.
This way I avoid problems with regional settings.

[Robert Picard]

Here's a little program that converts base 10 numbers into Excel column identifiers

*! version 1.0.0, 26feb2015, Robert Picard, [email protected]      
program define xlscol, rclass

    version 9
    
    args j
    
    confirm integer number `j'
    
    while `j' > 0 {
        
        local i = mod(`j'-1,26)
        local letter = char(`i' + 65)
        local res `letter'`res'
        local j = int((`j'-`i') / 26)
        
    }
    
    dis as text "col = " as res "`res'"
    return local col `res'
    
end

You need to save the above as "xlscol.ado" in a directory where Stata will find it (help adopath). Here's an example of how to use it

forvalues i = 1/110 {
    qui xlscol `i'
    dis `i' "  `r(col)'" 
}

[Hua Peng (StataCorp)]

There is an undocumented Mata function numtobase26() which we use in -export excel-. The function takes a real matrix, returns the base 26 representation of each entry.

mata:
a = numtobase26(20)
a = numtobase26((100, 20\4, 55))
end

[Sergiy Radyakin]

ExcelCol was created precisely for this task:
https://ideas.repec.org/c/boc/bocode/s457865.html

Described here:
http://radyakin.org/stata/excelcol/

Best, Sergiy Radyakin

[Laurence Lester]

I never ceases to impress me how helpful this list is.
Thanks to all.
Regards
Laurence