quick get start

If not installed, you can

pip install xlwt

First look at a simple code written in excel

>>> import xlwt
>>> excel = xlwt.Workbook()
>>> sh1 = excel.add_sheet("one")#Create a sheet
>>> sh2 = excel.add_sheet("two")
>>> sh1.write(0,0,'a')          #Write a to row 0 and column 0 in sh1
>>> sh1.write(0,1,'b')
>>> for i in range(10):
...   sh2.write(0,i,i)          #Write i to 0 row i column in sh1
...
>>> excel.save("test.xls")      #Save excel as test.xls

If the demand is very simple, you can directly click here to collect, and you can take your time later.

Workbook object

The Workbook class encapsulates many functions and variables. In addition to the constructor, there are only two most important ones, add_sheet and save.

Where, add_ In addition to the sheet name, the input parameters of sheet include cell_overwrite_ok, if True, no error will be reported when a new sheet is added to overwrite the existing sheet.

Its constructor Workbook has two parameters, encoding and style_compression: the former defaults to utf8, while the latter defaults to 0, which is related to setting Style.

Worksheet object

adopt

wb = xlwt.Workbook()
sh = wb.add_sheet("sheet1",cell_overwrite_ok=True)

To create a sheet object. Sheet is also the most important class in xlwt. All operations on tables are performed in sheet. The most commonly used one is write, that is, write to cells.

In the source code, write is defined as

def write(self, r, c, label="", style=Style.default_style):

Where r and c represent row and column numbers respectively, label is the content of the cell, and style is the writing format of the cell.

According to different label contents, the written data format is also different

• When the input is a number, whether integer or decimal, it will be converted to floating point in Excel
• When the input is a character, it will be converted to unicode
• When you enter the datetime or time class in datetime, it will be converted to the date format in Excel
• Enter a bool instance, and it turns to TRUE or FALSE
• Enter the xlwt.Formula class to convert it to a formula in Excel.

for example

sh.write(0,0,int(12345))
sh.write(1,0,"abcde")
sh.write(2,0,datetime.date(2021,10,28))
sh.write(3,0,xlwt.Formula("A1&A2"))
wb.save("test.xls")

Get excel as shown in the figure

Since the number starts from 0 in Python and 1 in Excel, the (0,0) we set is written into A1.

It is obvious that cell A4 realizes the functions of A1 & A2; A5 is also displayed as FALSE. The only surprise is A3. According to our expectation, the time should be displayed, but the time stamp is displayed.

At this time, the style parameter is used, and its input can be

• An instance of the Style.XFStyle class, such as its default parameter xlwt.Style.default_style
• xlwt.Style.easyxf function, whose return type is XFStyle.

The easyxf function is defined as

def easyxf(strg_to_parse="", num_format_str=None, **kwargs):

Where STRG_ to_ The input of parse is a string. The data format in the string is similar to that in the dictionary and corresponds to XF_ Value in dict, xf_dict is located in Style.py Line 515. for example

easyxf('font: bold on; align: wrap on, vert centre, horiz center')

num_format_str is a string used to represent the number format, which is the same as the type (T) of the number tab in formatting cells in Excel.

Therefore, we can re assign sh:

sh.write(1,0,"abcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcde",
style=xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center'))
sh.write(2,0,datetime.date(2021,10,28),style=xlwt.easyxf(num_format_str="yyyy-mm-dd"))
wb.save("test.xls")

You can see obvious format changes

In addition, write is encapsulated in the Worksheet_ Merge function, used to merge cells, defined as

def write_merge(self, r1, r2, c1, c2, label="", style=Style.default_style):

Indicates that rows r1 to r2 and columns c1 to c2 are merged, and the cell content is written as label.