Python實踐:操作Excel文件之xlrd、xlwt

Python實踐：操作Excel文件之xlrd、xlwt一、xlrd模塊讀xls格式文件

下載安裝：https://pypi.org/project/xlrd/
文檔說明：https://xlrd.readthedocs.io/en/latest/
Github地址：https://github.com/python-excel/xlrd
PDF文檔：https://github.com/python-excel/tutorial/raw/master/python-excel.pdf


Working with Excel Files in Python：http://www.python-excel.org/

xlrd作用

xlrd is a library for reading data and formatting information from Excel files in the historical .xls format.

xlrd安裝

安裝命令：pip install xlrd

1pip install xlrd
2
3Collecting xlrd
4  Downloading xlrd-2.0.1-py2.py3-none-any.whl (96 kB)
5     |████████████████████████████████| 96 kB 639 kB/s
6Installing collected packages: xlrd
7Successfully installed xlrd-2.0.1

xlrd模塊常用類及方法

workbook對象對應一個excel文件

1• open_workbook(filename=None,
2                  logfile=sys.stdout,
3                  verbosity=0,
4                  use_mmap=True,
5                  file_contents=None,
6                  encoding_override=None,
7                  formatting_info=False,
8                  on_demand=False,
9                  ragged_rows=False,
10                  ignore_workbook_corruption=False
11                  )

通過上述方法獲取一個 xlrd.book.Book 類對象

cellname(rowx, colx)：返回指定單元格的坐標名字 ，例如：(5, 7)=> H6）

xlrd.book.Book 類有如下常用方法：

sheet_by_index(self, sheetx)：根據索引獲取xlrd.sheet.Sheet對象

sheet_by_name(self, sheet_name)：根據名字獲取xlrd.sheet.Sheet對象

sheet_names(self)：獲取所有sheet名字組成的列表

sheets(self)：獲取所有sheet對象組成的列表

xlrd.sheet.Sheet類有如下常用方法：

row_values(self, rowx, start_colx=0, end_colx=None)：返回指定行的所有單元格的值所組成的列表

col_values(self, colx, start_rowx=0, end_rowx=None)：返回指定列的所有單元格的值所組成的列表

cell_value(self, rowx, colx)：返回指定單元格的值

nrows：返回總行數

ncols：返回總列數

cell(self, rowx, colx)：返回xlrd.sheet.Cell對象

row_slice(self, rowx, start_colx=0, end_colx=None)：返回指定範圍的數據列表

row_values(self, rowx, start_colx=0, end_colx=None)：返回指定範圍的數據列表

row_types(self, rowx, start_colx=0, end_colx=None)：返回指定範圍的數據類型

操作workbook對象

示例：D盤根目錄新建一個"測試文件.xls"文件，內容如下

1import xlrd
2
3xlsfile = r'D:\測試文件.xls'
4# 獲取Excel文件的workbook對象
5workbook = xlrd.open_workbook(xlsfile)
6
7# 獲取sheet總個數
8print("sheet個數："+str(workbook.nsheets))
9
10# 根據索引遍歷sheet
11for sheet_index in range(workbook.nsheets):
12    print("根據索引獲取sheet："+str(workbook.sheet_by_index(sheet_index)))
13
14# 獲取所有sheet頁的名字組成的列表
15print("sheet對象的名字："+str(workbook.sheet_names()))
16
17# 根據sheet名字遍歷sheet
18for sheet_name in workbook.sheet_names():
19    print("根據sheet名字獲取sheet對象："+str(workbook.sheet_by_name(sheet_name)))
20
21# 遍歷所有sheet對象
22for sheet in workbook.sheets():
23    print("遍歷所有sheet對象："+str(sheet))
24    print("總行數："+str(sheet.nrows))
25    print("總列數："+str(sheet.ncols))

輸出結果：

1sheet個數：3
2根據索引獲取sheet：Sheet  0:<Sheet1>
3根據索引獲取sheet：Sheet  1:<Sheet2>
4根據索引獲取sheet：Sheet  2:<Sheet3>
5sheet對象的名字：['Sheet1', 'Sheet2', 'Sheet3']
6根據sheet名字獲取sheet對象：Sheet  0:<Sheet1>
7根據sheet名字獲取sheet對象：Sheet  1:<Sheet2>
8根據sheet名字獲取sheet對象：Sheet  2:<Sheet3>
9遍歷所有sheet對象：Sheet  0:<Sheet1>
10總行數：1
11總列數：2
12遍歷所有sheet對象：Sheet  1:<Sheet2>
13總行數：0
14總列數：0
15遍歷所有sheet對象：Sheet  2:<Sheet3>
16總行數：0
17總列數：0

獲取某一行或某一列的數據列表

xlrd.sheet.Sheet類有如下常用方法：
• row_values(self, rowx, start_colx=0, end_colx=None)：返回指定行的所有單元格的值所組成的列表
• col_values(self, colx, start_rowx=0, end_rowx=None)：返回指定列的所有單元格的值所組成的列表

