How to read/write Excel files in Python

Python-Read-Write-Excel-Files

In this tutorial, we’re gonna look at way to use openpyxl module to read, write Excel spreadsheet files in Python program.

Excel spreadsheet files

An Excel file is called a workbook which is saved on PC as .xlsx extension.
Each workbook contains one or more sheets.
Each sheet has columns (letters: A, B, C…) and rows (numbers: 1, 2, 3…).
A box addressed by [column, row] pair is called a cell.

read-write-excel-files-in-python-openpyxl-module-excel-file

Read/Write Excel files in Python

Install openpyxl module

Open cmd, then run:
pip install openpyxl

Once the installation is successful, we can see openpyxl folder at Python\Python[version]\Lib\site-packages.
(In this tutorial, we use openpyxl 2.5.12)

Now we can import the module by running import openpyxl.

Read Excel file
Open workbook

We use openpyxl.load_workbook(filename) function with input filename to get a Workbook object that represents the Excel file.

Get sheets

We call Workbook’s get_sheet_names() method to get list of all the sheet names in the workbook.

Then we can obtain a Worksheet object with the sheet name using Workbook’s get_sheet_by_name() method, or we can get the Workbook’s active sheet using get_active_sheet() method.

We can get size of Worksheet object using its max_row and max_column attributes.

Get cells

With a Worksheet object, we can access Cell objects inside.

After column Z, the columns continues with two letters: AA, AB… To make things simple, we can also get a Cell using Worksheet’s cell() method with input integers for row and column arguments.

*Note: The first row or column integer is 1.

We can also accessCell object location information with its row, column, and coordinate attributes.

We can also slice Worksheet object to get list of Cell objects in a rectangular area:

For cells in a particular row or column, we can also use a Worksheet object’s rows and columns attribute.

Write Excel file
Create workbook

We use openpyxl.Workbook() function that returns a new blank Workbook object.

Create & Remove sheet

We use Workbook’s create_sheet() method to create a new Worksheet object.
By default, it is set to be the last sheet in the workbook named SheetX.

We can also specify the index and name of the new sheet by passing them as arguments to code>create_sheet() method.

To remove a Worksheet object from a Workbook, we use remove_sheet() method and pass that Worksheet object as argument (not the sheet name).

Write value to cells

It is just like writing values to keys in a Python dictionary. In this case, key are cell’s coordinate.

Save workbook

All the changes we have made on Workbook object will not be saved until we call Workbook’s save() method.

Whenever we edit a file, we should always save the new, edited one to a different filename than the original. Passing a different filename to save() method (such as 'grokonez_copy_1.xlsx') will save the changes to a copy of the file.

Setting cells

Font object

To customize font styles in cells, we import Font from the openpyxl.styles module.

Formula

Adding formulas to cells programmatically is just like any normal value.

Size of cells

We can set row_dimensions and column_dimensions attributes value of Worksheet object to change row heights (using numbers) and column widths (using letters).

read-write-excel-files-in-python-openpyxl-module-excel-file-change-size-cell

Merge and Unmerge cells

We can use WorkSheet‘s merge_cells() method to merge a rectangular area into a single cell.


read-write-excel-files-in-python-openpyxl-module-excel-file-change-merge-cells

By grokonez | January 25, 2019.


Related Posts


1 thought on “How to read/write Excel files in Python”

Got Something To Say:

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

*