Introduction to Python xlwt module

Keywords: Python Excel encoding ascii

I. Introduction to basic

1. Introduction to Workbook class:

import xlwt
class  Workbook(object0):
    //Workbook class. When using xlwt to create excel files, you first need to instantiate the objects of this class
     def __init__(self, encoding='ascii', style_compression=0):

    def add_sheet(self,sheetname, cell_overwrite_ok=False):
        //Create sheet in Workbook
        :param sheetname: Name of worksheet
        :param cell_overwrite_ok: If the value is True,You can write data (overwrite) multiple times in the cell, which is equivalent to modifying the cell data. If the value is False Multiple writes will throw an exception.
        :return: Returns a worksheet object (that is Worksheet()Object)
    def save(self,filename_or_stream):
        //Save the file in Excel format
        :param filename_or_stream:Excel File name

2. Introduction to WorkSheet

class Worksheet(object):
    Worksheet class
    def __init__(self, sheetname, parent_book, cell_overwrite_ok=False):
        Parameters of instanced objects
        : param sheetname: sheet name
        : param parent? Book: Workbook object
        : param cell "overwrite" OK: overwrite write cell (True can overwrite write cell, False can throw exception if overwrite write)

    def write(self, r, c, label="", style=xlwt.Style.default_style):
        Write data to cells on the worksheet
        : param R: short for row, which represents a row, counting from 0.
        : param C: short for column, which means column, counting from 0.
        : param label: data to write (supported data types: int, long, float, string, date, TRUE, FALSE, xlwt.Formula, etc.)
        : param style: table style (style class: Font, Alignment, Borders, Pattern, Protection). Style functions: easyxf, easyfont, add ﹐ palette ﹐ colour)

3. Create a basic Excel table without style

import xlwt
# Instantiate a workbook object
workbook = xlwt.Workbook(encoding = 'utf-8')

# Get sheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')

# Add data (parameter row) to the worksheet, column, Value)
for i in range(3):
    for j in range(3):
        worksheet.write(i,j, label = 'test_' + str(j),)

# Save data to hard disk'i:\Excel_test.xls')

Finally, we will see the following figure:



II. xlwt advanced application

1. Introduction to Font

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)

        self.height = 0x00C8 # 200: this is font with height 10 points Font height
        self.italic = False      # Italics
        self.struck_out = False  # Delete line
        self.outline = False     # Font outline (no change found)
        self.shadow = False      # Font shadow
        self.colour_index = 0x7FFF  # There seems to be a limit to the color of this font. I don't understand it very well. The range is 0 x00 To 0 x35 There are colors in between. If the color exceeds this range, it is black. The maximum value cannot exceed 0 xFFFF Otherwise, an error will be reported.
        self.bold = False  # bold
        self._weight = 0x0190 # 0x02BC gives bold font  # Font width
        self.escapement = self.ESCAPEMENT_NONE  #Font position in cell, 0 x00 Normal, 0 x01 Font superscript, 0 x02 Font subscript.
        self.underline = self.UNDERLINE_NONE  # Default font is not underlined, 0 x01 Single underline, 0 x02 And 0 x01 Almost, 0 x21 Double underline, 0 x02 And 0 x21 Almost = self.FAMILY_NONE        # Don't know what to do, default no, 0 x01 Roman family, 0 x02 Swiss family, 0 x03 Modern family, 0 x04 Script family, 0 x05 Description family
        self.charset = self.CHARSET_SYS_DEFAULT  # 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'  # Typeface

1.1 create an Excel that changes the default font style

import xlwt
# Instantiate a workbook object
workbook = xlwt.Workbook(encoding = 'utf-8')

# Get sheet object Worksheet
worksheet = workbook.add_sheet('work_sheet')

# Instancing table style objects
xstyle = xlwt.XFStyle()

# Set font style
xfont = xlwt.Font()
xfont.colour_index = 0x04  # Set font color
xfont.bold = True          # Bold font
xfont.height = 20 * 18     # Set font height (20 is base unchanged, 18 is font size for resizing)
xfont.underline = 0x01     # Set font underline = 'Hua Wencaiyun'     # Set font

# Assign a font object to a style object
xstyle.font = xfont

for i in range(3):
    for j in range(3):
        # Add data (parameter row) to the worksheet, column, Value, style)
        worksheet.write(i,j, label = 'test_' + str(j),style=xstyle)

# Save data to hard disk'i:\2.xls')

If everything goes well, open 2.xls and you will see the following:


To be continued------------------------------------------------------------------------------

Posted by Oubipaws on Tue, 05 Nov 2019 08:14:13 -0800