The filtering code that I wrote doesn't work for some reason, every single formula is returning error in the filtered_data.json file. The json file's data is formated like this:
"sheets": [
{
"id": "sheet-0",
"data": []
},
{
"id": "sheet-1",
"data": [
[
2,
4,
8,
16
]
]
},
{
"id": "sheet-2",
"data": [
[
5,
"=A1",
22,
"=C1"
]
]
},
{
"id": "sheet-3",
"data": [
[
22,
212212,
"=SUM(A1, B1)"
]
]
}
My code returns:
[
{
"id":"sheet-2",
"data":[
[
5,
"#ERROR",
22,
"#ERROR"
]
]
},
{
"id":"sheet-3",
"data":[
[
22,
212212,
"#ERROR"
]
]
},
{
"id":"sheet-4",
"data":[
[
221212,
22,
"#ERROR",
212
]
]
}
def evaluate_formula(formula, sheet):
# Check if the formula is a SUM formula
if formula.startswith('=('):
try:
range_str = parse_operands(formula[2:-1])
except ValueError:
return '#ERROR'
# Extract the cell to reference
cell_str = range_str.upper()
col = ord(cell_str[0]) - 65
row = int(cell_str[1:]) - 1
return sheet[row][col]
elif formula.startswith('=SUM('):
# Extract the range of cells to sum
try:
range_str = parse_operands(formula[5:-1])
except ValueError:
return '#ERROR'
range_arr = re.findall('[A-Z]+[0-9]+', range_str)
# Calculate the sum of the cells
sum_val = 0
for cell in range_arr:
col = ord(cell[:-1]) - 65
row = int(cell[-1:]) - 1
sum_val += sheet[row][col]
return sum_val
# Check if the formula is a MULTIPLY formula
elif formula.startswith('=MULTIPLY('):
# Extract the range of cells to multiply
try:
range_str = parse_operands(formula[10:-1])
except ValueError:
return '#ERROR'
range_arr = re.findall('[A-Z]+[0-9]+', range_str)
# Calculate the product of the cells
product_val = 1
for cell in range_arr:
col = ord(cell[:-1]) - 65
row = int(cell[-1:]) - 1
product_val *= sheet[row][col]
return product_val
# Check if the formula is a DIVIDE formula
elif formula.startswith('=DIVIDE('):
# Extract the range of cells to divide
try:
range_str = parse_operands(formula[8:-1])
except ValueError:
return '#ERROR'
range_arr = re.findall('[A-Z]+[0-9]+', range_str)
# Calculate the quotient of the cells
quotient_val = None
for cell in range_arr:
col = ord(cell[:-1]) - 65
row = int(cell[-1:]) - 1
if quotient_val is None:
quotient_val = sheet[row][col]
else:
quotient_val /= sheet[row][col]
return quotient_val
# Check if the formula is an AND formula
elif formula.startswith('=AND('):
# Extract the range of cells to check for truthiness
try:
range_str = parse_operands(formula[5:-1])
except ValueError:
return '#ERROR'
range_arr = re.findall('[A-Z]+[0-9]+', range_str)
# Check if all cells are true
for cell in range_arr:
col = ord(cell[:-1]) - 65
row = int(cell[-1:]) - 1
if not sheet[row][col]:
return False
return True
# Check if the formula is an OR formula
elif formula.startswith('=OR('):
# Extract the range of cells to check for truthiness
try:
range_str = parse_operands(formula[4:-1])
except ValueError:
return '#ERROR'
range_arr = re.findall('[A-Z]+[0-9]+', range_str)
# Check if any cells are true
for cell in range_arr:
col = ord(cell[:-1]) - 65
row = int(cell[-1:]) - 1
if sheet[row][col]:
return True
return False
# Check if the formula is a NOT formula
elif formula.startswith('=NOT('):
# Extract the cell to negate
try:
cell_str = parse_operands(formula[5:-1])
except ValueError:
return '#ERROR'
col = ord(cell_str[:-1]) - 65
row = int(cell_str[-1:]) - 1
return not sheet[row][col]
# Check if the formula is a CONCATENATE formula
elif formula.startswith('=CONCATENATE('):
# Extract the range of cells to concatenate
try:
range_str = parse_operands(formula[13:-1])
except ValueError:
return '#ERROR'
range_arr = re.findall('[A-Z]+[0-9]+', range_str)
# Concatenate the cells
concat_str = ''
for cell in range_arr:
col = ord(cell[:-1]) - 65
row = int(cell[-1:]) - 1
concat_str += str(sheet[row][col])
return concat_str
else:
# If the formula is not recognized, return an error
return '#ERROR'
def evaluate_cell(cell, sheet):
# Check if the cell is a constant
if isinstance(cell, (int, float)):
return cell
# Check if the cell is an error
elif cell.startswith('#'):
return cell
# Check if the cell is a formula
elif cell.startswith("="):
# Evaluate the formula
formula = cell[1:]
return evaluate_formula(formula, sheet)
# If the cell is not one of the above, there is an error
else:
return '#ERROR'
def parse_operands(operands_string, data):
# Parse a string of operands separated by commas
operands = []
for operand_string in operands_string.split(','):
operand_string = operand_string.strip()
if operand_string.isnumeric():
# Operand is a numeric value
operand = int(operand_string)
elif isinstance(operand_string, str) and operand_string.startswith('=') or isinstance(operand_string, str) and operand_string.startswith("=SUM(") or isinstance(operand_string, str) and operand_string.startswith("=DIVIDE(") or isinstance(operand_string, str) and operand_string.startswith("=MULTIPLY(") or isinstance(operand_string, str) and operand_string.startswith("=NOT(") or isinstance(operand_string, str) and operand_string.startswith("=EQ(") or isinstance(operand_string, str) and operand_string.startswith("=GT("):
# Operand is a reference to another cell
cell_id = operand_string[len(operand_string):]
cell_value = get_cell_value(cell_id, data)
operand = cell_value
else:
raise ValueError(f'Invalid operand: {operand_string}')
operands.append(operand)
return operands
def get_cell_value(cell_id, data):
# Get the value of a cell by its ID
row_index, column_index = cell_id.split(':')
row_index = int(row_index) - 1
column_index = ord(column_index) - 65
if row_index >= len(data) or column_index >= len(data[row_index]):
raise ValueError(f'Invalid cell reference: {cell_id}')
return data[row_index][column_index]
I tried to check if the .startswith() methods were correct but didn't work.
source https://stackoverflow.com/questions/75998930/filtering-json-file-using-python
Comments
Post a Comment