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

Reply via email to