python module xlwt writes .xls file

There are many modules for Python to operate Excel, and each has its own advantages and disadvantages. Different modules support different operations and file types. The following is the support of each module:

xlrd: xlrd reads .xls files
xlwings: xlwings reads and writes Excel files
openpyxl: openpyxl reads and writes .xlsx files

1. Installation

pip install xlwt

Write content to xls file

import xlwt
#Creating a workbook object is equivalent to creating an Excel file
workbook = xlwt.Workbook(encoding='utf-8',style_compression=0) #encoding: set encoding, can write Chinese; style_compression: whether to compress
# Create a sheet object, which is equivalent to creating a sheet page
worksheet = workbook.add_sheet('This is sheet1', cell_overwrite_ok=True) # cell_overwrite_ok: Whether the cell can be overwritten, the default is False
# Add data to the sheet page: worksheet.write(row, column, value)
style = xlwt.XFStyle()# Initialize style
worksheet.write(0,0,'I'm going to get rich',style) # Write data in row 1, column 1, style: style
# Save the above content to the specified file
workbook.save('test file.xls')

3. Set style

style = xlwt.XFStyle()# Initialize style
font = xlwt.Font()# Set font style (font)
pattern = xlwt.Pattern() #Background color setting

Font style

font.name = 'Song' # specify the font
font.height = 300 # and excel font size ratio 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

#0x01
#0x02
#0x03
#0x04
#0x05
font.family=0x01

#xlwt.Font.CHARSET_ANSI_LATIN = 0x00C
#xlwt.Font.HARSET_SYS_DEFAULT= 0x01C
#xlwt.Font.HARSET_SYMBOL= 0x02
#xlwt.Font.CHARSET_APPLE_ROMAN= 0x4D
#xlwt.Font.CHARSET_ANSI_JAP_SHIFT_JIS= 0x80C
#xlwt.Font.HARSET_ANSI_KOR_HANGUL= 0x81
#xlwt.Font.CHARSET_ANSI_KOR_JOHAB= 0x82
#xlwt.Font.CHARSET_ANSI_CHINESE_GBK= 0x86
#xlwt.Font.CHARSET_ANSI_CHINESE_BIG5= 0x88
#xlwt.Font.CHARSET_ANSI_GREEK= 0xA1
#xlwt.Font.CHARSET_ANSI_TURKISH= 0xA2
#xlwt.Font.CHARSET_ANSI_VIETNAMESE= 0xA3
#xlwt.Font.CHARSET_ANSI_HEBREW= 0xB1
#xlwt.Font.CHARSET_ANSI_ARABIC= 0xB2
#xlwt.Font.CHARSET_ANSI_BALTIC= 0xBA
#xlwt.Font.CHARSET_ANSI_CYRILLIC= 0xCC
#xlwt.Font.CHARSET_ANSI_THAI= 0xDE
#xlwt.Font.CHARSET_ANSI_LATIN_II= 0xEE
#xlwt.Font.CHARSET_OEM_LATIN_I= 0xFF
font.charset = 0x86 #Set character set

#You can refer to the value in xlwt.Style.colour_map. The color_map attribute returns a dictionary with the serial number corresponding to the color. font.colour_index =xlwt.Style.colour_map['blue']
font.colour_index = 4 # Font color

# xlwt.Font.ESCAPEMENT_SUBSCRIPT Set the font to be suspended below
# xlwt.Font.ESCAPEMENT_SUPERSCRIPT Set the font to be suspended above
# xlwt.Font.ESCAPEMENT_NONE Setting the font does not have this effect
font.escapement==xlwt.Font.ESCAPEMENT_SUPERSCRIPT

font.num_format_str = '#,##0.00' # set text mode

style.font = font # Set font style

Background color setting

pattern = xlwt.Pattern()
#Create schema object

#xlwt.Pattern.SOLID_PATTERN= 0x01 sets the pattern to real
#xlwt.Pattern.NO_PATTERN= 0x00 Set mode to no pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN #Set the background color pattern

#You can refer to the value in xlwt.Style.colour_map. The color_map attribute returns a dictionary with the serial number corresponding to the color. pattern.pattern_fore_colour =xlwt.Style.colour_map['blue']
pattern.pattern_fore_colour = 3 # The foreground color will not affect the cell border
pattern.pattern_back_colour = 0x41 # The background will affect the cell border

style.pattern = pattern # Set the background pattern style

# Set background color
xlwt.add_palette_colour("custom_colour", 0x21)
workbook.set_colour_RGB(0x21, 219, 229, 241)

Border settings

borders = xlwt.Borders()
borderinfo = borders._search_key() #Attributes that can be set
prit(borderinfo)
# Set border properties
# xlwt.Borders.NO_LINE = 0x00 no borders
# xlwt.Borders.THIN= 0x01 thin solid line
# xlwt.Borders.MEDIUM= 0x02 medium solid line
# xlwt.Borders.DASHED = 0x03 medium thin dashed line
# xlwt.Borders.DOTTED= 0x04 thin dotted line
# xlwt.Borders.THICK= 0x05 thick solid line
# xlwt.Borders.DOUBLE0x06 Double bottom line
# xlwt.Borders.HAIR= 0x07 thin dotted line·
# xlwt.Borders.MEDIUM_DASHED= 0x08 thick dashed line
# xlwt.Borders.THIN_DASH_DOTTED= 0x09 thin dotted line
# xlwt.Borders.MEDIUM_DASH_DOTTED= 0x0A thick dotted line
# xlwt.Borders.THIN_DASH_DOT_DOTTED= 0x0B thin double-dotted line
# xlwt.Borders.MEDIUM_DASH_DOT_DOTTED= 0x0C thick double-dot dash line
# xlwt.Borders.SLANTED_MEDIUM_DASH_DOTTED= 0x0D dash-dash line
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
#0x00 to 0x0D.
border.left_colour = 0x40
border.top_colour = 0x40
border.right_colour = 0x40
border.bottom_colour = 0x40

style. borders = borders

Alignment settings (alignment)

alignment = xlwt.Alignment()
#xlwt.Alignment.VERT_TOP= 0x00 font at top of cell
#xlwt.Alignment.VERT_CENTER= 0x01 font vertically centered
#xlwt.Alignment.VERT_BOTTOM= 0x02 font at the bottom of the cell
#xlwt.Alignment.VERT_JUSTIFIED= 0x03 Display in a reasonable position
#xlwt.Alignment.VERT_DISTRIBUTED= 0x04 has a vertical centering effect. If the font exceeds the cell range, it will be displayed vertically.
alignment.vert = 0x01 #font vertical position

#xlwt.Alignment.HORZ_GENERAL = 0x00 normal display
#xlwt.Alignment.HORZ_LEFT=0x01 left alignment
#xlwt.Alignment.HORZ_CENTER= 0x02 center
#xlwt.Alignment.HORZ_RIGHT=0x03 right alignment
#xlwt.Alignment.HORZ_FILLED=0x04 fill cell
#xlwt.Alignment.HORZ_JUSTIFIED=0x05 Display in a reasonable position
#xlwt.Alignment.HORZ_CENTER_ACROSS_SEL=0x06 I think this should be displayed in the middle of the cell
#xlwt.Alignment.HORZ_DISTRIBUTED=0x07 When it comes to Chinese characters, the font is horizontal and evenly distributed in the table. If it is English letters, it is displayed horizontally and centered.
alignment.horz = 0x03 #font horizontal position

#xlwt.Alignment.DIRECTION_GENERAL= 0x00 # BIFF8X normal display
#xlwt.Alignment.DIRECTION_LR= 0x01
#xlwt.Alignment.DIRECTION_RL= 0x02
alignment.dire = 0x00 #font direction

#xlwt.Alignment.ORIENTATION_NOT_ROTATED= 0x00
#xlwt.Alignment.ORIENTATION_STACKED= 0x01
#xlwt.Alignment.ORIENTATION_90_CC= 0x02
#xlwt.Alignment.ORIENTATION_90_CW = 0x03
alignment.orie= 0x00

#xlwt.Alignment.WRAP_AT_RIGHT = 0x01 # Automatic line wrapping
#xlwt.Alignment.NOT_WRAP_AT_RIGHT= 0x00 # No automatic line wrapping by default
alignment.wrap = 0x01 # automatic line wrap

#xlwt.Alignment.SHRINK_TO_FIT = 0x01 # Shrink the font to fit the cell size
#xlwt.Alignment.NOT_SHRINK_TO_FIT = 0x00 # Default is normal display
alignment.shri = 0x01 #Automatic indentation

#xlwt.Alignment.WRAP_AT_RIGHT = 0x01 # Automatic line wrapping
#xlwt.Alignment.NOT_WRAP_AT_RIGHT= 0x00 # No automatic line wrapping by default
alignment.wrap = 0x01 #auto wrap

alignment.rota = 2 #Rotation direction, the value is a number, indicating the angle of rotation
alignment.inde = 0
alignment.merg = 0

style. alignment = alignment

Set the cell width

worksheet.col(col_index).width = 256 * len(col_str) * 2 # Set the column width and calculate the column width: 256 is the cell base * use the data length * 2 to get the final result of the cell The width and data just fill up.

Set line style

tall_style = xlwt.easyxf('font:height 600') # set line height
worksheet.row(row_index).set_style(tall_style) # Set the tall_style style for the first row, which is the row height

worksheet.row(row_index).height_mismatch = True
worksheet.row(row_index).height =20 * 30 #20 is the base *30 is the height of the row

Four. Others

Enter a date into a cell

import datetime

#'general' # The default is this option, the general display.
#'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'
#'@'
style.num_format_str = 'M/D/YY'

worksheet.write(0, 0, datetime.date.today(), style)

#Time type conversion, convert the time in excel to python time (two ways)
xlrd.xldate_as_tuple(table.cell(2,2).value, 0) #Convert to tuple form
xlrd.xldate.xldate_as_datetime(table.cell(2,2).value, 1) #directly convert to datetime object
xlrd.xldate_as_tuple(d,0) #d is a floating point number read from excel

Add a formula

worksheet.write(0,2,xlwt.Formula('A1 * B1'),style)# Find the product
worksheet.write(0,2,xlwt.Formula('SUM(A1:A3)'),style)# summation function
worksheet.write(0,2, xlwt.Formula('AVERAGE(C1:C3)'),style)# find the average

Add a hyperlink to the cell

worksheet.write(0, 0, xlwt.Formula('HYPERLINK("https://www.baidu.com";"Baidu")'),style ))

Merge columns and rows

worksheet.write_merge(8, 10, 2, 4, 'merged cells', style)

Insert picture
```
worksheet.insert_bitmap("mm.bmp",0,0)
```

Data protection, after setting cell data protection, the data can only be viewed but not modified and edited

protection = xlwt.Protection()
protection.cell_locked = 1 # set cell locked
protection.formula_hidden = 1 # Set the formula in the hidden cell
worksheet.set_protect(1) # Only valid when the st table is set to protection

Colormap

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