# Program to perform excel spreadsheet operation in Python?

PythonServer Side ProgrammingProgramming

#### Beyond Basic Programming - Intermediate Python

Most Popular

36 Lectures 3 hours

#### Practical Machine Learning using Python

Best Seller

91 Lectures 23.5 hours

#### Practical Data Science using Python

22 Lectures 6 hours

Suppose we have a 2D matrix representing an excel spreadsheet. We have to find the same matrix with all cells and formulas computed. An excel spreadsheet looks like below

 B1 7 0 3 5 =A1+A2

The columns are named as (A, B, C...) and rows are (1, 2, 3....) Each cell will either contain a value, a reference to another cell, or an excel formula for an operation with between numbers or cell reference. (Example. "=A1+5", "=A2+B2", or "=2+5")

So, if the input is like

 B1 7 0 3 5 =A1+A2

then the output will be

 7 7 0 3 5 10

as the B1 = 7 (The first row second column) and "=A1 + A2" is 7 + 3 = 10.

To solve this, we will follow these steps

• Define a function resolve() . This will take s

• if s is numeric, then return s as integer

• otherwise return solve(getIdx(s))

• Define a function getIdx() . This will take s

• return a list where first value is substring of s from 1 to end as integer and second value is ASCII of s[0] - ASCII of "A"

• Define a function do() . This will take a, b, op

• if op is same as "+", then

• return a + b

• if op is same as "-", then

• return a - b

• if op is same as "*", then

• return a * b

• if op is same as "/", then

• return a / b

• Define a function solve() . This will take i, j

• if matrix[i,j] is numeric then return that value

• otherwise:

• s := matrix[i, j]

• if s[0] is same as "=", then

• for each c in substring of s[from index 2 to end], do

• if c is any operator in (+, -, /, *), then

• op := c

• come out from the loop

• [a, b] := substring of s [from index 1 to end] and split it with op

• [aRes, bRes] := [resolve(a) , resolve(b)]

• return do(aRes, bRes, op)

• otherwise,

• return solve(getIdx(s))

• for i in range 0 to row count of matrix, do

• for j in range 0 to column count of matrix, do

• matrix[i, j] := (solve(i, j)) as string

• return matrix

Let us see the following implementation to get better understanding:

## Example

Live Demo

class Solution:
def solve(self, matrix):
def resolve(s):
try:
return int(s)
except:
return solve(*getIdx(s))

def getIdx(s):
return [int(s[1:]) - 1, ord(s[0]) - ord("A")]

def do(a, b, op):
if op == "+":
return a + b
if op == "-":
return a - b
if op == "*":
return a * b
if op == "/":
return a / b

def solve(i, j):
try:
return int(matrix[i][j])
except:
s = matrix[i][j]
if s[0] == "=":
for c in s[2:]:
if c in "+-/*":
op = c
break
a, b = s[1:].split(op)
aRes, bRes = resolve(a), resolve(b)
return do(aRes, bRes, op)
else:
return solve(*getIdx(s))

for i in range(len(matrix)):
for j in range(len(matrix[0])):
matrix[i][j] = str(solve(i, j))

return matrix

ob = Solution()
matrix = [
["B1", "7", "0"],
["3", "5", "=A1+A2"]
]
print(ob.solve(matrix))

## Input

[["B1", "7", "0"],
["3", "5", "=A1+A2"] ]

## Output

[['7', '7', '0'],
['3', '5', '10']]
Updated on 10-Nov-2020 09:22:23