Source Browser ?

1% language=us
2
3% author    : Hans Hagen
4% copyright : ConTeXt Development Team
5% license   : Creative Commons Attribution ShareAlike 4.0 International
6% reference : pragma-ade.nl | contextgarden.net | texlive (related) distributions
7% origin    : the ConTeXt distribution
8%
9% comment   : Because this manual is distributed with TeX distributions it comes with a rather
10%             liberal license. We try to adapt these documents to upgrades in the (sub)systems
11%             that they describe. Using parts of the content otherwise can therefore conflict
12%             with existing functionality and we cannot be held responsible for that. Many of
13%             the manuals contain characteristic graphics and personal notes or examples that
14%             make no sense when used out-of-context.
15%
16% comment   : Some chapters might have been published in TugBoat, the NTG Maps, the ConTeXt
17%             Group journal or otherwise. Thanks to the editors for corrections. Also thanks
18%             to users for testing, feedback and corrections.
19
20\usemodule[spreadsheet]
21\usemodule[art-01,abr-02]
22
23\definecolor[darkred]  [r=.4]
24\definecolor[darkgreen][g=.4]
25\definecolor[darkblue] [b=.4]
26
27\definecolor[maincolor] [darkred]
28\definecolor[extracolor][darkblue]
29
30\setuptyping
31  [color=extracolor]
32
33\setuptype
34  [color=extracolor]
35
36\setuphead
37  [section]
38  [color=maincolor]
39
40\setupbodyfont
41  [10pt]
42
43\setupinteraction
44  [hidden]
45
46% \setupnumbering
47%   [alternative=doublesided]
48
49\startdocument
50  [metadata:author=Hans Hagen,
51   metadata:title=Simple Spreadsheets,
52   author=Hans Hagen,
53   affiliation=PRAGMA ADE,
54   location=Hasselt NL,
55   title=Simple Spreadsheets,
56   extra=ConTeXt MkIV,
57   support=www.contextgarden.net,
58   website=www.pragma-ade.nl]
59
60\startMPpage
61
62    StartPage;
63
64    numeric n, m ; n := 3 * 4 ; m := 4 * 4 ;
65    numeric w, h ; w := PaperWidth/n ; h := PaperHeight/m ;
66
67    for i=1 upto n :
68        for j=1 upto m :
69            fill
70                unitsquare
71                xysized (w,h)
72                shifted ((i-1)*w,(j-1)*h)
73                withcolor .5[i*red/n,j*blue/m]
74            ;
75        endfor ;
76    endfor ;
77
78    for i=1 upto n :
79        for j=1 upto m :
80            draw
81                textext("\tt" & char(64+i) & if j < 10 : "0" else : "" fi & decimal j)
82                xysized (.7*w,.7*h)
83                shifted (i*w-.5w,(m+1-j)*h-.5h)
84                withcolor .5[(n+1-i)*green/n,(m+1-j)*yellow/m]
85            ;
86        endfor ;
87    endfor ;
88
89    draw
90        textext.llft("\ssbf{\documentvariable{title}}")
91        xsized (PaperHeight-h)
92        rotated 90
93        shifted (PaperWidth-1.75w,PaperHeight-h/2)
94        withcolor white
95    ;
96
97    draw
98        textext.llft("\ssbf{\documentvariable{extra}}")
99        ysized (h/2)
100        shifted (PaperWidth-2.5w,3.75h)
101        withcolor white
102    ;
103
104
105    draw
106        textext.llft("\ssbf{\documentvariable{author}}")
107        ysized (h/2)
108        shifted (PaperWidth-2.5w,2.75h)
109        withcolor white
110    ;
111
112    draw
113        textext.llft("\ssbf \currentdate")
114        ysized (h/2)
115        shifted (PaperWidth-2.5w,1.75h)
116        withcolor white
117    ;
118
119    StopPage;
120
121\stopMPpage
122
123% \page[empty] \setuppagenumber[start=1]
124
125\startsubject[title={Contents}]
126
127\placelist[section][criterium=all,interaction=all]
128
129\stopsubject
130
131\startsection [title={Introduction}]
132
133Occasionally a question pops up on the \CONTEXT\ mailing list and answering it
134becomes a nice distraction from a boring task at hand. The spreadsheet module is
135the result of such a diversion. As with more support code in \CONTEXT, this is
136not a replacement for \quote {the real thing} but just a nice feature for simple
137cases. The module is loaded with
138
139\starttyping
140\usemodule[spreadsheet]
141\stoptyping
142
143So this is (at least currently) not one of the core functionalities but an
144add||on. Of course some useful extensions might appear in the future.
145
146\stopsection
147
148\startsection [title={Spreadsheet tables}]
149
150We can use \LUA\ in each cell, because under the hood it is all \LUA. There is
151some basic parsing applied so that we can use the usual \type {A..Z} variables to
152access cells.
153
154\startbuffer[demo]
155\startspreadsheettable[test]
156  \startrow
157    \startcell 1.1         \stopcell
158    \startcell 2.1         \stopcell
159    \startcell A[1] + B[1] \stopcell
160  \stoprow
161  \startrow
162    \startcell 2.1         \stopcell
163    \startcell 2.2         \stopcell
164    \startcell A[2] + B[2] \stopcell
165  \stoprow
166  \startrow
167    \startcell A[1] + B[1] \stopcell
168    \startcell A[2] + B[2] \stopcell
169    \startcell A[3] + B[3] \stopcell
170  \stoprow
171\stopspreadsheettable
172\stopbuffer
173
174\typebuffer[demo]
175
176The rendering is shown in \in {figure} [spreadsheet:1]. Keep in mind that in
177\LUA\ all calculations are done using floats, at least in \LUA\ versions with
178version numbers preceding 5.3.
179
180\placefigure
181  [here]
182  [spreadsheet:1]
183  {A simple spreadsheet.}
184  {\getbuffer[demo]}
185
186The last cell can also look like this:
187
188\starttyping
189\startcell
190function()
191  local s = 0
192  for i=1,2 do
193    for j=1,2 do
194      s = s + dat[i][j]
195    end
196  end
197  return s
198end
199\stopcell
200\stoptyping
201
202The content of a cell is either a number or a function. In this example
203we just loop over the (already set) cells and calculate their sum. The
204\type {dat} variable accesses the grid of cells.
205
206\starttyping
207\startcell
208function()
209  local s = 0
210  for i=1,2 do
211    for j=1,2 do
212      s = s + dat[i][j]
213    end
214  end
215  tmp.total = s
216end
217\stopcell
218\stoptyping
219
220In this variant we store the sum in the table \type {tmp} which is local to the
221current sheet. Another table is \type {fnc} where we can store functions. This
222table is shared between all sheets. There are two predefined functions:
223
224\starttyping
225sum(columnname,firstrow,lastrow)
226fmt(specification,n)
227\stoptyping
228
229The \type {sum} function works top||down in columns, and roughly looks like
230this:
231
232\starttyping
233function sum(currentcolumn,firstrow,lastrow)
234  local r = 0
235  for i = firstrow, lastrow do
236    r = r + cells[currentcolumn][i]
237  end
238  return r
239end
240\stoptyping
241
242The last two arguments are optional:
243
244\starttyping
245sum(columnname,lastrow)
246\stoptyping
247
248This is equivalent to:
249
250\starttyping
251function sum(currentcolumn,lastrow)
252  local r = 0
253  for i = 1, lastrow do
254    r = r + cells[currentcolumn][i]
255  end
256  return r
257end
258\stoptyping
259
260While:
261
262\starttyping
263sum(columnname)
264\stoptyping
265
266boils down to:
267
268\starttyping
269function sum(currentcolumn)
270  local r = 0
271  for i = 1, currentrow do
272    r = r + cells[currentcolumn][i]
273  end
274  return r
275end
276\stoptyping
277
278Empty cells or cells that have no numbers are skipped. Let's now see these
279functions in action:
280
281\startbuffer[demo]
282\startspreadsheettable[test]
283  \startrow
284    \startcell 1.1 \stopcell \startcell 2.1 \stopcell
285  \stoprow
286  \startrow
287    \startcell 2.1 \stopcell \startcell 2.2 \stopcell
288  \stoprow
289  \startrow
290    \startcell
291      function()
292        local s = 0
293        for i=1,2 do
294          for j=1,2 do
295            s = s + dat[i][j]
296          end
297        end
298        context.bold(s)
299      end
300    \stopcell
301    \startcell
302      function()
303        local s = 1
304        for i=1,2 do
305          for j=1,2 do
306            s = s * dat[i][j]
307          end
308        end
309        context.bold(fmt("@.1f",s))
310      end
311    \stopcell
312  \stoprow
313\stopspreadsheettable
314\stopbuffer
315
316\typebuffer[demo]
317
318The result is shown in \in {figure} [spreadsheet:2]. Watch the \type {fmt} call:
319we use an at sign instead of a percent to please \TEX.
320
321\placefigure
322  [here]
323  [spreadsheet:2]
324  {Cells can be (complex) functions.}
325  {\getbuffer[demo]}
326
327Keep in mind that we're typesetting and that doing complex calculations is not
328our main objective. A typical application of this module is in making bills, for
329which you can combine it with the correspondence modules. We leave that as an
330exercise for the reader and stick to a simple example.
331
332\startbuffer[demo]
333\startspreadsheettable[test]
334  \startrow
335    \startcell[align=flushleft,width=8cm] "item one" \stopcell
336    \startcell[align=flushright,width=3cm] @ "0.2f EUR" 3.50 \stopcell
337  \stoprow
338  \startrow
339    \startcell[align=flushleft] "item two" \stopcell
340    \startcell[align=flushright] @ "0.2f EUR" 8.45 \stopcell
341  \stoprow
342  \startrow
343    \startcell[align=flushleft] "tax 19\percent" \stopcell
344    \startcell[align=flushright] @ "0.2f EUR" 0.19 * (B[1]+B[2]) \stopcell
345  \stoprow
346  \startrow
347    \startcell[align=flushleft] "total 1" \stopcell
348    \startcell[align=flushright] @ "0.2f EUR" sum(B,1,3) \stopcell
349  \stoprow
350  \startrow
351    \startcell[align=flushleft] "total 2" \stopcell
352    \startcell[align=flushright] @ "0.2f EUR" B[1] + B[2] + B[3] \stopcell
353  \stoprow
354  \startrow
355    \startcell[align=flushleft] "total 3" \stopcell
356    \startcell[align=flushright] @ "0.2f EUR" sum(B) \stopcell
357  \stoprow
358\stopspreadsheettable
359\stopbuffer
360
361\typebuffer[demo]
362
363Here (and in \in {figure} [spreadsheet:8]) you see a quick and more
364readable way to format cell content. The \type {@} in the template is
365optional, but needed in cases like this:
366
367\starttyping
368@ "(@0.2f) EUR" 8.45
369\stoptyping
370
371A \type {@} is only prepended when no \type {@} is given in the template.
372
373\placefigure
374  [here]
375  [spreadsheet:8]
376  {Cells can be formatted by using \type {@} directives.}
377  {\getbuffer[demo]}
378
379In practice this table we can be less specific and let \type {\sum} behave more
380automatical. That way the coding can be simplified (see \in {figure}
381[spreadsheet:7]) and also look nicer.
382
383\startbuffer[demo]
384\startspreadsheettable[test][frame=off]
385  \startrow
386    \startcell[align=flushleft,width=8cm] "The first item" \stopcell
387    \startcell[align=flushright,width=3cm] @ "0.2f EUR" 3.50 \stopcell
388  \stoprow
389  \startrow
390    \startcell[align=flushleft] "The second item" \stopcell
391    \startcell[align=flushright] @ "0.2f EUR" 8.45 \stopcell
392  \stoprow
393  \startrow
394    \startcell[align=flushleft] "The third item" \stopcell
395    \startcell[align=flushright] @ "0.2f EUR" 5.90 \stopcell
396  \stoprow
397  \startrow[topframe=on]
398    \startcell[align=flushleft] "VAT 19\percent" \stopcell
399    \startcell[align=flushright] @ "0.2f EUR" 0.19 * sum(B) \stopcell
400  \stoprow
401  \startrow[topframe=on]
402    \startcell[align=flushleft] "\bf Grand total" \stopcell
403    \startcell[align=flushright] @ "0.2f EUR" sum(B) \stopcell
404  \stoprow
405\stopspreadsheettable
406\stopbuffer
407
408\typebuffer[demo]
409
410\placefigure
411  [here]
412  [spreadsheet:7]
413  {The \type {sum} function accumulates stepwise.}
414  {\getbuffer[demo]}
415
416There are a few more special start characters. This is demonstrated in \in
417{figure} [spreadsheet:9]. An \type {=} character is ignored. \footnote {Taco
418suggested to support this because some spreadsheet programs use that character to
419flush a value.} When we start with an \type {!}, the content is not typeset.
420Strings can be surrounded by single or double quotes and are not really
421processed.
422
423\startbuffer[demo]
424\startspreadsheettable[test][offset=1ex]
425  \startrow
426    \startcell[align=flushleft] "first" \stopcell
427    \startcell[align=flushleft] '\type{@ "[@i]" 1}' \stopcell
428    \startcell[align=flushright,width=3cm] @ "[@i]" 1 \stopcell
429  \stoprow
430  \startrow
431    \startcell[align=flushleft] "second" \stopcell
432    \startcell[align=flushleft] '\type{= 2}' \stopcell
433    \startcell[align=flushright] = 2 \stopcell
434  \stoprow
435  \startrow
436    \startcell[align=flushleft] "third" \stopcell
437    \startcell[align=flushleft] '\type{! 3}' \stopcell
438    \startcell[align=flushright] ! 3 \stopcell
439  \stoprow
440  \startrow
441    \startcell[align=flushleft] "fourth" \stopcell
442    \startcell[align=flushleft] '\type{4}' \stopcell
443    \startcell[align=flushright] 4 \stopcell
444  \stoprow
445  \startrow
446    \startcell[align=flushleft] "\bf total one" \stopcell
447    \startcell[align=flushleft] '\type{sum(C)}' \stopcell
448    \startcell[align=flushright] sum(C) \stopcell
449  \stoprow
450  \startrow
451    \startcell[align=flushleft] "\bf total two" \stopcell
452    \startcell[align=flushleft] '\type{= sum(C)}' \stopcell
453    \startcell[align=flushright] = sum(C) \stopcell
454  \stoprow
455\stopspreadsheettable
456\stopbuffer
457
458\typebuffer[demo]
459
460The \type {sum} function is clever enough not to include itself in the
461summation. Only preceding cells are taken into account, given that they
462represent a number.
463
464\placefigure
465  [here]
466  [spreadsheet:9]
467  {Cells can be hidden by \type {!} and can contain strings only.}
468  {\getbuffer[demo]}
469
470\stopsection
471
472\startsection [title={Normal tables}]
473
474In the previous examples we used \TEX\ commands for structuring the sheet but
475the content of cells is \LUA\ code. It is also possible to stick to a regular
476table and use specific commands to set and get cell data.
477
478\startbuffer[demo]
479\bTABLE[align=middle]
480  \bTR
481    \bTD \getspr{100} \eTD \bTD test \setspr{30} \eTD
482  \eTR
483  \bTR
484    \bTD \getspr{20} \eTD \bTD \getspr{4+3} \eTD
485  \eTR
486  \bTR
487    \bTD \getspr{A[1] + A[2]} \eTD
488    \bTD \getspr{B1 + B2} \eTD
489  \eTR
490  \bTR
491    \bTD[nx=2] \bf \getspr{(A[3] + B[3]) /100} \eTD
492  \eTR
493  \bTR
494    \bTD[nx=2] \bf \getspr{fmt("@0.3f",(A[3] + B[3]) /100)} \eTD
495  \eTR
496  \bTR
497    \bTD[nx=2] \bf \getspr{fmt("@0.3f",(sum(A,1,2)) / 10)} \eTD
498  \eTR
499\eTABLE
500\stopbuffer
501
502\typebuffer[demo]
503
504The method to use depends on the complexity of the table. If there is
505more text than data then this method is probably more comfortable.
506
507\placefigure
508  [here]
509  [spreadsheet:3]
510  {A sheet can be filled and accessed from regular tables.}
511  {\getbuffer[demo]}
512
513% \setupspreadsheet[mysheet]
514%
515% \startspreadsheet[mysheet]
516%
517% \bTABLE[align=middle]
518%   \bTR
519%     \bTD \getspr{100} \eTD \bTD test \setspr{30} \eTD
520%   \eTR
521%   \bTR
522%     \bTD \getspr{20} \eTD \bTD \getspr{4+3.5} \eTD
523%   \eTR
524%   \bTR
525%     \bTD \getspr{A[1] + A[2]} \eTD
526%     \bTD \getspr{B[1] + B[2]} \eTD
527%   \eTR
528%   \bTR
529%     \bTD[nx=2] \bf \getspr{A[3] + B[3]} \eTD
530%   \eTR
531% \eTABLE
532%
533% \stopspreadsheet
534
535\stopsection
536
537\startsection[title={A few settings}]
538
539It's possible to influence the rendering. The following example demonstrates
540this. We don't use any formatting directives.
541
542\startbuffer[demo]
543\startspreadsheettable[test]
544  \startrow
545    \startcell   123456.78 \stopcell
546  \stoprow
547  \startrow
548    \startcell  1234567.89 \stopcell
549  \stoprow
550  \startrow
551    \startcell A[1] + A[2] \stopcell
552  \stoprow
553\stopspreadsheettable
554\stopbuffer
555
556\typebuffer[demo]
557
558\placefigure
559  [here]
560  [spreadsheet:4]
561  {Formatting (large) numbers.}
562  {\getbuffer[demo]}
563
564\in {Figure} [spreadsheet:4] demonstrates how this gets rendered by
565default. However, often you want numbers to be split in parts separated by
566periods and commas. This can be done as follows:
567
568\startbuffer[setup]
569\definehighlight[BoldAndRed]  [style=bold,color=darkred]
570\definehighlight[BoldAndGreen][style=bold,color=darkgreen]
571
572\setupspreadsheet
573  [test]
574  [period={\BoldAndRed{.}},
575   comma={\BoldAndGreen{,}},
576   split=yes]
577\stopbuffer
578
579\typebuffer[setup] \getbuffer[setup]
580
581\placefigure
582  [here]
583  [spreadsheet:5]
584  {Formatting (large) numbers with style and color.}
585  {\getbuffer[setup,demo]}
586
587\stopsection
588
589\startsection[title={The \LUA\ end}]
590
591You can also use spreadsheets from within \LUA. The following example is
592rather straightforward:
593
594\startbuffer[demo-a]
595\startluacode
596context.startspreadsheettable { "test" }
597  context.startrow()
598    context.startcell() context("123456.78")   context.stopcell()
599  context.stoprow()
600  context.startrow()
601    context.startcell() context("1234567.89")  context.stopcell()
602  context.stoprow()
603  context.startrow()
604    context.startcell() context("A[1] + A[2]") context.stopcell()
605  context.stoprow()
606context.stopspreadsheettable()
607\stopluacode
608\stopbuffer
609
610\typebuffer[demo-a]
611
612However, even more \LUA|-|ish is the next variant:
613
614\startbuffer[demo-b]
615\startluacode
616  local set = moduledata.spreadsheets.set
617  local get = moduledata.spreadsheets.get
618
619  moduledata.spreadsheets.start("test")
620    set("test",1,1,"123456.78")
621    set("test",2,1,"1234567.89")
622    set("test",3,1,"A[1] + A[2]")
623  moduledata.spreadsheets.stop()
624
625  context.bTABLE()
626    context.bTR()
627      context.bTD() context(get("test",1,1)) context.eTD()
628    context.eTR()
629    context.bTR()
630      context.bTD() context(get("test",2,1)) context.eTD()
631    context.eTR()
632    context.bTR()
633      context.bTD() context(get("test",3,1)) context.eTD()
634    context.eTR()
635  context.eTABLE()
636\stopluacode
637\stopbuffer
638
639\typebuffer[demo-b]
640
641Of course the second variant does not make much sense as we can do this way
642more efficient by not using a spreadsheet at all:
643
644\startbuffer[demo-c]
645\startluacode
646  local A1, A2 = 123456.78, 1234567.89
647  context.bTABLE()
648    context.bTR()
649      context.bTD() context(A1)    context.eTD()
650    context.eTR()
651    context.bTR()
652      context.bTD() context(A2)    context.eTD()
653    context.eTR()
654    context.bTR()
655      context.bTD() context(A1+A2) context.eTD()
656    context.eTR()
657  context.eTABLE()
658\stopluacode
659\stopbuffer
660
661\typebuffer[demo-c]
662
663You can of course use format explicitly. Here we use the normal percent
664directives because we're in \LUA, and not in \TEX, where percentage
665signs are a bit of an issue.
666
667\startbuffer[demo-d]
668\startluacode
669  local A1, A2 = 123456.78, 1234567.89
670  local options = { align = "flushright" }
671  context.bTABLE()
672    context.bTR()
673      context.bTD(options)
674        context("%0.2f",A1)
675      context.eTD()
676    context.eTR()
677    context.bTR()
678      context.bTD(options)
679        context("%0.2f",A2)
680      context.eTD()
681    context.eTR()
682    context.bTR()
683      context.bTD(options)
684        context("%0.2f",A1+A2)
685      context.eTD()
686    context.eTR()
687  context.eTABLE()
688\stopluacode
689\stopbuffer
690
691\typebuffer[demo-d]
692
693As expected and shown in \in {figure} [spreadsheet:6], only the first and last
694variant gets the numbers typeset nicely.
695
696\placefigure
697  [here]
698  [spreadsheet:6]
699  {Spreadsheets purely done as \CONTEXT\ \LUA\ Document.}
700  {\startcombination[4*1]
701     {\getbuffer[demo-a]} {}
702     {\getbuffer[demo-b]} {}
703     {\getbuffer[demo-c]} {}
704     {\getbuffer[demo-d]} {}
705   \stopcombination}
706
707\stopsection
708
709\startsection[title={Helper macros}]
710
711There are two helper macros that you can use to see what is stored in a
712spreadsheet:
713
714\starttyping
715\inspectspreadsheet[test]
716\showspreadsheet   [test]
717\stoptyping
718
719The first command reports the content of \type {test} to the console, and
720the second one typesets it in the running text:
721
722\blank
723\showspreadsheet[test]
724\blank
725
726Another helper function is \type {\doifelsespreadsheetcell}, You can use this
727one to check if a cell is set.
728
729\startbuffer[demo]
730(1,1): \doifelsespreadsheetcell[test]{1}{1}{set}{unset}
731(2,2): \doifelsespreadsheetcell[test]{2}{2}{set}{unset}
732(9,9): \doifelsespreadsheetcell[test]{9}{9}{set}{unset}
733\stopbuffer
734
735\typebuffer[demo]
736
737This gives:
738
739\startlines
740\getbuffer[demo]
741\stoplines
742
743There is not much more to say about this module, apart from that it is a
744nice example of a \TEX\ and \LUA\ mix. Maybe some more (basic) functionality
745will be added in the future but it all depends on usage.
746
747\stopsection
748
749\startsubject[title={Colofon}]
750
751\starttabulate[|B|p|]
752\NC author    \NC \documentvariable{author}, \documentvariable{affiliation}, \documentvariable{location} \NC \NR
753\NC version   \NC \currentdate \NC \NR
754\NC website   \NC \documentvariable{website} \endash\ \documentvariable{support} \NC \NR
755\NC copyright \NC \symbol[cc][cc-by-sa] \NC \NR
756\stoptabulate
757
758\stopsubject
759
760\stopdocument
761