Menu
I am in the process of brain storming how to best tackle the below problem. Any input is greatly appreciated.
Sample Excel sheet columns:
Column A | Column B | Column C
Apple | Apple |
Orange | Orange |
Pear | Banana |
I want Excel to tell me whether items in column A and B match or mismatch and display results in column C. The formula I enter in column C would be =IF(A1=B1, "Match", "Mismatch")
On excel, I would just drag the formula to the rest of the cells in column C to apply the formula to them and the result would be:
Column A | Column B | Column C
Apple | Apple | Match
Orange | Orange | Match
Pear | Banana | Mismatch
To automate this using a python script, I tried:
import openpyxl
wb = openpyxl.load_workbook('test.xlsx')
Sheet = wb.get_sheet_by_name('Sheet1')
for cellObj in Sheet.columns[2]:
cellObj.value = '=IF($A$1=$B$1, "Match", "Mismatch")
wb.save('test.xlsx')
This wrote the formula to all cells in column C, however the formula only referenced cell A1 and B1, so result in all cells in column C = Match.
Column A | Column B | Column C
Apple | Apple | Match
Orange | Orange | Match
Pear | Banana | Match
How would you handle this?
335
Many Python users are transitioning from spreadsheets because of a Python package that allows users to use Excel-like syntax. It is a spreadsheet environment for JupyterLab to help you with your Python analysis. Meet Mito — a Python package that initializes an interactive spreadsheet into your JupyterLab Environment.
There are two basic ways to write to a cell: using a key of a worksheet such as A1 or D3, or using a row and column notation with the cell method. In the example, we write two values to two cells. Here, we assing a numerical value to the A1 cell. In this line, we write to cell B2 with the row and column notation.
To work on this Excel sheet we are going to use a library openpyxl. Create a folder in your directory, give it a name and install the openpyxl package by executing the following command in your terminal. Now we can import this package to work on our spreadsheet. Before that add the spreadsheet in your project folder.
Adding formulas We often require formulas to be included in our Excel datasheet. We can easily add formulas using the Openpyxl module just like you add values to a cell. The above program will add the formula (=SUM(A2:A8)) in cell A9. The result will be as below.
fortunately now you can easy do formulas in certain records. Also there are simpler functions to use, such as:
wb.sheetnames instead of wb.read_sheet_names()
sheet = wb['SHEET_NAME'] instead of sheet = wb.get_sheet_by_name('SHEET_NAME')
And formulas can be easily inserted with:
sheet['A1'] = '=SUM(1+1)'
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
