Debian Codegraph @

texmf-dist/doc/lualatex/odsfile/odsfile.tex

raw
1 \documentclass{ltxdoc}
2 \usepackage{fontspec}
3 \setmainfont{TeX Gyre Schola}
4 % \usepackage[utf8]{luainputenc}
5 % \usepackage[T1]{fontenc}
6 % \usepackage{odsfile,tgschola,metalogo,hyperref,xspace,showexpl,booktabs,url}
7 \usepackage{odsfile,metalogo,hyperref,xspace,microtype,showexpl,booktabs,url}
8 \author{Michal Hoftich (\url{[email protected]})}
9 \title{The \textsf{odsfile} package:\\
10 accessing of the \textsf{opendocument spreadsheet} from \LaTeX{}
11 documents\thanks{Version 0.10, last revisited 2025-10-31.}
12 }
13 \usepackage[english]{babel}
14 \lstloadlanguages{[LaTeX]Tex}
15 \lstset{%
16 basicstyle=\ttfamily,
17 commentstyle=\itshape\ttfamily,
18 showspaces=false,
19 showstringspaces=false,
20 breaklines=true,
21 breakautoindent=true,
22 captionpos=t
23 }
24
25 \newcommand\ods{\textsf{ods}\xspace}
26
27 \begin{document}
28 \maketitle
29
30 \tableofcontents
31
32 \section{Introduction}
33
34 This is \LuaLaTeX{} package and \textsf{lua} library for working with
35 \textsf{opendocument spreadsheet} (\ods) documents from Open/Libre Office Calc.
36 Their contents can be read as \LaTeX{} tables, can be pass to macros,
37 you can also add new data to existing spreadsheets.
38
39 The \ods format consist of number of |xml| files packed in the |zip| file.
40 This package uses \LuaTeX's zip library, LuaXML library\footnote{Pure |lua| library
41 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,
42 which means that it is not possible to use this package with pdf\LaTeX{} or \XeLaTeX.
43 On the other side, |odsfile.lua| library can be used from Plain\TeX,
44 Con\TeX{}t or pure |lua| scripts.
45
46 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.
47 \section{Usage}
48
49 You can load |odsfile| classically with
50 \begin{verbatim}
51 \usepackage{odsfile}
52 \end{verbatim}
53 There are macros:
54 \begin{itemize}
55 \item \cmd{\includespread}
56 \item \cmd{\includespread*}
57 \item \cmd{\OdsNl}
58 \item \cmd{\OdsLastNl}
59 \item \cmd{\tabletemplate}
60 \item \cmd{\loadodsfile}
61 \item \cmd{\savespreadsheet}
62 \item |AddRow| environment
63 \end{itemize}
64
65 \noindent Main command is\marginpar{\cmd{\includespread}} \cmd{\includespread}
66 and it's starred variant \cmd{\includespread*}. It's syntax is:\\
67 \cmd{\includespread}\oarg{key-value list}
68
69 Options are:
70
71 \begin{description}
72 \item[file] Filename of file to be loaded. You should specify this only on first use of \cmd{\includespread}.
73 \item[sheet] Name of sheet to be loaded. If it's not specified on first use of
74 \cmd{\includespread}, then first sheet from the file is loaded. The sheet
75 remains selected until another use of |sheet|.
76
77 \begin{LTXexample}
78 \begin{tabular}{l l}
79 \includespread[file=pokus.ods,sheet=List2]
80 \end{tabular}
81 \end{LTXexample}
82
83 \item[range] Selects range from table to be inserted. Range is specified in
84 format similar to spreadsheet processors, like |a2:c4|, selecting cells
85 starting at first column, second row and ending and third column, fourth row.
86 Other variant of supported ranges are \textit{named ranges}, which can be
87 saved in the |ods file|. You can specify multiple ranges to be included, separated
88 by comma, but in this case the range specification must be surrounded by braces: |{a1:b1,a3:b3}|.
89
90 \begin{LTXexample}
91 \begin{tabular}{lll}
92 \includespread[sheet=List1,range=a2:c4]
93 \end{tabular}
94 \end{LTXexample}
95 You can omit some or both of the numbers:
96 \begin{LTXexample}
97 \begin{tabular}{lll}
98 \includespread[range=a:c4]
99 \end{tabular}
100 \end{LTXexample}
101
102 \begin{LTXexample}
103 \begin{tabular}{ll}
104 \includespread[range=a:b]
105 \end{tabular}
106 \end{LTXexample}
107
108 \begin{LTXexample}
109 \begin{tabular}{ll}
110 \includespread[range=b2:c]
111 \end{tabular}
112 \end{LTXexample}
113
114 \begin{LTXexample}
115 \begin{tabular}{ll}
116 \includespread[range={b1:c1,b3:c}]
117 \end{tabular}
118 \end{LTXexample}
119
120 \begin{LTXexample}
121 \begin{tabular}{lll}
122 \includespread[range=newrangetest]
123 \end{tabular}
124 \end{LTXexample}
125
126 \item[columns] Column heading specification. It can be either |head|, |top|, or comma separated list of values.
127 \begin{description}
128 \item[top] Use as headers first line from the table.
129 \begin{LTXexample}
130 \begin{tabular}{ll}
131 \includespread[range=b3:c5,columns=top]
132 \end{tabular}
133 \end{LTXexample}
134 Note that if you include whole table, first line is included twice:
135 \begin{LTXexample}
136 \begin{tabular}{lll}
137 \includespread[columns=top]
138 \end{tabular}
139 \end{LTXexample}
140 in this case you can use
141 \item[head] use first row from selection as headings.
142 \begin{LTXexample}
143 \begin{tabular}{lll}
144 \includespread[columns=head,range=a:c3]
145 \end{tabular}
146 \end{LTXexample}
147 \item[manually specified list] If column headings are not specified in the file, you can set them manually.
148 \begin{LTXexample}
149 \begin{tabular}{ll}
150 \includespread[columns={title,amount},sheet=List2]
151 \end{tabular}
152 \end{LTXexample}
153 \end{description}
154 \item[columnbreak] Command inserted in manual linebreaks in cells. Default value is |\linebreak|
155
156 \item[rowseparator] Rows are normally separated with newlines, if you really want, you can separate them with hlines.
157
158 Possible values:
159 \begin{description}
160 \item[tableline (default)] Inserts |\\| character
161 \item[hline] Inserts |\\ \\hline|
162 \item[newline] Inserts blank line
163 \item[user specified separator] useful in conjunction with \ref{it:rowtemplate}
164 (p. \pageref{it:rowtemplate}), for example if you want to include sheets
165 as plaintext or input for plotting functions.
166 \end{description}
167 \begin{LTXexample}
168 \begin{tabular}{lll}
169 \includespread[columns=top,sheet=List1,rowseparator=hline,range=a2:b5]
170 \end{tabular}
171 \end{LTXexample}
172
173 \item[newline] Code that will be inserted instead of |\\| between rows. Default value is |\OdsNl|.
174 \item[lastnewline] Code inserted after the last row. Default value is |\OdsLastNl|.
175
176 \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.
177 \begin{LTXexample}
178 \includespread[columns=top,template=booktabs,range=a3]
179 \end{LTXexample}
180 For more info about templates, see next section \ref{sec:tpl}
181
182 \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.
183
184 \begin{LTXexample}
185 \includespread[columns=head, template=booktabs, coltypes=lrr]
186 \end{LTXexample}
187
188 \item[rowtemplate]
189 \makeatletter
190 \edef\@currentlabel{rowtemplate}
191 \label{it:rowtemplate} Enables to convert tabular data to something different than \LaTeX\ tables.
192 Syntax for rowtemplates is similar to the table templates, variables are inserted with |-{number}|, where
193 number is the position of the cell from beginning of the selection.
194 \makeatother
195 \begin{LTXexample}
196 \includespread[range=a2:b,rowseparator={,\ },rowtemplate={\textit{-{1}}:-{2}}]
197 \end{LTXexample}
198
199 \item[celltemplate] This template is applied to every cell in the included table.
200 \begin{LTXexample}
201 \begin{tabular}{lll}
202 \includespread[range=a1:c1,celltemplate={\textit{-{value}}}]
203 \includespread[range=a2:c]
204 \end{tabular}
205 \end{LTXexample}
206
207 \item[multicoltemplate]
208 supports merged cells. Default template uses left aligned \verb|\multicolumn|
209 command
210 \begin{LTXexample}
211 \begin{tabular}{lll}
212 \includespread[sheet=Sheet3, multicoltemplate={\multicolumn{-{count}}{r}{-{value}}}]
213 \end{tabular}
214 \end{LTXexample}
215
216 \end{description}
217
218 \subsection{Character escaping}
219
220 To prevent compilation errors, \textsf{odsfile} escapes characters that have special meaning in \LaTeX,
221 for example backslashes or dollar characters. In some cases, you may want to
222 execute the \TeX\ code that is saved in the spreadsheet. In that case, use the \texttt{escape} option:
223
224 \begin{description}
225
226 \item[escape] Set the value to false to ignore special characters in the cell data.
227 \end{description}
228
229 The following example prints the left cell in the last row in table in bold, because the original
230 spreadsheet contains the \verb|\textbf{bold}| command.
231
232 \begin{LTXexample}
233 \begin{tabular}{lll}
234 \includespread[sheet=List1,columns=head,escape=false]
235 \end{tabular}
236 \end{LTXexample}
237
238 \subsection{Saving the generated table to a file}
239
240 Use the \texttt{save} option to save the generated table to a file instead of including it directly into the document.
241
242 \begin{LTXexample}
243 There should be no table listed
244 \includespread[sheet=List1,columns=head,escape=false,save=save-test.tex,debug=true]
245 \end{LTXexample}
246
247 \subsection{Debugging}
248
249 You can print the generated table to the terminal output of \LaTeX\ using the \texttt{debug} option. Possible values are
250 \texttt{true} and \texttt{false}.
251
252
253 \section{Templates}\label{sec:tpl}
254
255 If you don't want to specify tabular environment by hand, you can use simple templating mechanism to insert tabular environment by hand.
256
257 Templates are defined with macro\marginpar{\cmd{\tabletemplate}}\\
258 \cmd{\tabletemplate}\marg{template name}\marg{template code}\\
259 there is default template:
260 \begin{verbatim}
261 \tabletemplate{default}{-{colheading}-{rowsep}-{content}}
262 \end{verbatim}
263
264 Code |-{variable name}| inserts one of the following variables:
265
266 \begin{description}
267 \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.
268 %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.
269 The |p| column specifier is used for each column, where cell with manual line break occurs, |l| is used otherwise.
270
271 \item[colheading] Column headings.
272 \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.
273 \item[content] Tabular data.
274 \end{description}
275
276 \paragraph{More powerful template for the \textsc{booktabs} package}
277
278 \begin{verbatim}
279 \tabletemplate{booktabs}{%
280 \begin{tabular}{-{coltypes}}
281 \toprule
282 -{colheading}
283 \midrule
284 -{content}
285 \bottomrule
286 \end{tabular}
287 }
288 \end{verbatim}
289
290 \section{Adding data}
291
292 There is simple interface for adding new rows to the spreadsheet.
293
294 |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.
295
296 Inside |AddRow|, you can use
297 \begin{itemize}
298 \item \cmd{\AddString}\marg{text}\marg{position}
299 \item \cmd{\AddNumber}\marg{number}\marg{position}
300 \end{itemize}
301 Position specifies cell, where data should be added, if you leave it blank, it will be laced next to the previous one.
302
303 \begin{LTXexample}
304 \includespread[columns=head,template=booktabs]
305 \begin{AddRow}
306 \AddString{last row}{}
307 \end{AddRow}
308 \begin{AddRow}[3]
309 \AddString{third row}{}
310 \AddNumber{22}{2}
311 \end{AddRow}
312 \includespread[columns=head,template=booktabs]
313 \end{LTXexample}
314
315 \section{Loading and saving of the \ods file}
316
317 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}.
318
319 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
320 \begin{verbatim}
321 zip -r filename.ods content.xml
322 \end{verbatim}
323 by hand.
324 \section{Lua library}
325
326
327 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.
328
329 To use library in your code, you can use:
330
331 \begin{verbatim}
332 require("odsfile")
333 \end{verbatim}
334
335 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.
336
337 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.
338
339 \paragraph{Example usage -- file \textsf{odsexample.lua}}
340
341 \begin{verbatim}
342 require "odsfile"
343
344 -- Helper function to print structure of the table
345 function printable(tb, level)
346 level = level or 1
347 local spaces = string.rep(' ', level*2)
348 for k,v in pairs(tb) do
349 if type(v) ~= "table" then
350 print(spaces .. k..'='..v)
351 else
352 print(spaces .. k)
353 level = level + 1
354 printable(v, level)
355 end
356 end
357 end
358
359 local ods = odsfile.load("filename.ods")
360 local f, e = ods:loadContent()
361
362 -- Get First sheet from the table
363 body= odsfile.getTable(f)
364 -- Print structure of the range a4:b5
365 printable(odsfile.tableValues(body,odsfile.getRange("a4:b5")))
366 \end{verbatim}
367
368 Run the example with |texlua odsexample.lua| from the command line, you should get following result:
369
370 \begin{verbatim}
371 1
372 1
373 value=AA
374 attr
375 office:value-type=string
376 2
377 value=3
378 attr
379 office:value-type=float
380 office:value=3
381 2
382 1
383 value=BB
384 attr
385 office:value-type=string
386 2
387 value=4
388 attr
389 office:value-type=float
390 office:value=4
391 \end{verbatim}
392
393 To convert this structure to \LaTeX{} tabular code, you can use following function:
394
395 \begin{verbatim}
396 function tableToTabular(values)
397 local rowValues = function(row)
398 local t={}
399 for _,column in pairs(row) do table.insert(t,column.value) end
400 return t
401 end
402 content = {}
403 for i,row in pairs(values) do
404 table.insert(content,table.concat(rowValues(row)," & "))
405 end
406 return table.concat(content,"\\\\\n")
407 end
408 -- Now use it with objects from previous example
409 print(tableToTabular(odsfile.tableValues(body)))
410 \end{verbatim}
411
412 This example yields
413
414 \begin{verbatim}
415 Label & Position & Count\\
416 Hello & 1 & 3\\
417 World & 2 & 4\\
418 AA & 3 & 5\\
419 BB & 4 & 6\\
420 CC & 5 & 7
421 \end{verbatim}
422
423
424
425 \section{Changes}
426
427 \begin{description}
428 \item [v0.10]
429 \begin{itemize}
430 \item define Lua functions locally, to prevent pollution of global namespace\footnote{Thanks to Udi Fogiel}
431 \end{itemize}
432 \item [v0.9]
433 \begin{itemize}
434 \item added the \texttt{save} and \texttt{debug} options
435 \item added support for multiple ranges in the \texttt{range} option
436 \item added \texttt{newline} and \texttt{lastnewline} options
437 \end{itemize}
438 \item[v0.8]
439 \begin{itemize}
440 \item added the escape option for enabling or disabling of the character escaping
441 \item fixed handling of merged cells in ranges
442 \item fixed escaping of backslashes
443 \item fixed row insertion
444 \end{itemize}
445 \item[v0.7]
446 \begin{itemize}
447 \item Fixed character escaping. Unescaped ``\%,\#,\$, \_ and \&'' characters will be escaped to prevent compilation errors.
448 Characters that are already escaped in the ODS file will be left untouched.\footnote{Thanks to Wybo Dekker}
449 \item Added support for LuaTeX -recorder command line option
450 \item Added \texttt{celltemplate} option
451 \item Fixed named range handling\footnote{Thanks to Angelforest}
452 \end{itemize}
453
454 \item[v0.6]
455 \begin{itemize}
456 \item Fixed bug in handling of rows with only one cell\footnote{Thanks to Ulrike Fisher}
457 \item Added support for named ranges
458 \item Fixed text:s handling. Beware that using multiple formatting styles
459 in one cell may result in discarding whole cell, or wrong order. This is
460 caused by underlying LuaXML library and it is likely to remain.
461 \end{itemize}
462 \item[v0.5]
463 \begin{itemize}
464 \item Fixed bug: cell attributes weren't saved
465 \item Added support for merged cells
466 \item Added \texttt{multicoltemplate} option
467 \end{itemize}
468
469 \item[v0.4]
470 \begin{itemize}
471 \item Fixed bugs in loading sheets without ranges
472 \item Fixed bugs in behaviour of empty cells\footnote{Thanks to \href{https://github.com/TripleWhy}{TrippleWhy}}
473 \item Fixed bug in row counting\footnote{Thanks to \href{https://github.com/yamsu}{yamsu}}
474 \item Added support for children element in column paragraphs
475 \item Added cell value escaping
476 \end{itemize}
477 \item[v0.3]
478 \begin{itemize}
479 \item Added support for multiline cells
480 \item Improved automatic column types generation
481 \item Added new options, |coltypes| and |columnbreak|
482 \end{itemize}
483 \item[v0.2]
484 \begin{itemize}
485 \item LuaXML is now distributed as separate library, so other projects can use it.
486 \item New |AddRow| environment for adding data to the \ods file
487 \item New command |\savespreadsheet| for saving \ods file
488 \item Bug fixes: corrected loading of the sheets, corrected behaviour of blank cell
489 \end{itemize}
490 \item[v0.1] First version
491 \end{description}
492
493
494
495 \end{document}