How to read / write and create excel easily with Python?

Prerequisites:

  1. Ensure you have Python 3 install at your environment

In order to interact with excel file with python, we need to install openpyxl. Run command below in terminal / cmd to install the library.

pip install openpyxl

Script 1: Read excel file

  1. Create a simple excel file “sample1.xlsx” with content above and saved it in my project directory
  2. Create a new python script as “read_excel.py”
  3. This script will read and print out content to python console. Please refer to script as per below:
from openpyxl import load_workbook

if __name__ == "__main__":
    wb = load_workbook(filename="sample1.xlsx", read_only=True)
    ws = wb["Sheet1"] # reading from Sheet1
    for i, row in enumerate(ws.rows):
        if i > 0:
            city_id = row[0].value
            city_name = row[1].value
            print("id: {} | city name: {}".format(city_id, city_name))

Script 2: Write excel file

  1. This script will edit “sample1.xlsx” created previously and append some data inside the file
  2. Please refer to script as per below:
from openpyxl import load_workbook

if __name__ == "__main__":
    wb = load_workbook(filename="sample1.xlsx", read_only=False)
    ws = wb["Sheet1"]
    # add Shah Alam
    ws["A7"] = 6
    ws["B7"] = "Shah Alam"

    # add Georgetown
    ws["A8"] = 7
    ws["B8"] = "Georgetown"
    wb.save("sample1.xlsx") # save to original file name

Script 3: Create new excel file

  1. This script will create new excel file “sample2.xlsx” and write random data to multiple worksheet
  2. Please refer to script as per below:
from openpyxl import Workbook

if __name__ == "__main__":
wb = Workbook(write_only=True)

# create state's worksheet and add random data
state_ws = wb.create_sheet("Malaysia State")
# state header
state_ws.append(["id", "State Name"])
# state data
state_ws.append([1, "Sarawak"])
state_ws.append([2, "Sabah"])
state_ws.append([3, "Perlis"])
state_ws.append([4, "Penang"])

# create city's worksheet and add random data
city_ws = wb.create_sheet("Malaysia City")
# city header
city_ws.append(["id", "City Name"])
# city data
city_ws.append([1, "Kuching"])
city_ws.append([2, "Sibu"])
city_ws.append([3, "Miri"])
city_ws.append([4, "Kota Kinabalu"])

#save file
wb.save("sample2.xlsx")

Please refer to screenshot for newly created file below:

Feel free to check my Github repo for this post.
Let me know if you have any enquiries.
Happy coding 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *