summaryrefslogtreecommitdiff
path: root/macros/luatex/latex/odsfile/odsfile.tex
blob: cf2d141f6a78b34c6437f27955ccd2207a008462 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
\documentclass{ltxdoc}
\usepackage[utf8]{luainputenc}
\usepackage[T1]{fontenc}
\usepackage{odsfile,tgschola,metalogo,hyperref,xspace,microtype,showexpl,booktabs,url}
\author{Michal Hoftich (\url{michal.h21@gmail.com})}
\title{The \textsf{odsfile} package:\\
accessing of the \textsf{opendocument spreadsheet} from \LaTeX{}
documents\thanks{Version 0.6, last revisited 2015-09-20.}
}
\usepackage[english]{babel}
\lstloadlanguages{[LaTeX]Tex} 
\lstset{% 
    basicstyle=\ttfamily, 
    commentstyle=\itshape\ttfamily, 
    showspaces=false, 
    showstringspaces=false, 
    breaklines=true, 
    breakautoindent=true, 
    captionpos=t 
} 

\newcommand\ods{\textsf{ods}\xspace}

\begin{document}
\maketitle

\tableofcontents

\section{Introduction}

This is \LuaLaTeX{} package and \textsf{lua} library for working with 
\textsf{opendocument spreadsheet} (\ods) documents from Open/Libre Office Calc. 
Their contents can be read as \LaTeX{} tables, can be pass to macros,
 you can also add new data to existing spreadsheets.

