In this Python article i want to show you how to How to Read Microsoft Excel Files in Python, basically we are using openpyxl library for this purpose. also you can watch the complete video at the end of this article for Python Read Microsoft Excel Files with openpyxl.
What is openpyxl?
Openpyxl is a Python library that allows for reading and writing Excel files (XLSX) in Python. It enables developers to interact with Excel files programmatically, and this makes it possible to automate tasks such as generating reports, manipulating data, and creating spreadsheets dynamically.
Key features of Openpyxl include:
- Read and Write Excel Files: Openpyxl allows users to read data from existing Excel files and write data to new or existing Excel files.
- Access to Worksheets and Cells: Developers can access individual worksheets inside Excel files and manipulate cell values, styles and formulas programmatically.
- Formatting and Styling: Openpyxl supports different formatting and styling options, including font styles, colors, borders, and alignment settings.
- Charts and Graphs: While primarily focused on reading and writing data, Openpyxl also provides limited support for creating basic charts and graphs within Excel files.
- Cross-Platform Compatibility: Openpyxl is compatible with Windows, macOS, and Linux operating systems, making it accessible for a wide range of developers.
openpyxl Installation
Install openpyxl using pip. It is advisable to do this in a Python virtualenv without system packages:
|
1 |
pip install openpyxl |
Create Excel Workbook with Python
There is no need to create a file on the filesystem to get started with openpyxl. Just import the Workbook class and start work:
|
1 2 |
from openpyxl import Workbook wb = Workbook() |
A workbook is always created with at least one worksheet. You can get it by using the Workbook.active property:
|
1 |
ws = wb.active |
You can create new worksheets using the Workbook.create_sheet() method:
|
1 2 3 4 5 |
ws1 = wb.create_sheet("Mysheet") # insert at the end (default) # or ws2 = wb.create_sheet("Mysheet", 0) # insert at first position # or ws3 = wb.create_sheet("Mysheet", -1) # insert at the penultimate position |
Sheets are given a name automatically when they are created. They are numbered in sequence (Sheet, Sheet1, Sheet2, …). You can change this name at any time with the Worksheet.title property:
|
1 |
ws.title = "New Title" |
The background color of the tab holding this title is white by default. You can change this providing an RRGGBB color code to the Worksheet.sheet_properties.tabColor attribute:
|
1 |
ws.sheet_properties.tabColor = "1072BA" |
Once you gave a worksheet a name, you can get it as a key of the workbook:
|
1 |
ws3 = wb["New Title"] |
You can review the names of all worksheets of the workbook with the Workbook.sheetname attribute
|
1 2 |
print(wb.sheetnames) ['Sheet2', 'New Title', 'Sheet1'] |
You can loop through worksheets
|
1 2 |
for sheet in wb: print(sheet.title) |
You can create copies of worksheets within a single workbook with Workbook.copy_worksheet() method:
|
1 2 |
source = wb.active target = wb.copy_worksheet(source) |
Accessing one cell
Now we know how to get a worksheet, we can start modifying cells content. Cells can be accessed directly as keys of the worksheet:
|
1 |
c = ws['A4'] |
This will return the cell at A4, or create one if it does not exist yet. Values can be directly assigned:
|
1 |
ws['A4'] = 4 |
There is also the Worksheet.cell() method. This provides access to cells using row and column notation:
Accessing many cells
Ranges of cells can be accessed using slicing:
|
1 |
cell_range = ws['A1':'C2'] |
Ranges of rows or columns can be obtained similarly:
|
1 2 3 4 |
colC = ws['C'] col_range = ws['C:D'] row10 = ws[10] row_range = ws[5:10] |
Loading from a file
The same way as writing, you can use the openpyxl.load_workbook() to open an existing workbook:
|
1 2 3 |
from openpyxl import load_workbook wb2 = load_workbook('test.xlsx') print (wb2.sheetnames) |
FAQs:
Can Python create Excel files?
Yes, Python can create Excel files using libraries such as openpyxl, XlsxWriter, pandas and xlwt. These libraries provides different functionalities to create, write and manipulate Excel files directly from Python code.
How do you create and write to Excel in Python?
To create and write to Excel files in Python, you can use libraries like openpyxl, XlsxWriter, or pandas. This is a general approach:
-
- Install the desired library (like openpyxl, XlsxWriter) using pip.
- Import the library in your Python script.
- Create a new Excel workbook or open an existing one.
- Write data to the Excel workbook by accessing worksheets and cells.
- Save the workbook to a specified file path.
How do I create an XLSX file?
For creating an XLSX file in Python, you typically use a library like openpyxl or XlsxWriter. After installing the library, you can create a new Excel workbook object and save it with a .xlsx extension. This is a basic example using openpyxl:
|
1 2 3 4 5 6 7 |
from openpyxl import Workbook # Create a new workbook workbook = Workbook() # Save the workbook to a file workbook.save('example.xlsx') |
How do I write data into an XLSX file in Python?
For writing data into an XLSX file in Python, you can use libraries like openpyxl, XlsxWriter, or pandas. This is a simple example using openpyxl:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
from openpyxl import Workbook # Create a new workbook workbook = Workbook() # Get the active worksheet sheet = workbook.active # Write data to cells sheet['A1'] = 'Hello' sheet['B1'] = 'World' # Save the workbook to a file workbook.save('example.xlsx') |
Learn More:
Subscribe and Get Free Video Courses & Articles in your Email
How can I read a password encrypted excel file using openpylxl?