help excel2latex

Title

excel2latex -- Convert Excel table to LaTeX table

Syntax

excel2latex,[options]

options

saving(["]destfile["],s_options) Save the coverted table to destfile. Note that
destfile can contain the directory where it is located. The s_options specifies how to
save the coverted table. It has two options: replace
and append. replace will overwrite an existing file or create a new file if the
"destfile.filetype" does not exist. append just writes the converted table to
the end of the existing file.

as(filetype) Specify the type of file to save, e.g., .txt, tex or .log;
the default type is .tex.

display Display the latex table in the Stata results window. At least one option of saving()
and display must be specfied. If saving() is not
specified but display is or saving(destfile,replace are specified, then the latest table will be
displayed in the results window; if saving(destfile,append) is specified, all
tables contained in the the destfil, including the latest table will be displayed.

using(["]sourcefile[": sheetname],u_options) Import the table which is intended to be coverted.
u_options tells Stata the data format of the sourcefile. Currently, excel2latex
supports four types of files: Excel spreadsheet, text (tab delimited), csv and Stata data files.
Correspondingly, u_options has four possible values: excel, txt, csv and dta.
For spreadsheet in Excel, if the sheetname is not specified, the
first sheet will be imported to Stata for the conversion, for example, using("tables": table 1,excel)
and using("tables",excel) performs the same task if "table 1" is the first sheet of "tables.xls".
If two .xlsx and .xls files have the same name, e.g., "tables.xlsx" and "tables.xls", and they are in the
same directory, Stata imports the tables in the .xls file when the extension name is not specified in the using() option.
If using() is not specified or is empty (i.e., using()), the current active data in
the memory will be converted into a LaTeX table. When importing the table in other formats as data
to Stata, excel2latex removes the empty columns.

landscape Set the layout of the table to be landscape; the default is portrait.

caption(text) The title of the table.

titlecell(#1 #2 #3) #1 and #2 are the row and column numbers of the cell in which
the title is located in the spread sheet or data set; #3 is the number of letter from which
the "real" title starts. For example, if the title in the Excel spreadsheet is "Table 3:
Robustness checks", #3 should be 10 because the first 9 letters (including blank space)
just mark the order of the table, which will be done automatically in Latex. If caption
and titlecell are both specified, the title in caption() rather than that
in the original sourcefile will be added to the resulting Latex table.

label(text) The label of the table, which is used to refer to the table
in the text of the paper. This can be used only if either titlecell() or caption()
is specified.

makebox(#) The width of the table (as a # multiple of the standard text width);
this option is often used in the case where the table width is wider than the standard text
width (the default table width)

baseline(#) The line spacing. The default is single line spacing.

arrraystretch(#) The the space between the rows of the table. The default
is 1.2.

tabpos(text) The position of table; it may be h, t, b, H,
!thb and so on; the default is h, namely, here; note that to use the position H,
the Latex package float should be used

fontsize The font size of the text in the table, such as large, footnotesize, etc.
The default font size is small.

notefontsize Specify the font size of the notes. Without using this option, the font size of the note
is the same as that specified by fontsize.

notecell(#1 #2,#2 #3,...) A table may inclue more than one note.
#1 and #2 are the numbers of the row and column where the first note is located; #3 and #4 are
the numbers of the row and column where the second note is located, etc.

notescontent(string) The content of the notes. If this option is specified,
the content of the notes will replace the content in the row #1 and column #2 (and so on) in Excel
spreadsheet (if it is specified by notecell()).

nonote There is no note for the table regardless of whether notescontent and notecell()
are specified. Without this as well as notecell() and
notescontent(), excel2latex will leave the place for note ("Note: ..."),
although there may be no note in the original table.

hline(numlist) Draw horizontal lines below the rows specified by hline. For
example, hline(4 10) means drawing two horizontal lines below rows 4 and 10.

droprow(numlist) Drop the rows specified by droprow(). For example,
dropr(11) means dropping the 11th row of the original table when coverting it to the
LaTeX table.

colalignment(alignment[,force]) Specify the alignment of columns, e.g., lccc
or lcc|cc. The number of alignments specified should be equal to the number of columns.
If some sophiscated alignment is used here, e.g., p{2cm}cccc, it is neccessary to add
the otpion force.

multicolumn(#1 #2 #3 alignment1, #4 #5 #6 alignment2,...) #1 and #2 are the numers of the
row and column in which the first multi-collumn cell begin, #3 is the number of columns
included in the cell, and alignment1 specifies the alignment in the multi-column cell #4, #5, #6 and
alignment2 are for the second multi-column cell, and so on. For example, multic(2 2 3 l)
means the multi-column cell begins from the seond row and second column, it includes 3 columns
and the content in the cell should be left aligned.

Description

excel2latex converts Excel tables to LaTeX tables. The basic idea is to import the
an Excel table to Stata as a data set and then write the data into an ASCII file in the
format of a LaTeX table. Altough the main purpose of excel2latex is to covert the
Excel table to LaTeX table, it also supports tables in .txt, .csv or .dta format. Actually, any data in
the memory can export into a LaTeX table. Thus, we can just import the data/table
into Stata, make some modification and then export them into a LaTeX table using
excel2latex. Note that excel2latex
may not be able to create the table exactly as desired, but it can help perform the most
tedious task involved in making tables in LaTeX. In addition, the LaTeX tables made by
excel2latex are based on the tabularx package. To compile the .tex file,
users should install this package and import it by adding "\usepackage" in the preamble.

There is one point to be noted: If a table contains _, #, $, > and <, excel2latex replaces them with \_, \#, \$, $>$ and $<$, respectively, in the resulting LaTeX table. If there are greek letters and another symbols in the table, users can edit the table either before or after using excel2latex. The example 2 below show how to edit the table using Stata before the conversion.

All the examples below use the ancillary files: "tables.xlsx", "tables.xls", "table 1.dta", "table 2.txt", "table 3.csv".

Example 1: convert a table in Excel spreadsheet to LaTeX

excel2latex,using("tables",excel) saving(table,replace) as(tex) titlecell(1 1 9) multicolumn(2 2 3 c, 2 5 3 c) notecell(19 1, 20 1) fonts(footnotesize) makebox(1.1) hline(4) label(summary) notefonts(scriptsize) disp

excel2latex,using(tables:Table 2,excel) saving(table,append) notecel(49 1, 50 1) fonts(footnotesize) titlec(1 1) hline(4) multicolumn(2 2 2 c, 2 4 2 c) makebox(1.05) nonote label(tb:OLS_Lewbel)

Example 2: import an Excel table into Stata, edit it, and then covert it to a Latex table

import excel using "tables.xlsx", clear sheet("tab1")

rename (_all)(var1 var2 var3 var4 var5 var6)

cap tostring var4,replace

replace var4="~"

foreach i in 2 3 5 6 {

destring var`i',gen(tmp`i') force
tostring tmp`i',replace format(%10.2f) force
replace var`i'=tmp`i' in 1/14 if tmp`i'~="."

}

drop tmp?

replace var1=subinstr(var1,"levela","level\textsuperscript",1)

replace var1=subinstr(var1,"schoolb","school\textsuperscript",1)

replace var2="\textbf{"+var2+"}" in 2

replace var5="\textbf{"+var5+"}" in 2

replace var1="\cline{2-3} \cline{5-6}" + var1 in 3

foreach i in 2 3 5 6 {

gen tmp`i'=strpos(var`i',".") in 4/14
qui sum tmp`i'
local maxtmp`i'=r(max)
if `maxtmp`i''>0 {
forvalues j=1/`maxtmp`i'' {
replace var`i'="~"+var`i' if strpos(var`i',".")<`maxtmp`i'' in 4/14
}
}

}

drop tmp?

local cola=">{\centering\arraybackslash}p{0.3\textwidth}"

excel2latex, saving(table,append) as(tex) makebox(1.1) titlecell(1 1 9) multicolumn(2 2 2 `cola', 2 5 2 `cola', 15 2 2 c, 15 5 2 c) notecell(16 1) fonts(scriptsize) hline(3 14) label(tab1) notefonts(scriptsize)

Example 3: convert a table in .txt, .csv and .dta files to LaTeX

excel2latex,using("table 1",dta) saving(table,append) as(tex) titlecell(1 1 9) multicolumn(2 2 3 c, 2 5 3 c) notecell(19 1, 20 1) fonts(footnotesize) makebox(1.1) hline(4) label(summary) notefonts(scriptsize)

excel2latex,using("table 2.txt",txt) saving(table,append) notecel(49 1, 50 1) fonts(footnotesize) titlec(1 1) hline(4) multicolumn(2 2 2 c, 2 4 2 c) arraystretch(1.1) notefonts(scriptsize)

excel2latex,using("table 3",csv) saving(table,append) fonts(footnotesize) titlec(1 1 10) droprow(19 20) notecell(19 1, 20 1) multicolumn(2 2 2 c, 2 4 2 c, 5 1 5 l, 11 1 5 l) hline(4 10)

Authors

Guochang Zhao

Research Institute of Economics and Management

Southwestern University of Finance and Economics

Chengdu, China

gc_zhao@foxmail.com

Mengmeng Guo

Research Institute of Economics and Management

Southwestern University of Finance and Economics

Chengdu, China