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


42 thoughts on “How to read/write Excel files in Python”

  1. 835122 918865Come across back yard garden unusual periods of ones Are typically Weight reduction and every 1 1 could be essential. 1 way state could possibly be substantial squandering through the diet. shed weight 835294

  2. 283690 480593You can certainly see your skills inside the function you write. The world hopes for more passionate writers like you who arent afraid to say how they believe. At all times follow your heart 228313

  3. 159187 457691I need to admit that that is 1 wonderful insight. It surely gives a company the opportunity to have in about the ground floor and really take part in making a thing special and tailored to their needs. 356893

  4. 173155 194121I basically could not go away your site prior to suggesting that I in fact enjoyed the standard information an individual supply to your visitors? Is gonna be once again continuously so that you can look at new posts 852196

  5. 864016 6297Howdy just wanted to give you a brief heads up and let you know a few with the pictures arent loading properly. Im not positive why but I believe its a linking concern. Ive tried it in two different web browsers and both show exactly the same outcome. 37065

  6. 248774 153940Im not certain why but this weblog is loading incredibly slow for me. Is anyone else having this issue or is it a issue on my end? Ill check back later and see if the dilemma nonetheless exists. 346380

  7. 305556 407765Spot up for this write-up, I truly feel this outstanding web site requirements a great deal more consideration. Ill much more likely be once once again to read considerably much more, thank you that information. 619682

  8. 92103 462293Thanks for the post. I like your writing style – Im trying to start a weblog myself, I feel I may possibly read thru all your posts for some suggestions! Thanks once far more. 435274

  9. 221005 737256Oh my goodness! a wonderful post dude. Thanks Nevertheless My business is experiencing concern with ur rss . Dont know why Not able to sign up for it. Possibly there is any person obtaining identical rss difficulty? Anyone who knows kindly respond. Thnkx 81698

  10. 741847 865673Reasonably unusual. Is likely to appreciate it for those who add forums or anything, website theme . a tones way for your customer to communicate. Nice task.. 990089

  11. 747844 200947An attention-grabbing dialogue is value comment. I believe which you need to write far more on this matter, it wont be a taboo topic nevertheless usually individuals are not sufficient to speak on such topics. Towards the next. Cheers 864092

Got Something To Say:

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

*