Merging cells in Excel using Mata xl()

Question

I have a number of Excel tables created using the Stata commands putexcel or export excel.

I need to format many of the tables after saving. Because of the size of the files, I am trying to use mata for the formatting, rather than putexcel.

This works well, except when I try to merge two cells into one:

*Build sample matrix
set obs 10
foreach i of numlist 1/10{
    gen var`i' = _n + `i'
}

mkmat var1-var10, matrix(m)

*To Excel
quietly putexcel set "Test_Merge.xlsx", sheet("Sheet1") modify
quietly putexcel A1=matrix(m) 

*Prep for mata
mata: b = xl()
mata: b.load_book("Test_Merge.xlsx")

*Left-align some cells
mata: cols = (1,2)
mata: b.set_horizontal_align(1, cols, "left")

*Center some cells
mata: cols = (4,5)
mata: b.set_horizontal_align(1, cols, "center")

*Merge some cells
mata: cols = (7,8)
mata: b.set_horizontal_align(1, cols, "merge")

With the putexcel command i would do the following:

quietly putexcel (G1:H1), merge

However, in the mata version, the "merge" option appears to simply center the specified cells, rather than merging them together.

Am I misunderstanding the "merge" option in b.set_horizontal_align()?

Answer 1

You need to use the mata function b.set.sheet.merge() instead:

clear

*Build sample matrix
set obs 10
foreach i of numlist 1/10{
    gen var`i' = _n + `i'
}

mkmat var1-var10, matrix(m)

*To Excel
quietly putexcel set "Test_Merge.xlsx", sheet("Sheet1") modify
quietly putexcel A1=matrix(m) 

*Prep for mata
mata: b = xl()
mata: b.load_book("Test_Merge.xlsx")

*Left-align some cells
mata: cols = (1,2)
mata: b.set_horizontal_align(1, cols, "left")

*Center some cells
mata: cols = (4,5)
mata: b.set_horizontal_align(1, cols, "center")

*Merge some cells
mata: rows = (1,1)
mata: cols = (7,8)
mata: b.set_sheet_merge("Sheet1", rows, cols)

Note the changes in bold, which apply the function to the two cells.