pip install XlsxWriter或者下載最新版本的，然后安裝
tar -zxvf XlsxWriter-1.2.3.tar.gzcd XlsxWriter-1.2.3python setup.py install# xlsxwriter只可以新建一個(gè)excel，不可以讀取和更新# 創(chuàng )建一個(gè)workbook 和增加一個(gè)worksheet，默認為sheet1...，也可以直接為sheet命名，例如下邊的testworkbook = xlsxwriter.Workbook('data.xlsx')# 添加 sheetworksheet = workbook.add_worksheet() # 默認為 Sheet1.worksheet1 = workbook.add_worksheet("test") # testworksheet2 = workbook.add_worksheet() # 默認為 Sheet3.# 測試數據expenses = ( ['Rent', 1000], ['Gas', 100], ['Food', 300], ['Gym', 50],)# 從首行、首列開(kāi)始.row = 0col = 0# 通過(guò)迭代寫(xiě)入數據.for item, cost in (expenses): worksheet.write(row, col, item) worksheet.write(row, col + 1, cost) row += 1# 使用公式，例如B列的和.worksheet.write(row, 0, 'Total')worksheet.write(row, 1, '=SUM(B1:B4)')# 只有此函數才可以生成excelworkbook.close()
顏色查看：Working with Colors
詳情查看：The Format Class，Workbook Class，Worksheet Class
from datetime import datetimeimport xlsxwriterworkbook = xlsxwriter.Workbook('Expenses03.xlsx')worksheet = workbook.add_worksheet()# 定義一個(gè)紅色+黑體的格式.bold = workbook.add_format({'bold': 1, "color": "red"})# 設置錢(qián)數的格式.money_format = workbook.add_format({'num_format': '$#,##0'})# 設置時(shí)間格式.date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})# 設置列、行寬.worksheet.set_column(1, 1, 15)# worksheet.set_row(1, 1, 15)# 定義表頭.worksheet.write('A1', 'Item', bold)worksheet.write('B1', 'Date', bold)worksheet.write('C1', 'Cost', bold)# 測試數據.expenses = ( ['Rent', '2013-01-13', 1000], ['Gas', '2013-01-14', 100], ['Food', '2013-01-16', 300], ['Gym', '2013-01-20', 50],)# 初始化行列.row = 1col = 0for item, date_str, cost in (expenses): # 轉換時(shí)間格式. date = datetime.strptime(date_str, "%Y-%m-%d") worksheet.write_string(row, col, item) worksheet.write_datetime(row, col + 1, date, date_format) worksheet.write_number(row, col + 2, cost, money_format) row += 1# 使用公式C2到C5的和.worksheet.write(row, 0, 'Total', bold)worksheet.write(row, 2, '=SUM(C2:C5)', money_format)workbook.close()
詳情查看：Working with Charts，The Chart Class，The Chartsheet Class
import xlsxwriterworkbook = xlsxwriter.Workbook('chart.xlsx')worksheet = workbook.add_worksheet()# 創(chuàng )建一個(gè)類(lèi)型為column的圖表對象.chart = workbook.add_chart({'type': 'column'})# Write some data to add to plot on the chart.data = [ [1, 2, 3, 4, 5], [2, 4, 6, 8, 10], [3, 6, 9, 12, 15],]worksheet.write_column('A1', data[0])worksheet.write_column('B1', data[1])worksheet.write_column('C1', data[2])# 配置chart. 添加數據 series.chart.add_series({'values': '=Sheet1!$A$1:$A$5'})# chart.add_series({'values': '=Sheet1!$B$1:$B$5'})# 或者使用一個(gè)list添加series（下邊），[sheetname, first_row, first_col, last_row, last_col]chart.add_series({'values': ['Sheet1', 0, 1, 4, 1], 'line': {'color': 'red'}})chart.add_series({'values': '=Sheet1!$C$1:$C$5'})# 添加標題（圖表上邊）chart.set_title({ 'name': 'Title',})# 添加x軸標題chart.set_x_axis({ 'name': 'X axis',})# 添加y軸標題chart.set_y_axis({ 'name': 'y axis',})# 圖表下邊插入數據表chart.set_table()# 將表插入到worksheet.worksheet.insert_chart('A7', chart)workbook.close()chart的類(lèi)型：
也可以添加子類(lèi)型，支持的子類(lèi)型如下：
workbook.add_chart({'type': 'bar', 'subtype': 'stacked'})area（stacked， percent_stacked）
bar（ stacked，percent_stacked）
column（ stacked，percent_stacked）
scatter（straight_with_markers， straight， smooth_with_markers，smooth）
radar（with_markers，filled）
worksheet.write_formula('A1', '=10*B1 + C1')
詳情參考：Working with Autofilters，example
import xlsxwriterdata = [["Region", "Item", "Volume", "Month"], ["East", "Apple", 5000, "july"], ["west", "Grape", 7000, "December"], ["East", "orange", 4000, "October"], ["East", "Grape", 7000, "October"], ["East", "Apple", 4000, "April"], ["East", "Grape", 6000, "February"], ["South", "orange", 3000, "November"], ["West", "Grape", 2000, "October"], ["East", "Apple", 4000, "April"], ["North", "Grape", 6000, "February"], ]workbook = xlsxwriter.Workbook('autofilter.xlsx')# 添加不同的sheet.worksheet1 = workbook.add_worksheet()worksheet2 = workbook.add_worksheet()worksheet3 = workbook.add_worksheet()worksheet4 = workbook.add_worksheet()# header格式.bold = workbook.add_format({'bold': 1})headers = data[0]# 添加表頭，為每個(gè)sheet.for worksheet in (workbook.worksheets()): # 列寬. worksheet.set_column('A:D', 12) # 行高. worksheet.set_row(0, 20, bold) worksheet.write_row('A1', headers)worksheet1.autofilter('A1:D51')# worksheet1.autofilter(0, 0, 10, 3) # 和上邊的作用一樣，不同寫(xiě)法.row = 1for row_data in (data[1:]): worksheet1.write_row(row, 0, row_data) row += 1 # ************************sheet2******************************worksheet2.autofilter(0, 0, 50, 3)# 添加過(guò)濾，Region等于East的worksheet2.filter_column(0, 'Region == East')# 隱藏其余的.row = 1for row_data in (data[1:]): region = row_data[0] if region == 'East': pass else: worksheet2.set_row(row, options={'hidden': True}) worksheet2.write_row(row, 0, row_data) row += 1# **********************sheet3***********************worksheet3.autofilter('A1:D51')worksheet3.filter_column('A', 'x == East or x == South')row = 1for row_data in (data[1:]): region = row_data[0] if region == 'East' or region == 'South': pass else: worksheet3.set_row(row, options={'hidden': True}) worksheet3.write_row(row, 0, row_data) row += 1 # ********************sheet4 ***********************worksheet4.autofilter('A1:D51')worksheet4.filter_column('A', 'x == East')worksheet4.filter_column('C', 'x > 3000 and x < 8000')row = 1for row_data in (data[1:]): region = row_data[0] volume = int(row_data[2]) if region == 'East' and volume > 3000 and volume < 8000: pass else: worksheet4.set_row(row, options={'hidden': True}) worksheet4.write_row(row, 0, row_data) row += 1workbook.close()
詳情參考：Working with Data Validation
詳情參考：Working with Conditional Formatting
import xlsxwriterdata = [["Region", "Item", "Volume", "Month"], ["East", "Apple", 5000, "july"], ["west", "Grape", 7000, "December"], ["East", "orange", 4000, "October"], ["East", "Grape", 7000, "October"], ["East", "Apple", 4000, "April"], ["East", "Grape", 6000, "February"], ["South", "orange", 3000, "November"], ["West", "Grape", 2000, "October"], ["East", "Apple", 4000, "April"], ["North", "Grape", 6000, "February"], ]workbook = xlsxwriter.Workbook('autofilter.xlsx')# 添加不同的sheet.worksheet1 = workbook.add_worksheet()# header格式.bold = workbook.add_format({'bold': 1})headers = data[0]# 列寬.worksheet1.set_column('A:D', 12)# 行高.worksheet1.set_row(0, 20, bold)worksheet1.write_row('A1', headers)worksheet1.autofilter('A1:D51')# worksheet1.autofilter(0, 0, 10, 3) # 和上邊的作用一樣，不同寫(xiě)法.row = 1for row_data in (data[1:]): worksheet1.write_row(row, 0, row_data) row += 1format1 = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})format2 = workbook.add_format({'bg_color': '#FFEB9C', 'font_color': '#9C6500'})worksheet1.conditional_format('C2:C10', {'type': 'cell', 'criteria': '>=', 'value': 5000, 'format': format1})worksheet1.conditional_format('C2:C10', {'type': 'cell', 'criteria': '<=', 'value': 3000, 'format': format2})workbook.close()詳情參考：Working with Worksheet Tables
當處于’constant_memory’ 模式時(shí)，Table不可用
import xlsxwriterdata = [ ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700]]workbook = xlsxwriter.Workbook('tables.xlsx')worksheet = workbook.add_worksheet()worksheet1 = workbook.add_worksheet()# sheet1worksheet.set_column('B:G', 12)formula = r'=SUM(Table1[@[Quarter 1]:[Quarter 4]])'worksheet.add_table('B3:G7', {'data': data, 'columns': [{'header': 'Product'}, {'header': 'Quarter 1'}, {'header': 'Quarter 2'}, {'header': 'Quarter 3'}, {'header': 'Quarter 4'}, {'header': 'Year', 'formula': formula}, ]})# sheet 2options = {'data': data, 'total_row': 1, 'columns': [{'header': 'Product', 'total_string': 'Totals'}, {'header': 'Quarter 1', 'total_function': 'sum'}, {'header': 'Quarter 2', 'total_function': 'sum'}, {'header': 'Quarter 3', 'total_function': 'sum'}, {'header': 'Quarter 4', 'total_function': 'sum'}, {'header': 'Year', 'formula': '=SUM(Table2[@[Quarter 1]:[Quarter 4]])', 'total_function': 'sum' }, ]}# Add a table to the worksheet.worksheet1.add_table('B3:G8', options)workbook.close()詳情參考：Working with Python Pandas and XlsxWriter
import pandas as pd# Create a Pandas dataframe from the data.df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})# Create a Pandas Excel writer using XlsxWriter as the engine.writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')# Convert the dataframe to an XlsxWriter Excel object.df.to_excel(writer, sheet_name='Sheet1')# Close the Pandas Excel writer and output the Excel file.writer.save()詳情參考：Working with Memory and Performance
聯(lián)系客服
