[Python and Office software] xlwt and Excel table: cell format, font format, alignment, border and filling, etc

Keywords: Python Excel Windows

;

Python xlwt module Excel table foundation 3: cell format, font format, alignment, border and fill, etc

preface:

The first two sections of the blog introduce some basic operations of xlwt on Excel tables. The last two sections are as follows:

1: Cell write, merge, insert bitmap, etc
2: Freezing windows, setting encryption protection, printing settings, etc

This blog mainly introduces cell format settings, including:

1. Cell data type;
2. Font setting;
3. Alignment;
4. Border setting;
5. Filling setting;
6. Cell protection.

1. Data type settings

Here we still use the method of the previous section. First, we create two sheet tables, write data, and then set a custom cell format for the Tset sheet table, and the T2 sheet table is written in the default cell format.

# Import module
import xlwt

work_book = xlwt.Workbook()
work_sheet = work_book.add_sheet('Test')
w2 = work_book.add_sheet('T2')
# Create data
z = [[r,c] for r in range(20) for c in range(20)]
[l.append(str(i)) for i,l in enumerate(z)]

Cell formatting method:

# Style
my_style_1 = xlwt.XFStyle()
# Create cell data type, numeric type, default: 'General'
my_style_1.num_format_str = '0'
# Write data, Test sheet object in custom format, T2 sheet object in default format
for info in z:
    # Write data and Format Cells
    work_sheet.write(info[0],info[1],info[2],my_style_1)
    w2.write(info[0],info[1],info[2])
    
# Save file
work_book.save('Test3.xls')

The data formats that can be set are:

# Cell data type  #######################################################
'''
            'general',
            '0',
            '0.00',
            '#,##0',
            '#,##0.00',
            '"$"#,##0_);("$"#,##0)',
            '"$"#,##0_);[Red]("$"#,##0)',
            '"$"#,##0.00_);("$"#,##0.00)',
            '"$"#,##0.00_);[Red]("$"#,##0.00)',
            '0%',
            '0.00%',
            '0.00E+00',
            '# ?/?',
            '# ??/??',
            'M/D/YY',
            'D-MMM-YY',
            'D-MMM',
            'MMM-YY',
            'h:mm AM/PM',
            'h:mm:ss AM/PM',
            'h:mm',
            'h:mm:ss',
            'M/D/YY h:mm',
            '_(#,##0_);(#,##0)',
            '_(#,##0_);[Red](#,##0)',
            '_(#,##0.00_);(#,##0.00)',
            '_(#,##0.00_);[Red](#,##0.00)',
            '_("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)',
            '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
            '_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)',
            '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)',
            'mm:ss',
            '[h]:mm:ss',
            'mm:ss.0',
            '##0.0E+0',
            '@'
'''

2. Font settings

First create a font object:

# Create font
font = my_style_1.font

Then set the font name, font height, bold and other aspects:

Settable (default):

# self.height = 0x00C8  # 200: this is font with height 10 points
# self.italic = False
# self.struck_out = False
# self.outline = False
# self.shadow = False
# self.colour_index = 0x7FFF
# self.bold = False
# self._weight = 0x0190  # 0x02BC gives bold font
# self.escapement = self.ESCAPEMENT_NONE
# self.underline = self.UNDERLINE_NONE
# self.family = self.FAMILY_NONE
# self.charset = self.CHARSET_SYS_DEFAULT
# self.name = 'Arial'

example:

# Set font name
font.name = 'Times New Roman '
# Set font height
font.height = 400
# Set italics
font.italic = True
# Set strikethrough
font.struck_out = True
# Set profile
font.outline = True
# Set shadow
font.shadow = True
# Set font color, blue
font.colour_index = 0x0C
# Set bold
font.bold = True
# Set font superscript and superscript
# font.escapement = 1
# Set underline, double underline
font.underline = 2
# Set character set, GBK
font.charset = 0x86

Set up renderings:


Get font format information:

font_info = font._search_key()
print(font_info)
# Print results:
# (400, True, True, True, True, 12, True, 400, 0, 2, 0, 134, 'Times New Roman ')

Setting Description:

The above settings set the font color to blue:

font.colour_index = 0x0C

Font color options:

