Python Xlsxwriter 简明教程

Python XlsxWriter - Quick Guide

Python XlsxWriter - Overview

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

XlsxWriter Features

XlsxWriter 的一些重要功能包括： −

  1. XlsxWriter 创建的文件 100% 兼容于 Excel XLSX 文件。

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

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

  4. 使用 XlsxWriter 可以设置自动筛选。

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

  6. 使用 XlsxWriter，可以插入 PNG/JPEG/GIF/BMP/WMF/EMF 图像。

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

  8. XlsxWriter 还支持添加宏。

  9. XlsxWriter 具有写入大文件的内存优化模式。

Python XlsxWriter - Environment Setup

Installing XlsxWriter using PIP

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

pip3 install xlsxwriter

Installing from a Tarball

另一种选择是从其托管在 https://github.com/jmcnamara/XlsxWriter/ 的源代码中安装 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

Cloning from 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

Getting Started

第一个测试模块/库正常运行的程序通常是写 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')

Example

hello.py 的完整代码如下 −

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

Output

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

hello world

Python XlsxWriter - Important Classes

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

  1. Workbook class

  2. Worksheet class

  3. Format class

  4. Chart class

  5. Chartsheet class

  6. Exception class

Workbook Class

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

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

工作簿类别定义了以下方法 -

Sr.No

Workbook Class & Description

1

add_worksheet() 向工作簿中添加一个新工作表。

2

add_format() 用于创建新的格式对象，这些对象用于将格式应用于单元格。

3

add_chart() 创建新的图表对象，可以通过 insert_chart() Worksheet 方法插入到工作表中。

4

add_chartsheet() 向工作簿中新增一个图表工作表。

5

close() 关闭 Workbook 对象并编写 XLSX 文件。

6

define_name() 在工作簿中创建已定义的名称，用作变量。

7

add_vba_project() 用于使用二进制 VBA 项目文件向工作簿添加宏或函数。

8

worksheets() 返回工作簿中的工作表列表。

Worksheet Class

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

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

write()

将泛型数据写入工作表单元格。 Parameters − row −单元格行（从零开始）。 col −单元格列（从零开始）。 *args −传递给子方法的其他参数，如数字、字符串和 cell_format。 Returns − 0 −成功 -1 −行或列超出工作表边界。

write_string()

向由行和列指定单元格中写入字符串。 Parameters − row (int) −单元格行（从零开始）。 col (int) −单元格列（从零开始）。 string (string) −要写入单元格的字符串。 cell_format (Format) −可选的格式对象。 Returns − 0 −成功 -1 −行或列超出工作表边界。 -2 −字符串被截断为 32k 个字符。

write_number()

向由行和列指定单元格中写入数字类型。 Parameters − row (int) −单元格行（从零开始）。 col (int) −单元格列（从零开始）。 string (string) −要写入单元格的字符串。 cell_format (Format) −可选的格式对象。 Returns − 0 −成功 -1 −行或列超出工作表边界。

write_formula()

将公式或函数写入由行和列指定单元格。 Parameters − row (int) −单元格行（从零开始）。 col (int) −单元格列（从零开始）。 formula (string) −要写入单元格的公式。 cell_format (Format) −可选的格式对象。 value −可选的结果。该值为公式计算的结果。 Returns − 0 −成功 -1 −行或列超出工作表边界。

insert_image()

用于向工作表中插入图像。图像可以为 PNG、JPEG、GIF、BMP、WMF 或 EMF 格式。 Parameters − row (int) −单元格行（从零开始）。 col (int) −单元格列（从零开始）。 filename −图像文件名（如果需要，包括路径）。 Returns − 0 −成功 -1 −行或列超出工作表边界。

insert_chart()

用于向工作表中插入图表。可以通过 Workbook add_chart() 方法创建图表对象。 Parameters − row (int) −单元格行（从零开始）。 col (int) −单元格列（从零开始）。 chart −图表对象。

conditional_format()

用于根据用户定义的条件向单元格或单元格范围添加格式。 Parameters − first_row (int) −该范围的第一行。（全部从零开始） first_col (int) −该范围的第一列。 last_row (int) −该范围的最后一行。 last_col (int) −该范围的最后一列。 options (dict) −条件格式选项。必须是一个包含描述条件格式类型和样式的参数的字典 Returns − 0 −成功 -1 −行或列超出工作表边界。 -2 −不正确的参数或选项。

add_table()

用于将单元格范围分组到 Excel 表中。 Parameters − first_row (int) −该范围的第一行。（全部从零开始） first_col (int) −该范围的第一列。 last_row (int) −该范围的最后一行。 last_col (int) −该范围的最后一列。 options (dict) −表格格式选项。

autofilter()

在工作表中设置自动筛选区域。它向二维范围的工作表数据标题添加下拉列表。用户可以基于简单条件来筛选数据。 Parameters − first_row (int) −该范围的第一行。（全部从零开始） first_col (int) −该范围的第一列。 last_row (int) −该范围的最后一行。 last_col (int) −该范围的最后一列。

