Repository navigation
Expand file tree
/
Copy pathexcel2.1.hs
More file actions
263 lines (221 loc) · 9.57 KB
/
Copy pathexcel2.1.hs
File metadata and controls
263 lines (221 loc) · 9.57 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
import Data.Map.Strict as Map (Map, empty, elems, mapWithKey, foldrWithKey, member, insert, lookup, toList)
import Data.Graph as Graph (Graph, Vertex, graphFromEdges, transposeG, reachable)
import Data.List (foldl')
import Data.Tree (flatten)
import Data.Char (ord, chr)
import Data.Set as Set (Set, insert, member, empty, union, toList, singleton, fromList)
import Debug.Trace
import Data.Fixed
import Text.PrettyPrint.Boxes as Box (render, hcat, vcat, text)
import Text.PrettyPrint.Boxes as Alignment (left, right)
data XlValue = XlNumber Double
| XlString String
| XlBoolean Bool
| XlError String
| XlMatrix [[XlValue]]
deriving Eq
instance Show XlValue where
show (XlNumber d) = show d
show (XlString s) = show s
show (XlBoolean b) = show b
show (XlError e) = show e
show (XlMatrix m) = show m
data XlAddr = XlAbs Int
| XlRel Int
deriving (Eq, Ord)
instance Show XlAddr where
show (XlAbs n) = show n
show (XlRel n) = "[" ++ show n ++ "]"
data XlRC = XlRC XlAddr XlAddr
deriving (Eq, Ord)
instance Show XlRC where
show (XlRC r@(XlAbs rn) c@(XlAbs cn)) = "<" ++ [chr (rn + 65)] ++ show (cn + 1) ++ ">"
show (XlRC r c) = "R" ++ show r ++ "C" ++ show c
data XlFormula = XlLit XlValue
| XlFun String [XlFormula]
| XlRef XlRC
| XlRng XlRC XlRC
deriving Show
data XlCell = XlCell XlFormula
| XlAFCell XlFormula
deriving Show
type XlCells = Map.Map XlRC XlCell
data XlWorksheet = XlWorksheet XlCells
deriving Show
--data XlWorkbook = XlWorkbook (Map String XlWorksheet)
data XlEvent = XlEvent XlRC XlCell
deriving Show
type XlValues = Map.Map XlRC XlValue
type XlDeps = [(XlCell, XlRC, [XlRC])]
data XlState = XlState XlCells XlValues XlDeps
instance Show XlState where
show (XlState cells values _) = (Box.render $ Box.vcat Alignment.left $ map Box.text $ (map show (Map.toList cells))) ++ "\n\n" ++ (Box.render $ Box.hcat Alignment.left $ numbers : map doRow [0..25])
where
lpad m xs = reverse $ take m $ reverse $ (take m $ repeat ' ') ++ (take m xs)
numbers = Box.vcat Alignment.right $ map Box.text $ " " : map show [1..26]
doRow r = Box.vcat Alignment.left $ Box.text ['|', chr (r + 65)] : map doColumn [0..25]
where
doColumn c =
let
rc = (XlRC (XlAbs r) (XlAbs c))
val = Map.lookup rc values
in
case val of
Just (XlNumber n) -> Box.text ('|' : (lpad 11 (toString n)))
Just v -> Box.text ('|' : show v)
Nothing -> Box.text "|"
toAbs :: XlRC -> XlRC -> XlRC
toAbs base@(XlRC br bc) cell@(XlRC cr cc) = XlRC (toAbsAddr br cr) (toAbsAddr bc cc)
where
toAbsAddr :: XlAddr -> XlAddr -> XlAddr
toAbsAddr _ a@(XlAbs _) = a
toAbsAddr (XlAbs aa) (XlRel rr) = XlAbs (aa + rr)
toAbsAddr base@(XlRel _) _ = error ("base in toAbs must be absolute, got " ++ show base)
-- Converts Excel addresses in "A1" format to internal RC format.
-- Supports only rows A-Z, and absolute addresses.
toRC :: String -> XlRC
toRC (l:num) = XlRC (XlAbs ((ord l) - 65)) (XlAbs ((read num) - 1))
toString :: Double -> String
toString n =
if fromIntegral (floor n) /= n
then show n
else show (floor n)
putValue :: XlState -> XlRC -> XlValue -> XlState
putValue env@(XlState cells values deps) rc value = XlState cells (Map.insert rc value values) deps
evalFormula :: Set XlRC -> XlState -> XlRC -> XlFormula -> (XlValue, XlState)
evalFormula _ env rc (XlLit value) = (value, putValue env rc value)
evalFormula visiting cells values rc (XlRef ref') =
let
ref = toAbs rc ref'
give val = (val, Map.insert rc val values)
in
if Set.member ref visiting
then give (XlError "#LOOP!")
else
case Map.lookup ref values of
Just v -> give v
Nothing ->
case Map.lookup ref cells of
Just (XlCell formula) -> evalFormula (Set.insert rc visiting) cells values ref formula
Nothing -> give (XlNumber 0)
evalFormula visiting cells values rc (XlRng from to) =
let
(XlRC (XlAbs fromR) (XlAbs fromC)) = toAbs rc from
(XlRC (XlAbs toR) (XlAbs toC)) = toAbs rc to
minR = min fromR toR
maxR = max fromR toR
minC = min fromC toC
maxC = max fromC toC
(mtx, newValues) =
foldl' handleRow ([], values) [minR..maxR]
where
handleRow :: ([[XlValue]], XlValues) -> Int -> ([[XlValue]], XlValues)
handleRow (curMtx, curValues) r =
let
(newRow, newValues) =
foldl' handleValue ([], curValues) [minC..maxC]
where
handleValue (curRow, curRowValues) c =
let
curRc = XlRC (XlAbs r) (XlAbs c)
(newValue, newRowValues) = evalFormula visiting cells curRowValues curRc (XlRef curRc)
in
(curRow ++ [newValue], newRowValues)
in
(curMtx ++ [newRow], newValues)
in
(XlMatrix mtx, newValues)
evalFormula visiting cells values rc (XlFun "IF" [i, t, e]) =
let
(vi, valuesi) = evalFormula (Set.insert rc visiting) cells values rc i
(vt, valuest) = evalFormula (Set.insert rc visiting) cells valuesi rc t
(ve, valuese) = evalFormula (Set.insert rc visiting) cells valuesi rc e
(vr, valuesr) =
case vi of
err@(XlError _) -> (vi, valuesi)
(XlNumber 0) -> (ve, valuese)
(XlMatrix _) -> ((XlError "#VALUE!"), valuesi)
_ -> (vt, valuest)
in
(vr, Map.insert rc vr valuesr)
evalFormula visiting cells values rc (XlFun "INDIRECT" [addr]) =
let
(va, valuesa) = evalFormula (Set.insert rc visiting) cells values rc addr
(vr, valuesr) =
case va of
XlString computed ->
evalFormula (Set.insert rc visiting) cells valuesa rc (XlRef (toRC computed))
_ ->
((XlError "#VALUE!"), valuesa)
in
(vr, Map.insert rc vr valuesr)
evalFormula visiting cells values rc (XlFun "+" [a, b]) =
let
(va, values') = evalFormula (Set.insert rc visiting) cells values rc a
(vb, values'') = evalFormula (Set.insert rc visiting) cells values' rc b
doSum e@(XlError _) _ = e
doSum _ e@(XlError _) = e
doSum (XlNumber na) (XlNumber nb) = XlNumber (na + nb)
doSum (XlString sa) (XlNumber nb) = XlString (sa ++ (toString nb))
doSum (XlNumber na) (XlString sb) = XlString ((toString na) ++ sb)
doSum (XlString sa) (XlString sb) = XlString (sa ++ sb)
doSum _ _ = XlError "#VALUE!"
val = doSum va vb
in
(val, Map.insert rc val values'')
evalFormula visiting cells values rc (XlFun "SUM" [rng]) =
let
(vrng, values') = evalFormula (Set.insert rc visiting) cells values rc rng
doSum e@(XlError _) _ = e
doSum _ e@(XlError _) = e
doSum s@(XlString _) v = v
doSum v s@(XlString _) = v
doSum (XlBoolean b) (XlNumber n) = XlNumber ((if b == True then 1 else 0) + n)
doSum (XlNumber n) (XlBoolean b) = XlNumber ((if b == True then 1 else 0) + n)
doSum (XlNumber a) (XlNumber b) = XlNumber (a + b)
val =
case vrng of
XlMatrix mtx ->
foldl' (foldl' doSum) (XlNumber 0) mtx
_ ->
XlError "#VALUE!"
in
(val, Map.insert rc val values')
updateCell :: XlState -> XlRC -> XlCell -> XlState
updateCell env@(XlState cells values deps) rc cell@(XlCell formula) =
let
newCells = (Map.insert rc cell cells)
env' = XlState newCells values deps
(_, (newValues, newDeps)) = evalFormula Set.empty env' rc formula
in
XlState newCells newValues newDeps
calcCell env rc (XlAFCell formula) = undefined -- TODO
run :: XlWorksheet -> [XlEvent] -> XlState
run sheet@(XlWorksheet cells) events =
let
runEvent :: XlState -> XlEvent -> XlState
runEvent env@(XlState cells values deps) event@(XlEvent rc newCell) =
updateCell (XlState cells values deps) rc newCell
in foldl' runEvent (XlState cells Map.empty []) events
str :: String -> XlFormula
str s = XlLit (XlString s)
num :: Double -> XlFormula
num n = XlLit (XlNumber n)
cell :: String -> XlFormula
cell a1 = XlRef (toRC a1)
range :: String -> String -> XlFormula
range a1 b2 = XlRng (toRC a1) (toRC b2)
main :: IO ()
main = print $ run (XlWorksheet Map.empty)
[
(XlEvent (toRC "A1") (XlCell (num 15))),
(XlEvent (toRC "B1") (XlCell (num 0))),
(XlEvent (toRC "A2") (XlCell (XlFun "+" [cell "A1", cell "B1"]))),
(XlEvent (toRC "B1") (XlCell (cell "A1"))),
(XlEvent (toRC "C1") (XlCell (str "B"))),
(XlEvent (toRC "C2") (XlCell (num 1))),
(XlEvent (toRC "B2") (XlCell (XlFun "INDIRECT" [XlFun "+" [cell "C1", cell "C2"]]))),
(XlEvent (toRC "D1") (XlCell (XlFun "SUM" [range "A1" "B2"]) )),
(XlEvent (toRC "E1") (XlCell (XlFun "SUM" [range "B1" "B2"]) )),
(XlEvent (toRC "F1") (XlCell (XlFun "SUM" [range "D1" "E1"]) ))
]