1import xlrd
2
3xlsfile = r'D:\測試文件.xls'
4# 獲取Excel文件的workbook對象
5workbook = xlrd.open_workbook(xlsfile)
6
7# 獲取第一個sheet對象
8sheet1=workbook.sheets()[0]
9print(sheet1)
10
11# 獲得第1行的數據列表
12row_data=sheet1.row_values(0)
13print(row_data)
14
15# 獲得第1列的數據列表
16col_data=sheet1.col_values(0)
17print(col_data)
18
19# 遍歷第一列所有數據
20for col in col_data:
21    print(col)

輸出結果：

1Sheet  0:<Sheet1>
2['姓名1', '成績1', '語文1', '數學1']
3['姓名1', '姓名2', '姓名3', '姓名4', '姓名5', '姓名6', '姓名7', '姓名8', '姓名9', '姓名10', '姓名11', '姓名12', '姓名13', '姓名14', '姓名15', '姓名16', '姓名17', '姓名18', '姓名19', '姓名20', '姓名21']
4姓名1
5姓名2
6姓名3
7姓名4
8姓名5
9姓名6
10姓名7
11姓名8
12姓名9
13姓名10
14姓名11
15姓名12
16姓名13
17姓名14
18姓名15
19姓名16
20姓名17
21姓名18
22姓名19
23姓名20
24姓名21

根據cell的位置坐標獲取指定cell的值

xlrd.sheet.Sheet類有如下常用方法：
• cell_value(self, rowx, colx)：返回指定單元格的值

1import xlrd
2
3xlsfile = r'D:\測試文件.xls'
4# 獲取Excel文件的workbook對象
5workbook = xlrd.open_workbook(xlsfile)
6
7# 獲取第一個sheet對象
8sheet1=workbook.sheets()[0]
9print(sheet1)
10
11# 獲取第一行、第二列單元格的值
12cell_value1=sheet1.cell_value(0,1)
13print(cell_value1)
14
15cell_value2=sheet1.cell(0,1)
16print(cell_value2)

輸出結果：

1Sheet  0:<Sheet1>
2成績1
3text:'成績1'

獲取cell單元格的名字及值

xlrd.cellname：獲取cell單元格的名字

xlrd.sheet.Sheet類的cell(self, rowx, colx)方法返回xlrd.sheet.Cell對象，再通過name屬性獲取值

1import xlrd
2
3xlsfile = r'D:\測試文件.xls'
4# 獲取Excel文件的workbook對象
5workbook = xlrd.open_workbook(xlsfile)
6
7# 獲取第一個sheet對象
8sheet1=workbook.sheets()[0]
9print(sheet1.name)
10
11for row_index in range(sheet1.nrows):
12    for col_index in range(sheet1.ncols):
13        print(xlrd.cellname(row_index,col_index),"-",sheet1.cell(row_index,col_index).value)

輸出結果：

1Sheet1
2A1 - 姓名1
3B1 - 成績1
4C1 - 語文1
5D1 - 數學1
6A2 - 姓名2
7B2 - 成績2
8C2 - 語文2
9D2 - 數學2
10A3 - 姓名3
11B3 - 成績3
12C3 - 語文3
13D3 - 數學3
14A4 - 姓名4
15B4 - 成績4
16C4 - 語文4
17D4 - 數學4
18A5 - 姓名5
19B5 - 成績5
20C5 - 語文5
21D5 - 數學5
22A6 - 姓名6
23B6 - 成績6
24C6 - 語文6
25D6 - 數學6
26A7 - 姓名7
27B7 - 成績7
28C7 - 語文7
29D7 - 數學7
30A8 - 姓名8
31B8 - 成績8
32C8 - 語文8
33D8 - 數學8
34A9 - 姓名9
35B9 - 成績9
36C9 - 語文9
37D9 - 數學9
38A10 - 姓名10
39B10 - 成績10
40C10 - 語文10
41D10 - 數學10
42A11 - 姓名11
43B11 - 成績11
44C11 - 語文11
45D11 - 數學11
46A12 - 姓名12
47B12 - 成績12
48C12 - 語文12
49D12 - 數學12
50A13 - 姓名13
51B13 - 成績13
52C13 - 語文13
53D13 - 數學13
54A14 - 姓名14
55B14 - 成績14
56C14 - 語文14
57D14 - 數學14
58A15 - 姓名15
59B15 - 成績15
60C15 - 語文15
61D15 - 數學15
62A16 - 姓名16
63B16 - 成績16
64C16 - 語文16
65D16 - 數學16
66A17 - 姓名17
67B17 - 成績17
68C17 - 語文17
69D17 - 數學17
70A18 - 姓名18
71B18 - 成績18
72C18 - 語文18
73D18 - 數學18
74A19 - 姓名19
75B19 - 成績19
76C19 - 語文19
77D19 - 數學19
78A20 - 姓名20
79B20 - 成績20
80C20 - 語文20
81D20 - 數學20
82A21 - 姓名21
83B21 - 成績21
84C21 - 語文21
85D21 - 數學21

返回指定範圍的數據

xlrd.sheet.Sheet類使用如下方法：

row_slice(self, rowx, start_colx=0, end_colx=None)：返回指定範圍的數據列表

row_values(self, rowx, start_colx=0, end_colx=None)：返回指定範圍的數據列表

