Python Xlsxwriter 简明教程

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. Parameters − row − The cell row (zero indexed). col − The cell column (zero indexed). *args − The additional args passed to the sub methods such as number, string and cell_format. Returns − 0 − Success -1 − Row or column is out of worksheet bounds.

write_string()

Writes a string to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − String truncated to 32k characters.

write_number()

Writes numeric types to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds.

write_formula()

Writes a formula or function to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). formula (string) −Formula to write to cell. cell_format (Format) − Optional Format object. value − Optional result. The value if the formula was calculated. Returns − 0 − Success -1 − Row or column is out of worksheet bounds.

insert_image()

Used to insert an image into a worksheet. The image can be in PNG, JPEG, GIF, BMP, WMF or EMF format. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). filename − Image filename (with path if required). Returns − 0 − Success -1 − Row or column is out of worksheet bounds.

insert_chart()

Used to insert a chart into a worksheet. A chart object is created via the Workbook add_chart() method. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). chart − A chart object.

conditional_format()

Used to add formatting to a cell or range of cells based on user-defined criteria. Parameters − first_row (int) − The first row of the range.(All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int)− The last col of the range. options (dict) − Conditional formatting options. must be a dictionary containing the parameters that describe the type and style of the conditional format Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − Incorrect parameter or option.

add_table()

Used to group a range of cells into an Excel Table. Parameters − first_row (int) − The first row of the range. (All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int) − The last col of the range. options (dict) − Table formatting options.

autofilter()

Set the auto-filter area in the worksheet. It adds drop down lists to the headers of a 2D range of worksheet data. User can filter the data based on simple criteria. Parameters − first_row (int) − The first row of the range. (All zero indexed) first_col (int) − The first column of the range. last_row (int) − The last row of the range. last_col (int)− The last col of the range.

Format Class

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

Format objects are created by calling the workbook add_format() method. Methods and properties available to this object are related to fonts, colors, patterns, borders, alignment and number formatting.

Font formatting methods and properties −

Method Name

Description

Property

set_font_name()

Font type

'font_name'

set_font_size()

Font size

'font_size'

set_font_color()

Font color

'font_color'

set_bold()

Bold

'bold'

set_italic()

Italic

'italic'

set_underline()

Underline

'underline'

set_font_strikeout()

Strikeout

'font_strikeout'

set_font_script()

Super/Subscript

'font_script'

Alignment formatting methods and properties

Method Name

Description

Property

set_align()

Horizontal align

'align'

set_align()

Vertical align

'valign'

set_rotation()

Rotation

'rotation'

set_text_wrap()

Text wrap

'text_wrap'

set_reading_order()

Reading order

'reading_order'

set_text_justlast()

Justify last

'text_justlast'

set_center_across()

Center across

'center_across'

set_indent()

Indentation

'indent'

set_shrink()

Shrink to fit

'shrink'

Chart Class

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

A chart object is created via the add_chart() method of the Workbook object where the chart type is specified.

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

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

The chart object is inserted in the worksheet by calling insert_chart() method.

worksheet.insert_chart('A7', chart)

XlxsWriter 支持以下图表类型 −

XlxsWriter supports the following chart types −

  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. Parameters − options (dict) − A dictionary of chart size options. name − Set the name (title) for the chart. The name is displayed above the chart. name_font − Set the font properties for the chart title. overlay − Allow the title to be overlaid on the chart. layout − Set the (x, y) position of the title in chart relative units.

set_legend()

This method formats the chart legends with the following properties − noneposition, font, borderfill, pattern, gradient

Chartsheet Class

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

A chartsheet in a XLSX file is a worksheet that only contains a chart and no other data. a new chartsheet object is created by calling the add_chartsheet() method from a Workbook object −

chartsheet = workbook.add_chartsheet()

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

Some functionalities of the Chartsheet class are similar to that of data Worksheets such as tab selection, headers, footers, margins, and print properties. However, its primary purpose is to display a single chart, whereas an ordinary data worksheet can have one or more embedded charts.

chartsheet 图表的数据必须出现在单独的工作表上。因此，它总是在使用 set_chart() 方法创建至少一个数据工作表的同时创建。

The data for the chartsheet chart must be present on a separate worksheet. Hence it is always created along with at least one data worksheet, using set_chart() method.

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

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

Remember that a Chartsheet can contain only one chart.

Example

以下代码在工作表名称 sheet1 中写入数据系列，但会打开一个新的图表表，以便基于 sheet1 中的数据添加柱形图。

The following code writes the data series in the worksheet names sheet1 but opens a new chartsheet to add a column chart based on the data in sheet1.

import xlsxwriter

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

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

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

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

cs.set_chart(chart)
cs.activate()

wb.close()

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.