19

After reading this amazing answer, I'm wondering if it possible to do the same thing but the other way round, that is create a LibreOffice spreadsheet from luatex, exporting data from the tex file to the spreadsheet.

The example I have in mind is: say I'm typesetting the solution of an exam with a documentclass that defines a macro \answer to be used in the following context.

\documentclass{myclass}

\begin{document}

\section{Test 1}

\begin{enumerate}
\item The \answer{definition of entropy} states that \answer{$S = k_B
    \ln \Omega$}. 
\end{enumerate}

\end{document}

I grade my students with a LibreOffice spreadsheet. So I'd like luatex to create (or modify) a spreasheet so I can have automatically something like in the screenshot below.

screenshot of spreadsheet

I understand that a lua interface to LibreOffice does not exist. So I see three ways here:

  1. Creating a zip file from scratch meeting the .ods standard.
  2. Using some magic to interface LibreOffice API with luatex (with LuaJava?).
  3. Using some perl or other kind of magic to do the job.

As @canaaerus suggested in comments, I could export things from tex with a csv file but I'd like to have a real .ods out of the box so that I could have style information (like bold face, centered text, etc.) and formulas.

Community
  • 1
cjorssen
  • 10,032
  • 4
  • 36
  • 126
  • 1
    Maybe you could use a csv files as data source for both your TeX document and your spreadsheet. The datatool package could help. – bodo Jul 24 '12 at 14:19
  • @canaaerus Well I was hoping to embed style information and possibly formulas. So csv is an idea but that will not fit fully my needs. – cjorssen Jul 24 '12 at 14:28
  • On second thought, you could also write to a csv file from TeX and then open it with libreoffice. – bodo Jul 24 '12 at 14:38
  • @canaaerus Yes, that was how I understood your first comment and it is indeed better than what I'm doing now (retyping all the answers in the spreadsheet...). But it is still not enough to store style information (bold, centered, etc.) or formulas. – cjorssen Jul 24 '12 at 14:43
  • Ok. But then you should maybe update your example. – bodo Jul 24 '12 at 19:04
  • The experimental Latex 3 data tables package l3dt will go a long way toward providing a data structure the Lua code can use as input. – J. C. Salomon Jul 26 '12 at 16:46
  • What I meant with my comment above is, that your example does not show how you want the style information and formulas to be handled. – bodo Jul 26 '12 at 19:30
  • lua zip library is unfortunately only for reading zip files. but one can use zip utility to modify contents of archive. My idea is to use some ods file as template, where you can fill some columns with sample data and apply styles, then load this file with odsfile to lua table, use these template columns to insert new ones with style attributes copied. Then this lua table can be serialized back to the xml, template ods file copied to the new one and then replace content.xml in new ods file with our serialized table. – michal.h21 Jul 27 '12 at 13:51

2 Answers2

19

Edit 14-08-2012 New version of odsfile incorporates ideas from this answer. It is now possible to use following code:

\documentclass{article}
\usepackage{lmodern,odsfile,booktabs}
\begin{document}
\loadodsfile{template.ods}

\begin{AddRow}
\AddString{Hello world}{}
\AddNumber{46}{3}
\end{AddRow}

\includespread[template=booktabs,columns=head]
\savespreadsheet
\end{document}

Package is now included in TeX Live and xml library is distributed separately http://ctan.org/pkg/luaxml, so now it can be used by other packages.

This is my solution using lualatex and development version of odsfile. First of all, I created serialization function from LuaXML tables to xml.

Code

xml = require("xml-mod")
local t = {html={head = {title = "Hello world"}}} -- Table in format as created with LuaXML parser
print(xml.serialize(t))

outputs

<?xml version="1.0" encoding="UTF-8"?>
<html>
  <head>
    <title>Hello world</title>
  </head>
</html>

Then there is small library for adding contents to ods file, exams.lua

module(...,package.seeall)
require("odsfile")
xml = require("xml-mod")


function updateZip(zipfile, updatefile)
  local command  =  string.format("zip %s %s",zipfile, updatefile)
  print ("Updating an ods file.\n" ..command .."\n Return code: ", os.execute(command))  
