Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • xl() kills Excel formatting. Why?

    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:

    Code:
    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

  • #2
    The output of help xl() tells us that adding
    Code:
    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

    Comment

    Working...
    X