Insert line break in collect note when exporting to Excel

[Michael Evangelist]

I am using collect to export a table to Excel. I have a long table note that I would like to break up into multiple lines, either by inserting manual breaks or by having the note wrap with the table width. I'm able to insert a manual break using {break}, but the line break doesn't carry over when exporting to Excel. Any advice would be appreciated.

I've provided example code below. My actual table is much more complicated, and I'm not using dtable or etable.

sysuse auto, clear

collect clear

collect: mean weight
collect: mean length

collect note "Line 1 {break} Line 2"

collect layout (colname#result[_r_b _r_se])

collect export auto.xlsx, replace

[Jeff Pitblado (StataCorp)]

SMCL codes are not translated when exporting to other file formats. The strings in notes are exported asis.

If you want to break a long note into separate lines, post the note in separate pieces.

sysuse auto, clear

collect clear

collect: mean weight
collect: mean length

collect note "Line 1"
collect note "Line 2"

collect layout (colname#result[_r_b _r_se])

collect export auto.xlsx, replace

If you want to get Excel to automatically wrap your note with the width of the table, you need to figure out how many cells the table uses, then use putexcel to reformat the cell containing the long note. For example,

sysuse auto, clear

collect clear

collect: mean weight
collect: mean length
collect note "This is a long note that I would like to wrap with the width of the table, but that is not a built-in feature of the collect system. :("
collect layout (colname#result[_r_b _r_se])
tokenize "`s(table1)'"
args rows x cols
* add column for row headers
local ++cols
local ALPHA `"`c(ALPHA)'"'
local lastCol : word `cols' of `ALPHA'
* the notes begin in the row following the table,
* there are no column headers in this layout
local note_row = `rows' + 1

putexcel set auto.xlsx, open replace
putexcel A1 = collect
putexcel A`note_row':`lastCol'`note_row', overwritefmt txtwrap merge
putexcel save

You still have to open the file with your spreadsheet software to get the optimal column widths for the table and option row height for the note.

Here is what the exported table in auto.xlsx looks like after I got LibreOffice to use optimal row and column sizes.

[Michael Evangelist]

Jeff, Thank you for this solution. I was able to adapt the wrapping code to my problem. I have one follow-up question. As you pointed out, my example didn't include a header. Is it possible to access the number of headers and total number of notes. I'm able to adjust local note_row manually, but thought there might be a way to further automate placement.

Thanks for your help!

[Jeff Pitblado (StataCorp)]

Sadly, the cell counts used by the row and column headers is not reported by any collect commands right now.

However, I see how this information is helpful/necessary for certain kinds of automation, and while I cannot make any promises right now; I can see us providing this feature in a future Stata release.