# Color index  #######################################################
"""
aqua 0x31
black 0x08
blue 0x0C
blue_gray 0x36
bright_green 0x0B
brown 0x3C
coral 0x1D
cyan_ega 0x0F
dark_blue 0x12
dark_blue_ega 0x12
dark_green 0x3A
dark_green_ega 0x11
dark_purple 0x1C
dark_red 0x10
dark_red_ega 0x10
dark_teal 0x38
dark_yellow 0x13
gold 0x33
gray_ega 0x17
gray25 0x16
gray40 0x37
gray50 0x17
gray80 0x3F
green 0x11
ice_blue 0x1F
indigo 0x3E
ivory 0x1A
lavender 0x2E
light_blue 0x30
light_green 0x2A
light_orange 0x34
light_turquoise 0x29
light_yellow 0x2B
lime 0x32
magenta_ega 0x0E
ocean_blue 0x1E
olive_ega 0x13
olive_green 0x3B
orange 0x35
pale_blue 0x2C
periwinkle 0x18
pink 0x0E
plum 0x3D
purple_ega 0x14
red 0x0A
rose 0x2D
sea_green 0x39
silver_ega 0x16
sky_blue 0x28
tan 0x2F
teal 0x15
teal_ega 0x15
turquoise 0x0F
violet 0x14
white 0x09
yellow 0x0D
"""

Font superscript setting, underline setting, character set setting:

# Font superscript
font.escapement = 1
# Set underline, double underline
font.underline = 2
# Set character set, GBK
font.charset = 0x86

Resolution:

Here, the default font superscript and subscript settings of cells are:

self.escapement = self.ESCAPEMENT_NONE

Look up the following index: Escape element_ NONE = 0x00
If we want to set cell as superscript, superscript is: Escape element_ Superscript, corresponding value: 0x01 (16 digits)
And cell format:

font.escapement = 0x01
//Or:
font.escapement = 1

The other settings that appear below are similar in principle. If they appear again, they will not be explained.

Superscript, underscore, character set index:

ESCAPEMENT_NONE         = 0x00
# Superscript
ESCAPEMENT_SUPERSCRIPT  = 0x01
# subscript
ESCAPEMENT_SUBSCRIPT    = 0x02
# Underline optional
UNDERLINE_NONE          = 0x00
UNDERLINE_SINGLE        = 0x01
UNDERLINE_SINGLE_ACC    = 0x21
UNDERLINE_DOUBLE        = 0x02
UNDERLINE_DOUBLE_ACC    = 0x22
# Character set optional
CHARSET_ANSI_LATIN          = 0x00
CHARSET_SYS_DEFAULT         = 0x01
CHARSET_SYMBOL              = 0x02
CHARSET_APPLE_ROMAN         = 0x4D
CHARSET_ANSI_JAP_SHIFT_JIS  = 0x80
CHARSET_ANSI_KOR_HANGUL     = 0x81
CHARSET_ANSI_KOR_JOHAB      = 0x82
CHARSET_ANSI_CHINESE_GBK    = 0x86
CHARSET_ANSI_CHINESE_BIG5   = 0x88
CHARSET_ANSI_GREEK          = 0xA1
CHARSET_ANSI_TURKISH        = 0xA2
CHARSET_ANSI_VIETNAMESE     = 0xA3
CHARSET_ANSI_HEBREW         = 0xB1
CHARSET_ANSI_ARABIC         = 0xB2
CHARSET_ANSI_BALTIC         = 0xBA
CHARSET_ANSI_CYRILLIC       = 0xCC
CHARSET_ANSI_THAI           = 0xDE
CHARSET_ANSI_LATIN_II       = 0xEE
CHARSET_OEM_LATIN_I         = 0xFF

3. Cell alignment

Establish alignment objects and set alignment properties:

Settable (default):

# self.horz = self.HORZ_GENERAL
# self.vert = self.VERT_BOTTOM
# self.dire = self.DIRECTION_GENERAL
# self.orie = self.ORIENTATION_NOT_ROTATED
# self.rota = self.ROTATION_0_ANGLE
# self.wrap = self.NOT_WRAP_AT_RIGHT
# self.shri = self.NOT_SHRINK_TO_FIT
# self.inde = 0
# self.merg = 0

example:

# Establish a way to:
alignment = my_style_1.alignment
# Horizontal alignment, horizontal center
alignment.horz = 2
# Vertical alignment, vertical center
alignment.vert = 1
# Rotation direction, set rotation direction 45
alignment.rota = 45
# Auto indent settings
alignment.shri = 1

Set up renderings:


Get alignment information:

# Get current alignment
alignment_info = alignment._search_key()
print(alignment_info)
# Print results:
# (2, 1, 0, 0, 45, 0, 1, 0, 0)