end

-- Object for adding new rows 

function newRow()
  local p = {
    pos = 0,
    cells = {},
    -- Generic function for inserting cell
    addCell = function(self,val, attr,pos)
      if pos then
        table.insert(self.cells,pos,{["text:p"] = val, ["_attr"] = attr})
        self.pos = pos
      else
        self.pos = self.pos + 1
        table.insert(self.cells,self.pos,{["text:p"] = val, ["_attr"] = attr})
      end
    end, 
    addString = function(self,s,attr,pos)
      local attr = attr or {}
      attr["office:value-type"] = "string"
      self:addCell(s,attr,pos)
    end,
    addFloat = function(self,i,attr,pos)
      local attr = attr or {}
      local s = tonumber(i) or 0
      s = tostring(s)
      attr["office:value-type"] = "float"
      attr["office:value"] = s
      self:addCell(s,attr,pos)
    end, 
    findLastRow = function(self,sheet)
      for i= #sheet["table:table-row"],1,-1 do
        if sheet["table:table-row"][i]["_attr"]["table:number-rows-repeated"] then
          return i
        end
      end
    end,
    insert = function(self, sheet, pos)
      local t = {}
      local pos = pos or self:findLastRow(sheet)
      for i=1, #sheet["table:table-column"] do
        table.insert(t,self.cells[i] or {})  
      end
      t = {["table:table-cell"]=t}
      table.insert(sheet["table:table-row"],pos,t)
    end
  }
  return p
end

It has one function and one object. updateZip(zipfile, updatefile) tries to run zip utility, which have to be installed in your system. Unfortunately, I have some problems with access rights on my computer, so it doesn't work here. Instead, I just run this command from commandline using generated xml and it works just fine.

newRow() returns object with some methods:

  • addCell(val,attr,pos)
  • addString(val,attr,pos)
  • addNumber(val,attr,pos) these methods add columns into row. addCell is generic one and should not be used. attr is array with attributes, you can leave it empty. pos means position of the column in the row, it can also be nil
  • insert(sheet, pos) this function insert row to the sheet, you can specify row position with pos

Now some simple LaTeX package, exams.sty

\ProvidesPackage{exams}
\RequirePackage{luacode}
\begin{luacode}
exams = require("exams")
require("odsfile")
xml = require("xml-mod")
--exams.test()
row      = {}
sheet    = {}
ods      = {}
filename = ""
\end{luacode}

\newcommand\examspreadsheet[2]{%
 \luaexec{%
  sh = "\luatexluaescapestring{#2}"
  if sh == "" then sh = nil end
  filename = "\luatexluaescapestring{#1}"
  local f   = odsfile.load(filename)
  ods = f:loadContent()
  sheet = odsfile.getTable(ods,sh)
 }
}