Format Class

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

Font formatting methods and properties −

Method Name

Description

Property

set_font_name()

Font type

'font_name'

set_font_size()

Font size

'font_size'

set_font_color()

Font color

'font_color'

set_bold()

Bold

'bold'

set_italic()

Italic

'italic'

set_underline()

Underline

'underline'

set_font_strikeout()

Strikeout

'font_strikeout'

set_font_script()

Super/Subscript

'font_script'

Alignment formatting methods and properties

Method Name

Description

Property

set_align()

Horizontal align

'align'

set_align()

Vertical align

'valign'

set_rotation()

Rotation

'rotation'

set_text_wrap()

Text wrap

'text_wrap'

set_reading_order()

Reading order

'reading_order'

set_text_justlast()

Justify last

'text_justlast'

set_center_across()

Center across

'center_across'

set_indent()

Indentation

'indent'

set_shrink()

Shrink to fit

'shrink'

Chart Class

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

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

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

worksheet.insert_chart('A7', chart)

XlxsWriter 支持以下图表类型 −

  1. area − 创建一个区域（填充线）样式图表。

  2. bar − 创建一个条形样式（转置直方图）图表。

  3. column − 创建一个列样式（直方图）图表。

  4. line − 创建一个线样式图表。

  5. pie − 创建一个饼样式图表。

  6. doughnut − 创建一个甜甜圈样式图表。

  7. scatter − 创建一个散点样式图表。

  8. stock − 创建一个股票样式图表。

  9. radar − 创建一个雷达样式图表。

Chart 类定义了以下方法 −

add_series(options)

将数据系列添加到图表。可以给出以下属性 −Values、categoriesnameline、borderfill、pattern、gradientdata_labels、points

set_x_axis(options)

设置图表 X 轴选项，包括 name、name_fontnum_font、num_formatline、fill、pattern、gradientmin、maxposition_axislabel_position、label_aligndate_axis、text_axisminor_unit_type、major_unit_type

set_y_axis(options)

设置图表 Y 轴选项，包括 −name、name_fontnum_font、num_formatline、fill、pattern、gradientmin、maxposition_axislabel_position、label_aligndate_axis、text_axisminor_unit_type、major_unit_type

set_size()

此方法用于设置图表的尺寸。可以通过设置宽度和高度或通过设置 x_scale 和 y_scale 来修改图表的大小。

set_title(options)

设置图表标题选项。 Parameters − options (dict) − 图表尺寸选项的词典。 name − 设置图表的名称（标题）。该名称显示在图表上方。 name_font − 设置图表标题的字体属性。 overlay − 允许标题叠加在图表上。 layout − 设置标题在图表中的 (x, y) 位置（相对单位）。

set_legend()

此方法使用以下属性格式化图表图例 −noneposition、font、borderfill、pattern、gradient

Chartsheet Class

XLSX 文件中的图表表是一个仅包含图表而不包含任何其他数据的工作表。通过从工作簿对象调用 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)

请记住，图表表仅可包含一张图表。

Example

以下代码在工作表名称 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()

Output

chartsheet class

Exception Class

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

Sr.No

Exception Classes & Description

1

XlsxWriterException Base exception for XlsxWriter.

2

XlsxFileError 所有文件相关错误的基本异常。

3

XlsxInputError 所有输入数据相关错误的基本异常。

4

FileCreateError 在将 xlsx 文件写入磁盘时遇到文件权限错误或 IO 错误，或 Excel 已打开该文件时发生。

5

UndefinedImageSize 如果图像不包含高度或宽度信息，则与 insert_image() 方法一起引发。Workbook close() 期间引发异常。

6

UnsupportedImageFormat 如果图像不是受支持的文件格式（PNG、JPEG、GIF、BMP、WMF 或 EMF）之一，则引发异常。

7

EmptyChartSeries 当向不包含数据系列的工作表添加图表时，会发生此异常。

8

InvalidWorksheetName 如果工作表名称太长或包含无效字符。

9

DuplicateWorksheetName 当工作表名称已存在时引发此异常。

Exception FileCreateError

假设已经使用 Excel 应用程序打开了名为 hello.xlsx 的工作簿，则以下代码会引发 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'

Handling the Exception

我们可以使用 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

Exception 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 - Cell Notation & Ranges

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

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

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

cell notations1

Cell Notations

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

Example

在以下示例中，使用 Excel 的标准单元格地址将字符串“Hello world”写入 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()

Output

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

cell notations2

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

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])

可以通过使用以下代码中使用的工作表对象的 write_row() 方法来实现相同的结果 −

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

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

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

Example

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

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()

Output

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

cell notations3

Python XlsxWriter - Defined Names

