[Tex/LaTex] How to convert a table with multicolumns from excel to latex

Question

My question is that different cols have different widths. each row has multicolumns whose widths change. How do i have a macro which allow me to input such table from Excel to Latex. Thanks in advance.

Answer 1

I will summarize the simple solutions in this answer and write a Matlab-integrated in the sequence.

---

1. Do LaTeX in Excel: Slow, difficult, high manual effort, susceptible to many errors, can be reusable but need copy and paste every time.

This method you just built the LaTeX by typing the pieces you need inside Excel. That's was the first idea I ever had. It will be useful only if you can copy and paste the data at the exactly same template. But you will always have to copy and paste ... and it is incredible slow.

Consider the following spreadsheet

Starting to write the commands (I made some mistakes when creating it). It is easier to define the commands on separated cells.

The auto complete tool helps but not too much if you have many special cases.

Every merge must be manually correct and it is not simple to edit it correctly.

Then I noticed a error. \hline should be \\ \hline and then add a \hline only above the first row.

The result must be copied into your LaTeX editor.

---

2. The services online: Good velocity, easy, medium manual effort, previews avoid errors, not reusable and need to copy and paste every time.

a) excel2latex.com

It is faster, just drag your .xls or .xlsx file into it. But you might have to edit extra rows and lines are always inserted.

b) tablesgenerator.com

The best option for few tables, it has the tools to edit the table and generate LaTeX code. It has the slow down of converting your file into .csv, but the quick tools to format really payoff.

The syntax is presented and can be edited.

And adding borders is as easy as it is in any WYSIWYG software

---

3. My Matlab-integrated solution: Fast after preparations, easy, low manual effort, errors are quickly corrected, the most reusable and no need to copy and paste every time.

Another important backslash is that Matlab is a commercial software and I cannot say my codes work properly on Octave. Some of them use syntax I have never tried in Octave.

The solution is still not perfect, I usually don't have special cases to merge using \multicolumn{cols}{pos}{text} or worse \multirow{number of rows}{width}{text}. Although, I have coded a solution to the same problem in my Matlab to Word Lbrary, I still haven't made the same at the Matlab to LaTeX library.

My Matlab-integrated solution is presented in another dedicated answer.

Edit: I decided to integrate in only one answer.

---

So here we are with our Matlab-integrated solution.

To perform to full task, we gonna need three of my libraries

1. Matlab to LaTeX Library

To manage latex syntax and create it from matlab objects.

2. File Manipulation Library

To load excel file and to create the .tex file directly from Matlab.

3. Search and Replace a WordSet Library

This library is the master Gambiarra (workaround in Brazilian Portuguese) of all Gambiarras.

It let's you replace a given set of words for another set in as many files you want. It almost the same as NotePad++ does with its Find Files command. But here you can do it programmatically and even taking advantage of the sequence of operations.

---

The LaTeX MWE is indeed very simple

\documentclass{article}
\usepackage{multirow}
\begin{document}
    \input{input_tab_test}
\end{document}

The key concept is to always create external files, so that you only need to input it into the main file. It let's you modify external files as many times as you want.

input_tab_test should look like this when we finish

---

The MATLAB code follows among the explanations.

Load Excel into Matlab, 'F5:K11'is important to avoid extra cells. The second entry, 1, is the number of the sheet.

excel_filename = 'Pasta1.xlsx';
[data, txt, raw] = xlsread(excel_filename,1,'F5:K11');

The following loop converts all entries of the cell raw into strings. I will probably promote it to a function soon.

% Analysis needed because all data must be string to the code
new_raw = cell(size(raw));
for i = 1:size(raw,1)
    for j = 1:size(raw,2)
        if isnumeric(raw{i,j})
            if isnan(raw{i,j})
                new_raw{i,j} = '';
            else
                new_raw{i,j} = sprintf('%g',raw{i,j});
            end
        else
            new_raw{i,j} = sprintf('%s',raw{i,j});
        end
    end
end

Then we define the format and call the first library function.

format = '| p{1cm} | p{0.5 cm} | p{1.5cm} | p{2cm} | p{3cm} | p{1cm} |';
tb_l = fc_lib_latex_cell_to_tab_line(new_raw,'\hline');

For repeated letters use:

format = repmat('c',[1 6]);

tb_l is the very same result from the online gadgets.

Now, a function with some syntax is called to add \begin{tabular}, \end{tabular} and whatever is necessary. The Matlab command char is the key here.

S = fc_lib_latex_struct_strings_02_table;
l1 = sprintf(S.btab,format);
tab = char(l1,'\hline',tb_l,S.etab);

tabshould look like this

And finally we can save it in the disk with a function from the second library.

str_ext = '.tex'; file_name = 'input_tab_test';
fc_lib_save_file_extensao(file_name, str_ext, tab);

As I don't have a merge columns function right now, I will use the third library. This step makes the whole process less efficient, as the user still have to manually define the substitutions. You can be a little bit flexible as the example with w1.

w1 = 'f';
input = {sprintf('%s &  &', w1),...
    'o &  &  &',...
    'z &',...
    '1 &'};
output = {sprintf('\\multicolumn{3}{|c|}{%s}',w1),...
        '\multicolumn{4}{|c|}{o}',...
        '\multicolumn{2}{|c|}{z}',...
        '\multicolumn{2}{|c|}{1}',...
    };

To merge, I will create a cell with the row number and a vector with the first and last cell of each group to merge.

colMerge_p = {1, [3,4; 5,6];
             2, [1,3; 4,6];
             4, [1,6]};

But I still need to code it for the LaTeX syntax.

Finally, we call the function to search every input and replace the respective output.

filter = {'input_tab_test'}; encod = 'ISO-8859-1'; flag_copy = 0;
fc_lib_file_search_replace_wordset(pwd,filter,input,output,flag_copy,encod);

The variable filter filters only files of interesting.

This method works better when you don't have special cases, in the future this should not be a issue anymore, and the greatest advantage is the incredible speed to correct mistakes of to reuse with others tables with the same template. And as it saves a external file, all you need to do is to recompile the main .tex file.

This is the result in LaTeX

Edit 2: New function to automatic merge columns.

Consider the following spreadsheet

Loading it into Matlab . The result of the command tb_l = fc_lib_latex_cell_to_tab_line(new_raw,'\hline'); is

The new function to merge is new_tb_l = fc_lib_latex_tab_line_colMerge(tb_l,colMerge); where

colMerge = {2, [1,3], '|c|';
    3, [3,4], '|c|';
    4, [1,4], '|l|';
    6, [3,4; 5,6], '|c|';
    8, [1,6], '|c|';
    9, [1,5], '|r|';
    10, [2,3; 4,6], '|c|';
    11, [1,2; 4,5], '|c|'};

the first entry is the line to merge, the second has a matrix with first and last columns of each gap to merge and the third entry is the alignment. The LaTeX syntax is

and the final result is

Don't forget `\usepackage{inputenc}' to deal with accented characters.