Python XlsxWriter 快速指南

---

---

Python XlsxWriter - 概述

XlsxWriter 是一个 Python 模块，用于创建 Excel 2007 (XLSX) 格式的电子表格文件，它使用开放 XML 标准。XlsxWriter 模块由 John McNamara 开发。其最早版本 (0.0.1) 于 2013 年发布。最新版本 3.0.2 于 2021 年 11 月发布。最新版本需要 Python 3.4 或更高版本。

XlsxWriter 功能

XlsxWriter 的一些重要功能包括：

• XlsxWriter 创建的文件与 Excel XLSX 文件 100% 兼容。

• XlsxWriter 提供完整的格式化功能，例如合并单元格、定义名称、条件格式等。

• XlsxWriter 允许以编程方式在 XLSX 文件中插入图表。

• 可以使用 XlsxWriter 设置自动筛选器。

• XlsxWriter 支持数据验证和下拉列表。

• 使用 XlsxWriter，可以插入 PNG/JPEG/GIF/BMP/WMF/EMF 图片。

• 使用 XlsxWriter，可以将 Excel 电子表格与 Pandas 库集成。

• XlsxWriter 还支持添加宏。

• XlsxWriter 具有内存优化模式，用于写入大型文件。

Python XlsxWriter - 环境设置

使用 PIP 安装 XlsxWriter

安装 XlsxWriter 最简单且推荐的方法是使用 PIP 安装程序。使用以下命令安装 XlsxWriter（最好在虚拟环境中）。

pip3 install xlsxwriter

从 Tarball 安装

另一种选择是从其源代码安装 XlsxWriter，该源代码托管在 https://github.com/jmcnamara/XlsxWriter/。下载最新的源代码 tarball 并使用以下命令安装库：

$ curl -O -L http://github.com/jmcnamara/XlsxWriter/archive/main.tar.gz

$ tar zxvf main.tar.gz
$ cd XlsxWriter-main/
$ python setup.py install

从 GitHub 克隆

您也可以克隆 GitHub 存储库并从中安装。

$ git clone https://github.com/jmcnamara/XlsxWriter.git

$ cd XlsxWriter
$ python setup.py install

要确认 XlsxWriter 是否已正确安装，请从 Python 提示符检查其版本：

>>> import xlsxwriter
>>> xlsxwriter.__version__
'3.0.2'

Python XlsxWriter - Hello World

入门

测试模块/库是否正常工作的第一个程序通常是写入 Hello world 消息。以下程序创建一个具有 .XLSX 扩展名的文件。xlsxwriter 模块中的 Workbook 类对象对应于当前工作目录中的电子表格文件。

wb = xlsxwriter.Workbook('hello.xlsx')

接下来，调用 Workbook 对象的 add_worksheet() 方法在其内插入一个新的工作表。

ws = wb.add_worksheet()

我们现在可以通过调用工作表对象的 write() 方法在 A1 单元格添加 Hello World 字符串。它需要两个参数：单元格地址和字符串。

ws.write('A1', 'Hello world')

示例

hello.py 的完整代码如下：

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.write('A1', 'Hello world')
wb.close()

输出

执行上述代码后，将在当前工作目录中创建 hello.xlsx 文件。您现在可以使用 Excel 软件打开它。

Python XlsxWriter - 重要类

XlsxWriter 库包含以下类。这些类中定义的所有方法都允许对 XLSX 文件进行不同的编程操作。这些类是：

• Workbook 类
• Worksheet 类
• Format 类
• Chart 类
• Chartsheet 类
• Exception 类

Workbook 类

这是 XlsxWriter 模块公开的主要类，也是您唯一需要直接实例化的类。它表示写入磁盘上的 Excel 文件。

wb=xlsxwriter.Workbook('filename.xlsx')

Workbook 类定义了以下方法：

Worksheet 类

Worksheet 类表示 Excel 工作表。此类的对象处理诸如将数据写入单元格或设置工作表布局之类的操作。它通过从 Workbook() 对象调用 add_worksheet() 方法来创建。

Worksheet 对象可以访问以下方法：

Format 类

Format 对象是通过调用工作簿 add_format() 方法创建的。此对象可用的方法和属性与字体、颜色、图案、边框、对齐和数字格式有关。

字体格式方法和属性：

对齐格式方法和属性

Chart 类

图表对象是通过 Workbook 对象的 add_chart() 方法创建的，其中指定了图表类型。

chart = workbook.add_chart({'type': 'column'})

通过调用 insert_chart() 方法将 chart 对象插入工作表。

worksheet.insert_chart('A7', chart)

XlxsWriter 支持以下图表类型：

• area - 创建面积图（填充线样式）。

• bar - 创建条形图（转置直方图）。

• column - 创建柱状图（直方图）。

• line - 创建折线图。

• pie - 创建饼图。

• doughnut - 创建环形图。

• scatter - 创建散点图。

• stock - 创建股票图。

• radar − 创建雷达图。

Chart 类定义了以下方法：

图表工作表类

XLSX 文件中的图表工作表是一个仅包含图表而不包含其他数据的worksheet。通过从 Workbook 对象调用add_chartsheet() 方法来创建一个新的chartsheet 对象：

chartsheet = workbook.add_chartsheet()

Chartsheet 类的一些功能与数据工作表类似，例如选项卡选择、页眉、页脚、页边距和打印属性。但是，它的主要目的是显示单个图表，而普通数据工作表可以包含一个或多个嵌入式图表。

chartsheet 图表的数据必须存在于单独的工作表中。因此，它总是与至少一个数据工作表一起创建，使用set_chart() 方法。

chartsheet = workbook.add_chartsheet()
chart = workbook.add_chart({'type': 'column'})
chartsheet.set_chart(chart)

请记住，图表工作表只能包含一个图表。

示例

以下代码将数据系列写入名为 sheet1 的工作表中，但会打开一个新的图表工作表以根据 sheet1 中的数据添加柱状图。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()

cs = wb.add_chartsheet()
chart = wb.add_chart({'type': 'column'})

data = [
   [10, 20, 30, 40, 50],
   [20, 40, 60, 80, 100],
   [30, 60, 90, 120, 150],
]
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
worksheet.write_column('C1', data[2]) 

chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({'values': '=Sheet1!$C$1:$C$5'})

cs.set_chart(chart)
cs.activate()

wb.close()

输出

异常类

XlsxWriter 识别各种运行时错误或异常，可以使用 Python 的错误处理技术来捕获这些错误，以避免 Excel 文件损坏。XlsxWriter 中的异常类如下：

异常 FileCreateError

假设一个名为hello.xlsx的工作簿已经使用 Excel 应用程序打开，则以下代码将引发FileCreateError：

import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
workbook.close()

运行此程序时，错误消息将显示如下：

PermissionError: [Errno 13] Permission denied: 'hello.xlsx'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
   File "hello.py", line 4, in <module>
   workbook.close()
File "e:\xlsxenv\lib\site-packages\xlsxwriter\workbook.py", line 326, in close
   raise FileCreateError(e)
xlsxwriter.exceptions.FileCreateError: [Errno 13] Permission denied: 'hello.xlsx'

处理异常

为此，我们可以使用 Python 的异常处理机制。

import xlsxwriter
try:
   workbook = xlsxwriter.Workbook('hello.xlsx')
   worksheet = workbook.add_worksheet()
   workbook.close()
except:
   print ("The file is already open")

现在将显示自定义错误消息。

(xlsxenv) E:\xlsxenv>python ex34.py
The file is already open

异常 EmptyChartSeries

添加带有数据系列的图表时引发的另一个异常情况。

import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
chart = workbook.add_chart({'type': 'column'})
worksheet.insert_chart('A7', chart)
workbook.close()

这将导致 EmptyChartSeries 异常：

xlsxwriter.exceptions.EmptyChartSeries: Chart1 must contain at least one data series.

Python XlsxWriter - 单元格表示法和范围

工作簿中的每个工作表都是一个包含大量单元格的网格，每个单元格可以存储一个数据——值或公式。网格中的每个单元格都由其行号和列号标识。

在 Excel 的标准单元格寻址中，列由字母 A、B、C……Z、AA、AB 等标识，行从 1 开始编号。

每个单元格的地址都是字母数字的，其中字母部分对应于列，数字部分对应于行。例如，地址“C5”指向“C”列和“5”行中的单元格。

单元格表示法

标准 Excel 使用列字母和基于 1 的行的字母数字序列。XlsxWriter 支持标准 Excel 表示法 (A1 表示法) 以及行-列表示法，该表示法对行和列都使用基于零的索引。

示例

在下面的示例中，字符串“Hello world”使用 Excel 的标准单元格地址写入 A1 单元格，而“Welcome to XLSXWriter”使用行-列表示法写入 C5 单元格。

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.write('A1', 'Hello world')           # A1 notation
ws.write(4,2,"Welcome to XLSXWriter")   # Row-column notation
wb.close()

输出

使用 Excel 软件打开hello.xlsx 文件。

编号的行-列表示法在以编程方式引用单元格时特别有用。在以下代码中，必须将列表列表中的数据写入工作表中的一系列单元格。这是通过两个嵌套循环实现的，外部循环表示行号，内部循环表示列号。

data = [
   ['Name', 'Physics', 'Chemistry', 'Maths', 'Total'],
   ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85],
   ['Karishma', 55, 65, 75],
]
for row in range(len(data)):
   for col in range(len(data[row])):
      ws.write(row, col, data[row][col])

可以使用以下代码中使用的worksheet对象的write_row()方法实现相同的结果：

for row in range(len(data)):
   ws.write_row(6+row,0, data[row])

worksheet 对象具有add_table() 方法，该方法将数据写入一个范围并转换为 Excel 范围，在顶行显示自动筛选下拉箭头。

ws.add_table('G6:J9', {'data': data, 'header_row':True})

示例

以上三个代码的输出可以通过以下代码验证，并在下图中显示：

import xlsxwriter

wb = xlsxwriter.Workbook('ex1.xlsx')
ws = wb.add_worksheet()

data = [
   ['Name', 'Physics', 'Chemistry', 'Maths', 'Total'],
   ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85],
   ['Karishma', 55, 65, 75],
]
for row in range(len(data)):
   for col in range(len(data[row])):
      ws.write(row, col, data[row][col])
      
for row in range(len(data)):
   ws.write_row(6+row,0, data[row])
   
ws.add_table('G6:J9', {'data': data, 'header_row':False})

wb.close()

输出

执行上述程序并使用 Excel 软件打开ex1.xlsx。

Python XlsxWriter - 定义名称

在 Excel 中，可以使用用户定义的名称来标识单元格、公式或单元格范围，该名称可以用作变量，使公式定义易于理解。这可以通过 Workbook 类的define_name() 方法来实现。