在 Excel 中，可以通过用户定义的名称来识别单元格、公式或单元格范围，该名称可用作变量，以便更容易理解公式的定义。这可以使用工作簿类的 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() 方法的第二个参数是一个 string ，其中包含表的名称，后跟 " ! " 符号，然后使用绝对寻址方案指定单元格区域。在本例中，sheet1 中的区域 A1:E1 命名为 marks 。

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

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

我们还可以在 write_formula() 方法中使用带名称的单元格。在以下代码中，此方法用于计算 rate is a defined_name 中数额的利息。

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)}')

Example

使用 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()

Output

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

ex2 xlsx

Python XlsxWriter - Formula & Function

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

  1. write_formula()

  2. write_array_formula()

  3. write_dynamic_array_formula()

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

The write_formula() Method

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

Example

在下面的示例中，各种语句使用 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()

Output

Excel 文件显示以下结果 −

write formula

The write_array_formula() Method

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

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

Example

在以下示例中，数组公式用于列 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()

Output

以下是使用 MS Excel 打开工作表时的显示方式 −

write array formula

The write_dynamic_array_data() Method

write_dynamic_array_data() 方法在单元格区域中写入一个动态数组公式。在 EXCEL 的 365 版本中引入了动态数组的概念，并且还引入了一些利用动态数组优势的新函数。这些函数有 −

Sr.No

Functions & Description

1

FILTER 过滤数据并返回匹配记录

2

RANDARRAY 生成随机数数组

3

SEQUENCE 生成顺序数字数组

4

SORT Sort range by column

5

SORTBY 通过另一个区域或数组对区域进行排序

6

UNIQUE 从列表或区域中提取唯一值

7

XLOOKUP replacement for VLOOKUP

8

XMATCH MATCH 函数的替代方案

动态数组是其大小可以根据结果而变化的返回值区域。例如，函数 FILTER() 返回一个数组，其大小可以根据筛选结果而变化。

Example

在下面的示例中，数据范围是 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()

Output

请注意，write_dynamic_array_formula() 的公式字符串不必包含大括号。 The resultant hello.xlsx must be opened with Excel 365 app.

write dynamic array data

Python XlsxWriter - Date & Time

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

date format

The set_num_format() Method

由于 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)

The num_format Parameter

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

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

Example

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

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()

Output

工作表在 Excel 软件中看上去如下 −

num format Parameter

write_datetime() and strptime()

XlsxWriter 的工作表对象还有 write_datetime() 方法，该方法在使用 Python 标准库的 datetime 模块获取日期和时间对象时很有用。

strptime() 方法从根据给定格式解析的字符串中返回 datetime 对象。格式化字符串使用的部分代码如下所示 −

%a

Abbreviated weekday name

Sun, Mon

%A

Full weekday name

Sunday, Monday

%d

以零填充的十进制数表示的月中天数

01, 02

%-d

以十进制数表示的月中天数

1, 2..

%b

Abbreviated month name

Jan, Feb

%m

月份为零填充的十进制数字

01, 02

%-m

月份为十进制数字

1, 2

%B

Full month name

January, February

%y

无世纪年份为零填充的十进制数字

99, 00

%-y

无世纪年份为十进制数字

0, 99

%Y

有世纪的年份为十进制数字

2022, 1999

%H

小时（24 小时制）为零填充的十进制数字

01, 23

%-H

小时（24 小时制）为十进制数字

1, 23

%I

小时（12 小时制）为零填充的十进制数字

01, 12

%-I

小时（12 小时制）为十进制数字

1, 12

%p

locale’s AM or PM

AM, PM

%M

分钟为零填充的十进制数字

01, 59

%-M

以十进制数表示的分钟数

1, 59

%S

秒为零填充的十进制数字

01, 59

%-S

以十进制数表示的秒数

1, 59

%c

合适的日期和时间表示

2022 年 9 月 30 日 星期一 07:06:05

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 对象写入工作表。

Example

在以下示例中， 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()

Output

在使用 Excel 打开时，工作表将显示如下内容：

datetime and strptime

Python XlsxWriter - Tables

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

The add_table() Method

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

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

标准“ A1 ”或“ Row/Column ”符号均可用于指定范围。 add_table() 方法可以采用下面的一个或多个可选参数。请注意，除了范围参数之外，其他参数都是可选的。如果不给出，则创建一个空表。

Example

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()

Output

以下是结果 −

add table

header_row

该参数可用于关闭或开启表中的标题行。默认情况下它是打开的。标题行将包含一些默认标题，如 Column 1、Column 2 等等。你可以使用 columns 参数设置必需的标题。

Columns

Example

该属性用于设置列标题。

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()

Output

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

header row

autofilter

该参数默认情况下是 ON。将其设置成 OFF 时，标题行不显示下拉箭头来设置筛选条件。

Name

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

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

Formula

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

Example

在以下示例中，表的名称属性设置为“marklist”。“总计”列 E 的公式执行成绩的总和，并分配给公式子属性的值。

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()

