I. Introduction to basic categories
1, Workbook class Workbook Introduction:
import xlwt
class Workbook(object0):
'''
Workbook class, when creating an excel file using xlwt, first instantiate an object of this class
'''
def __init__(self, encoding='ascii', style_compression=0):
pass
def add_sheet(self,sheetname, cell_overwrite_ok=False):
'''
Creating a sheet in a workbook
:param sheetname: name of the sheet
:param cell_overwrite_ok: If True, you can write to a cell multiple times (overwrite), which is equivalent to modifying the cell's data; if False, multiple writes will raise an exception.
:return: return a worksheet object (also known as Worksheet() object)
'''
pass
def save(self,filename_or_stream):
'''
Save the file in Excel format
:param filename_or_stream:File name for Excel
:return.
'''
pass
2, WorkSheet class WorkSheet Introduction
class Worksheet(object):
'''
Worksheet Classes
'''
def __init__(self, sheetname, parent_book, cell_overwrite_ok=False):
'''
Parameters of the instantiated object
:param sheetname: sheet name
:param parent_book: workbook object
:param cell_overwrite_ok: overwrite cell (if True you can overwrite the cell, if False you will throw an exception if you overwrite)
'''
pass
def write(self, r, c, label="", style=.default_style):
'''
Writes data to a cell in the worksheet
:param r: Abbreviation for row, counting from 0.
:param c: abbreviation for column, counts from 0.
:param label: the data to be written (supported data types: int, long, float, string, date, TRUE, FALSE, etc.)
:param style:style of the table (style classes: Font, Alignment, Borders, Pattern, Protection. style functions: easyxf, easyfont, add_palette_colour)
:return.
'''
pass
3, create a basic Excel table without styles
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Add data to the worksheet (parameters correspond to rows, columns, values)
for i in range(3):
for j in range(3):
(i,j, label = 'test_' + str(j),)
# Save data to hard disk
(r'i:\Excel_test.xls')
Eventually we will see the following image:
II. xlwt advanced applications
Font: Used to set the font, font size, font color, whether to bold, italic, font with underline, strikethrough and so on.
Alignment: Used to set the horizontal and vertical alignment of fonts, text line breaks, indentation, direction/rotation, text orientation, and so on.
Border: Form border settings.
Background: the background color of the cell.
1, Font class Font Introduction
class Font(object):
def __init__(self):
# twip = 1/20 of a point = 1/1440 of a inch
# usually resolution == 96 pixels per 1 inch
# (rarely 120 pixels per 1 inch or another one)
= 0x00C8 # 200: this is font with height 10 points Font Height
= False # Italics
self.struck_out = False # Delete line
= False # Font outlines (didn't notice any changes)
= False # Font Shadow
self.colour_index = 0x7FFF # This font color seems to have a limit, did not quite understand, the range is 0x00 to 0x35 between the color, more than this range are black, the maximum can not be more than 0xFFFF or will report an error.
= False # Bold
self._weight = 0x0190 # 0x02BC gives bold font # font width
= self.ESCAPEMENT_NONE #Font position in cell, 0x00 normal, 0x01 font superscript, 0x02 font subscript.
= self.UNDERLINE_NONE # Default font without underline, 0x01 single underline, 0x02 about the same as 0x01, 0x21 double underline, 0x02 about the same as 0x21
= self.FAMILY_NONE # Not sure what to do, none by default, 0x01 Roman family, 0x02 Swiss family, 0x03 Modern family, 0x04 Script family, 0x05 Description family
= self.CHARSET_SYS_DEFAULT # Character set, character set options
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
= 'Arial' # calligraphic style
1.1 Create an Excel that changes the default font style
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 0x01 # Setting fonts with underlining
= 'Huawen Caiyun' # Set the font
# Assign the font object to the style object
= xfont
for i in range(3):
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
(i,j, label = 'test_' + str(j),style=xstyle)
# Save data to hard disk
(r'i:\')
If all goes well, you will see the following when you open it:
2, Alignment class introduction
class Alignment(object):
# Font Level Setting
HORZ_GENERAL = 0x00 # Normal display
HORZ_LEFT = 0x01 # Left-aligned
HORZ_CENTER = 0x02 # Centered
HORZ_RIGHT = 0x03 # Right-aligned
HORZ_FILLED = 0x04 # Fill cells
HORZ_JUSTIFIED = 0x05 # Displayed in a logical place
HORZ_CENTER_ACROSS_SEL = 0x06 # Centred across selection I think this should be displayed in the right center of the cell, but the test found that the same effect as 0x02 can only be horizontally centered can not be vertically centered!
HORZ_DISTRIBUTED = 0x07 # Distributed fonts are distributed horizontally in the table for Chinese characters and centered horizontally for English letters.
# Vertical settings
VERT_TOP = 0x00 # Fonts at the top of the cell
VERT_CENTER = 0x01 # Center fonts vertically
VERT_BOTTOM = 0x02 # Fonts at the bottom of the cell
VERT_JUSTIFIED = 0x03 # Justified (BIFF5-BIFF8X) displayed in a logical place
VERT_DISTRIBUTED = 0x04 # Distributed (BIFF8X) has the effect of vertical centering, if the font exceeds the range of cells will be displayed vertically distributed
# font orientation (didn't get it haha)
DIRECTION_GENERAL = 0x00 # BIFF8X Normal display
DIRECTION_LR = 0x01
DIRECTION_RL = 0x02
ORIENTATION_NOT_ROTATED = 0x00
ORIENTATION_STACKED = 0x01
ORIENTATION_90_CC = 0x02
ORIENTATION_90_CW = 0x03
ROTATION_0_ANGLE = 0x00
ROTATION_STACKED = 0xFF # That's the only one that's vertical
WRAP_AT_RIGHT = 0x01 # Automatic line feeds
NOT_WRAP_AT_RIGHT = 0x00 # No automatic line breaks by default
SHRINK_TO_FIT = 0x01 # Reduce fonts to fit cell size
NOT_SHRINK_TO_FIT = 0x00 # Default normal display
def __init__(self):
= self.HORZ_GENERAL # Horizontal settings, default is normal display
= self.VERT_BOTTOM # Vertical settings, default font at bottom of cell
= self.DIRECTION_GENERAL
= self.ORIENTATION_NOT_ROTATED
= self.ROTATION_0_ANGLE
= self.NOT_WRAP_AT_RIGHT # If the data exceeds the range of the cell display whether to automatically line feed the display, the default is not line feed
= self.NOT_SHRINK_TO_FIT # If the data exceeds the cell range will automatically reduce the font to fit the current cell display range, the default does not reduce the font.
= 0
= 0
2.1 The following is a small example:
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Assign the font object to the style object
= xfont
= xalig
for i in range(3):
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
(i,j, label = 'test_'+str(j),style=xstyle)
# Save data to hard disk
(r'i:\')
If all goes well you will see the following:
3, Border class introduction
class Borders(object):
NO_LINE = 0x00 # Borderless
THIN = 0x01 # Thin border
MEDIUM = 0x02 # Medium fine border
DASHED = 0x03 # Dashed border
DOTTED = 0x04 # Dotted and dashed lines are pretty much the same
THICK = 0x05 # Bold border
DOUBLE = 0x06 # Bold border
HAIR = 0x07 # It's almost like a dotted line
#The following for BIFF8
MEDIUM_DASHED = 0x08 # Medium dotted line
THIN_DASH_DOTTED = 0x09 # Fine dashed lines
MEDIUM_DASH_DOTTED = 0x0A # Medium dotted dashes
THIN_DASH_DOT_DOTTED = 0x0B # Dotted line
MEDIUM_DASH_DOT_DOTTED = 0x0C # Medium dotted line
SLANTED_MEDIUM_DASH_DOTTED = 0x0D # Slash dashed dots consisting of
NEED_DIAG1 = 0x01
NEED_DIAG2 = 0x01
NO_NEED_DIAG1 = 0x00
NO_NEED_DIAG2 = 0x00
def __init__(self):
# Setting the border
= self.NO_LINE
= self.NO_LINE
= self.NO_LINE
= self.NO_LINE
= self.NO_LINE # I don't know what it's for
# Set the border color
self.left_colour = 0x40
self.right_colour = 0x40
self.top_colour = 0x40
self.bottom_colour = 0x40
self.diag_colour = 0x40
# I don't know what it's for
self.need_diag1 = self.NO_NEED_DIAG1
self.need_diag2 = self.NO_NEED_DIAG2
3.1 Here is a small example with a border
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Setting the border
xborder = ()
# Borders are slanted, dotted dashes
= 0x0d
= 0x0d
= 0x0d
= 0x0d
# Border color
xborder.bottom_colour = 0x35
xborder.top_colour = 0x8
xborder.left_colour = 0x012
xborder.right_colour = 0x4
# Set the style
= xfont
= xalig
= xborder
for i in range(3):
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
(i,j, label = 'test_'+str(j),style=xstyle)
# Save data to hard disk
(r'i:\')
Eventually you will see the following:
4, background class Background Introduction
class Pattern(object):
# patterns 0x00 - 0x12
NO_PATTERN = 0x00
SOLID_PATTERN = 0x01
def __init__(self):
= self.NO_PATTERN # Whether to set the background or not, must be allowed to set the background for pattern_fore_color and pattern_back_color to take effect
self.pattern_fore_colour = 0x40 # Foreground color does not affect cell borders
self.pattern_back_colour = 0x41 # Rear view affects cell borders
4.1 A small example follows:
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Setting the border
xborder = ()
# Borders are slanted, dotted dashes
= 0x0d
= 0x0d
= 0x0d
= 0x0d
# Border color
xborder.bottom_colour = 0x35
xborder.top_colour = 0x8
xborder.left_colour = 0x012
xborder.right_colour = 0x4
# Set cell backgrounds
xpattern = ()
= 0x01
xpattern.pattern_fore_colour = 0x05
# Set the style
= xfont
= xalig
= xborder
= xpattern
for i in range(3):
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
(i,j, label = 'test_'+str(j),style=xstyle)
# Save data to hard disk
(r'i:\')
Eventually you will see the image below:
The above is just a simple introduction to the most basic methods of xlwt, there are many methods of functionality has not been listed, here will not be listed. Interested partners can look at their own source code to achieve the function they want.
III. Introduction to some commonly used methods
1, set the cell row height, column width.
I haven't found a good way to set this up so far, so I have to set it up row by row and column by column. The code is as follows:
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Setting the border
xborder = ()
# Borders are slanted, dotted dashes
= 0x0d
= 0x0d
= 0x0d
= 0x0d
# Border color
xborder.bottom_colour = 0x35
xborder.top_colour = 0x8
xborder.left_colour = 0x012
xborder.right_colour = 0x4
# Set cell backgrounds
xpattern = ()
= 0x01
xpattern.pattern_fore_colour = 0x05
# Set the style
= xfont
= xalig
= xborder
= xpattern
for i in range(3):
# Set the line height
(i).height_mismatch = True
(i).height = 20 * 30 # 20 is the base * 30 is the height of the rows
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
data = 'test_'+str(j)
(j).width = 256 * len(data) * 2 # Set the column width, calculate the column width: 256 is the cell base * with the length of the data * 2 the final result of the cell width and data to fill exactly.
(i,j, label = data,style=xstyle)
# Save data to hard disk
(r'i:\')
You will end up with the following effect
2, in the cell to display the date, the code is as follows:
import xlwt
import datetime
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Setting the border
xborder = ()
# Borders are slanted, dotted dashes
= 0x0d
= 0x0d
= 0x0d
= 0x0d
# Border color
xborder.bottom_colour = 0x35
xborder.top_colour = 0x8
xborder.left_colour = 0x012
xborder.right_colour = 0x4
# Set cell backgrounds
xpattern = ()
= 0x01
xpattern.pattern_fore_colour = 0x05
# Set the style
= xfont
= xalig
= xborder
= xpattern
xstyle.num_format_str = 'M/D/YY' # Date display format, there are many other options for other data types, see below.
for i in range(3):
# Set the line height
(i).height_mismatch = True
(i).height = 20 * 30 # 20 is the base * 30 is the height of the rows
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
data = () # Get the current date
(j).width = 256 * len(('%Y-%m-%d')) * 2 # Calculate the width of the column: 256 is the cell base * with the length of the data * 2 the final result cell width and data exactly filled.
(i,j, label = data,style=xstyle)
# Save data to hard disk
(r'i:\')
If all goes well the effect is as follows:
For financial or other date and time, decimal, scientific notation, and other display formats, refer to the following options:
_std_num_fmt_list = [
'general', # The default is this option, which is routinely displayed.
'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', # This is what we just used
'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',
'@'
]
xstyle.num_format_str = 'M/D/YY' # We simply replace this string with the one we need above。
3, in the Excel form to insert the formula, the code is as follows:
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Setting the border
xborder = ()
# Borders are slanted, dotted dashes
= 0x0d
= 0x0d
= 0x0d
= 0x0d
# Border color
xborder.bottom_colour = 0x35
xborder.top_colour = 0x8
xborder.left_colour = 0x012
xborder.right_colour = 0x4
# Set cell backgrounds
xpattern = ()
= 0x01
xpattern.pattern_fore_colour = 0x05
# Set the style
= xfont
= xalig
= xborder
= xpattern
for i in range(3):
# Set the line height
(i).height_mismatch = True
(i).height = 20 * 30 # 20 is the base * 30 is the height of the rows
for j in range(3):
# Add data to the worksheet (parameters correspond to rows, columns, values, styles)
(i,j, label = int(j)+1,style=xstyle)
# Set the line height
(3).height_mismatch = True
(3).height = 20 * 30 # 20 is the base * 30 is the height of the rows
(3,0, label = ('SUM(A1:A3)'),style=xstyle) # Summation function
(3,1, label = ('B1*B2*B3'),style=xstyle) # Find the product
(3,2, label = ('AVERAGE(C1:C3)'),style=xstyle) # Finding the average
# Save data to hard disk
(r'i:\')
Eventually we will see the following effect:
4, insert a hyperlink in the form, the code is as follows:
import xlwt
# Instantiate a workbook object
workbook = (encoding = 'utf-8')
# Get the Worksheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')
# Instantiate the form style object
xstyle = ()
# Set the font style
xfont = ()
xfont.colour_index = 0x04 # Set the font color
= True # Font Bolding
= 20 * 18 # Set font height (20 is base constant, 18 is font size for resizing)
= 'Song Style' # Set the font
# Set the position of the font in the cell
xalig = ()
= 0x02 # Center fonts horizontally
= 0x01 # Center fonts horizontally
# Setting the border
xborder = ()
# Borders are slanted, dotted dashes
= 0x0d
= 0x0d
= 0x0d
= 0x0d
# Border color
xborder.bottom_colour = 0x35
xborder.top_colour = 0x8
xborder.left_colour = 0x012
xborder.right_colour = 0x4
# Set cell backgrounds
xpattern = ()
= 0x01
xpattern.pattern_fore_colour = 0x05
# Set the style
= xfont
= xalig
= xborder
= xpattern
# Set the line height
(0).height_mismatch = True
(0).height = 20 * 30 # 20 is the base * 30 is the height of the rows
a_data = 'HYPERLINK("";"baidu")' # The URL to insert, 'baidu' is the value to display in Excel.
(0).width = 256 * len(a_data) * 2 # Set cell width
(0,0, label = (a_data),style=xstyle) # Insert hyperlinks
# Save data to hard disk
(r'i:\')
If all goes well you will see the following:
5. About the color mapping table is as follows:
_colour_map_text = """\ 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"""
Well!!! Let's just write briefly here for now. I'll continue to add any fun features I find in the future.
Above is the detailed content of the analysis of the use of python xlwt module, more information about the use of python xlwt module please pay attention to my other related articles!