In my formal job, as an analyst (Real estate), I used VBA to control Excel spreadsheets. It is quite convenient to use VBA macro to control Excel. No Env-setting, just save the file with .xlsm and you can simply share the code with the file to your colleagues or friends. However, sometimes I found out that VBA is not good at sorting data lists such as concatenation or format, list method…etc.
So I decided to use Python to control Excel with the library Openpyxl.
Contents
Pre-requirement
- Python installed
- any text editor / or use jupyter notebook
- library “openpyxl” installed — by command line: pip install openpyxl (or pip3 install openpyxl on mac)
- Of course, MS-Excel program
Start
from openpyxl.workbook import Workbook
from openpyxl import load_workbook
# create a workbook obj
wb = Workbook()
#create an active worksheet
ws = wb.active
#load existing spreadsheet, here we are loading the file in the same folder
wb = load_workbook('youFileName.xlsx')
ws = wb.active
First, we need to import the methods. (the first 2 lines) Then create a wb object and active worksheet.
Grab a cell from a spreadsheet
#print something from our spreadsheet
print(ws["A2"].value)
#use python build-in function to play with spreadsheet
print(f'Eng: {ws["A2"].value}, Jap: {ws["B2"].value}')Ouput:
accountant Eng: accountant, Jap: 会計士
Grab a whole column
We can see that the col is saved as tuples. So we can loop it and append them to a list as the image below.
Grab ranges
We can notice that the output is tuples inside a tuple… there are double parentheses.
So we need to for-loop it twice to get the value of the cells.
Iterate Thru Rows(or Cols) via “.iter_rows()”
We are using the method .iter_rows() to iterate. So there are a couple parameters we could put in.
Here is my example, it would loop thru row 1 to 5 on col 1 and getting “.value” only.
- min_row=1
- max_row=5
- min_col=1
- max_col=1
- values_only=True
As you can see from the image above, the output is “value” but in the tuple data type. So we need to for-loop it again.
Change cells and save file
It is really easy to change cells’ value, just simply assign value to the cell. For example:
ws['A2'] = "Hi"
#or
ws.cell(row=3,column=1).value = "Hello"Save the file just simply use “wb.save(‘SavedFileName.xlsx’) ” and it would save the file under the same folder. If you want to save it to another folder, just simply add the absolute path to the folder.
Use Formulas with Python code
It is really simple. Just put the formula including “=” and assign it to the target cell. Such as:
ws['C6'] = "=SUM(C2:C5)"Change Style of the cell
FIrst, we need to import the font, then, use Font() method with the properties we want to change.
#import Font, Border, Side
from openpyxl.styles import Font, Border, Side
c = ws['A1']
c.font = Font(size=30, bold=True, italic=False, color="226622")On the other hand, to play with the border, we need to define a border first by Side() method, then, we use Border() to apply the side we defined to the target cell as the example below:
mybd = Side(style="thick", color="d80d0d")
ws['B3'].border = Border(left=mybd,right=mybd,top=mybd,bottom=mybd)So in practice, we could use if-condition to decide which cell should have what kind of style. Very easy to customize, right?
Conclusion
I guess the basic knowledge of openpyxl will let us cover most of the VBA functions. Especially, we can use powerful Python build-in methods that can let us work much more efficiently.