Output

执行以上代码时，工作表将显示 Total 列，其中包含成绩总和。

autofilter

Python XlsxWriter - Applying Filter

在 Excel 中，你可以根据使用逻辑表达式的条件，对表格数据设置筛选器。在 XlsxWriter 的工作表类中，我们有 autofilter() 方法或其用途。该方法的必需参数是单元格区域。这会在标题行中创建下拉选择器。要应用一些条件，我们有两个可用的方法 − filter_column() 或 filter_column_list() 。

Applying Filter Criteria for a Column

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

Example

数据范围中所有不满足筛选条件的行都通过为工作表对象的 set_row() 方法设置隐藏选项为 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()

Output

当我们使用 Excel 打开工作表时，我们会发现只有区域='东部’的行是可见的，其他行被隐藏了（你可以通过清除筛选器来再次显示它们）。

filter criteria1

列参数可以是零索引列号或字符串列名。在 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

Example

在以下示例中，第一列上的第一个筛选器需要区域为西部，第二列筛选器的条件为“ 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()

Output

在 Excel 中，可以在标题 A 和 D 列上看到筛选器图标。下面是过滤后的数据 −

filter criteria2

Applying a Column List Filter

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

ws.filter_column_list(col,list)

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

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

它会导致对数据进行筛选，以便列 C 中的值与列表中的任何项匹配。

Example

在以下示例中，方法 filter_column_list() 用于筛选 rows ，区域等于东部或西部。

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()

Output

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

column list filter1

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

column list filter2

Python XlsxWriter - Fonts & Colors

Working with Fonts

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

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

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

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

Example

要使单元格 bold, underline, italic 或 strike through, 中的文本可以设置，我们可以使用这些属性或相应的函数。在下面的示例中，文本 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()

Output

以下是结果 −

working with fonts1

Example

另一方面，我们也可以像以下示例中那样使用 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()

Output

打开工作表并使用 Excel 查看以上代码的输出 -

working with fonts2

Text Alignment

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

Example

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()

Output

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

text alignment1

Example

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()

Output

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

text alignment2

Cell Background and Foreground Colors

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

Example

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()

Output

上述代码结果如下所示 -

cell background and foreground colors

Python XlsxWriter - Number Formats

在 Excel 中，数字数据的不同格式化选项在 Format Cells 菜单的 Number tab 中提供。

format cells

要用 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。

Example

在以下示例中，数字 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()

Output

使用过的格式代码和格式化数字如下所示 −

formatted number

Python XlsxWriter - Border

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

Working with Cell Border

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

Description

Property

method

Cell border

'border'

set_border()

Bottom border

'bottom'

set_bottom()

Top border

'top'

set_top()

Left border

'left'

set_left()

Right border

'right'

set_right()

Border color

'border_color'

set_border_color()

Bottom color

'bottom_color'

set_bottom_color()

Top color

'top_color'

set_top_color()

Left color

'left_color'

set_left_color()

Right color

'right_color'

set_right_color()

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

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

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

还可以通过调用 set_border() 方法来执行相同操作 -

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

各个边框元素可以通过以下属性或格式方法进行配置 -

  1. set_bottom()

  2. set_top()

  3. set_left()

  4. set_right()

这些边框方法/属性具有一个整数值，该值对应于预定义的样式，如下表所示 -

Index

Name

Weight

Style

0

None

0

1

Continuous

1

-----------

2

Continuous

2

-----------

3

Dash

1

- - - - - -

4

Dot

1

[style="arabic"]. . . . . .

5

Continuous

3

-----------

6

Double

3

===========

7

Continuous

0

-----------

8

Dash

2

- - - - - -

9

Dash Dot

1

- . - . - .

10

Dash Dot

2

- . - . - .

11

Dash Dot Dot

1

- . . - . .

12

Dash Dot Dot

2

- . . - . .

13

SlantDash Dot

2

/ - . / - .

Example

以下代码显示了边框属性是如何使用的。此处，每行都有一个对应于连续加粗的边框样式 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()

Output

工作表在单元格周围显示一个加粗边框。

cell border

Working with Textbox Border

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

设置为 none 的行或边框表示文本框没有边框。 dash_type 参数可以是以下任意值 −

  1. solid

  2. round_dot

  3. square_dot

  4. dash

  5. dash_dot

  6. long_dash

  7. long_dash_dot

  8. long_dash_dot_dot

Example

这是一个显示两个文本框的程序，其中一个具有蓝色实心边框，而第二个盒子具有蓝色 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()

Output

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

textbox borders

Python XlsxWriter - Hyperlinks

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

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

import xlsxwriter

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

workbook.close()

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

Example

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

import xlsxwriter

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

worksheet.write_url('A1', 'https://www.tutorialspoint.com/index.htm')
worksheet.write_url('A3', 'http://localhost:8080')
worksheet.write_url('A5', 'ftp://www.python.org')
worksheet.write_url('A7', 'mailto:dummy@abc.com')

