Title

xtable - Export table output to Excel.

Syntax

xtable uses the same syntax of table:

xtable rowvar [ colvar [ supercolvar ] ]
[if] [in] [weight]
[, table_options export_options]

where table_options are any options listed for
table, except for replace and name().
Unlike table, xtable does not accept the prefix
by (as in by varlist: xtable).

export_options Description

filename(filename) name of the Excel file (passed to putexcel); default is xtable.xlsx
sheet("sheetname" [, replace]) write to Excel worksheet sheetname (passed to putexcel)
replace overwrite Excel file (passed to putexcel); default is to modify if filename is specified or overwrite xtable.xlsx
modify modify Excel file (passed to putexcel)
noput do not export table; output will only be available in stored results

Description

xtable is a drop-in replacement for table.
You can just replace table with xtable in your code and
use it the same way (see Examples). The actual tabulation will be
done by table, so you will get the exact same output
in the results window, plus a link to an Excel
spreadsheet containing the exported table.

The only major restriction is that you cannot use
it with the prefix by. 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.

xtable leverages table's replace option to
create a matrix that reproduces as best as possible what is shown
on screen and then exports it using putexcel.
Because it depends on putexcel, xtable requires Stata 13.1
or newer.

Options

table_options

Please refer to table to see the full range of its capabilities. Formatting options such as cellwidth(), concise and format(%fmt) will affect only the output shown in the results windows, not the exported table.

export_options

By default, 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 putexcel:

filename(filename) specifies the name of the Excel file to be used. Default is "xtable.xlsx".
Both .xlsx and .xls extensions are accepted. If no extension is specified, .xlsx will be used.

sheet("sheetname") specifies the name of the Excel worksheet to be used.
If the sheet exists in the specified file, the default is to modify it. To replace it, use sheet("sheetname", replace).

replace overwrites the Excel file. Default is to modify if filename is
specified or overwrite "xtable.xlsx".

modify modifies the Excel file.

noput keeps 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 any other way (see example below). This might be particularly useful if you use Stata 14 or newer,
which added formatting options to putexcel.

Examples

Setup
. sysuse auto

One-way table, multiple statistics, with row totals
. xtable foreign, cont(mean mpg sd mpg) row

Two-way table, multiple statistics, specifying filename and sheetname
. xtable foreign rep78, cont(mean mpg sd mpg) filename(results.xlsx) sheet(table1, replace) replace

-------------------------------------------------------------

Setup
. webuse byssin1

Four-way table, with frequency weight and row, column and supercolumn totals
. xtable workplace smokes race [fw=pop], by(sex) c(mean prob) row col scol

Cancel automatic exporting and use stored matrix with putexcel:
. xtable workplace smokes race [fw=pop], by(sex) c(mean prob) row col scol noput
. putexcel A1 = ("A nice and informative title") A3 = matrix(r(xtable), names) using myfile.xlsx, replace

Stored results

r(xtable): matrix reproducing the specified tabulation.
If present, value labels for row, column and superrow variables are
included as matrix rownames and matrix colnames.

Author

Weverthon Machado

weverthonmachado[at]gmail[dot]com

weverthonmachado.github.io

v1.0.2