Program to perform excel spreadsheet operation in Python?

PythonServer Side ProgrammingProgramming

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

B170
35=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

B170
35=A1+A2

then the output will be

770
3510

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']]
raja
Published on 10-Nov-2020 09:22:23
Advertisements