02.06.2018

The elegance of Python: Interacting with Excel

 

python+excel

Python + Excel = True? Do you have a huge Excel spreadsheet with a lot of data that you need have sorted in a special way? Do you feel that you would need to assign one of your staff members to update the Excel spreadsheet for two months? That is when you need Python to interact with your spreadsheet! Here we are going to cover how to connect to an Excel spreadsheet with OpenPyXl.If you haven't done so already, install OpenPyXl with pip by writing this in your cmd/terminal: py -m pip install openpyxl
Create a file named "my_test_excel_file.xlsx" and then write the following code in your script:

#! python3
import openpyxl
wb_name = "my_test_excel_file.xlsx"
wb = openpyxl.load_workbook(wb_name)
sheet = wb.get_sheet_by_name("Sheet1")
sheet.cell(row=1, column = 1).value = "I changed this cell value with Python!"
wb.save(wb_name)

Try it out, run the script and then open the file, voilà! Cell A1's value is now changed!
Let us walk through the code. First, we import openpyxl and then we specify the workbook name. We then open the workbook with openpyxl. We tell openpyxl that we want to work with Sheet1. Then we change the value of cell A1 (R1C1 notation) and save the workbook. Make sure that you have the workbook closed when you run the script, otherwise openpyxl cannot save the changes.

 

Leave a Reply

Your email address will not be published.

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram