xtable: Stata module for exporting table output to Excel
xtable exports output from Stata’s built-in command
table to an Excel spreadsheet. It works as drop-in replacement: you can just replace
xtable in your code and run it the same way (see Usage for minor restrictions and additional options). You will get the exact same output
in the results window, plus a link to an Excel spreadsheet containing the exported table.
table is a very powerful and flexible command, but it’s not easy to get its nice tables out of Stata for further processing. You have to resort to copy/paste or, at best,
putexcel command introduced in Stata 13 made exporting stuff to Excel a lot easier, but it relies on stored results and
table produces none.
replace option to create a matrix that reproduces as best as possible what’s shown on screen and then exports it using
putexcel. Because it depends on
xtable requires Stata 13 or newer.
Install it by typing:
ssc install xtable
You can use the exact same syntax from
xtable will just pass the arguments to
table and then export the results. So, instead of running:
sysuse auto table foreign rep78, c(mean mpg sd mpg)
you can just append an “x” and run:
sysuse auto xtable foreign rep78, c(mean mpg sd mpg)
Your data will be preserved, so the only difference you will see is a link to the Excel spreadsheet containing the exported table:
And the spreadsheet will look like this:
The only real restriction is that you can not use it with the
by prefix (i.e.
by varname: xtable). But bear in mind that you can specify row, column, supercolumn and up to four superrow variables, so you can get up to 7-way tabulations.
concise option, that suppresses rows with all missing entries, will not affect the exported table. If you use it, you will still get a concise table on Stata’s results window, but the Excel spreadsheet will contain all rows.
xtable will export the tabulation to a file named “xtable.xlsx” in the current working directory, overwriting it if it already exists. You can control the exporting process by using the following options, which will be passed to
filename(string): name of the Excel file to be used. Default is “xtable.xlsx”. Both .xlsx and .xls extensions are accepted. If you do not specify an extension, .xlsx will be used;
sheet(string): name of the Excel worksheet to be used. If the sheet exists in the specified file, the default is to modify it. To replace, use
replace: overwrite Excel file. Default is to modify if filename() is specified or overwrite “xtable.xlsx”. Note that
tablealso has a
replaceoption which is not honored by
modify: modify Excel file.
webuse byssin xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) sc filename(myfile) sheet(prevalence) replace
noput option will keep
xtable from writing to any file. Instead, it will just store the matrix in r(xtable), so you can include it in a
putexcel call (or use it in another way):
webuse byssin xtable workplace smokes race [fw=pop], c(mean prob) format(%9.3f) noput putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names) using myfile.xlsx, replace
This might be particularly useful if you use Stata 14 or newer, which added formatting options to
putexcel. The matrix saved in r(xtable) will not include supercolumn labels.
PhD Candidate in Sociology
Universidade do Estado do Rio de Janeiro