row_types(self, rowx, start_colx=0, end_colx=None)：返回指定範圍的數據類型

列同樣存在這些類似方法，不再贅述

1import xlrd
2
3xlsfile = r'D:\測試文件.xls'
4# 獲取Excel文件的workbook對象
5workbook = xlrd.open_workbook(xlsfile)
6
7# 獲取第一個sheet對象
8sheet1=workbook.sheets()[0]
9print(sheet1.row(0))  # [text:'姓名1', text:'成績1', text:'語文1', text:'數學1']
10print(sheet1.col(0))  # [text:'姓名1', text:'姓名2', text:'姓名3', text:'姓名4', text:'姓名5', text:'姓名6', text:'姓名7', text:'姓名8', text:'姓名9', text:'姓名10', text:'姓名11', text:'姓名12', text:'姓名13', text:'姓名14', text:'姓名15', text:'姓名16', text:'姓名17', text:'姓名18', text:'姓名19', text:'姓名20', text:'姓名21']
11
12print(sheet1.row_slice(0,1)) # [text:'成績1', text:'語文1', text:'數學1']
13# 第一行 且 從 第二列開始的數據列表
14print(sheet1.row_values(0,1)) # ['成績1', '語文1', '數學1']
15print(sheet1.row_values(0,1,3)) # ['成績1', '語文1', '數學1']
16print(sheet1.row_types(0,1))  # array('B', [1, 1, 1])
17print(sheet1.row_types(0,1,3)) # array('B', [1, 1])
18
19print(sheet1.col_slice(0,1)) # [text:'姓名2', text:'姓名3', text:'姓名4', text:'姓名5', text:'姓名6', text:'姓名7', text:'姓名8', text:'姓名9', text:'姓名10', text:'姓名11', text:'姓名12', text:'姓名13', text:'姓名14', text:'姓名15', text:'姓名16', text:'姓名17', text:'姓名18', text:'姓名19', text:'姓名20', text:'姓名21']

輸出結果：

1[text:'姓名1', text:'成績1', text:'語文1', text:'數學1']
2[text:'姓名1', text:'姓名2', text:'姓名3', text:'姓名4', text:'姓名5', text:'姓名6', text:'姓名7', text:'姓名8', text:'姓名9', text:'姓名10', text:'姓名11', text:'姓名12', text:'姓名13', text:'姓名14', text:'姓名15', text:'姓名16', text:'姓名17', text:'姓名18', text:'姓名19', text:'姓名20', text:'姓名21']
3[text:'成績1', text:'語文1', text:'數學1']
4['成績1', '語文1', '數學1']
5['成績1', '語文1']
6array('B', [1, 1, 1])
7array('B', [1, 1])
8[text:'姓名2', text:'姓名3', text:'姓名4', text:'姓名5', text:'姓名6', text:'姓名7', text:'姓名8', text:'姓名9', text:'姓名10', text:'姓名11', text:'姓名12', text:'姓名13', text:'姓名14', text:'姓名15', text:'姓名16', text:'姓名17', text:'姓名18', text:'姓名19', text:'姓名20', text:'姓名21']

操作Excel大文件

• The **on_demand **parameter can be passed as True to open_workbook resulting in  worksheets only being loaded into memory when they are requested.
• xlrd.Book objects have an **unload_sheet **method that will unload worksheet, specified by either sheet index or sheet name, from memory.

1import xlrd
2
3xlsfile = r'D:\測試文件.xls'
4# 獲取Excel文件的workbook對象
5workbook = xlrd.open_workbook(xlsfile,on_demand=True)
6for name in workbook.sheet_names():
7    print(name)
8    if name.endswith('2'):
9        sheet=workbook.sheet_by_name(name)
10        print(sheet.cell(0,0))
11        workbook.unload_sheet(name)
12
13輸出結果：
14Sheet1
15Sheet2
16text:'英語1'
17Sheet3

二、xlwt模塊寫xls格式文件xlwt安裝

1pip install xlwt
2
3Collecting xlwt
4  Downloading xlwt-1.3.0-py2.py3-none-any.whl (99 kB)
5     |████████████████████████████████| 99 kB 323 kB/s
6Installing collected packages: xlwt
7Successfully installed xlwt-1.3.0

xlwt模塊常用類及方法

xlwt.Workbook.Workbook類有如下常用方法：

xlwt.Workbook()：通過xlwt模塊的該方法返回xlwt.Workbook.Workbook對象

add_sheet(self, sheetname, cell_overwrite_ok=False)：添加一個sheet頁並返回一個xlwt.Worksheet.Worksheet對象

get_sheet(self, sheet)：根據索引獲取指定xlwt.Worksheet.Worksheet對象

xlwt.Worksheet.Worksheet類有如下常用方法：

write(self, r, c, label="", style=Style.default_style)：將數據寫入的指定行、列所對應的單元格

row(self, indx)：根據指定行返回xlwt.Row.Row對象

col(self, indx)：根據指定行返回xlwt.Column.Column對象

flush_row_data(self)：較少內存壓力，任何行在flushed之後都不能再修改或訪問（報異常：Attempt to reuse row index 0 of sheet 'sheetBB' after flushing）

