Python Xlsxwriter 简明教程
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 is a Python module for creating spreadsheet files in Excel 2007 (XLSX) format that uses open XML standards. XlsxWriter module has been developed by John McNamara. Its earliest version (0.0.1) was released in 2013. The latest version 3.0.2 was released in November 2021. The latest version requires Python 3.4 or above.
XlsxWriter Features
XlsxWriter 的一些重要功能包括: −
Some of the important features of XlsxWriter include −
-
Files created by XlsxWriter are 100% compatible with Excel XLSX files.
-
XlsxWriter provides full formatting features such as Merged cells, Defined names, conditional formatting, etc.
-
XlsxWriter allows programmatically inserting charts in XLSX files.
-
Autofilters can be set using XlsxWriter.
-
XlsxWriter supports Data validation and drop-down lists.
-
Using XlsxWriter, it is possible to insert PNG/JPEG/GIF/BMP/WMF/EMF images.
-
With XlsxWriter, Excel spreadsheet can be integrated with Pandas library.
-
XlsxWriter also provides support for adding Macros.
-
XlsxWriter has a Memory optimization mode for writing large files.
Python XlsxWriter - Environment Setup
Installing XlsxWriter using PIP
安装 XlsxWriter 最简单且推荐的方法是使用 PIP 安装程序。使用以下命令安装 XlsxWriter(最好在虚拟环境中)。
The easiest and recommended method of installing XlsxWriter is to use PIP installer. Use the following command to install XlsxWriter (preferably in a virtual environment).
pip3 install xlsxwriter
Installing from a Tarball
另一种选择是从其托管在 https://github.com/jmcnamara/XlsxWriter/ 的源代码中安装 XlsxWriter。下载最新的源 tarball,并使用以下命令安装库 −
Another option is to install XlsxWriter from its source code, hosted at https://github.com/jmcnamara/XlsxWriter/. Download the latest source tarball and install the library using the following commands −
$ 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 存储库并从中安装。
You may also clone the GitHub repository and install from it.
$ git clone https://github.com/jmcnamara/XlsxWriter.git
$ cd XlsxWriter
$ python setup.py install
要确认 XlsxWriter 已正确安装,请从 Python 提示符检查其版本 −
To confirm that XlsxWriter is installed properly, check its version from the Python prompt −
>>> import xlsxwriter
>>> xlsxwriter.__version__
'3.0.2'
Python XlsxWriter - Hello World
Getting Started
第一个测试模块/库正常运行的程序通常是写 Hello world 消息。下列程序创建一个带 .XLSX 扩展名的文件。xlsxwriter 模块中 Workbook 类的对象对应于当前工作目录中的电子表格文件。
The first program to test if the module/library works correctly is often to write Hello world message. The following program creates a file with .XLSX extension. An object of the Workbook class in the xlsxwriter module corresponds to the spreadsheet file in the current working directory.
wb = xlsxwriter.Workbook('hello.xlsx')
接下来,调用 Workbook 对象的 add_worksheet() 方法在其中插入一个新工作表。
Next, call the add_worksheet() method of the Workbook object to insert a new worksheet in it.
ws = wb.add_worksheet()
我们现在可以通过调用工作表对象的 write() 方法在 A1 单元格中添加 Hello World 字符串。它需要两个参数:单元格地址和字符串。
We can now add the Hello World string at A1 cell by invoking the write() method of the worksheet object. It needs two parameters: the cell address and the string.
ws.write('A1', 'Hello world')
Python XlsxWriter - Important Classes
XlsxWriter 库包括以下类别。这些类别中定义的所有方法都允许对 XLSX 文件以编程方式执行不同的操作。这些类别是 -
The XlsxWriter library comprises of following classes. All the methods defined in these classes allow different operations to be done programmatically on the XLSX file. The classes are −
-
Workbook class
-
Worksheet class
-
Format class
-
Chart class
-
Chartsheet class
-
Exception class
Workbook Class
这是 XlsxWriter 模块公开的主要类别,它是你唯一需要直接实例化的类别。它表示在磁盘上写好的 Excel 文件。
This is the main class exposed by the XlsxWriter module and it is the only class that you will need to instantiate directly. It represents the Excel file as it is written on a disk.
wb=xlsxwriter.Workbook('filename.xlsx')
工作簿类别定义了以下方法 -
The Workbook class defines the following methods −
Sr.No |
Workbook Class & Description |
1 |
add_worksheet() Adds a new worksheet to a workbook. |
2 |
add_format() Used to create new Format objects which are used to apply formatting to a cell. |
3 |
add_chart() Creates a new chart object that can be inserted into a worksheet via the insert_chart() Worksheet method |
4 |
add_chartsheet() Adds a new chartsheet to a workbook. |
5 |
close() Closes the Workbook object and write the XLSX file. |
6 |
define_name() Creates a defined name in the workbook to use as a variable. |
7 |
add_vba_project() Used to add macros or functions to a workbook using a binary VBA project file. |
8 |
worksheets() Returns a list of the worksheets in a workbook. |
Worksheet Class
Worksheet 类表示 Excel 工作表。此类的对象处理操作,例如将数据写入单元格或格式化工作表布局。它通过从 add_worksheet() 对象调用 Workbook() 方法创建。
The worksheet class represents an Excel worksheet. An object of this class handles operations such as writing data to cells or formatting worksheet layout. It is created by calling the add_worksheet() method from a Workbook() object.
Worksheet 对象可以访问以下方法:
The Worksheet object has access to the following methods −
write() |
Writes generic data to a worksheet cell. Parameters − row − The cell row (zero indexed). col − The cell column (zero indexed). *args − The additional args passed to the sub methods such as number, string and cell_format. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. |
write_string() |
Writes a string to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − String truncated to 32k characters. |
write_number() |
Writes numeric types to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. |
write_formula() |
Writes a formula or function to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). formula (string) −Formula to write to cell. cell_format (Format) − Optional Format object. value − Optional result. The value if the formula was calculated. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. |
insert_image() |
Used to insert an image into a worksheet. The image can be in PNG, JPEG, GIF, BMP, WMF or EMF format. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). filename − Image filename (with path if required). Returns − 0 − Success -1 − Row or column is out of worksheet bounds. |
insert_chart() |
Used to insert a chart into a worksheet. A chart object is created via the Workbook add_chart() method. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). chart − A chart object. |
conditional_format() |
Used to add formatting to a cell or range of cells based on user-defined criteria. Parameters − first_row (int) − The first row of the range.(All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int)− The last col of the range. options (dict) − Conditional formatting options. must be a dictionary containing the parameters that describe the type and style of the conditional format Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − Incorrect parameter or option. |
add_table() |
Used to group a range of cells into an Excel Table. Parameters − first_row (int) − The first row of the range. (All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int) − The last col of the range. options (dict) − Table formatting options. |
autofilter() |
Set the auto-filter area in the worksheet. It adds drop down lists to the headers of a 2D range of worksheet data. User can filter the data based on simple criteria. Parameters − first_row (int) − The first row of the range. (All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int)− The last col of the range. |
Format Class
通过调用工作簿 add_format() 方法创建格式对象。此对象可用方法和属性与字体、颜色、图案、边框、对齐方式和数字格式相关。
Format objects are created by calling the workbook add_format() method. Methods and properties available to this object are related to fonts, colors, patterns, borders, alignment and number formatting.
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() 方法创建的,其中指定了图表类型。
A chart object is created via the add_chart() method of the Workbook object where the chart type is specified.
chart = workbook.add_chart({'type': 'column'})
通过调用 insert_chart() 方法在工作表中插入 chart 对象。
The chart object is inserted in the worksheet by calling insert_chart() method.
worksheet.insert_chart('A7', chart)
XlxsWriter 支持以下图表类型 −
XlxsWriter supports the following chart types −
-
area − Creates an Area (filled line) style chart.
-
bar − Creates a Bar style (transposed histogram) chart.
-
column − Creates a column style (histogram) chart.
-
line − Creates a Line style chart.
-
pie − Creates a Pie style chart.
-
doughnut − Creates a Doughnut style chart.
-
scatter − Creates a Scatter style chart.
-
stock − Creates a Stock style chart.
-
radar − Creates a Radar style chart.
Chart 类定义了以下方法 −
The Chart class defines the following methods −
add_series(options) |
Add a data series to a chart. Following properties can be given − Values, categoriesnameline, borderfill , pattern , gradientdata_labels, points |
set_x_axis(options) |
Set the chart X-axis options including 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) |
Set the chart Y-axis options including − name, name_fontnum_font, num_formatline, fill, pattern, gradientmin, maxposition_axislabel_position, label_aligndate_axis, text_axisminor_unit_type, major_unit_type |
set_size() |
This method is used to set the dimensions of the chart. The size of the chart can be modified by setting the width and height or by setting the x_scale and y_scale. |
set_title(options) |
Set the chart title options. Parameters − options (dict) − A dictionary of chart size options. name − Set the name (title) for the chart. The name is displayed above the chart. name_font − Set the font properties for the chart title. overlay − Allow the title to be overlaid on the chart. layout − Set the (x, y) position of the title in chart relative units. |
set_legend() |
This method formats the chart legends with the following properties − noneposition, font, borderfill, pattern, gradient |
Chartsheet Class
XLSX 文件中的图表表是一个仅包含图表而不包含任何其他数据的工作表。通过从工作簿对象调用 add_chartsheet() 方法来创建新的 chartsheet 对象−
A chartsheet in a XLSX file is a worksheet that only contains a chart and no other data. a new chartsheet object is created by calling the add_chartsheet() method from a Workbook object −
chartsheet = workbook.add_chartsheet()
Chartsheet 类的一些功能类似于数据工作表,例如选项卡选择、标题、页脚、边距和打印属性。但是,它的主要目的是显示单个图表,而普通数据工作表可以嵌入一个或多个图表。
Some functionalities of the Chartsheet class are similar to that of data Worksheets such as tab selection, headers, footers, margins, and print properties. However, its primary purpose is to display a single chart, whereas an ordinary data worksheet can have one or more embedded charts.
chartsheet 图表的数据必须出现在单独的工作表上。因此,它总是在使用 set_chart() 方法创建至少一个数据工作表的同时创建。
The data for the chartsheet chart must be present on a separate worksheet. Hence it is always created along with at least one data worksheet, using set_chart() method.
chartsheet = workbook.add_chartsheet()
chart = workbook.add_chart({'type': 'column'})
chartsheet.set_chart(chart)
请记住,图表表仅可包含一张图表。
Remember that a Chartsheet can contain only one chart.
Example
以下代码在工作表名称 sheet1 中写入数据系列,但会打开一个新的图表表,以便基于 sheet1 中的数据添加柱形图。
The following code writes the data series in the worksheet names sheet1 but opens a new chartsheet to add a column chart based on the data in 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()
Exception Class
XlsxWriter 会识别可使用 Python 的错误处理技术来捕获的各种运行时错误或异常,以避免 Excel 文件损坏。XlsxWriter 中的异常类如下 −
XlsxWriter identifies various run-time errors or exceptions which can be trapped using Python’s error handling technique so as to avoid corruption of Excel files. The Exception classes in XlsxWriter are as follows −
Sr.No |
Exception Classes & Description |
1 |
XlsxWriterException Base exception for XlsxWriter. |
2 |
XlsxFileError Base exception for all file related errors. |
3 |
XlsxInputError Base exception for all input data related errors. |
4 |
FileCreateError Occurs if there is a file permission error, or IO error, when writing the xlsx file to disk or if the file is already open in Excel. |
5 |
UndefinedImageSize Raised with insert_image() method if the image doesn’t contain height or width information. The exception is raised during Workbook close(). |
6 |
UnsupportedImageFormat Raised if the image isn’t one of the supported file formats: PNG, JPEG, GIF, BMP, WMF or EMF. |
7 |
EmptyChartSeries This exception occurs when a chart is added to a worksheet without a data series. |
8 |
InvalidWorksheetName if a worksheet name is too long or contains invalid characters. |
9 |
DuplicateWorksheetName This exception is raised when a worksheet name is already present. |
Exception FileCreateError
假设已经使用 Excel 应用程序打开了名为 hello.xlsx 的工作簿,则以下代码会引发 FileCreateError −
Assuming that a workbook named hello.xlsx is already opened using Excel app, then the following code will raise a FileCreateError −
import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
workbook.close()
当运行此程序时,会出现如下错误消息 −
When this program is run, the error message is displayed as below −
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 的异常处理机制来达到此目的。
We can use Python’s exception handling mechanism for this purpose.
import xlsxwriter
try:
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()
workbook.close()
except:
print ("The file is already open")
现在会显示自定义错误消息。
Now the custom error message will be displayed.
(xlsxenv) E:\xlsxenv>python ex34.py
The file is already open
Exception EmptyChartSeries
在添加带有数据系列的图表时引发的异常的另一个情况。
Another situation of an exception being raised when a chart is added with a data series.
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 例外 -
This leads to EmptyChartSeries exception −
xlsxwriter.exceptions.EmptyChartSeries: Chart1 must contain at least one data series.
Python XlsxWriter - Cell Notation & Ranges
工作簿中的每个工作表都是一个数字巨大的单元格网格,每个单元格可以存储一个数据 - 值或公式。网格中的每个单元格由其行号和列号标识。
Each worksheet in a workbook is a grid of a large number of cells, each of which can store one piece of data - either value or formula. Each Cell in the grid is identified by its row and column number.
在 Excel 的标准单元格寻址中,列用字母标识,A、B、C、…、Z、AA、AB 等等,而行从 1 开始编号。
In Excel’s standard cell addressing, columns are identified by alphabets, A, B, C, …., Z, AA, AB etc., and rows are numbered starting from 1.
每个单元格的地址都是字母数字的,其中字母部分对应于列,数字部分对应于行。例如,地址“C5”指向列“C”和行号“5”中的单元格。
The address of each cell is alphanumeric, where the alphabetic part corresponds to the column and number corresponding to the row. For example, the address "C5" points to the cell in column "C" and row number "5".

