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

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

then the output will be

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']]