This package enables the construction of tables in which the values in some cells depend on values in others, and mathematical expressions can be evaluated, as in a spreadsheet. Not only arithmetic operations but trigonometric, exponential and other functions are provided. When the document is compiled the calculations are done and the calculated values appear in the pdf output. But I've found building spreadtab tables of any complexity in a text editor difficult, which is why the idea of using LyX's table building machinery -- the toolbar and right-click context menu -- was so appealing.
I've now managed to create a working module -- spreadtab.module, at the bottom of this email -- which makes spreadtab usable in LyX (in much the way biblatex is usable in LyX, although not as yet properly supported). It gives LyX a limited spreadsheet-like capability -- values are (re)calculated at every View Update and appear in the pdf or other output (but not in LyX). LyX's excellent table building machinery makes all the difference.
The spreadtab module adds two environments to a document. Once one has built a table, you envelope it in one or other of these environments -- position the cursor and select the environment from the drop-down list, that's all -- and, on clicking View or Update, the calculations are performed and a LyX table appears in the pdf output with the calculated values. Quite elaborate calculations are possible -- the spreadtab manual gives a good range of examples.
The spreadtab package requires the xstring and fp packages. The latter is the one that does the actual calculating (to 18 places of decimals).
There are some complications in using the module, arising mainly from the way LyX puts `protective clothing' on some of the characters it reads (as you can see in the View Source window). Because of this, symbols like *, ^, \, <<, >>, { and }, which are used in spreadtab, need to be entered in table cells in ERT. But the module is perfectly usable and provides a new area of functionality for LyX without adding a single byte to the LyX binary (although I hope that some LyX C++ developer might have his interest piqued and alter that).
To use the module save it to your personal LyX layout folder. On Windows Vista this is C:\Users\<your name>\AppData\Roaming\LyX2.0\layouts. LyX will need to be reconfigured to recognise the existence of the module. I've also attached to this email a 7-zipped LyX document SpreadtabWithLyX.7z with instructions for using the module and examples, although reading or at least skimming the initial sections of the spreadtab manual is advisable. (The manual is not large -- just over 20 pages.) SpreadtabWithLyX also describes a timesheet which makes use of the new capability -- you enter date, start time and finish time from which spreadtab calculates the daily hours worked, total hours worked, and how much you've earned at a given pay rate. There are also instructions for using the calculator, which is a spin-off of the new capability.
Technical note: In the module there is a big \renewcommand section of code modified from spreadtab.sty. This is because LyX's tables use \tabularnewline to end a row whereas spreadtab uses \\. I found that simply substituting \tabularnewline for the instances of \\ in the spreadtab macro solved the problem. Christian Tellechea, who is the author of spreadtab, has sent me a reworking of the macro so that the user can choose which command ends a table row. That would certainly simplify the module. However, it is also clear that in the inequality MSWord < LyX < (La)TeX he places himself well to the right, and didn't seem keen to make this option available in his projected update of spreadtab (perhaps suspecting it would only aid the LyXian heresy). In the meantime the big chunk of code is necessary.
Non-technical note: Oliver Sacks in his book `Awakenings' (1973) recounts the stories of victims of the 1920s encephalitis lethargica epidemic who were `woken' from their catatonic state by a certain drug. Writing `sL' for `spreadtab LyX' and 'do' (for 'do') suggested \sLdop@ for a macro that woke numbers waiting (in their catatonic state) to be calculated.
Andrew
#\DeclareLyXModule[spreadtab.sty,fp.sty,xstring.sty]{Spreadtab}
#DescriptionBegin
#Adds environments which convert LyX tables to spreadtab tables. These
#allow the values in some cells to depend on values in others, as in a
#spreadsheet. The tables are built in LyX using its table toolbar. Also
#provides a calculator as a custom inset. Values are calculated on clicking
#the View or Update buttons and appear in the pdf or other output file,
#not in LyX.
#DescriptionEnd
#
#2011-5-14
#Version 1.0. Assumes version 0.3c of spreadtab.sty
#Author: Andrew Parsloe <apars...@clear.net.nz>
#The author & maintainer of spreadtab.sty is
#Christian Tellechea <unbonpe...@gmail.com>
Format 35
AddToPreamble
\usepackage{spreadtab}
% substitute \tabularnewline for \\ in the spreadtab definition:
\makeatletter
\renewcommand\ST@read@tab@i{%
\advance\ST@rowcount\@ne
\ST@Ifinstr\ST@tab{\@empty\tabularnewline}
{\ST@split\ST@tab\tabularnewline\ST@current@row\ST@tab
\@namedef{endrow@\number\ST@rowcount}{\tabularnewline}
\ST@Iffirstis\ST@tab[
{\ST@between\ST@tab[]\ST@temp@a
\ST@Ifinstr\ST@temp@a,
\relax
{\ST@split\ST@tab]\ST@temp@a\ST@tab
\expandafter\ST@expadd@tomacro\csname
endrow@\number\ST@rowcount\endcsname{\ST@temp@a]}}
}%
\relax
\ST@search@hline\ST@tab
\ifx\ST@tab\@empty\let\ST@next@readrows\relax\edef\ST@total@rownumber{\number\ST@rowcount}\else\let\ST@next@readrows\ST@read@tab@i\fi}%
{\let\ST@current@row\ST@tab
\let\ST@next@readrows\relax
\edef\ST@total@rownumber{\number\ST@rowcount}}
\ST@Ifinstr\ST@current@row{\@empty\SThiderow}
{\edef\ST@row@skiplist{(\number\ST@rowcount)\ST@row@skiplist}
\StrDel\ST@current@row{\@empty\SThiderow}[\ST@current@row]%
}%
\relax
\ST@colcount\z@
\let\ST@multicol@number\@empty
\let\ST@nextcell\@empty
\ST@read@cells
\ST@next@readrows
}
\makeatother
%
\renewcommand{\STtextcell}{`}
EndPreamble
Style sLTable
Category Tables
LatexType Environment
LatexName sLTable
OptionalArgs 1
NextNoIndent 0
TopSep 0.5
BottomSep 0.5
Align Center
AlignPossible Block, Left, Right, Center
Preamble
\makeatletter
\def\sLdop@[#1]#2\begin#3#4#5\end#6{\begin{spreadtab}[#1]{{#3}{#4}}#5\end{spreadtab}}
\newenvironment{sLTable}[1][]{\par\medskip\centering%
\sLdop@[#1]}{\par\medskip}
\makeatother
EndPreamble
End
Style sLTablehcol
CopyStyle sLTable
LatexName sLTablehcol
Preamble
\makeatletter
\def\sLdop@hcol[#1]#2\begin#3#4#5\end#6{\begin{spreadtab}[#1]{{#3}{#2}}#5\end{spreadtab}}
\newenvironment{sLTablehcol}[2][]{\par\medskip\centering%
\sLdop@hcol[#1]{#2}}{\par\medskip}
\makeatother
EndPreamble
End
InsetLayout Flex:sLCalculate
LyXType custom
LatexType Command
LatexName sLdocalc
ContentAsLabel 1
Decoration Classic
Display 0
LabelString Calc
Multipar 0
PassThru 1
ResetsFont 0
Preamble
\def\sLdocalc#1{\mbox{\kern -1.2em%
\begin{spreadtab}[\STsavecell\sLlastcalc{a1}]{{tabular}{c}}#1\tabularnewline\end{spreadtab}%
\kern -.6em}%
}
EndPreamble
End
InsetLayout Flex:sLNumberFormat
LyXType custom
LatexType Command
LatexName sLdoNF
ContentAsLabel 1
Decoration Classic
Font
Color phantomtext
End
Display 0
LabelString Fmt
Multipar 0
PassThru 1
ResetsFont 0
Preamble
\def\sLdoNF#1{\sLdofmt#1}
\def\sLdofmt#1,#2{\def\sLsigfig{#1}%
\if*#2\def\sLfillzeros{*}\else\def\sLfillzeros{}\fi%
\expandafter\STautoround\sLfillzeros{\sLsigfig}}
EndPreamble
End
SpreadtabWithLyX.7z
Description: Binary data