Cell Notations
标准 Excel 使用字母列和基于 1 的行的字母数字序列。XlsxWriter 支持标准 Excel 表示法 ( A1 表示法) 以及 Row-column 表示法,该表示法对行和列都使用基于零的索引。
The standard Excel uses alphanumeric sequence of column letter and 1-based row. XlsxWriter supports the standard Excel notation (A1 notation) as well as Row-column notation which uses a zero based index for both row and column.
Example
在以下示例中,使用 Excel 的标准单元格地址将字符串“Hello world”写入 A1 单元格,而使用行-列表示法将“Welcome to XLSXWriter”写入单元格 C5。
In the following example, a string 'Hello world' is written into A1 cell using Excel’s standard cell address, while 'Welcome to XLSXWriter' is written into cell C5 using row-column notation.
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 文件。
Open the hello.xlsx file using Excel software.

编号的 row-column 表示法在以编程方式引用单元格时特别有用。在以下代码中,列表中的数据必须写入工作表中的单元格范围。这是通过两个嵌套循环实现的,外部循环表示行号,内部循环表示列号。
The numbered row-column notation is especially useful when referring to the cells programmatically. In the following code data in a list of lists has to be written to a range of cells in a worksheet. This is achieved by two nested loops, the outer representing the row numbers and the inner loop for column numbers.
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() 方法来实现相同的结果 −
The same result can be achieved by using write_row() method of the worksheet object used in the code below −
for row in range(len(data)):
ws.write_row(6+row,0, data[row])
工作表对象具有 add_table() 方法,该方法将数据写入范围,并转换为 Excel 范围,在顶部行中显示自动筛选下拉箭头。
The worksheet object has add_table() method that writes the data to a range and converts into Excel range, displaying autofilter dropdown arrows in the top row.
ws.add_table('G6:J9', {'data': data, 'header_row':True})
Example
可以通过以下代码验证以上所有三个代码的输出,并显示在以下图形中 -
The output of all the three codes above can be verified by the following code and displayed in the following figure −
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()
Python XlsxWriter - Defined Names
在 Excel 中,可以通过用户定义的名称来识别单元格、公式或单元格范围,该名称可用作变量,以便更容易理解公式的定义。这可以使用工作簿类的 define_name() 方法实现。
In Excel, it is possible to identify a cell, a formula, or a range of cells by user-defined name, which can be used as a variable used to make the definition of formula easy to understand. This can be achieved using the define_name() method of the Workbook class.
在以下代码片段中,我们有一个由数字组成的单元格范围。这个范围已命名为 marks。
In the following code snippet, we have a range of cells consisting of numbers. This range has been given a name as 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 。
If the name is assigned to a range of cells, the second argument of define_name() method is a string with the name of the sheet followed by "!" symbol and then the range of cells using the absolute addressing scheme. In this case, the range A1:E1 in sheet1 is named as marks.
此名称可用于任何公式。例如,我们计算由名称 marks 识别的范围中数字的和。
This name can be used in any formula. For example, we calculate the sum of numbers in the range identified by the name marks.
ws.write('F1', '=sum(marks)')
我们还可以在 write_formula() 方法中使用带名称的单元格。在以下代码中,此方法用于计算 rate is a defined_name 中数额的利息。
We can also use the named cell in the write_formula() method. In the following code, this method is used to calculate interest on the amount where the 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() 方法来代替循环 -
We can also use write_array_formula() method instead of the loop in the above code −
ws.write_array_formula('D7:D9' , '{=rate/100*(A7:A9)}')
Example
使用 define_name() 方法的完整代码如下所示 -
The complete code using *define_name() * method is given below −
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()
Python XlsxWriter - Formula & Function
Worksheet 类提供了三种方法来使用公式。
The Worksheet class offers three methods for using formulas.
-
write_formula()
-
write_array_formula()
-
write_dynamic_array_formula()
所有这些方法都用于将公式和函数分配给单元格。
All these methods are used to assign formula as well as function to a cell.
The write_formula() Method
write_formula() 方法需要单元格的地址和包含有效 Excel 公式的字符串。在公式字符串中,仅接受 A1 样式的地址表示法。但是,单元格地址参数可以是标准 Excel 类型,也可以是零为基础的行和列号表示法。
The write_formula() method requires the address of the cell, and a string containing a valid Excel formula. Inside the formula string, only the A1 style address notation is accepted. However, the cell address argument can be either standard Excel type or zero based row and column number notation.
Example
在下面的示例中,各种语句使用 write_formula() 方法。第一个使用标准 Excel 表示法分配公式。第二个语句使用行和列号来指定设置公式的目标单元格的地址。在第三个示例中, IF() 函数被分配给 G2 单元格。
In the example below, various statements use write_formula() method. The first uses a standard Excel notation to assign a formula. The second statement uses row and column number to specify the address of the target cell in which the formula is set. In the third example, the IF() function is assigned to G2 cell.
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()
The write_array_formula() Method
write_array_formula() 方法用于将公式扩展到某个范围。在 Excel 中,数组公式对一组值执行计算。它可以返回一个值或一系列值。
The write_array_formula() method is used to extend the formula over a range. In Excel, an array formula performs a calculation on a set of values. It may return a single value or a range of values.
数组公式通过公式周围的一对大括号 {=SUM(A1:B1*A2:B2)} 来表示。该范围可以通过范围中第一个和最后一个单元格的行号和列号(例如 0,0, 2,2)或字符串表示法 'A1:C2' 来指定。
An array formula is indicated by a pair of braces around the formula − {=SUM(A1:B1*A2:B2)}. The range can be either specified by row and column numbers of first and last cell in the range (such as 0,0, 2,2) or by the string representation 'A1:C2'.
Example
在以下示例中,数组公式用于列 E、F 和 G 以计算范围 B2:D4 中分数的总计、百分比和结果。
In the following example, array formulas are used for columns E, F and G to calculate total, percent and result from marks in the range 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()
The write_dynamic_array_data() Method
write_dynamic_array_data() 方法在单元格区域中写入一个动态数组公式。在 EXCEL 的 365 版本中引入了动态数组的概念,并且还引入了一些利用动态数组优势的新函数。这些函数有 −
The write_dynamic_array_data() method writes an dynamic array formula to a cell range. The concept of dynamic arrays has been introduced in EXCEL’s 365 version, and some new functions that leverage the advantage of dynamic arrays have been introduced. These functions are −
Sr.No |
Functions & Description |
1 |
FILTER Filter data and return matching records |
2 |
RANDARRAY Generate array of random numbers |
3 |
SEQUENCE Generate array of sequential numbers |
4 |
SORT Sort range by column |
5 |
SORTBY Sort range by another range or array |
6 |
UNIQUE Extract unique values from a list or range |
7 |
XLOOKUP replacement for VLOOKUP |
8 |
XMATCH replacement for the MATCH function |
动态数组是其大小可以根据结果而变化的返回值区域。例如,函数 FILTER() 返回一个数组,其大小可以根据筛选结果而变化。
Dynamic arrays are ranges of return values whose size can change based on the results. For example, a function such as FILTER() returns an array of values that can vary in size depending on the filter results.
Example
在下面的示例中,数据范围是 A1:D17。筛选函数使用此范围,标准范围是 C1:C17,其中给出了产品名称。 FILTER() 函数产生动态数组,因为满足标准的行数可能发生变化。
In the example below, the data range is A1:D17. The filter function uses this range and the criteria range is C1:C17, in which the product names are given. The FILTER() function results in a dynamic array as the number of rows satisfying the criteria may change.
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()
Python XlsxWriter - Date & Time
在 Excel 中,日期存储为实数,以便可以在计算中使用它们。默认情况下,1900 年 1 月 1 日(称为纪元)被视为 1,因此 2022 年 1 月 28 日对应于 44589。类似地,时间表示为数字的小数部分,作为一天的百分比。因此,2022 年 1 月 28 日 11:00 对应于 44589.45833。
In Excel, dates are stored as real numbers so that they can be used in calculations. By default, January 1, 1900 (called as epoch) is treated 1, and hence January 28, 2022 corresponds to 44589. Similarly, the time is represented as the fractional part of the number, as the percentage of day. Hence, January 28, 2022 11.00 corresponds to 44589.45833.

