On Sun, May 22, 2011 at 11:02 AM, Andrew Parsloe <apars...@clear.net.nz> wrote: > A few weeks ago I wrote to the list about the missing element in the `LyX > office suite', namely spreadsheet capabilities, and generated a strongly > negative reaction. What my choice of words may have obscured was a > suggestion that LyX support the Latex spreadtab package. > > 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. > Very nice. Please consider adding this info & the module itself to [1]. In time, as the module stabilizes, you may create a bug to request inclusion in the main LyX distribution.
Regards Liviu [1] http://wiki.lyx.org/Layouts/Modules > 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 > -- Do you know how to read? http://www.alienetworks.com/srtest.cfm http://goodies.xfce.org/projects/applications/xfce4-dict#speed-reader Do you know how to write? http://garbl.home.comcast.net/~garbl/stylemanual/e.htm#e-mail