workbook.close()

Output

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

hyperlink1

Example

我们还可以将超链接插入到同一工作簿中的另一个工作表或另一个工作簿中。这是通过在本地 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()

Output

请注意， string 和 tip 参数作为 link 和 tool tip 的备用文本。上面程序的输出如下所示 −

hyperlink2

Python XlsxWriter - Conditional Formatting

Excel 使用“@ {s5}”基于用户定义的条件更改范围内单元格的外观。从条件格式设置菜单中，可以定义涉及各种类型值的条件。

conditional formatting

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

conditional formatting1

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

conditional formatting2

The conditional_format() method

在 XlsxWriter 中，在 Worksheet 类中定义“@ {s6}”方法。若要实现上述显示结果，请按以下代码调用“@ {s7}”方法：

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()

Parameters

“@ {s8}”方法的第一个参数是单元格范围，第二个参数是条件格式设置选项的字典。

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

“@ {s9}”选项是必需参数。其值可以是单元格、日期、文本、公式等。每个参数都有子参数，例如条件、值、格式等。

  1. “@ {s10}”是最常见的条件格式设置类型。在根据简单条件将格式应用于单元格时使用它。

  2. “@ {s11}”参数设置单元格数据将按其评估的条件。除了 between 和 not between 运算符之外的所有逻辑运算符都是 criteria 参数的可能值。

  3. “@ {s12}”参数是形成该规则的条件的运算符。

  4. “@ {s13}”参数是 Format 对象（由“@ {s14}”方法返回）。这定义了要应用于满足条件的单元格的格式化功能，例如字体、颜色等。

date 类型与单元格类型相似，并且使用相同的标准和值。但是，值参数应给为 datetime 对象。

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

Example

当使用 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()

Output

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

parameters

Python XlsxWriter - Adding Charts

Excel 最重要的功能之一是其将数据转换为图表的能力。图表是数据的可视化表示。可以从 Chart 菜单生成不同类型的图表。

insert chart

为了以编程方式生成图表，XlsxWriter 库具有一个 Chart 类。它的对象可以通过调用 Workbook 类的 add_chart() 方法获得。接下来，借助 add_series() 方法，它与工作表中的数据范围关联起来。然后，使用其 insert_chart() 方法在工作表中插入图表对象。

Example

下面给出了用于显示简单柱状图的代码。

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()

Output

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

add series

add_series() 方法具有以下附加参数 −

  1. Values − 这是最重要的必需属性选项。它将图表与它显示的工作表数据链接起来。

  2. Categories − 这设置图表分类标签。如果未给出，图表将只从 1…n 中假设一个顺序序列。

  3. Name − 为序列设置名称。此名称显示在公式栏中。

  4. Line − 设置序列线型的属性，例如颜色和宽度。

  5. Border − 设置序列的边框属性，例如颜色和样式。

  6. Fill − 设置序列的纯色填充属性，例如颜色。

  7. Pattern − 设置序列的图案填充属性。

  8. Gradient − 设置序列的渐变填充属性。

  9. data_labels − 设置序列的数据标签。

  10. 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],
]

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

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

第二个数据序列也引用列 A 中的姓名作为分类，列 C 以 heading 作为 Maths 作为 values 属性。

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

Example

以下是完整的示例代码 −

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()

Output

基于它的工作表和图表显示如下 −

add series1

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

Example

以下是 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()

Output

执行代码并打开 Hello.xlsx 。现在， column 图表显示 data 标签。

hello xlsx

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

XlsxWriter 支持以下类型的图表 −

  1. Area − 创建面积（填充线）样式图表。

  2. Bar − 创建条形样式（转置直方图）图表。

  3. Column − 创建列样式（直方图）图表。

  4. Line − 创建线样式图表。

  5. Pie − 创建饼图样式图表。

  6. Doughnut − 创建甜甜圈样式图表。

  7. Scatter − 创建散点图样式图表。

  8. Stock − 创建股票样式图表。

  9. 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 值用于水平/垂直缩放图表。说明字段可用于为图表指定说明或“替代文本”字符串。

decorative 参数用于将图表标记为装饰性的，因此对于自动屏幕阅读器而言是不可理解的。须将其设置为 True/False。最后， object_position 参数控制图表的对象定位。它允许以下值 −

  1. 1 - 使用单元格移动和调整大小（默认）。

  2. 2 - 仅移动，不随单元格调整大小。

  3. 3 - 不随单元格移动或调整大小。

Python XlsxWriter - Chart Formatting

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

  1. 设置并设置图表标题格式

  2. 设置 X 和 Y 轴标题以及其他参数

  3. Configure the chart legends

  4. Chat layout options

  5. Setting borders and patterns

Title

