# Insert libreoffice table as input

This question led to a new package:
odsfile

Suppose you have an localc spreadsheet myspread.ods and you want to insert the content of the range H4:I40 as a table into your latex document. I know that I can export the table or just do some copy and paste. However is it possible to write something like:

\includespread{file=myspread.ods,range=H4:I40}

Then the table in my LaTeX-document changes when the corresponding spreadsheet changes.

Would be nice to have also the possibility to modify global appearence properties of the table as optional arguments of \includespread.

-
This isn't really possible in (PDF)LaTeX: .ods files are actually zip archives. Maybe with LuaTeX, which has a zip library, but then you need also to interpret the XML files contained in the archive. – egreg Jun 19 '12 at 12:18
too bad LuaTeX doen't have an XML library included :( – topskip Jun 19 '12 at 12:54

Edit: There is now LaTeX package for testing https://github.com/michal-h21/odsfile

Before I post it on CTAN, any comments on style/grammar/spell in the documentation files, as well comments on the source code, are highly welcome.

There is solution using luatex's zip library and pure lua xml processing library LuaXML, which you should install to same directory as odsfile.lua.

ods format consist of number of xml files packed in the zip file. data are contained in content.xml file. its structure is following:

<?xml version="1.0" encoding="UTF-8"?>
<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" ... >
<office:scripts/>
<office:font-face-decls>
...
</office:font-face-decls>
<office:automatic-styles>
...
</office:automatic-styles>
<office:body>
<table:table table:name="List1" table:style-name="ta1" table:print="false">
<table:table-column table:style-name="co1" table:number-columns-repeated="3" table:default-cell-style-name="Default"/>
<table:table-row table:style-name="ro1">
<table:table-cell office:value-type="string">
<text:p>Hello</text:p>
</table:table-cell>
<table:table-cell office:value-type="float" office:value="1">
<text:p>1</text:p>
</table:table-cell>
<table:table-cell table:formula="of:=[.B1]+2" office:value-type="float" office:value="3">
<text:p>3</text:p>
</table:table-cell>
</table:table-row>
...
</table:table>
<table:table table:name="List2" table:style-name="ta1" table:print="false">
...
</table:table>
</office:body>
</office:document-content>


