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>
<office:spreadsheet>
<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:spreadsheet>
</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")
function load(filename)
local p = {
file = zip.open(filename),
content_file_name = "content.xml",
loadContent = function(self,filename)
local treehandler = simpleTreeHandler()
local filename = filename or self.content_file_name
local xmlfile = self.file:open(filename)
local text = xmlfile:read("*a")
local xml = xmlParser(treehandler)
xml:parse(text)
return treehandler
end
}
return p
end
function getTable(x,table_name)
local tables = x.root["office:document-content"]["office:body"]["office:spreadsheet"]["table:table"]
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
\define@key{includespread}{file}{\loadodsfile{#1}}
\define@key{includespread}{sheet}{\luaexec{sheetname = "\luatexluaescapestring{#1}"}}
\define@key{includespread}{range}{\luaexec{%
local x1,y1,x2,y2 = odsreader.getRange("\luatexluaescapestring{#1}")%
range = {x1,y1,x2,y2}%
}}
\begin{luacode*}
odsreader = require("odsfile")
odsfile = nil
sheetname = nil
range = {nil,nil,nil,nil}
\end{luacode*}
\newcommand\loadodsfile[2][]{%
\setkeys{includespread}{#1}%
\luaexec{%
odsfile = "\luatexluaescapestring{#2}"%
local ods = odsreader.load(odsfile)%
odsfile, e = ods:loadContent()%
}%
}
\newcommand\includespread[1]{%
\luaexec{range = {nil,nil,nil,nil}}%
\setkeys{includespread}{#1}%
\luaexec{%
local body = odsreader.getTable(odsfile,sheetname)
local values =odsreader.tableValues(body,range[1],range[2],range[3],range[4])
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}
\includespread{file=pokus.ods,sheet=List1,range=a2:b4}
\end{tabular}
Including of whole sheet
\begin{tabular}{l l l}
\includespread{}
\end{tabular}
Inserting second sheet
\begin{tabular}{l}
\includespread{file=pokus.ods,sheet=List2}
\end{tabular}
\end{document}

.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.