The set_num_format() Method
由于 Excel 中的日期或时间就像其他任何数字一样,因此要将数字显示为日期,必须对其应用 Excel 数字格式。使用适当格式的 Format 对象的 set_num_format() 方法。
Since date or time in Excel is just like any other number, to display the number as a date you must apply an Excel number format to it. Use set_num_format() method of the Format object using appropriate formatting.
以下代码段显示“dd/mm/yy”格式的数字。
The following code snippet displays a number in "dd/mm/yy" format.
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 参数设置为所需的格式。
Alternatively, the num_format parameter of add_format() method can be set to the desired format.
format1 = wb.add_format({'num_format':'dd/mm/yy'})
ws.write('B2', num, format1)
Example
以下代码显示数字在各种日期格式中的情况。
The following code shows the number in various date formats.
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()
write_datetime() and strptime()
XlsxWriter 的工作表对象还有 write_datetime() 方法,该方法在使用 Python 标准库的 datetime 模块获取日期和时间对象时很有用。
The XlsxWriter’s Worksheet object also has write_datetime() method that is useful when handling date and time objects obtained with datetime module of Python’s standard library.
strptime() 方法从根据给定格式解析的字符串中返回 datetime 对象。格式化字符串使用的部分代码如下所示 −
The strptime() method returns datetime object from a string parsed according to the given format. Some of the codes used to format the string are given below −
%a |
Abbreviated weekday name |
Sun, Mon |
%A |
Full weekday name |
Sunday, Monday |
%d |
Day of the month as a zero-padded decimal |
01, 02 |
%-d |
day of the month as decimal number |
1, 2.. |
%b |
Abbreviated month name |
Jan, Feb |
%m |
Month as a zero padded decimal number |
01, 02 |
%-m |
Month as a decimal number |
1, 2 |
%B |
Full month name |
January, February |
%y |
Year without century as a zero padded decimal number |
99, 00 |
%-y |
Year without century as a decimal number |
0, 99 |
%Y |
Year with century as a decimal number |
2022, 1999 |
%H |
Hour (24 hour clock) as a zero padded decimal number |
01, 23 |
%-H |
Hour (24 hour clock) as a decimal number |
1, 23 |
%I |
Hour (12 hour clock) as a zero padded decimal number |
01, 12 |
%-I |
Hour (12 hour clock) as a decimal number |
1, 12 |
%p |
locale’s AM or PM |
AM, PM |
%M |
Minute as a zero padded decimal number |
01, 59 |
%-M |
Minute as a decimal number |
1, 59 |
%S |
Second as a zero padded decimal number |
01, 59 |
%-S |
Second as a decimal number |
1, 59 |
%c |
locale’s appropriate date and time representation |
Mon Sep 30 07:06:05 2022 |
strptime() 方法的使用方式如下:
The strptime() method is used as follows −
>>> 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 对象写入工作表。
This datetime object can now be written into the worksheet with write_datetime() method.
Example
在以下示例中, datetime 对象将采用不同的格式写入。
In the following example, the datetime object is written with different formats.
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()
Python XlsxWriter - Tables
在 MS Excel 中,表是作为单个实体分组的一系列单元格。它可以从公式中引用,并具有公共格式属性。可以在工作表中定义一些诸如列标题、自动筛选器、总行、列公式之类的功能。
In MS Excel, a Table is a range of cells that has been grouped as a single entity. It can be referenced from formulas and has common formatting attributes. Several features such as column headers, autofilters, total rows, column formulas can be defined in a worksheet table.
The add_table() Method
工作表方法 add_table() 用于将单元格范围添加为表。
The worksheet method add_table() is used to add a cell range as a table.
worksheet.add_table(first_row, first_col, last_row, last_col, options)
标准“ A1 ”或“ Row/Column ”符号均可用于指定范围。 add_table() 方法可以采用下面的一个或多个可选参数。请注意,除了范围参数之外,其他参数都是可选的。如果不给出,则创建一个空表。
Both the methods, the standard 'A1' or 'Row/Column' notation are allowed for specifying the range. The add_table() method can take one or more of the following optional parameters. Note that except the range parameter, others are optional. If not given, an empty table is created.
Example
data
data
该参数可用于指定表中单元格中的数据。看看下列示例 −
This parameter can be used to specify the data in the cells of the table. Look at the following 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})
wb.close()
header_row
该参数可用于关闭或开启表中的标题行。默认情况下它是打开的。标题行将包含一些默认标题,如 Column 1、Column 2 等等。你可以使用 columns 参数设置必需的标题。
This parameter can be used to turn on or off the header row in the table. It is on by default. The header row will contain default captions such as Column 1, Column 2, etc. You can set required captions by using the columns parameter.
Columns
Example
该属性用于设置列标题。
This property is used to set column captions.
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
该参数默认情况下是 ON。将其设置成 OFF 时,标题行不显示下拉箭头来设置筛选条件。
This parameter is ON, by default. When set to OFF, the header row doesn’t show the dropdown arrows to set the filter criteria.
Name
在 Excel 工作表中,表被命名为 Table1、Table2 等。name 参数可用于根据需要设置表名称。
In Excel worksheet, the tables are named as Table1, Table2, etc. The name parameter can be used to set the name of the table as required.
ws.add_table("A1:E4", {'data':data, 'name':'marklist'})
Formula
可以在列选项中指定公式子属性来创建包含公式的列。
Column with a formula can be created by specifying formula sub-property in columns options.
Example
在以下示例中,表的名称属性设置为“marklist”。“总计”列 E 的公式执行成绩的总和,并分配给公式子属性的值。
In the following example, the table’s name property is set to 'marklist'. The formula for 'Total' column E performs sum of marks, and is assigned the value of formula sub-property.
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 - Applying Filter
在 Excel 中,你可以根据使用逻辑表达式的条件,对表格数据设置筛选器。在 XlsxWriter 的工作表类中,我们有 autofilter() 方法或其用途。该方法的必需参数是单元格区域。这会在标题行中创建下拉选择器。要应用一些条件,我们有两个可用的方法 − filter_column() 或 filter_column_list() 。
In Excel, you can set filter on a tabular data based upon criteria using logical expressions. In XlsxWriter’s worksheet class, we have autofilter() method or the purpose. The mandatory argument to this method is the cell range. This creates drop-down selectors in the heading row. To apply some criteria, we have two methods available − filter_column() or filter_column_list().
Applying Filter Criteria for a Column
在以下示例中,范围 A1:D51(即单元格 0,0 到 50,3)中的数据被用作方法 autofilter() 的范围参数。筛选条件 'Region == East' 被用方法 filter_column() 设置在第 0 列(区域标题)。
In the following example, the data in the range A1:D51 (i.e. cells 0,0 to 50,3) is used as the range argument for autofilter() method. The filter criteria 'Region == East' is set on 0th column (with Region heading) with filter_column() method.
Example
数据范围中所有不满足筛选条件的行都通过为工作表对象的 set_row() 方法设置隐藏选项为 true 而隐藏。
All the rows in the data range not meeting the filter criteria are hidden by setting hidden option to true for the set_row() method of the worksheet object.
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 打开工作表时,我们会发现只有区域='东部’的行是可见的,其他行被隐藏了(你可以通过清除筛选器来再次显示它们)。
When we open the worksheet with the help of Excel, we will find that only the rows with Region='East' are visible and others are hidden (which you can display again by clearing the filter).

列参数可以是零索引列号或字符串列名。在 Python 中允许的所有逻辑运算符都可以在条件中使用(==、!=、<、>、⇐、>=)。可以针对多个列定义筛选条件,并且可以通过 and 或 or 运算符将它们组合起来。带逻辑运算符的条件示例如下 −
The column parameter can either be a zero indexed column number or a string column name. All the logical operators allowed in Python can be used in criteria (==, !=, <, >, ⇐, >=). Filter criteria can be defined on more than one columns and they can be combined by and or or operators. An example of criteria with logical operator can be as follows −
ws.filter_column('A', 'x > 2000')
ws.filter_column('A', 'x != 2000')
ws.filter_column('A', 'x > 2000 and x<5000')
请注意条件参数中的“ x ”只是一个形式上的占位符,它可以是任何适当的字符串,因为无论如何它在内部被忽略。
Note that "x" in the criteria argument is just a formal place holder and can be any suitable string as it is ignored anyway internally.
ws.filter_column('A', 'price > 2000')
ws.filter_column('A', 'x != 2000')
ws.filter_column('A', 'marks > 60 and x<75')
XlsxWriter 还允许在包含字符串数据的列上使用筛选条件中的通配符“ * ”和“ ? ”。
XlsxWriter also allows the use of wild cards "*" and "?" in the filter criteria on columns containing string data.
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 ”的行被隐藏。
In the following example, first filter on column A requires region to be West and second filter’s criteria on column D is "units > 5000". Rows not satisfying the condition "region = West" or "units > 5000" are hidden.
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()
Applying a Column List Filter
方法 filter_column_list() 可以用于表示具有 Excel 2007 样式的多选条件的筛选器。
The filter_column_list() method can be used to represent filters with multiple selected criteria in Excel 2007 style.
ws.filter_column_list(col,list)
第二个参数是给定列中的数据与之匹配的值列表。例如 −
The second argument is a list of values against which the data in a given column is matched. For example −
ws.filter_column_list('C', ['March', 'April', 'May'])
它会导致对数据进行筛选,以便列 C 中的值与列表中的任何项匹配。
It results in filtering the data so that value in column C matches with any item in the list.
Example
在以下示例中,方法 filter_column_list() 用于筛选 rows ,区域等于东部或西部。
In the following example, the filter_column_list() method is used to filter the rows with region equaling either East or West.
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 列显示了自动筛选器已应用。区域为东部或西部的所有行都显示,其余行被隐藏。
The Column A shows that the autofilter is applied. All the rows with Region as East or West are displayed and rest are hidden.

从 Excel 软件中,单击标题 Region 中的 filter 选择器箭头,我们应该看到已应用区域等于东部或西部的筛选器。
From the Excel software, click on the filter selector arrow in the Region heading and we should see that the filter on region equal to East or West is applied.