在以下代码片段中，我们有一系列包含数字的单元格。此范围已命名为 marks。

data=['marks',50,60,70, 'Total']
ws.write_row('A1', data)
wb.define_name('marks', '=Sheet1!$A$1:$E$1')

如果名称分配给一系列单元格，则define_name() 方法的第二个参数是一个字符串，其中包含工作表名称，后跟“！”符号，然后是使用绝对寻址方案的单元格范围。在这种情况下，sheet1 中的范围A1:E1 命名为marks。

此名称可用于任何公式。例如，我们计算由名称marks标识的范围中数字的总和。

ws.write('F1', '=sum(marks)')

我们还可以在write_formula() 方法中使用命名的单元格。在以下代码中，此方法用于计算金额的利息，其中rate 是一个定义的名称。

ws.write('B5', 10)
wb.define_name('rate', '=sheet1!$B$5')

ws.write_row('A5', ['Rate', 10])

data=['Amount',1000, 2000, 3000]
ws.write_column('A6', data)
ws.write('B6', 'Interest')
for row in range(6,9):
   ws.write_formula(row, 1, '= rate*$A{}/100'.format(row+1))

我们也可以使用write_array_formula() 方法代替上面的循环代码：

ws.write_array_formula('D7:D9' , '{=rate/100*(A7:A9)}')

示例

使用define_name() 方法的完整代码如下：

import xlsxwriter

wb = xlsxwriter.Workbook('ex2.xlsx')
ws = wb.add_worksheet()

data = ['marks',50,60,70, 'Total']
ws.write_row('A1', data)
wb.define_name('marks', '=Sheet1!$A$1:$E$1')
ws.write('F1', '=sum(marks)')

ws.write('B5', 10)
wb.define_name('rate', '=sheet1!$B$5')

ws.write_row('A5', ['Rate', 10])

data=['Amount',1000, 2000, 3000]
ws.write_column('A6', data)
ws.write('B6', 'Interest')

for row in range(6,9):
   ws.write_formula(row, 1, '= rate*$A{}/100'.format(row+1))
   
wb.close()

输出

运行上述程序并使用 Excel 打开ex2.xlsx。

Python XlsxWriter - 公式和函数

Worksheet 类提供三种使用公式的方法。

• write_formula()
• write_array_formula()
• write_dynamic_array_formula()

所有这些方法都用于为单元格分配公式和函数。

write_formula() 方法

write_formula() 方法需要单元格的地址和包含有效 Excel 公式的字符串。在公式字符串内，只接受 A1 样式的地址表示法。但是，单元格地址参数可以是标准 Excel 类型或基于零的行和列号表示法。

示例

在下面的示例中，各种语句使用write_formula() 方法。第一个使用标准 Excel 表示法分配公式。第二个语句使用行和列号来指定设置公式的目标单元格的地址。在第三个示例中，IF() 函数被分配给 G2 单元格。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[
   ['Name', 'Phy', 'Che', 'Maths', 'Total', 'percent', 'Result' ],
   ['Arvind', 50,60,70]
]
ws.write_row('A1', data[0])
ws.write_row('A2', data[1])
ws.write_formula('E2', '=B2+C2+D2')
ws.write_formula(1,5, '=E2*100/300')
ws.write_formula('G2', '=IF(F2>=50, "PASS","FAIL")')

wb.close()

输出

Excel 文件显示以下结果：

write_array_formula() 方法

write_array_formula() 方法用于将公式扩展到一个范围。在 Excel 中，数组公式对一组值执行计算。它可以返回单个值或一系列值。

数组公式用公式周围的一对大括号表示——{=SUM(A1:B1*A2:B2)}。范围可以通过范围中第一个和最后一个单元格的行号和列号指定（例如 0,0, 2,2）或通过字符串表示'A1:C2'指定。

示例

在下面的示例中，数组公式用于 E、F 和 G 列，根据 B2:D4 范围中的分数计算总计、百分比和结果。

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[
   ['Name', 'Phy', 'Che', 'Maths', 'Total', 'percent', 'Result'],
   ['Arvind', 50,60,70],
   ['Amar', 55,65,75],
   ['Asha', 75,85,80]
]

for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
ws.write_array_formula('E2:E4', '{=B2:B4+C2:C4+D2:D4}')
ws.write_array_formula(1,5,3,5, '{=(E2:E4)*100/300}')
ws.write_array_formula('G2:G4', '{=IF((F2:F4)>=50, "PASS","FAIL")}')

wb.close()

输出

以下是使用 MS Excel 打开时工作表的外观：

write_dynamic_array_data() 方法

write_dynamic_array_data() 方法将动态数组公式写入单元格范围。动态数组的概念已在 EXCEL 的 365 版本中引入，并且已经引入了一些利用动态数组优势的新函数。这些函数是：

动态数组是返回值范围，其大小可以根据结果而改变。例如，像FILTER()这样的函数返回一个值数组，其大小可能因筛选结果而异。

示例

在下面的示例中，数据范围是 A1:D17。筛选器函数使用此范围，条件范围是 C1:C17，其中给出了产品名称。FILTER() 函数的结果是一个动态数组，因为满足条件的行数可能会改变。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = (
   ['Region', 'SalesRep', 'Product', 'Units'],
   ['East',   'Tom',      'Apple',    6380],
   ['West',   'Fred',     'Grape',    5619],
   ['North',  'Amy',      'Pear',     4565],
   ['South',  'Sal',      'Banana',   5323],
   ['East',   'Fritz',    'Apple',    4394],
   ['West',   'Sravan',   'Grape',    7195],
   ['North',  'Xi',       'Pear',     5231],
   ['South',  'Hector',   'Banana',   2427],
   ['East',   'Tom',      'Banana',   4213],
   ['West',   'Fred',     'Pear',     3239],
   ['North',  'Amy',      'Grape',    6520],
   ['South',  'Sal',      'Apple',    1310],
   ['East',   'Fritz',    'Banana',   6274],
   ['West',   'Sravan',   'Pear',     4894],
   ['North',  'Xi',       'Grape',    7580],
   ['South',  'Hector',   'Apple',    9814])
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
ws.write_dynamic_array_formula('F1', '=FILTER(A1:D17,C1:C17="Apple")')
wb.close()

输出

请注意，写入 write_dynamic_array_formula() 的公式字符串不需要包含花括号。生成的 hello.xlsx 必须使用 Excel 365 应用程序打开。

Python XlsxWriter - 日期和时间

在 Excel 中，日期存储为实数，以便它们可以用于计算。默认情况下，1900 年 1 月 1 日（称为纪元）被视为 1，因此 2022 年 1 月 28 日对应于 44589。类似地，时间表示为数字的小数部分，作为一天的百分比。因此，2022 年 1 月 28 日 11.00 对应于 44589.45833。

set_num_format() 方法

由于 Excel 中的日期或时间就像任何其他数字一样，要将数字显示为日期，必须为其应用 Excel 数字格式。使用 Format 对象的set_num_format() 方法使用适当的格式。

以下代码片段将数字显示为“dd/mm/yy”格式。

num = 44589
format1 = wb.add_format()
format1.set_num_format('dd/mm/yy')
ws.write('B2', num, format1)

num_format 参数

或者，可以将add_format() 方法的num_format 参数设置为所需的格式。

format1 = wb.add_format({'num_format':'dd/mm/yy'})
ws.write('B2', num, format1)

示例

以下代码显示各种日期格式的数字。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

num=44589
ws.write('A1', num)

format2 = wb.add_format({'num_format': 'dd/mm/yy'})
ws.write('A2', num, format2)

format3 = wb.add_format({'num_format': 'mm/dd/yy'})
ws.write('A3', num, format3)

format4 = wb.add_format({'num_format': 'd-m-yyyy'})
ws.write('A4', num, format4)

format5 = wb.add_format({'num_format': 'dd/mm/yy hh:mm'})
ws.write('A5', num, format5)

format6 = wb.add_format({'num_format': 'd mmm yyyy'})
ws.write('A6', num, format6)

format7 = wb.add_format({'num_format': 'mmm d yyyy hh:mm AM/PM'})
ws.write('A7', num, format7)

wb.close()

输出

在Excel软件中，工作表如下所示：

write_datetime() 和 strptime()

XlsxWriter 的 Worksheet 对象也具有 **write_datetime()** 方法，此方法在处理使用 Python 标准库的 datetime 模块获得的日期和时间对象时非常有用。

**strptime()** 方法根据给定的格式解析字符串，并返回 **datetime** 对象。下面给出了一些用于格式化字符串的代码：

**strptime()** 方法使用方法如下：

>>> from datetime import datetime
>>> dt="Thu February 3 2022 11:35:5"
>>> code="%a %B %d %Y %H:%M:%S"
>>> datetime.strptime(dt, code)
datetime.datetime(2022, 2, 3, 11, 35, 5)

现在可以使用 **write_datetime()** 方法将此 **datetime** 对象写入工作表。

示例

在下面的示例中，**datetime** 对象使用不同的格式写入。

import xlsxwriter
from datetime import datetime
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()

dt="Thu February 3 2022 11:35:5"
code="%a %B %d %Y %H:%M:%S"
obj=datetime.strptime(dt, code)
date_formats = (
   'dd/mm/yy',
   'mm/dd/yy',
   'dd m yy',
   'd mm yy',
   'd mmm yy',
   'd mmmm yy',
   'd mmmm yyy',
   'd mmmm yyyy',
   'dd/mm/yy hh:mm',
   'dd/mm/yy hh:mm:ss',
   'dd/mm/yy hh:mm:ss.000',
   'hh:mm',
   'hh:mm:ss',
   'hh:mm:ss.000',
)
worksheet.write('A1', 'Formatted date')
worksheet.write('B1', 'Format')
row = 1

for fmt in date_formats:
   date_format = wb.add_format({'num_format': fmt, 'align': 'left'})
   worksheet.write_datetime(row, 0, obj, date_format)
   worksheet.write_string(row, 1, fmt)
   row += 1
wb.close()

输出

使用 Excel 打开时，工作表显示如下。

Python XlsxWriter - 表格

在 MS Excel 中，表是一个已被分组为单个实体的单元格范围。可以从公式中引用它，并且它具有公共的格式属性。可以在工作表表中定义一些功能，例如列标题、自动筛选器、总计行、列公式。

add_table() 方法

工作表方法 **add_table()** 用于将单元格范围添加为表。

worksheet.add_table(first_row, first_col, last_row, last_col, options)

允许使用标准“**A1**”或“**行/列**”表示法来指定范围。**add_table()** 方法可以采用一个或多个以下可选参数。请注意，除范围参数外，其他参数都是可选的。如果未给出，则会创建一个空表。

示例

data

