• 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 in Gnumeric your table. You can make the complete layout in Gnumeric. Writing emphasis-styles as bold, small caps or italic are taken over, likewise the table lines. Thick lines are converted in double lines.
• Go to File -> Save As... and store the table as a *.tex-file.
• Open the *.tex-file in any text editor. Read the instruction in the first comment box.
• Start LyX. Open the dialog box Document -> Preferences -> LaTeX Preamble.
• Change to the text editor. Copy the commands for the LaTeX preamble.
• Change to LyX. Insert the commands into the array "LaTeX Preamble". Remove the comment symbols. Write the following command into a ERT box at the beginning of your LyX document:

• Now the table can be inserted into the LyX document over Insert -> File -> Subdocument.

Notes:

• Self-evident the *.tex-file can be user-defined edited. In this way you can also realize formatting, which are not possible in Gnumeric themselves. For example you can insert mathematical formulas between two dollar characters ("$...$").
• If you insert the table into a float, you must the following command add in the text editor into the *.tex-file:

• If in Gnumeric numbers with the euro-symbol were formatted, 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". A quite good euro-symbol offers the package marvosym. In the *.tex-file then should stand the character string "\EURtm" instead of the question mark. In LyX you should add the following command in the dialog box "LaTeX Preamble":

--Marcus

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