Styling by using xlwt

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.