xl() kills Excel formatting. Why?

[boriskaiser]

Hi everyone,

I'm using xl() in Mata to write lots of results to Excel files. The task is large: around 20'000 files with tables on 20 sheets each. I'm using xl() for speed, putexcel in Stata is not an option.
The empty tables are nicely pre-formatted. My problem is that when I fill in the numbers, xl() kills all of the formatting. Here's a mini example:

putexcel set "test.xlsx", sh(Tab) replace
putexcel A1 = 9.234346325, left font("Times New Roman", 14) bold nfor("0.00") // add some formatting

mata: mata clear
mata
    b=xl()
    b.set_mode("open")
    b.load_book("test.xlsx")
    b.set_sheet("Tab")
    b.put_number(1,1,7.223890456) // put a number
    b.close_book()
end
// the formatting is gone

Can someone please help me with this?

Thanks very much in advance!

Best,
Boris

[William Lisowski]

The output of help xl() tells us that adding

b.set_keep_cell_format("on")

to your code will do what you want. The default is "off", so the specific answer to the subject you chose for your post

xl() kills Excel formatting. Why?

is

because you didn't tell it not to do so