xlwt.Row.Row類有如下常用方法：

新建sheet

1import xlwt
2
3workbook=xlwt.Workbook()
4print(type(workbook))
5sheet1=workbook.add_sheet("sheetAA")
6print(type(sheet1))
7workbook.add_sheet("sheetBB")
8
9workbook.save("測試數據22.xls")

輸出結果：

1<class 'xlwt.Workbook.Workbook'>
2<class 'xlwt.Worksheet.Worksheet'>
3

生成的"測試數據22.xls"文件：

通過sheet對象將數據寫入單元格

write(self, r, c, label="", style=Style.default_style)：將數據寫入的指定行、列所對應的單元格

1import xlwt
2
3workbook=xlwt.Workbook()
4print(type(workbook))
5sheet1=workbook.add_sheet("sheetAA")
6print(type(sheet1))
7workbook.add_sheet("sheetBB")
8
9sheet1.write(0,0,'A1')
10sheet1.write(0,1,'B1')
11
12workbook.save("測試數據22.xls")

輸出結果：

1<class 'xlwt.Workbook.Workbook'>
2<class 'xlwt.Worksheet.Worksheet'>
3

生成"測試數據22.xls"文件如下：

通過sheet對象操作單元格

1import xlwt
2
3workbook=xlwt.Workbook()
4print(type(workbook))
5sheet1=workbook.add_sheet("sheetAA")
6print(type(sheet1))
7workbook.add_sheet("sheetBB")
8
9row1=sheet1.row(1)
10row1.write(0,"A2")
11print(type(row1))
12
13workbook.save("測試數據22.xls")

輸出結果：

1<class 'xlwt.Workbook.Workbook'>
2<class 'xlwt.Worksheet.Worksheet'>
3<class 'xlwt.Row.Row'>
4

生成"測試數據22.xls"文件如下：

sheet對象的flush_row_data方法

If a large number of rows have been written to a Worksheet and memory usage is  becoming a problem, the **flush_row_data **method may be called on the Worksheet.  Once called, any rows flushed cannot be accessed or modified.

It is recommended that **flush_row_data **is called for every 1000 or so rows of a normal  size that are written to an xlwt.Workbook. If the rows are huge, that number should be  reduced.

1import tempfile
2import xlwt
3
4workbook=xlwt.Workbook()
5print(type(workbook))
6sheet1=workbook.add_sheet("sheetAA")
7print(type(sheet1))
8workbook.add_sheet("sheetBB")
9
10sheet2=workbook.get_sheet(1)
11print(type(sheet2))
12
13sheet2.row(0).write(0,'sheet 2 A1')
14sheet2.row(0).write(1,'sheet 2 B1')
15sheet2.flush_row_data()
16sheet2.row(0).write(2,'sheet 2 C1') # Exception: Attempt to reuse row index 0 of sheet 'sheetBB' after flushing
17# sheet2.write(3,0,'ddd')
18
19workbook.save("測試數據22.xls")
20workbook.save(tempfile.TemporaryFile())

輸出結果：

1<class 'xlwt.Workbook.Workbook'>
2<class 'xlwt.Worksheet.Worksheet'>
3<class 'xlwt.Worksheet.Worksheet'>
4Traceback (most recent call last):
5  File "C:/Users/zz/PycharmProjects/pythonProject/Hello.py", line 16, in <module>
6    sheet2.row(0).write(2,'sheet 2 C1') # Exception: Attempt to reuse row index 0 of sheet 'sheetBB' after flushing
7  File "D:\6.CommonTools\python379\lib\site-packages\xlwt\Worksheet.py", line 1141, in row
8    raise Exception("Attempt to reuse row index %d of sheet %r after flushing" % (indx, self.__name))
9Exception: Attempt to reuse row index 0 of sheet 'sheetBB' after flushing

flush_row_data()方法調用後，再次通過row對象操作單元格會報錯。如果使用sheet對象的write方法操作單元格則不會報錯，示例：

1import tempfile
2import xlwt
3
4workbook=xlwt.Workbook()
5print(type(workbook))
6sheet1=workbook.add_sheet("sheetAA")
7print(type(sheet1))
8workbook.add_sheet("sheetBB")
9
10sheet2=workbook.get_sheet(1)
11print(type(sheet2))
12
13sheet2.row(0).write(0,'sheet 2 A1')
14sheet2.row(0).write(1,'sheet 2 B1')
15sheet2.flush_row_data()
16# sheet2.row(0).write(2,'sheet 2 C1') # Exception: Attempt to reuse row index 0 of sheet 'sheetBB' after flushing
17sheet2.write(3,0,'ddd')
18
19workbook.save("測試數據22.xls")
20workbook.save(tempfile.TemporaryFile())

輸出結果：

1<class 'xlwt.Workbook.Workbook'>
2<class 'xlwt.Worksheet.Worksheet'>
3<class 'xlwt.Worksheet.Worksheet'>
4

此時生成文件內容如下：

單元格的值重複寫入

如果沒有明確指定cell_overwrite_ok參數，cell_overwrite_ok參數值則默認為False

