• View
• Edit
• Upload
• History
• Print

CopyTablesFromSpreadsheets

Pasting data from a spreadsheet into LyX

As of Lyx 1.5, you can get data copied from a spreadsheet into Lyx using the "Paste Special" command:

• Follow the procedure below up to step 5
• Instead of using the middle button, select either Edit -> Paste Special -> Plain Text or Edit -> Paste Special -> Plain Text, Join Lines
• Sometimes you seem to need the "Join Lines" version, sometimes the standard one (?)

-- Zeyn

On the mailing list Samar Singh indicated a great and apparently undocumented way to get tabular data inside a Lyx table:

1. open your xls file with gnumeric or open office.

2. Select the requisite number of columns and rows

3. Come back to your lyx document

4. Insert a table which has at least the selected number of rows and columns

5. Go to the cell on your lyx table where you want the top left corner of the 
   section to be placed.

6. Click the middle button on your mouse (if you only have two buttons, press 
   both buttons simultaneously).

That should be it. The experts tell me this should not work but unfortunately 
for the theory, it does for me. 

samar

-- samar j. singh

DW added: This does not work for me. I must select Edit -> Paste Special -> Selection, join lines

Sudarsun added: This works for me on Ubuntu 16.10, LyX 2.1.4, Gnumeric 1.12.28. Copy table from Gnumeric using ctrl+c and Paste using middle button onto LyX table works like a charm.

Kenward Vaughan added:

I myself have tried this succesfully on Windows with Excel and Lyx 1.3.6 -- and by the way, instead of the middle mouse button sometimes it also worked to paste via menu selection or C-v shortcut (but not always).

--

For my Excel 2002 on WinXP and LyX on Win V136-1 it also works by direcly copying the cells from excel, without opening it in openoffice or gnumeric. The only nasty thing is that the last column gets an additional square character.

-- Laura

Spreadsheet as an external inset

New in LyX 2.0 is the ability to insert a spreadsheet as "external material". This inserts a link to the spreadsheet file in LyX. The spreadsheet is not copied, so if you update the spreadsheet later, the LyX document is updated too.

Follow these steps in LyX:

1. Insert->File->External Material
2. In the dialog, select "Gnumericspreadsheet" (This also works for excel and openoffice spreadsheets.)
3. Select the spreadsheet file you want

When you print or preview the document, the entire spreadsheet appears in the document. Long spreadsheets will be broken up across page boundaries. Note that it is necessary to have gnumeric installed, even if using excel/openoffice spreadsheets. This is so because the conversion software is part of gnumeric. Make sure that LyX runs as administrator in Windows to allow it to spawn the gnumeric converter.

Example

-- Helge Hafting

Spreadsheet import from CSV file

You can easily import tables created by Excel or some other spreadsheet program to a native table in LyX.

To do so, just follow these three steps:

1. Save your table from the source spreadsheet program as .csv file.
2. In LyX, select "File"->"Import"-> Table "CSV"
3. Select your saved .csv table

A new LyX files opens, containing your table in native LyX format. You can now easily copy this table to your original LyX-file.

-- Constantin Ludmann

Tables using EPS

In OpenOffice, it is possible to export tables as EPS and include it into Lyx with all the formatting (color, fonts, borders etc) exactly like in OO Calc

Follow these steps:

1. Open the spreadheet, select and copy the region of interest.
2. Select File->New->Drawing.
3. Right click somewhere on the drawing page and Paste.
4. Select File->Export now to get the EPS. (Note the selection only option on the file dialog, if you export more than the selection the bounding box will be too big)

As far as I can tell, this does NOT work for PDF export. How can one export an encapsulated (ie no excess bounding box, no page) PDF version of a table directly? (There are of course other external tools for converting EPS to PDF)

See WinGraphics for more EPS related exports

Tables using Calc2Latex

Calc2LaTeX is an OpenOffice macro to convert OOCalc tables to LaTeX. It works well and is easy to use. You can paste the result in a LaTeX file and then import it in LyX.

-- Charles

Tables using Gnumeric

Gnumeric is a spread-sheet program with a good LaTeX export. Follow these steps:

