1.安裝
pip install xlsxwriter
or
easy_install xlsxwriter
or
tar -zxvf xlsxwriter-*.*.*.tar.gz
python setup.py install
2.操作一個簡單的Excel文檔
操作注釋及代碼：
# 引入依賴模塊
import xlsxwriter
# 數(shù)據(jù)準(zhǔn)備
datas = (
['Rent', 1000],
['Gas', 100 ],
['Food', 300 ],
['Gym', 50 ],
)
# 創(chuàng)建一個Excel文檔
workbook = xlsxwriter.Workbook('ex01.xlsx')
# 添加一個工作表
worksheet = workbook.add_worksheet()
# 設(shè)置行和列的偏移
row, col = 0, 0
# 開始添加數(shù)據(jù)
for item, cost in datas:
# 指定行、列的單元格，添加數(shù)據(jù)
worksheet.write(row, col, item)
worksheet.write(row, col+1, cost)
# 行增加
row += 1
# 添加一個計算總數(shù)的函數(shù)
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')
# 關(guān)閉文檔
workbook.close()
操作完成后，數(shù)據(jù)存儲結(jié)果如下：
image.png
3. 操作簡單Excel文檔并添加數(shù)據(jù)格式
操作代碼如下：附帶數(shù)據(jù)格式的定義
# 導(dǎo)入依賴的模塊
import xlsxwriter
# 數(shù)據(jù)準(zhǔn)備
datas = (
['Rent', 1000],
['Gas', 100 ],
['Food', 300 ],
['Gym', 50 ],
)
# 創(chuàng)建表格
workbook = xlsxwriter.Workbook('ex02.xlsx')
worksheet = workbook.add_worksheet('data')
# 添加全局格式
bold = workbook.add_format({'bold': True})
# 添加money格式
money = workbook.add_format({'num_format': '$#,##0'})
# 添加表格頭，附帶格式
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)
# 數(shù)據(jù)表格偏移
row, col = 1, 0
# 添加數(shù)據(jù)
for item, cost in datas:
# 按照行列單元格添加數(shù)據(jù)
worksheet.write(row, col, item)
worksheet.write(row, col+1, cost)
row += 1
# 添加一列求和計數(shù)
worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 1, '=SUM(B2:B5)', money)
# 存儲退出
workbook.close()
操作效果如圖所示：
image.png
4.Excel中添加不同類型的數(shù)據(jù)
操作代碼如下：將不同的數(shù)據(jù)按照指定的格式添加到文件中
# 引入依賴的模塊
from datetime import datetime
import xlsxwriter
# 數(shù)據(jù)準(zhǔn)備
datas = (
['Rent', '2018-05-13', 1000],
['Gas', '2018-05-14', 100],
['Food', '2018-05-16', 300],
['Gym', '2018-05-20', 50],
)
# 創(chuàng)建表格
workbook = xlsxwriter.Workbook('ex03.xlsx')
worksheet = workbook.add_worksheet('data')
# 添加格式
bold_f = workbook.add_format({'bold': True})
money_f = workbook.add_format({'num_format': '$#,##0'})
# 添加日期格式new
date_f = workbook.add_format({'num_format': 'mmmm d yyyy'})
# 添加對齊方式和表格寬度new
worksheet.set_column(1, 1, 15)
# 添加表頭數(shù)據(jù)
worksheet.write('A1', 'Item', bold_f)
worksheet.write('B1', 'Date', bold_f)
worksheet.write('C1', 'Cost', bold_f)
# 定義偏移值
row, col = 1, 0
# 添加數(shù)據(jù)
for item, date, cost in datas:
date = datetime.strptime(date, '%Y-%m-%d')
worksheet.write(row, col, item)
worksheet.write_datetime(row, col+1, date, date_f)
worksheet.write(row, col+2, cost, money_f)
row += 1
# 添加計數(shù)
worksheet.write(row, 0, 'Total', bold_f)
worksheet.write(row, 2, '=SUM(C2:C5)', money_f)
# 存儲并關(guān)閉
workbook.close()
代碼執(zhí)行結(jié)果如下：
image.png
注意：xlsxwriter中提供了向excel中寫入數(shù)據(jù)的多種方式，如下：
- write_string()
- write_number()
- write_blank()
- write_formula()
- write_datetime()
- write_boolean()
- write_url()
5.Excel中添加數(shù)據(jù)圖表
操作代碼如下：將對應(yīng)的數(shù)據(jù)展示圖表添加到文件中
# 引入依賴模塊
import xlsxwriter
# 操作數(shù)據(jù)
data = [20, 45, 26, 18, 45]
# 創(chuàng)建表格
workbook = xlsxwriter.Workbook("ex04.xlsx")
worksheet = workbook.add_worksheet("data")
# 添加數(shù)據(jù)：一次添加多個數(shù)據(jù)
worksheet.write_column('A1', data)
# 創(chuàng)建圖表
chart = workbook.add_chart({'type': 'line'})
# 圖表添加數(shù)據(jù)
chart.add_series({
'values': '=data!$A1:$A6',
'name': '圖表線名稱',
'marker': {
'type': 'circle',
'size': 8,
'border': {'color': 'black'},
'fill': {'color': 'red'}
},
'data_labels': {'value': True},
'trendline': {
'type': 'polynomial',
'order': 2,
'name': '示例趨勢線',
'forward': 0.5,
'backward': 0.5,
'display_equation': True,
'line': {'color': 'red', 'width': 1, 'dash_type': 'long_dash'}
}
})
worksheet.insert_chart('C1', chart)
workbook.close()
代碼執(zhí)行結(jié)果如下圖所示：
image.png
