OpenPyXL gives you the ability to style your cells in many different ways. Styling cells will give your spreadsheets pizazz! Your spreadsheets can have some pop and zing to them that will help differentiate them from others. However, don't go overboard! If every cell had a different font and color, your spreadsheet would look like a mess.
You should use the skills that you learn in this article sparingly. You'll still have beautiful spreadsheets that you can share with your colleagues. If you would like to learn more about what styles OpenPyXL supports, you should check out their documentation.
In this article, you will learn about the following:
Now that you know what you're going to learn, it's time to get started by discovering how to work with fonts using OpenPyXL!
You use fonts to style your text on a computer. A font controls the size, weight, color, and style of the text you see on-screen or in print. There are thousands of fonts that your computer can use. Microsoft includes many fonts with its Office products.
When you want to set a font with OpenPyXL, you will need to import the
Font class from
openpyxl.styles. Here is how you would do the import:
from openpyxl.styles import Font
Font class takes many parameters. Here is the
Font class's full list of parameters according to OpenPyXL's documentation:
class openpyxl.styles.fonts.Font(name=None, sz=None, b=None, i=None, charset=None, u=None, strike=None, color=None, scheme=None, family=None, size=None, bold=None, italic=None, strikethrough=None, underline=None, vertAlign=None, outline=None, shadow=None, condense=None, extend=None)
The following list shows the parameters you are most likely to use and their defaults:
These settings allow you to set most of the things you'll need to make your text look nice. Note that the color names in OpenPyXL use hexadecimal values to represent RGB (red, green, blue) color values. You can set whether or not the text should be bold, italic, underlined, or struck-through.
To see how you can use fonts in OpenPyXL, create a new file named
font_sizes.py and add the following code to it:
# font_sizes.py import openpyxl from openpyxl.styles import Font def font_demo(path): workbook = openpyxl.Workbook() sheet = workbook.active cell = sheet["A1"] cell.font = Font(size=12) cell.value = "Hello" cell2 = sheet["A2"] cell2.font = Font(name="Arial", size=14, color="00FF0000") sheet["A2"] = "from" cell2 = sheet["A3"] cell2.font = Font(name="Tahoma", size=16, color="00339966") sheet["A3"] = "OpenPyXL" workbook.save(path) if __name__ == "__main__": font_demo("font_demo.xlsx")
This code uses three different fonts in three different cells. In A1, you use the default, which is Calibri. Then in A2, you set the font size to Arial and increase the size to 14 points. Finally, in A3, you change the font to Tahoma and the font size to 16 points.
For the second and third fonts, you also change the text color. In A2, you set the color to red, and in A3, you set the color to green.
When you run this code, your output will look like this:
Try changing the code to use other fonts or colors. If you want to get adventurous, you should try to make your text bold or italicized.
Now you're ready to learn about text alignment.
You can set alignment in OpenPyXL by using
openpyxl.styles.Alignment. You use this class to rotate the text, set text wrapping, and for indentation.
Here are the defaults that the
Alignment class uses:
It's time for you to get some practice in. Open up your Python editor and create a new file named
alignment.py. Then add this code to it:
# alignment.py from openpyxl import Workbook from openpyxl.styles import Alignment def center_text(path, horizontal="center", vertical="center"): workbook = Workbook() sheet = workbook.active sheet["A1"] = "Hello" sheet["A1"].alignment = Alignment(horizontal=horizontal, vertical=vertical) sheet["A2"] = "from" sheet["A3"] = "OpenPyXL" sheet["A3"].alignment = Alignment(text_rotation=90) workbook.save(path) if __name__ == "__main__": center_text("alignment.xlsx")
You will center the string both horizontally and vertically in A1 when you run this code. Then you use the defaults for A2. Finally, for A3, you rotate the text 90 degrees.
Try running this code, and you will see something like the following:
That looks nice! It would be best if you took the time to try out different
text_rotation values. Then try changing the
vertical parameters with different values. Pretty soon, you will be able to align your text like a pro!
Now you're ready to learn about adding borders to your cells!
OpenPyXL gives you the ability to style the borders on your cell. You can specify a different border style for each of the four sides of a cell.
You can use any of the following border styles:
Open your Python editor and create a new file named
border.py. Then enter the following code in your file:
# border.py from openpyxl import Workbook from openpyxl.styles import Border, Side def border(path): pink = "00FF00FF" green = "00008000" thin = Side(border_style="thin", color=pink) double = Side(border_style="double", color=green) workbook = Workbook() sheet = workbook.active sheet["A1"] = "Hello" sheet["A1"].border = Border(top=double, left=thin, right=thin, bottom=double) sheet["A2"] = "from" sheet["A3"] = "OpenPyXL" sheet["A3"].border = Border(top=thin, left=double, right=double, bottom=thin) workbook.save(path) if __name__ == "__main__": border("border.xlsx")
This code will add a border to cell A1 and A3. The top and bottom of A1 use a "double" border style and are green, while the cell sides are using a "thin" border style and are colored pink.
Cell A3 uses the same borders but swaps them so that the sides are now green and the top and bottom are pink.
You get this effect by creating
Side objects in the
border_style and the
color to be used. Then you pass those
Side objects to a
Border class, which allows you to set each of the four sides of a cell individually. To apply the
Border to a cell, you must set the cell's
When you run this code, you will see the following result:
This image is zoomed in a lot so that you can easily see the borders of the cells. It would be best if you tried modifying this code with some of the other border styles mentioned at the beginning of this section so that you can see what else you can do.
You can highlight a cell or a range of cells by changing its background color. Highlighting a cell is more eye-catching than changing the text's font or color in most cases. OpenPyXL gives you a class called
PatternFill that you can use to change a cell's background color.
PatternFill class takes in the following arguments (defaults included below):
There are several different fill types you can use. Here is a list of currently supported fill types:
Now you have enough information to try setting the background color of a cell using OpenPyXL. Open up a new file in your Python editor and name it
background_colors.py. Then add this code to your new file:
# background_colors.py from openpyxl import Workbook from openpyxl.styles import PatternFill def background_colors(path): workbook = Workbook() sheet = workbook.active yellow = "00FFFF00" for rows in sheet.iter_rows(min_row=1, max_row=10, min_col=1, max_col=12): for cell in rows: if cell.row % 2: cell.fill = PatternFill(start_color=yellow, end_color=yellow, fill_type = "solid") workbook.save(path) if __name__ == "__main__": background_colors("bg.xlsx")
This example will iterate over nine rows and 12 columns. It will set every cell's background color to yellow if that cell is in an odd-numbered row. The cells with their background color changes will be from column A through column L.
When you want to set the cell's background color, you set the cell's
fill attribute to an instance of
PatternFill. In this example, you specify a
start_color and an
end_color. You also set the
fill_type to "solid". OpenPyXL also supports using a
GradientFill for the background.
Try running this code. After it runs, you will have a new Excel document that looks like this:
Here are some ideas that you can try out with this code:
Once you are done experimenting with background colors, you can learn about inserting images in your cells!
OpenPyXL makes inserting an image into your Excel spreadsheets nice and straightforward. To make this magic happen, you use the
add_image() method. This method takes in two arguments:
img- The path to the image file that you are inserting
anchor- Provide a cell as a top-left anchor of the image (optional)
For this example, you will be using the Mouse vs. Python logo:
The GitHub repository for this book has the image for you to use.
Once you have the image downloaded, create a new Python file and name it
insert_image.py. Then add the following:
# insert_image.py from openpyxl import Workbook from openpyxl.drawing.image import Image def insert_image(path, image_path): workbook = Workbook() sheet = workbook.active img = Image("logo.png") sheet.add_image(img, "B1") workbook.save(path) if __name__ == "__main__": insert_image("logo.xlsx", "logo.png")
Here you pass in the path to the image that you wish to insert. To insert the image, you call
add_image(). In this example, you are hard-coding to use cell B1 as the anchor cell. Then you save your Excel spreadsheet.
If you open up your spreadsheet, you will see that it looks like this:
You probably won't need to insert an image into an Excel spreadsheet all that often, but it's an excellent skill to have.
Merged cells are cells where you have two or more adjacent cells merged into one. If you want to set the value of a merged cell with OpenPyXL, you must use the top left-most cell of the merged cells.
You also must use this particular cell to set the style for the merged cell as a whole. You can use all the styles and font settings you used on an individual cell with the merged cell. However, you must apply the style to the top-left cell for it to apply to the entire merged cell.
You will understand how this works if you see some code. Go ahead and create a new file named
style_merged_cell.py. Now enter this code in your file:
# style_merged_cell.py from openpyxl import Workbook from openpyxl.styles import Font, Border, Side, GradientFill, Alignment def merge_style(path): workbook = Workbook() sheet = workbook.active sheet.merge_cells("A2:G4") top_left_cell = sheet["A2"] light_purple = "00CC99FF" green = "00008000" thin = Side(border_style="thin", color=light_purple) double = Side(border_style="double", color=green) top_left_cell.value = "Hello from PyOpenXL" top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double) top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF")) top_left_cell.font = Font(b=True, color="FF0000", size=16) top_left_cell.alignment = Alignment(horizontal="center", vertical="center") workbook.save(path) if __name__ == "__main__": merge_style("merged_style.xlsx")
Here you create a merged cell that starts at A2 (the top-left cell) through G4. Then you set the cell's value, border, fill, font and alignment.
When you run this code, your new spreadsheet will look like this:
Doesn't that look nice? You should take some time and try out some different styles on your merged cell. Maybe come up with a better gradient than the gray one used here, for example.
Now you're ready to learn about OpenPyXL's built-in styles!
OpenPyXL comes with multiple built-in styles that you can use as well. Rather than reproducing the entire list of built-in styles in this book, you should go to the official documentation as it will be the most up-to-date source for the style names.
However, it is worth noting some of the styles. For example, here are the number format styles you can use:
You can also apply text styles. Here is a listing of those styles:
OpenPyXL has several other built-in style groups. You should check out the documentation to learn about all the different styles that are supported.
Now that you know about some of the built-in styles you can use, it's time to write some code! Create a new file and name it
builtin_styls.py. Then enter the following code:
# builtin_styles.py from openpyxl import Workbook def builtin_styles(path): workbook = Workbook() sheet = workbook.active sheet["A1"].value = "Hello" sheet["A1"].style = "Title" sheet["A2"].value = "from" sheet["A2"].style = "Headline 1" sheet["A3"].value = "OpenPyXL" sheet["A3"].style = "Headline 2" workbook.save(path) if __name__ == "__main__": builtin_styles("builtin_styles.xlsx")
Here you apply three different styles to three different cells. You use "Title", "Headline 1" and "Headline 2", specifically.
When you run this code, you will end up having a spreadsheet that looks like this:
As always, you should try out some of the other built-in styles. Trying them out is the only way to determine what they do and if they will work for you.
But wait! What if you wanted to create your style? That's what you will cover in the next section!
You can create custom styles of your design using OpenPyXL as well. To create your style, you must use the
NamedStyle class takes the following arguments (defaults are included too):
You should always provide your own
name to your
NamedStyle to keep it unique. Go ahead and create a new file and call it
named_style.py. Then add this code to it:
# named_style.py from openpyxl import Workbook from openpyxl.styles import Font, Border, Side, NamedStyle def named_style(path): workbook = Workbook() sheet = workbook.active red = "00FF0000" font = Font(bold=True, size=22) thick = Side(style="thick", color=red) border = Border(left=thick, right=thick, top=thick, bottom=thick) named_style = NamedStyle(name="highlight", font=font, border=border) sheet["A1"].value = "Hello" sheet["A1"].style = named_style sheet["A2"].value = "from" sheet["A3"].value = "OpenPyXL" workbook.save(path) if __name__ == "__main__": named_style("named_style.xlsx")
Here you create a
Border() instance to pass to your
NamedStyle(). Once you have your custom style created, you can apply it to a cell by setting the cell's
style attribute. Applying a custom style is done in the same way as you applied built-in styles!
You applied the custom style to the cell, A1.
When you run this code, you will get a spreadsheet that looks like this:
Now it's your turn! Edit the code to use a
Side style, which will change your border. Or create multiple
Side instances so you can make each side of the cell unique. Play around with different fonts or add a custom background color!
You can do a lot of different things with cells using OpenPyXL. The information in this article gives you the ability to format your data in beautiful ways.
In this article, you learned about the following topics:
You can take the information that you learned in this article to make beautiful spreadsheets. You can highlight exciting data by changing the cell's background color or font. You can also change the cell's format by using a built-in style. Go ahead and give it a try.
Experiment with the code in this article and see how powerful and valuable OpenPyXL is when working with cells.
Copyright © 2021 Mouse Vs Python | Powered by Pythonlibrary