此参数可用于指定表单元格中的数据。请看下面的例子：

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = [
   ['Namrata',  75, 65, 80],
   ['Ravi',     60, 70, 80],
   ['Kiran',    65, 75, 85],
   ['Karishma', 55, 65, 75],
]
ws.add_table("A1:D4", {'data':data})

wb.close()

输出

结果如下：

header_row

此参数可用于启用或禁用表中的标题行。默认情况下为启用。标题行将包含默认标题，例如“列 1”、“列 2”等。您可以使用 columns 参数设置所需的标题。

Columns

示例

此属性用于设置列标题。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = [
   ['Namrata',  75, 65, 80],
   ['Ravi',     60, 70, 80],
   ['Kiran',    65, 75, 85],
   ['Karishma', 55, 65, 75],
]

ws.add_table("A1:D4",
{'data':data,
   'columns': [
   {'header': 'Name'},
   {'header': 'physics'},
   {'header': 'Chemistry'},
   {'header': 'Maths'}]
})
wb.close()

输出

现在标题行已设置为如下所示：

autofilter

此参数默认为启用。设置为禁用时，标题行不显示下拉箭头以设置筛选条件。

Name

在 Excel 工作表中，表命名为 Table1、Table2 等。可以使用 *name* 参数根据需要设置表的名称。

ws.add_table("A1:E4", {'data':data, 'name':'marklist'})

Formula

可以通过在 columns 选项中指定 formula 子属性来创建包含公式的列。

示例

在下面的示例中，表的名称属性设置为“marklist”。“总计”列 E 的公式对分数进行求和，并赋值给 formula 子属性。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = [
   ['Namrata',  75, 65, 80],
   ['Ravi',     60, 70, 80],
   ['Kiran',    65, 75, 85],
   ['Karishma', 55, 65, 75],
]
formula = '=SUM(marklist[@[physics]:[Maths]])'
tbl = ws.add_table("A1:E5",
{'data': data,
   'autofilter': False,
   'name': 'marklist',
   'columns': [
      {'header': 'Name'},
      {'header': 'physics'},
      {'header': 'Chemistry'},
      {'header': 'Maths'},
      {'header': 'Total', 'formula': formula}
   ]
})
wb.close()

输出

执行上述代码后，工作表将显示包含分数总和的 **总计** 列。

Python XlsxWriter - 应用筛选

在 Excel 中，您可以使用逻辑表达式根据条件设置表格数据的筛选器。在 XlsxWriter 的工作表类中，我们有 **autofilter()** 方法用于此目的。此方法的必需参数是单元格范围。这会在标题行中创建下拉选择器。要应用某些条件，我们有两种方法可用：**filter_column()** 或 **filter_column_list()**。

应用列的筛选条件

在下面的示例中，范围 A1:D51（即单元格 0,0 到 50,3）中的数据用作 **autofilter()** 方法的范围参数。使用 **filter_column()** 方法在第 0 列（带有“地区”标题）上设置筛选条件 **'Region == East'**。

示例

通过为工作表对象的 **set_row()** 方法的 hidden 选项设置 true，隐藏数据范围内不满足筛选条件的所有行。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = (
   ['Region', 'SalesRep', 'Product', 'Units'],
   ['East',   'Tom',      'Apple',    6380],
   ['West',   'Fred',     'Grape',    5619],
   ['North',  'Amy',      'Pear',     4565],
   ['South',  'Sal',      'Banana',   5323],
   ['East',   'Fritz',    'Apple',    4394],
   ['West',   'Sravan',   'Grape',    7195],
   ['North',  'Xi',       'Pear',     5231],
   ['South',  'Hector',   'Banana',   2427],
   ['East',   'Tom',      'Banana',   4213],
   ['West',   'Fred',     'Pear',     3239],
   ['North',  'Amy',      'Grape',    6520],
   ['South',  'Sal',      'Apple',    1310],
   ['East',   'Fritz',    'Banana',   6274],
   ['West',   'Sravan',   'Pear',     4894],
   ['North',  'Xi',       'Grape',    7580],
   ['South',  'Hector',   'Apple',    9814]
)
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
ws.autofilter(0, 0, 50, 3)

ws.filter_column(0, 'Region == East')

row = 1
for row_data in (data):
   region = row_data[0]
   
   if region != 'East':
      ws.set_row(row, options={'hidden': True})
      
   ws.write_row(row, 0, row_data)
   
   row += 1
wb.close()

输出

当我们借助 Excel 打开工作表时，我们会发现只有“地区 = 东部”的行可见，其他行被隐藏（您可以通过清除筛选器再次显示它们）。

column 参数可以是零索引的列号或字符串列名。Python 中允许的所有逻辑运算符都可以在条件中使用（==，！=，<，>，<=，>=）。可以在多列上定义筛选条件，并且可以通过 **and** 或 **or** 运算符将它们组合起来。带有逻辑运算符的条件示例如下：

ws.filter_column('A', 'x > 2000')
ws.filter_column('A', 'x != 2000')
ws.filter_column('A', 'x > 2000 and x<5000')

请注意，条件参数中的“**x**”只是一个形式上的占位符，可以是任何合适的字符串，因为它在内部会被忽略。

ws.filter_column('A', 'price > 2000')
ws.filter_column('A', 'x != 2000')
ws.filter_column('A', 'marks > 60 and x<75')

XlsxWriter 还允许在包含字符串数据的列的筛选条件中使用通配符“**\***”和“**?**”。

