Insert libreoffice table as input

Question

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.

Answer 1

---

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}

Answer 2

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.