• Design your table in Gnumeric. You can make the complete layout in Gnumeric. Displayed digits and writing emphasis-styles as bold, small caps or italic are all taken over, likewise the table lines. Thick lines are converted in double lines.
• In Gnumeric, go to Data -> Export Data -> Export into Other Format ..., select a File Type of "LaTeX 2e (*.tex)" and save the data.
• Open the *.tex-file in any text editor. Read the instruction in the first comment box.
• In the same text editor, copy the commands for the LaTeX preamble. It is easier to do this from the actual code of the TeX file, since you won't need to remove the comment symbols. You need the \usepackage directives. I had to comment out the first one, %\usepackage[latin1]{inputenc}, because it conflicted with an existing setting using latin9.
• Start LyX. Open the dialog box Document -> Preferences -> LaTeX Preamble.
• In LyX insert the commands just copied into the "LaTeX Preamble".
• In Lyx, write the following command into a ERT box at the beginning of your LyX document:

• In your LyX document go to the location in which you wish the table to appear. Create an ERT and put \input{MyFile.tex} into it, as advised the .tex file comment.

Notes:

• The *.tex-file can be user edited. In this way you can also realize formatting that is not possible in Gnumeric itself. For example you can insert mathematical formulas between two dollar characters ("$...$").
• The table headers generated by this process were all in math mode. Each heading that had been generated by Gnumeric was surrounded by "$...$", which I had to remove manually.
• If you insert the table into a float, you must add the following command into the *.tex-file:

• If numbers were formatted with the euro-symbol in Gnumeric, a question mark ("?") appears in the *.tex-file. In the text editor you must replace the question marks by a LaTeX coding for the euro-symbol over "Find And Replace". The package marvosym offers quite a good euro-symbol. To use it:

    + In the *.tex-file replace the offending question marks with the character string "\EURtm".
    + In your LyX document add \usepackage{marvosym} to the "LaTeX Preamble".

• Data -> Export Data -> Export into Other Format ... offers 3 different options for exporting to .tex. The others do a more limited export and do not have the initial comment referred to in the previous instructions. They probably could be made to work.
• The instructions above are from LyX 2.3.6 and Gnumeric 1.12.48 on Debian GNU/Linux 11/bullseye in September, 2023. The previous version of these instructions differed on some points:

    +  Go to File -> Save As... and store the table as a *.tex-file.  My version has no tex options in Save As, even though a "HTML & TeX" plugin shows as active.  I found similar functions on Data -> Export Data.  Despite the latter's location under Data, it does seem to retain formatting.  This might explain why the headers were treated as math.

    +  Insert the table into the LyX document with Insert -> File -> Subdocument.  The closest my version had was Insert -> File -> Child Document, which seemed to be doing something else.  It wouldn't even allow me to click the OK button if I selected the tex file and chose input or include as the type.  So instead my revised instructions say to enter the include command in the ERT box.

--Marcus (creator) and Ross (updater)

Insert a table as a picture

The paste special command, described above can be also used to insert a table as a picture (either as PNG or JPEG).

1. Open the table in your spreadsheet application (e.g. Libre Office, OpenOffice.org)
2. Copy the part of the table, which you need
3. Go to LyX
4. Edit -> Paste Special -> Paste as PNG or Edit -> Paste Special -> Paste as JPEG
5. You are prompted to choose the place to save the image file. Choose a file on your harddrive
6. et voilá. The table is inserted in exactly the same formatting style as in the spreadsheet application

Three things:

• I am using a German version, so the correct English entry might deviate somewhat in the wording
• A picture cannot be broken over several pages in a convenient way. So this has to be considered. You are basically deviating from the strict WYSIWYM principle
• You might have to explain to latex or to pdflatex how to handle a PNG or JPEG. There a plenty ways to do it.

--Ilja 

To use math mode in Gnumeric tables you need to pass LaTeX code directly without any changes. To achieve that you need to wrap your LaTeX code into \L{} or \l{} (I don't know the difference). For example to get Mass–energy equivalence you need to have this code in the cell:

 \L{$e = m c^2$}

--Roman Inflianskas Tips