ws.filter_column('A', name=K*') #starts with K
ws.filter_column('A', name=*K*') #contains K
ws.filter_column('A', name=?K*') # second character as K
ws.filter_column('A', name=*K??') #any two characters after K

示例

在下面的示例中，对列 A 的第一个筛选要求区域为西部，对列 D 的第二个筛选的条件为“**units > 5000**”。不满足条件“**region = West**”或“**units > 5000**”的行将被隐藏。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = (
   ['Region',  'SalesRep', 'Product', 'Units'],
   ['East',    'Tom',      'Apple',    6380],
   ['West',    'Fred',     'Grape',    5619],
   ['North',   'Amy',      'Pear',     4565],
   ['South',   'Sal',      'Banana',   5323],
   ['East',    'Fritz',    'Apple',    4394],
   ['West',    'Sravan',   'Grape',    7195],
   ['North',   'Xi',       'Pear',     5231],
   ['South',   'Hector',   'Banana',   2427],
   ['East',    'Tom',      'Banana',   4213],
   ['West',    'Fred',     'Pear',     3239],
   ['North',   'Amy',      'Grape',    6520],
   ['South',   'Sal',      'Apple',    1310],
   ['East',    'Fritz',    'Banana',   6274],
   ['West',    'Sravan',   'Pear',     4894],
   ['North',   'Xi',       'Grape',    7580],
   ['South',   'Hector',   'Apple',    9814])
        
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
ws.autofilter(0, 0, 50, 3)

ws.filter_column('A', 'x == West')
ws.filter_column('D', 'x > 5000')

row = 1
for row_data in (data[1:]):
   region = row_data[0]
   volume = int(row_data[3])
   
   if region == 'West' or volume > 5000:
      pass
   else:
      ws.set_row(row, options={'hidden': True})
      
   ws.write_row(row, 0, row_data)
   row += 1
   
wb.close()

输出

在 Excel 中，可以在列 A 和 D 标题上看到筛选图标。筛选后的数据如下所示：

应用列列表筛选器

**filter_column_list()** 方法可用于表示 Excel 2007 样式中具有多个选定条件的筛选器。

ws.filter_column_list(col,list)

第二个参数是与给定列中的数据匹配的值列表。例如：

ws.filter_column_list('C', ['March', 'April', 'May'])

这将导致筛选数据，以便列 C 中的值与列表中的任何项目匹配。

示例

在下面的示例中，**filter_column_list()** 方法用于筛选区域等于东部或西部的 **行**。

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
data = (
   ['Region', 'SalesRep', 'Product', 'Units'],
   ['East',   'Tom',      'Apple',    6380],
   ['West',   'Fred',     'Grape',    5619],
   ['North',  'Amy',      'Pear',     4565],
   ['South',  'Sal',      'Banana',   5323],
   ['East',   'Fritz',    'Apple',    4394],
   ['West',   'Sravan',   'Grape',    7195],
   ['North',  'Xi',       'Pear',     5231],
   ['South',  'Hector',   'Banana',   2427],
   ['East',   'Tom',      'Banana',   4213],
   ['West',   'Fred',     'Pear',     3239],
   ['North',  'Amy',      'Grape',    6520],
   ['South',  'Sal',      'Apple',    1310],
   ['East',   'Fritz',    'Banana',   6274],
   ['West',   'Sravan',   'Pear',     4894],
   ['North',  'Xi',       'Grape',    7580],
   ['South',  'Hector',   'Apple',    9814]
)
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
ws.autofilter(0, 0, 50, 3)

l1= ['East', 'West']
ws.filter_column_list('A', l1)

row = 1
for row_data in (data[1:]):
   region = row_data[0]
   
   if region not in l1:
      ws.set_row(row, options={'hidden': True})
      
   ws.write_row(row, 0, row_data)
   row += 1
   
wb.close()

输出

列 A 显示已应用自动筛选器。显示所有区域为东部或西部的行，其余行被隐藏。

在 Excel 软件中，单击 **地区** 标题中的 **筛选器** 选择器箭头，我们应该看到已应用区域等于东部或西部的筛选器。

Python XlsxWriter - 字体和颜色

使用字体

要对工作表单元格进行格式化，我们需要使用 **add_format()** 方法借助 Format 对象并使用其属性或格式化方法对其进行配置。

f1 = workbook.add_format()
f1 = set_bold(True)
# or
f2 = wb.add_format({'bold':True})

然后将此格式对象用作工作表的 write() 方法的参数。

ws.write('B1', 'Hello World', f1)

示例

要使单元格中的文本 **加粗、下划线、斜体** 或 **删除线**，我们可以使用这些属性或相应的 methods。在下面的示例中，文本“Hello World”使用 set 方法编写。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

for row in range(4):
   ws.write(row,0, "Hello World")

f1=wb.add_format()
f2=wb.add_format()
f3=wb.add_format()
f4=wb.add_format()

f1.set_bold(True)
ws.write('B1', '=A1', f1)

f2.set_italic(True)
ws.write('B2', '=A2', f2)

f3.set_underline(True)
ws.write('B3', '=A3', f3)

f4.set_font_strikeout(True)
ws.write('B4', '=A4', f4)

wb.close()

输出

结果如下：

示例

另一方面，我们可以使用 **font_color、font_name** 和 **font_size** 属性来格式化文本，如下例所示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

for row in range(4):
   ws.write(row,0, "Hello World")
   
f1=wb.add_format({'bold':True, 'font_color':'red'})
f2=wb.add_format({'italic':True,'font_name':'Arial'})
f3=wb.add_format({'font_size':20})
f4=wb.add_format({'font_color':'blue','font_size':14,'font_name':'Times New Roman'})

ws.write('B1', '=A1', f1)
ws.write('B2', '=A2', f2)
ws.write('B3', '=A3', f3)
ws.write('B4', '=A4', f4)

wb.close()

输出

可以使用 Excel 打开工作表来验证上述代码的输出：

文本对齐

XlsxWriter 的 Format 对象也可以使用对齐方法/属性创建。align 属性可以具有 **left、right、center** 和 **justify** 值。

示例

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
for row in range(4):
   ws.write(row,0, "Hello World")
ws.set_column('B:B', 30)

f1=wb.add_format({'align':'left'})
f2=wb.add_format({'align':'right'})
f3=wb.add_format({'align':'center'})
f4=wb.add_format({'align':'justify'})
ws.write('B1', '=A1', f1)
ws.write('B2', '=A2', f2)
ws.write('B3', '=A3', f3)
ws.write('B4', 'Hello World', f4)

wb.close()

输出

以下输出显示具有不同对齐方式的文本“Hello World”。请注意，B 列的宽度由工作表对象的 **set_column()** 方法设置为 30。

示例

Format 对象还具有 **valign** 属性来控制单元格的垂直位置。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

for row in range(4):
   ws.write(row,0, "Hello World")
   
ws.set_column('B:B', 30)

for row in range(4):
   ws.set_row(row, 40)
f1=wb.add_format({'valign':'top'})
f2=wb.add_format({'valign':'bottom'})
f3=wb.add_format({'align':'vcenter'})
f4=wb.add_format({'align':'vjustify'})

ws.write('B1', '=A1', f1)
ws.write('B2', '=A2', f2)
ws.write('B3', '=A3', f3)
ws.write('B4', '=A4', f4)

wb.close()

输出

在上述代码中，使用 set_row() 方法将第 1 行到第 4 行的高度设置为 40。

单元格背景色和前景色

Format 对象的两个重要属性是 **bg_color** 和 **fg_color**，用于设置单元格的背景色和前景色。

示例

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.set_column('B:B', 30)

f1=wb.add_format({'bg_color':'red', 'font_size':20})
f2=wb.add_format({'bg_color':'#0000FF', 'font_size':20})

ws.write('B1', 'Hello World', f1)
ws.write('B2', 'HELLO WORLD', f2)
wb.close()

输出

上述代码的结果如下所示：

Python XlsxWriter - 数字格式

在 Excel 中，不同的数字数据格式选项在 **设置单元格格式** 菜单的 **数字** 选项卡中提供。

要使用 XlsxWriter 控制数字的格式，我们可以使用 **set_num_format()** 方法或定义 **add_format()** 方法的 **num_format** 属性。

f1 = wb.add_format()
f1.set_num_format(FormatCode)
#or
f1 = wb.add_format('num_format': FormatCode)

Excel 有许多预定义的数字格式。它们可以在数字选项卡的自定义类别下找到，如上图所示。例如，带有两位小数和逗号分隔符的数字的格式代码为 #,##0.00。

示例

在下面的示例中，数字 1234.52 使用不同的格式代码进行格式化。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.set_column('B:B', 30)

num=1234.52

num_formats = (
   '0.00',
   '#,##0.00',
   '0.00E+00',
   '##0.0E+0',
   '₹#,##0.00',
)
ws.write('A1', 'Formatted Number')
ws.write('B1', 'Format')

row = 1
for fmt in num_formats:
   format = wb.add_format({'num_format': fmt})
   ws.write_number(row, 0, num, format)
   ws.write_string(row, 1, fmt)
   row += 1
wb.close()

输出

下图显示了已格式化的数字及其使用的格式代码：

Python XlsxWriter - 边框

本节介绍如何应用和设置单元格边框以及文本框周围边框的外观。

使用单元格边框

控制单元格边框外观的 **add_format()** 方法中的属性如下表所示：

请注意，对于add_format() 方法的每个属性，都有一个对应的以set_propertyname() 方法开头的格式类方法。

例如，要在单元格周围设置边框，我们可以使用add_format() 方法中的 border 属性，如下所示：

f1= wb.add_format({ 'border':2})

同样的操作也可以通过调用set_border() 方法来完成：

f1 = workbook.add_format()
f1.set_border(2)

各个边框元素可以通过属性或格式方法进行配置，如下所示：

• set_bottom()
• set_top()
• set_left()
• set_right()

这些边框方法/属性具有与预定义样式相对应的整数值，如下表所示：

示例

以下代码展示了如何使用 border 属性。这里，每一行都有一个值为 2 的边框样式，对应粗实线。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

f1=wb.add_format({'bold':True, 'border':2, 'border_color':'red'})
f2=wb.add_format({'border':2, 'border_color':'red'})

headings = ['Month', 'Product A', 'Product B']

data = [
   ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'June'],
   [10, 40, 50, 20, 10, 50],
   [30, 60, 70, 50, 40, 30],
]

ws.write_row('A1', headings, f1)
ws.write_column('A2', data[0], f2)
ws.write_column('B2', data[1],f2)
ws.write_column('C2', data[2],f2)

wb.close()

输出

工作表显示单元格周围有粗体边框。

文本框边框的使用

border 属性也适用于文本框对象。文本框还有一个 line 属性，它与 border 属性类似，因此可以互换使用。边框本身可以通过 none、color、width 和dash_type 参数进一步设置格式。

将 Line 或 border 设置为 none 表示文本框将没有任何边框。dash_type 参数可以是以下任何值：

• 实线 (solid)
• 圆点 (round_dot)
• 方点 (square_dot)
• 虚线 (dash)
• 点划线 (dash_dot)
• 长划线 (long_dash)
• 长点划线 (long_dash_dot)
• 长双点划线 (long_dash_dot_dot)

示例

这是一个显示两个文本框的程序，一个带有红色实线边框；第二个框具有蓝色dash_dot 类型边框。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

ws.insert_textbox('B2', 'Welcome to Tutorialspoint',
{'border': {'color': '#FF9900'}})

ws.insert_textbox('B10', 'Welcome to Tutorialspoint', {
   'line':
   {'color': 'blue', 'dash_type': 'dash_dot'}
})
wb.close()

输出

输出工作表显示文本框边框。

Python XlsxWriter - 超链接

超链接是一个字符串，单击它时，会将用户带到其他位置，例如 URL、同一工作簿中的另一个工作表或计算机上的另一个工作簿。Worksheet 类为此提供了write_url() 方法。超链接也可以使用 url 属性放置在文本框内。

首先，让我们了解write_url() 方法。除了单元格位置外，它还需要指向的 URL 字符串。

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write_url('A1', 'https://tutorialspoint.com/index.htm')

workbook.close()

此方法有一些可选参数。一个是 Format 对象，用于配置要显示的 URL 的字体、颜色属性。我们还可以指定工具提示字符串和 URL 的显示文本。当文本未给出时，URL 本身将显示在单元格中。

示例

支持的不同类型的 URL 包括http://、https://、ftp:// 和mailto:。在下面的示例中，我们使用了这些 URL。

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write_url('A1', 'https://tutorialspoint.com/index.htm')
worksheet.write_url('A3', 'https://:8080')
worksheet.write_url('A5', 'ftp://pythonlang.cn')
worksheet.write_url('A7', 'mailto:dummy@abc.com')

workbook.close()

输出

运行上述代码，并使用 Excel 打开hello.xlsx 文件。

示例

我们还可以插入指向同一工作簿中的另一个工作表或另一个工作簿的超链接。这可以通过在本地 URI 前缀加上internal: 或external: 来完成。

import xlsxwriter

workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write_url('A1', 'internal:Sheet2!A1', string="Link to sheet2", tip="Click here")
worksheet.write_url('A4', "external:c:/test/testlink.xlsx", string="Link to other workbook")
   
workbook.close()

输出

请注意，string 和tip 参数作为link 和tool tip 的替代文本给出。上述程序的输出如下所示：

Python XlsxWriter - 条件格式

Excel 使用条件格式根据用户定义的条件更改区域中单元格的外观。从条件格式菜单中，可以定义包含各种类型值的条件。

在下面显示的工作表中，A 列包含不同的数字。小于 50 的数字以红色字体和灰色背景显示。

这是通过定义以下条件格式规则实现的：

conditional_format() 方法

在 XlsxWriter 中，Worksheet 类中定义了一个conditional_format() 方法。为了实现上述结果，conditional_format() 方法的调用方式如下面的代码所示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[56,95,63,34,81,47,74,5,99,12]
row=0

for num in data:
   ws.write(row,0,num)
   row+=1
f1 = wb.add_format({'bg_color': '#D9D9D9', 'font_color': 'red'})
ws.conditional_format(
   'A1:A10',{
      'type':'cell', 'criteria':'<', 'value':50, 'format':f1
   }
)
wb.close()

参数

conditional_format() 方法的第一个参数是单元格区域，第二个参数是条件格式选项的字典。

选项字典使用以下参数配置条件格式规则：

type 选项是必需参数。其值为 cell、date、text、formula 等。每个参数都有子参数，例如 criteria、value、format 等。

• Type 是最常见的条件格式类型。当基于简单条件将格式应用于单元格时使用。

• Criteria 参数设置用于评估单元格数据的条件。除了 between 和 not between 运算符之外，所有逻辑运算符都是 criteria 参数的可能值。

• Value 参数是构成规则的 criteria 的操作数。

• Format 参数是 Format 对象（由add_format() 方法返回）。这定义了要应用于满足条件的单元格的格式化功能，例如字体、颜色等。

date 类型类似于 cell 类型，并使用相同的 criteria 和 values。但是，value 参数应作为datetime 对象给出。

text 类型指定 Excel 的“特定文本”样式条件格式。它用于使用 criteria 和 value 参数进行简单的字符串匹配。

示例

当使用formula 类型时，条件格式取决于用户定义的公式。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = [
   ['Anil', 45, 55, 50], ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85], ['Karishma', 55, 65, 45]
]   
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
f1 = wb.add_format({'font_color': 'blue', 'bold':True})

ws.conditional_format(
   'A1:D4',
   {
      'type':'formula', 'criteria':'=AVERAGE($B1:$D1)>60', 'value':50, 'format':f1
   })
wb.close()

输出

使用 MS Excel 打开结果工作簿。我们可以看到满足上述条件的行根据格式对象显示为蓝色。条件格式规则管理器还显示我们在上述代码中设置的条件。

Python XlsxWriter - 添加图表

Excel 最重要的功能之一是将数据转换为图表。图表是数据的直观表示。可以通过图表菜单生成不同类型的图表。

为了以编程方式生成图表，XlsxWriter 库具有 Chart 类。它的对象是通过调用 Workbook 类的add_chart() 方法获得的。然后，它使用add_series() 方法与工作表中的数据范围相关联。然后，使用其insert_chart() 方法将图表对象插入到工作表中。

示例

以下是显示简单柱状图的代码：

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart = wb.add_chart({'type': 'column'})

data = [
   [10, 20, 30,  40,  50],
   [20, 40, 60,  80, 100],
   [30, 60, 90, 120, 150],
]
worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])
worksheet.write_column('C1', data[2])

