[Tex/LaTex] Convert table from excel to latex with format $10^{-n}$

excelexcel2latex

I would like to convert a table from Mircosoft Excel to latex (on Winedt) based on Microsoft Windows operation system. The number in excel is, for instance,

1 2

1.00E-02 1.05E-03

I would like to have 1.00 $\times 10^{-2}$ and 1.05$\times 10^{-3}$ in latex table directly.

I have tried excel2latex.xla, it could convert table, but does not change formatting as $\times 10^{-2}$. Is there any simple way to do that? though I am somehow doubt about it…

Best Answer

The package siunitx could be a solution for you, but it requires some adjustment of the excel2latex output, which I'll explain below. siunitx defines a special column type, S used in place of the usual l,c,r specifications.

Each cell entry is then taken as the argument to siunitx's \num{} command, which accepts scientific notation input as 1.00E-02.

One behavior of this solution is that siunitx centers the columns around the decimal separator. siunitx tries to be smart about what is text/headings and what is numerical data. But anything that could be mistaken for numerical data (in your example, the first row, which I'm assuming contains headings) should be protected with curly braces.

So, to apply this solution to excel2latex output, you'll have to:

load the siunitx package in the document preamble,
change column specifiers from c (or l or r) to s in the beginning of the tabular environment for any columns with numerical data, and
protect any non-numerical data that shouldn't be aligned at the decimal separator with curly braces {}.

An example:

Before:

\begin{tabular}{cc}
1 & 2 \\
1.00E-02 & 1.05E-03 \\
\end{tabular}

After:

\begin{tabular}{SS}
{1} & {2} \\
1.00E-02 & 1.05E-03 \\
\end{tabular}

Complete Code and Output:

\documentclass{article}
\usepackage{siunitx}

\begin{document}
\begin{tabular}{SS}
{1} & {2} \\
1.00E-02 & 1.05E-03 \\
\end{tabular}
\end{document}

enter image description here

Many adjustments can be made for alignment and spacing of the S columns. If you decide this solution is workable for you, you can consult the package manual (Section 4.6) for details.

Related Solutions

[Tex/LaTex] A program to export latex tables to excel/ods/csv

Far from perfection, but for a document with a single table you can use detex and then replace & by , and delete empty lines. In Linux you can use some tools as grep and sed for this. The first line of the table is then the columns types, so you can delete manually or with some tool like tail. Example command line:

detex file.tex | grep -v '^$' | tail -n+2  | sed 's/&/,/g' > file.csv

Example input file.tex:

\documentclass{article}
\begin{document}
\begin{tabular}{llll}
    11 & 12 & 13 & 14\\
    21 & 22 & 23 & 24\\
    31 & 32 & 33 & 34\\
    41 & 42 & 43 & 44\\
\end{tabular}
\end{document}

Ouput (file.csv):

    11 , 12 , 13 , 14
    21 , 22 , 23 , 24
    31 , 32 , 33 , 34
    41 , 42 , 43 , 44

[Tex/LaTex] Link LaTeX Documentation to Excel Data

The datatool package has a Java helper application datatooltk that has a batch mode and a GUI mode. The batch mode is the default and allows it to be integrated into the document build process. Using this sample XLS file as an example, I can fetch the data from the first sheet (labelled products) using datatooltk and store it in datatool's internal database structure:

% arara: datatooltk: {
% arara: --> output: products.dbtex,
% arara: --> xls: shop.xls,
% arara: --> sheet: products }
% arara: pdflatex
\documentclass{article}

\usepackage{datatool}

\DTLloaddbtex{\products}{products.dbtex}

\begin{document}

Fetch values from row 3:
\DTLassign{\products}{3}{\Product=Product,\Price=Price (inc VAT)}
Product: \Product. Price: \Price.

\end{document}

I've included arara directives, but if you're not using arara the build process is (assuming the file is called test.tex):

datatooltk --output products.dbtex --xls shop.xls --sheet products
pdflatex test