Python XlsxWriter - Fonts & Colors
Working with Fonts
要执行工作表单元格格式,我们需要使用格式对象,借助 add_format() 方法对其进行配置,并使用其属性或格式化方法对对象进行配置。
To perform formatting of worksheet cell, we need to use Format object with the help of add_format() method and configure it with its properties or formatting methods.
f1 = workbook.add_format()
f1 = set_bold(True)
# or
f2 = wb.add_format({'bold':True})
然后将此格式对象用作工作表 write() 方法的参数。
This format object is then used as an argument to worksheet’s write() method.
ws.write('B1', 'Hello World', f1)
Example
要使单元格 bold, underline, italic 或 strike through, 中的文本可以设置,我们可以使用这些属性或相应的函数。在下面的示例中,文本 Hello World 使用 set 方法进行编写。
To make the text in a cell bold, underline, italic or strike through, we can either use these properties or corresponding methods. In the following example, the text Hello World is written with set methods.
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()
Example
另一方面,我们也可以像以下示例中那样使用 font_color, font_name 和 font_size 属性来设置文本格式 -
On the other hand, we can use font_color, font_name and font_size properties to format the text as in the following example −
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()
Text Alignment
XlsxWriter 的 Format 对象也可以用对齐方法/属性创建。align 属性可以具有 left, right, center 和 justify 值。
XlsxWriter’s Format object can also be created with alignment methods/properties. The align property can have left, right, center and justify values.
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。
The following output shows the text "Hello World" with different alignments. Note that the width of B column is set to 30 by set_column() method of the worksheet object.

Example
Format 对象还具有 valign 属性来控制单元格的垂直位置。
Format object also has valign properties to control vertical placement of the cell.
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()
Cell Background and Foreground Colors
Format 对象有两个重要属性 bg_color 和 fg_color ,用于设置单元格的背景色和前景色。
Two important properties of Format object are bg_color and fg_color to set the background and foreground color of a cell.
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()
Python XlsxWriter - Number Formats
在 Excel 中,数字数据的不同格式化选项在 Format Cells 菜单的 Number tab 中提供。
In Excel, different formatting options of numeric data are provided in the Number tab of Format Cells menu.

要用 XlsxWriter 控制数字的格式化,我们可以使用 set_num_format() 方法或定义 add_format() 方法的 num_format 属性。
To control the formatting of numbers with XlsxWriter, we can use the set_num_format() method or define num_format property of add_format() method.
f1 = wb.add_format()
f1.set_num_format(FormatCode)
#or
f1 = wb.add_format('num_format': FormatCode)
Excel 有许多预定义的数字格式。它们可以在上方图片所示的数字选项卡的自定义类别中找到。例如,带有两个小数点和逗号分隔符的数字的格式代码是 , #0.00。
Excel has a number of predefined number formats. They can be found under the custom category of Number tab as shown in the above figure. For example, the format code for number with two decimal points and comma separator is ,#0.00.
Example
在以下示例中,数字 1234.52 使用不同的格式代码进行格式化。
In the following example, a number 1234.52 is formatted with different format codes.
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 - Border
本节介绍如何应用和设置单元格边框以及文本框周围的边框的外观。
This section describes how to apply and format the appearance of cell border as well as a border around text box.
Working with Cell Border
add_format() 方法中控制单元格边框外观的属性如下表所示 -
The properties in the add_format() method that control the appearance of cell border are as shown in the following table −
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() 方法开头的相应格式类方法。
Note that for each property of add_format() method, there is a corresponding format class method starting with the set_propertyname() method.
例如,要设置单元格的边框,可以使用 add_format() 方法中的 border 属性,如下所示 -
For example, to set a border around a cell, we can use border property in add_format() method as follows −
f1= wb.add_format({ 'border':2})
还可以通过调用 set_border() 方法来执行相同操作 -
The same action can also be done by calling the set_border() method −
f1 = workbook.add_format()
f1.set_border(2)
各个边框元素可以通过以下属性或格式方法进行配置 -
Individual border elements can be configured by the properties or format methods as follows −
-
set_bottom()
-
set_top()
-
set_left()
-
set_right()
这些边框方法/属性具有一个整数值,该值对应于预定义的样式,如下表所示 -
These border methods/properties have an integer value corresponding to the predefined styles as in the following table −
Index |
Name |
Weight |
Style |
0 |
None |
0 |
|
1 |
Continuous |
1 |
----------- |
2 |
Continuous |
2 |
----------- |
3 |
Dash |
1 |
- - - - - - |
4 |
Dot |
1 |
. . . . . . |
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。
Following code shows how the border property is used. Here, each row is having a border style 2 corresponding to continuous bold.
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()
Working with Textbox Border
边框属性也适用于文本框对象。文本框还有一个类似于边框的行属性,因此它们可以互换使用。边框本身还可以通过 none、color、width 和 dash_type 参数进行进一步格式化。
The border property is also available for the text box object. The text box also has a line property which is similar to border, so that they can be used interchangeably. The border itself can further be formatted by none, color, width and dash_type parameters.
设置为 none 的行或边框表示文本框没有边框。 dash_type 参数可以是以下任意值 −
Line or border set to none means that the text box will not have any border. The dash_type parameter can be any of the following values −
-
solid
-
round_dot
-
square_dot
-
dash
-
dash_dot
-
long_dash
-
long_dash_dot
-
long_dash_dot_dot
Example
这是一个显示两个文本框的程序,其中一个具有蓝色实心边框,而第二个盒子具有蓝色 dash_dot 类型边框。
Here is a program that displays two text boxes, one with a solid border, red in color; and the second box has dash_dot type border in blue color.
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 - Hyperlinks
hyperlink 是一个字符串,单击时,它使用户转到其他位置,例如 URL、同一工作簿中的另一个工作表或计算机上的另一个工作簿。工作表类提供 write_url() 方法用于此目的。还可以在文本框中放置超链接,方法是使用 url 属性。
A hyperlink is a string, which when clicked, takes the user to some other location, such as a URL, another worksheet in the same workbook or another workbook on the computer. Worksheet class provides write_url() method for the purpose. Hyperlinks can also be placed inside a textbox with the use of url property.
首先,让我们了解一下 write_url() 方法。除了单元格位置外,它还需要定向到的 URL 字符串。
First, let us learn about write_url() method. In addition to the Cell location, it needs the URL string to be directed to.
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 本身将出现在单元格中。
This method has a few optional parameters. One is a Format object to configure the font, color properties of the URL to be displayed. We can also specify a tool tip string and a display text foe the URL. When the text is not given, the URL itself appears in the cell.
Example
支持的 URL 类型包括 http:// 、 https:// 、 ftp:// 和 mailto: 。在下面的示例中,我们使用这些 URL。
Different types of URLs supported are http://, https://, ftp:// and mailto:. In the example below, we use these URLs.
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 文件。
Run the above code and open the hello.xlsx file using Excel.

Example
我们还可以将超链接插入到同一工作簿中的另一个工作表或另一个工作簿中。这是通过在本地 URI 前加上 internal: 或 external: 来完成的。
We can also insert hyperlink to either another workskeet in the same workbook, or another workbook. This is done by prefixing with internal: or external: the local URIs.
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()
Python XlsxWriter - Conditional Formatting
Excel 使用“@ {s5}”基于用户定义的条件更改范围内单元格的外观。从条件格式设置菜单中,可以定义涉及各种类型值的条件。
Excel uses conditional formatting to change the appearance of cells in a range based on user defined criteria. From the conditional formatting menu, it is possible to define criteria involving various types of values.

在下方的工作表中,A 列具有不同的数字。小于 50 的数字以红色字体和灰色背景色显示。
In the worksheet shown below, the column A has different numbers. Numbers less than 50 are shown in red font color and grey background color.

这是通过定义以下条件格式规则实现的:
This is achieved by defining a conditional formatting rule below −

The conditional_format() method
在 XlsxWriter 中,在 Worksheet 类中定义“@ {s6}”方法。若要实现上述显示结果,请按以下代码调用“@ {s7}”方法:
In XlsxWriter, there as a conditional_format() method defined in the Worksheet class. To achieve the above shown result, the conditional_format() method is called as in the following code −
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}”方法的第一个参数是单元格范围,第二个参数是条件格式设置选项的字典。
The conditional_format() method’s first argument is the cell range, and the second argument is a dictionary of conditional formatting options.
选项字典使用以下参数配置条件格式设置规则:
The options dictionary configures the conditional formatting rules with the following parameters −
“@ {s9}”选项是必需参数。其值可以是单元格、日期、文本、公式等。每个参数都有子参数,例如条件、值、格式等。
The type option is a required parameter. Its value is either cell, date, text, formula, etc. Each parameter has sub-parameters such as criteria, value, format, etc.
-
Type is the most common conditional formatting type. It is used when a format is applied to a cell based on a simple criterion.
-
Criteria parameter sets the condition by which the cell data will be evaluated. All the logical operator in addition to between and not between operators are the possible values of criteria parameter.
-
Value parameter is the operand of the criteria that forms the rule.
-
Format parameter is the Format object (returned by the add_format() method). This defines the formatting features such as font, color, etc. to be applied to cells satisfying the criteria.
date 类型与单元格类型相似,并且使用相同的标准和值。但是,值参数应给为 datetime 对象。
The date type is similar the cell type and uses the same criteria and values. However, the value parameter should be given as a datetime object.
text 类型指定 Excel 的“特定文本”样式条件格式。它用于使用标准和值参数执行简单的字符串匹配。
The text type specifies Excel’s "Specific Text" style conditional format. It is used to do simple string matching using the criteria and value parameters.
Example
当使用 formula 类型时,条件格式取决于用户定义的公式。
When formula type is used, the conditional formatting depends on a user defined 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 打开结果工作簿。我们可以看到满足上述条件的行按照格式对象显示为蓝色。条件格式规则管理器还显示我们在上述代码中设置的标准。
Open the resultant workbook using MS Excel. We can see the rows satisfying the above condition displayed in blue color according to the format object. The conditional format rule manager also shows the criteria that we have set in the above code.

Python XlsxWriter - Adding Charts
Excel 最重要的功能之一是其将数据转换为图表的能力。图表是数据的可视化表示。可以从 Chart 菜单生成不同类型的图表。
One of the most important features of Excel is its ability to convert data into chart. A chart is a visual representation of data. Different types of charts can be generated from the Chart menu.

为了以编程方式生成图表,XlsxWriter 库具有一个 Chart 类。它的对象可以通过调用 Workbook 类的 add_chart() 方法获得。接下来,借助 add_series() 方法,它与工作表中的数据范围关联起来。然后,使用其 insert_chart() 方法在工作表中插入图表对象。
To generate charts programmatically, XlsxWriter library has a Chart class. Its object is obtained by calling add_chart() method of the Workbook class. It is then associated with the data ranges in the worksheet with the help of add_series() method. The chart object is then inserted in the worksheet using its insert_chart() method.
Example
下面给出了用于显示简单柱状图的代码。
Given below is the code for displaying a simple column 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()
Output
生成的图表嵌入到工作表中,并显示如下 −
The generated chart is embedded in the worksheet and appears as follows −

