Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 36 ยท Reproducible Reporting

Exporting to Excel, Word and PDF: putexcel, putdocx, graphs

Export results from Stata to Excel with export excel and putexcel, build Word and PDF reports with putdocx and putpdf, and save graphs with graph export.

Analysis is only useful once someone else can read it. Stata includes commands that write directly into Excel workbooks, Word documents and PDFs, and others that save graphs as image files. Because these are commands in a do-file, the report is rebuilt every time the data or code changes, with no copy and paste.

Stata cannot run on this site, so the commands below are read-only. We do not print Stata output or show the files it creates. The runnable blocks are Python stand-ins that build the same kind of content (cell grids, report text, an image-size calculator) so the logic is visible.

Whole datasets to and from Excel

STATA
import excel "data/raw/survey.xlsx", sheet("Responses") firstrow clear
export excel id wage educ using "output/clean.xlsx", firstrow(variables) replace
export excel using "output/clean.xlsx", sheet("Wages", modify) firstrow(variables)
export delimited using "output/clean.csv", replace

firstrow treats row 1 as variable names; cellrange(A2:F500) limits the area; allstring imports everything as text so you can clean it yourself. Spreadsheet types are guessed, so check describe afterwards, and keep the original workbook untouched. export delimited and import delimited handle CSV files and are more reliable than Excel for large data.

putexcel: writing cells and formatting

putexcel writes individual cells, matrices and formatting into a workbook:

STATA
putexcel set "output/results.xlsx", sheet("Summary") replace
putexcel A1 = "Variable"  B1 = "Mean"  C1 = "SD", bold
summarize wage
putexcel A2 = "wage"  B2 = `r(mean)'  C2 = `r(sd)', nformat(number_d2)
regress wage educ exper
matrix T = r(table)'
putexcel E1 = matrix(T), names nformat(number_d3)
putexcel save

putexcel set chooses the file and sheet; each later call writes at a cell. Use A2 = formula(...) to insert real Excel formulas, putexcel (A1:C1), border(bottom) and font(Arial, 11), fpattern() for formatting, and putexcel clear when you finish. Matrices (matrix list e(b)) and etable (Stata 17+) export in one step.

putdocx: a Word report

STATA
putdocx begin
putdocx paragraph, style(Title)
putdocx text ("Wage analysis")
putdocx paragraph
putdocx text ("The sample has `=_N' workers. Mean wage is "), 
putdocx text ("`: display %5.2f r(mean)'"), bold

regress wage educ exper
putdocx table results = etable        // a regression table, formatted
graph export "output/wage_hist.png", replace width(1600)
putdocx paragraph
putdocx image "output/wage_hist.png", width(5)
putdocx save "output/report.docx", replace

putdocx begin starts a document in memory, paragraph, text, table, image and pagebreak add content, and putdocx save writes the .docx. You can build tables from matrices (putdocx table t = matrix(M)) or from a collect table with putdocx collect. The idea is that every number in the text comes from stored results (r(), e()), so nothing is typed by hand.

putpdf has the same structure for PDF: putpdf begin, putpdf paragraph, putpdf text, putpdf table, putpdf image, putpdf save.

Graphs

STATA
histogram wage, normal title("Distribution of wages")
graph export "output/wage_hist.png", replace width(1600)
graph export "output/wage_hist.pdf", replace
graph save "output/wage_hist.gph", replace      // editable Stata format
graph combine g1 g2, cols(2) 
graph set window fontface "Arial"
set scheme s1color

graph export writes PNG, PDF, SVG, EPS or TIFF according to the extension. For publication, vector formats (PDF, SVG, EPS) stay sharp at any size; for the web, use PNG with a width() of at least 1200 pixels. graph save keeps the editable .gph. Name graphs with name(g1, replace) so that you can combine them.

Stand-in: a cell grid and a text report

First, a Python stand-in for what putexcel does: place values in A1-style cells and show the resulting grid.

Python A putexcel-like cell writer (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Second, a stand-in for putdocx: assemble a report whose numbers come from computed values, never typed in.

Python A report built from results (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If the data change, the report changes with them, which is exactly the value of putdocx over a hand-edited Word file.

Choosing a method

Need Tool
A regression table for a paper esttab (tables) or etable
Raw cells and a custom layout in Excel putexcel
A complete Word or PDF report putdocx, putpdf, dyndoc
Figures graph export
Data for another program export delimited, export excel
Markdown reports with code dyndoc or the user-written markstat (see the workflow lesson)

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which command starts a Word document in memory?

    putdocx begin starts a document; putdocx save writes it to disk.
  2. 2. What does firstrow do in import excel?

    firstrow turns the spreadsheet's header row into variable names.
  3. 3. Which graph format stays sharp at any size?

    Vector formats such as PDF, SVG and EPS scale without pixelation.
  4. 4. Why take numbers in a report from r() and e() rather than typing them?

    Pulling values from stored results removes transcription errors and keeps the document in sync.

Summary

  • import/export excel and export delimited move data; putexcel writes cells, formats and matrices.
  • putdocx and putpdf build Word and PDF reports from stored results; graph export saves figures.
  • Pull every number from r() and e() so the report stays in sync with the analysis.
  • Python blocks are stand-ins that show the logic.

Next: A reproducible research workflow