1if not modules then modules = { } end modules ['m-spreadsheet'] = {
2 version = 1.001,
3 comment = "companion to m-spreadsheet.mkiv",
4 author = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
5 copyright = "PRAGMA ADE / ConTeXt Development Team",
6 license = "see context related readme files"
7}
8
9local byte, format, gsub, find = string.byte, string.format, string.gsub, string.find
10local R, P, S, C, V, Cs, Cc, Ct, Cg, Cf, Carg = lpeg.R, lpeg.P, lpeg.S, lpeg.C, lpeg.V, lpeg.Cs, lpeg.Cc, lpeg.Ct, lpeg.Cg, lpeg.Cf, lpeg.Carg
11local lpegmatch, patterns = lpeg.match, lpeg.patterns
12local setmetatable, loadstring, next, tostring, tonumber,rawget = setmetatable, loadstring, next, tostring, tonumber, rawget
13local formatters = string.formatters
14
15local context = context
16
17local splitthousands = utilities.parsers.splitthousands
18local variables = interfaces.variables
19
20local v_yes = variables.yes
21
22moduledata = moduledata or { }
23
24local spreadsheets = { }
25moduledata.spreadsheets = spreadsheets
26
27local data = {
28
29}
30
31local settings = {
32 period = ".",
33 comma = ",",
34}
35
36spreadsheets.data = data
37spreadsheets.settings = settings
38
39local defaultname = "default"
40local stack = { }
41local current = defaultname
42
43local d_mt ; d_mt = {
44 __index = function(t,k)
45 local v = { }
46 setmetatable(v,d_mt)
47 t[k] = v
48 return v
49 end,
50}
51
52local s_mt ; s_mt = {
53 __index = function(t,k)
54 local v = settings[k]
55 t[k] = v
56 return v
57 end,
58}
59
60function spreadsheets.setup(t)
61 for k, v in next, t do
62 settings[k] = v
63 end
64end
65
66local function emptydata(name,settings)
67 local data = { }
68 local specifications = { }
69 local settings = settings or { }
70 setmetatable(data,d_mt)
71 setmetatable(specifications,d_mt)
72 setmetatable(settings,s_mt)
73 return {
74 name = name,
75 data = data,
76 maxcol = 0,
77 maxrow = 0,
78 settings = settings,
79 temp = { },
80 specifications = specifications,
81 }
82end
83
84function spreadsheets.reset(name)
85 if not name or name == "" then name = defaultname end
86 data[name] = emptydata(name,data[name] and data[name].settings)
87end
88
89function spreadsheets.start(name,s)
90 if not name or name == "" then
91 name = defaultname
92 end
93 if not s then
94 s = { }
95 end
96 table.insert(stack,current)
97 current = name
98 if data[current] then
99 setmetatable(s,s_mt)
100 data[current].settings = s
101 else
102 data[current] = emptydata(name,s)
103 end
104end
105
106function spreadsheets.stop()
107 current = table.remove(stack)
108end
109
110spreadsheets.reset()
111
112local offset = byte("A") - 1
113
114local function assign(s,n)
115 return formatters["moduledata.spreadsheets.data['%s'].data[%s]"](n,byte(s)-offset)
116end
117
118function datacell(a,b,...)
119 local n = 0
120 if b then
121 local t = { a, b, ... }
122 for i=1,#t do
123 n = n * (i-1) * 26 + byte(t[i]) - offset
124 end
125 else
126 n = byte(a) - offset
127 end
128 return formatters["dat[%s]"](n)
129end
130
131local function checktemplate(s)
132 if find(s,"%",1,true) then
133
134 return s
135 elseif find(s,"@",1,true) then
136
137 return gsub(s,"@","%%")
138 else
139
140 return "%" .. s
141 end
142end
143
144local quoted = Cs(patterns.unquoted)
145local spaces = patterns.whitespace^0
146local cell = C(R("AZ"))^1 / datacell * (Cc("[") * (R("09")^1) * Cc("]") + #P(1))
147
148
149
150
151local pattern = Cf ( spaces * Ct("") * { "start",
152 start = V("value") + V("set") + V("format") + V("string") + V("code"),
153 value = Cg(P([[=]]) * spaces * Cc("kind") * Cc("value")) * V("code"),
154 set = Cg(P([[!]]) * spaces * Cc("kind") * Cc("set")) * V("code"),
155 format = Cg(P([[@]]) * spaces * Cc("kind") * Cc("format")) * spaces * Cg(Cc("template") * Cs(quoted/checktemplate)) * V("code"),
156 string = Cg(#S([["']]) * Cc("kind") * Cc("string")) * Cg(Cc("content") * quoted),
157 code = spaces * Cg(Cc("code") * Cs((cell + P(1))^0)),
158}, rawset)
159
160local functions = { }
161spreadsheets.functions = functions
162
163function functions._s_(row,col,c,f,t)
164 local r = 0
165 if f and t then
166
167 elseif f then
168 f, t = 1, f
169 else
170 f, t = 1, row - 1
171 end
172 for i=f,t do
173 local ci = c[i]
174 if type(ci) == "number" then
175 r = r + ci
176 end
177 end
178 return r
179end
180
181functions.fmt = string.tformat
182
183local f_code = formatters [ [[
184 local _m_ = moduledata.spreadsheets
185 local dat = _m_.data['%s'].data
186 local tmp = _m_.temp
187 local fnc = _m_.functions
188 local row = %s
189 local col = %s
190 function fnc.sum(...) return fnc._s_(row,col,...) end
191 local sum = fnc.sum
192 local fmt = fnc.fmt
193 return %s
194]] ]
195
196
197
198local function propername(name)
199 if name ~= "" then
200 return name
201 elseif current ~= "" then
202 return current
203 else
204 return defaultname
205 end
206end
207
208
209
210local function execute(name,r,c,str)
211 if str ~= "" then
212 local d = data[name]
213 if c > d.maxcol then
214 d.maxcol = c
215 end
216 if r > d.maxrow then
217 d.maxrow = r
218 end
219 local specification = lpegmatch(pattern,str,1,name)
220 d.specifications[c][r] = specification
221 local kind = specification.kind
222 if kind == "string" then
223 return specification.content or ""
224 else
225 local code = specification.code
226 if code and code ~= "" then
227 code = f_code(name,r,c,code or "")
228 local result = loadstring(code)
229 result = result and result()
230 if type(result) == "function" then
231 result = result()
232 end
233 if type(result) == "number" then
234 d.data[c][r] = result
235 end
236 if not result then
237
238 elseif kind == "set" then
239
240 elseif kind == "format" then
241 return formatters[specification.template](result)
242 else
243 return result
244 end
245 end
246 end
247 end
248end
249
250function spreadsheets.set(name,r,c,str)
251 name = propername(name)
252 execute(name,r,c,str)
253end
254
255function spreadsheets.get(name,r,c,str)
256 name = propername(name)
257 local dname = data[name]
258 if not dname then
259
260 elseif not str or str == "" then
261 context(dname.data[c][r] or 0)
262 else
263 local result = execute(name,r,c,str)
264 if result then
265
266
267
268
269 local settings = dname.settings
270 local split = settings.split
271 local period = settings.period
272 local comma = settings.comma
273 if split == v_yes then
274 result = splitthousands(result)
275 end
276 if period == "" then period = nil end
277 if comma == "" then comma = nil end
278 result = gsub(result,".",{ ["."] = period, [","] = comma })
279 context(result)
280 end
281 end
282end
283
284function spreadsheets.doifelsecell(name,r,c)
285 name = propername(name)
286 local d = data[name]
287 local d = d and d.data
288 local r = d and rawget(d,r)
289 local c = r and rawget(r,c)
290 commands.doifelse(c)
291end
292
293local function simplify(name)
294 name = propername(name)
295 local data = data[name]
296 if data then
297 data = data.data
298 local temp = { }
299 for k, v in next, data do
300 local t = { }
301 temp[k] = t
302 for kk, vv in next, v do
303 if type(vv) == "function" then
304 t[kk] = "<function>"
305 else
306 t[kk] = vv
307 end
308 end
309 end
310 return temp
311 end
312end
313
314local function serialize(name)
315 local s = simplify(name)
316 if s then
317 return table.serialize(s,name)
318 else
319 return formatters["<unknown spreadsheet %a>"](name)
320 end
321end
322
323spreadsheets.simplify = simplify
324spreadsheets.serialize = serialize
325
326function spreadsheets.inspect(name)
327 inspect(serialize(name))
328end
329
330function spreadsheets.tocontext(name)
331 context.tocontext(simplify(name))
332end
333 |