1import xlwt
2
3book=xlwt.Workbook()
4sheet1=book.add_sheet("Sheet11")
5sheet1.write(0,0,"數據1")
6sheet=book.get_sheet(0)
7sheet.write(0,0,"數據2")
8
9book.save("測試數據44.xls")

輸出結果：

1Traceback (most recent call last):
2  File "C:/Users/zz/PycharmProjects/pythonProject/Hello.py", line 7, in <module>
3    sheet.write(0,0,"數據2")
4  File "D:\6.CommonTools\python379\lib\site-packages\xlwt\Worksheet.py", line 1088, in write
5    self.row(r).write(c, label, style)
6  File "D:\6.CommonTools\python379\lib\site-packages\xlwt\Row.py", line 235, in write
7    StrCell(self.__idx, col, style_index, self.__parent_wb.add_str(label))
8  File "D:\6.CommonTools\python379\lib\site-packages\xlwt\Row.py", line 154, in insert_cell
9    raise Exception(msg)
10Exception: Attempt to overwrite cell: sheetname='Sheet11' rowx=0 colx=0

從上面可以看到，當重複對同一個單元格寫入值時會報錯（異常信息：Exception: Attempt to overwrite cell）。如何解決呢？指定cell_overwrite_ok 參數為True即可，示例：

1import xlwt
2
3book=xlwt.Workbook()
4sheet1=book.add_sheet("Sheet11",cell_overwrite_ok=True)
5sheet1.write(0,0,"數據1")
6sheet=book.get_sheet(0)
7sheet.write(0,0,"數據2")
8
9book.save("測試數據44.xls")

生成的"測試數據44.xls"內容如下：

單元格寫值類型

xlwt.Row.Row類有如下常用方法：

write(self, r, c, label="", style=Style.default_style)：將內容寫入到指定單元格

set_cell_number(self, colx, number, style=Style.default_style)：寫入數值類型到指定單元格（浮點型、int、長整型、十進位類型）

set_cell_date(self, colx, datetime_obj, style=Style.default_style)：寫入日期類型到指定單元格 （如果沒有指定格式化，則顯示為數字）

set_cell_boolean(self, colx, value, style=Style.default_style)：寫入boolean類型到指定單元格

set_cell_error(self, colx, error_string_or_code, style=Style.default_style)：寫入錯誤值到指定單元格

set_cell_blank(self, colx, style=Style.default_style)：寫入空白字符到指定單元格，可以用於設置背景色

set_cell_mulblanks(self, first_colx, last_colx, style=Style.default_style)：如果需要對連續多個單元格寫入空白字符，使用此方法

1from datetime import date, time, datetime
2from decimal import Decimal
3from xlwt import Workbook, Style
4
5wb = Workbook()
6ws = wb.add_sheet('類型模板')
7ws.row(0).write(0, u'\xa3')
8ws.row(0).write(1, 'Text')
9ws.row(1).write(0, 3.1415)
10ws.row(1).write(1, 15)
11ws.row(1).write(2, 265)
12ws.row(1).write(3, Decimal('3.65'))
13ws.row(2).set_cell_number(0, 3.1415)
14ws.row(2).set_cell_number(1, 15)
15ws.row(2).set_cell_number(2, 265)
16ws.row(2).set_cell_number(3, Decimal('3.65'))
17ws.row(3).write(0, date(2021, 3, 18))
18ws.row(3).write(1, datetime(2021, 3, 18, 17, 0, 1))
19ws.row(3).write(2, time(17, 1))
20ws.row(4).set_cell_date(0, date(2021, 3, 18))
21ws.row(4).set_cell_date(1, datetime(2021, 3, 18, 17, 0, 1))
22ws.row(4).set_cell_date(2, time(17, 1))
23ws.row(5).write(0, False)
24ws.row(5).write(1, True)
25ws.row(6).set_cell_boolean(0, False)
26ws.row(6).set_cell_boolean(1, True)
27ws.row(7).set_cell_error(0, 0x17)
28ws.row(7).set_cell_error(1, '#NULL!')
29ws.row(8).write(0, '', Style.easyxf('pattern: pattern solid, fore_colour green;'))
30ws.row(8).write(1, None, Style.easyxf('pattern: pattern solid, fore_colour blue;'))
31ws.row(9).set_cell_blank(0, Style.easyxf('pattern: pattern solid, fore_colour yellow;'))
32ws.row(10).set_cell_mulblanks(5, 10, Style.easyxf('pattern: pattern solid, fore_colour red;'))
33wb.save('測試數據33.xls')

"'測試數據33.xls"文件內容如下：

表格樣式邊框Borders類的屬性值：

# THIN    = 0x01 細實線

# MEDIUM  = 0x02 小粗實線

# DASHED  = 0x03 細虛線

# DOTTED  = 0x04 中細虛線

# THICK   = 0x05 大粗實線

# DOUBLE  = 0x06 雙線

# HAIR    = 0x07 細點虛線

# 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 斜點劃線

borders.left_colour ：設置線顏色