\newcommand\savespreadsheet{%
\luaexec{exams.updateZip(filename,"content.xml")}
}
\newcommand\answer[1]{%
\begingroup%
\AddRow%
\AddNumber{\theenumi}{}%
\AddString{#1}{}%
\endAddRow%
\endgroup%
#1%
}
\newenvironment{exam}[1]{%
\section{#1}
\AddRow
\AddString{#1}{}
\endAddRow
\enumerate%
}{\endenumerate%
 \luaexec{%
   f = io.open("content.xml","w")
   f:write(xml.serialize(ods.root))
 }
}

\newenvironment{AddRow}[1][]{%
\def\AddString##1##2{% 
\luaexec{%
local pos = "\luatexluaescapestring{##2}"%
if pos == "" then pos = nil end; row:addString("\luatexluaescapestring{\unexpanded{##1}}",nil,pos)%
}%
}%
\def\AddNumber##1##2{%
\luaexec{%
local pos = "\luatexluaescapestring{##2}"%
if pos == "" then pos = nil end; row:addFloat("\luatexluaescapestring{##1}",nil,pos)%
}%
}%
\luaexec{%
pos = "\luatexluaescapestring{#1}"%
if pos == "" then pos = nil end; row = exams.newRow()%
}
}{%
\luaexec{%
row:insert(sheet,pos)%
}
}

This package defines three user commands and one environment:

  • \examspreadsheet{odsfile}{sheet} loads sheet from file. you can left sheet blank, then first sheet will be selected
  • \answer{text} add row to the spreadsheet
  • \savespreadsheet saves sheet to the ods file
  • environment exam all answers should be inside it, at end, it saves file content.xml in the current directory.

Note: there is some spurious space in definition of \answer, that I cannot find.

Now some sample file:

\documentclass{article}
\usepackage{lmodern,exams}
\begin{document}
\examspreadsheet{template.ods}{}

\begin{exam}{Test1}
\item Bla bla \answer{Test of special characters < > \& "}
\item The \answer{definition of entropy} states that \answer{$S = k_B
    \ln \Omega$}. 
\end{exam}
\savespreadsheet
\end{document}

Compile with 

lualatex --shell-escape  sample.tex

If you get error message in the console output like:

zip I/O error: Permission denied

zip error: Could not create output file (was replacing the original zip file)
Updating an ods file.
zip template.ods content.xml
 Return code:   15

then you have to change file permissions of the ods file to enable writing for other users, or simply run command

zip -r template.ods content.xml

And the result in openoffice calc:

enter image description here

doncherry
  • 54,637
michal.h21
  • 50,697
10

This is a long comment rather than an answer.

In ConTeXt you can generate XML corresponding to the document structure. For example, the ConTeXt document corresponding to your LaTeX example is:

\setupbackend[export=yes]
\definehighlight[answer] %[style=..., color=...]

\starttext
\startsection[title={Test 1}]
  \startitemize[n]
    \item The \answer{definition of entropy} states that \answer{$S=k_B ≤ Ω$}
  \stopitemize
\stopsection
\stoptext

The important part here is the \setupbackend command on the top. I do not know how you want to visually display the output of the \answer macro, so I left it blank.

Compiling this file generates the following \jobname.export file:

<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>

<!-- input filename   : test              -->
<!-- processing date  : Thu Jul 26 14:21:19 2012 -->
<!-- context version  : 2012.07.19 17:52  -->
<!-- exporter version : 0.30              -->


<document language="en" file="test" date="Thu Jul 26 14:21:19 2012" context="2012.07.19 17:52" version="0.30" xmlns:m="http://www.w3.org/1998/Math/MathML">
  <section detail="section" location='aut:1'>
    <sectionnumber>1</sectionnumber> 
    <sectiontitle>Test 1</sectiontitle> 
    <sectioncontent>
      <itemgroup detail="itemize" symbol="n">
        <item>
          <itemtag>1.</itemtag>
          <itemcontent>The <highlight detail="answer">definition of entropy</highlight> states that <highlight detail="answer"><m:math display="inline"><m:mrow><m:mi></m:mi><m:mo>=</m:mo><m:msub><m:mi></m:mi><m:mi></m:mi></m:msub><m:mo>≤</m:mo><m:mi>Ω</m:mi></m:mrow></m:math></highlight></itemcontent>
        </item>
      </itemgroup>
    </sectioncontent>
  </section>
</document>

This is a well formed XML containing all the information in the original document (including the \answer macro and the inline math snippet). In principle, it should be straightforward to take this XML and transform it to the format needed by Calc and zip it. (In fact, ConTeXt does something similar when generating epub output from TeX input). But I am know the XML processing tools well enough to give a complete answer.

doncherry
  • 54,637
Aditya
  • 62,301
  • Thanks for your answer. ConTeXt does once again a great job here, but I'm afraid I'll stick to LaTeX for some time more. – cjorssen Jul 27 '12 at 13:39
  • The other option is to start with an XML file. I believe there are LaTeX packages that can be used to process the XML file (ConTeXt can do this natively), otherwise you can use something from the XML toolchain to transform the input to both a valid latex and a valid ODS file. – Aditya Jul 27 '12 at 17:02