您可以通过调用图表对象的 set_title() 方法来设置和配置图表对象的标题。可以是各种参数如下 −

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

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

  3. Overlay − 允许标题覆盖在图表上。

  4. Layout − 设置图表中标题的 (x, y) 位置（相对单位）。

  5. None − Excel 会添加自动图表标题。none 选项关闭此默认标题。它也会关闭所有其他 set_title() 选项。

X and Y axis

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

  1. name − 设置轴的标题或标题。

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

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

  4. num_format − 设置轴的数字格式。

  5. major_gridlines − 配置轴的主要网格线。

  6. 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}
   }
)

Example

在完整代码中添加以上代码段。现在如下所示： −

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()

Output

图表显示 title 和 axes 标题，如下所示： −

title axes

Python XlsxWriter - Chart Legends

根据图表类型，数据以不同颜色或图案的形式在柱状图、条形图、折线图、圆弧图等形式中进行视觉呈现。图表图例能够轻松直观地显示哪种颜色/图案对应于哪种数据系列。

Working with Chart Legends

为了设置图例并配置其属性，例如位置和字体，XlsxWriter 提供了 set_legend() 方法。其属性为 −

  1. None - 在 Excel 中，图表图例默认打开。none=True 选项可关闭图表图例。

  2. Position - 设置图表图例的位置。可以设置为顶部、底部、左部、右部、无。

  3. Font - 设置图表图例的字体属性（如名称、大小、粗体、斜体等）。

  4. Border - 设置图例的边框属性，例如颜色和样式。

  5. Fill - 设置图例的实心填充属性，例如颜色。

  6. Pattern - 设置图例的图案填充属性。

  7. Gradient - 设置图例的渐变填充属性。

某些图例属性可用于图表，如下所示 −

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

Example

以下是按上述特性显示图例的完整代码 −

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()

Output

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

legend

在图表中，对应于 physics 和 maths 的列以不同的颜色显示。图表右侧的小色块符号是图例，用来显示哪种颜色对应于 physics 或 maths 。

Python XlsxWriter - Bar Chart

条形图类似于柱形图，不同之处在于数据用成比例的水平条表示，而不是垂直列。要生成条形图，方法 add_chart() 的类型参数必须设置为“bar”。

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

条形图如下所示 −

bar chart1

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

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

Example

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

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()

Output

输出文件将如下所示：

bar chart2

Python XlsxWriter - Line Chart

折线图直观显示了一系列数据点，这些数据点通过一条直线在 X 轴上连接起来。这是一条独立轴，因为 X 轴上的值不依赖于纵向 Y 轴。

Y 轴是一条从属轴，因为其值依赖于 X 轴，结果是水平延伸的折线。

Working with XlsxWriter Line Chart

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

Example

在以下示例中，我们绘制 line chart ，显示了六个月内两种产品的销售数字。通过 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()

Output

执行以上程序后，以下是如何：XlsxWriter 生成折线图 −

sales analysis

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

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'},})

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

sales analysis1

折线图还支持 stacked 和 percent_stacked 子类型。

sales analysis2

Python XlsxWriter - Pie Chart

pie chart 是单个数据序列在圆环中的表示，这个圆环被分成对应于序列中每个数据项目的切片。在饼图中，每个切片的弧长与它所表示的数量成正比。在以下工作表中，按季度划分的某个产品的销售额以饼图的形式显示。

pie chart of quarterly sales

Working with XlsxWriter Pie Chart

使用 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 的图表对象，并且单元格范围 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'})

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

Example

生成饼图的完整程序如下 −

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()

Output

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

pie chart of quarterly sales1

Doughnut Chart

doughnut chart 是饼图的一个变体，其中心有一个孔，它以圆弧而不是切片显示类别。两者都使人们一目了然地掌握整体与部分之间的关系。只需将图表类型更改为 doughnut 。

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

以上示例中数据的环形图显示如下 −

doughnut chart

Python XlsxWriter - Sparklines

sparkline 是一种小型图表，没有坐标轴或坐标。它提供某个参数变化的表示形式。普通图表尺寸更大，具有许多说明性特征，例如标题、图例、数据标签等，并且与附带文本分开。另一方面，迷你图尺寸很小，可以嵌入文本中，也可以嵌入具有其上下文的文本或工作表单元格中。

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

火花图有三种类型 -

  1. line - 类似折线图

  2. column - 类似柱状图

  3. win_loss - 是否每个值都是正值（赢）或负值（输）。

Working with XlsxWriter Sparklines

XlsxWriter 模块有 add_sparkline() 方法。它基本上需要火花图的单元格位置和要表示为火花图的数据范围。此外，还可以以字典对象的形式提供其他参数，例如类型、样式等。默认情况下，类型为折线。

Example

以下程序表示在折线和柱状火花图中相同的数字列表。

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()

Output

火花图被添加到单元格 K 中。

sparklines

