Python Xlsxwriter 简明教程

Python XlsxWriter - Quick Guide

Python XlsxWriter - Overview

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

XlsxWriter 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 −

  1. Files created by XlsxWriter are 100% compatible with Excel XLSX files.

  2. XlsxWriter provides full formatting features such as Merged cells, Defined names, conditional formatting, etc.

  3. XlsxWriter allows programmatically inserting charts in XLSX files.

  4. Autofilters can be set using XlsxWriter.

  5. XlsxWriter supports Data validation and drop-down lists.

  6. Using XlsxWriter, it is possible to insert PNG/JPEG/GIF/BMP/WMF/EMF images.

  7. With XlsxWriter, Excel spreadsheet can be integrated with Pandas library.

  8. XlsxWriter also provides support for adding Macros.

  9. 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')

Example

hello.py 的完整代码如下 −

The complete code of hello.py is as follows −

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

Output

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

After the above code is executed, hello.xlsx file will be created in the current working directory. You can now open it using Excel software.

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 −

  1. Workbook class

  2. Worksheet class

  3. Format class

  4. Chart class

  5. Chartsheet class

  6. 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. Parametersrow − 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. Returns0 − Success -1 − Row or column is out of worksheet bounds.

write_string()

Writes a string to the cell specified by row and column. Parametersrow (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. Returns0 − 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. Parametersrow (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. Returns0 − 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. Parametersrow (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. Returns0 − 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. Parametersrow (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). filename − Image filename (with path if required). Returns0 − 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. Parametersrow (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. Parametersfirst_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 Returns0 − 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. Parametersfirst_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. Parametersfirst_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 −

  1. area − Creates an Area (filled line) style chart.

  2. bar − Creates a Bar style (transposed histogram) chart.

  3. column − Creates a column style (histogram) chart.

  4. line − Creates a Line style chart.

  5. pie − Creates a Pie style chart.

  6. doughnut − Creates a Doughnut style chart.

  7. scatter − Creates a Scatter style chart.

  8. stock − Creates a Stock style chart.

  9. 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. Parametersoptions (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()

Output

chartsheet class

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 notations1

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.

cell notations2

编号的 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()

Output

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

Execute the above program and open the ex1.xlsx using Excel software.

cell notations3

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

Output

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

Run the above program and open ex2.xlsx with Excel.

ex2 xlsx

Python XlsxWriter - Formula & Function

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

The Worksheet class offers three methods for using formulas.

  1. write_formula()

  2. write_array_formula()

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

Output

Excel 文件显示以下结果 −

The Excel file shows the following result −

write formula

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

Output

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

Here is how the worksheet appears when opened using MS Excel −

write array formula

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

Output

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

Note that the formula string to write_dynamic_array_formula() need not contain curly brackets. The resultant hello.xlsx must be opened with Excel 365 app.

write dynamic array data

Python XlsxWriter - Date & Time

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

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.

date format

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

Output

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

The worksheet looks like the following in Excel software −

num format Parameter

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

Output

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

The worksheet appears as follows when opened with Excel.

datetime and strptime

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

Output

以下是结果 −

Here’s the result −

add table

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

Output

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

The header row is now set as shown −

header row

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

Output

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

When the above code is executed, the worksheet shows the Total column with the sum of marks.

autofilter

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

filter criteria1

列参数可以是零索引列号或字符串列名。在 Python 中允许的所有逻辑运算符都可以在条件中使用(==、!=、<、>、⇐、>=)。可以针对多个列定义筛选条件,并且可以通过 andor 运算符将它们组合起来。带逻辑运算符的条件示例如下 −

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

Output

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

In Excel, the filter icon can be seen on columns A and D headings. The filtered data is seen as below −

filter criteria2

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.

column list filter1

从 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.

column list filter2

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

Output

以下是结果 −

Here is the result −

working with fonts1

Example

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

Output

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

The output of the above code can be verified by opening the worksheet with Excel −

working with fonts2

Text Alignment

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

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.

text alignment1

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

Output

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

In the above code, the height of rows 1 to 4 is set to 40 with set_row() method.

text alignment2

Cell Background and Foreground Colors

Format 对象有两个重要属性 bg_colorfg_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()

Output

上述代码结果如下所示 -

The result of above code looks like this −

cell background and foreground colors

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.

format cells

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

Output

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

The formatted number along with the format code used is shown in the following figure −

formatted number

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 −

  1. set_bottom()

  2. set_top()

  3. set_left()

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

Output

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

The worksheet shows a bold border around the cells.

cell border

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 −

  1. solid

  2. round_dot

  3. square_dot

  4. dash

  5. dash_dot

  6. long_dash

  7. long_dash_dot

  8. long_dash_dot_dot

Example

这是一个显示两个文本框的程序,其中一个具有蓝色实心边框,而第二个盒子具有蓝色 dash_dot 类型边框。

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

Output

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

The output worksheet shows the textbox borders.

textbox borders

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.

hyperlink1

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

Output

请注意, stringtip 参数作为 linktool tip 的备用文本。上面程序的输出如下所示 −

Note that the string and tip parameters are given as an alternative text to the link and tool tip. The output of the above program is as given below −

hyperlink2

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.

conditional formatting

在下方的工作表中,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.

conditional formatting1

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

This is achieved by defining a conditional formatting rule below −

conditional formatting2

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.

  1. Type is the most common conditional formatting type. It is used when a format is applied to a cell based on a simple criterion.

  2. 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.

  3. Value parameter is the operand of the criteria that forms the rule.

  4. 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.

parameters

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.

insert chart

为了以编程方式生成图表,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

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

The add_series() method has following additional parameters −

  1. Values − This is the most important property mandatory option. It links the chart with the worksheet data that it displays.

  2. Categories − This sets the chart category labels. If not given, the chart will just assume a sequential series from 1…n.

  3. Name − Set the name for the series. The name is displayed in the formula bar.

  4. Line − Set the properties of the series line type such as color and width.

  5. Border − Set the border properties of the series such as color and style.

  6. Fill − Set the solid fill properties of the series such as color.

  7. Pattern − Set the pattern fill properties of the series.

  8. Gradient − Set the gradient fill properties of the series.

  9. data_labels − Set data labels for the series.

  10. 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 series1

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.

hello xlsx

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

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 −

  1. Area − Creates an Area (filled line) style chart.

  2. Bar − Creates a Bar style (transposed histogram) chart.

  3. Column − Creates a column style (histogram) chart.

  4. Line − Creates a Line style chart.

  5. Pie − Creates a Pie style chart.

  6. Doughnut − Creates a Doughnut style chart.

  7. Scatter − Creates a Scatter style chart.

  8. Stock − Creates a Stock style chart.

  9. 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_offsety_offset 值以像素为单位,而 x_scaley_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. 1 − Move and size with cells (the default).

  2. 2 − Move but don’t size with cells.

  3. 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 −

  1. Set and format chart title

  2. Set the X and Y axis titles and other parameters

  3. Configure the chart legends

  4. Chat layout options

  5. 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 −

  1. 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.

  2. name_font − Set the font properties for the chart title.

  3. Overlay − Allow the title to be overlaid on the chart.

  4. Layout − Set the (x, y) position of the title in chart relative units.

  5. 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.

  1. name − Set the title or caption for the axis.

  2. name_font − Set the font properties for the axis title.

  3. num_font − Set the font properties for the axis numbers.

  4. num_format − Set the number format for the axis.

  5. major_gridlines − Configure the major gridlines for the axis.

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

Output

图表显示 titleaxes 标题,如下所示: −

The chart shows the title and axes captions as follows −

title axes

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 −

  1. None − In Excel chart legends are on by default. The none=True option turns off the chart legend.

  2. Position − Set the position of the chart legend. It can be set to top, bottom, left, right, none.

  3. Font − Set the font properties (like name, size, bold, italic etc.) of the chart legend.

  4. Border − Set the border properties of the legend such as color and style.

  5. Fill − Set the solid fill properties of the legend such as color.

  6. Pattern − Set the pattern fill properties of the legend.

  7. 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.

legend

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

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 −

bar chart1

条形图有两个子类型,分别是堆叠和 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()

Output

输出文件将如下所示:

The output file will look like the one given below −

bar chart2

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 −

sales analysis

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

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.

sales analysis1

折线图还支持 stackedpercent_stacked 子类型。

Line chart also supports stacked and percent_stacked subtypes.

sales analysis2

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.

pie chart of quarterly sales

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

Output

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

Have a look at the pie chart that the above program produces.

pie chart of quarterly sales1

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 −

doughnut chart

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 −

  1. line − Similar to line chart

  2. column − Similar to column chart

  3. 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.

sparklines

属性为 -

The properties are −

  1. range − is the mandatory parameter. It specifies the cell data range that the sparkline will plot.

  2. type − specifies the type of sparkline. There are 3 available sparkline types are line, column and win_loss.

  3. markers − Turn on the markers for line style sparklines

  4. style − The sparkline styles defined in MS Excel. There are 36 style types.

  5. negative_points − If set to True, the negative points in a sparkline are highlighted.

Example

以下程序生成一个有 markersline 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.

line sparkline

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

Output

它将生成如下输出:

It will produce the following output −

column sparkline

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 −

data validation

在第二个选项卡中,设置当用户光标所在期望单元格时闪现的消息,在本例中为“输入 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.

data validation1

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

The third tab allows asks you to define any error message you would like to flash if the validation criteria fails.

data validation2

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

When the user places the cursor in I10 (for which the validation is set), you can see the input message.

age1

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

When the entered number is not in the range, the error message will flash.

age2

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 −

  1. 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.

  2. criteria − It is used to set the criteria for validation. It can be set to any logical operator including between/ not between, ==, !=, <, >, , >=, etc.

  3. 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.

  4. input_title − Used to set the title of the input message when the cursor is placed in the target cell.

  5. input_message − The message to be displayed when a cell is entered.

  6. error_title − The title of the error message to be displayed when validation criteria is not met.

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

Output

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

The dropdown list appears when the cursor is placed in I10 cell −

dropdown list

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

Output

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

If the string is having less than 5 characters, the error message pops up as follows −

string

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.

outline

该工作表显示按项目划分的销售小计,最后是总计。该工作表左侧显示大纲级别。原始数据位于第 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.

item and sales

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.

outlines

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

At each level, the minus sign indicates that the rows can be collapsed and only the subtotal row will be displayed.

subtotal row

此图显示“@ {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.

grand total

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.

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

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.

top row

同样,我们可以让 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.

column a

通过将 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.

freeze panes

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

Output

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

Run the code and open hello.xlsx using Excel. As we can see, the worksheet is split into different panes at 10th row and 7th column.

split panes

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 −

hide

您不能隐藏 “ 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 −

hide column

同理,借助 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 −

hide row

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 −

text box

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.

text box with multiple lines

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.

text rotation

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. "1" − Move and size with cells (the default).

  2. "2" − Move but don’t size with cells.

  3. "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_scaley_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()

Output

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

Here is the view of the resultant worksheet −

insert image

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.

当使用上述方法打印工作表时, headerfooter 将在纸上生成。打印预览还显示页眉和页脚。两者均使用 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()

Output

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

Run the above Python code and open the worksheet. From File menu, choose Print option. On the right pane, the preview is shown. You should be able to see the header and footer.

header and footer

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.

comment

若要添加和设置批注的格式,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.

comment1

默认情况下,批注不可见,直到光标悬停在写入批注的单元格上。可以通过调用工作表对象的 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 −

show comment

可以设置 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 −

set comments author

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.

Example

import pandas as pd

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

writer.save()

Output

创建的工作表如下所示 −

The worksheet so created shows up as follows −

pandas dataframe

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

Output

数据和柱形图如下所示:

The column chart along with the data is shown below −

column chart

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

Output

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

The Table using default autofilter settings appears at A1 cell onwards.

autofilter

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.

record macro

若要查看 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.

macro name

将显示 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.

visual basic of applications

确认宏运行完美。按 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()

Output

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

When the above code is executed, the macro enabled workbook named testvba.xlsm will be created. Open it and click on the button. It will cause the message box to pop up as shown.

vba project