1from datetime import date
2from xlwt import Workbook, XFStyle, Borders, Pattern, Font
3
4# 創建樣式
5style = XFStyle()
6
7# 邊框
8# THIN    = 0x01 細實線
9# MEDIUM  = 0x02 小粗實線
10# DASHED  = 0x03 細虛線
11# DOTTED  = 0x04 中細虛線
12# THICK   = 0x05 大粗實線
13# DOUBLE  = 0x06 雙線
14# HAIR    = 0x07 細點虛線
15# MEDIUM_DASHED = 0x08  大粗虛線
16# THIN_DASH_DOTTED = 0x09 細點劃線
17# MEDIUM_DASH_DOTTED = 0x0A 粗點劃線
18# THIN_DASH_DOT_DOTTED = 0x0B 細雙點劃線
19# MEDIUM_DASH_DOT_DOTTED = 0x0C 粗雙點劃線
20# SLANTED_MEDIUM_DASH_DOTTED = 0x0D 斜點劃線
21borders = Borders()
22borders.left = Borders.THIN
23borders.right = Borders.DASHED
24borders.top = Borders.DOUBLE
25borders.bottom = Borders.MEDIUM_DASH_DOT_DOTTED
26borders.left_colour = 4 #設置線顏色
27
28# 設置樣式的邊框屬性
29style.borders = borders
30# 格式化單元格數據
31style.num_format_str = 'YYYY-MM-DD'
32
33book = Workbook()
34sheet = book.add_sheet('樣式')
35sheet.write(1, 1, date(2021, 1, 15), style)
36book.save('測試數據55.xls')

填充Pattern

背景填充Pattern類的屬性值：

NO_PATTERN：無填充

SOLID_PATTERN：填充

0 黑色 1 白色 2 紅色 3 綠色 4 藍色 5 黃色 6 紫紅色

1from datetime import date
2from xlwt import Workbook, XFStyle, Borders, Pattern, Font
3
4# 創建樣式
5style = XFStyle()
6
7# 背景
8pattern = Pattern()
9pattern.pattern = Pattern.SOLID_PATTERN  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
10pattern.pattern_fore_colour = 4  # 給背景顏色賦值
11style.pattern = pattern  # 把背景顏色加到表格樣式裡去
12
13# 格式化單元格數據
14style.num_format_str = 'YYYY-MM-DD'
15
16book = Workbook()
17sheet = book.add_sheet('樣式')
18sheet.write(1, 1, date(2021, 1, 15), style)
19book.save('測試數據55.xls')

字體Font

字體Font類的屬性，其中字體顏色設置方法：

