Microsoft Excel is arguably the most popular business software on the planet, with almost every single company making use of it in one way or another. Proficiency in Excel is a must-have skill in many jobs. Here is the rub. Managing data in Excel manually is often quite repetitive and tedious. Wouldn’t it be great if you could still leverage the power of Microsoft Excel while reducing the repetition? Enter the Python Programming Language. Just as Excel is one of the most popular computer applications, Python is one of the most popular programming languages. Once you have learned basic Python, you can integrate Python and Excel to streamline your workflow, lighten your workload, accomplish more in less time, and generally just be more effective. In this tutorial, we are going to examine how to get started using Python and Exel together.
How To Load And Save Data Using Pandas
Pandas is the popular open-source library written in Python explicitly designed for data analysis and manipulation. Pandas can work directly with Excel files by way of something called a dataframe. A dataframe is like a spreadsheet in the memory of your Python application. A dataframe has indexed rows and header columns that both store what’s called a series. The series stores all the values in the rows and columns, almost like the list. Because both these objects are iterable, Pandas makes it easy to traverse and pick up the data. Using the dataframe, we can do any kind of analysis or grouping we’d want and export it to Excel. Cool!
Install Pandas
To get started with Pandas, make sure you have it installed. The pip install pandas command should do the trick.
While we’re at it, we should also run pip install openpyxl and pip install xlrdsince we will be working with these modules as well.
Loading And Saving Data Using Pandas
To start working with data in Pandas, we need to import some data from files. The first two Pandas methods to help us with this are .read_excel() and .read_csv(). You can also create data frames in Pandas from lists or objects in code.
Excel Data Frame
In this first example, we have a file named regiondata.xlsx. It just has some simple sample data to view.
Now we can use Python to read that .xlsx file into a data frame, then output the results. We do this by calling import pandas as pd. We can also import the openpyxl module that will allow saving to an Excel document. Once we have these, we just need to initialize our data frame using .read_excel().
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_excel = pd.read_excel('excel/regiondata.xlsx')
print(dataframe_excel)
You can see that Pandas uses indices for rows and headers for columns. In this case, the indices are integers, and our headers are strings, such as region, unit, sales, and exports.
CSV Data Frame
Now let’s go ahead and read a CSV file into a data frame using Pandas. For this we can use the pandas .read_csv() method. Here is the CSV file we want to open.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv')
print(dataframe_csv)
We can see that the indices are present, but the first row of data is acting as a header for the rest of the file. We don’t really want this so we need to specify that there is no header present in the CSV file.
To fix this all we have to do is add a header argument and set that to None. When we run this again, we can see that now the headers are integer based.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
print(dataframe_csv)
Setting Column Names
Those integers could be more helpful if they were descriptive names of what each column actually is. We can take care of this by setting the columns. We simply assign a list with each element corresponding to the column as we want them named.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv)
Pandas CSV to Excel
Now let’s go ahead and save the CSV file to an Excel file of type .xlsx. This is done using the .to_excel() method and passing in the path and name of the file.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv)
dataframe_csv.to_excel('excel/some_names_modified.xlsx')
This creates a new Excel file in the directory we specify, and when we open that file in Excel we can see that it has the indices and headers as we expect.
Text File Data Frame
Oddly enough, creating a data frame from a text file is done using the same .read_csv() function. This is because there is not too much of a difference between a CSV file and a text file, except for some minor formatting differences. We have a text file that has over 11,000 rows of data. Pandas can read this large amount of data super quickly.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_txt = pd.read_csv('excel/some_data.txt', delimiter='t')
print(dataframe_txt)
The file itself is tab-delimited, which is why we need to specify delimiter=’t’ as the second argument to the .read_csv() function. Pandas provides the output as we see here, with the middle chunk of rows omitted since there are so many rows in the data set.
How To View And Inspect Data With Pandas
When working with pandas, getting what you want out of the data is a matter of manipulating the data frame. Let’s look at a few selection and viewing functions, as well as saving our desired values to an Excel sheet.
How To Work With Columns In Pandas
The code below is operating on the same some_names.csv file from earlier where we had assigned column names. Imagine if you had to deal with a spreadsheet with so many columns that it was hard to read the data in the terminal. You need to know which columns contain what so that you can access the data you need to proceed. In order to do this, we can use the same function we used to assign the columns. Printing out the dataframe_csv.columns variable shows us the data we are working with.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv.columns)
Index(['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population'], dtype='object')
This shows us how the data is split up by column, allowing us to now be able to view just one column if we like. Pandas data frames are a multi-row and multi-column data structure, like a matrix. This makes it easy to work with all data in just one column, or one row if we want. No loops are needed, and all we have to do is index by the column name. So let’s say we want First Name. Then we just take the First Name index and this prints out all the values in the First Name column and their indices.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv['First'])
0 Liam 1 Noah 2 Emma 3 Olivia 4 Ava 5 James Name: First, dtype: object
If you wanted to access multiple column’s data, you can pass in a list. So let’s say we want the Address and the State columns. We just construct a list with those two values, then pass them in using bracket notation. Now we have both the Address and the State and their indices.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv[['Address', 'State']])
Address State 0 3 Jackson Drive MI 1 81 Emerson Way CO 2 17 Upside Lane TX 3 712 Front Street CA 4 24 Seven Street ID 5 11 Breaker Lane OH
When a spreadsheet has hundreds of rows of data, you may want to just focus in on a subset of the data in a particular column for example. This can be done using slicing, and the syntax is the standard Python slicing approach you are used to. In this example below, we want to look at the Zip column, but only the first two results. The highlighted line shows the slice syntax to achieve that.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv['Zip'][0:2])
0 49508 1 80922 Name: Zip, dtype: int64
How To Work With Rows In Pandas
Let’s look at how to work with some rows in Pandas now that we have learned a bit about columns. Rows are indexed with integers automatically by Pandas, and we can use these indices to locate them specifically with the .iloc function. In the example below, we pass in 2 as an index and print that to the screen. The data printed out is the row that we expect.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv.iloc[2])
First Emma Last Williams Address 17 Upside Lane City Watauga State TX Zip 76148 Population 120000 Name: 2, dtype: object
If you are looking at the actual spreadsheet in Excel, the above code is selecting the row seen here.
You can also drill down and select individual cells in the spreadsheet. Again, the in-memory data frame is like a matrix just like we see visually. Let’s consider we want to access the cell data circled here.
It would be possible to find this data using iteration, but it is more efficient to pass two numbers or variables to .iloc[] in the way they correspond to a row-column value or coordinate system. So in our example, we want the text circled above. In Pandas, we know that indices are numbered starting at zero and our headers are strings. What is also true is that when using the .iloc[] function, headers also correspond to integer values starting at zero. The first argument is the row, and the second is the column. Since these are zero-based, we want the integer 3 row and the integer 2 column.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv.iloc[3, 2])
712 Front Street
Saving Extracted Data
Now that we have the knowledge of how to access data by column, row, or coordinator, we can see how it is easy to drill down on only the data we want. With this in mind, let’s say we want to access a subset of the data, then store that data in its own spreadsheet. We can use code as we see here to accomplish that goal.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
extracted_data = dataframe_csv[['First', 'Last', 'City']]
stored = extracted_data.to_excel('extracted_data.xlsx', index=None)
Opening the resulting file extracted_data.xlsx in Excel shows that it has the exact data we were drilling down on. Very cool!
How To Filter And Sort Data Using Pandas
You can filter and sort data in Pandas in almost countless ways, we’ll look at a few of the more simple examples here. This first example simply looks for all rows that have the value of Worthington in the City column.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv[dataframe_csv['City'] == 'Worthington'])
First Last Address City State Zip Population 4 Zach Price 99 Eleven Way Worthington OH 43085 68000 8 James Miller 11 Breaker Lane Worthington OH 43085 68000
To combine two conditions when filtering data in Pandas, you use the ampersand & operator as we see below. This code checks for all rows where the City is Kentwood *and* the First column has a value of Sam.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv[(dataframe_csv['City'] == 'Kentwood') & (dataframe_csv['First'] == 'Sam')])
First Last Address City State Zip Population 0 Sam Smith 3 Jackson Drive Kentwood MI 49508 45000 6 Sam Miller 75 High Street Kentwood MI 49508 45000
If you only want to work on a subset of the data, you can drop columns using the .drop() function.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
drop = ['Address', 'Population']
dataframe_csv.drop(columns=drop, inplace=True)
print(dataframe_csv)
First Last City State Zip 0 Sam Smith Kentwood MI 49508 1 Sally Holmes Boise ID 83704 2 Noah Johnson C Springs CO 80922 3 Emma Williams Watauga TX 76148 4 Zach Price Worthington OH 43085 5 Olivia Jones C Valley CA 94546 6 Sam Miller Kentwood MI 49508 7 Ava Brown Boise ID 83704 8 James Miller Worthington OH 43085
Here we can create a new column with a default value of false. Then, we can look at the data in other columns and change that value to True based on a condition. In this case, we check to see if the State column has a value of OH, and if it does, go ahead and set the new column we defined to True.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
drop = ['Address', 'Population']
dataframe_csv.drop(columns=drop, inplace=True)
dataframe_csv['T or F'] = False
dataframe_csv.loc[dataframe_csv['State'] == 'OH', 'T or F'] = True
print(dataframe_csv)
First Last City State Zip T or F 0 Sam Smith Kentwood MI 49508 False 1 Sally Holmes Boise ID 83704 False 2 Noah Johnson C Springs CO 80922 False 3 Emma Williams Watauga TX 76148 False 4 Zach Price Worthington OH 43085 True 5 Olivia Jones C Valley CA 94546 False 6 Sam Miller Kentwood MI 49508 False 7 Ava Brown Boise ID 83704 False 8 James Miller Worthington OH 43085 True
In the below two examples, we use the .sort_values() method to sort the data on a particular column.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv.sort_values('First'))
First Last Address City State Zip Population 7 Ava Brown 24 Seven Street Boise ID 83704 30000 3 Emma Williams 17 Upside Lane Watauga TX 76148 120000 8 James Miller 11 Breaker Lane Worthington OH 43085 68000 2 Noah Johnson 81 Emerson Way C Springs CO 80922 18000 5 Olivia Jones 712 Front Street C Valley CA 94546 90000 1 Sally Holmes 12 Front Drive Boise ID 83704 30000 0 Sam Smith 3 Jackson Drive Kentwood MI 49508 45000 6 Sam Miller 75 High Street Kentwood MI 49508 45000 4 Zach Price 99 Eleven Way Worthington OH 43085 68000
To sort the data in the other direction, just add ascending=False as the second argument.
import pandas as pd
from openpyxl.workbook import Workbook
dataframe_csv = pd.read_csv('excel/some_names.csv', header=None)
dataframe_csv.columns = ['First', 'Last', 'Address', 'City', 'State', 'Zip', 'Population']
print(dataframe_csv.sort_values('First', ascending=False))
First Last Address City State Zip Population 4 Zach Price 99 Eleven Way Worthington OH 43085 68000 0 Sam Smith 3 Jackson Drive Kentwood MI 49508 45000 6 Sam Miller 75 High Street Kentwood MI 49508 45000 1 Sally Holmes 12 Front Drive Boise ID 83704 30000 5 Olivia Jones 712 Front Street C Valley CA 94546 90000 2 Noah Johnson 81 Emerson Way C Springs CO 80922 18000 8 James Miller 11 Breaker Lane Worthington OH 43085 68000 3 Emma Williams 17 Upside Lane Watauga TX 76148 120000 7 Ava Brown 24 Seven Street Boise ID 83704 30000
Controlling Excel Directly With Openpyxl
Openpyxl is another popular library that is good for searching on certain data, copying and pasting to new Excel documents, or just trying to make your data look good. It is a package in Python that allows your code to interact directly with Excel documents. With Openpyxl, you can manipulate not just the data in the spreadsheet, but also the properties of the spreadsheet itself. Openpyxl does this by storing an Excel workbook as well as the worksheets as objects that can be altered with Python code. Openpyxl is the most used module for working specifically with Excel, as it allows developers to do anything from styling sheets to parsing data or creating graphs. To get started with Openpyxl, install it with the pip install openpyxl command.
.load_workbook()
We have a file named stock_options.xlsx that we can use for some examples. A quick recap of Excel terms is in order before we get started.
- Workbook – The Excel document itself is called a Workbook.
- .xlsx extension – A single Workbook is saved to a file with the .xlsx extension.
- Worksheets(Sheets) – Each Workbook can have multiple Worksheets.
- Columns – Columns are aligned vertically and use Letters for identification.
- Rows – Rows are aligned horizontally and use Numbers for identification.
- Cell – At the intersection of a Column and Row is what is known as a cell.
The Excel file we will work with looks like this.
To start working with a Workbook, we can use code as we see here.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
print(type(workbook))
Printing out the workbook variable shows it is an object of type Workbook.
<class 'openpyxl.workbook.workbook.Workbook'>
How To Access Worksheets
A Workbook may contain multiple Worksheets. In openpyxl, we can access a worksheet object a couple of ways. If you know the name of the Worksheet you want to work with, this code will suit you.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
print(type(sheet))
<class 'openpyxl.worksheet.worksheet.Worksheet'>
If you don’t know the name of the worksheet you want to access, you can check what names exist with a simple call to .sheetnames.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheetnames = workbook.sheetnames
print(sheetnames)
['Sheet1', 'Sheet2', 'Sheet3']
How To Access Cells In Sheets
Once you have a sheet object to work with, you can access it’s cells and values pretty easily.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
cell = sheet['A3']
print(cell.value)
SFIX
You can also access a cell using the .cell() method and passing both the row and column as integers like so.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
cell = sheet.cell(row=4, column=14)
print(cell.value)
0.3745
The approach of using the .cell() method is good when you want to iterate over values in the sheet. For example:
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
for i in range(2, 7):
cell = sheet.cell(row=i, column=1)
print(cell.value)
CCJ SFIX FE WLL ACC
We can also use slicing to select a range of cells. Here is an example of that.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
cell_range = sheet['A1':'A3']
print(cell_range)
((<Cell 'Sheet1'.A1>,), (<Cell 'Sheet1'.A2>,), (<Cell 'Sheet1'.A3>,))
To select an entire column, we can simply specify the column we want. We’ll then print out the number of items in that column since actually printing out the entire data would be too many to view in such a large document. We can see there are 923 values in column A.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
column_a = sheet['A']
print(len(column_a))
923
This code shows us all of the cells that have values in row 1.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
row_0 = sheet[1]
print(row_0)
(<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>, <Cell 'Sheet1'.D1>, <Cell 'Sheet1'.E1>, <Cell 'Sheet1'.F1>, <Cell 'Sheet1'.G1>, <Cell 'Sheet1'.H1>, <Cell 'Sheet1'.I1>, <Cell 'Sheet1'.J1>, <Cell 'Sheet1'.K1>, <Cell 'Sheet1'.L1>, <Cell 'Sheet1'.M1>, <Cell 'Sheet1'.N1>, <Cell 'Sheet1'.O1>)
Cells are accessed by the combination of string headers and integers and columns are only the headers. For rows, we only use the integer and we still access them through our worksheet. Just like with other functions if we wanted to grab one row, we index by a specific number. But if we wanted to grab multiple rows we use the slicing method.
The final access function we can look at is iterating through columns or rows. When we iterate through these objects we use a specific function in our for loop. So let’s go over the rows function. The column function is exactly the same but the words are switched. In the iter_rows or iter_columns function, we have to specify the minimum and maximums for our rows and columns. We’ll set our minimum row to one, our maximum column to three, and our maximum row to two. Inside the of the loop, we iterate through the objects again. Now you’ll notice we should be printing the cells from a minimum row of one, a minimum column of one, to a maximum row of two and a maximum column of three. We can see that our minimum row one corresponds to the first row and our max row corresponds just to the second. So you see we only have A1 to C2. The maximum column equals three puts us all the way to C.
import openpyxl
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
for row in sheet.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
<Cell 'Sheet1'.A1> <Cell 'Sheet1'.B1> <Cell 'Sheet1'.C1> <Cell 'Sheet1'.A2> <Cell 'Sheet1'.B2> <Cell 'Sheet1'.C2>
Creating New Workbooks and Worksheets
We don’t have to import an already existing excel file when using openpyxl. The following code creates a Workbook with three Worksheets.
import openpyxl
workbook = openpyxl.Workbook()
worksheet = workbook.active
worksheet2 = workbook.create_sheet('First Sheet')
worksheet3 = workbook.create_sheet('Second Sheet')
worksheet.title = 'My Awesome Sheet'
print(workbook.sheetnames)
['My Awesome Sheet', 'First Sheet', 'Second Sheet']
To add some data to one of the Worksheets in our Workbook, we can assign a value to the cell we are interested in. Then we will save the workbook and view it in Excel.
import openpyxl
workbook = openpyxl.Workbook()
worksheet = workbook.active
worksheet2 = workbook.create_sheet('First Sheet')
worksheet3 = workbook.create_sheet('Second Sheet')
worksheet.title = 'My Awesome Sheet'
worksheet['A1'] = 'Hello Openpyxl'
workbook.save('excel/awesomeworkbook.xlsx')
How To Format Workbooks
When presenting large sets of data to others, it helps to format it in such a way that it is readable and understandable. If you want to create an Excel that’s stylized and professional then openpyxl provides the perfect formatting tools. In the example below, we use a few of the available formatting tools available in openpyxl to add a nice header to the spreadsheet. By inserting rows and then using the merge function, we can create a header that is above all of the actual data. In that header area, we use the Font module to add color to the text in addition to italicizing it. We know how to set a value to a cell, so we do that next. The Alignment module makes it easy to put content in a merged cell area with a great amount of accuracy. We set the text to be vertically centered and horizontally shifted to the right. The GradientFill module allows us to add a cool gradient effect to the header as well.
import openpyxl
from openpyxl.styles import Font, Alignment, GradientFill
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
sheet.insert_rows(1, 2)
sheet.merge_cells('A1:O2')
cell = sheet['A1']
cell.font = Font(color='007742', size=20, italic=True)
cell.value = 'Super Cool And Stylish Spreadsheet'
cell.alignment = Alignment(horizontal='right', vertical='center')
cell.fill = GradientFill(stop=('000000', 'ffffff'))
workbook.save('excel/stylish.xlsx')
This code saves a new Workbook to the excel directory named stylish.xlsx. If we open up that workbook using Excel, we can see two new rows at the top of the workbook. All of the cells in that range have been merged, and there is now some text in that area. We also see the neat color, font, and italics that we applied. Cool!
Named Styles In Openpyxl
Openpyxl has a cool module named NamedStyles. Named styles are objects we can create that store a style so that we can use it multiple times instead of having to type all that out as we did just above. To complete this example, we import a few more modules at the top of the file. These are NamedStyle, Side, Border, and PatternFill. We want to add another effect to the top of each column for the data. So we create a new variable named highlight and assign a NamedStyle to it. Then we can simply build up that object with all of the properties we want the style to have. In this case, we add a border to the top and bottom of the cell, make it yellow in color, and set the font to bold. One the named style is in place, we set simply iterate over each cell in row 3, applying the highlight on each iteration.
import openpyxl
from openpyxl.styles import Font, Alignment, GradientFill, NamedStyle, Side, Border, PatternFill
workbook = openpyxl.load_workbook('excel/stock_options.xlsx')
sheet = workbook['Sheet1']
sheet.insert_rows(1, 2)
sheet.merge_cells('A1:O2')
cell = sheet['A1']
cell.font = Font(color='007742', size=20, italic=True)
cell.value = 'Super Cool And Stylish Spreadsheet'
cell.alignment = Alignment(horizontal='right', vertical='center')
cell.fill = GradientFill(stop=('000000', 'ffffff'))
highlight = NamedStyle(name='highlight')
highlight.font = Font(bold=True)
bd = Side(style='thick', color='000000')
highlight.border = Border(left=None, top=bd, right=None, bottom=bd)
highlight.fill = PatternFill('solid', fgColor='fde295')
for cell in sheet['3:3']:
cell.style = highlight
workbook.save('excel/stylish.xlsx')
Learn More About How To Use Python With Excel
- Pandas Docs Stable Tutorials.html (pandas.pydata.org)
- Python Openpyxl (zetcode.com)
- Python Pandas Tutorial Complete Introduction For Beginners (learndatasci.com)
- Pythontutor Openpyxl (pythontutor.net)
- Pandas_Basics (learnpython.org)
- Working With Excel Sheets In Python Using Openpyxl (medium.com)
- Python_Pandas (tutorialspoint.com)
- Automatetheboringstuff Openpyxl (automatetheboringstuff.com)
- Pandas Tutorial Dataframe Python (datacamp.com)
- Python Excel Openpyxl (pythonexcel.com)
- Pandas Tutorial 1 Basics Reading Data Files Dataframes (data36.com)
- Python Reading Excel File Using Openpyxl Module (geeksforgeeks.org)
- Pandas Python Tutorial (dataquest.io)
- Openpyxl Excel Spreadsheets Python (realpython.com)
- Python Pandas (javatpoint.com)
- Likegeeks Pandas Tutorial (likegeeks.com)
- Openpyxl Tutorial (openpyxl.readthedocs.io)
How To Use Python With Excel Summary
Python works great with Excel, both very popular in their respective domains. Python has a growing community of developers that produce great tools to use like Pandas and Openpyxl. We had a look at the basics of using both tools to work with Excel. There is always more to learn, so the list of resources just above is a great place to keep learning.