2

I'd like to use LibreOffice Calc or MS Excel to create and edit spreadsheets and include these in LaTeX (LuaLaTeX would be fine, too). It is necessary for me to get calculated values like SUM(C2:C5) as well.

So far I've tried odsfile and exceltex. Where odsfile didn't work at all, exceltex did work but ignored calculated values.

Has anyone successfully tried to include spreadsheet data with calculated values?

Update

This question is not the same as "I'd like a easy interface for LaTeX-Tables", as I look for a more comprehensive solution. We use spreadsheets for modeling our problems. When we refine our models, I'd like to update the TeX-based reports without exporting/importing many tables.

That said, odsfile works pretty much as I wanted to.

Thanks for your comments and suggestiongs so far. Do you know any other solutions like odsfile?

Marcus Bitzl
  • 649
  • Most of the spreadsheet softwares are able to generate a .csv - a list of comma seperated values (which will contain also calculated values, afaik). From there you could try to make your way with a perl script or something similar. – Ruben Jan 27 '14 at 11:58
  • This works fine with export to .csv. I do it regularly. I just use a bash script to transform things and then use merge to pull it into a LaTeX template. Very straightforward once you've set it up. (Of course, if you edit the spreadsheet, you need to regenerate the .csv.) I use LibreOffice's Calc. I imagine Excel would work, too, but haven't tried that. – cfr Jan 27 '14 at 12:15
  • 1
    did odsfile produce some error message? could you send me some sample file which failed to work? (I am the author of odsfile) – michal.h21 Jan 27 '14 at 12:35
  • I noticed that I had not the most recent version. After upgrading it works well :-) – Marcus Bitzl Jan 27 '14 at 14:21
  • @michal.h21 are you still developing odsfile? The last CTAN update is from 2015. I guess it has to be adapted to reflect latest OO/LO and TeX package developments. What is the status? (I haven't tried it, yet.) – thymaro Jan 31 '18 at 07:58
  • @thymaro the Github version contains some updates, but I found that there are some issues which cannot be solved with the current code, so I started a new package, which will support xlsx, ods and csv formats. It is in early stage and I haven't been able to work on it lately. – michal.h21 Jan 31 '18 at 09:56
  • @michal.h21I'd offer to help, unfortunately, I'm not a coder. Is there anyways to be notified, when the package is ready for beta testing? Even though I don't understand the inner workings of things, I'm not such a bad bug reporter (I think). – thymaro Jan 31 '18 at 13:00

2 Answers2

6

Whichever tool you use, you will hit a rock one day. This is a hack that I use:

enter image description here

In excel use the concatenation command to translate the cells to have the & inserted to the left at a mirror of the columns somewhere to the right of your columns.

Cut and paste as needed. Takes a few minutes, saves tonnes of time on complicated tables that you use all the time.

It also produces very neat tables when pasted to the editor.

enter image description here

And if you are not up to speed with LaTeX table will help you learn how to do it. It is not an automated tool, but has a good understandable workflow that you are fully in control. Alternatives tend to need a lot of clean up later.

yannisl
  • 117,160
1

TeXShop (for the Mac) includes a very handy macro for pasting MS Excel cells from the clipboard into LaTeX.

As an example, I opened a new Excel spreadsheet, typed 4 into cell A1, 3 into A2, =SUM(A1+A2) into B1 and =A1*A2 into B2. I then selected the four cells, copied them to the clipboard, opened TeXShop, and selected the command Macros > Paste Spreadsheet Cells. A dialogue box opened, and I chose the option simple. The resulting output was:

\begin{tabular}{ll}

4 & 7 \\
3 & 12 \\

\end{tabular}

Another option might be use the TeXTable or similar software.

Alex Roberts
  • 1,379