During the work we need to automate some reports frequently, including
manipulating excel workbook. In this blog I will introduce some basic
manipulations on excel workbook by
In the following, I will create a workbook, write values into one worksheet, and some handling like merging cells, set font size/color/type, set background color, set borders, etc.
Before coding, I’ll import all packages we need.
Now, let’s start!
Workbook() helps us to create a workbook, use
active module to assign actual
worksheet and use
title module to name it.
We can write values into specified cells with pattern
worksheet[cells] = value.
Here I iterate a map, who contains values and cells’ positions, to write values
Font (size, type, bold, italic)
openpyxl, we can set font styles(size/color/type) by different
Font(). Then assign
worksheet[cells].font equals to
Cut and paste values from C4:F4 to C5:F5
Here I use
string.ascii_uppercase to generate automatically from letter C to
letter F, and copy values from row 4 to row 5. But if we only copy value, font
style will be lost, so I also copied cell’s style with
._style for the one
Remove row 4
Since we don’t need the fourth row any more, I removed it by
Merge C1:D2 and set number format
Then I merge cells from D1 to E2 with
worksheet.merge_cells. If you want to
set numbers’ format, like currency or display a thousands separator, you can
Set cells’ background colors and center characters
When we want to highlight header, adding the background color might be a good
idea. Then we can assign
PatternFill(), with RBG
colors. Moreover, I wanted to center horizontal and vertical alignment for all
cells. So I iterated all cells, and set their
alignment module as center.
Wrap text in cells
Since characters in each cell of row 4 are a little bit more, we can wrap them
Alignment() for each cell in row 4.
Reset columns’ width
Sometimes, a cell contains lots of characters and cannot be displayed by default.
In this case, if you want to display all, you can set cell’s width, with pattern
Next, I used a function
set_border() that found on stackoverflow.
In this function, it used
Border() to set outside borders for
Hide grid and set zoom scale
Furthermore, if you want to hide all grid lines or set zoom scale, you can use
Create named range
workbook.create_named_range() create named ranges from C4 to C6, in ws_test
worksheet, named ‘ZoneId’.
Finally, save the workbook to given path with
Voila, result as follows:
- “0*eSQte3e-rJeH7bu8”, miro.medium.com. [Online]. Available: https://miro.medium.com/max/1400/0*eSQte3e-rJeH7bu8.jpg