From sample file I created, it seems that sheet data are saved in office:document-content / office:body / office:spreadsheet / table:table path. Table rows are saved as table:table-row and cells as table:table-cell. Each cell has set datatype of its content, if cell contains formula it is saved in table:formula attribute. In every case, computed value ready for print is contained in subelement of table-cell, text:p. I hope this structure is created also with different versions of Open/Libre Office Calc than is mine, otherwise the code will be not working :(

With LuaXML, we can convert xml into lua tables, which can be then easily processed using standard lua techniques.

library odsfile.lua

module(...,package.seeall)
require "zip"
dofile("xml.lua")
dofile("handler.lua")

local p = {
file = zip.open(filename),
content_file_name = "content.xml",
local treehandler = simpleTreeHandler()
local filename = filename or self.content_file_name
local xmlfile = self.file:open(filename)
local xml = xmlParser(treehandler)
xml:parse(text)
return treehandler
end
}
return p
end

function getTable(x,table_name)
if type(tables) == "table" and table_name ~= nil then
for k,v in pairs(tables) do
if(v["_attr"]["table:name"]==table_name) then
return v
end
end
elseif type(tables) == "table" and table_name == nil then
return tables[1]
else
return tables
end
end

function tableValues(tbl,x1,y1,x2,y2)
local t= {}
if type(tbl["table:table-row"])=="table" then
local rows = table_slice(tbl["table:table-row"],y1,y2)
for k,v in pairs(rows) do
local j = {}
if #v["table:table-cell"] > 1 then
local r = table_slice(v["table:table-cell"],x1,x2)
for p,n in pairs(r) do
table.insert(j,{value=n["text:p"],attr=n["_attr"]})
end
else
local p = {value=v["table:table-cell"]["text:p"],attr=v["table:table-cell"]["_attr"]}
table.insert(j,p)
end
table.insert(t,j)
end
end
return t
end

function getRange(range)
local r = range:lower()
local function getNumber(s)
if s == "" or s == nil then return nil end
local f,ex = 0,0
for i in string.gmatch(s:reverse(),"(.)") do
f = f + (i:byte()-96) * 26 ^ ex
ex = ex + 1
end
return f
end
for x1,y1,x2,y2 in r:gmatch("(%a*)(%d*):*(%a*)(%d*)") do
return getNumber(x1),tonumber(y1),getNumber(x2),tonumber(y2)
--print(string.format("%s, %s, %s, %s",getNumber(x1),y1,getNumber(x2),y2))
end
end

function table_slice (values,i1,i2)
-- Function from http://snippets.luacode.org/snippets/Table_Slice_116
local res = {}
local n = #values
-- default values for range
i1 = i1 or 1
i2 = i2 or n
if i2 < 0 then
i2 = n + i2 + 1
elseif i2 > n then
i2 = n
end
if i1 < 1 or i1 > n then
return {}
end
local k = 1
for i = i1,i2 do
res[k] = values[i]
k = k + 1
end
return res
end


Function odsfile.load(filename) returns odsfile object, with loadContent() method, which returns lua table representing content.xml file. We can select sheet from spreadsheet with odsfile.getTable(xmlobject,sheet_name). If we omit sheet_name, first sheet from spreadsheet is selected.

Data from sheet can be read with odsfile.tableValues(sheet, x1, y1, x2, y2). x1 - y2 are range to be selected, they can be null, in which case whole rows and cells are selected. For converting of standard range expressions of form a1:b2 to this representation, function odsfile.getRange(range) can be used.

Now some LaTeX interface. odsfile.sty

\ProvidesPackage{odsfile.sty}
\RequirePackage{luacode,xkeyval}

%keyval keys

range = {x1,y1,x2,y2}%
}}

\begin{luacode*}
odsfile   = nil
sheetname     = nil
range     = {nil,nil,nil,nil}
\end{luacode*}

\luaexec{%
odsfile     = "\luatexluaescapestring{#2}"%
}%
}

\luaexec{range = {nil,nil,nil,nil}}%
\luaexec{%
local rowValues = function(row)
local t={}
for _,column in pairs(row) do table.insert(t,column.value) end
return t
end
for i,row in pairs(values) do
tex.sprint(table.concat(rowValues(row)," & ").."\\\\")
end
}
}
\endinput


In this version, you have to specify table columns manually, but it shouldn't be difficult to analyse for example contents of first row and generate column specification from this.

Example usage:

\documentclass{article}
\usepackage{odsfile,lmodern}
\begin{document}

\pagestyle{empty}

Inserting range from sheet

\begin{tabular}{l l}
\end{tabular}

Including of whole sheet

\begin{tabular}{l l l}
\end{tabular}

Inserting second sheet

\begin{tabular}{l}
\end{tabular}

\end{document}


-
Absolutely fantastic! :) – Paulo Cereda Jul 20 '12 at 0:36
@michal.h21 I just tried in vain, I can not give you instead of one point, let's say, 20. But your answer is really an innovation. – Keks Dose Jul 20 '12 at 21:02
@KeksDose thank you :) although LuaXML is simple library with some bugs, it is sufficient in many cases – michal.h21 Jul 20 '12 at 21:15
@Kurt I am doing some modifications and have to write some documentation, then I will probably post this as package to CTAN – michal.h21 Jul 21 '12 at 21:24
@Kurt there is now LaTeX package for download, see edit. – michal.h21 Jul 23 '12 at 20:42

You can probably do this by passing out a pipe of commands via \write18 to unzip the document.xml to stdin and into lxprintf (part of the LTxml suite from http://www.ltg.ed.ac.uk/software/ltxml/ which lets you extract content from XML) and then munge the result via awk or whatever. Or just use Perl/Tcl/Python.

-
Sounds interesting. Could you provide some more details? – student Jul 18 '12 at 16:15