Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Program to perform excel spreadsheet operation in Python?
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
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']]
242 Views