属性为 -

  1. range - 是必需的参数。它指定火花图将绘制的单元格数据范围。

  2. type - 指定火花图的类型。有 3 种可用的火花图类型：折线、柱状和盈亏。

  3. markers - 为折线样式火花图开启标记

  4. style - 在 MS Excel 中定义的火花图样式。有 36 种样式类型。

  5. negative_points - 如果设置为 True，则突出显示火花图中的负值点。

Example

以下程序生成一个有 markers 的 line sparkline 和一个突出显示负值点的 win_loss sparkline 。

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()

Output

K1 中的折线火花图有标记。K5 中的火花图显示负值突出显示。

line sparkline

Example – Style Types

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

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()

Output

它将生成如下输出：

column sparkline

Python XlsxWriter - Data Validation

Excel 中的 Data validation 特性使您可以控制用户在单元格中输入的内容。您可以使用此特性确保单元格中的值是处于特定范围内的数字/日期、具有所需长度的文本，或以下拉菜单形式显示用于从其中选择值的列表。

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

data validation

在第二个选项卡中，设置当用户光标所在期望单元格时闪现的消息，在本例中为“输入 1 到 25 之间的任意整数”。您还可以设置消息标题；在本例中为“年龄”。

data validation1

第三个选项卡允许您定义任意错误消息，如果验证条件未能满足，则会闪现此错误消息。

data validation2

当用户将光标置于 I10（为此设置了验证）中时，您会看到输入消息。

age1

当输入的数字不在该范围内时，错误消息将闪烁。

age2

Working with XlsxWriter Data Validation

您可以使用 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 参数，该参数是一个包含以下参数的字典：

  1. validate ：用于设置要验证的数据类型。允许的值为整数、小数、列表、日期、时间、长度等。

  2. criteria ：用于设置验证条件。它可以设置为任意逻辑运算符，包括 between / not between 、 == 、 != 、 &lt; 、 &gt; 、 &#8656; 、 &gt;= 等。

  3. value ：设置条件所应用的限定值。始终需要此值。使用列表验证时，它给定为以逗号分隔的值字符串。

  4. input_title − 用于当光标放置于目标单元格时设置输入消息的标题。

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

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

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

Example

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',
   }
)

Example

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

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()

Output

当光标放置在 I10 单元格中时出现下拉列表 −

dropdown list

Example

如果您想要让用户输入长度大于 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()

Output

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

string

Python XlsxWriter - Outlines & Grouping

在 Excel 中，你可以对具有相同特定列（或行）值的行或列进行分组，以便可以用单击隐藏或显示它们。此功能称为 outlines and grouping 。它有助于显示小计或合计。可以在 MS excel 软件的 Data→Outline 组中找到此功能。

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

outline

该工作表显示按项目划分的销售小计，最后是总计。该工作表左侧显示大纲级别。原始数据位于第 3 级，小计位于第 2 级，总计位于第 1 级。

item and sales

Working with Outlines and Grouping

若要使用 XlsxWriter 执行此操作，我们需要使用“@ {s0}”方法的 level 属性。数据行设置在第 2 级。

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

小计行位于第 1 级。

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

我们使用“@ {s1}”函数计算和显示一组中的销售额总和。

Example

以下是完整代码 −

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()

Output

运行代码并使用 Excel 打开“@ {s2}”。正如我们所见，大纲显示在左侧。

outlines

在各个级别，减号表示可以折叠行并仅显示小计行。

subtotal row

此图显示“@ {s3}”中的所有行已折叠。它现在在大纲中显示正号，这意味着可以展开数据行。如果您单击“@ {s4}”处的减号，则工作表上将仅保留总计。

grand total

Python XlsxWriter - Freeze & Split Panes

The freeze_panes() method

XlsxWriter 库中的 freeze_panes() 方法将工作表分成水平或垂直区域，称为窗格，并“冻结”其中任何一格或两格，这样，如果我们向下或向右滚动，窗格（分别为顶部或左侧）将保持不动。

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

Example

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

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()

Output

接下来，我们冻结 top row pane 。结果，在打开工作表后，如果单元格指针向下滚动，则顶部行始终显示在工作表上。

top row

同样，我们可以让 first column 固定。

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

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

column a

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

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

打开最终工作表并滚动单元格光标。你会发现，已用粗体和背景色格式化的顶部行和最左侧列中的行号和列号始终可见。

freeze panes

The split_panes() method

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 列找到拆分器。您可以向垂直拆分器的左右方向以及水平拆分的顶部和底部滚动窗格。请注意，其他窗格将保持不变。

Example

以下是创建分隔符的完整代码示例，其下方是显示的输出 −

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()

Output

运行该代码并使用 Excel 打开 hello.xlsx 。如我们所见，工作表在第 10 行和第 7 列被分隔成不同的窗格。

split panes

Python XlsxWriter - Hide/Protect Worksheet

工作表对象 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()

它将创建以下工作表 −

hide

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

sheet2.activate()
sheet1.hide()

Hide Specific Rows or Columns

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

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

