Headline account: KeLei software testing
Study catalog
Understand the excel table file format on the computer
Install xlwt library
xlwt library writes table content
1 Import xlwt library
2 Use a picture to show the commonly used functions of xlwt
3 Write some content into the table and save it
4 Set style
1 Initialize XFStyle first
2 Set the font font
3 Set borders
4 Set alignment
5 Set up cells
6 Set row height and column width
7 Merge cells
Excel tables are a file format that is often used by everyone, and all walks of life will deal with it. This time we introduce two classic libraries that are often used, xlrd and xlwt. xlrd is used to read the contents of excel tables, and xlwt is used to write the contents of excel tables.
Understand the excel table file format on your computer
When editing and saving Excel tables from Microsoft or Kingsoft, you generally need to select the file suffix, which includes xls and xlsx.
The main differences between xls and xlsx suffix files:
- File format: xls is a binary format, while xlsx is an XML-based compression method.
- Version: xls is the file format generated by Excel 2003 and previous versions, while xlsx is the file format generated by Excel 2007 and later versions.
- Compatibility: The xlsx format is backward compatible, but the xls format does not support backward compatibility.
Install xlwt library
pip install xlwt -i https://mirrors.aliyun.com/pypi/simple/
xlwt library writes table content
1 Import xlwt library
Execute import xlwt to import the library
2 Use a picture to show the commonly used functions of xlwt
3 Write some content into the table and save it (without setting the style)
# encoding: Set encoding, the default is ascii, generally set to utf-8, and can support Chinese;
#style_compression Just keep the default
work_book = xlwt.Workbook(encoding='utf-8')
# Create a sheet object, which is equivalent to creating a sheet page and fill in the name of the sheet page.
sheet_data = work_book.add_sheet('sheet1')
# Add data to the sheet page: function write, the parameters are brought in (row number, column number, filled in value), and the rows and columns start from 0.
#There is also a parameter style=Style.default_style, which is used to set the font/cell format/alignment, etc. If not set, the default value will be used.
sheet_data.write(0,0,'username') # Write data in row 1 and column 1. No style is needed here. We will introduce it later.
sheet_data.write(0,1,'age') # Write data in row 1 and column 2. No style is needed here. We will introduce it later.
sheet_data.write(1,0,'Chapter 3') # Write data in row 1 and column 1. No style is needed here, which will be introduced later.
sheet_data.write(1,1,'22') # Write data in row 1 and column 2. No style is needed here. We will introduce it later.
#Save as an excel table with the suffix xls or xlsx
#When saving as xlsx, the subsequent setting style will not take effect, so we save it as an xls suffix file
work_book.save('1.xls')
After the program is executed, an excel table will be generated in the same directory as the program, as shown below after opening:
4 Set style
As mentioned above, the write function has a default parameter style=Style.default_style, and Style.default_style refers to a class Style.XFStyle under xlwt
Several commonly used properties in the XFStyle class:
- num_format_str represents the data format
- font represents the font
- alignment means alignment
- borders means borders
1 Initialize XFStyle first
style = xlwt.XFStyle()# Initialization style
2 Set font
Because the __init__.py file of xlwt has imported the Font class from the Formatting module, we define the font style directly through xlwt.Font()
Font class initialization parameters:
The specific explanation is as follows:
|
height |
font size. The default value of 200 represents a font size of 10 (200 is 20*10, where 20 is the unit of measurement and 10 is the font size) |
|
italic |
Whether to set italics. |
|
struck_out |
Whether to set strikethrough. |
|
outline |
Whether to set the outline |
|
shadow |
Whether to set shadow |
|
color_index |
font color |
|
bold |
Whether to set bold |
|
weight |
Set stroke width |
|
escapement |
Whether to set as superscript and subscript |
|
underline |
Whether to set underline |
|
family |
Set font set. |
|
charset |
Set character set |
|
name |
Set font name |
The font color has some setting values found in the style.py file:
We set the font as follows:
#Create font (font) for style font = xlwt.Font() # Specify some common attributes of fonts font.name = 'Song' #Specify font font.height = 200 #The ratio of font size to excel is 1:20 font.bold = True # Whether the font is bold font.underline = True # Whether the font is underlined font.struck_out = True # Whether the font has horizontal lines font.italic = True # Whether to italicize font.colour_index = 0x3C # Font color brown # Set font style style.font = font
Change the write function to the following and re-execute it. Check the table as follows. The font setting has taken effect.
# Write data in row 1 and column 1 sheet_data.write(0,0,'username',style=style) # Write data in row 1 and column 2 sheet_data.write(0,1,'age',style=style) # Write data in row 1 and column 1 sheet_data.write(1,0,'Chapter Three',style=style) # Write data in row 1 and column 2 sheet_data.write(1,1,'22',style=style)
3 Set borders
Just like setting the font, we define the border style directly through xlwt.Borders().
Borders class initialization parameters: 
The attributes are explained in detail as follows:
|
left |
Set left border |
|
right |
Set right border |
|
top |
Set top border |
|
bottom |
Set bottom border |
|
diag |
Set diagonal style |
|
left_colour |
Set left border color |
|
right_colour |
Set right border color |
|
top_colour |
Set top border color |
|
bottom_colour |
Set bottom border color |
|
diag_colour |
Set diagonal color |
|
need_diag1 |
Set whether to display the upper left-lower right diagonal line |
|
need_diag2 |
Set whether to display the lower left-upper right diagonal line |
The values that can be filled in for each attribute are marked with a red box in the picture above. For example, set it to a thick solid line, corresponding to THICK (0x05)
We set the border as follows:
#Border class initialization borders = xlwt.Borders() #Define attribute values borders.top = 0x04 #Top border style borders.bottom = 0x05 #Bottom border style borders.left = 0x06 #Left border style borders.right = 0x07 #Right border style borders.top_colour = 0x08 #Top border color borders.bottom_colour = 0x36 #Lower border color borders.left_colour = 0x11 #Left border color borders.right_colour = 0x12 #Right border color borders.need_diag1 = 1 #Set to display the upper left-lower right diagonal line borders.diag = 0x02 #Set diagonal style borders.diag_colour = 0x30 #Set diagonal color #Assign value to style style.borders = borders
Add 2 lines of write function as follows, re-execute, check the table as follows, the border setting has taken effect
# Write data in row 3 and column 3 sheet_data.write(2,2,'',style=style) # Write data in row 4 and column 4 sheet_data.write(3,3,'',style=style)
4 Set alignment
Just like setting the font, we define the alignment style directly through xlwt.Alignment().
Alignment class initialization parameters: 
The main attributes are explained in detail as follows:
|
horz |
Set horizontal alignment: Normal, left aligned, centered, right aligned, padded, justified, centered across columns, scattered alignment |
|
vert |
Set vertical alignment: Top alignment, vertical center, bottom alignment, both ends alignment, scattered alignment |
|
dire |
Set text direction: Depending on the content, always left to right, always right to left |
|
rota |
Set rotation direction |
|
wrap |
Whether to set automatic line wrapping |
The values that can be filled in for each attribute are marked with a red box in the picture above. For example, if the vertical direction of the text is set to top, the corresponding VERT_TOP= 0x00
We set the alignment as follows:
#Set alignment #Alignment class initialization alignment = xlwt.Alignment() #Define attribute values alignment.horz = 0x02 # Set the horizontal alignment to centered alignment.vert = 0x00 # Set the vertical alignment to top alignment.wrap = 0x01 # Set automatic line wrapping style.alignment = alignment
Add 3 lines of write function as follows, re-execute, check the table as follows, the alignment setting has taken effect
# Write data in row 5 and column 5 sheet_data.write(4,4,'1',style=style) # Write data in row 6 and column 6 sheet_data.write(5,5,'2',style=style) # Write data in row 7 and column 7 sheet_data.write(6,6,'sssssssssssssssss',style=style)
5 Set cells
We directly define the cell format as follows
#Set cell format style.num_format_str = '0.00' #Numbers retain 2 decimal places
The format of the cell is defined in the style.py file as follows:
It is the same as the custom format in the cell format we define in the excel table.
Add 1 line of write function as follows, re-execute, check the table as follows, the numbers have retained 2 decimal places
6 Set row height and column width
work_book = xlwt.Workbook(encoding='utf-8')
# Create a sheet object, which is equivalent to creating a sheet page. Enter the name of the sheet page.
sheet_data = work_book.add_sheet('sheet1')
#The above is the code mentioned above, configure the row height and column width in the sheet object
#Configure row height and column width in the sheet object
#Set the column width of each column. The parameter of the function is the column number, starting from 0
sheet_data.col(0).width = 256*20
#256 is the unit of measurement, 20 represents the width of 20 characters
#Set row height
height_set = xlwt.easyxf(f'font:height {20*40}') #20 is the unit, 40 means 40px
sheet_data.row(0).set_style(height_set) #Parameter 0 represents the first row
After executing the write, the row height and column width of row 1 and column 1 have been set successfully.
7 Merge cells
Use the write_merge function to merge multiple cells and write the content
# Rows 8 to 9 and columns 8 to 9 are combined into one cell and write data sheet_data.write_merge(8, 9, 8, 9, 'Merge data') After executing the write, it is as follows:
Summary
In the above code, only one style is used for all wtite operations. In actual use, you need to define different styles according to the actual situation.
—-Thank you readers for reading and learning, thank you all.
Encourage each other: Ban Gu of the Eastern Han Dynasty, “Hanshu Meicheng Biography”: “The tube of Mount Tai penetrates the stone, and the unipolar husk breaks the stem. Water is not the drill of stone, and the rope is not the saw of wood. Gradual decline makes it happen.”
—– refers to water droplets dripping continuously, which can penetrate stones;
—–It is a metaphor that if you persevere and gather even a small amount of strength, you can accomplish incredible feats.