chart.add_series({'values': '=Sheet1!$A$1:$A$5'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
chart.add_series({'values': '=Sheet1!$C$1:$C$5'})

worksheet.insert_chart('B7', chart)

wb.close()

输出

生成的图表嵌入到工作表中，显示如下：

add_series() 方法还有以下附加参数：

• Values - 这是最重要的必填属性。它将图表与它显示的工作表数据链接起来。

• Categories - 这设置图表类别标签。如果没有给出，图表将只假设从 1…n 的顺序序列。

• Name - 为序列设置名称。名称显示在公式栏中。

• Line - 设置序列线类型的属性，例如颜色和宽度。

• Border - 设置序列的边框属性，例如颜色和样式。

• Fill - 设置序列的纯色填充属性，例如颜色。

• Pattern - 设置序列的图案填充属性。

• Gradient - 设置序列的渐变填充属性。

• data_labels - 为序列设置数据标签。

• Points - 为序列中的各个点设置属性。

在下面的示例中，在添加数据序列时，定义了 value 和 categories 属性。示例数据为：

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay",   30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

创建图表对象后，第一个数据序列对应于以 phy 作为 name 属性值的列。第一列中的学生姓名用作类别。

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})

第二个数据序列也参考 A 列中的姓名作为类别，以及标题为 Maths 的 C 列作为 values 属性。

chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})

示例

这是完整的示例代码：

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'column'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay",   30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})

chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})

worksheet.insert_chart('B7', chart1)

wb.close()

输出

工作表及其基础图表如下所示：

add_series() 方法还有data_labels 属性。如果设置为 True，则绘制数据点的值将显示在每一列的顶部。

示例

以下是 add_series() 方法的完整代码示例：

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'column'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay",   30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
   'data_labels': {'value':True},
})
chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
   'data_labels': {'value':True},
})
worksheet.insert_chart('B7', chart1)

wb.close()

输出

执行代码并打开Hello.xlsx。柱状图现在显示了数据标签。

数据标签可以显示在所有类型的图表中。数据标签的位置参数可以设置为顶部、底部、左侧或右侧。

XlsxWriter 支持以下类型的图表：

• Area - 创建区域（填充线）样式图表。

• Bar - 创建条形样式（转置直方图）图表。

• Column - 创建柱形样式（直方图）图表。

• Line - 创建线条样式图表。

• Pie - 创建饼图样式图表。

• Doughnut - 创建环形图样式图表。

• Scatter - 创建散点图样式图表。

• Stock - 创建股票样式图表。

• Radar - 创建雷达图样式图表。

许多图表类型也有子类型。例如，柱状图、条形图、区域图和折线图的子类型有堆叠和percent_stacked。类型和子类型参数可以在add_chart() 方法中给出。

workbook.add_chart({'type': column, 'subtype': 'stacked'})

图表使用其insert_chart() 方法嵌入到工作表中，该方法采用以下参数：

worksheet.insert_chart(location, chartObj, options)

options 参数是一个字典，用于配置图表的定位和比例。选项属性及其默认值为：

{
   'x_offset':        0,
   'y_offset':        0,
   'x_scale':         1,
   'y_scale':         1,
   'object_position': 1,
   'description':     None,
   'decorative':      False,
}

x_offset 和y_offset 值以像素为单位，而x_scale 和y_scale 值用于水平/垂直缩放图表。description 字段可用于为图表指定说明或“替代文本”字符串。

decorative 参数用于将图表标记为装饰性的，因此对于自动屏幕阅读器来说是不提供信息的。它必须设置为 True/False。最后，object_position 参数控制图表的对象定位。它允许以下值：

• 1 - 与单元格一起移动和调整大小（默认值）。

• 2 - 移动但不与单元格一起调整大小。

• 3 - 不与单元格一起移动或调整大小。

Python XlsxWriter - 图表格式

可以自定义图表的默认外观，使其更具吸引力、更具解释性和更方便用户使用。使用XlsxWriter，我们可以对 Chart 对象进行以下增强：

• 设置和格式化图表标题

• 设置 X 轴和 Y 轴标题和其他参数

• 配置图表图例

• 图表布局选项

• 设置边框和图案

标题

您可以通过调用其set_title() 方法来设置和配置图表对象的标题。可以使用各种参数，如下所示：

• Name - 设置要在图表上方显示的图表名称（标题）。name 属性是可选的。默认情况下没有图表标题。

• name_font − 设置图表标题的字体属性。

• Overlay - 允许标题叠加在图表上。

• 布局 − 设置图表标题的 (x, y) 位置（相对单位）。

• 无 − Excel 会自动添加图表标题。选择“无”选项将关闭此默认标题。它还会关闭所有其他 set_title() 选项。

X 轴和 Y 轴

使用两种方法 set_x_axis() 和 set_y_axis() 设置轴标题，name_font 用于标题文本的字体，num_font 用于显示在 X 轴和 Y 轴上的数字。

• name − 设置轴的标题或说明。

• name_font − 设置轴标题的字体属性。

• num_font − 设置轴数字的字体属性。

• num_format − 设置轴的数字格式。

• major_gridlines − 配置轴的主网格线。

• display_units − 设置轴的显示单位。

在前面的示例中，marklist 的数据已以柱状图的形式显示，我们设置了图表格式选项，例如图表标题以及 X 轴和 Y 轴的标题及其其他显示属性，如下所示：

chart1.set_x_axis(
   {'name': 'Students', 'name_font':{'name':'Arial', 'size':16, 'bold':True},})
chart1.set_y_axis(
   {
      'name': 'Marks', 'name_font':
      {'name':'Arial', 'size':16, 'bold':True}, 'num_font':{'name':'Arial', 'italic':True}
   }
)

示例

将上述代码片段添加到完整代码中。现在它看起来如下所示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'column'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']

data = [
   ["Jay", 30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})

chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})
chart1.set_title ({'name': 'Marklist',
   'name_font': {'name':'Times New Roman', 'size':24}
})
chart1.set_x_axis({'name': 'Students',
   'name_font': {'name':'Arial', 'size':16, 'bold':True},
})
chart1.set_y_axis({'name': 'Marks',
   'name_font':{'name':'Arial', 'size':16, 'bold':True},
   'num_font':{'name':'Arial', 'italic':True}
})
worksheet.insert_chart('B7', chart1)

wb.close()

输出

图表显示标题和坐标轴标题如下所示：

Python XlsxWriter - 图例

根据图表类型，数据以不同颜色或图案的柱状、条形、线形、弧形等形式直观地表示。图表图例使您可以轻松快速地了解哪种颜色/图案对应于哪个数据系列。

使用图表图例

要设置图例并配置其属性（例如位置和字体），XlsxWriter 有set_legend() 方法。属性如下：

• 无 − 在 Excel 图表中，图例默认情况下处于启用状态。none=True 选项将关闭图表图例。

• 位置 − 设置图表图例的位置。可以设置为顶部、底部、左侧、右侧、无。

• 字体 − 设置图表图例的字体属性（如名称、大小、粗体、斜体等）。

• 边框 − 设置图例的边框属性，例如颜色和样式。

• 填充 − 设置图例的纯色填充属性，例如颜色。

• 图案 − 设置图例的图案填充属性。

• 渐变 − 设置图例的渐变填充属性。

图表的一些图例属性设置如下：

chart1.set_legend(
   {'position':'bottom', 'font': {'name':'calibri','size': 9, 'bold': True}}
)

示例

以下是根据上述特性显示图例的完整代码：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'column'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay", 30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])
chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})
chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})
chart1.set_title ({'name': 'Marklist', 'name_font':
   {'name':'Times New Roman', 'size':24}})

chart1.set_x_axis({'name': 'Students', 'name_font':
   {'name':'Arial', 'size':16, 'bold':True},})

chart1.set_y_axis({'name': 'Marks','name_font':
   {'name':'Arial', 'size':16, 'bold':True},
   'num_font':{'name':'Arial', 'italic':True}})

chart1.set_legend({'position':'bottom', 'font':
   {'name':'calibri','size': 9, 'bold': True}})

worksheet.insert_chart('B7', chart1)

wb.close()

输出

图表在 X 轴标题下方显示图例。

在图表中，对应于物理和数学的列以不同的颜色显示。图表右侧的小彩色方块符号是图例，显示哪种颜色对应于物理或数学。

Python XlsxWriter - 条形图

条形图类似于柱状图，只是数据以成比例的水平条形而不是垂直列表示。要制作条形图，必须将add_chart() 方法的 type 参数设置为“bar”。

chart1 = workbook.add_chart({'type': 'bar'})

条形图如下所示：

条形图有两种子类型，即堆叠和百分比堆叠。在堆叠图中，某个类别的不同颜色的条形一个接一个地放置。在百分比堆叠图中，每个条形的长度显示其在每个类别总值中的百分比。

chart1 = workbook.add_chart({
   'type': 'bar',
   'subtype': 'percent_stacked'
})

示例

生成百分比堆叠条形图的程序如下所示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
chart1 = wb.add_chart({'type': 'bar', 'subtype': 'percent_stacked'})

# Add the worksheet data that the charts will refer to.
headings = ['Name', 'Phy', 'Maths']
data = [
   ["Jay", 30, 60],
   ["Mohan", 40, 50],
   ["Veeru", 60, 70],
]

worksheet.write_row(0,0, headings)
worksheet.write_row(1,0, data[0])
worksheet.write_row(2,0, data[1])
worksheet.write_row(3,0, data[2])

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$4',
   'values': '=Sheet1!$B$2:$B$4',
})

chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 3, 0],
   'values': ['Sheet1', 1, 2, 3, 2],
})
chart1.set_title ({'name': 'Marklist', 'name_font':
   {'name':'Times New Roman', 'size':24}})

chart1.set_x_axis({'name': 'Students', 'name_font':
   {'name':'Arial', 'size':16, 'bold':True}, })

chart1.set_y_axis({'name': 'Marks','name_font':
   {'name':'Arial', 'size':16, 'bold':True},
   'num_font':{'name':'Arial', 'italic':True}})

chart1.set_legend({'position':'bottom', 'font':
   {'name':'calibri','size': 9, 'bold': True}})

worksheet.insert_chart('B7', chart1)

wb.close()