\ods format consist of number of |xml| files packed in the |zip| file. 
This package uses \LuaTeX's zip library, LuaXML library\footnote{Pure |lua| library 
for working with |xml| files, it is available form CTAN or \url{https://github.com/michal-h21/LuaXML}} and lua scripting to read |xml| content from this archive, 
which means that it is not possible to use this package with pdf\LaTeX{} or \XeLaTeX. 
On the other side, |odsfile.lua| library can be used from Plain\TeX, 
Con\TeX{}t or pure |lua| scripts.   

Creation of this package was motivated by question\footnote{\url{http://tex.stackexchange.com/questions/60378/insert-libreoffice-table-as-input}} on site \url{http://tex.stackexchange.com/}. Development version of the package can be found at \url{https://github.com/michal-h21/odsfile}, all contributions and comments are welcome. 
\section{Usage}

You can load |odsfile| classically with
\begin{verbatim}
\usepackage{odsfile}
\end{verbatim}
There are macros:
\begin{itemize}
\item \cmd{\includespread}
\item \cmd{\tabletemplate}
\item \cmd{\loadodsfile}
\item \cmd{\savespreadsheet}
\item |AddRow| environment 
\end{itemize}

\noindent Main command is\marginpar{\cmd{\includespread}} \cmd{\includespread}. It's syntax is:\\ 
\cmd{\includespread}\oarg{key-value list}

Options are:

\begin{description}
\item[file] Filename of file to be loaded. You should specify this only on first use of \cmd{\includespread}.
\item[sheet] Name of sheet to be loaded. If it's not specified on first use of
  \cmd{\includespread}, then first sheet from the file is loaded. The sheet
  remains selected until another use of |sheet|.

\begin{LTXexample}
\begin{tabular}{l l}
\includespread[file=pokus.ods,sheet=List2]
\end{tabular}     
\end{LTXexample}

\item[range] Selects range from table to be inserted. Range is specified in
  format similar to spreadsheet processors, like |a2:c4|, selecting cells
  starting at first column, second row and ending and third column, fourth row.
  Other variant of supported ranges are \textit{named ranges}, which can be
  saved in the |ods file|.

\begin{LTXexample}
\begin{tabular}{lll}
\includespread[sheet=List1,range=a2:c4]
\end{tabular}     
\end{LTXexample}
You can omit some or both of the numbers:
\begin{LTXexample}
\begin{tabular}{lll}
\includespread[range=a:c4]
\end{tabular}     
\end{LTXexample}

\begin{LTXexample}
\begin{tabular}{ll}
\includespread[range=a:b]
\end{tabular}     
\end{LTXexample}
  
\begin{LTXexample}
\begin{tabular}{ll}
\includespread[range=b2:c]
\end{tabular}     
\end{LTXexample}  
\item[columns] Column heading specification. It can be either |head|, |top|, or comma separated list of values.
\begin{description}
\item[top] Use as headers first line from the table.
\begin{LTXexample}
\begin{tabular}{ll}
\includespread[range=b3:c5,columns=top]
\end{tabular}
\end{LTXexample}
Note that if you include whole table, first line is included twice:
\begin{LTXexample}
\begin{tabular}{lll}
\includespread[columns=top]
\end{tabular}     
\end{LTXexample}
in this case you can use
\item[head] use first row from selection as headings.
\begin{LTXexample}
\begin{tabular}{lll}
\includespread[columns=head,range=a:c3]
\end{tabular}     
\end{LTXexample}
\item[manually specified list] If column headings are not specified in the file, you can set them manually.
\begin{LTXexample}
\begin{tabular}{ll}
\includespread[columns={title,amount},sheet=List2]
\end{tabular}     
\end{LTXexample}
\end{description}
\item[columnbreak] Command inserted in manual linebreaks in cells. Default value is |\linebreak|

\item[rowseparator] Rows are normally separated with newlines, if you really want, you can separate them with hlines. 

Possible values:
\begin{description}
\item[tableline (default)] Inserts |\\| character
\item[hline] Inserts |\\ \\hline| 
\item[newline] Inserts blank line
\item[user specified separator] useful in conjunction with \ref{it:rowtemplate} 
(p. \pageref{it:rowtemplate}), for example if you want to include sheets 
as plaintext or input for plotting functions.  
\end{description} 
\begin{LTXexample}
\begin{tabular}{lll}
\includespread[columns=top,sheet=List1,rowseparator=hline,range=a2:b5]
\end{tabular}     
\end{LTXexample} 

\item[template] Templates are simple mechanism to insert whole tabular environment with column specification. All columns are aligned to the left, if you want to do more advanced stuff with column specifications, you must enter them manually as in all previous examples.
\begin{LTXexample}
\includespread[columns=top,template=booktabs,range=a3]
\end{LTXexample}
For more info about templates, see next section \ref{sec:tpl}

\item[coltypes] When using template, column types are inferred automatically. If that doesn't work well in your case, you can specify them manually with |coltypes| option.

  \begin{LTXexample}
    \includespread[columns=head, template=booktabs, coltypes=lrr] 
  \end{LTXexample}

\item[rowtemplate] 
\makeatletter
\edef\@currentlabel{rowtemplate}
\label{it:rowtemplate} Enables to convert tabular data to something different than \LaTeX\ tables. 
Syntax for rowtemplates is similar to the table templates, variables are inserted with |-{number}|, where 
number is the position of the cell from beginning of the selection. 
\makeatother
\begin{LTXexample}
\includespread[range=a2:b,rowseparator={,\ },rowtemplate={\textit{-{1}}:-{2}}]
\end{LTXexample}

\item[multicoltemplate] 
supports merged cells. Default template uses left aligned \verb|\multicolumn|
command
\begin{LTXexample}
  \begin{tabular}{lll}
  \includespread[sheet=Sheet3, multicoltemplate={\multicolumn{-{count}}{r}{-{value}}}]
  \end{tabular}
\end{LTXexample}

\end{description} 


\section{Templates}\label{sec:tpl}

If you don't want to specify tabular environment by hand, you can use simple templating mechanism to insert tabular environment by hand. 

Templates are defined with macro\marginpar{\cmd{\tabletemplate}}\\ 
\cmd{\tabletemplate}\marg{template name}\marg{template code}\\
there is default template:
\begin{verbatim}
\tabletemplate{default}{-{colheading}-{rowsep}-{content}}
\end{verbatim}

Code |-{variable name}| inserts one of the following variables:

\begin{description}
\item[coltypes] This is code to be inserted in |\begin{tabular}{coltypes}|. %In current version, it inserts |l| for left alignment column, for all columns of inserted table. 
%It should be possible to use more intelligent method based on types of column content, or \ods styles, maybe in future versions some of them will be used. If you want other alignment of columns now, you have to specify |\begin{tabular}{column types}| manually.
The |p| column specifier is used for each column, where cell with manual line break occurs, |l| is used otherwise.

\item[colheading] Column headings.
\item[rowsep] It inserts row separator defined with |rowsepartor| key of |\includespread|. It is used in default style to delimit column headings and table contents.   
\item[content] Tabular data.
\end{description} 

\paragraph{More powerful template for the \textsc{booktabs} package}

\begin{verbatim}
\tabletemplate{booktabs}{%
\begin{tabular}{-{coltypes}}
\toprule
-{colheading}
\midrule
-{content}
\\ \bottomrule
\end{tabular}
} 
\end{verbatim}

\section{Adding data}

There is simple interface for adding new rows to the spreadsheet.

|AddRow|\oarg{row number}\marginpar{|AddRow|} environment for adding new row to the current sheet. Optional argument \oarg{row number} specifies where it should be inserted, if blank, it will be inserted at end.

Inside |AddRow|, you can use 
\begin{itemize}
	\item \cmd{\AddString}\marg{text}\marg{position} 
	\item \cmd{\AddNumber}\marg{number}\marg{position}
\end{itemize} 
Position specifies cell, where data should be added, if you leave it blank, it will be laced next to the previous one.

\begin{LTXexample}
\includespread[columns=head,template=booktabs]
\begin{AddRow}
  \AddString{last row}{}
\end{AddRow} 
\begin{AddRow}[3]
  \AddString{third row}{}
  \AddNumber{22}{2}
\end{AddRow} 
\includespread[columns=head,template=booktabs]
\end{LTXexample}

\section{Loading and saving of the \ods file}

You can explicitly load \ods file with \marginpar{\cmd{\loadodsfile}}\cmd{\loadodsfile}\oarg{key val list}\marg{filename}. This can be useful, if you only want to write some data to the file, otherwise it is better to use \cmd{\includespread}.

For saving spreadsheets modified with |AddRow|, you can use \cmd{\savespreadsheet}\marginpar{\cmd{\savespreadsheet}}. This command uses call to external |zip| utility, so you should have installed it and you have to call lua\LaTeX with |lualatex --shell-escape filename|. Lua\LaTeX also must have write permissions for accessing the \ods file. This command creates file |content.xml| in the current directory, so if externall call fails, you can run
\begin{verbatim}
zip -r filename.ods content.xml
\end{verbatim} 
by hand.
\section{Lua library}


The |lua| library uses |luazip| library integrated to \LuaTeX{} and |LuaXML|\footnote{\url{https://github.com/michal-h21/LuaXML}}, pure |lua| library for working with |XML| files.

To use library in your code, you can use:

\begin{verbatim}
require("odsfile")
\end{verbatim} 

Function |odsfile.load(filename)| returns |odsfile| object, with |loadContent()| method, which returns |lua| table representing |content.xml| file. We can select sheet from the 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 |nil|, 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.

\paragraph{Example usage -- file \textsf{odsexample.lua}}

\begin{verbatim}
require "odsfile"

-- Helper function to print structure of the table
function printable(tb, level)
  level = level or 1
  local spaces = string.rep(' ', level*2)
  for k,v in pairs(tb) do
      if type(v) ~= "table" then
         print(spaces .. k..'='..v)
      else
         print(spaces .. k)
         level = level + 1
         printable(v, level)
      end
  end
end

local ods = odsfile.load("filename.ods")
local f, e = ods:loadContent()

-- Get First sheet from the table
body= odsfile.getTable(f)
-- Print structure of the range a4:b5  
printable(odsfile.tableValues(body,odsfile.getRange("a4:b5")))
\end{verbatim}

Run the example with |texlua odsexample.lua| from the command line, you should get following result:

\begin{verbatim}
  1
    1
      value=AA
      attr
        office:value-type=string
    2
        value=3
        attr
          office:value-type=float
          office:value=3
  2
      1
        value=BB
        attr
          office:value-type=string
      2
          value=4
          attr
            office:value-type=float
            office:value=4
\end{verbatim}

To convert this structure to \LaTeX{} tabular code, you can use following function:

\begin{verbatim}
function tableToTabular(values)
  local rowValues = function(row)
    local t={} 
    for _,column in pairs(row) do table.insert(t,column.value) end
    return t
  end
  content = {}   
  for i,row in pairs(values) do
    table.insert(content,table.concat(rowValues(row)," & "))
  end
  return table.concat(content,"\\\\\n")
end
-- Now use it with objects from previous example
print(tableToTabular(odsfile.tableValues(body)))
\end{verbatim}  

This example yields

\begin{verbatim}
Label & Position & Count\\
Hello & 1 & 3\\
World & 2 & 4\\
AA & 3 & 5\\
BB & 4 & 6\\
CC & 5 & 7
\end{verbatim}



\section{Changes}

\begin{description}

\item[v0.6]
  \begin{itemize}
    \item  Fixed bug in handling of rows with only one cell\footnote{Thanks to Ulrike Fisher}
    \item  Added support for named ranges
    \item  Fixed text:s handling. Beware that using multiple formatting styles
      in one cell may result in discarding whole cell, or wrong order. This is
      caused by underlying LuaXML library and it is likely to remain.
\end{itemize}
\item[v0.5]
\begin{itemize}
  \item Fixed bug: cell attributes weren't saved
  \item Added support for merged cells
  \item Added \texttt{multicoltemplate} option
\end{itemize}

\item[v0.4]
\begin{itemize}
	\item Fixed bugs in loading sheets without ranges
	\item Fixed bugs in behaviour of empty cells\footnote{Thanks to \href{https://github.com/TripleWhy}{TrippleWhy}}
  \item Fixed bug in row counting\footnote{Thanks to \href{https://github.com/yamsu}{yamsu}}
	\item Added support for children element in column paragraphs
	\item Added cell value escaping
\end{itemize}
\item[v0.3]
\begin{itemize}
  \item Added support for multiline cells
  \item Improved automatic column types generation
  \item Added new options, |coltypes| and |columnbreak|
\end{itemize}
\item[v0.2] 
\begin{itemize}
 \item LuaXML is now distributed as separate library, so other projects can use it.
 \item New |AddRow| environment for adding data to the \ods file
 \item New command |\savespreadsheet| for saving \ods file
 \item Bug fixes: corrected loading of the sheets, corrected behaviour of blank cell 
\end{itemize}
\item[v0.1] First version
\end{description}



\end{document}