Other alignment indexes:

# Horizontal alignment
HORZ_GENERAL                = 0x00
HORZ_LEFT                   = 0x01
HORZ_CENTER                 = 0x02
HORZ_RIGHT                  = 0x03
HORZ_FILLED                 = 0x04
HORZ_JUSTIFIED              = 0x05 # BIFF4-BIFF8X
HORZ_CENTER_ACROSS_SEL      = 0x06 # Centred across selection (BIFF4-BIFF8X)
HORZ_DISTRIBUTED            = 0x07 # Distributed (BIFF8X)
# Vertical alignment
VERT_TOP                    = 0x00
VERT_CENTER                 = 0x01
VERT_BOTTOM                 = 0x02
VERT_JUSTIFIED              = 0x03 # Justified (BIFF5-BIFF8X)
VERT_DISTRIBUTED            = 0x04 # Distributed (BIFF8X)
# Rotation angle
ROTATION_0_ANGLE            = 0x00
ROTATION_STACKED            = 0xFF
# Auto indent settings
SHRINK_TO_FIT               = 0x01
NOT_SHRINK_TO_FIT           = 0x00

4. Cell border settings

To create a cell border object, set the property value:

Settable (default):

# self.left = self.NO_LINE
# self.right = self.NO_LINE
# self.top = self.NO_LINE
# self.bottom = self.NO_LINE
# self.diag = self.NO_LINE

# self.left_colour = 0x40
# self.right_colour = 0x40
# self.top_colour = 0x40
# self.bottom_colour = 0x40
# self.diag_colour = 0x40

# self.need_diag1 = self.NO_NEED_DIAG1
# self.need_diag2 = self.NO_NEED_DIAG2

example:

borders = my_style_1.borders
# Left border thin line
borders.left = 1
# Center thin line of right border
borders.right = 2
# Dashed top border
borders.top = 3
# Bottom border dotted line
borders.bottom = 4
# Thick line of inner border
borders.diag = 5

# Left border color blue
borders.left_colour = 0x0C
# Right border color gold
borders.right_colour = 0x33
# Top border color green
borders.top_colour = 0x11
# Bottom border color red
borders.bottom_colour = 0x0A
# Yellow inner border
borders.diag_colour = 0x0D

Setting effect:


Get border setting information:

borders_info = borders._search_key()
print(borders_info)
# Print results
# (1, 2, 3, 4, 5, 12, 51, 17, 10, 13, 0, 0)

Border line index:

NO_LINE = 0x00
THIN    = 0x01
MEDIUM  = 0x02
DASHED  = 0x03
DOTTED  = 0x04
THICK   = 0x05
DOUBLE  = 0x06
HAIR    = 0x07
#The following for BIFF8
MEDIUM_DASHED               = 0x08
THIN_DASH_DOTTED            = 0x09
MEDIUM_DASH_DOTTED          = 0x0A
THIN_DASH_DOT_DOTTED        = 0x0B
MEDIUM_DASH_DOT_DOTTED      = 0x0C
SLANTED_MEDIUM_DASH_DOTTED  = 0x0D

Note: for border color setting, please refer to font color setting index.

5. Fill settings

To create a fill object, set properties:

Settable (default):

# self.pattern = self.NO_PATTERN
# self.pattern_fore_colour = 0x40
# self.pattern_back_colour = 0x41

example:

# Fill settings
pat = my_style_1.pattern
# Open fill
pat.pattern = 1
# Fill foreground, purple
pat.pattern_fore_colour = 0x14
# Fill background color, purple
pat.pattern_back_colour = 0x14

design sketch:


Note: it seems that the xlwt module only provides color fill setting method, while the hatch method is not provided. Color index see font color index.

6. Cell protection

Establish the protection object and set the properties:

Settable (default):

# self.cell_locked = 1
# self.formula_hidden = 0

example:

# Set cell lock
protection.cell_locked = 1
# Set hidden formula in cell
protection.formula_hidden = 1
# Only valid if sheet table is set to protected
work_sheet.set_protect(1)

Note: it is only valid when the sheet table is set as protected (for the effect, please refer to the table protection content in Section 2).

last:

Thank you for reading.

[Python and Office software] a series of articles will explain the use of common Office software processing modules in Python. Interested friends can pay attention to and collect them.

Posted by PallaviDalvi on Thu, 18 Jun 2020 01:01:27 -0700