add_series() 方法具有以下附加参数 −
The add_series() method has following additional parameters −
-
Values − This is the most important property mandatory option. It links the chart with the worksheet data that it displays.
-
Categories − This sets the chart category labels. If not given, the chart will just assume a sequential series from 1…n.
-
Name − Set the name for the series. The name is displayed in the formula bar.
-
Line − Set the properties of the series line type such as color and width.
-
Border − Set the border properties of the series such as color and style.
-
Fill − Set the solid fill properties of the series such as color.
-
Pattern − Set the pattern fill properties of the series.
-
Gradient − Set the gradient fill properties of the series.
-
data_labels − Set data labels for the series.
-
Points − Set properties for individual points in a series.
在以下示例中,在添加数据序列时,定义了 value 和 categories 属性。用于示例的数据为 −
In the following examples, while adding the data series, the value and categories properties are defined. The data for the example is −
# 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 的列。第一列中的学生姓名用作分类
After creating the chart object, the first data series corresponds to the column with phy as the value of name property. Names of the students in the first column are used as categories
chart1.add_series({
'name': '=Sheet1!$B$1',
'categories': '=Sheet1!$A$2:$A$4',
'values': '=Sheet1!$B$2:$B$4',
})
第二个数据序列也引用列 A 中的姓名作为分类,列 C 以 heading 作为 Maths 作为 values 属性。
The second data series too refers to names in column A as categories and column C with heading as Maths as the values property.
chart1.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 0, 3, 0],
'values': ['Sheet1', 1, 2, 3, 2],
})
Example
以下是完整的示例代码 −
Here is the complete example code −
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
基于它的工作表和图表显示如下 −
The worksheet and the chart based on it appears as follows −

add_series() 方法还具有 data_labels 属性。如果设置为 True,会找到绘制的数据点值,并将其显示在每列顶部。
The add_series() method also has data_labels property. If set to True, values of the plotted data points are displayed on top of each column.
Example
以下是 add_series() 方法的完整代码示例 −
Here is the complete code example for add_series() method −
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 标签。
Execute the code and open Hello.xlsx. The column chart now shows the data labels.

数据标签可以显示在所有类型的图表中。数据标签的位置参数可以设置为顶部、底部、左侧或右侧。
The data labels can be displayed for all types of charts. Position parameter of data label can be set to top, bottom, left or right.
XlsxWriter 支持以下类型的图表 −
XlsxWriter supports the following types of charts −
-
Area − Creates an Area (filled line) style chart.
-
Bar − Creates a Bar style (transposed histogram) chart.
-
Column − Creates a column style (histogram) chart.
-
Line − Creates a Line style chart.
-
Pie − Creates a Pie style chart.
-
Doughnut − Creates a Doughnut style chart.
-
Scatter − Creates a Scatter style chart.
-
Stock − Creates a Stock style chart.
-
Radar − Creates a Radar style chart.
许多图表类型也有子类型。例如,柱形图、条形图、面积图和折线图的子类型是堆叠和 percent_stacked 。类型和子类型参数可以在 add_chart() 方法中给出。
Many of the chart types also have subtypes. For example, column, bar, area and line charts have sub types as stacked and percent_stacked. The type and subtype parameters can be given in the add_chart() method.
workbook.add_chart({'type': column, 'subtype': 'stacked'})
该图表通过其 insert_chart() 方法嵌入到工作表中,该方法采用以下参数 −
The chart is embedded in the worksheet with its insert_chart() method that takes following parameters −
worksheet.insert_chart(location, chartObj, options)
options 参数是一个字典,用于配置图表的放置和比例。选项属性及其默认值是 −
The options parameter is a dictionary that configures the position and scale of chart. The option properties and their default values are −
{
'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 值用于水平/垂直缩放图表。说明字段可用于为图表指定说明或“替代文本”字符串。
The x_offset and y_offset values are in pixels, whereas x_scale and y_scale values are used to scale the chart horizontally / vertically. The description field can be used to specify a description or "alt text" string for the chart.
decorative 参数用于将图表标记为装饰性的,因此对于自动屏幕阅读器而言是不可理解的。须将其设置为 True/False。最后, object_position 参数控制图表的对象定位。它允许以下值 −
The decorative parameter is used to mark the chart as decorative, and thus uninformative, for automated screen readers. It has to be set to True/False. Finally, the object_position parameter controls the object positioning of the chart. It allows the following values −
-
1 − Move and size with cells (the default).
-
2 − Move but don’t size with cells.
-
3 − Don’t move or size with cells.
Python XlsxWriter - Chart Formatting
可以自定义图表的默认外观使其更具吸引力、解释性和用户友好性。使用 XlsxWriter ,我们可以对图表对象进行以下增强 −
The default appearance of chart can be customized to make it more appealing, explanatory and user friendly. With XlsxWriter, we can do following enhancements to a Chart object −
-
Set and format chart title
-
Set the X and Y axis titles and other parameters
-
Configure the chart legends
-
Chat layout options
-
Setting borders and patterns
Title
您可以通过调用图表对象的 set_title() 方法来设置和配置图表对象的标题。可以是各种参数如下 −
You can set and configure the main title of a chart object by calling its set_title() method. Various parameters that can be are as follows −
-
Name − Set the name (title) for the chart to be displayed above the chart. The name property is optional. The default is to have no chart title.
-
name_font − Set the font properties for the chart title.
-
Overlay − Allow the title to be overlaid on the chart.
-
Layout − Set the (x, y) position of the title in chart relative units.
-
None − Excel adds an automatic chart title. The none option turns this default title off. It also turns off all other set_title() options.
X and Y axis
两种方法 set_x_axis() 和 set_y_axis() 用于轴标题, name_font 用于标题文本, num_font 用于 X 和 Y 轴上显示的数字。
The two methods set_x_axis() and set_y_axis() are used to axis titles, the name_font to be used for the title text, the num_font to be used for numbers displayed on the X and Y axis.
-
name − Set the title or caption for the axis.
-
name_font − Set the font properties for the axis title.
-
num_font − Set the font properties for the axis numbers.
-
num_format − Set the number format for the axis.
-
major_gridlines − Configure the major gridlines for the axis.
-
display_units − Set the display units for the axis.
在前一个示例中,marklist 的数据已显示为柱形图形式,我们设置了图表格式化选项(例如图表标题和 X/Y 轴标题及其其他显示属性),如下所示: −
In the previous example, where the data of marklist has been shown in the form of a column chart, we set up the chart formatting options such as the chart title and X as well as Y axis captions and their other display properties as follows −
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
在完整代码中添加以上代码段。现在如下所示: −
Add the above snippet in the complete code. It now looks as given below −
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 - Chart Legends
根据图表类型,数据以不同颜色或图案的形式在柱状图、条形图、折线图、圆弧图等形式中进行视觉呈现。图表图例能够轻松直观地显示哪种颜色/图案对应于哪种数据系列。
Depending upon the type of chart, the data is visually represented in the form of columns, bars, lines, arcs, etc. in different colors or patterns. The chart legend makes it easy to quickly understand which color/pattern corresponds to which data series.
Working with Chart Legends
为了设置图例并配置其属性,例如位置和字体,XlsxWriter 提供了 set_legend() 方法。其属性为 −
To set the legend and configure its properties such as position and font, XlsxWriter has set_legend() method. The properties are −
-
None − In Excel chart legends are on by default. The none=True option turns off the chart legend.
-
Position − Set the position of the chart legend. It can be set to top, bottom, left, right, none.
-
Font − Set the font properties (like name, size, bold, italic etc.) of the chart legend.
-
Border − Set the border properties of the legend such as color and style.
-
Fill − Set the solid fill properties of the legend such as color.
-
Pattern − Set the pattern fill properties of the legend.
-
Gradient − Set the gradient fill properties of the legend.
某些图例属性可用于图表,如下所示 −
Some of the legend properties are set for the chart as below −
chart1.set_legend(
{'position':'bottom', 'font': {'name':'calibri','size': 9, 'bold': True}}
)
Example
以下是按上述特性显示图例的完整代码 −
Here is the complete code to display legends as per the above characteristics −
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 轴标题的下方显示图例。
The chart shows the legend below the caption of the X axis.

在图表中,对应于 physics 和 maths 的列以不同的颜色显示。图表右侧的小色块符号是图例,用来显示哪种颜色对应于 physics 或 maths 。
In the chart, the columns corresponding to physics and maths are shown in different colors. The small colored box symbols to the right of the chart are the legends that show which color corresponds to physics or maths.
Python XlsxWriter - Bar Chart
条形图类似于柱形图,不同之处在于数据用成比例的水平条表示,而不是垂直列。要生成条形图,方法 add_chart() 的类型参数必须设置为“bar”。
The bar chart is similar to a column chart, except for the fact that the data is represented in proportionate horizontal bars instead of vertical columns. To produce a bar chart, the type argument of add_chart() method must be set to 'bar'.
chart1 = workbook.add_chart({'type': 'bar'})
条形图如下所示 −
The bar chart appears as follows −

条形图有两个子类型,分别是堆叠和 percent_stacked 。在堆叠图中,某个分类的不同颜色的条形图一个接一个地放置。在 percent_stacked 图中,每个条形图的长度显示了其在每个分类的总值中的百分比。
There are two subtypes of bar chart, namely stacked and percent_stacked. In the stacked chart, the bars of different colors for a certain category are placed one after the other. In a percent_stacked chart, the length of each bar shows its percentage in the total value in each category.
chart1 = workbook.add_chart({
'type': 'bar',
'subtype': 'percent_stacked'
})
Example
生成百分比堆叠条形图的程序如下所示:
Program to generate percent stacked bar chart is given below −
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 - Line Chart
折线图直观显示了一系列数据点,这些数据点通过一条直线在 X 轴上连接起来。这是一条独立轴,因为 X 轴上的值不依赖于纵向 Y 轴。
A line shows a series of data points connected with a line along the X-axis. It is an independent axis because the values on the X-axis do not depend on the vertical Y-axis.
Y 轴是一条从属轴,因为其值依赖于 X 轴,结果是水平延伸的折线。
The Y-axis is a dependent axis because its values depend on the X-axis and the result is the line that progress horizontally.
Working with XlsxWriter Line Chart
要使用 XlsxWriter 以编程方式生成 line chart ,我们使用 add_series() 。图表对象类型定义为“ line ”。
To generate the line chart programmatically using XlsxWriter, we use add_series(). The type of chart object is defined as 'line'.
Example
在以下示例中,我们绘制 line chart ,显示了六个月内两种产品的销售数字。通过 add_series() 方法将对应于产品 A 和产品 B 的销售数字的两个数据系列添加到图表中。
In the following example, we shall plot line chart showing the sales figures of two products over six months. Two data series corresponding to sales figures of Product A and Product B are added to the chart with add_series() method.
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 生成折线图 −
After executing the above program, here is how XlsxWriter generates the Line chart −

除了 data_labels , add_series() 方法还具有 marker 属性。这在折线图中尤其有用。数据点由圆圈、三角形、正方形、菱形等标记符号表示。让我们将 circle 和 square 符号指定给此图表中的两个数据系列。
Along with data_labels, the add_series() method also has a marker property. This is especially useful in a line chart. The data points are indicated by marker symbols such as a circle, triangle, square, diamond etc. Let us assign circle and square symbols to the two data series in this chart.
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'},})
数据标签和标记添加到折线图中。
The data labels and markers are added to the line chart.

折线图还支持 stacked 和 percent_stacked 子类型。
Line chart also supports stacked and percent_stacked subtypes.

Python XlsxWriter - Pie Chart
pie chart 是单个数据序列在圆环中的表示,这个圆环被分成对应于序列中每个数据项目的切片。在饼图中,每个切片的弧长与它所表示的数量成正比。在以下工作表中,按季度划分的某个产品的销售额以饼图的形式显示。
A pie chart is a representation of a single data series into a circle, which is divided into slices corresponding to each data item in the series. In a pie chart, the arc length of each slice is proportional to the quantity it represents. In the following worksheet, quarterly sales figures of a product are displayed in the form of a pie chart.

Working with XlsxWriter Pie Chart
使用 XlsxWriter 以编程方式生成上述图表,我们首先在工作表中写入以下数据。
To generate the above chart programmatically using XlsxWriter, we first write the following data in the worksheet.
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)是类别。
A Chart object with type=pie is declared and the cell range B1:D1 is used as value parameter for add_series() method and the quarters (Q1, Q2, Q3 and Q4) in column A are the categories.
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 来表示每个饼的百分比值。
In the pie chart, we can use data_labels property to represent the percent value of each pie by setting percentage=True.
Example
生成饼图的完整程序如下 −
The complete program for pie chart generation is as follows −
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 Chart
doughnut chart 是饼图的一个变体,其中心有一个孔,它以圆弧而不是切片显示类别。两者都使人们一目了然地掌握整体与部分之间的关系。只需将图表类型更改为 doughnut 。
The doughnut chart is a variant of the pie chart, with a hole in its center, and it displays categories as arcs rather than slices. Both make part-to-whole relationships easy to grasp at a glance. Just change the chart type to doughnut.
chart1 = workbook.add_chart({'type': 'doughnut'})
以上示例中数据的环形图显示如下 −
The doughnut chart of the data in above example appears as below −

