Python Read All Existing Cells in Excel
Watch Now This tutorial has a related video grade created by the Real Python squad. Picket information technology together with the written tutorial to deepen your agreement: Editing Excel Spreadsheets in Python With openpyxl
Excel spreadsheets are 1 of those things you might have to deal with at some point. Either it's because your dominate loves them or because marketing needs them, you might accept to learn how to piece of work with spreadsheets, and that's when knowing openpyxl comes in handy!
Spreadsheets are a very intuitive and user-friendly way to manipulate large datasets without any prior technical background. That's why they're still so commonly used today.
In this commodity, you'll larn how to apply openpyxl to:
- Manipulate Excel spreadsheets with confidence
- Extract data from spreadsheets
- Create simple or more circuitous spreadsheets, including adding styles, charts, and so on
This commodity is written for intermediate developers who have a pretty good knowledge of Python data structures, such as dicts and lists, but also experience comfortable around OOP and more intermediate level topics.
Before You Begin
If y'all ever become asked to excerpt some data from a database or log file into an Excel spreadsheet, or if you oft have to catechumen an Excel spreadsheet into some more than usable programmatic form, then this tutorial is perfect for you. Let's jump into the openpyxl caravan!
Practical Use Cases
First things first, when would y'all need to utilize a bundle similar openpyxl in a real-globe scenario? You'll encounter a few examples below, but really, there are hundreds of possible scenarios where this knowledge could come in handy.
Importing New Products Into a Database
Y'all are responsible for tech in an online store company, and your boss doesn't desire to pay for a cool and expensive CMS system.
Every fourth dimension they want to add new products to the online shop, they come up to you with an Excel spreadsheet with a few hundred rows and, for each of them, y'all have the product name, description, price, and so forth.
At present, to import the information, you'll take to iterate over each spreadsheet row and add each product to the online store.
Exporting Database Data Into a Spreadsheet
Say you have a Database table where you record all your users' data, including name, telephone number, email address, and and so forth.
Now, the Marketing squad wants to contact all users to give them some discounted offer or promotion. However, they don't have access to the Database, or they don't know how to use SQL to excerpt that information easily.
What tin you practice to help? Well, you can brand a quick script using openpyxl that iterates over every single User record and puts all the essential information into an Excel spreadsheet.
That'southward gonna earn you an extra slice of cake at your company's next birthday political party!
Appending Information to an Existing Spreadsheet
You may also have to open a spreadsheet, read the data in it and, according to some concern logic, append more data to it.
For example, using the online shop scenario again, say you go an Excel spreadsheet with a list of users and you need to append to each row the total amount they've spent in your shop.
This data is in the Database and, in social club to exercise this, you have to read the spreadsheet, iterate through each row, fetch the total amount spent from the Database and then write back to the spreadsheet.
Not a problem for openpyxl!
Learning Some Basic Excel Terminology
Here's a quick list of basic terms yous'll see when you're working with Excel spreadsheets:
| Term | Caption |
|---|---|
| Spreadsheet or Workbook | A Spreadsheet is the master file you are creating or working with. |
| Worksheet or Sail | A Sheet is used to split up unlike kinds of content within the same spreadsheet. A Spreadsheet tin accept 1 or more Sheets. |
| Column | A Cavalcade is a vertical line, and information technology's represented by an capital letter of the alphabet: A. |
| Row | A Row is a horizontal line, and it's represented past a number: 1. |
| Jail cell | A Cell is a combination of Column and Row, represented by both an capital letter and a number: A1. |
Getting Started With openpyxl
Now that y'all're aware of the benefits of a tool like openpyxl, permit's get down to information technology and offset by installing the parcel. For this tutorial, you should use Python three.seven and openpyxl 2.6.ii. To install the package, yous can do the following:
Subsequently y'all install the package, yous should be able to create a super unproblematic spreadsheet with the following code:
from openpyxl import Workbook workbook = Workbook () sheet = workbook . active sheet [ "A1" ] = "howdy" sail [ "B1" ] = "world!" workbook . save ( filename = "hello_world.xlsx" ) The lawmaking above should create a file chosen hello_world.xlsx in the folder you are using to run the code. If you lot open that file with Excel you should see something similar this:
Woohoo, your kickoff spreadsheet created!
Reading Excel Spreadsheets With openpyxl
Let's showtime with the most essential thing ane can do with a spreadsheet: read it.
Yous'll become from a straightforward arroyo to reading a spreadsheet to more circuitous examples where yous read the information and convert it into more useful Python structures.
Dataset for This Tutorial
Earlier you lot swoop deep into some lawmaking examples, you should download this sample dataset and shop information technology somewhere equally sample.xlsx:
This is one of the datasets you'll be using throughout this tutorial, and it'southward a spreadsheet with a sample of real information from Amazon'south online product reviews. This dataset is only a tiny fraction of what Amazon provides, but for testing purposes, it's more than enough.
A Unproblematic Approach to Reading an Excel Spreadsheet
Finally, permit'south first reading some spreadsheets! To brainstorm with, open our sample spreadsheet:
>>>
>>> from openpyxl import load_workbook >>> workbook = load_workbook ( filename = "sample.xlsx" ) >>> workbook . sheetnames ['Sheet 1'] >>> canvas = workbook . agile >>> sheet <Worksheet "Sail 1"> >>> sail . title 'Sail ane' In the code above, you first open the spreadsheet sample.xlsx using load_workbook(), then y'all can use workbook.sheetnames to meet all the sheets you have bachelor to work with. After that, workbook.active selects the first available canvass and, in this case, you tin run into that it selects Sail 1 automatically. Using these methods is the default way of opening a spreadsheet, and you'll see it many times during this tutorial.
Now, afterwards opening a spreadsheet, you can easily recollect data from it like this:
>>>
>>> sail [ "A1" ] <Cell 'Sail 1'.A1> >>> canvass [ "A1" ] . value 'marketplace' >>> canvass [ "F10" ] . value "G-Shock Men's Grey Sport Picket" To render the actual value of a cell, you need to do .value. Otherwise, you'll get the principal Jail cell object. You tin likewise use the method .cell() to retrieve a prison cell using index annotation. Think to add .value to become the actual value and not a Cell object:
>>>
>>> sail . cell ( row = 10 , cavalcade = vi ) <Cell 'Canvass 1'.F10> >>> sheet . prison cell ( row = 10 , column = 6 ) . value "Thousand-Shock Men's Grey Sport Picket" You can see that the results returned are the same, no matter which manner you determine to go with. However, in this tutorial, you'll be by and large using the offset approach: ["A1"].
The above shows y'all the quickest way to open up a spreadsheet. Yet, y'all can pass boosted parameters to change the way a spreadsheet is loaded.
Additional Reading Options
There are a few arguments you can pass to load_workbook() that alter the way a spreadsheet is loaded. The most important ones are the following two Booleans:
- read_only loads a spreadsheet in read-simply mode allowing you lot to open up very large Excel files.
- data_only ignores loading formulas and instead loads only the resulting values.
Importing Data From a Spreadsheet
Now that you've learned the basics almost loading a spreadsheet, it's virtually time yous get to the fun part: the iteration and actual usage of the values inside the spreadsheet.
This department is where you'll larn all the unlike ways you can iterate through the data, only as well how to catechumen that data into something usable and, more importantly, how to practice information technology in a Pythonic way.
Iterating Through the Information
At that place are a few different ways y'all can iterate through the information depending on your needs.
You can slice the data with a combination of columns and rows:
>>>
>>> sheet [ "A1:C2" ] ((<Prison cell 'Canvass 1'.A1>, <Jail cell 'Sheet 1'.B1>, <Cell 'Sail ane'.C1>), (<Cell 'Sheet 1'.A2>, <Jail cell 'Sheet i'.B2>, <Cell 'Sheet i'.C2>)) You can get ranges of rows or columns:
>>>
>>> # Get all cells from cavalcade A >>> canvas [ "A" ] (<Cell 'Sheet 1'.A1>, <Prison cell 'Sheet 1'.A2>, ... <Prison cell 'Sheet 1'.A99>, <Cell 'Sheet 1'.A100>) >>> # Become all cells for a range of columns >>> sheet [ "A:B" ] ((<Cell 'Sheet 1'.A1>, <Prison cell 'Sheet one'.A2>, ... <Cell 'Sheet i'.A99>, <Prison cell 'Sail 1'.A100>), (<Cell 'Sheet 1'.B1>, <Prison cell 'Canvass 1'.B2>, ... <Cell 'Sail 1'.B99>, <Cell 'Sheet one'.B100>)) >>> # Get all cells from row 5 >>> canvas [ 5 ] (<Cell 'Sail one'.A5>, <Cell 'Sheet 1'.B5>, ... <Cell 'Canvas 1'.N5>, <Cell 'Sheet 1'.O5>) >>> # Get all cells for a range of rows >>> sheet [ 5 : 6 ] ((<Cell 'Sheet 1'.A5>, <Prison cell 'Sheet 1'.B5>, ... <Jail cell 'Sheet ane'.N5>, <Cell 'Sheet 1'.O5>), (<Cell 'Canvas 1'.A6>, <Prison cell 'Sheet 1'.B6>, ... <Jail cell 'Canvass 1'.N6>, <Cell 'Canvass ane'.O6>)) You'll notice that all of the above examples return a tuple. If you want to refresh your retentivity on how to handle tuples in Python, check out the article on Lists and Tuples in Python.
In that location are also multiple ways of using normal Python generators to go through the data. The main methods yous can utilize to achieve this are:
-
.iter_rows() -
.iter_cols()
Both methods can receive the following arguments:
-
min_row -
max_row -
min_col -
max_col
These arguments are used to set boundaries for the iteration:
>>>
>>> for row in sheet . iter_rows ( min_row = 1 , ... max_row = two , ... min_col = 1 , ... max_col = three ): ... print ( row ) (<Prison cell 'Sheet 1'.A1>, <Cell 'Sail 1'.B1>, <Prison cell 'Canvas 1'.C1>) (<Prison cell 'Sheet i'.A2>, <Prison cell 'Sheet 1'.B2>, <Prison cell 'Sheet ane'.C2>) >>> for cavalcade in sheet . iter_cols ( min_row = 1 , ... max_row = 2 , ... min_col = 1 , ... max_col = 3 ): ... print ( column ) (<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>) (<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>) (<Cell 'Sheet i'.C1>, <Jail cell 'Canvas ane'.C2>) You'll notice that in the first case, when iterating through the rows using .iter_rows(), yous get one tuple element per row selected. While when using .iter_cols() and iterating through columns, you'll get one tuple per column instead.
I additional statement you can pass to both methods is the Boolean values_only. When it's fix to True, the values of the jail cell are returned, instead of the Cell object:
>>>
>>> for value in sail . iter_rows ( min_row = one , ... max_row = 2 , ... min_col = ane , ... max_col = 3 , ... values_only = Truthful ): ... impress ( value ) ('marketplace', 'customer_id', 'review_id') ('US', 3653882, 'R3O9SGZBVQBV76') If yous want to iterate through the whole dataset, and so you tin likewise utilize the attributes .rows or .columns direct, which are shortcuts to using .iter_rows() and .iter_cols() without whatever arguments:
>>>
>>> for row in sheet . rows : ... print ( row ) (<Cell 'Sheet ane'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Canvas 1'.C1> ... <Cell 'Sheet one'.M100>, <Prison cell 'Sheet one'.N100>, <Cell 'Sheet i'.O100>) These shortcuts are very useful when you lot're iterating through the whole dataset.
Manipulate Information Using Python's Default Data Structures
Now that you know the basics of iterating through the data in a workbook, allow'south expect at smart ways of converting that data into Python structures.
As yous saw earlier, the result from all iterations comes in the class of tuples. However, since a tuple is nothing more than than an immutable list, you lot can easily access its data and transform it into other structures.
For example, say yous desire to extract production information from the sample.xlsx spreadsheet and into a dictionary where each cardinal is a product ID.
A straightforward way to exercise this is to iterate over all the rows, selection the columns you lot know are related to product information, and so store that in a lexicon. Let's code this out!
First of all, have a await at the headers and encounter what information you care almost about:
>>>
>>> for value in sheet . iter_rows ( min_row = 1 , ... max_row = ane , ... values_only = True ): ... print ( value ) ('marketplace', 'customer_id', 'review_id', 'product_id', ...) This code returns a list of all the column names you have in the spreadsheet. To start, grab the columns with names:
-
product_id -
product_parent -
product_title -
product_category
Lucky for y'all, the columns you demand are all next to each other so you tin can use the min_column and max_column to easily go the data you lot want:
>>>
>>> for value in canvass . iter_rows ( min_row = 2 , ... min_col = iv , ... max_col = seven , ... values_only = True ): ... print ( value ) ('B00FALQ1ZC', 937001370, 'Invicta Women\'s 15150 "Angel" 18k Yellow...) ('B00D3RGO20', 484010722, "Kenneth Cole New York Women'south KC4944...) ... Nice! At present that you know how to get all the important production information you need, permit's put that information into a dictionary:
import json from openpyxl import load_workbook workbook = load_workbook ( filename = "sample.xlsx" ) sheet = workbook . active products = {} # Using the values_only because yous want to return the cells' values for row in sheet . iter_rows ( min_row = 2 , min_col = iv , max_col = vii , values_only = True ): product_id = row [ 0 ] product = { "parent" : row [ 1 ], "championship" : row [ two ], "category" : row [ 3 ] } products [ product_id ] = product # Using json here to be able to format the output for displaying later impress ( json . dumps ( products )) The code to a higher place returns a JSON similar to this:
{ "B00FALQ1ZC" : { "parent" : 937001370 , "championship" : "Invicta Women'south 15150 ..." , "category" : "Watches" }, "B00D3RGO20" : { "parent" : 484010722 , "title" : "Kenneth Cole New York ..." , "category" : "Watches" } } Here you tin can meet that the output is trimmed to 2 products only, just if yous run the script as it is, and then you should get 98 products.
Convert Information Into Python Classes
To finalize the reading section of this tutorial, let'south dive into Python classes and run into how you could improve on the case above and better structure the data.
For this, you'll be using the new Python Information Classes that are available from Python 3.7. If you lot're using an older version of Python, and then you can use the default Classes instead.
So, get-go things first, permit'southward look at the data you have and determine what you want to store and how you want to store it.
Every bit y'all saw right at the start, this data comes from Amazon, and it's a listing of product reviews. You lot can check the list of all the columns and their meaning on Amazon.
At that place are two significant elements you tin can extract from the data bachelor:
- Products
- Reviews
A Product has:
- ID
- Title
- Parent
- Category
The Review has a few more fields:
- ID
- Customer ID
- Stars
- Headline
- Trunk
- Date
You tin ignore a few of the review fields to brand things a flake simpler.
So, a straightforward implementation of these two classes could exist written in a separate file classes.py:
import datetime from dataclasses import dataclass @dataclass class Product : id : str parent : str title : str category : str @dataclass class Review : id : str customer_id : str stars : int headline : str torso : str engagement : datetime . datetime Subsequently defining your data classes, yous need to catechumen the data from the spreadsheet into these new structures.
Earlier doing the conversion, it'south worth looking at our header again and creating a mapping betwixt columns and the fields you lot need:
>>>
>>> for value in sail . iter_rows ( min_row = 1 , ... max_row = ane , ... values_only = True ): ... print ( value ) ('marketplace', 'customer_id', 'review_id', 'product_id', ...) >>> # Or an alternative >>> for cell in sheet [ one ]: ... print ( cell . value ) marketplace customer_id review_id product_id product_parent ... Permit's create a file mapping.py where you accept a list of all the field names and their column location (zero-indexed) on the spreadsheet:
# Product fields PRODUCT_ID = 3 PRODUCT_PARENT = 4 PRODUCT_TITLE = 5 PRODUCT_CATEGORY = 6 # Review fields REVIEW_ID = 2 REVIEW_CUSTOMER = 1 REVIEW_STARS = seven REVIEW_HEADLINE = 12 REVIEW_BODY = 13 REVIEW_DATE = 14 You don't necessarily have to do the mapping above. It'south more for readability when parsing the row information, and then you don't end upward with a lot of magic numbers lying around.
Finally, let'due south look at the code needed to parse the spreadsheet data into a list of production and review objects:
from datetime import datetime from openpyxl import load_workbook from classes import Product , Review from mapping import PRODUCT_ID , PRODUCT_PARENT , PRODUCT_TITLE , \ PRODUCT_CATEGORY , REVIEW_DATE , REVIEW_ID , REVIEW_CUSTOMER , \ REVIEW_STARS , REVIEW_HEADLINE , REVIEW_BODY # Using the read_only method since you're not gonna be editing the spreadsheet workbook = load_workbook ( filename = "sample.xlsx" , read_only = True ) sheet = workbook . active products = [] reviews = [] # Using the values_only because y'all just desire to return the jail cell value for row in sheet . iter_rows ( min_row = 2 , values_only = True ): production = Production ( id = row [ PRODUCT_ID ], parent = row [ PRODUCT_PARENT ], title = row [ PRODUCT_TITLE ], category = row [ PRODUCT_CATEGORY ]) products . append ( product ) # You need to parse the appointment from the spreadsheet into a datetime format spread_date = row [ REVIEW_DATE ] parsed_date = datetime . strptime ( spread_date , "%Y-%m- %d " ) review = Review ( id = row [ REVIEW_ID ], customer_id = row [ REVIEW_CUSTOMER ], stars = row [ REVIEW_STARS ], headline = row [ REVIEW_HEADLINE ], body = row [ REVIEW_BODY ], appointment = parsed_date ) reviews . append ( review ) print ( products [ 0 ]) print ( reviews [ 0 ]) Subsequently you lot run the code above, you should get some output like this:
Production ( id = 'B00FALQ1ZC' , parent = 937001370 , ... ) Review ( id = 'R3O9SGZBVQBV76' , customer_id = 3653882 , ... ) That's it! Now you should have the data in a very simple and digestible class format, and yous can beginning thinking of storing this in a Database or any other type of data storage you similar.
Using this kind of OOP strategy to parse spreadsheets makes handling the data much simpler subsequently on.
Appending New Data
Before y'all get-go creating very complex spreadsheets, have a quick look at an example of how to append data to an existing spreadsheet.
Get dorsum to the get-go example spreadsheet you lot created (hello_world.xlsx) and try opening it and appending some data to it, like this:
from openpyxl import load_workbook # Start past opening the spreadsheet and selecting the main canvas workbook = load_workbook ( filename = "hello_world.xlsx" ) sheet = workbook . active # Write what y'all want into a specific prison cell sail [ "C1" ] = "writing ;)" # Save the spreadsheet workbook . save ( filename = "hello_world_append.xlsx" ) Et voilà, if you open the new hello_world_append.xlsx spreadsheet, you'll run into the following change:
Notice the additional writing ;) on cell C1.
Writing Excel Spreadsheets With openpyxl
There are a lot of different things you can write to a spreadsheet, from uncomplicated text or number values to complex formulas, charts, or fifty-fifty images.
Permit's showtime creating some spreadsheets!
Creating a Simple Spreadsheet
Previously, you saw a very quick example of how to write "Hello world!" into a spreadsheet, and so you lot tin start with that:
1 from openpyxl import Workbook 2 iii filename = "hello_world.xlsx" iv 5 workbook = Workbook () half-dozen sheet = workbook . active vii eight canvas [ "A1" ] = "hullo" nine sheet [ "B1" ] = "world!" 10 eleven workbook . save ( filename = filename ) The highlighted lines in the lawmaking higher up are the most important ones for writing. In the code, y'all tin see that:
- Line 5 shows you how to create a new empty workbook.
- Lines 8 and 9 evidence yous how to add data to specific cells.
- Line eleven shows you how to save the spreadsheet when you're done.
Even though these lines above tin can exist straightforward, it'southward however practiced to know them well for when things become a scrap more complicated.
Ane thing yous tin can practise to help with coming code examples is add the following method to your Python file or panel:
>>>
>>> def print_rows (): ... for row in sail . iter_rows ( values_only = True ): ... impress ( row ) It makes it easier to impress all of your spreadsheet values by merely calling print_rows().
Basic Spreadsheet Operations
Before you get into the more advanced topics, it'south good for you to know how to manage the most simple elements of a spreadsheet.
Adding and Updating Cell Values
You already learned how to add values to a spreadsheet like this:
>>>
>>> sail [ "A1" ] = "value" There's another way y'all can do this, by first selecting a cell so irresolute its value:
>>>
>>> cell = sheet [ "A1" ] >>> cell <Prison cell 'Sheet'.A1> >>> cell . value 'hello' >>> cell . value = "hey" >>> cell . value 'hey' The new value is but stored into the spreadsheet once you telephone call workbook.save().
The openpyxl creates a prison cell when adding a value, if that jail cell didn't exist before:
>>>
>>> # Before, our spreadsheet has merely i row >>> print_rows () ('how-do-you-do', 'world!') >>> # Try adding a value to row 10 >>> sheet [ "B10" ] = "test" >>> print_rows () ('howdy', 'world!') (None, None) (None, None) (None, None) (None, None) (None, None) (None, None) (None, None) (None, None) (None, 'exam') Every bit you can run into, when trying to add a value to cell B10, you lot end upwards with a tuple with 10 rows, simply then you lot tin accept that test value.
Managing Rows and Columns
One of the most mutual things you have to do when manipulating spreadsheets is adding or removing rows and columns. The openpyxl parcel allows you to do that in a very straightforward mode past using the methods:
-
.insert_rows() -
.delete_rows() -
.insert_cols() -
.delete_cols()
Every single one of those methods tin receive two arguments:
-
idx -
amount
Using our basic hello_world.xlsx example again, permit's come across how these methods work:
>>>
>>> print_rows () ('hello', 'world!') >>> # Insert a cavalcade before the existing column i ("A") >>> sheet . insert_cols ( idx = 1 ) >>> print_rows () (None, 'hello', 'earth!') >>> # Insert 5 columns between cavalcade 2 ("B") and 3 ("C") >>> canvass . insert_cols ( idx = 3 , corporeality = v ) >>> print_rows () (None, 'hello', None, None, None, None, None, 'earth!') >>> # Delete the created columns >>> canvass . delete_cols ( idx = 3 , amount = 5 ) >>> sheet . delete_cols ( idx = one ) >>> print_rows () ('hello', 'world!') >>> # Insert a new row in the beginning >>> sail . insert_rows ( idx = 1 ) >>> print_rows () (None, None) ('hello', 'world!') >>> # Insert iii new rows in the kickoff >>> canvass . insert_rows ( idx = ane , amount = 3 ) >>> print_rows () (None, None) (None, None) (None, None) (None, None) ('hello', 'world!') >>> # Delete the commencement 4 rows >>> sheet . delete_rows ( idx = 1 , amount = 4 ) >>> print_rows () ('hello', 'world!') The but thing you demand to remember is that when inserting new data (rows or columns), the insertion happens before the idx parameter.
So, if you exercise insert_rows(1), it inserts a new row before the existing outset row.
It'due south the same for columns: when y'all call insert_cols(ii), it inserts a new column correct earlier the already existing second cavalcade (B).
Withal, when deleting rows or columns, .delete_... deletes data starting from the alphabetize passed as an argument.
For example, when doing delete_rows(2) it deletes row 2, and when doing delete_cols(3) it deletes the third cavalcade (C).
Managing Sheets
Sheet management is also one of those things you might need to know, even though it might be something that you don't utilise that often.
If you look back at the code examples from this tutorial, you lot'll notice the post-obit recurring piece of code:
This is the way to select the default sheet from a spreadsheet. Still, if you're opening a spreadsheet with multiple sheets, then you tin can always select a specific one like this:
>>>
>>> # Let's say you have two sheets: "Products" and "Visitor Sales" >>> workbook . sheetnames ['Products', 'Visitor Sales'] >>> # Y'all tin select a sail using its title >>> products_sheet = workbook [ "Products" ] >>> sales_sheet = workbook [ "Company Sales" ] Y'all can also alter a sheet championship very hands:
>>>
>>> workbook . sheetnames ['Products', 'Company Sales'] >>> products_sheet = workbook [ "Products" ] >>> products_sheet . title = "New Products" >>> workbook . sheetnames ['New Products', 'Company Sales'] If you desire to create or delete sheets, then you can also do that with .create_sheet() and .remove():
>>>
>>> workbook . sheetnames ['Products', 'Company Sales'] >>> operations_sheet = workbook . create_sheet ( "Operations" ) >>> workbook . sheetnames ['Products', 'Visitor Sales', 'Operations'] >>> # You lot can as well ascertain the position to create the sheet at >>> hr_sheet = workbook . create_sheet ( "Hr" , 0 ) >>> workbook . sheetnames ['HR', 'Products', 'Company Sales', 'Operations'] >>> # To remove them, simply pass the sheet as an argument to the .remove() >>> workbook . remove ( operations_sheet ) >>> workbook . sheetnames ['Hr', 'Products', 'Company Sales'] >>> workbook . remove ( hr_sheet ) >>> workbook . sheetnames ['Products', 'Company Sales'] One other thing you can do is make duplicates of a sail using copy_worksheet():
>>>
>>> workbook . sheetnames ['Products', 'Visitor Sales'] >>> products_sheet = workbook [ "Products" ] >>> workbook . copy_worksheet ( products_sheet ) <Worksheet "Products Copy"> >>> workbook . sheetnames ['Products', 'Visitor Sales', 'Products Copy'] If you open up your spreadsheet after saving the higher up code, you'll find that the sheet Products Copy is a duplicate of the sheet Products.
Freezing Rows and Columns
Something that y'all might want to do when working with big spreadsheets is to freeze a few rows or columns, so they remain visible when you scroll right or down.
Freezing data allows y'all to keep an middle on important rows or columns, regardless of where you curl in the spreadsheet.
Again, openpyxl also has a way to accomplish this by using the worksheet freeze_panes aspect. For this example, get dorsum to our sample.xlsx spreadsheet and endeavor doing the following:
>>>
>>> workbook = load_workbook ( filename = "sample.xlsx" ) >>> sheet = workbook . active >>> sail . freeze_panes = "C2" >>> workbook . save ( "sample_frozen.xlsx" ) If you open the sample_frozen.xlsx spreadsheet in your favorite spreadsheet editor, you'll observe that row i and columns A and B are frozen and are always visible no matter where yous navigate within the spreadsheet.
This feature is handy, for instance, to keep headers inside sight, so you lot always know what each column represents.
Here's how it looks in the editor:
Discover how you're at the cease of the spreadsheet, and all the same, you tin can run across both row one and columns A and B.
Adding Filters
You lot tin can use openpyxl to add filters and sorts to your spreadsheet. Still, when yous open the spreadsheet, the information won't be rearranged according to these sorts and filters.
At beginning, this might seem like a pretty useless feature, but when you're programmatically creating a spreadsheet that is going to exist sent and used by somebody else, it's yet nice to at to the lowest degree create the filters and allow people to use it afterward.
The code below is an case of how you would add some filters to our existing sample.xlsx spreadsheet:
>>>
>>> # Bank check the used spreadsheet space using the attribute "dimensions" >>> sheet . dimensions 'A1:O100' >>> sheet . auto_filter . ref = "A1:O100" >>> workbook . save ( filename = "sample_with_filters.xlsx" ) You should now come across the filters created when opening the spreadsheet in your editor:
You don't have to apply sheet.dimensions if you know precisely which office of the spreadsheet y'all want to utilise filters to.
Adding Formulas
Formulas (or formulae) are one of the almost powerful features of spreadsheets.
They gives you the power to apply specific mathematical equations to a range of cells. Using formulas with openpyxl is as uncomplicated as editing the value of a jail cell.
Yous can see the list of formulas supported by openpyxl:
>>>
>>> from openpyxl.utils import FORMULAE >>> FORMULAE frozenset({'ABS', 'ACCRINT', 'ACCRINTM', 'ACOS', 'ACOSH', 'AMORDEGRC', 'AMORLINC', 'AND', ... 'YEARFRAC', 'YIELD', 'YIELDDISC', 'YIELDMAT', 'ZTEST'}) Let'south add some formulas to our sample.xlsx spreadsheet.
Starting with something easy, let's check the average star rating for the 99 reviews within the spreadsheet:
>>>
>>> # Star rating is column "H" >>> sheet [ "P2" ] = "=Average(H2:H100)" >>> workbook . salvage ( filename = "sample_formulas.xlsx" ) If you lot open up the spreadsheet now and go to cell P2, you lot should encounter that its value is: 4.18181818181818. Have a await in the editor:
You tin utilise the same methodology to add any formulas to your spreadsheet. For example, let's count the number of reviews that had helpful votes:
>>>
>>> # The helpful votes are counted on column "I" >>> sheet [ "P3" ] = '=COUNTIF(I2:I100, ">0")' >>> workbook . save ( filename = "sample_formulas.xlsx" ) You lot should go the number 21 on your P3 spreadsheet cell like so:
You'll have to make sure that the strings within a formula are always in double quotes, and then you either take to utilize unmarried quotes around the formula like in the example above or you'll have to escape the double quotes inside the formula: "=COUNTIF(I2:I100, \">0\")".
There are a ton of other formulas you can add together to your spreadsheet using the aforementioned procedure you tried above. Give it a go yourself!
Adding Styles
Fifty-fifty though styling a spreadsheet might not exist something you would do every day, it's still proficient to know how to do it.
Using openpyxl, you tin can apply multiple styling options to your spreadsheet, including fonts, borders, colors, and and so on. Accept a wait at the openpyxl documentation to learn more.
Yous can also choose to either apply a style directly to a cell or create a template and reuse it to apply styles to multiple cells.
Permit'southward offset by having a await at uncomplicated jail cell styling, using our sample.xlsx once again equally the base spreadsheet:
>>>
>>> # Import necessary mode classes >>> from openpyxl.styles import Font , Color , Alignment , Border , Side >>> # Create a few styles >>> bold_font = Font ( bold = True ) >>> big_red_text = Font ( colour = "00FF0000" , size = 20 ) >>> center_aligned_text = Alignment ( horizontal = "center" ) >>> double_border_side = Side ( border_style = "double" ) >>> square_border = Border ( elevation = double_border_side , ... correct = double_border_side , ... bottom = double_border_side , ... left = double_border_side ) >>> # Style some cells! >>> sail [ "A2" ] . font = bold_font >>> sheet [ "A3" ] . font = big_red_text >>> sheet [ "A4" ] . alignment = center_aligned_text >>> sheet [ "A5" ] . border = square_border >>> workbook . relieve ( filename = "sample_styles.xlsx" ) If you lot open your spreadsheet now, you should see quite a few different styles on the first v cells of column A:
At that place you go. Y'all got:
- A2 with the text in bold
- A3 with the text in ruby-red and bigger font size
- A4 with the text centered
- A5 with a square border around the text
You lot can besides combine styles by but adding them to the cell at the same time:
>>>
>>> # Reusing the same styles from the instance higher up >>> canvas [ "A6" ] . alignment = center_aligned_text >>> sheet [ "A6" ] . font = big_red_text >>> sheet [ "A6" ] . border = square_border >>> workbook . save ( filename = "sample_styles.xlsx" ) Have a look at cell A6 here:
When you want to apply multiple styles to one or several cells, you tin can utilize a NamedStyle course instead, which is like a style template that you can use over and over once more. Accept a look at the example beneath:
>>>
>>> from openpyxl.styles import NamedStyle >>> # Let's create a way template for the header row >>> header = NamedStyle ( name = "header" ) >>> header . font = Font ( bold = True ) >>> header . edge = Edge ( lesser = Side ( border_style = "thin" )) >>> header . alignment = Alignment ( horizontal = "center" , vertical = "center" ) >>> # Now allow'south apply this to all first row (header) cells >>> header_row = sheet [ i ] >>> for cell in header_row : ... cell . style = header >>> workbook . save ( filename = "sample_styles.xlsx" ) If you lot open the spreadsheet now, y'all should see that its first row is bold, the text is aligned to the center, and at that place'due south a modest bottom border! Have a look beneath:
As you saw above, there are many options when it comes to styling, and it depends on the employ case, so feel costless to check openpyxl documentation and run across what other things you tin can do.
Provisional Formatting
This feature is one of my personal favorites when information technology comes to calculation styles to a spreadsheet.
It's a much more than powerful arroyo to styling because it dynamically applies styles according to how the information in the spreadsheet changes.
In a nutshell, provisional formatting allows you to specify a listing of styles to apply to a cell (or cell range) according to specific atmospheric condition.
For example, a widespread employ case is to have a balance sheet where all the negative totals are in ruby, and the positive ones are in green. This formatting makes it much more efficient to spot good vs bad periods.
Without farther ado, let's option our favorite spreadsheet—sample.xlsx—and add together some provisional formatting.
You can offset by adding a simple ane that adds a red groundwork to all reviews with less than three stars:
>>>
>>> from openpyxl.styles import PatternFill >>> from openpyxl.styles.differential import DifferentialStyle >>> from openpyxl.formatting.rule import Dominion >>> red_background = PatternFill ( fgColor = "00FF0000" ) >>> diff_style = DifferentialStyle ( fill = red_background ) >>> rule = Dominion ( type = "expression" , dxf = diff_style ) >>> dominion . formula = [ "$H1<3" ] >>> sheet . conditional_formatting . add ( "A1:O100" , rule ) >>> workbook . save ( "sample_conditional_formatting.xlsx" ) Now you'll see all the reviews with a star rating beneath iii marked with a red background:
Lawmaking-wise, the only things that are new here are the objects DifferentialStyle and Rule:
-
DifferentialStyleis quite like toNamedStyle, which you already saw above, and it's used to amass multiple styles such as fonts, borders, alignment, and then forth. -
Dominionis responsible for selecting the cells and applying the styles if the cells match the rule's logic.
Using a Rule object, you can create numerous conditional formatting scenarios.
However, for simplicity sake, the openpyxl parcel offers 3 built-in formats that make it easier to create a few common conditional formatting patterns. These built-ins are:
-
ColorScale -
IconSet -
DataBar
The ColorScale gives you the power to create color gradients:
>>>
>>> from openpyxl.formatting.dominion import ColorScaleRule >>> color_scale_rule = ColorScaleRule ( start_type = "min" , ... start_color = "00FF0000" , # Cherry-red ... end_type = "max" , ... end_color = "0000FF00" ) # Green >>> # Again, allow'due south add together this gradient to the star ratings, cavalcade "H" >>> sheet . conditional_formatting . add ( "H2:H100" , color_scale_rule ) >>> workbook . relieve ( filename = "sample_conditional_formatting_color_scale.xlsx" ) At present you should see a colour slope on column H, from ruby to green, according to the star rating:
You tin also add together a third colour and make two gradients instead:
>>>
>>> from openpyxl.formatting.rule import ColorScaleRule >>> color_scale_rule = ColorScaleRule ( start_type = "num" , ... start_value = 1 , ... start_color = "00FF0000" , # Red ... mid_type = "num" , ... mid_value = 3 , ... mid_color = "00FFFF00" , # Yellowish ... end_type = "num" , ... end_value = v , ... end_color = "0000FF00" ) # Green >>> # Once again, allow's add this gradient to the star ratings, column "H" >>> sheet . conditional_formatting . add ( "H2:H100" , color_scale_rule ) >>> workbook . save ( filename = "sample_conditional_formatting_color_scale_3.xlsx" ) This time, y'all'll notice that star ratings between i and 3 have a gradient from red to xanthous, and star ratings between three and 5 have a gradient from yellowish to green:
The IconSet allows you to add together an icon to the cell according to its value:
>>>
>>> from openpyxl.formatting.rule import IconSetRule >>> icon_set_rule = IconSetRule ( "5Arrows" , "num" , [ one , 2 , 3 , four , 5 ]) >>> canvas . conditional_formatting . add ( "H2:H100" , icon_set_rule ) >>> workbook . save ( "sample_conditional_formatting_icon_set.xlsx" ) You'll run across a colored pointer next to the star rating. This pointer is red and points down when the value of the jail cell is 1 and, as the rating gets improve, the arrow starts pointing upwardly and becomes dark-green:
The openpyxl bundle has a full listing of other icons you lot can use, as well the arrow.
Finally, the DataBar allows you to create progress bars:
>>>
>>> from openpyxl.formatting.rule import DataBarRule >>> data_bar_rule = DataBarRule ( start_type = "num" , ... start_value = 1 , ... end_type = "num" , ... end_value = "five" , ... color = "0000FF00" ) # Green >>> sheet . conditional_formatting . add ( "H2:H100" , data_bar_rule ) >>> workbook . salve ( "sample_conditional_formatting_data_bar.xlsx" ) You'll at present see a greenish progress bar that gets fuller the closer the star rating is to the number 5:
As you can see, there are a lot of cool things you tin can do with conditional formatting.
Here, you saw only a few examples of what you can reach with information technology, but cheque the openpyxl documentation to see a bunch of other options.
Calculation Images
Even though images are non something that you'll often run into in a spreadsheet, it'southward quite cool to be able to add them. Maybe y'all can utilise it for branding purposes or to make spreadsheets more personal.
To be able to load images to a spreadsheet using openpyxl, you'll accept to install Pillow:
Apart from that, you'll besides need an paradigm. For this example, you can grab the Real Python logo below and convert information technology from .webp to .png using an online converter such as cloudconvert.com, salve the final file as logo.png, and copy it to the root folder where you're running your examples:
Afterwards, this is the code you demand to import that prototype into the hello_word.xlsx spreadsheet:
from openpyxl import load_workbook from openpyxl.drawing.image import Image # Let'due south use the hello_world spreadsheet since it has less data workbook = load_workbook ( filename = "hello_world.xlsx" ) sheet = workbook . agile logo = Prototype ( "logo.png" ) # A bit of resizing to not fill the whole spreadsheet with the logo logo . height = 150 logo . width = 150 sail . add_image ( logo , "A3" ) workbook . salve ( filename = "hello_world_logo.xlsx" ) You have an image on your spreadsheet! Hither it is:
The image'southward left summit corner is on the jail cell you chose, in this case, A3.
Calculation Pretty Charts
Another powerful thing you can do with spreadsheets is create an incredible diverseness of charts.
Charts are a great way to visualize and understand loads of information quickly. There are a lot of different nautical chart types: bar nautical chart, pie chart, line chart, and then on. openpyxl has back up for a lot of them.
Here, you'll encounter only a couple of examples of charts because the theory behind information technology is the same for every single chart blazon:
For whatsoever chart y'all want to build, you'll need to ascertain the nautical chart type: BarChart, LineChart, so along, plus the data to exist used for the chart, which is called Reference.
Before you tin build your chart, you demand to define what data you want to see represented in it. Sometimes, you can use the dataset as is, but other times yous need to massage the data a fleck to become additional information.
Let'southward start by building a new workbook with some sample data:
i from openpyxl import Workbook 2 from openpyxl.chart import BarChart , Reference 3 4 workbook = Workbook () five sheet = workbook . agile 6 7 # Let's create some sample sales data 8 rows = [ 9 [ "Product" , "Online" , "Store" ], 10 [ 1 , 30 , 45 ], 11 [ 2 , 40 , xxx ], 12 [ 3 , xl , 25 ], 13 [ 4 , 50 , thirty ], 14 [ 5 , 30 , 25 ], 15 [ 6 , 25 , 35 ], 16 [ 7 , 20 , 40 ], 17 ] 18 19 for row in rows : 20 sheet . suspend ( row ) Now yous're going to starting time by creating a bar chart that displays the total number of sales per product:
22 chart = BarChart () 23 information = Reference ( worksheet = sheet , 24 min_row = 1 , 25 max_row = 8 , 26 min_col = 2 , 27 max_col = iii ) 28 29 chart . add_data ( data , titles_from_data = True ) thirty sheet . add_chart ( chart , "E2" ) 31 32 workbook . save ( "chart.xlsx" ) In that location you take it. Beneath, you can see a very straightforward bar chart showing the difference between online product sales online and in-shop product sales:
Like with images, the peak left corner of the chart is on the jail cell you lot added the chart to. In your case, information technology was on cell E2.
Try creating a line chart instead, changing the information a chip:
i import random ii from openpyxl import Workbook 3 from openpyxl.chart import LineChart , Reference 4 5 workbook = Workbook () 6 sheet = workbook . active vii 8 # Let's create some sample sales data 9 rows = [ 10 [ "" , "January" , "February" , "March" , "April" , 11 "May" , "June" , "July" , "August" , "September" , 12 "Oct" , "Nov" , "December" ], thirteen [ 1 , ], xiv [ 2 , ], 15 [ 3 , ], 16 ] 17 18 for row in rows : nineteen sheet . append ( row ) 20 21 for row in canvass . iter_rows ( min_row = 2 , 22 max_row = 4 , 23 min_col = 2 , 24 max_col = 13 ): 25 for prison cell in row : 26 cell . value = random . randrange ( 5 , 100 ) With the above code, you'll exist able to generate some random data regarding the sales of 3 different products across a whole year.
Once that's done, you can very easily create a line chart with the following lawmaking:
28 chart = LineChart () 29 information = Reference ( worksheet = sheet , 30 min_row = 2 , 31 max_row = 4 , 32 min_col = one , 33 max_col = xiii ) 34 35 chart . add_data ( information , from_rows = True , titles_from_data = Truthful ) 36 sheet . add_chart ( nautical chart , "C6" ) 37 38 workbook . relieve ( "line_chart.xlsx" ) Here's the outcome of the above piece of code:
Ane matter to keep in mind here is the fact that y'all're using from_rows=Truthful when adding the data. This argument makes the nautical chart plot row by row instead of column by cavalcade.
In your sample information, you lot meet that each product has a row with 12 values (1 cavalcade per month). That'due south why you lot apply from_rows. If you don't laissez passer that argument, by default, the nautical chart tries to plot by column, and you'll get a month-past-month comparison of sales.
Another divergence that has to do with the above argument change is the fact that our Reference now starts from the first cavalcade, min_col=1, instead of the second one. This modify is needed because the nautical chart now expects the first column to have the titles.
In that location are a couple of other things you can also change regarding the style of the chart. For example, you tin can add specific categories to the chart:
cats = Reference ( worksheet = canvass , min_row = 1 , max_row = 1 , min_col = 2 , max_col = xiii ) chart . set_categories ( cats ) Add this piece of code before saving the workbook, and you should see the month names actualization instead of numbers:
Lawmaking-wise, this is a minimal modify. But in terms of the readability of the spreadsheet, this makes information technology much easier for someone to open up the spreadsheet and empathize the chart straight away.
Some other thing you can exercise to amend the chart readability is to add together an axis. You can practice it using the attributes x_axis and y_axis:
chart . x_axis . title = "Months" chart . y_axis . title = "Sales (per unit)" This will generate a spreadsheet like the below one:
As y'all tin can meet, pocket-size changes like the above make reading your chart a much easier and quicker task.
There is likewise a fashion to style your chart by using Excel's default ChartStyle property. In this example, yous take to cull a number between ane and 48. Depending on your pick, the colors of your nautical chart alter as well:
# You can play with this by choosing any number betwixt 1 and 48 nautical chart . style = 24 With the mode selected in a higher place, all lines accept some shade of orange:
There is no articulate documentation on what each style number looks similar, but this spreadsheet has a few examples of the styles bachelor.
Here'south the full code used to generate the line chart with categories, axis titles, and style:
import random from openpyxl import Workbook from openpyxl.nautical chart import LineChart , Reference workbook = Workbook () sail = workbook . active # Let's create some sample sales data rows = [ [ "" , "January" , "February" , "March" , "April" , "May" , "June" , "July" , "August" , "September" , "October" , "November" , "December" ], [ 1 , ], [ 2 , ], [ 3 , ], ] for row in rows : sheet . append ( row ) for row in sheet . iter_rows ( min_row = 2 , max_row = 4 , min_col = 2 , max_col = thirteen ): for jail cell in row : prison cell . value = random . randrange ( five , 100 ) # Create a LineChart and add the main data chart = LineChart () data = Reference ( worksheet = canvas , min_row = 2 , max_row = four , min_col = ane , max_col = 13 ) chart . add_data ( data , titles_from_data = Truthful , from_rows = Truthful ) # Add together categories to the nautical chart cats = Reference ( worksheet = sheet , min_row = one , max_row = 1 , min_col = 2 , max_col = 13 ) chart . set_categories ( cats ) # Rename the 10 and Y Axis chart . x_axis . title = "Months" chart . y_axis . title = "Sales (per unit)" # Utilize a specific Mode nautical chart . manner = 24 # Save! sheet . add_chart ( nautical chart , "C6" ) workbook . save ( "line_chart.xlsx" ) There are a lot more chart types and customization you can use, so be sure to bank check out the parcel documentation on this if you lot need some specific formatting.
Convert Python Classes to Excel Spreadsheet
Y'all already saw how to convert an Excel spreadsheet'due south data into Python classes, but now permit's practise the opposite.
Let's imagine you have a database and are using some Object-Relational Mapping (ORM) to map DB objects into Python classes. Now, you want to export those aforementioned objects into a spreadsheet.
Allow's assume the post-obit information classes to represent the data coming from your database regarding product sales:
from dataclasses import dataclass from typing import List @dataclass course Sale : quantity : int @dataclass form Product : id : str proper name : str sales : List [ Sale ] Now, let's generate some random data, assuming the above classes are stored in a db_classes.py file:
1 import random 2 3 # Ignore these for at present. You'll utilise them in a sec ;) four from openpyxl import Workbook v from openpyxl.nautical chart import LineChart , Reference 6 7 from db_classes import Product , Sale 8 9 products = [] x 11 # Let's create 5 products 12 for idx in range ( one , vi ): 13 sales = [] 14 15 # Create 5 months of sales 16 for _ in range ( 5 ): 17 sale = Auction ( quantity = random . randrange ( 5 , 100 )) 18 sales . append ( auction ) 19 20 product = Product ( id = str ( idx ), 21 name = "Product %s " % idx , 22 sales = sales ) 23 products . suspend ( production ) By running this piece of code, you should get five products with v months of sales with a random quantity of sales for each month.
Now, to convert this into a spreadsheet, yous need to iterate over the information and append it to the spreadsheet:
25 workbook = Workbook () 26 sheet = workbook . active 27 28 # Append column names first 29 sheet . suspend ([ "Product ID" , "Product Name" , "Month i" , xxx "Calendar month ii" , "Month 3" , "Month 4" , "Month 5" ]) 31 32 # Suspend the data 33 for product in products : 34 data = [ product . id , production . name ] 35 for auction in product . sales : 36 information . append ( auction . quantity ) 37 sheet . suspend ( data ) That'south information technology. That should allow you lot to create a spreadsheet with some data coming from your database.
All the same, why not use some of that cool knowledge you gained recently to add a chart as well to brandish that data more than visually?
All correct, and so you lot could probably exercise something similar this:
38 chart = LineChart () 39 data = Reference ( worksheet = sail , twoscore min_row = two , 41 max_row = half-dozen , 42 min_col = 2 , 43 max_col = vii ) 44 45 chart . add_data ( information , titles_from_data = True , from_rows = True ) 46 sheet . add_chart ( chart , "B8" ) 47 48 cats = Reference ( worksheet = canvas , 49 min_row = 1 , l max_row = 1 , 51 min_col = three , 52 max_col = vii ) 53 nautical chart . set_categories ( cats ) 54 55 chart . x_axis . championship = "Months" 56 chart . y_axis . title = "Sales (per unit)" 57 58 workbook . save ( filename = "oop_sample.xlsx" ) Now we're talking! Here's a spreadsheet generated from database objects and with a chart and everything:
That's a not bad way for you to wrap upwards your new noesis of charts!
Bonus: Working With Pandas
Even though you can use Pandas to handle Excel files, there are few things that you either can't accomplish with Pandas or that you'd be improve off just using openpyxl directly.
For example, some of the advantages of using openpyxl are the ability to hands customize your spreadsheet with styles, conditional formatting, and such.
But guess what, you don't take to worry most picking. In fact, openpyxl has support for both converting data from a Pandas DataFrame into a workbook or the contrary, converting an openpyxl workbook into a Pandas DataFrame.
Showtime things commencement, remember to install the pandas package:
Then, let's create a sample DataFrame:
one import pandas as pd 2 3 data = { 4 "Product Name" : [ "Production i" , "Product ii" ], 5 "Sales Month ane" : [ 10 , 20 ], 6 "Sales Month 2" : [ five , 35 ], 7 } 8 df = pd . DataFrame ( data ) Now that you have some data, you can utilise .dataframe_to_rows() to catechumen it from a DataFrame into a worksheet:
10 from openpyxl import Workbook 11 from openpyxl.utils.dataframe import dataframe_to_rows 12 13 workbook = Workbook () 14 sheet = workbook . active 15 16 for row in dataframe_to_rows ( df , index = Simulated , header = True ): 17 sail . suspend ( row ) eighteen xix workbook . save ( "pandas.xlsx" ) Y'all should see a spreadsheet that looks like this:
If you want to add the DataFrame'due south index, you can change index=True, and it adds each row's alphabetize into your spreadsheet.
On the other hand, if you want to convert a spreadsheet into a DataFrame, y'all tin as well do it in a very straightforward way like then:
import pandas as pd from openpyxl import load_workbook workbook = load_workbook ( filename = "sample.xlsx" ) sheet = workbook . active values = sheet . values df = pd . DataFrame ( values ) Alternatively, if you want to add together the correct headers and use the review ID equally the alphabetize, for case, then y'all can also practice it like this instead:
import pandas every bit pd from openpyxl import load_workbook from mapping import REVIEW_ID workbook = load_workbook ( filename = "sample.xlsx" ) canvass = workbook . active data = canvas . values # Set the start row as the columns for the DataFrame cols = next ( information ) data = listing ( data ) # Ready the field "review_id" as the indexes for each row idx = [ row [ REVIEW_ID ] for row in data ] df = pd . DataFrame ( information , alphabetize = idx , columns = cols ) Using indexes and columns allows you to access data from your DataFrame hands:
>>>
>>> df . columns Index(['marketplace', 'customer_id', 'review_id', 'product_id', 'product_parent', 'product_title', 'product_category', 'star_rating', 'helpful_votes', 'total_votes', 'vine', 'verified_purchase', 'review_headline', 'review_body', 'review_date'], dtype='object') >>> # Get showtime 10 reviews' star rating >>> df [ "star_rating" ][: x ] R3O9SGZBVQBV76 5 RKH8BNC3L5DLF v R2HLE8WKZSU3NL ii R31U3UH5AZ42LL 5 R2SV659OUJ945Y 4 RA51CP8TR5A2L 5 RB2Q7DLDN6TH6 5 R2RHFJV0UYBK3Y 1 R2Z6JOQ94LFHEP 5 RX27XIIWY5JPB 4 Name: star_rating, dtype: int64 >>> # Grab review with id "R2EQL1V1L6E0C9", using the index >>> df . loc [ "R2EQL1V1L6E0C9" ] marketplace The states customer_id 15305006 review_id R2EQL1V1L6E0C9 product_id B004LURNO6 product_parent 892860326 review_headline V Stars review_body Beloved it review_date 2015-08-31 Name: R2EQL1V1L6E0C9, dtype: object In that location you go, whether you desire to use openpyxl to prettify your Pandas dataset or utilise Pandas to do some hardcore algebra, you now know how to switch between both packages.
Conclusion
Phew, after that long read, y'all now know how to work with spreadsheets in Python! Y'all can rely on openpyxl, your trustworthy companion, to:
- Extract valuable information from spreadsheets in a Pythonic fashion
- Create your own spreadsheets, no matter the complication level
- Add together absurd features such as provisional formatting or charts to your spreadsheets
In that location are a few other things you can exercise with openpyxl that might not have been covered in this tutorial, but yous can always bank check the package's official documentation website to learn more most it. Y'all can fifty-fifty venture into checking its source lawmaking and improving the bundle farther.
Feel free to leave any comments beneath if you accept whatever questions, or if there's any section you'd beloved to hear more about.
Scout Now This tutorial has a related video form created by the Real Python team. Watch it together with the written tutorial to deepen your understanding: Editing Excel Spreadsheets in Python With openpyxl
Source: https://realpython.com/openpyxl-excel-spreadsheets-python/
0 Response to "Python Read All Existing Cells in Excel"
إرسال تعليق