XlsxWriter 只寫
基本介紹
XlsxWriter 是在 Python 下操作 EXCEL 文檔的利器
100% 支持 Excel XLSX 文件， 支持 Excel 2003、Excel 2007 等版本
支持所有 Excel 單元格的數(shù)據(jù)格式
包括：
- 合并單元格、批注、自動篩選、豐富多格式字符串等
- 支持工作表PNG / JPEG / BMP / WMF / EMF圖像。
- 用于寫入大文件的內(nèi)存優(yōu)化模式。
官方文檔 https://xlsxwriter.readthedocs.io/
安裝
pip3 install XlsxWriter
基本使用示例
import xlsxwriter
# 創(chuàng)建一個新的 Excel 文件，并添加一個工作表
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
# 設(shè)置第一列(A) 單元格寬度為 20
worksheet.set_column('A:A', 20)
# 定義一個加粗的格式對象
bold = workbook.add_format({'bold': True})
# 在 A1 單元格處寫入字符串 'Hello'
worksheet.write('A1', 'Hello')
# 在 A2 單元格處寫入中文字符串，并加粗字體
worksheet.write('A2', '千鋒教育', bold)
# 利用 行和列的索引號方式，寫入數(shù)字，索引號是從 0 開始的
worksheet.write(2, 0, 100) # 3 行 1列
worksheet.write(3, 0, 35.8)
# 計算 A3 到 A4 的結(jié)果
worksheet.write(4, 0, '=SUM(A3:A4)')
# 在 B5 單元格處插入一個圖片
worksheet.insert_image('B5', 'logo.png')
# 關(guān)閉 Excel 文件
workbook.close()
完成圖
image.png
方法解析
行和列的表示方法
XlsxWriter支持兩種表示法來指定單元格的位置： 行列符和A1符號。
行列符號 表示法是 對行和列都使用基于零的索引號表示。
而A1符號表示法是 使用標(biāo)準(zhǔn)Excel中的字母表示列，基于1的數(shù)字表示行。例如：
# 下面兩個都表示為 第一行的第一列
(0, 0)
('A1')
# 下面兩個都表示為 第七行的第三列
(6, 2)
('C7')
如果您以編程方式引用單元格，則行列符號表示法很有用：
for row in range(0, 5):
worksheet.write(row, 0, 'Hello')
如何表示行列范圍
XlsxWriter支持Excel的工作表限制1,048,576行16,384列。
# 用 A1 符號表示法
worksheet.print_area('A1:XFD1') # Same as 1:1
worksheet.print_area('A1:A1048576') # Same as A:A
這些范圍也可以使用行列符號指定：
worksheet.print_area(0, 0, 0, 16383) # Same as 1:1
worksheet.print_area(0, 0, 1048575, 0) # Same as A:A
Workbook 類
用于創(chuàng)建一個新的 Excel 文件
workbook = xlsxwriter.Workbook('demo.xlsx')
注意： 和文件的打開模式 w 一樣，會把原來的內(nèi)容清空。所以只能新建文件。
他會返回一個 Workbook 的實例對象，代表了整個 Excel 文件，并且會把這個文件存儲在磁盤上。
最后要記得關(guān)閉這個文件
workbook.close()
當(dāng)然建議使用 with 管理上下文，這樣就可以不用顯示的調(diào)用 workboo.close()
with xlsxwriter.Workbook('hello_world.xlsx') as workbook:
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
我們通過這個實例的方法來操作 Excel 文件，具體有以下方法：
-
add_worksheet([sheetname]) 方法
用于創(chuàng)建一個新的工作表
sheet_shee1 = workbook.add_worksheet() # 工作表名: Shee1
sheet_qf01 = workbook.add_worksheet('qf01') # 工作表名: qf01
sheet_shee3 = workbook.add_worksheet() # 工作表名: Shee3
它會返回一個表的對象，可以通過這個對象對表里的內(nèi)容和樣式來進(jìn)行具體的操作。
-
add_format([properties]) 方法
用于創(chuàng)建一個格式化對象，使用這個對象可以對任意一個單元格進(jìn)行格式化
properties是一個字典類型的參數(shù)，里邊定義具體的格式
字體加粗格式
bold = workbook.add_format({'bold': True})
日期格式
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
示例演練
假設(shè)有如下數(shù)據(jù)主機 IP 信息，需要轉(zhuǎn)化成 Excel 文件來展示
import xlsxwriter
host_ip = (
["server1",'192.168.1.101','2018-06-11'],
["server2",'192.168.1.102','2018-06-11'],
["server3",'192.168.1.103','2018-06-11'],
["server4",'192.168.1.104','2018-06-11']
)
示例代碼
import xlsxwriter
host_ip = (
["server1",'192.168.1.101','2018-06-11'],
["server2",'192.168.1.102','2018-06-11'],
["server3",'192.168.1.103','2018-06-11'],
["server4",'192.168.1.104','2018-06-11']
)
# 創(chuàng)建一個新的文件
with xlsxwriter.Workbook('host_ip.xlsx') as workbook:
# 添加一個工作表
worksheet = workbook.add_worksheet('ip信息')
# 設(shè)置一個加粗的格式
bold = workbook.add_format({"bold": True})
# 設(shè)置一個日期的格式
date_format = workbook.add_format(
{'num_format': 'yyyy-mm-dd'})
# 分別設(shè)置一下 A 和 B 列的寬度
worksheet.set_column('A:A', 10)
worksheet.set_column('B:B', 15)
worksheet.set_column('C:C', 18)
# 先把表格的抬頭寫上，并設(shè)置字體加粗
worksheet.write('A1', '主機名', bold)
worksheet.write('B1', 'IP 地址', bold)
worksheet.write(0,2, '統(tǒng)計日期', bold)
# 設(shè)置數(shù)據(jù)寫入文件的初始行和列的索引位置
row = 1
col = 0
# 迭代數(shù)據(jù)并逐行寫入文件
for name, ip,date in (host_ip):
worksheet.write(row, col, name)
worksheet.write(row, col + 1, ip)
worksheet.write(row, col + 2, date, date_format)
row += 1
示例文檔的樣子
image.png
openpyxl 讀寫
安裝
pip3 install openpyxl
基本使用
在內(nèi)存中創(chuàng)建一個新文檔
>>> from openpyxl import Workbook
>>> wb = Workbook()
一個新文檔中必須至少有一個工作表
創(chuàng)建一個新工作表
>>> ws = wb.active
# 或者
>>> ws1 = wb.create_sheet() # 默認(rèn)在結(jié)尾創(chuàng)建一個工作表
>>> ws2 = wb.create_sheet("date1", 0) # 指定在開頭創(chuàng)建一個工作表
在創(chuàng)建表格時，假如不指定名稱，則表格會自動命名。(Sheet1,Sheet2)
工作表的名稱可以更改
ws.title = 'New Title'
可以在一個文件中對某一個工作表進(jìn)行復(fù)制操作
qf_copy = wb.copy_worksheet(ws2)
qf_copy.title = 'date1_copy'
注意:
只有單元格（包括值，樣式，超鏈接和注釋）和某些工作表屬性（包括尺寸，格式和屬性）被復(fù)制。所有其他工作簿/工作表屬性不會被復(fù)制 - 例如圖像，圖表。
查看當(dāng)前文件中所有的工作表對象
print(wb.worksheets)
# 輸出結(jié)果:
[<Worksheet "date1">, <Worksheet "New Title">, <Worksheet "Sheet1">, <Worksheet "date1_copy">]
當(dāng)然也可以循環(huán)這些工作表名稱
for sheet in wb:
print(sheet.title)
# 輸出結(jié)果
date1
New Title
Sheet1
訪問單元格
當(dāng)工作表在內(nèi)存中創(chuàng)建時，它不包含單元格。它們在第一次訪問時創(chuàng)建。
單元格可以作為工作表的鍵直接訪問
# 存在則獲取值，不存在則創(chuàng)建
>>> c= ws['A4']
# 賦值操作，不存在則直接創(chuàng)建
>>> ws['A4'] = 4
這使用行和列表示法提供對單元格的訪問權(quán)限：
>>> d = ws.cell(row=4, column=2, value=10)
警告
由于這個特性，即使沒有為它們賦值，通過滾動單元格而不是直接訪問它們也會在內(nèi)存中創(chuàng)建它們。
就像是
>>> for i in range(1,101):
for j in range(1,101):
ws.cell(row=i, column=j)
將在內(nèi)存中創(chuàng)建100x100的單元格，無需任何操作。
單元格的范圍操作
同樣支持 A1 表示法的切片操作
>>> cell_range = ws['A1':'C2']
還可以用以下方法獲取
ws[1:4] # 獲取到第一列的第一行到第四行
循環(huán)單元格以行為單位返回
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
print(row) # 每一行
for cell in row:
print(cell) # 每一行中的每一列
循環(huán)單元格以列為單位返回
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell)
給單元格賦值
從以上知識點中我們得到了具體的單元格對象，此時我們就可對他們進(jìn)行賦值的操作了
# 可以這樣賦值
ws.cell(row=1, column=2, value='sharkyun') # 對第一行的第二列進(jìn)行賦值
# 還可以這樣
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
cell.value = 'sharkyun'
獲取單元格的值
# 方式一 指定獲取第 4 行的第 3 列
cell_val = ws.cell(row=4,column=3).value
print(cell_val)
# 方式二 循環(huán)得到每個單元格的值
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell.value)
設(shè)置單元格的字體樣式
from openpyxl.styles import Font
from openpyxl.styles import colors
# 設(shè)置字體樣式
ft = Font(bold=True, # 加粗
size=20, # 大小
color=colors.RED, # 顏色
# color='FFBB00' # 顏色
)
# 使用字體樣式到單元格
ws["A1"].font = ft
ws["B1"].font = ft
ws["C1"].font = ft
設(shè)置列寬
ws.column_dimensions['A'].width = 18
迭代所有的行和列
迭代所有的行
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 假如循環(huán)的是新創(chuàng)建的工作表， 那么初始時工作表中是沒有單元格的，
# 所以需要先在工作表中創(chuàng)建出一些單元格
ws['C4'] = "Hello"
# ws.rows 是一個生成器對象，可以迭代它，每次迭代會返回工作表中的一行
for row in ws.rows:
print(row)
# 以下是輸出結(jié)果
(<Cell 'Sheet3'.A1>, <Cell 'Sheet3'.B1>, <Cell 'Sheet3'.C1>)
(<Cell 'Sheet3'.A2>, <Cell 'Sheet3'.B2>, <Cell 'Sheet3'.C2>)
(<Cell 'Sheet3'.A3>, <Cell 'Sheet3'.B3>, <Cell 'Sheet3'.C3>)
(<Cell 'Sheet3'.A4>, <Cell 'Sheet3'.B4>, <Cell 'Sheet3'.C4>)
迭代所有的列
此示例的數(shù)據(jù)對象，沿用了上面的示例
for col in ws.columns:
print(col)
# 以下是輸出結(jié)果
(<Cell 'Sheet3'.A1>, <Cell 'Sheet3'.A2>, <Cell 'Sheet3'.A3>, <Cell 'Sheet3'.A4>)
(<Cell 'Sheet3'.B1>, <Cell 'Sheet3'.B2>, <Cell 'Sheet3'.B3>, <Cell 'Sheet3'.B4>)
(<Cell 'Sheet3'.C1>, <Cell 'Sheet3'.C2>, <Cell 'Sheet3'.C3>, <Cell 'Sheet3'.C4>)
保存數(shù)據(jù)
要把內(nèi)存的數(shù)據(jù)保存到硬盤中，使用 wb.save() 方法即可
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws["A1"] = "hello"
wb.save("one.xlsx")
# 注意文件的后綴名 *.xlsx
示例演練
假設(shè)有如下數(shù)據(jù)主機 IP 信息，需要轉(zhuǎn)化成 Excel 文件來展示
import xlsxwriter
host_ip = (
["server1",'192.168.1.101','2018-06-11'],
["server2",'192.168.1.102','2018-06-11'],
["server3",'192.168.1.103','2018-06-11'],
["server4",'192.168.1.104','2018-06-11']
)
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import colors
# 定制一個字體樣式對象
ft = Font(bold=True, size=20,
color=colors.RED,
# color='FFBB00'
)
host_ip = (
["server1", '192.168.1.101', '2018-06-11'],
["server2", '192.168.1.102', '2018-06-11'],
["server3", '192.168.1.103', '2018-06-11'],
["server4", '192.168.1.104', '2018-06-11']
)
# 創(chuàng)建 Excel 文件對象
wb = Workbook()
ws1 = wb.active
# 更改工作表標(biāo)簽的背景色,值是RRGGBB顏色代碼
# http://www.sioe.cn/yingyong/yanse-rgb-16/
ws1.sheet_properties.tabColor = "DC143C"
# 設(shè)置標(biāo)題的內(nèi)容和字體樣式
ws1.cell(row=1,column=1,value="主機名").font = ft
ws1.cell(row=1,column=2,value="IP 地址").font = ft
ws1.cell(row=1,column=3,value="統(tǒng)計時間").font = ft
# 設(shè)置列寬
ws1.column_dimensions['A'].width = 16
ws1.column_dimensions['B'].width = 22
ws1.column_dimensions['C'].width = 22
# 獲取到所有的行以及每行的所有列
rows = ws1.iter_rows(min_row=2, max_col=len(host_ip[0]),max_row=len(host_ip))
for row,items in zip(rows, host_ip):
for cell, item in zip(row, items):
cell.value = item
cell.font = Font(size=18)
# print(cell.value, item)
# 定義文件名
dest_filename = 'empty_book.xlsx'
# 保存文件到硬盤
wb.save(filename = dest_filename)
讀取一個已存在的 Excel 文件
from openpyxl import load_workbook
# 獲取文件對象
wb2 = load_workbook('empty_book.xlsx')
# 查看文件中所有的工作表名
wb2.get_sheet_names()
# 通過工作表名獲取到工作表對象
ws = wb2.get_sheet_by_name('Sheet')
# 同樣可以安裝上面提到的方法訪問這個工作表中的行和列
# 比如循環(huán)每一行
for row in ws.rows:
print(row)
