# 631. Design Excel Sum Formula

Your task is to design the basic function of Excel and implement the function of sum formula. Specifically, you need to implement the following functions:

`Excel(int H, char W):` This is the constructor. The inputs represents the height and width of the Excel form. His a positive integer, range from 1 to 26. It represents the height. W is a character range from ‘A’ to ‘Z’. It represents that the width is the number of characters from ‘A’ to W. The Excel form content is represented by a height * width 2D integer array `C`, it should be initialized to zero. You should assume that the first row of `C` starts from 1, and the first column of `C` starts from ‘A’.

`void Set(int row, char column, int val):` Change the value at `C(row, column)` to be val.

`int Get(int row, char column):` Return the value at `C(row, column)`.

`int Sum(int row, char column, List of Strings : numbers):` This function calculate and set the value at `C(row, column)`, where the value should be the sum of cells represented by `numbers`. This function return the sum result at `C(row, column)`. This sum formula should exist until this cell is overlapped by another value or another sum formula.

`numbers` is a list of strings that each string represent a cell or a range of cells. If the string represent a single cell, then it has the following format : `ColRow`. For example, “F7” represents the cell at (7, F).

If the string represent a range of cells, then it has the following format : `ColRow1:ColRow2`. The range will always be a rectangle, and ColRow1 represent the position of the top-left cell, and ColRow2 represents the position of the bottom-right cell.

Example 1:

``````Excel(3,"C");
// construct a 3*3 2D array with all zero.
//   A B C
// 1 0 0 0
// 2 0 0 0
// 3 0 0 0

Set(1, "A", 2);
// set C(1,"A") to be 2.
//   A B C
// 1 2 0 0
// 2 0 0 0
// 3 0 0 0

Sum(3, "C", ["A1", "A1:B2"]);
// set C(3,"C") to be the sum of value at C(1,"A") and the values sum of the rectangle range whose top-left cell is C(1,"A") and bottom-right cell is C(2,"B"). Return 4.
//   A B C
// 1 2 0 0
// 2 0 0 0
// 3 0 0 4

Set(2, "B", 2);
// set C(2,"B") to be 2. Note C(3, "C") should also be changed.
//   A B C
// 1 2 0 0
// 2 0 2 0
// 3 0 0 6
``````

Note:

1. You could assume that there won’t be any circular sum reference. For example, A1 = sum(B1) and B1 = sum(A1).
2. The test cases are using double-quotes to represent a character.
3. Please remember to RESET your class variables declared in class Excel, as static/class variables are persisted across multiple test cases. Please see here for more details.

[“Excel”,”sum”,”set”,”get”]
[[3,”C”],[1,”A”,[“A2”]],[2,”A”,1],[1,”A”]]
Expected:
[null,0,null,1]

``````class Excel {
public:
Excel(int H, char W) {
m.clear();
mat.resize(H, vector<int>(W - 'A', 0));
}

void set(int r, char c, int v) {
if (m.count({r, c})) m.erase({r, c});
mat[r - 1][c - 'A'] = v;
}

int get(int r, char c) {
if (m.count({r, c})) return sum(r, c, m[{r, c}]);
return mat[r - 1][c - 'A'];
}

int sum(int r, char c, vector<string> strs) {
int res = 0;
for (string str : strs) {
auto found = str.find_last_of(":");
if (found == string::npos) {
char y = str[0];
int x = stoi(str.substr(1));
res += get(x, y);
} else {
int x1 = stoi(str.substr(1, (int)found - 1)), y1 = str[0] - 'A';
int x2 = stoi(str.substr(found + 2)), y2 = str[found + 1] - 'A';
for (int i = x1; i <= x2; ++i) {
for (int j = y1; j <= y2; ++j) {
res += get(i, j + 'A');
}
}
}
}
m[{r, c}] = strs;
return res;
}

private:
vector<vector<int>> mat;
map<pair<int, char>, vector<string>> m;
};
``````

Github 同步地址：

https://github.com/grandyang/leetcode/issues/631

https://leetcode.com/problems/design-excel-sum-formula/

https://discuss.leetcode.com/topic/93819/c-3-ms-solution-easy-to-understand

https://discuss.leetcode.com/topic/93812/c-3-ms-concise-and-easy-to-understand

LeetCode All in One 题目讲解汇总(持续更新中…)

 微信打赏 Venmo 打赏
（欢迎加入博主的知识星球，博主将及时答疑解惑，并分享刷题经验与总结，试运营期间前五十位可享受半价优惠～）

×

Help us with donation