Python XlsxWriter - Sparklines
sparkline 是一种小型图表,没有坐标轴或坐标。它提供某个参数变化的表示形式。普通图表尺寸更大,具有许多说明性特征,例如标题、图例、数据标签等,并且与附带文本分开。另一方面,迷你图尺寸很小,可以嵌入文本中,也可以嵌入具有其上下文的文本或工作表单元格中。
A sparkline is a small chart, that doesn’t have axes or coordinates. It gives a representation of variation of a certain parameter. Normal charts are bigger in size, with a lot of explanatory features such as title, legend, data labels etc. and are set off from the accompanying text. Sparkline on the other hand is small in size and can be embedded inside the text, or a worksheet cell that has its context.
Edward Tufte 于 1983 年引入了火花图功能。Microsoft 于 2010 年在 Excel 中引入了火花图。我们可以在 Excel 软件的插入功能区中找到火花图选项。
Feature of Sparkline was introduced by Edward Tufte in 1983. Microsoft introduced sparklines in Excel 2010. We can find sparkline option in the insert ribbon of Excel software.
火花图有三种类型 -
Sparklines are of three types −
-
line − Similar to line chart
-
column − Similar to column chart
-
win_loss − Whether each value is positive (win) or negative (loss).
Working with XlsxWriter Sparklines
XlsxWriter 模块有 add_sparkline() 方法。它基本上需要火花图的单元格位置和要表示为火花图的数据范围。此外,还可以以字典对象的形式提供其他参数,例如类型、样式等。默认情况下,类型为折线。
XlsxWriter module has add_sparkline() method. It basically needs the cell location of the sparkline and the data range to be represented as a sparkline. Optionally, other parameters such as type, style, etc. are provided in the form of dictionary object. By default, the type is line.
Example
以下程序表示在折线和柱状火花图中相同的数字列表。
Following program represents same list of numbers in line and column sparklines.
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 中。
In cell K, the sparklines are added.

属性为 -
The properties are −
-
range − is the mandatory parameter. It specifies the cell data range that the sparkline will plot.
-
type − specifies the type of sparkline. There are 3 available sparkline types are line, column and win_loss.
-
markers − Turn on the markers for line style sparklines
-
style − The sparkline styles defined in MS Excel. There are 36 style types.
-
negative_points − If set to True, the negative points in a sparkline are highlighted.
Example
以下程序生成一个有 markers 的 line sparkline 和一个突出显示负值点的 win_loss sparkline 。
The following program produces a line sparkline with markers and a win_loss sparkline having negative points highlighted.
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 in K1 has markers. The sparkline in K5 shows negative points highlighting.

Example – Style Types
以下代码显示了柱状火花图中的一系列数字。这里使用了十种不同的样式类型。
Following code displays a series of numbers in column sparkline. Ten different style types are used here.
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 - Data Validation
Excel 中的 Data validation 特性使您可以控制用户在单元格中输入的内容。您可以使用此特性确保单元格中的值是处于特定范围内的数字/日期、具有所需长度的文本,或以下拉菜单形式显示用于从其中选择值的列表。
Data validation feature in Excel allows you to control what a user can enter into a cell. You can use it to ensure that the value in a cell is a number/date within a specified range, text with required length, or to present a dropdown menu to choose the value from.
数据验证工具在“数据”菜单中提供。第一个选项卡允许您设置验证条件。下图显示条件需要单元格包含 1 到 25 之间的整数:
The data validation tools are available in the Data menu. The first tab allows you to set a validation criterion. Following figure shows that criteria requires the cell should contain an integer between 1 to 25 −

在第二个选项卡中,设置当用户光标所在期望单元格时闪现的消息,在本例中为“输入 1 到 25 之间的任意整数”。您还可以设置消息标题;在本例中为“年龄”。
In the second tab, set the message to be flashed when user’s cursor is on the desired cell, which in this case is 'Enter any integer between 1 to 25'. You can also set the message title; in this case it is Age.

第三个选项卡允许您定义任意错误消息,如果验证条件未能满足,则会闪现此错误消息。
The third tab allows asks you to define any error message you would like to flash if the validation criteria fails.

当用户将光标置于 I10(为此设置了验证)中时,您会看到输入消息。
When the user places the cursor in I10 (for which the validation is set), you can see the input message.

当输入的数字不在该范围内时,错误消息将闪烁。
When the entered number is not in the range, the error message will flash.

Working with XlsxWriter Data Validation
您可以使用 data_validation() 方法以编程方式设置验证条件、输入消息和错误消息。
You can set the validation criteria, input and error message programmatically with data_validation() method.
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 参数,该参数是一个包含以下参数的字典:
The data_validation() method accepts options parameter as a dictionary with following parameters −
-
validate − It is used to set the type of data that you wish to validate. Allowed values are integer, decimal, list, date, time, length etc.
-
criteria − It is used to set the criteria for validation. It can be set to any logical operator including between/ not between, ==, !=, <, >, ⇐, >=, etc.
-
value − Sets the limiting value to which the criteria is applied. It is always required. When using the list validation, it is given as a Comma Separated Variable string.
-
input_title − Used to set the title of the input message when the cursor is placed in the target cell.
-
input_message − The message to be displayed when a cell is entered.
-
error_title − The title of the error message to be displayed when validation criteria is not met.
-
error_message − Sets the error message. The default error message is "The value you entered is not valid. A user has restricted values that can be entered into the cell."
Example
data_validation() 方法的以下用法导致了数据验证功能的行为,如上图所示。
Following usage of data_validation() method results in the behavior of data validation feature as shown in the above figures.
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 设置了一个验证条件,以强制用户从下拉列表中的字符串列表中选择其值。
As another example, the cell I10 is set a validation criterion so as to force the user choose its value from a list of strings in a drop down.
worksheet.data_validation(
'I10',
{
'validate': 'list',
'source': ['Mumbai', 'Delhi', 'Chennai', 'Kolkata'],
'input_title': 'Choose one:',
'input_message': 'Select a value from th list',
}
)
Example
带有下拉列表的验证的修改后的程序如下所示 −
The modified program for validation with the drop down list is as follows −
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()
Example
如果您想要让用户输入长度大于 5 的字符串,请使用 >= 作为条件,并将值设置为 5。
If you want to make the user enter a string of length greater than 5, use >= as criteria and value set to 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()
Python XlsxWriter - Outlines & Grouping
在 Excel 中,你可以对具有相同特定列(或行)值的行或列进行分组,以便可以用单击隐藏或显示它们。此功能称为 outlines and grouping 。它有助于显示小计或合计。可以在 MS excel 软件的 Data→Outline 组中找到此功能。
In Excel, you can group rows or columns having same value of a particular column (or row)) so that they can be hidden or displayed with a single mouse click. This feature is called to as outlines and grouping. It helps in displaying sub-totals or summaries. This feature can be found in MS excel software’s Data→Outline group.
若要使用此功能,数据范围的所有行都应按一列中值分类。假设我们有不同项目的销售数据。按项目名称对范围排序之后,单击“大纲”组中的“小计”选项。将弹出以下对话框。
To use this feature, the data range must have all rows should be in the sorted order of values in one column. Suppose we have sales figures of different items. After sorting the range on name of item, click on the Subtotal option in the Outline group. Following dialog box pops up.

该工作表显示按项目划分的销售小计,最后是总计。该工作表左侧显示大纲级别。原始数据位于第 3 级,小计位于第 2 级,总计位于第 1 级。
The worksheet shows item-wise subtotal of sales and at the end the grand total. On the left of the worksheet, the outline levels are shown. The original data is at level 3, the subtotals at level 2 and grand total at level 1.

Working with Outlines and Grouping
若要使用 XlsxWriter 执行此操作,我们需要使用“@ {s0}”方法的 level 属性。数据行设置在第 2 级。
To do this using XlsxWriter, we need to use the level property of the set_row() method. The data rows are set at level 2.
ws.set_row(row, None, None, {'level': 2})
小计行位于第 1 级。
The rows for subtotal are having level 1.
ws.set_row(row, None, None, {'level': 1})
我们使用“@ {s1}”函数计算和显示一组中的销售额总和。
We use SUBTOTAL() function to calculate and display the sum of sales figures in one group.
Example
以下是完整代码 −
The complete code is given below −
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}”。正如我们所见,大纲显示在左侧。
Run the code and open hello.xlsx using Excel. As we can see, the outlines are displayed on the left.

