Python Script: Read Excel .xlsx file (Source code)


Why Do I Need to Read Excel Files in Python?

If you’re a Python programmer, you may need to read an Excel file sooner or later. For example, maybe you’re building a tool that analyzes sales data from an online store. To do this analysis, you’ll need to be able to read data from an Excel spreadsheet and store it in a format that’s easy for your Python code to work with. This is where the openpyxl package comes in.

In this blog post, we’ll show you how to read XLSX files in Python using the openpyxl package. We’ll walk you through every step of the process, from installing the openpyxl package to reading and writing Excel files. Let’s get started!

Installing openpyxl

Before we can start working with Excel files in Python, we first need to install the openpyxl package. We can do this using pip, the Python package manager. To install openpyxl using pip, simply type the following into your terminal:

$pip install openpyxl
Code language: PHP (php)

Reading Excel Files

Once we have the openpyxl package installed, we can start reading Excel files. To do this, we’ll use the load_workbook() function. This function accepts a single parameter, which is the filename of the Excel file that you want to read. For example, if we wanted to read an Excel file named “example.xlsx”, we would use the following code:

from openpyxl import load_workbook wb = load_workbook('example.xlsx')
Code language: JavaScript (javascript)

Writing Excel Files

We can also use openpyxl to write Excel files. To do this, we’ll use the save() method. This method takes two parameters: the filename of the file you want to write and a Workbook object that represents the contents of the file you want to write. For example, if we wanted to create an Excel file named “output.xlsx” with a single sheet containing the text “Hello, world!”, we could use the following code:

from openpyxl import Workbook wb = Workbook() ws = wb.create_sheet('Sheet1') # Creates a new sheet named Sheet1 ws['A1'] = 'Hello, world!' # writes Hello, world! in cell A1 wb.save('output.xlsx') # saves changes made to output.xlsx
Code language: Python (python)

Reading an Excel (.xlsx) file into a dictionary in Python

import openpyxl workbook = openpyxl.load_workbook("EXAMPLE.xlsx") sheet = workbook.active first_row = [] # The row where we stock the name of the column for col in range(1, sheet.max_column+1): first_row.append(sheet.cell(row=1, column=col).value) data =[] for row in range(2, sheet.max_row+1): elm = {} for col in range(1, sheet.max_column+1): elm[first_row[col-1]]=sheet.cell(row=row,column=col).value data.append(elm) print (data)
Code language: Python (python)

Conclusion:

We’ve shown you how to read and write XLSX files in Python using the openpyxl package. We hope you found this tutorial helpful!

Andy Avery

I really enjoy helping people with their tech problems to make life easier, ​and that’s what I’ve been doing professionally for the past decade.

Recent Posts