1aqua 0x31
2black 0x08
3blue 0x0C
4blue_gray 0x36
5bright_green 0x0B
6brown 0x3C
7coral 0x1D
8cyan_ega 0x0F
9dark_blue 0x12
10dark_blue_ega 0x12
11dark_green 0x3A
12dark_green_ega 0x11
13dark_purple 0x1C
14dark_red 0x10
15dark_red_ega 0x10
16dark_teal 0x38
17dark_yellow 0x13
18gold 0x33
19gray_ega 0x17
20gray25 0x16
21gray40 0x37
22gray50 0x17
23gray80 0x3F
24green 0x11
25ice_blue 0x1F
26indigo 0x3E
27ivory 0x1A
28lavender 0x2E
29light_blue 0x30
30light_green 0x2A
31light_orange 0x34
32light_turquoise 0x29
33light_yellow 0x2B
34lime 0x32
35magenta_ega 0x0E
36ocean_blue 0x1E
37olive_ega 0x13
38olive_green 0x3B
39orange 0x35
40pale_blue 0x2C
41periwinkle 0x18
42pink 0x0E
43plum 0x3D
44purple_ega 0x14
45red 0x0A
46rose 0x2D
47sea_green 0x39
48silver_ega 0x16
49sky_blue 0x28
50tan 0x2F
51teal 0x15
52teal_ega 0x15
53turquoise 0x0F
54violet 0x14
55white 0x09
56yellow 0x0D"""
57

示例：

1from datetime import date
2from xlwt import Workbook, XFStyle, Borders, Pattern, Font
3
4# 創建樣式
5style = XFStyle()
6
7# 字體
8font = Font()
9font.name = '微軟雅黑'  # 設置字體
10font.colour_index = 4  # 設置字體顏色
11font.bold = True  # 是否加粗
12font.italic = True  # 字體是否為斜體
13font.underline = True  # 字體是否有下劃線
14font.struck_out = True  # 字體中是否有橫線
15font.height = 20 * 10  # 設置字號為10號
16style.font = font
17
18# 格式化單元格數據
19style.num_format_str = 'YYYY-MM-DD'
20
21book = Workbook()
22sheet = book.add_sheet('樣式')
23sheet.write(1, 1, date(2021, 1, 15), style)
24book.save('測試數據55.xls')

對齊Alignment

常用屬性值：

1from datetime import date
2from xlwt import Workbook, XFStyle, Borders, Pattern, Font, Alignment
3
4# 創建樣式
5style = XFStyle()
6
7# 對齊
8alignment = Alignment()
9alignment.horz = 0x02  # 0x01(左端對齊)、0x02(水平方向上居中對齊)、0x03(右端對齊)
10alignment.vert = 0x01  # 0x00(上端對齊)、 0x01(垂直方向上居中對齊)、0x02(底端對齊)
11alignment.wrap = 1  # 設置自動換行
12style.alignment = alignment
13
14# 格式化單元格數據
15style.num_format_str = 'YYYY-MM-DD'
16
17book = Workbook()
18sheet = book.add_sheet('樣式')
19sheet.write(1, 1, date(2021, 1, 15), style)
20book.save('測試數據55.xls')

設置單元格高度、寬度

ROW類的width 和 height 屬性用於設置寬和高

默認行高是和文字的高度進行匹配的，即 **height_mismatch **的值是 False，這就導致了我們設置行高不會生效，我們要做的就是在設置某一行的高度之前，先把這個屬性的值改為 True 就好了

寬度的基本單位為256.所以設置的時候一般用256 × 需要的寬度

行高的基本單位為20

1from datetime import date
2import xlwt
3
4book = xlwt.Workbook()
5sheet = book.add_sheet('樣式')
6
7# 設置單元格寬度
8sheet.col(0).width = 5000
9# 設置單元格高度
10sheet.row(0).height_mismatch = True
11sheet.row(0).height = 800
12
13sheet.write(1, 1, date(2021, 1, 15))
14book.save('測試數據55.xls')

使用easyxf設置樣式

語法規則：(<element>:(<attribute> <value>,)+;)+

1easyxf(
2    '元素: 屬性 值;'
3    '元素: 屬性 值, 屬性 值, 屬性 值, 屬性 值;'
4    '元素: 屬性 值, 屬性 值;')

文本包含元素定義的分號分隔列表

每個元素包含一個用逗號分隔的屬性和值對列表

1from datetime import date
2from xlwt import Workbook, easyxf
3
4book = Workbook()
5sheet = book.add_sheet('A Date')
6sheet.write(1, 1, date(2021, 1, 15), easyxf(
7    'font: name 宋體;'
8    'borders: left thick, right thick, top thick, bottom thick;'
9    'pattern: pattern solid, fore_colour red;', num_format_str='YYYY-MM-DD'))
10book.save('測試數據66.xls')

格式化行和列

樣式的優先級：

行和列設置樣式：

單元格設置樣式：

Sheet類的write(self, r, c, label="", style=Style.default_style)方法

隱藏行和列：

1from xlwt import Workbook, easyxf
2from xlwt.Utils import rowcol_to_cell
3
4row = easyxf('pattern: pattern solid, fore_colour blue')
5col = easyxf('pattern: pattern solid, fore_colour green')
6cell = easyxf('pattern: pattern solid, fore_colour red')
7book = Workbook()
8sheet = book.add_sheet('優先級')
9for i in range(0, 10, 2):
10    sheet.row(i).set_style(row)
11for i in range(0, 10, 2):
12    sheet.col(i).set_style(col)
13for i in range(10):
14    sheet.write(i, i, None, cell)
15sheet = book.add_sheet('隱藏')
16for rowx in range(10):
17    for colx in range(10):
18        sheet.write(rowx, colx, rowcol_to_cell(rowx, colx))
19for i in range(0, 10, 2):
20    sheet.row(i).hidden = True
21    sheet.col(i).hidden = True
22sheet = book.add_sheet('行高度和列寬度')
23for i in range(10):
24    sheet.write(0, i, 0)
25for i in range(10):
26    sheet.row(i).set_style(easyxf('font:height ' + str(200 * i)))
27    sheet.col(i).width = 256 * i
28book.save('測試數據77.xls')

超連結

1from xlwt import Workbook, easyxf, Formula
2
3style = easyxf('font: underline single')
4book = Workbook()
5sheet = book.add_sheet('Hyperlinks')
6sheet.write(0, 0, Formula('HYPERLINK("http://www.python.org";"Python")'), style)
7
8link = 'HYPERLINK("mailto:[e-mail]";"help")'
9sheet.write(1, 0, Formula(link), style)
10book.save("超連結.xls")

插入圖片

通過xlwt.Worksheet.Worksheet對象的 insert_bitmap(self, filename, row, col, x = 0, y = 0, scale_x = 1, scale_y = 1) 方法向指定單元格插入圖片

1from xlwt import Workbook
2w = Workbook()
3ws = w.add_sheet('圖片')
4ws.insert_bitmap('222.bmp', 0, 0)
5w.save('images.xls')

合併單元格

通過xlwt.Worksheet.Worksheet對象的 write_merge(self, r1, r2, c1, c2, label="", style=Style.default_style) 方法合併單元格，其中r1 和 r2 表示開始行結束行；c1和c2表示開始列和結束列。

1from xlwt import Workbook, easyxf
2
3style = easyxf('pattern: pattern solid, fore_colour red;'
4               'align: vertical center, horizontal center;')
5w = Workbook()
6ws = w.add_sheet('合併單元')
7ws.write_merge(1, 5, 1, 5, '合併222', style)
8w.save('合併單元格.xls')

拆分和凍結窗口

1from xlwt import Workbook
2from xlwt.Utils import rowcol_to_cell
3
4w = Workbook()
5sheet = w.add_sheet('Freeze')
6sheet.panes_frozen = True
7sheet.remove_splits = True
8sheet.vert_split_pos = 2
9sheet.horz_split_pos = 10
10sheet.vert_split_first_visible = 5
11sheet.horz_split_first_visible = 40
12for col in range(20):
13    for row in range(80):
14        sheet.write(row, col, rowcol_to_cell(row, col))
15w.save('panes.xls')

將列表寫入單元格

步驟：

1from xlwt import Workbook
2
3data = [
4    ['', '', '2008', '', '2009'],
5    ['', '', 'Jan', 'Feb', 'Jan', 'Feb'],
6    ['Company X'],
7    ['', 'Division A'],
8    ['', '', 100, 200, 300, 400],
9    ['', 'Division B'],
10    ['', '', 100, 99, 98, 50],
11    ['Company Y'],
12    ['', 'Division A'],
13    ['', '', 100, 100, 100, 100],
14    ['', 'Division B'],
15    ['', '', 100, 101, 102, 103], ]
16w = Workbook()
17ws = w.add_sheet('Outlines')
18for i, row in enumerate(data):
19    print("i=", i, "row=", row)
20    for j, cell in enumerate(row):
21        print("j=", j, "cell=", cell)
22        ws.write(i, j, cell)
23w.save('outlines.xls')

輸出結果：

1i= 0 row= ['', '', '2008', '', '2009']
2j= 0 cell= 
3j= 1 cell= 
4j= 2 cell= 2008
5j= 3 cell= 
6j= 4 cell= 2009
7i= 1 row= ['', '', 'Jan', 'Feb', 'Jan', 'Feb']
8j= 0 cell= 
9j= 1 cell= 
10j= 2 cell= Jan
11j= 3 cell= Feb
12j= 4 cell= Jan
13j= 5 cell= Feb
14i= 2 row= ['Company X']
15j= 0 cell= Company X
16i= 3 row= ['', 'Division A']
17j= 0 cell= 
18j= 1 cell= Division A
19i= 4 row= ['', '', 100, 200, 300, 400]
20j= 0 cell= 
21j= 1 cell= 
22j= 2 cell= 100
23j= 3 cell= 200
24j= 4 cell= 300
25j= 5 cell= 400
26i= 5 row= ['', 'Division B']
27j= 0 cell= 
28j= 1 cell= Division B
29i= 6 row= ['', '', 100, 99, 98, 50]
30j= 0 cell= 
31j= 1 cell= 
32j= 2 cell= 100
33j= 3 cell= 99
34j= 4 cell= 98
35j= 5 cell= 50
36i= 7 row= ['Company Y']
37j= 0 cell= Company Y
38i= 8 row= ['', 'Division A']
39j= 0 cell= 
40j= 1 cell= Division A
41i= 9 row= ['', '', 100, 100, 100, 100]
42j= 0 cell= 
43j= 1 cell= 
44j= 2 cell= 100
45j= 3 cell= 100
46j= 4 cell= 100
47j= 5 cell= 100
48i= 10 row= ['', 'Division B']
49j= 0 cell= 
50j= 1 cell= Division B
51i= 11 row= ['', '', 100, 101, 102, 103]
52j= 0 cell= 
53j= 1 cell= 
54j= 2 cell= 100
55j= 3 cell= 101
56j= 4 cell= 102
57j= 5 cell= 103

根據指定單元格獲取樣式信息

注意：formatting_info必須設置為True，才能使用xlutils.styles
步驟：

1from xlrd import open_workbook
2from xlutils.styles import Styles
3
4book = open_workbook('測試數據88.xls', formatting_info=True)
5styles = Styles(book)
6sheet = book.sheet_by_index(0)
7print(styles[sheet.cell(1, 1)].name)
8print(styles[sheet.cell(1, 2)].name)
9A1_style = styles[sheet.cell(0, 0)]
10A1_font = book.font_list[A1_style.xf.font_index]
11print(book.colour_map[A1_font.colour_index])

輸出結果：

1Normal
2Normal
3None

更新已存在的表格

xlutils.copy方法傳入xlrd.book.Book對象，返回一個xlwt.Workbook.Workbook對象

通過xlwt.Workbook.Workbook對象更新表格數據

最後調用xlwt.Workbook.Workbook對象的save方法保存數據（文件名可以與原文件同名，但是考慮到部分東西不會被複製，建議另存為其他文件名）

需要注意的是，有些東西不會被複製：

1from xlrd import open_workbook
2from xlwt import easyxf
3from xlutils.copy import copy
4
5rb = open_workbook('測試數據88.xls', formatting_info=True)
6rs = rb.sheet_by_index(0)
7wb = copy(rb)
8ws = wb.get_sheet(0)
9plain = easyxf('')
10for i, cell in enumerate(rs.col(2)):
11    if not i:  # 數字0等同於False，i=0時，跳過
12        continue
13    ws.write(i, 2, cell.value + "BB", plain)
14for i, cell in enumerate(rs.col(4)):
15    if not i:  # 數字0等同於False，i=0時，跳過
16        continue
17    ws.write(i, 4, cell.value + "AA")
18wb.save('測試數據881.xls')

測試數據88.xls：

測試數據881.xls：