openpyxl: Error in summing values of cells from multiple files-CodePudding

I want to sum numbers in each cell from multiple different files. The files have the same form. (only the values of the cells are different) ex) enter image description here

from openpyxl import load_workbook
from openpyxl import Workbook
import os
import shutil   

abspath = os.path.abspath(__file__)
dname = os.path.dirname(abspath)
os.chdir(dname)

file_path= os.getcwd()
file_format = ".xlsx"
file_list = [f"{file}" for file in os.listdir(file_path) if file_format in file]

shutil.copy(file_list[0], 'result_sehyun.xlsx')

# col_range = ['B','C','D','E','F','G','H']
# row_range = ['4','5','6','7','8','9','10','11','12','13','14','15','16']
wb_to = load_workbook('result_sehyun.xlsx')
sheet_list_to = wb_to.sheetnames
for file in file_list[1:]: 
    wb_from = load_workbook(file)
    sheet_list_from = wb_from.sheetnames 
    for sheet_from in sheet_list_from:
        for sheet_to in sheet_list_to: 
            ws_to = wb_to[sheet_to] 
            ws_from = wb_from[sheet_from] 
            for row in range(4,17):
                for col in range(2,9): 
                    ws_to.cell(row, col).value = ws_to.cell(row, col).value   ws_from.cell(row, col).value
            # for col in col_range:
            #     for row in row_range:
            #         ws_to[col row] = ws_to[col row].value   ws_from[col row].value

This is the code I've written so far. And when I run it, I get this error

Traceback (most recent call last): File "c:\Sehyun\Sehyun.py", line 33, in ws_to.cell(row, col).value = ws_to.cell(row, col).value ws_from.cell(row, col).value TypeError: unsupported operand type(s) for : 'int' and 'NoneType

Is this happening because some of the cells are empty? If it is, what code should I use? Aprreciate any helps. Thanks!

CodePudding user response：

yes, the error you are seeing is because there is no data in that cell. One way is to replace the missing value with zero(0). That way, the resulting cell will show zero if there is no data in either of the two cells. If that is fine, you can add below code where this line is -> ws_to.cell(row, col).value = ws_to.cell(row, col).value ws_from.cell(row, col).value

Append code (make sure it is tabbed to be in line with the addition line)

....
if ws_to.cell(row, col).value == None:
    ws_to.cell(row, col).value = 0
if ws_from.cell(row, col).value == None:
    ws_from.cell(row, col).value = 0
ws_to.cell(row, col).value = ws_to.cell(row, col).value   ws_from.cell(row, col).value
....