1.安裝
pip install xlsxwriter oreasy_install xlsxwriter ortar -zxvf xlsxwriter-*.*.*.tar.gzpython setup.py install
2.操作一個(gè)簡單的Excel文檔
操作注釋及代碼：
# 引入依賴模塊import xlsxwriter# 數(shù)據(jù)準(zhǔn)備datas = ( ['Rent', 1000], ['Gas', 100 ], ['Food', 300 ], ['Gym', 50 ],)# 創(chuàng)建一個(gè)Excel文檔workbook = xlsxwriter.Workbook('ex01.xlsx')# 添加一個(gè)工作表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# 添加一個(gè)計(jì)算總數(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# 添加一列求和計(jì)數(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 datetimeimport 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'})# 添加日期格式newdate_f = workbook.add_format({'num_format': 'mmmm d yyyy'})# 添加對齊方式和表格寬度newworksheet.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# 添加計(jì)數(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ù)：一次添加多個(gè)數(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
無聊寫生姑且枉談，點(diǎn)亂混敲其說難圓，隨筆胡涂烏月晴空，一走一留其上難下。
