Implement a simplified in-memory spreadsheet that supports three operations: SET, GET, and SUM, similar to basic Excel functionality.
Assumptions and requirements:
'A'
to
'Z'
.
1
to
1000
.
'A1'
,
'B2'
,
'Z1000'
.
You must support the following operations:
SUM
operation), its value is the sum of its referenced cells, recursively evaluated.
cellId
is the target cell where we store a formula.
refs
is a list of references. Each reference is either:
'B2'
, or
'A1:B2'
, which represents all cells whose column is between
A
and
B
inclusive and whose row is between
1
and
2
inclusive.
cellId
a formula that is defined as the sum of all cells in
refs
(expanding ranges into the constituent cells).
Behavioral example (your implementation should be consistent with this behavior):
SET('A1', 1)
→ returns
1
SET('B2', 2)
→ returns
2
SUM('C1', ['A1:B2', 'B2'])
→ returns
5
SUM('C1', ['B2'])
→ returns
2
SET('C1', 5)
→ returns
5
GET('C1')
→ returns
5
SUM('D1', ['C1', 'A1'])
→ returns
6
SET('A1', 10)
→ returns
10
GET('C1')
→ returns
14
(because
C1
still refers to
B2
whose value is
2
, so its formula sum is
2 + 10 + 2
in the example sequence)
Additional requirements and edge cases:
SET
or
SUM
must correctly affect any other cells whose formulas depend (directly or transitively) on that cell, so that subsequent
GET
calls return up-to-date values.
A1
used by
B1
, which is used by
C1
, etc.
A1
depends on
B1
and later
B1
is defined to depend on
A1
, there is a cycle.
GET
.
Design and implement the data structures and methods to support these operations.