在各个级别,减号表示可以折叠行并仅显示小计行。
At each level, the minus sign indicates that the rows can be collapsed and only the subtotal row will be displayed.

此图显示“@ {s3}”中的所有行已折叠。它现在在大纲中显示正号,这意味着可以展开数据行。如果您单击“@ {s4}”处的减号,则工作表上将仅保留总计。
This figure shows all rows at level 2 have been collapsed. It now shows plus symbol in the outline which means that the data rows can be expanded. If you click the minus symbol at level 1, only the grand total will remain on the worksheet.

Python XlsxWriter - Freeze & Split Panes
The freeze_panes() method
XlsxWriter 库中的 freeze_panes() 方法将工作表分成水平或垂直区域,称为窗格,并“冻结”其中任何一格或两格,这样,如果我们向下或向右滚动,窗格(分别为顶部或左侧)将保持不动。
The freeze_panes() method of Worksheet object in XlsxWriter library divides or splits the worksheet into horizontal or vertical regions known as panes, and "freezes" either or both of these panes so that if we scroll down or scroll down or scroll towards right, the panes (top or left respectively) remains stationary.
该方法需要参数 row 和 col 指定拆分的位置。应注意,拆分指定在单元格的顶部或左侧,并且该方法使用基于零的索引。如果您不想要垂直或水平拆分,可以将行和列参数之一设置为零。
The method requires the parameters row and col to specify the location of the split. It should be noted that the split is specified at the top or left of a cell and that the method uses zero based indexing. You can set one of the row and col parameters as zero if you do not want either a vertical or horizontal split.
Example
以下示例中的工作表在每一行中显示递增的列数的倍数,因此每个单元格都显示行号和列号的乘积。
The worksheet in the following example displays incrementing multiples of the column number in each row, so that each cell displays product of row number and column number.
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 。结果,在打开工作表后,如果单元格指针向下滚动,则顶部行始终显示在工作表上。
We then freeze the top row pane. As a result, after opening the worksheet, if the cell pointer is scrolled down, the top row always remains on the worksheet.

同样,我们可以让 first column 固定。
Similarly, we can make the first column stationery.
# Freeze pane on the first column.
worksheet.freeze_panes(0, 1)
以下屏幕截图显示,即使我们向右滚动, column A 仍然可见。
The following screenshot shows that column A remains visible even if we scroll towards the right.

通过将 freeze_panes() 方法中的行和列参数设置为 1,顶部行和最左侧列都将冻结。
By setting row and column parameter in freeze_panes() method to 1, both the top row and leftmost column will freeze.
# Freeze pane on the first row, first column.
worksheet.freeze_panes(1, 1)
打开最终工作表并滚动单元格光标。你会发现,已用粗体和背景色格式化的顶部行和最左侧列中的行号和列号始终可见。
Open the resulting worksheet and scroll the cell cursor around. You will find that row and column numbers in top row and leftmost column, which have been formatted in bold and with a background color, are visible always.

The split_panes() method
split_panes() 方法还会将工作表分成水平或垂直区域,称为窗格,但与 freeze_panes() 方法不同,窗格之间的拆分对用户可见,并且每个窗格都有自己独立的滚动条。
The split_panes() method also divides the worksheet into horizontal or vertical regions known as panes, but unlike freeze_panes() method, the splits between the panes will be visible to the user and each pane will have its own scroll bars.
该方法有参数 “y” 和 “x”,用于指定拆分的垂直和水平位置。这些参数以 Excel 使用的行高和列宽表示。缺省情况下,行高和列宽的值分别为行的 15 和列的 8.43。
The method has the parameters "y" and "x" that are used to specify the vertical and horizontal position of the split. These parameters are in terms of row height and column width used by Excel. The row heights and column widths have default values as 15 for a row and 8.43 for a column.
如果您不想要垂直或水平拆分,可以将 “y” 和 “x” 参数之一设置为零。
You can set one of the "y" and "x" parameters as zero if you do not want either a vertical or horizontal split.
要创建在第 10 行和第 7 列的拆分, split_panes() 方法如下:
To create a split at the 10th row and 7th column, the split_panes() method is used as follows −
worksheet.split_panes(15*10, 8.43*7)
你会在工作表的第 10 行和第 7 列找到拆分器。您可以向垂直拆分器的左右方向以及水平拆分的顶部和底部滚动窗格。请注意,其他窗格将保持不变。
You will find the splitters at 10th row and 7th column of the worksheet. You can scroll the panes to the left and right of vertical splitter and to the top and bottom of horizontal splitter. Note that the other panes will remain constant.
Example
以下是创建分隔符的完整代码示例,其下方是显示的输出 −
Here’s the complete code that creates the splitter, and below that the output is shown −
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()
Python XlsxWriter - Hide/Protect Worksheet
工作表对象 hide() 方法使工作表消失,直到通过 Excel 菜单将其取消隐藏。
The worksheet object’s hide() method makes the worksheet disappear till it is unhidden through Excel menu.
在以下工作表中,有三个工作表,其中 sheet2 已隐藏。
In the following worksheet, there are three sheets, of which sheet2 is hidden.
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()
它将创建以下工作表 −
It will create the following worksheet −

您不能隐藏 “ active ” 工作表,它通常是第一个工作表,因为这会导致 Excel error 。因此,为了隐藏第一个工作表,您需要激活另一个工作表。
You can’t hide the "active" worksheet, which generally is the first worksheet, since this would cause an Excel error. So, in order to hide the first sheet, you will need to activate another worksheet.
sheet2.activate()
sheet1.hide()
Hide Specific Rows or Columns
要隐藏工作表中的特定行或列,请在 set_row() 或 set_column() 方法中将隐藏参数设置为 1。以下语句隐藏活动工作表中的 C、D 和 E 列。
To hide specific rows or columns in a worksheet, set hidden parameter to 1 in set_row() or set_column() method. The following statement hides the columns C, D and E in the active worksheet.
worksheet.set_column('C:E', None, None, {'hidden': 1})
Example
请考虑以下程序 −
Consider the following program −
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 列在下方工作表中不可见 −
As a result of executing the above code, the columns C, D and E are not visible in the worksheet below −

同理,借助 set_row() 方法,我们可以隐藏带有 hidden 参数的行。
Similarly, we can hide rows with set_row() method with the help of hidden parameter.
for row in range(5, 7):
worksheet.set_row(row, None, None, {'hidden':1})
以下是结果 −
Here is the result −

Python XlsxWriter - Textbox
在 Excel 中, text box 是一个图形对象,可以放置在工作表的任何位置,并且在需要时可以移动。可以将诸如字体(颜色、大小、名称等)、对齐方式、填充效果、方向等所需的格式化功能应用于文本框中包含的文本。
In Excel, a text box is a graphic object that can be placed anywhere on the worksheet, and can be moved around if needed. Desired formatting features such as font (color, size, name etc.), alignment, fill effects, orientation etc. can be applied on the text contained in the text box.
Working with XlsxWriter – Textbox
在 XlsxWriter 中,一个 insert_textbox() 方法用于在工作表上放置文本框。必须给定文本框的单元格位置和要写入其中的文本。此外,以字典对象的形式给出了不同的格式化选项。
In XlsxWriter, there is insert_textbox() method to place text box on the worksheet. The cell location of the text box and the text to be written in it must be given. Additionally, different formatting options are given in the form of a dictionary object.
Example
以下代码在单元格 C5 中显示一个文本框,给定字符串将显示如下所示的字体和对齐方式属性:
The following code displays a text box at cell C5, the given string is displayed with font and alignment properties as shown below −
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 ”。文本框如下所示:
Open the worksheet 'hello.xlsx' with Excel app. The text box appears as below −

Textbox Options – fill
默认情况下,文本框大小为 192X120 像素(对应 3 列和 6 行)。此大小可以通过 width 和 height 参数进行更改,这两个参数都以像素为单位。 inset_textbox() 方法接受的参数之一是 fill 参数。它采用十六进制中的预定义颜色名称或颜色表示作为值。
The text box is by default 192X120 pixels in size (corresponds to 3 columns and 6 rows). This size can be changed with width and height parameters, both given in pixels. One of the parameters acceptable to inset_textbox() method is the fill parameter. It takes a predefined color name or color representation in hexadecimal as value.
Example
以下代码在自定义大小的文本框中显示多行字符串,背景填充为红色。
The following code displays a multi-line string in the custom sized text box having background filled with red color.
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 处呈现一个带有多行的文本框。
As we can see in the figure below, a text box with multiple lines is rendered at cell C5.

Textbox Options – text_rotation
另一个重要属性是 text_rotation 。默认情况下,文本水平显示。如果需要,您可以通过给出角度作为其值来更改其方向。请查看以下选项。
Another important property is the text_rotation. By default, the text appears horizontally. If required, you may change its orientation by giving an angle as its value. Look as the following options.
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()
文本现在以垂直方向显示在文本框中。
The text now appears in the text box with its vertical orientation.