输出

输出文件将如下所示：

Python XlsxWriter - 折线图

一条线显示一系列数据点，这些数据点沿 X 轴用一条线连接起来。这是一个独立轴，因为 X 轴上的值不依赖于垂直 Y 轴。

Y 轴是一个依赖轴，因为其值取决于 X 轴，结果是水平发展的线。

使用 XlsxWriter 线形图

要使用 XlsxWriter 以编程方式生成线形图，我们使用add_series()。图表对象的类型定义为“line”。

示例

在下面的示例中，我们将绘制一个线形图，显示两种产品在六个月内的销售额。使用add_series() 方法将对应于产品 A 和产品 B 销售额的两个数据系列添加到图表中。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
headings = ['Month', 'Product A', 'Product B']

data = [
   ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'June'],
   [10, 40, 50, 20, 10, 50],
   [30, 60, 70, 50, 40, 30],
]

bold=wb.add_format({'bold':True})
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

chart1 = wb.add_chart({'type': 'line'})

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$7',
   'values': '=Sheet1!$B$2:$B$7',
})
chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 6, 0],
   'values': ['Sheet1', 1, 2, 6, 2],
})
chart1.set_title ({'name': 'Sales analysis'})
chart1.set_x_axis({'name': 'Months'})
chart1.set_y_axis({'name': 'Units'})

worksheet.insert_chart('D2', chart1)

wb.close()

输出

执行上述程序后，XlsxWriter 将生成线形图，如下所示：

除了data_labels 之外，add_series() 方法还具有marker 属性。这在线形图中尤其有用。数据点由标记符号指示，例如圆形、三角形、正方形、菱形等。让我们在此图表的两个数据系列中分别指定圆形和正方形符号。

chart1.add_series({
   'name': '=Sheet1!$B$1',
   'categories': '=Sheet1!$A$2:$A$7',
   'values': '=Sheet1!$B$2:$B$7',
   'data_labels': {'value': True},
   'marker': {'type': 'circle'},
})
chart1.add_series({
   'name': ['Sheet1', 0, 2],
   'categories': ['Sheet1', 1, 0, 6, 0],
   'values': ['Sheet1', 1, 2, 6, 2],
   'data_labels': {'value': True},
   'marker': {'type': 'square'},})

数据标签和标记已添加到线形图中。

线形图还支持堆叠和百分比堆叠子类型。

Python XlsxWriter - 饼图

饼图是将单个数据系列表示为圆形，该圆形被划分为对应于系列中每个数据项的扇区。在饼图中，每个扇区的弧长与其表示的数量成正比。在下面的工作表中，产品的季度销售额以饼图的形式显示。

使用 XlsxWriter 饼图

要使用 XlsxWriter 以编程方式生成上述图表，我们首先将以下数据写入工作表。

headings = ['Category', 'Values']
data = [
   ['Q1', 'Q2', 'Q3', 'Q4'],
   [125, 60, 100, 80],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])

声明一个type=pie 的 Chart 对象，并将单元格范围 B1:D1 用作add_series() 方法的值参数，A 列中的季度 (Q1、Q2、Q3 和 Q4) 是类别。

chart1.add_series({
   'name': 'Quarterly sales data',
   'categories': ['Sheet1', 1, 0, 4, 0],
   'values': ['Sheet1', 1, 1, 4, 1],
})
chart1.set_title({'name': 'Pie Chart of Quarterly Sales'})

在饼图中，我们可以使用data_labels 属性通过设置percentage=True 来表示每个饼的百分比值。

示例

生成饼图的完整程序如下所示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()

headings = ['Category', 'Values']
data = [
   ['Q1', 'Q2', 'Q3', 'Q4'],
   [125, 60, 100, 80],
]
bold=wb.add_format({'bold':True})
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])

chart1 = wb.add_chart({'type': 'pie'})
chart1.add_series({
   'name': 'Quarterly sales data',
   'categories': ['Sheet1', 1, 0, 4, 0],
   'values': ['Sheet1', 1, 1, 4, 1],
   'data_labels': {'percentage':True},
})
chart1.set_title({'name': 'Pie Chart of Quarterly Sales'})

worksheet.insert_chart('D2', chart1)

wb.close()

输出

看看上述程序生成的饼图。

环形图

环形图是饼图的一种变体，中心有一个孔，它将类别显示为弧而不是扇区。两者都使一目了然地掌握部分与整体的关系变得容易。只需将图表类型更改为doughnut。

chart1 = workbook.add_chart({'type': 'doughnut'})

上述示例中数据的环形图如下所示：

Python XlsxWriter - 迷你图

迷你图是一个小型图表，没有坐标轴或坐标。它表示某个参数的变化。普通图表尺寸较大，具有许多解释性特征，例如标题、图例、数据标签等，并且与伴随文本分开。另一方面，迷你图尺寸较小，可以嵌入到文本或包含其上下文的单元格中。

迷你图功能由 Edward Tufte 于 1983 年引入。Microsoft 在 Excel 2010 中引入了迷你图。我们可以在 Excel 软件的插入功能区中找到迷你图选项。

迷你图有三种类型：

• line − 类似于线形图

• column − 类似于柱状图

• win_loss − 每个值是正数（赢）还是负数（输）。

使用 XlsxWriter 迷你图

XlsxWriter 模块具有add_sparkline() 方法。它基本上需要迷你图的单元格位置以及要表示为迷你图的数据范围。可选地，其他参数（例如类型、样式等）以字典对象的格式提供。默认情况下，类型为 line。

示例

以下程序以线形迷你图和柱形迷你图表示相同的一组数字。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[12,23,9,17,31,3,7,21,10,15]

ws.write_row('A1', data)
ws.set_column('K:K', 40)
ws.set_row(0, 30)
ws.add_sparkline('K1', {'range':'Sheet1!A1:J1'})

ws.write_row('A5', data)
ws.set_column('K:K', 40)
ws.set_row(4, 30)
ws.add_sparkline('K5', {'range':'Sheet1!A5:J5', 'type':'column'})

wb.close()

输出

在 K 单元格中添加了迷你图。

属性如下：

• range − 是必需参数。它指定迷你图将绘制的单元格数据范围。

• type − 指定迷你图的类型。有 3 种可用的迷你图类型：line、column 和 win_loss。

• markers − 为线型迷你图打开标记。

• style − MS Excel 中定义的迷你图样式。有 36 种样式类型。

• negative_points − 如果设置为 True，则迷你图中的负点将突出显示。

示例

以下程序生成带标记的线形迷你图和带突出显示负点的win_loss 迷你图。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[12,23,9,17,31,3,7,21,10,15]
ws.write_row('A1', data)
ws.set_column('K:K', 40)
ws.set_row(0, 30)

data=[1,1,-1,-1,-1,1,1,1,-1,-1]
ws.write_row('A5', data)
ws.set_column('K:K', 40)
ws.set_row(4, 30)
ws.add_sparkline('K1', {'range':'Sheet1!A1:J1', 'markers':True})
ws.add_sparkline('K5', {'range':'Sheet1!A5:J5', 'type':'win_loss',
'negative_points':True})

wb.close()

输出

K1 中的线形迷你图带有标记。K5 中的迷你图显示负点突出显示。

示例 – 样式类型

以下代码以柱形迷你图显示一系列数字。这里使用了十种不同的样式类型。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data=[12,23,9,17,31,3,7,21,10,15]
ws.write_row('C3', data)
ws.set_column('B:B',40)

for i in range(1,11):
   ws.write(i+4,0, 'style {}'.format(i))
   ws.add_sparkline(i+4,1,
   {'range':'Sheet1!$C$3:$L$3',
   'type':'column',
   'style':i})
   
wb.close()

输出

它将产生以下输出：

Python XlsxWriter - 数据验证

Excel 中的数据验证功能允许您控制用户可以输入单元格的内容。您可以使用它来确保单元格中的值是指定范围内的数字/日期、具有所需长度的文本，或者提供下拉菜单以从中选择值。

数据验证工具位于“数据”菜单中。第一个选项卡允许您设置验证条件。下图显示该条件要求单元格应包含 1 到 25 之间的整数：

在第二个选项卡中，设置当用户的游标位于目标单元格（在本例中为“输入 1 到 25 之间的任何整数”）时要显示的消息。您还可以设置消息标题；在本例中为年龄。

第三个选项卡允许您定义如果验证条件失败则要显示的任何错误消息。

当用户将光标放在 I10（已设置验证）中时，您可以看到输入消息。

当输入的数字不在范围内时，将显示错误消息。

使用 XlsxWriter 数据验证

您可以使用data_validation() 方法以编程方式设置验证条件、输入和错误消息。

worksheet.data_validation(
   'I10',
   {
      'validate': 'integer','criteria': 'between',
      'minimum': 1,'maximum': 25,
      'input_title': 'Enter an integer:',
      'input_message': 'between 1 and 25',
      'error_title': 'Input value is not valid!',
      'error_message': 'It  should be an integer between 1 and 25'
   }
)

data_validation() 方法接受 options 参数作为包含以下参数的字典：

• validate − 用于设置要验证的数据类型。允许的值包括整数、小数、列表、日期、时间、长度等。

• criteria − 用于设置验证条件。可以设置为任何逻辑运算符，包括between/not between、==、!=、<、>、<=、>= 等。

• value − 设置应用条件的限制值。始终需要此值。使用列表验证时，它作为逗号分隔的值字符串给出。

• input_title − 用于设置将光标放在目标单元格时输入消息的标题。

• input_message − 输入单元格时要显示的消息。

• error_title − 验证条件未满足时要显示的错误消息的标题。

• error_message − 设置错误消息。默认错误消息为“您输入的值无效。用户已限制可以输入单元格的值”。

示例

data_validation() 方法的以下用法会导致数据验证功能的行为如上图所示。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()

worksheet.data_validation(
   'I10',
   {
      'validate': 'integer','criteria': 'between',
      'minimum': 1,'maximum': 25,
      'input_title': 'Enter an integer:',
      'input_message': 'between 1 and 25',
      'error_title': 'Input value is not valid!',
      'error_message':'It should be an integer between 1 and 25'
   }
)
wb.close()

作为另一个示例，单元格 I10 设置了验证条件，以便强制用户从下拉列表中的字符串列表中选择其值。

worksheet.data_validation(
   'I10',
   {
      'validate': 'list',
      'source': ['Mumbai', 'Delhi', 'Chennai', 'Kolkata'],
      'input_title': 'Choose one:',
      'input_message': 'Select a value from th list',
   }
)

示例

带有下拉列表的修改后的验证程序如下所示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()

worksheet.data_validation(
   'I10',
   {
      'validate': 'list',
      'source': ['Mumbai', 'Delhi', 'Chennai', 'Kolkata'],
      'input_title': 'Choose one:',
      'input_message': 'Select a value from the list',
   }
)
wb.close()

