xlwt模塊的功能主要負(fù)責(zé)對(duì)Excel文件進(jìn)行寫入操作秘蛇，xlwt官網(wǎng)將其描述為：xlwt is a library for writing data and formatting information to older Excel files (ie: .xls)【注意older】
注意：
該模塊只可以將文件保存為xls格式的Excel文件，xls格式與xlms格式的區(qū)別簡(jiǎn)單來(lái)講主要在于Excel對(duì)宏（VBA）的處理上
建議：
當(dāng)使用Python在進(jìn)行數(shù)據(jù)可視化處理時(shí)淀散，將Python與Excel在數(shù)據(jù)可視化處理中各自應(yīng)發(fā)揮的優(yōu)勢(shì)和功能做好定位和區(qū)分，減少試圖通過(guò)Python來(lái)影響Excel內(nèi)容或希望Python可以一鍵操作數(shù)據(jù)的要求，各自發(fā)揮各自的優(yōu)勢(shì)，這也是Python的精髓勇婴，Python官網(wǎng)將其描述為：Python is a programming language that lets you work quickly and integrate systems more effectively
官網(wǎng)地址：
https://xlwt.readthedocs.io/en/latest/
xlwt API：
https://xlwt.readthedocs.io/en/latest/api.html
- xlwt常用類有
1. Workbook類
《用于創(chuàng)建工作簿及操作內(nèi)容的類，官方描述為：This is a class representing a workbook and all its contents. When creating Excel files with xlwt, you will normally start by instantiating an object of this class》
2. Worksheet類
《用于操作工作表內(nèi)容的類嘱腥，但該類不需要主動(dòng)調(diào)用耕渴，主要由Workbook類中add_sheet方法返回，官方描述為：This is a class representing the contents of a sheet in a workbook. warning:You don't normally create instances of this class yourself. They are returned from calls to :meth:~xlwt.Workbook.Workbook.add_sheet.》
3. Formatting類
《用于設(shè)置單元格樣式具體包括6組齿兔，分別包括數(shù)字橱脸、對(duì)齊方式、邊框分苇、背景添诉、保護(hù)，官方描述為：The XF record is able to store explicit cell formatting attributes or the attributes of a cell style. Explicit formatting includes the reference to a cell style XF record. This allows to extend a defined cell style with some explicit attributes. The formatting attributes are divided into 6 groups:
one group:Number format---Number format index (index to FORMAT record)
two group:Font---Font index (index to FONT record)
three group:Alignment---Horizontal and vertical alignment, text wrap, indentation, orientation/rotation, text direction
four group:Border---Border line styles and colours
five group:Background---Background area style and colours
six group:Protection---Cell locked, formula hidden》
4.Style類
《Style類為Formatting類提供了一個(gè)便捷操作的入口医寿，通過(guò)調(diào)用Style類及Style類的函數(shù)可以間接實(shí)現(xiàn)對(duì)Formatting類操作栏赴，從而避免了Formatting類中龐雜的操作細(xì)節(jié)，Style類與Formatting類配置項(xiàng)相比多了num_format_str一項(xiàng)靖秩，該項(xiàng)用來(lái)指定自定義的數(shù)字格式》
- 部分源碼如下
class XFStyle(object):
def __init__(self):
self.num_format_str = 'General'
self.font = Formatting.Font()
self.alignment = Formatting.Alignment()
self.borders = Formatting.Borders()
self.pattern = Formatting.Pattern()
self.protection = Formatting.Protection()
5.Column類
《該類用與設(shè)置工作表列的內(nèi)容》
6.Row類
《該類用于設(shè)置工作表行的內(nèi)容》
7. ExcelFormula類
《該類提供了Formula類函數(shù)须眷，該函數(shù)用來(lái)滿足工作表寫入公式竖瘾、超鏈接等其他特殊格式的數(shù)據(jù)》
-Workbook類常用方法
1. xlwt.Workbook(encoding='ascii', style_compression=0)
《建立一個(gè)新工作簿，encoding指定編碼方式花颗，compression指定是否壓縮捕传，該方法為類方法，即使用該方法時(shí)相當(dāng)于建立了一個(gè)workbook實(shí)例》
2. xlwt.Workbook.Workbook.add_sheet(sheetname, cell_overwrite_ok=False)
《sheetname指定工作表名捎稚，overwrite為TRUE將不會(huì)彈出對(duì)同一工作表中同一單元格重寫入的異常提示乐横，該函數(shù)返回worksheet類》
- 部分源碼如下
def add_sheet(self, sheetname, cell_overwrite_ok=False):
"""
This method is used to create Worksheets in a Workbook.
:param sheetname:
The name to use for this sheet, as it will appear in the
tabs at the bottom of the Excel application.
:param cell_overwrite_ok:
If ``True``, cells in the added worksheet will not raise an
exception if written to more than once.
:return:
The :class:`~xlwt.Worksheet.Worksheet` that was added.
"""
例：
newBook = xlwt.Workbook()
newSheet = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
newSheet.write(0,0,"林州歡迎你")
newSheet.write(0,0,"林州歡迎你呀")
newBook.save("TheRedFlagCanyon2.xls")
輸出錯(cuò)誤提示：Exception: Attempt to overwrite cell: sheetname='FirstSheet' rowx=0 colx=0
3. xlwt.Workbook.Workbook.save(filename_or_stream)
《filename指定保存文件的名稱或使用CompoundDoc類保存到IO流》
- 部分源碼如下
def save(self, filename_or_stream):
"""
This method is used to save the Workbook to a file in native Excel
format.
:param filename_or_stream:
This can be a string containing a filename of
the file, in which case the excel file is saved to disk using the name
provided. It can also be a stream object with a write method, such as
a :class:`~io.StringIO`, in which case the data for the excel
file is written to the stream.
"""
例：
wb = xlwt.Workbook("utf-8",style_compression=0)
ws1 = wb.add_sheet("這是工作表1",cell_overwrite_ok=False)
ws2 = wb.add_sheet("這是工作表2",cell_overwrite_ok=False)
wb.save("test.xls")
- Worksheet類常用方法
1. xlwt.Worksheet.write(r, c, label="", style=Style.default_style)
《r表明要寫入的單元格行數(shù)，默認(rèn)從0開(kāi)始今野，c表明要寫入的單元格列數(shù)，默認(rèn)從0開(kāi)始罐农，label指定數(shù)據(jù)轉(zhuǎn)換的方式条霜，如int型、long型將被轉(zhuǎn)換為float型等涵亏，style指定由XFStyle類設(shè)置的樣式》
- 部分源碼如下
def write(self, r, c, label="", style=Style.default_style):
"""
This method is used to write a cell to a :class:`Worksheet`.
:param r:
The zero-relative number of the row in the worksheet to which
the cell should be written.
:param c:
The zero-relative number of the column in the worksheet to which
the cell should be written.
:param label:
The data value to be written.
An :class:`int`, :class:`long`, or
:class:`~decimal.Decimal` instance is converted to :class:`float`.
A :class:`unicode` instance is written as is. A :class:`bytes`
instance is converted to :class:`unicode` using the
encoding, which defaults to ``ascii``, specified when the
:class:`Workbook` instance was created.
A :class:`~datetime.datetime`, :class:`~datetime.date` or
:class:`~datetime.time` instance is converted into Excel date format
(a float representing the number of days since (typically)
``1899-12-31T00:00:00``, under the pretence that
1900 was a leap year).
A :class:`bool` instance will show up as ``TRUE`` or ``FALSE`` in
Excel.
``None`` causes the cell to be blank: no data, only formatting.
An :class:`xlwt.Formula` instance causes an Excel formula to be
written.
:param style:
A style, also known as an XF (extended format), is an
:class:`~xlwt.Style.XFStyle` object, which encapsulates the
formatting applied to the cell and its contents.
:class:`~xlwt.Style.XFStyle` objects are best set up using the
:func:`~xlwt.Style.easyxf` function. They may also be set up by
setting attributes in :class:`Alignment`, :class:`Borders`,
:class:`Pattern`, :class:`Font` and :class:`Protection` objects then
setting those objects and a format string as attributes of an
:class:`~xlwt.Style.XFStyle` object.
"""
2. xlwt.Worksheet.merge(r1, r2, c1, c2, style=Style.default_style)
《該函數(shù)用于合并參數(shù)指定的單元格，r1指定起始行，r2指定合并終止行盯桦，c1指定啟始列连锯，c2指定合并終止列，style指定合并樣式》
例：
newBook = xlwt.Workbook()
newSheet = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
newStyle = xlwt.Style.XFStyle()
alignement = xlwt.Alignment()
alignement.vert = 1
alignement.horz = 2
newStyle.alignment = alignement
newSheet.merge(0,1,0,3)
newSheet.write(0,0,"林州歡迎你",newStyle)
newSheet.write(0,4,"林州歡迎你呀")
newSheet.write(1,4,"林州還是歡迎你呀")
newSheet.write(2,0,"林州依然歡迎你呀")
newSheet.write(2,1,"林州還是歡迎你呀")
newSheet.write(2,2,"林州一直歡迎你呀")
newSheet.write(2,3,"林州總是歡迎你呀")
newSheet.write(2,4,"林州永遠(yuǎn)歡迎你呀")
newBook.save("TheRedFlagCanyon2.xls")
注意：當(dāng)單元格合并后宠默，數(shù)據(jù)填充的位置將變?yōu)槠鹗夹泻推鹗剂恤锘校冶缓喜⒌奈恢贸鹗夹泻推鹗剂锌商畛渫馄溆啾缓喜⒌奈恢镁豢蓪懭霐?shù)據(jù)
3. xlwt.Worksheet.write_merge(r1, r2, c1, c2, label="", style=Style.default_style)
《該函數(shù)同merge()函數(shù)除多出可指定寫入數(shù)據(jù)外，其余完全相同》
例：
newBook = xlwt.Workbook()
newSheet = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
newStyle = xlwt.Style.XFStyle()
alignement = xlwt.Alignment()
alignement.vert = 1
alignement.horz = 2
newStyle.alignment = alignement
#注意此處替換
newSheet.write_merge(0,1,0,3,"林州歡迎你",newStyle)
newSheet.write(0,4,"林州歡迎你呀")
newSheet.write(1,4,"林州還是歡迎你呀")
newSheet.write(2,0,"林州依然歡迎你呀")
newSheet.write(2,1,"林州還是歡迎你呀")
newSheet.write(2,2,"林州一直歡迎你呀")
newSheet.write(2,3,"林州總是歡迎你呀")
newSheet.write(2,4,"林州永遠(yuǎn)歡迎你呀")
newBook.save("TheRedFlagCanyon2.xls")
4.xlwt.Worksheet.insert_bitmap(filename, row, col, x = 0, y = 0, scale_x = 1, scale_y = 1)
《該函數(shù)用于插入?yún)?shù)指定的位圖搀矫，filename指定要插入的位圖文件名稱抹沪，row，col指定插入的行和列瓤球，x融欧，y指定距離，scale指定縮放比例卦羡，注意圖片大小不能超過(guò)65K噪馏，且格式必須為bmp位圖格式》
例：
newBook = xlwt.Workbook()
newSheet = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
newStyle = xlwt.Style.XFStyle()
alignement = xlwt.Alignment()
alignement.vert = 1
alignement.horz = 2
newStyle.alignment = alignement
newSheet.write_merge(0,1,0,3,"林州歡迎你",newStyle)
newSheet.write(0,4,"林州歡迎你呀")
newSheet.write(1,4,"林州還是歡迎你呀")
newSheet.insert_bitmap('linzhou.bmp',2,0,x=10,y=10,scale_x=0.9,scale_y=0.9)
5.xlwt.Worksheet.get_name()
6.xlwt.Worksheet.set_name(value)
《函數(shù)5和函數(shù)6一個(gè)用來(lái)獲取工作表的名稱一個(gè)用來(lái)設(shè)置工作表的名稱》
例：
newBook = xlwt.Workbook()
newSheet = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
print(newSheet.get_name())
newSheet.set_name("WelcomeToLinzhou")
print(newSheet.get_name())
第一個(gè)輸出：FirstSheet
第二個(gè)輸出：WelcomeToLinzhou
7.xlwt.Worksheet.get_parent()
《該函數(shù)用來(lái)獲取該工作表的父對(duì)象也就是工作簿》
例：
newBook = xlwt.Workbook()
newSheet = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
newSheet2 = newSheet.get_parent().add_sheet("LinzhouWelcomeToyou",cell_overwrite_ok=True)
print(newSheet2.get_name())
第一個(gè)輸出：LinzhouWelcomeToyou
8. xlwt.Worksheet.set_protect(value)
《為工作表設(shè)置文檔保護(hù)，默認(rèn)不保護(hù)绿饵，value接受一個(gè)數(shù)值型參數(shù)欠肾，非0表示保護(hù)該工作表》
9. xlwt.Worksheet.set_password(value)
《為工作表設(shè)置保護(hù)密碼，當(dāng)撤銷工作表保護(hù)時(shí)需要輸入該密碼》
10.xlwt.Worksheet.set_panes_frozen(value)
10.1.xlwt.Worksheet.set_horz_split_pos(value)
10.2.xlwt.Wroksheet.set_vert_split_pos(value)
《函數(shù)10蝴罪、10.1董济、10.2用來(lái)實(shí)現(xiàn)工作表的凍結(jié)功能，其中函數(shù)10指定開(kāi)啟工作表凍結(jié)功能要门，函數(shù)10.1指定要凍結(jié)的行虏肾，函數(shù)10.2指定要凍結(jié)的列》
例：
newBook = xlwt.Workbook()
newSheet1 = newBook.add_sheet("FirstSheet",cell_overwrite_ok=False)
newSheet2 = newBook.add_sheet("SecondSheet",cell_overwrite_ok=False)
#生成一個(gè)二維數(shù)組
z = [[a,b] for a in range(20) for b in range(20)]
#將二維數(shù)組中每一維的序號(hào)轉(zhuǎn)換為字符串形式添加到每一維的數(shù)組中
[b.append(str(a)) for a, b in enumerate(z)]
#寫入數(shù)據(jù)
for t in z:
newSheet1.write(t[0],t[1],t[2])
newSheet2.write(t[0],t[1],t[2])
#設(shè)置凍結(jié)對(duì)象
newSheet1.set_panes_frozen(1)
#設(shè)置凍結(jié)行
newSheet1.set_horz_split_pos(2)
#設(shè)置凍結(jié)列
newSheet1.set_vert_split_pos(2)
newBook.save("TheRedFlagCanyon3.xls")
11.xlwt.Wroksheet.set_show_headers(value)
《該函數(shù)用來(lái)隱藏工作表的行列標(biāo)簽廓啊，默認(rèn)為顯示》
例：
newSheet1.set_show_headers(0)
12.xlwt.Worksheet.set_show_grid(value)
《該函數(shù)用來(lái)隱藏網(wǎng)格線，默認(rèn)為顯示》
例：
newSheet1.set_show_grid(0)
13.xlwt.Worksheet.set_cols_right_to_left(value)
《該函數(shù)用來(lái)設(shè)置表格排列方式封豪，默認(rèn)為從左到右排序》
例：
newSheet1.set_cols_right_to_left(1)
14.xlwt.Worksheet.set_print_headers(value)
《設(shè)置打印行列標(biāo)簽谴轮，默認(rèn)不打印》
例：
newSheet1.set_print_headers(1)
15.xlwt.Worksheet.set_print_grid(value)
《設(shè)置打印時(shí)候是否打印網(wǎng)格線，默認(rèn)不打印》
例：
newSheet1.set_print_grid(1)
16.xlwt.Worksheet.set_print_centered_vert(value)
17.xlwt.Worksheet.set_print_centered_horz(value)
《函數(shù)16吹埠、17用來(lái)設(shè)置打印時(shí)是否居中顯示第步，函數(shù)16指定是否垂直居中，默認(rèn)不居中缘琅，函數(shù)17指定是否水平居中粘都，默認(rèn)居中》
例：
newSheet1.set_print_centered_vert(1)
newSheet1.set_print_centered_horz(1)
18.xlwt.Worksheet.set_header_str(value)
19.xlwt.Worksheet.set_footer_str(value)
《函數(shù)18、19用來(lái)設(shè)置工作表的頁(yè)眉和頁(yè)腳刷袍，其中函數(shù)18設(shè)置頁(yè)眉翩隧，函數(shù)19設(shè)置頁(yè)腳，注意由于Excel文件在使用時(shí)不顯示頁(yè)眉和頁(yè)腳故只有在打印時(shí)頁(yè)眉和頁(yè)腳才有效呻纹，以上兩個(gè)函數(shù)接受的特殊字符及代表的意義如下》
例：
newSheet1.set_header_str('NAME: &A DATE:&D No.&P'.encode())
newSheet1.set_footer_str('No.&P'.encode())
特殊字符意義如下：
&& The "&" character itself
&L Start of the left section
&C Start of the centred section
&R Start of the right section
&P Current page number
&N Page count
&D Current date
&T Current time
&A Sheet name (BIFF5-BIFF8)
&F File name without path
&Z File path without file name (BIFF8X)
&G Picture (BIFF8X)
&B Bold on/off (BIFF2-BIFF4)
&I Italic on/off (BIFF2-BIFF4)
&U Underlining on/off
&E Double underlining on/off (BIFF5-BIFF8)
&S Strikeout on/off
&X Superscript on/off (BIFF5-BIFF8)
&Y Subscript on/off (BIFF5-BIFF8)
20.xlwt.Worksheet.set_left_margin(value)
21.xlwt.Worksheet.set_right_margin(value)
22.xlwt.Worksheet.set_top_margin(value)
23.xlwt.Worksheet.set_bottom_margin(value)
24.xlwt.Worksheet.set_header_margin(value)
25.xlwt.Worksheet.set_footer_margin(value)
《函數(shù)20堆生、21、22雷酪、23淑仆、24、25用來(lái)設(shè)置工作表的頁(yè)邊距哥力，其中函數(shù)20指定設(shè)置左邊距蔗怠，默認(rèn)0.3，函數(shù)21指定右邊距省骂，默認(rèn)0.3蟀淮，函數(shù)22指定上邊距，默認(rèn)0.6钞澳，函數(shù)23指定下邊距怠惶，默認(rèn)0.3，函數(shù)24指定頁(yè)眉邊距轧粟，默認(rèn)0.1策治，函數(shù)25指定頁(yè)腳邊距，默認(rèn)0.1》
例：
newSheet1.set_left_margin(0.5)
newSheet1.set_right_margin(0.5)
newSheet1.set_top_margin(0.5)
newSheet1.set_bottom_margin(0.5)
newSheet1.set_header_margin(0.5)
newSheet1.set_footer_margin(0.5)
newSheet1.set_header_str('No.&P'.encode())
newSheet1.set_footer_str('No.&P'.encode())
26.xlwt.Worksheet.set_paper_size_code(value)
27.xlwt.Worksheet.set_print_scaling(value)
28.xlwt.Worksheet.set_print_colour(value)
29.xlwt.Worksheet.set_vert_page_breaks(value)
30.xlwt.Worksheet.set_horz_page_breaks(value)
31.xlwt.Worksheet.set_print_notes(value)
32.xlwt.Worksheet.set_print_notes_at_end(value)
33.xlwt.Worksheet.set_print_omit_errors(value)
《函數(shù)26-33都是為設(shè)置Excel文件打印時(shí)的規(guī)格兰吟，其中函數(shù)26指定打印的紙張通惫，其參數(shù)使用代號(hào)表示，代號(hào)如下圖所示混蔼，函數(shù)27指定打印縮放比例履腋，默認(rèn)不縮放，函數(shù)28指定是否單色打印，默認(rèn)為單色遵湖，函數(shù)29指定垂直打印區(qū)域悔政，函數(shù)30指定水平打印區(qū)域，其中29延旧、30函數(shù)不太明白谋国，函數(shù)31設(shè)置是否打印批注，函數(shù)32設(shè)置是否將批注打印在文檔結(jié)尾迁沫，函數(shù)33設(shè)置是否將錯(cuò)誤單元格替換為#N/A打印芦瘾，#N/A為默認(rèn)打印，設(shè)置錯(cuò)誤顯示值如下圖所示》
打印紙張代號(hào)編碼
8 A3 297mm x 420mm
9 A4 210mm x 297mm
10 A4 small 210mm x 297mm
11 A5 148mm x 210mm
12 B4 (JIS) 257mm x 364mm
13 B5 (JIS) 182mm x 257mm
33 B4 (ISO) 250mm x 353mm
34 B5 (ISO) 176mm x 250mm
35 B6 (ISO) 125mm x 176mm
66 A2 420mm x 594mm
70 A6 105mm x 148mm
設(shè)置打印錯(cuò)誤顯示值
0 打印顯示值
1 打印空白
2 打印 -
3 打印 #N/A
例：
newSheet1.set_paper_size_code(9) #A4紙
newSheet1.set_print_scaling(50) #縮小90%
newSheet1.set_print_colour(1) #單色打印
newSheet1.set_print_notes(1) #打印批注
newSheet1.set_print_notes_at_end(1) #末尾打印
newSheet1.set_print_omit_errors(3) #錯(cuò)誤打印設(shè)置
34.xlwt.Worksheet.row(index)
35.xlwt.Worksheet.col(index)
《函數(shù)34用來(lái)定位工作表的行集畅，函數(shù)35中col的全拼為column近弟，意為定位工作表的列》
例：
newBook = xlwt.Workbook("utf-8",style_compression=False)
newSheet = newBook.add_sheet("FistSheet",cell_overwrite_ok=False)
newSheet.write(0,0,label="林州歡迎你")
newSheet.write(1,1,label="太行大峽谷歡迎你")
newSheet.write(2,2,label="紅旗渠歡迎你")
newSheet.set_protect(1)
newSheet.set_password("this is unlock words")
newSheet.col(1).width = 4000
column = newSheet.row(1)
columnStyle = xlwt.Style.easyxf('font:height 400;')
column.set_style(columnStyle)
newBook.save("TheRedCanyon.xls")
36.xlwt.Worksheet.get_cols()
37.xlwt.Worksheet.get_rows()
《函數(shù)36用來(lái)獲取當(dāng)前工作表的列內(nèi)容，格式為：xlwt.Column.Column object牡整，函數(shù)37用來(lái)獲取當(dāng)前工作表的行內(nèi)容藐吮，格式為xlwt.Row.Row object，但函數(shù)36計(jì)算方式不太明白逃贝，請(qǐng)慎用》
例：
print(instantiation.workSheet.get_cols())
print(instantiation.workSheet.get_rows())
第一個(gè)輸出：{1: <xlwt.Column.Column object at 0x1071fead0>,......} 實(shí)有10列，但輸出5列迫摔，且從1開(kāi)始沐扳，搞不清為什么
第二個(gè)輸出：{0: <xlwt.Row.Row object at 0x1071e6890>, ......}，實(shí)有10行句占，輸出10行沪摄，可以正確顯示
- Formatting類包含的常用類有
1. Font類
《XFStyle類實(shí)際調(diào)用的設(shè)置字體類》
2. Alignment類
《XFStyle類實(shí)際調(diào)用的設(shè)置對(duì)齊類》
3. Borders類
《XFStyle類實(shí)際調(diào)用的設(shè)置邊框類》
4.Pattern類
《XFStyle類實(shí)際調(diào)用的設(shè)置裝飾類》
5. Protection類
《XFStyle類實(shí)際調(diào)用的設(shè)置保護(hù)類》
- Font類、Alignment類纱烘、Borders類杨拐、Pattern類、Protection類均可用_search_key函數(shù)來(lái)查詢可設(shè)置的內(nèi)容
- _search_key
《返回可設(shè)置的內(nèi)容擂啥，每項(xiàng)均由字面意思表示》 - Font類_search_key源碼如下
def _search_key(self):
return (
self.height, #度量值哄陶，指明字體大小，其為20的倍數(shù)哺壶，20為字體衡量單位
self.italic, #是否斜體屋吨，F(xiàn)ALSEorTRUE
self.struck_out, #突出顯示，指明是否設(shè)置刪除線山宾，F(xiàn)ALSEorTRUE
self.outline, #是否設(shè)置輪廓至扰，F(xiàn)ALSEorTRUE
self.shadow, #是否設(shè)置陰影，F(xiàn)ALSEorTRUE
self.colour_index, #顏色指示资锰，指明字體顏色
self.bold, #是否加粗敢课，F(xiàn)ALSEorTRUE
self._weight, #
self.escapement, #
self.underline, #是否設(shè)置下劃線，0（默認(rèn)不帶），1（單下劃線）直秆，2（雙下劃線）濒募，3（會(huì)計(jì)用單下劃線），4（會(huì)計(jì)用雙下劃線）
self.family, #指明字體家族
self.charset, #指明字符集
self.name, #指明字體名稱
)
例：
newBook = xlwt.Workbook("utf-8",style_compression=False)
newSheet = newBook.add_sheet("FistSheet",cell_overwrite_ok=False)
reWriteXFStyle = xlwt.XFStyle()
reWriteFont = xlwt.Font()
reWriteFont.height = 400
reWriteFont.name = "微軟雅黑"
reWriteFont.underline = True
reWriteFont.italic = True
reWriteXFStyle.font = reWriteFont
newSheet.write(0,0,"林州歡迎你",reWriteXFStyle)
newBook.save("TheRedCanyon.xls")
- Alignment類_search_key源碼如下
def _search_key(self):
return (
self.horz, #horizontal切厘，指明水平對(duì)齊方式萨咳，默認(rèn)0（常規(guī)），1（左對(duì)齊）疫稿，2（居中）培他，3（右對(duì)齊），4（填充）遗座，5（兩端對(duì)齊）舀凛，6（跨列居中），7（分散對(duì)齊）
self.vert, #vertical途蒋，指明垂直對(duì)齊方式猛遍，默認(rèn)2（低端對(duì)齊），0（頂端對(duì)齊）号坡，1（垂直居中）懊烤，3（兩端對(duì)齊），4（分散對(duì)齊）
self.dire, #direction宽堆，指明文字方向腌紧，默認(rèn)0（根據(jù)內(nèi)容），1（總是從左到右）畜隶，2（總是從右到左）
self.orie, #orientation壁肋，指明定位，默認(rèn)0
self.rota, #rotate籽慢，指明旋轉(zhuǎn)方向浸遗，默認(rèn)0，范圍0-255
self.wrap, #是否設(shè)置自動(dòng)換行箱亿，默認(rèn)0（不自動(dòng)換行）跛锌，1（自動(dòng)換行）
self.shri, #shrinkage，指明是否縮小字體填充极景，默認(rèn)0（不縮胁炀弧），1（縮小填充）
self.inde, #index盼樟，指明索引氢卡，默認(rèn)0
self.merg, #merge，指明合并晨缴，默認(rèn)0
)
例：
newBook = xlwt.Workbook("utf-8",style_compression=False)
newSheet = newBook.add_sheet("FistSheet",cell_overwrite_ok=False)
reWriteXFStyle = xlwt.XFStyle()
reWriteFont = xlwt.Font()
reWriteAlignment = xlwt.Alignment()
reWriteFont.height = 400
reWriteFont.name = "微軟雅黑"
reWriteFont.underline = True
reWriteFont.italic = True
reWriteAlignment.horz = 2
reWriteAlignment.vert = 1
reWriteXFStyle.font = reWriteFont
reWriteXFStyle.alignment = reWriteAlignment
newSheet.write(0,0,"林州歡迎你",reWriteXFStyle)
newBook.save("TheRedCanyon.xls")
- Borders類_search_key源碼如下
def _search_key(self):
return (
self.left, #左邊框樣式译秦，默認(rèn)0x00（不設(shè)置）
self.right, #右邊框樣式，默認(rèn)0x00（ 不設(shè)置）
self.top, #上邊框樣式，默認(rèn)0x00（不設(shè)置）
self.bottom, #下邊框樣式筑悴，默認(rèn)0x00（不設(shè)置）
self.diag, #diagonal们拙，指明對(duì)角線樣式，默認(rèn)0x00（不設(shè)置）
self.left_colour, #左邊框顏色阁吝，默認(rèn)64
self.right_colour, #有邊框顏色砚婆，默認(rèn)64
self.top_colour, #上邊框顏色，默認(rèn)64
self.bottom_colour, #下邊框顏色突勇，默認(rèn)64
self.diag_colour, #對(duì)角線顏色装盯，默認(rèn)64
self.need_diag1, #是否設(shè)置左上-右下對(duì)角線，默認(rèn)0：NO_NEED_DIAG1（不顯示）甲馋，1：NEED_DIAG1（顯示）
self.need_diag2, #是否設(shè)置左下-右上對(duì)角線埂奈，默認(rèn)0：NO_NEED_DIAG2，1：NEED_DIAG2（顯示）
)
例：
import xlwt
wb = xlwt.Workbook("utf-8",style_compression=0)
ws1 = wb.add_sheet("這是工作表1",cell_overwrite_ok=False)
cellStyle1 = xlwt.XFStyle()
cellStyle2 = xlwt.XFStyle()
cellStyleBorders1 = xlwt.Borders()
cellStyleBorders2 = xlwt.Borders()
cellStyleBorders1.left = 0x00
cellStyleBorders1.right = 0x01
cellStyleBorders1.top = 0x02
cellStyleBorders1.bottom = 0x03
cellStyleBorders1.left_colour = 0x10
cellStyleBorders1.right_colour = 0x11
cellStyleBorders1.top_colour = 0x12
cellStyleBorders1.bottom_colour = 0x13
cellStyleBorders1.need_diag1 = 1
cellStyleBorders1.diag = 0x13
cellStyleBorders1.diag_colour = 0x13
cellStyle1.borders = cellStyleBorders1
cellStyleBorders2.left = 0x04
cellStyleBorders2.right = 0x05
cellStyleBorders2.top = 0x06
cellStyleBorders2.bottom = 0x07
cellStyleBorders2.left_colour = 0x14
cellStyleBorders2.right_colour = 0x15
cellStyleBorders2.top_colour = 0x16
cellStyleBorders2.bottom_colour = 0x17
cellStyleBorders2.need_diag2 = 1
cellStyleBorders2.diag = 0x14
cellStyleBorders2.diag_colour = 0x14
cellStyle2.borders = cellStyleBorders2
ws1.write(0,0,"林州歡迎你",cellStyle1)
ws1.write(1,1,"林州歡迎你",cellStyle2)
wb.save("test.xls")
- Pattern類_search_key源碼如下
def _search_key(self):
return (
self.pattern, #是否設(shè)置填充碧聪，默認(rèn)0：NO_PATTERN（不設(shè)置）借宵，1：SOLID_PATTERN（純色填充）
self.pattern_fore_colour, #設(shè)置填充的前景色，默認(rèn)64
self.pattern_back_colour, #設(shè)置填充的背景色矾削，默認(rèn)65
)
例：
wb = xlwt.Workbook("utf-8",style_compression=0)
ws1 = wb.add_sheet("這是工作表1",cell_overwrite_ok=False)
cellStyle1 = xlwt.XFStyle()
cellStyle2 = xlwt.XFStyle()
cellStylePattern1 = xlwt.Pattern()
cellStylePattern1.pattern = 1
cellStylePattern1.pattern_back_colour = 0x0D
cellStylePattern1.pattern_fore_colour = 0x0D
cellStyle1.pattern = cellStylePattern1
cellStylePattern2 = xlwt.Pattern()
cellStylePattern2.pattern = 1
cellStylePattern2.pattern_back_colour = 0x0C
cellStylePattern2.pattern_fore_colour = 0x0C
cellStyle2.pattern = cellStylePattern2
ws1.write(0,0,"林州歡迎你",cellStyle1)
ws1.write(1,1,"林州歡迎你",cellStyle2)
wb.save("test.xls")
- Protection類_search_key源碼如下
def _search_key(self):
return (
self.cell_locked, #設(shè)置單元格是否鎖定，默認(rèn)1（鎖定）豁护，0（不鎖定）
self.formula_hidden, #設(shè)置單元格公式是否隱藏哼凯，默認(rèn)0（不隱藏），1（隱藏）
)
例：
newBook = xlwt.Workbook("utf-8",style_compression=False)
newSheet = newBook.add_sheet("FistSheet",cell_overwrite_ok=False)
newSheet.set_protect(256)
newSheet.set_password("this is unlock words")
reWriteXFStyle1 = xlwt.XFStyle()
reWriteXFStyle2 = xlwt.XFStyle()
reWriteProtect1 = xlwt.Protection()
reWriteProtect2 = xlwt.Protection()
reWriteProtect1.cell_locked = 0
reWriteProtect1.formula_hidden = 0
reWriteProtect2.formula_hidden = 1
reWriteXFStyle1.protection = reWriteProtect1
reWriteXFStyle2.protection = reWriteProtect2
newSheet.write(0,0,"下列單元格不可更改楚里，且隱藏公式断部，注意，該標(biāo)題不能更改")
newSheet.write(1,0,xlwt.Formula("-(1+1)"),reWriteXFStyle2)
newSheet.write(1,1,"下列單元格可以更改班缎，不隱藏公式蝴光，注意，改標(biāo)題不能更改")
newSheet.write(2,1,xlwt.Formula("(2+2)"),reWriteXFStyle1)
newBook.save("TheRedCanyon.xls")
- Style類常用函數(shù)
1. xlwt.Style.easyxf(***)
《用于便捷設(shè)置樣式达址，該函數(shù)接受多個(gè)參數(shù)蔑祟，但經(jīng)常使用的為第一個(gè)參數(shù)和第二個(gè)參數(shù)，第一個(gè)參數(shù)將指定一個(gè)可讀的設(shè)置樣式字符串沉唠，第二個(gè)參數(shù)將指定數(shù)字的自定義格式》
- 部分源碼如下
def easyxf(strg_to_parse="", num_format_str=None,
field_sep=",", line_sep=";", intro_sep=":", esc_char="\\", debug=False):
"""
This function is used to create and configure
:class:`XFStyle` objects for use with (for example) the
:meth:`Worksheet.write` method.
It takes a string to be parsed to obtain attribute values for
:class:`Alignment`, :class:`Borders`, :class:`Font`, :class:`Pattern` and
:class:`Protection` objects.
Refer to the examples in the file `examples/xlwt_easyxf_simple_demo.py`
and to the `xf_dict` dictionary in :mod:`xlwt.Style`.
Various synonyms including color/colour, center/centre and gray/grey are
allowed. Case is irrelevant (except maybe in font names). ``-`` may be used
instead of ``_``.
Example: ``font: bold on; align: wrap on, vert centre, horiz center``
:param num_format_str:
To get the "number format string" of an existing
cell whose format you want to reproduce, select the cell and click on
Format/Cells/Number/Custom. Otherwise, refer to Excel help.
Examples: ``"#,##0.00"``, ``"dd/mm/yyyy"``
:return: An :class:`XFstyle` object.
"""
例：
newBook = xlwt.Workbook("utf-8", style_compression=False)
newSheet = newBook.add_sheet("FistSheet", cell_overwrite_ok=False)
numberFormat = xlwt.Style.easyxf('',num_format_str='#,##0.00')
newSheet.write(0, 0, label="林州歡迎你")
newSheet.write(1, 1, label="太行大峽谷歡迎你")
newSheet.write(2, 2, label="紅旗渠歡迎你")
newSheet.write(3, 3, 6811466,numberFormat)
column = newSheet.row(1)
columnStyle = xlwt.Style.easyxf('font: height 400,bold on;align: wrap on,vert center,horiz center;')
column.set_style(columnStyle)
newSheet.set_protect(1)
newSheet.set_password("this is unlock words")
newBook.save("TheRedCanyon.xls")
- ExcelFormula類常用函數(shù)
1. xlwt.ExcelFormula.Formula(object)
《該函數(shù)用來(lái)解析特殊的數(shù)據(jù)格式疆虚，如超鏈接、公式等，更多例子可在https://github.com/python-excel/xlwt/examples/formula.py查看》
例：
newBook = xlwt.Workbook("utf-8", style_compression=False)
newSheet = newBook.add_sheet("FistSheet", cell_overwrite_ok=False)
column = newSheet.row(1)
columnStyle = xlwt.Style.easyxf('font: height 400,bold on;align: wrap on,vert center,horiz center;')
column.set_style(columnStyle)
newSheet.write(0, 0, label="林州歡迎你")
newSheet.write(1, 1, xlwt.Formula("(444+222)"))
newSheet.write(2, 2, xlwt.Formula('HYPERLINK("http://www.linzhou.gov.cn/","林州政府")'))
newBook.save("TheRedCanyon.xls")