object_position 参数控制文本框的 behaviour 。它可以具有以下可能的值及其效果:
The object_position parameter controls the behaviour of the text box. It can have the following possible values and their effect −
-
"1" − Move and size with cells (the default).
-
"2" − Move but don’t size with cells.
-
"3" − Don’t move or size with cells.
Python XlsxWriter - Insert Image
可以借助 insert_image() 方法在工作表的特定单元格位置插入图像对象。基本上,你必须使用任何类型的符号指定单元格的位置和要插入的图像。
It is possible to insert an image object at a certain cell location of the worksheet, with the help of insert_image() method. Basically, you have to specify the location of cell using any type of notation and the image to be inserted.
worksheet.insert_image('C5', 'logo.png')
insert_image() 方法在字典中采用以下可选参数。
The insert_image() method takes following optional parameters in a dictionary.
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 参数用于水平和垂直缩放图像。
The offset values are in pixels. The x_scale and y_scale parameters are used to scale the image horizontally and vertically.
image_data 参数用于在 io.BytesIO 格式中添加内存中字节流。
The image_data parameter is used to add an in-memory byte stream in io.BytesIO format.
Example
下列程序从当前文件夹中的文件中提取图像数据,并将其用作 image_data 参数的值。
The following program extracts the image data from a file in the current folder and uses is as value for image_data parameter.
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 - Page Setup
工作表 page setup 方法与工作表在打印时显示有关。这些工作表方法控制方向、纸张大小、边距等。
The worksheet page setup methods are related to appearance of the worksheet when it is printed. These worksheet methods control the orientation, paper size, margins, etc.
set_landscape()
此方法用于将工作表的打印页方向设置为横向。
This method is used to set the orientation of a worksheet’s printed page to landscape.
set_portrait()
此方法用于将工作表的打印页方向设置为纵向。这是默认方向。
This method is used to set the orientation of a worksheet’s printed page to portrait. This is the default orientation.
set_page_view()
此方法用于在“页面视图/版面”模式下显示工作表。
This method is used to display the worksheet in "Page View/Layout" mode.
set_paper()
此方法用于设置工作表打印输出的纸张格式。它采用索引作为整数参数。这是 Excel 纸张格式索引。
This method is used to set the paper format for the printed output of a worksheet. It takes index as an integer argument. It is the Excel paper format index.
以下是部分纸张样式和索引值 −
Following are some of the paper styles and index values −
Index |
Paper format |
Paper size |
0 |
Printer default |
Printer default |
1 |
Letter |
8 1/2 x 11 in |
2 |
Letter Small |
8 1/2 x 11 in |
3 |
Tabloid |
11 x 17 in |
4 |
Ledger |
17 x 11 in |
5 |
Legal |
8 1/2 x 14 in |
6 |
Statement |
5 1/2 x 8 1/2 in |
7 |
Executive |
7 1/4 x 10 1/2 in |
8 |
A3 |
297 x 420 mm |
9 |
A4 |
210 x 297 mm |
set_margin()
此方法用于设置工作表在打印时的边距。它接受值为英寸的 left、right、top 和 bottom 参数。所有参数都是可选的。左侧和右侧参数默认值为 0.7,上方和下方默认值为 0.75。
This method is used to set the margins of the worksheet when it is printed. It accepts left, right, top and bottom parameters whose values are in inches. All parameters are optional The left and right parameters are 0.7 by default, and top and bottom are 0.75 by default.
Python XlsxWriter - Header & Footer
当使用上述方法打印工作表时, header 和 footer 将在纸上生成。打印预览还显示页眉和页脚。两者均使用 set_header() 和 set_footer() 方法进行配置。页眉和页脚字符串通过以下控制字符进行配置 −
When the worksheet is printed using the above methods, the header and footer are generated on the paper. The print preview also displays the header and footer. Both are configured with set_header() and set_footer() methods. Header and footer string is configured by following control characters −
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() 方法 −
The following code uses set_header() and set_footer() methods −
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 XlsxWriter - Cell Comments
在 Excel 工作表中,可以出于各种原因插入 comments 。一种用法是解释单元格中的公式。此外,Excel 批注还可以作为其他用户提醒或笔记。它们对于与其他 Excel 工作簿交叉引用很有用。
In an Excel worksheet, comments can be inserted for various reasons. One of the uses is to explain a formula in a cell. Also, Excel comments also serve as reminders or notes for other users. They are useful for cross-referencing with other Excel workbooks.
从 Excel 的菜单系统,可在功能区中的“审阅”菜单中找到批注功能。
From Excel’s menu system, comment feature is available on Review menu in the ribbon.

若要添加和设置批注的格式,XlsxWriter 具有 add_comment() 方法。该方法的两个必需参数是 cell location (A1 类型或行号和列号)和 comment text 。
To add and format comments, XlsxWriter has add_comment() method. Two mandatory parameters for this method are cell location (either in A1 type or row and column number), and the comment text.
Example
以下是一个简单示例:
Here is a simple 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 单元格顶部右侧的标记上时,将看到一个带有批注。
When we open the workbook, a comment will be seen with a marker on top right corner of C3 cell when the cursor is placed in it.

默认情况下,批注不可见,直到光标悬停在写入批注的单元格上。可以通过调用工作表对象的 show_comment() 方法或者将单个批注的可见属性设置为 True 来显示工作表中的所有批注。
By default, the comments are not visible until the cursor hovers on the cell in which the comment is written. You can either show all the comments in a worksheet by invoking show_comment() method of worksheet object, or setting visible property of individual comment to True.
ws.write_comment('C3', text, {'visible': True})
Example
在以下代码中,放置了三条批注。但是,C3单元格中的一个已配置为可见属性设置为 False。因此,在光标置于该单元格之前看不到它。
In the following code, there are three comments placed. However, the one in cell C3 is has been configured with visible property set to False. Hence, it cannot be seen until the cursor is placed in the cell.
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
它将生成如下输出:
It will produce the following output −

可以设置 author 选项以指示单元格批注的作者。批注的作者还显示在工作表底部的状态栏中。
You can set author option to indicate who is the author of the cell comment. The author of the comment is also displayed in the status bar at the bottom of the worksheet.
worksheet.write_comment('C3', 'Atonement', {'author': 'Tutorialspoint'})
可以使用 set_comments_author() 方法设置所有单元格批注的默认作者:
The default author for all cell comments can be set using the set_comments_author() method −
worksheet.set_comments_author('Tutorialspoint')
它将生成如下输出:
It will produce the following output −

Python XlsxWriter - Working with Pandas
Pandas 是一个流行的 Python 库,用于数据处理和分析。我们可以使用 XlsWriter 将 Pandas dataframes 写入 Excel 工作表。
Pandas is a popular Python library for data manipulation and analysis. We can use XlsWriter for writing Pandas dataframes into an Excel worksheet.
要了解本节中描述的功能,我们需要在已安装 XlsxWriter 的相同环境中安装 Pandas 库。
To learn the features described in this section, we need to install Pandas library in the same environment in which XlsxWriter has been installed.
pip3 install pandas
Using XlsxWriter with Pandas
让我们从一个简单的示例开始。首先,根据整数列表中的数据创建一个 Pandas 数据框。然后使用 XlsxWriter 作为引擎来创建 Pandas Excel 写入器。借助此引擎对象,我们可以将数据框对象写入 Excel 工作表。
Let us start with a simple example. First, create a Pandas dataframe from the data from a list of integers. Then use XlsxWriter as the engine to create a Pandas Excel writer. With the help of this engine object, we can write the dataframe object to Excel worksheet.
Adding Charts to Pandas Dataframe
就像我们获取 Workbook 类的对象,然后通过调用其 add_worksheet() 方法获取 Worksheet 对象一样,写入器对象也可以用来获取这些对象。获取后,可以使用 XlsxWriter 方法来添加图表、数据表等。
Just as we obtain an object of Workbook class, and then a Worksheet object by calling its add_worksheet() method, the writer object can also be used to fetch these objects. Once we get them, the XlsxWriter methods to add chart, data table etc. can be employed.
在此示例中,我们设置 Pandas 数据框并获取其维度(或形状)。
In this example, we set up a Pandas dataframe and obtain its dimension (or shape).
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
从写入器创建工作簿和工作表对象。
The workbook and worksheet objects are created from the writer.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
其余的事情很简单。图表对象已添加到我们之前执行的操作中。
Rest of things are easy. The chart object is added as we have done earlier.
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 准备好了一个柱形图。
The following code uses Pandas dataframe to write an Excel workbook and a column chart is prepared by 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()
Writing Dataframe to Excel Table
类似地,可以将数据框写入到 Excel 表对象中。此处的 data 数据框是从 Python 字典中派生的,其中键是数据框列标头。每个键有一个列表作为值,而该值反过来成为每列的值。
Similarly, the dataframe can be written to Excel table object. The dataframe here is derived from a Python dictionary, where the keys are dataframe column headers. Each key has list as a value which in turn becomes values of each column.
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) 中
Use xlsxwriter engine to write the dataframe to a worksheet (sheet1)
writer = pd.ExcelWriter('hello.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)
以下行提供工作簿和工作表对象。
Following lines give Workbook and Worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
工作表中的数据借助 add_table() 方法转换为表。
Data in the worksheet is converted to Table with the help of add_table() method.
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 表中。
Below is the complete code to write pandas dataframe to Excel table.
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()
Python XlsxWriter - VBA Macro
在 Excel 中, macro 是由一系列已记录的步骤组成,可以通过快捷键多次重复这些步骤。在录制宏时执行的步骤被转换成编程指令 VBA,VBA 代表 Visual Basic for Applications。VBA 是 Visual Basic 语言的一个子集,专门用于自动执行 MS Office 应用程序(如 Word、Excel、PowerPoint 等等)中的任务。
In Excel, a macro is a recorded series of steps that can be repeated any number of times with a shortcut key. The steps performed while recording the macro are translated into programming instructions VBA which stands for Visual Basic for Applications. VBA is a subset of Visual basic language, especially written to automate the tasks in MS Office apps such as Word, Excel, PowerPoint etc.
在 MS Excel 的“开发工具”菜单中提供录制宏的选项。如果看不到此菜单,则必须通过转到“文件→选项→自定义”功能区屏幕来激活它。
The option to record a macro is available in the Developer menu of MS Excel. If this menu is not seen, it has to be activated by going to the "File→Options→Customize" ribbon screen.
如下图所示,通过转到“视图→宏→录制宏”单击录制宏按钮,为宏提供一个合适的名称并执行要记录的所需操作。在步骤结束之后,停止录制。分配一个所需的快捷键,以便可以重复录制的操作并按下该快捷键。
As shown in the following figure, click the Record Macro button by going to "View→Macros→Record Macro", and give a suitable name to the macro and perform desired actions to be recorded. After the steps are over stop the recording. Assign a desired shortcut so that the recorded action can be repeated as and it is pressed.

若要查看 VBA 代码,可以通过转到“视图→宏→查看宏”来编辑宏。从宏名称中选择宏并单击“编辑”。
To view the VBA code, edit the macro by going View→ZMacros→View Macros. Select the Macro from Macro name and click on Edit.

将显示 VBA 编辑器。删除 Excel 生成的所有步骤并添加语句以弹出消息框。
The VBA editor will be shown. Delete all the steps generated by Excel and add the statement to pop-up a message box.

确认宏运行完美。按 CTL+Shift+M ,然后弹出消息框。使用 .xlsm 扩展名保存此文件。它在内部包含 vbaproject.bin ,它是二进制 OLE COM 容器。若要从 Excel 宏文件中提取它,请使用 vba_extract.py 实用工具。
Confirm that the macro works perfectly. Press CTL+Shift+M and the message box pops up. Save this file with the .xlsm extension. It internally contains vbaproject.bin, a binary OLE COM container. To extract it from the Excel macro file, use the vba_extract.py utility.
(xlsxenv) E:\xlsxenv>vba_extract.py test.xlsm
Extracted: vbaProject.bin
Example
现在可以使用 add_vba_project() 方法将此 vbaProject.bin 文件添加到 XlsxWriter 工作簿中。在此工作表中,在 B3 单元格放置一个按钮对象,并将其链接到我们已经创建的宏(即 macro1 )
This vbaProject.bin file can now be added to the XlsxWriter workbook using the add_vba_project() method. On this worksheet, place a button object at B3 cell, and link it to the macro that we had already created (i.e., 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()