输出

将光标放在 I10 单元格中时，将出现下拉列表：

示例

如果要让用户输入长度大于 5 的字符串，请使用>= 作为条件并将值设置为 5。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()

worksheet.data_validation(
   'I10',{
      'validate': 'length',
      'criteria': '>=','value': 5,'input_title': 'Enter name:',
      'input_message': 'Minimum length 5 character',
      'error_message':'Name should have at least 5 characters'
   }
)

wb.close()

输出

如果字符串的字符少于 5 个，则会弹出错误消息，如下所示：

Python XlsxWriter - 大纲和分组

在 Excel 中，您可以对具有特定列（或行）相同值的行列进行分组，以便可以通过单击鼠标即可隐藏或显示它们。此功能称为大纲和分组。它有助于显示小计或汇总。此功能可以在 MS Excel 软件的数据→大纲组中找到。

要使用此功能，数据范围必须使所有行都按一列中的值排序。假设我们有不同商品的销售额。按商品名称对范围进行排序后，单击“大纲”组中的“小计”选项。将弹出以下对话框。

工作表显示按商品的小计销售额，最后显示总计。在工作表的左侧，显示大纲级别。原始数据位于级别 3，小计位于级别 2，总计位于级别 1。

使用大纲和分组

要使用XlsxWriter实现此功能，我们需要使用set_row()方法的level属性。数据行设置为级别2。

ws.set_row(row, None, None, {'level': 2})

小计行级别为1。

ws.set_row(row, None, None, {'level': 1})

我们使用SUBTOTAL()函数来计算和显示一组中销售额的总和。

示例

完整的代码如下所示：

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

headings=['Item', 'Sales']
data=[
   ['Apple', 45], ['Apple', 84], ['Apple', 125],
   ['Mango', 32], ['Mango', 65], ['Mango', 90],
   ['Oranges', 60], ['Oranges', 75], ['Oranges',100],
]
ws.write_row('A1', headings)
item='Apple'
rownum=1
startrow=1
for row in data:
   if row[0]==item:
      ws.set_row(rownum, None, None, {'level': 2})
      ws.write_row(rownum,0, row)
      rownum+=1
else:
   ws.set_row(rownum, None, None, {'level': 1})
   ws.write(rownum, 0, item+' Subtotal')
   cellno='B{}:B{}'.format(startrow,rownum)
   print (cellno)
   ws.write(rownum,1,'=SUBTOTAL(9,'+cellno+')')
   # rownum+=1
   item=data[rownum][0]
   rownum+=1
   ws.set_row(rownum, None, None, {'level': 2})
   ws.write_row(rownum,0, row)
   rownum+=1
   startrow=rownum
else:
   ws.set_row(rownum, None, None, {'level': 1})
   ws.write(rownum, 0, item+' Subtotal')
   cellno='B{}:B{}'.format(startrow,rownum)
   ws.write(rownum,1,'=SUBTOTAL(9,'+cellno+')')
rownum+=1
ws.write(rownum, 0, 'Grand Total')
cellno='B{}:B{}'.format(1,rownum)
ws.write(rownum,1,'=SUBTOTAL(9,'+cellno+')')

wb.close()

输出

运行代码并使用Excel打开hello.xlsx。我们可以看到，大纲显示在左侧。

在每一级，减号表示可以折叠行，并且只显示小计行。

此图显示所有级别为2的行都已折叠。现在显示了大纲中的加号，这意味着可以展开数据行。如果单击级别1处的减号，则工作表中将只剩下总计。

Python XlsxWriter - 冻结和拆分窗格

freeze_panes()方法

XlsxWriter库中Worksheet对象的freeze_panes()方法将工作表划分为水平或垂直区域，称为窗格，并“冻结”这些窗格中的一个或两个，以便如果我们向下滚动或向右滚动，窗格（分别为顶部或左侧）将保持静止。

该方法需要参数row和col来指定分割的位置。需要注意的是，分割是在单元格的顶部或左侧指定的，并且该方法使用基于零的索引。如果不需要垂直或水平分割，可以将row和col参数之一设置为零。

示例

以下示例中的工作表在每一行显示列号的递增倍数，以便每个单元格显示行号和列号的乘积。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
format1=wb.add_format({'bg_color':'#D9D9D9', 'bold':True})

for col in range(0, 15):
   worksheet.write(0, col, col+1, format1)
   
for row in range(1, 51):
   for col in range(0,15):
      if col==0:
         worksheet.write(row,col,(col+1)*(row + 1), format1)
      else:
         worksheet.write(row,col,(col+1)*(row + 1))
# Freeze pane on the top row.
worksheet.freeze_panes(1, 0)

wb.close()

输出

然后，我们冻结顶行窗格。因此，打开工作表后，如果单元格指针向下滚动，顶行将始终保留在工作表上。

同样，我们可以使第一列保持静止。

# Freeze pane on the first column.
worksheet.freeze_panes(0, 1)

下面的屏幕截图显示，即使我们向右滚动，A列仍然可见。

通过将freeze_panes()方法中的row和column参数设置为1，顶行和最左列都将被冻结。

# Freeze pane on the first row, first column.
worksheet.freeze_panes(1, 1)

打开生成的工作表并滚动单元格光标。您会发现顶行和最左列中的行号和列号（已格式化为粗体并带有背景颜色）始终可见。

split_panes()方法

split_panes()方法也把工作表分成水平或垂直区域，称为窗格，但与freeze_panes()方法不同的是，窗格之间的分割线对用户可见，并且每个窗格都有自己的滚动条。

该方法具有参数“y”和“x”，用于指定分割的垂直和水平位置。这些参数以Excel使用的行高和列宽表示。行高和列宽的默认值分别为行15和列8.43。

如果不需要垂直或水平分割，可以将“y”和“x”参数之一设置为零。

要在第10行和第7列创建分割线，可以使用split_panes()方法，如下所示：

worksheet.split_panes(15*10, 8.43*7)

您会在工作表的第10行和第7列找到分割线。您可以将垂直分割线左右两侧的窗格以及水平分割线上下两侧的窗格滚动。请注意，其他窗格将保持不变。

示例

以下是创建分割线的完整代码，以及其输出：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
format1=wb.add_format({'bg_color':'#D9D9D9', 'bold':True})

for col in range(0, 15):
   worksheet.write(0, col, col+1, format1)
   
for row in range(1, 51):
   for col in range(0,15):
      if col==0:
         worksheet.write(row,col,(col+1)*(row + 1), format1)
      else:
         worksheet.write(row,col,(col+1)*(row + 1))
worksheet.split_panes(15*10, 8.43*7)

wb.close()

输出

运行代码并使用Excel打开hello.xlsx。我们可以看到，工作表在第10行和第7列被分成了不同的窗格。

Python XlsxWriter - 隐藏/保护工作表

工作表对象的hide()方法使工作表消失，直到通过Excel菜单取消隐藏。

在下面的工作表中，共有三个工作表，其中sheet2是隐藏的。

sheet1 = workbook.add_worksheet()
sheet2 = workbook.add_worksheet()
sheet3 = workbook.add_worksheet()

# Hide Sheet2. It won't be visible until it is unhidden in Excel.
worksheet2.hide()

它将创建以下工作表：

您不能隐藏“活动”工作表（通常是第一个工作表），因为这会导致Excel错误。因此，为了隐藏第一个工作表，您需要激活另一个工作表。

sheet2.activate()
sheet1.hide()

隐藏特定行或列

要在工作表中隐藏特定行或列，请在set_row()或set_column()方法中将hidden参数设置为1。以下语句隐藏活动工作表中的C、D和E列。

worksheet.set_column('C:E', None, None, {'hidden': 1})

示例

考虑以下程序：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
format1=wb.add_format({'bg_color':'#D9D9D9', 'bold':True})

for col in range(0, 15):
   worksheet.write(0, col, col+1, format1)
   
for row in range(1, 51):
   for col in range(0,15):
      if col==0:
         worksheet.write(row,col,(col+1)*(row + 1), format1)
      else:
         worksheet.write(row,col,(col+1)*(row + 1))
worksheet.set_column('C:E', None, None, {'hidden': 1})

wb.close()

输出

执行上述代码后，下面的工作表中C、D和E列不可见：

类似地，我们可以使用set_row()方法和hidden参数隐藏行。

for row in range(5, 7):
   worksheet.set_row(row, None, None, {'hidden':1})

结果如下：

Python XlsxWriter - 文本框

在Excel中，文本框是一个可以放置在工作表任何位置的图形对象，如果需要，可以随意移动。可以在文本框中包含的文本上应用所需的格式化功能，例如字体（颜色、大小、名称等）、对齐方式、填充效果、方向等。

使用XlsxWriter – 文本框

在XlsxWriter中，有一个insert_textbox()方法用于在工作表上放置文本框。必须给出文本框的单元格位置和要写入其中的文本。此外，不同的格式选项以字典对象的格式给出。

示例

以下代码在C5单元格显示一个文本框，给定的字符串将使用如下所示的字体和对齐属性显示：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
text = 'Welcome to TutorialsPoint'

options = {'font': {'color': 'red','size': 14},
   'align': {'vertical': 'middle','horizontal': 'center'}}
worksheet.insert_textbox('C5', text, options)

wb.close()

输出

使用Excel应用程序打开工作表“hello.xlsx”。文本框如下所示：

文本框选项 – 填充

文本框的默认大小为192 x 120像素（对应于3列和6行）。可以使用以像素为单位的width和height参数更改此大小。inset_textbox()方法可接受的参数之一是fill参数。它采用预定义的色名或十六进制表示的颜色作为值。

示例

以下代码在一个自定义大小的文本框中显示一个多行字符串，背景填充为红色。

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
text = 'TutorialsPoint - Simple Easy Learning\nThe best resource for Online Education'

options = {
   'width': 384,
   'height':80,
   'font': {'color': 'blue', 'bold':True, 'size': 14},
   'align': {'vertical': 'middle', 'horizontal': 'center'},
   'fill':{'color':'red'},
}
worksheet.insert_textbox('C5', text, options)
wb.close()

如下图所示，在C5单元格呈现了一个包含多行的文本框。

文本框选项 – text_rotation

另一个重要的属性是text_rotation。默认情况下，文本水平显示。如果需要，可以通过给定一个角度作为其值来更改其方向。请参考以下选项。

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
text = 'TutorialsPoint - Simple Easy Learning\nThe best resource for Online Education'

options = {
   'width': 128,
   'height':200,
   'font': {'bold':True, 'name':'Arial', 'size': 14},
   'text_rotation':90,
}
worksheet.insert_textbox('C5', text, options)
wb.close()

文本现在以垂直方向显示在文本框中。