Example

请考虑以下程序 −

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()

Output

执行上述代码后，C、D 和 E 列在下方工作表中不可见 −

hide column

同理，借助 set_row() 方法，我们可以隐藏带有 hidden 参数的行。

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

以下是结果 −

hide row

Python XlsxWriter - Textbox

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

Working with XlsxWriter – Textbox

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

Example

以下代码在单元格 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()

Output

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

text box

Textbox Options – fill

默认情况下，文本框大小为 192X120 像素（对应 3 列和 6 行）。此大小可以通过 width 和 height 参数进行更改，这两个参数都以像素为单位。 inset_textbox() 方法接受的参数之一是 fill 参数。它采用十六进制中的预定义颜色名称或颜色表示作为值。

Example

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

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 box with multiple lines

Textbox Options – 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()

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

text rotation

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

  1. "1" - 随单元格移动和调整大小（默认值）。

  2. "2" - 移动但不要随单元格调整大小。

  3. "3" - 不随单元格移动或调整大小。

Python XlsxWriter - Insert Image

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

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

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

Parameter

Default

'x_offset'

0,

'y_offset'

0,

'x_scale'

1,

'y_scale'

1,

'object_position'

2,

'image_data'

None

'url'

None

'description'

None

'decorative'

False

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

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

Example

下列程序从当前文件夹中的文件中提取图像数据，并将其用作 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()

Output

以下是生成的工作表的视图 −

insert image

Python XlsxWriter - Page Setup

工作表 page setup 方法与工作表在打印时显示有关。这些工作表方法控制方向、纸张大小、边距等。

set_landscape()

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

set_portrait()

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

set_page_view()

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

set_paper()

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

以下是部分纸张样式和索引值 −

Index

Paper format

Paper size

0

Printer default

Printer default

1

Letter

8 1/2 x 11 英寸

2

Letter Small

8 1/2 x 11 英寸

3

Tabloid

11 x 17 in

4

Ledger

17 x 11 in

5

Legal

8 1/2 x 14 英寸

6

Statement

5 1/2 x 8 1/2 英寸

7

Executive

7 1/4 x 10 1/2 英寸

8

A3

297 x 420 mm

9

A4

210 x 297 mm

set_margin()

此方法用于设置工作表在打印时的边距。它接受值为英寸的 left、right、top 和 bottom 参数。所有参数都是可选的。左侧和右侧参数默认值为 0.7，上方和下方默认值为 0.75。

Python XlsxWriter - Header & Footer

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

Control

Category

Description

&L

Justification

Left

&C

Center

&R

Right

&P

Information

Page number

&N

Total number of pages

&D

Date

&T

Time

&F

File name

&A

Worksheet name

&Z

Workbook path

&fontsize

Font

Font size

&"font,style"

Font name and style

&U

Single underline

&E

Double underline

&S

Strikethrough

&X

Superscript

&Y

Subscript

&[Picture]

Images

Image placeholder

&G

Same as &[Picture]

&&

Misc.

Literal ampersand "&"

Example

以下代码使用 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()

Output

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

header and footer

Python XlsxWriter - Cell Comments

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

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

comment

若要添加和设置批注的格式，XlsxWriter 具有 add_comment() 方法。该方法的两个必需参数是 cell location （A1 类型或行号和列号）和 comment text 。

Example

以下是一个简单示例：

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()

Output

当我们打开工作簿，然后将光标置于 C3 单元格顶部右侧的标记上时，将看到一个带有批注。

comment1

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

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

Example

在以下代码中，放置了三条批注。但是，C3单元格中的一个已配置为可见属性设置为 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()

Output

它将生成如下输出：

show comment

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

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

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

worksheet.set_comments_author('Tutorialspoint')

它将生成如下输出：

set comments author

Python XlsxWriter - Working with Pandas

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

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

pip3 install pandas

Using XlsxWriter with Pandas

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

Example

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()

Output

创建的工作表如下所示 −

pandas dataframe

Adding Charts to Pandas Dataframe

就像我们获取 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()

Example

以下代码使用 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()

Output

数据和柱形图如下所示：

column chart

Writing Dataframe to Excel Table

类似地，可以将数据框写入到 Excel 表对象中。此处的 data 数据框是从 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 = 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()

Example

下面的完整代码可以将 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()

Output

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

autofilter

Python XlsxWriter - VBA Macro

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

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

如下图所示，通过转到“视图→宏→录制宏”单击录制宏按钮，为宏提供一个合适的名称并执行要记录的所需操作。在步骤结束之后，停止录制。分配一个所需的快捷键，以便可以重复录制的操作并按下该快捷键。

record macro

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

macro name

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

visual basic of applications

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

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

Example

现在可以使用 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()

Output

当执行以上代码时，将创建名为 testvba.xlsm 的已启用宏的工作簿。打开它，然后单击按钮。它将导致弹出框按如所示。

vba project