Style by using xlwt
- Preface
- 1. Create excel and workbook
- 2. Set font style
-
- 1. Create a font style
- 2. Set style
- 3. Create a style object
- 4. Use styles
- 3. Set the border style
-
- 1. Create a border style
- 2. Set style
- 3. Create a style object
- 4. Use styles
- 4. Set content position style
-
- 1. Create a content position style
- 2. Set style
- 3. Modify cell size
- 4. Create a style object
- 4. Use styles
- 5. Set the background color style
-
- 1. Create a background color style
- 2. Set style
- 3. Create a style object
- 4. Use styles
- 6. Set styles through easyxf
-
- 1. Create a style object
- 2. Use easyxf to set the style
- 3. Use styles
Foreword
Excel files are made up of many worksheets, and each worksheet contains rows and columns of data. xlwt allows you to create a new Excel file, add worksheets, and populate their data. In addition to basic writing operations, it also provides functions for setting styles, fonts and borders.
1. Create excel and workbook
code show as below:
# Create an excel file wb = xlwt.Workbook() # Create a workbook ws = wb.add_sheet('data') # add content ws.write(1, 1, '123') ws.write(2, 2, '2233', style1)
Simply create an excel and workbook for later testing of setting styles
2. Set font style
1. Create a font style
code show as below:
ft = xlwt.Font()
Create a font style object for later setting a specific font style
2. Set style
code show as below:
# Set font ft.name = 'Microsoft Yahei' # Set font size ft.height = 30 * 20 # Set the font to be bold ft.bold = True # Set font underline ft.underline = True #Set font italic ft.italic=True # Set the font color ft.colour_index = 2
name
: Set the font type (the specific value is the excel font, which needs to be available locally on the computer).
height
: Set font size
bold
: Set whether the font is bold
underline
: Set whether the font has underline
italic
: Set whether the font is italic
colour_index
: Set the font color
3. Create a style object
code show as below:
# Create a style object style1 = xlwt.XFStyle() style1.font = ft
xlwt.XFStyle()
: Create a style object, in which many styles can be set. Here, the previously created font style is assigned to the style object.
4. Use styles
code show as below:
ws.write(2, 2, '2233', style1)
When writing data, the last property is set to the style object created.
3. Set border style
The usage is basically similar to the font style. The main difference is that the properties of the border style are different.
1. Create a border style
code show as below:
boder = xlwt.Borders()
Create a border style object to set the specific border style later.
2. Set style
code show as below:
# Type of line: thin solid line: 1, small thick solid line: 2, thin dotted line: 3, medium thin dotted line: 4, large thick solid line: 5, double line: 6, thin dotted line: 7 boder.top = 1 boder.bottom = 2 border.left = 3 border.right = 4 # Line color border.top_colour = 3 border.bottom_colour = 4 border.left_colour = 5 border.right_colour = 6
top
, bottom
, left
, and right
respectively represent the borders of a cell in four directions, which can be Set specific values.
Among them: thin solid line: 1, small thick solid line: 2, thin dotted line: 3, medium thin dotted line: 4, large thick solid line: 5, double line: 6, thin dotted line: 7
top_colour
, bottom_colour
, left_colour
, right_colour
respectively represent the colors of lines in four directions on a cell. Can be set separately.
3. Create a style object
code show as below:
# Create a style object style1 = xlwt.XFStyle() style1.borders = borders
xlwt.XFStyle()
: Create a style object, in which many styles can be set. Here, the previously created font style is assigned to the style object.
4. Use styles
code show as below:
ws.write(2, 2, '2233', style1)
When writing data, the last property is set to the style object created.
4. Set content position style
The usage is basically similar to the above style
1. Create a content position style
code show as below:
align = xlwt.Alignment()
Create a content position style object to set the specific content position later.
2. Set style
code show as below:
# Set the upper and lower positions 0 up 1 middle 2 down align.vert = 1 # Set the left and right positions 1 left 2 middle 3 right align.horz = 2
vert
: Set the upper and lower position of the content in the cell (0 upper, 1 middle, 2 lower)
horz
: Set the left and right position of the content in the cell (1 left, 2 middle, 3 right)
3. Modify cell size
Because when testing the content position style, it is difficult to see the effect using the original cell size. So, set the cell size here to enhance the effect. code show as below:
# If you need to set the cell height, you need to enable the setting height attribute. ws.row(2).height_mismatch = True # Enable permission to set height ws.row(2).height = 50 * 256 ws.col(2).width = 50
To set the size of a cell, you need to modify the properties of the cell in the specified column and row.
4. Create a style object
code show as below:
# Create a style object style1 = xlwt.XFStyle() style1.alignment = align
xlwt.XFStyle()
: Create a style object, in which many styles can be set. Here, the previously created font style is assigned to the style object.
4. Use styles
code show as below:
ws.write(2, 2, '2233', style1)
When writing data, the last property is set to the style object created.
5. Set background color style
The usage is basically similar to the above style
1. Create a background color style
code show as below:
pattern = xlwt.Pattern()
Create a background color style object to set the specific background color later.
2. Set style
code show as below:
# Set to solid color pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 3
pattern
: Set the type of color
pattern_fore_colour
: Set a specific color
3. Create a style object
code show as below:
# Create a style object style1 = xlwt.XFStyle() style1.pattern = pattern
xlwt.XFStyle()
: Create a style object, in which many styles can be set. Here, the previously created font style is assigned to the style object.
4. Use styles
code show as below:
ws.write(2, 2, '2233', style1)
When writing data, the last property is set to the style object created.
6. Set styles through easyxf
1. Create a style object
code show as below:
style2 = xlwt.XFStyle()
2. Use easyxf to set styles
style2 = xlwt.easyxf('font:bold on, color_index 5;align:vert center,horiz center')
Here, the string in xlwt.easyxf
is the specific attribute value, different styles are separated by semicolons ;
, and different attributes in the same style are separated by commas ,
separated, the name and value of the attribute are separated by spaces
.
3. Use styles
The method of using styles here is the same as above, the code is as follows:
ws.write(2, 2, '2233', style2)
When writing data, the last property is set to the style object created.