pip install XlsxWriter或者下载最新版本的，然后安装
tar -zxvf XlsxWriter-1.2.3.tar.gzcd XlsxWriter-1.2.3python setup.py install# xlsxwriter只可以新建一个excel，不可以读取和更新# 创建一个workbook 和增加一个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],)# 从首行、首列开始.row = 0col = 0# 通过迭代写入数据.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()# 定义一个红色+黑体的格式.bold = workbook.add_format({'bold': 1, "color": "red"})# 设置钱数的格式.money_format = workbook.add_format({'num_format': '$#,##0'})# 设置时间格式.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): # 转换时间格式. 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()# 创建一个类型为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'})# 或者使用一个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的类型：
也可以添加子类型，支持的子类型如下：
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]# 添加表头，为每个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) # 和上边的作用一样，不同写法.row = 1for row_data in (data[1:]): worksheet1.write_row(row, 0, row_data) row += 1 # ************************sheet2******************************worksheet2.autofilter(0, 0, 50, 3)# 添加过滤，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) # 和上边的作用一样，不同写法.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’ 模式时，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
联系客服
