python excel subtract with 2 worksheet

Question

Is it possible to create a python script to automatic which is subtract cell value with 2 worksheet in one excel file? I have checked some documents, and seem that use the method of pandas or openpyxl to do so. But I can't to do that. Do you have any suggestion to me? Many thanks.

Script:

from datetime import datetime
import pandas as pd
import openpyxl as xl;

currDateTime = datetime.now()

Sheet1 ="C:\\Users\\peter\\Downloads\\" + currDateTime.strftime('%Y%m%d') + "\\5250A" + "\\5250A.xlsx"
wb3 = xl.load_workbook(Sheet1) 
ws3 = wb3.worksheets[0] 
wb4 = xl.load_workbook(Sheet1) 
ws4 = wb4.worksheets[1] 
wb5 = xl.load_workbook(Sheet1) 
ws5 = wb5.create_sheet("Done")

wb4.subtract(wb3)

wb5.save(str(Sheet1))

Expected Result:

where did you get the function substract from openpyxl? and how about trying process the data row by row and save as a new sheet? — Sean Hsieh
– Sean Hsieh, Commented Mar 3, 2021 at 14:29
For the process the data row by row, is it use the for loop function to do so? Can you please give me one example for my reference? As I haven't idea how to do that. Many Thanks. — user11343272
– user11343272, Commented Mar 4, 2021 at 0:48

Sean Hsieh · Accepted Answer · 2021-03-04 03:15:53Z

Do so in excel coule be way easier I think. There could be a smarter way to write this code.

[NOTE] I just do the subsctraction cell by cell, so if there's any mismatch like same row but different dept.id or same col but different item will make errors. If you may meet this situation, you'll have a change some in the following code.

import openpyxl as xl

def get_row_values(worksheet):
    """
    return data structure:
    [
        [A1, B1, C1, ...],
        [A2, B2, C2, ...],
        ...
    ]
    """
    result = []
    for i in worksheet.rows:
        row_data = []
        for j in i:
            row_data.append(j.value)
        result.append(row_data)
    return result


if __name__ == '__main__':
    # load excel file
    wb = xl.load_workbook('test1.xlsx')
    ws1 = wb.worksheets[0]
    ws2 = wb.worksheets[1]

    # get data from the first 2 worksheets
    ws1_rows = get_row_values(ws1)
    ws2_rows = get_row_values(ws2)

    # calculate and make a new sheet
    ws_new = wb.create_sheet('Done')
    # insert header
    ws_new.append(ws1_rows[0])
    for row in range(1, len(ws1_rows)):
        # do the substract cell by cell
        row_data = []
        for column, value in enumerate(ws1_rows[row]):
            if column == 0:
                # insert first column
                row_data.append(value)
            else:
                if ws1_rows[row][0] == ws2_rows[row][0]:
                    # process only when first column match
                    row_data.append(value - ws2_rows[row][column])
        ws_new.append(row_data)
    wb.save('test2.xlsx')

here's my sample excel file

first sheet:

second sheet:

generated sheet:

Collectives™ on Stack Overflow

python excel subtract with 2 worksheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related