object_position参数控制文本框的行为。它可以具有以下可能的值及其效果：

• "1" – 与单元格一起移动和调整大小（默认值）。

• "2" – 移动但不与单元格一起调整大小。

• "3" – 不与单元格一起移动或调整大小。

Python XlsxWriter - 插入图片

借助insert_image()方法，可以在工作表的特定单元格位置插入图像对象。基本上，您必须使用任何类型的符号指定单元格的位置以及要插入的图像。

worksheet.insert_image('C5', 'logo.png')

insert_image()方法在字典中采用以下可选参数。

偏移值以像素为单位。x_scale和y_scale参数用于水平和垂直缩放图像。

image_data参数用于添加io.BytesIO格式的内存字节流。

示例

以下程序从当前文件夹中的文件提取图像数据，并将其用作image_data参数的值。

from io import BytesIO
import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

filename = 'logo.png'

file = open(filename, 'rb')
data = BytesIO(file.read())
file.close()

worksheet.insert_image('C5', filename, {'image_data': data})

workbook.close()

输出

以下是结果工作表的视图：

Python XlsxWriter - 页面设置

工作表的页面设置方法与打印工作表时的外观有关。这些工作表方法控制方向、纸张大小、页边距等。

set_landscape()

此方法用于将工作表打印页面的方向设置为横向。

set_portrait()

此方法用于将工作表打印页面的方向设置为纵向。这是默认方向。

set_page_view()

此方法用于在“页面视图/布局”模式下显示工作表。

set_paper()

此方法用于设置工作表打印输出的纸张格式。它采用整数参数作为索引。它是Excel纸张格式索引。

以下是一些纸张样式和索引值：

set_margin()

此方法用于设置打印工作表时的页边距。它接受左、右、上和下参数，其值以英寸为单位。所有参数都是可选的。左和右参数默认为0.7，上和下参数默认为0.75。

Python XlsxWriter - 页眉和页脚

使用上述方法打印工作表时，将在纸张上生成页眉和页脚。打印预览也显示页眉和页脚。两者都通过set_header()和set_footer()方法进行配置。页眉和页脚字符串通过以下控制字符进行配置：

示例

以下代码使用set_header()和set_footer()方法：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = [ 
   ['Anil', 45, 55, 50], ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85],['Karishma', 55, 65, 45]
]
   
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
header1 = '&CTutorialspoint'
footer1 = '&LSimply Easy Learning'

ws.set_landscape()
ws.set_paper(9) #A4 paper
ws.set_header(header1)
ws.set_footer(footer1)

ws.set_column('A:A', 50)

wb.close()

输出

运行上述Python代码并打开工作表。从“文件”菜单中选择“打印”选项。在右侧窗格中，显示预览。您应该能够看到页眉和页脚。

Python XlsxWriter - 单元格批注

在Excel工作表中，可以出于各种原因插入批注。其中一个用途是解释单元格中的公式。此外，Excel批注还可以作为对其他用户的提醒或注释。它们对于与其他Excel工作簿交叉引用非常有用。

从Excel的菜单系统中，可以在功能区的“审阅”菜单中找到批注功能。

要添加和格式化批注，XlsxWriter具有add_comment()方法。此方法的两个必需参数是单元格位置（A1类型或行和列号）和批注文本。

示例

这是一个简单的示例：

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data='XlsxWriter Library'

ws.set_column('C:C', 25)
ws.set_row(2, 50)
ws.write('C3', data)

text = 'Developed by John McNamara'
ws.write_comment('C3', text)

wb.close()

输出

当我们打开工作簿时，当光标放在C3单元格中时，会在其右上角看到一个带有标记的批注。

默认情况下，批注不可见，除非光标悬停在写入批注的单元格上。您可以通过调用工作表对象的show_comment()方法或将单个批注的visible属性设置为True来显示工作表中的所有批注。

ws.write_comment('C3', text, {'visible': True})

示例

在以下代码中，放置了三个批注。但是，C3单元格中的批注已将其visible属性设置为False。因此，除非光标放在单元格中，否则看不到它。

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

ws.show_comments()

data='Python'
ws.set_column('C:C', 25)
ws.set_row(0, 50)
ws.write('C1', data)
text = 'Programming language developed by Guido Van Rossum'
ws.write_comment('C1', text)
data= 'XlsxWriter'
ws.set_row(2, 50)
ws.write('C3', data)
text = 'Developed by John McNamara'
ws.write_comment('C3', text, {'visible':False})
data= 'OpenPyXl'
ws.set_row(4, 50)
ws.write('C5', data)
text = 'Developed by Eric Gazoni and Charlie Clark'
ws.write_comment('C5', text, {'visible':True})

wb.close()

输出

它将产生以下输出：

您可以设置author选项来指示单元格批注的作者是谁。批注的作者也显示在工作表底部的状态栏中。

worksheet.write_comment('C3', 'Atonement', {'author': 'Tutorialspoint'})

可以使用set_comments_author()方法设置所有单元格批注的默认作者：

worksheet.set_comments_author('Tutorialspoint')

它将产生以下输出：

Python XlsxWriter - 与Pandas协同工作

Pandas 是一个流行的 Python 库，用于数据操作和分析。我们可以使用 XlsWriter 将Pandas 数据框写入 Excel 工作表。

要学习本节中描述的功能，我们需要在已安装XlsxWriter 的相同环境中安装Pandas 库。

pip3 install pandas

将 XlsxWriter 与 Pandas 一起使用

让我们从一个简单的例子开始。首先，从整数列表中的数据创建一个 Pandas 数据框。然后使用 XlsxWriter 作为引擎来创建 Pandas Excel 写入器。借助此引擎对象，我们可以将数据框对象写入 Excel 工作表。

示例

import pandas as pd

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

writer.save()

输出

创建的工作表显示如下：

向 Pandas 数据框添加图表

就像我们获得 Workbook 类对象，然后通过调用其add_worksheet() 方法获得 Worksheet 对象一样，写入器对象也可以用于获取这些对象。一旦我们得到它们，就可以使用 XlsxWriter 方法添加图表、数据表等。

在此示例中，我们设置了一个 Pandas 数据框并获取其维度（或形状）。

import pandas as pd
df = pd.DataFrame({'Data': [105, 60, 35, 90, 15, 30, 75]})
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
(max_row, max_col) = df.shape

工作簿和工作表对象是从写入器创建的。

workbook = writer.book
worksheet = writer.sheets['Sheet1']

其余的事情很简单。图表对象像我们之前做的那样添加。

chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]})
worksheet.insert_chart(1, 3, chart)
writer.save()

示例

以下代码使用 Pandas 数据框写入 Excel 工作簿，并由 XlsxWriter 创建一个柱状图。

import pandas as pd

df = pd.DataFrame({'Data': [105, 60, 35, 90, 15, 30, 75]})
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
(max_row, max_col) = df.shape

workbook = writer.book
worksheet = writer.sheets['Sheet1']
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': ['Sheet1', 1, 1, max_row, 1]})
worksheet.insert_chart(1, 3, chart)

writer.save()

输出

柱状图及其数据如下所示：

将数据框写入 Excel 表格

类似地，数据框可以写入 Excel 表格对象。此处的 数据框源自一个 Python 字典，其中键是数据框列标题。每个键的值都是一个列表，该列表又成为每一列的值。

import pandas as pd

df = pd.DataFrame({
   'Name': ['Namrata','Ravi','Kiran','Karishma'],
   'Percent': [73.33, 70, 75, 65.5],
   'RollNo': [1, 2,3,4]})
   
df = df[['RollNo', 'Name', 'Percent']]
(max_row, max_col) = df.shape

使用 xlsxwriter 引擎将数据框写入工作表 (sheet1)

writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

以下几行给出 Workbook 和 Worksheet 对象。

workbook = writer.book
worksheet = writer.sheets['Sheet1']

工作表中的数据借助 add_table() 方法转换为表格。

column_settings = [{'header': column} for column in df.columns]

worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

writer.save()

示例

以下是将 pandas 数据框写入 Excel 表格的完整代码。

import pandas as pd
df = pd.DataFrame({
   'Name': ['Namrata','Ravi','Kiran','Karishma'],
   'Percent': [73.33, 70, 75, 65.5],
   'RollNo': [1, 2,3,4]
})
   
df = df[['RollNo', 'Name', 'Percent']]
(max_row, max_col) = df.shape

writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet1']

column_settings = [{'header': column} for column in df.columns]

worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

writer.save()

输出

使用默认自动筛选设置的表格将从 A1 单元格开始显示。

Python XlsxWriter - VBA宏

在 Excel 中，宏是一系列记录的步骤，可以使用快捷键重复任意多次。记录宏时执行的步骤被转换为 VBA 编程指令，VBA 代表 Visual Basic for Applications。VBA 是 Visual Basic 语言的一个子集，专门用于自动化 MS Office 应用程序（如 Word、Excel、PowerPoint 等）中的任务。

记录宏的选项可在 MS Excel 的“开发工具”菜单中找到。如果看不到此菜单，则需要通过转到“文件→选项→自定义”功能区屏幕来激活它。

如下图所示，通过转到“视图→宏→记录宏”来单击“记录宏”按钮，并为宏指定一个合适的名称，然后执行要记录的所需操作。步骤完成后停止录制。分配一个所需的快捷键，以便可以重复记录的操作，并且按下它。

要查看 VBA 代码，请通过转到“视图→宏→查看宏”来编辑宏。从宏名称中选择宏，然后单击“编辑”。

将显示 VBA 编辑器。删除 Excel 生成的所有步骤，并添加弹出消息框的语句。

确认宏运行完美。按CTL+Shift+M，消息框将弹出。将此文件保存为.xlsm扩展名。它内部包含vbaproject.bin，一个二进制 OLE COM 容器。要从 Excel 宏文件中提取它，请使用vba_extract.py实用程序。

(xlsxenv) E:\xlsxenv>vba_extract.py test.xlsm
Extracted: vbaProject.bin

示例

现在可以使用add_vba_project()方法将此 vbaProject.bin 文件添加到 XlsxWriter 工作簿。在此工作表上，在 B3 单元格中放置一个按钮对象，并将其链接到我们已创建的宏（即macro1）。

import xlsxwriter

workbook = xlsxwriter.Workbook('testvba.xlsm')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 30)
workbook.add_vba_project('./vbaProject.bin')
worksheet.write('A3', 'Press the button to say Welcome.')
worksheet.insert_button(
   'B3',
   {
      'macro': 'macro1',
      'caption': 'Press Me',
      'width': 80, 'height': 30
   }
)
workbook.close()

输出

执行上述代码后，将创建名为 testvba.xlsm 的宏启用工作簿。打开它并单击按钮。它将导致消息框弹出，如下所示。