Python Openpyxl

Python Openpyxl is a library that allows for reading, writing, and modifying Excel files in Python.

Python Openpyxl is a powerful library for reading and writing Excel files in the .xlsx format. This connector allows Swimlane Turbine users to automate various Excel workbook tasks such as creating workbooks, applying cell formatting, and managing formulas without writing code. By integrating Python Openpyxl with Swimlane Turbine, users can streamline their data processing workflows, enhance productivity, and ensure consistent data management across their security operations.

Limitations

Only .xlsx format is supported. Legacy .xls files are not supported.

Formula results are only available via the data_only option if the file was previously saved by Microsoft Excel (openpyxl does not evaluate formulas itself).

Merged cells, pivot tables, and macros are preserved on load/save but cannot be created or modified by this connector.

Supported Versions

This connector uses openpyxl 3.x.

Additional Documents

openpyxl Documentation

openpyxl on PyPI

Configuration

Prerequisites

This connector is file-based and does not require any external API credentials or asset configuration. Excel workbook files (.xlsx) are passed directly as attachments to each action.

All actions that modify a workbook accept an attachment as input and return the updated workbook as an output attachment, which can be passed to subsequent actions in the playbook chain.

Authentication Methods

No authentication is required. This connector operates entirely on file attachments and does not connect to any external service.

Capabilities

This Python Openpyxl connector provides the following capabilities:

Create Workbook

Load Workbook

Save Workbook

Write Cell Values

Write / Read Formulas

Apply Cell Formatting

Add Filter to First Row

Set Column Widths

Create Workbook

Creates a new blank Excel workbook with one or more named worksheets and returns it as a file attachment. If no sheet names are provided, a single sheet named Sheet is created. The resulting file can be passed to subsequent actions for writing data or formatting.

Key inputs:

filename — Output filename (default: workbook.xlsx).

sheets — List of worksheet names to create.

Load Workbook

Loads an existing Excel workbook from a file attachment and returns metadata about its structure, including sheet names, active sheet, sheet dimensions, and any defined named ranges.

Key inputs:

attachments — The .xlsx file to inspect.

sheet_name — Sheet to retrieve dimension info for (defaults to the active sheet).

data_only — When true, returns cached formula values instead of formula strings.

read_only — When true, opens the workbook in read-only mode for faster access.

Save Workbook

Loads a workbook from a file attachment and saves it back as a new attachment, optionally under a different filename. Useful as the final step in a playbook to produce a clean named output file.

Key inputs:

attachments — The .xlsx file to save.

filename — Output filename (defaults to the original filename).

Write Cell Values

Writes one or more cell values into a workbook and returns the updated file. Cells can be targeted by coordinate (e.g. A1) or by 1-based row and column numbers. String values that look like numbers (e.g. "5000") are automatically coerced to numeric types so that formulas like SUM() work correctly.

Key inputs:

attachments — The .xlsx file to update.

sheet_name — Target worksheet (defaults to the active sheet).

cells — List of cell write operations, each with address (or row/column) and value.

filename — Output filename.

Write / Read Formulas

Writes Excel formula strings into specified cells or reads back formula strings (and optionally cached values) from a cell range.

Use operation: write to assign formulas (e.g. =SUM(B1:B10)) and operation: read to retrieve them. Note that cached numeric values are only present if the file was previously saved by Microsoft Excel.

Key inputs:

attachments — The .xlsx file to process.

operation — write or read.

formulas — List of { address, formula } objects (required for write).

cell_range — Range to read from, e.g. A1:C10 (used for read; defaults to the entire used range).

data_only — When true and operation is read, returns cached values instead of formula strings.

filename — Output filename.

Apply Cell Formatting

Applies font, fill (background color), border, and alignment formatting to one or more cells in a workbook. All formatting options are optional — only the provided options are applied.

Colors must be supplied as hex strings: 6-character RGB (e.g. FF0000) or 8-character ARGB (e.g. FFFF0000). A leading # and surrounding whitespace are accepted and stripped automatically.

Key inputs:

attachments — The .xlsx file to update.

cell_address — Cell or range to format, e.g. A1 or A1:D4.

font — Font options: name, size, bold, italic, underline, strike, color.

fill — Fill options: fill_type (e.g. solid), fg_color, bg_color.

border — Border options: style, color, sides.

alignment — Alignment options: horizontal, vertical, wrap_text.

filename — Output filename.

Add Filter to First Row

Adds an AutoFilter to the first row of a worksheet, covering all used columns. The filter dropdown arrows will be visible in Excel or any compatible application. Note that openpyxl writes the filter definition into the file but does not actually hide or rearrange rows — the filtering is applied when the file is opened in Excel.

Key inputs:

attachments — The .xlsx file to update.

sheet_name — Target worksheet (defaults to the active sheet).

filename — Output filename.

Key outputs:

filter_ref — The cell range the AutoFilter was applied to (e.g. A1:D100).

Set Column Widths

Sets the width of one or more columns in a worksheet. Width values are in Excel character units (the default Excel column width is approximately 8.43). Multiple columns can be resized in a single action call.

Key inputs:

attachments — The .xlsx file to update.

sheet_name — Target worksheet (defaults to the active sheet).

columns — List of column width definitions, each with column (e.g. "A") and width (e.g. 20).

filename — Output filename.

Key outputs:

columns_set — Number of columns whose width was set.

Notes

All actions that return a modified workbook include a file output that can be referenced in subsequent actions via $actions.<action_name>.result.file.

Chaining actions: pass the file output of one action as the attachments input of the next to build up a workbook step-by-step within a single playbook.

The connector does not evaluate formulas. To read formula results, the workbook must have been saved by Microsoft Excel first (so cached values are present), and data_only: true must be set on the Load Workbook or Write / Read Formulas action.

Actions

Add Filter to First Row

Add an AutoFilter to the first row of an Excel worksheet covering all used columns. Requires attachments input and must be applied in Excel or a compatible application.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to update.
attachments.file	string	Optional	Parameter for Add Filter to First Row
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Add Filter to First Row
sheet_name	string	Optional	Name of the worksheet to apply the filter to. Defaults to the active sheet if not provided.
filename	string	Optional	Output filename for the updated workbook. Defaults to the original filename.

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"sheet_name":"Example Name","filename":"Example Name"}

Output

Parameter	Type	Description
file	object	Updated Excel workbook file with AutoFilter applied.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource
filter_ref	string	The cell range the AutoFilter was applied to (e.g. "A1:D100").

Output Example

{"file":{"file":"string","file_name":"Example Name"},"filter_ref":"string"}

Apply Cell Formatting

Apply font, fill, border, and alignment formatting to specified cells in an Excel workbook using Python Openpyxl. Requires attachments and cell address.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to update.
attachments.file	string	Optional	Parameter for Apply Cell Formatting
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Apply Cell Formatting
sheet_name	string	Optional	Name of the worksheet containing the target cell(s). Defaults to the active sheet.
cell_address	string	Required	Cell coordinate or range to format (e.g. "A1", "B2:D4").
font	object	Optional	Font formatting options.
font.name	string	Optional	Font name (e.g. "Calibri", "Arial").
font.size	number	Optional	Font size in points.
font.bold	boolean	Optional	Apply bold style.
font.italic	boolean	Optional	Apply italic style.
font.underline	string	Optional	Underline style. One of "single", "double", "singleAccounting", "doubleAccounting", or "none".
font.strike	boolean	Optional	Apply strikethrough.
font.color	string	Optional	Font color as an ARGB hex string (e.g. "FF0000" for red).
fill	object	Optional	Cell fill (background) options.
fill.fill_type	string	Optional	Fill pattern type. Use "solid" for a solid background color.
fill.fg_color	string	Optional	Foreground (pattern) color as an ARGB hex string (e.g. "FFFF00" for yellow).
fill.bg_color	string	Optional	Background color as an ARGB hex string.
border	object	Optional	Border options applied to all specified sides.
border.style	string	Optional	Border line style. One of "thin", "medium", "thick", "double", "dashed", "dotted", "mediumDashed", "dashDot", "mediumDashDot", "dashDotDot", "mediumDashDotDot", or "slantDashDot".
border.color	string	Optional	Border color as an ARGB hex string (e.g. "000000" for black).
border.sides	array	Optional	Which sides to apply the border to. Values can be "left", "right", "top", "bottom".
alignment	object	Optional	Cell alignment options.
alignment.horizontal	string	Optional	Horizontal alignment. One of "general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed".
alignment.vertical	string	Optional	Vertical alignment. One of "top", "center", "bottom", "justify", "distributed".

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"sheet_name":"Example Name","cell_address":"string","font":{"name":"Example Name","size":123,"bold":true,"italic":true,"underline":"string","strike":true,"color":"string"},"fill":{"fill_type":"solid","fg_color":"string","bg_color":"string"},"border":{"style":"thin","color":"000000","sides":["string"]},"alignment":{"horizontal":"string","vertical":"string","wrap_text":true},"filename":"Example Name"}

Output

Parameter	Type	Description
file	object	Updated Excel workbook file.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource
cells_formatted	integer	Number of cells that had formatting applied.

Output Example

{"file":{"file":"string","file_name":"Example Name"},"cells_formatted":123}

Create Workbook

Create a new Excel workbook with one or more named worksheets and return it as a file attachment.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
filename	string	Optional	Filename for the new workbook.
sheets	array	Optional	List of worksheet names to create. If omitted a single sheet named "Sheet" is created. The first entry becomes the active sheet.

Input Example

{"filename":"Example Name","sheets":["string"]}

Output

Parameter	Type	Description
file	object	Newly created Excel workbook file.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource
sheet_names	array	Names of the worksheets that were created.

Output Example

{"file":{"file":"string","file_name":"Example Name"},"sheet_names":[]}

Load Workbook

Load an Excel workbook from a file attachment and return metadata such as sheet names, active sheet, and per-sheet dimensions.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to load.
attachments.file	string	Optional	Parameter for Load Workbook
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Load Workbook
data_only	boolean	Optional	When true, cells with formulas return the last cached value instead of the formula string.
read_only	boolean	Optional	When true, open the workbook in read-only mode (faster, lower memory).
sheet_name	string	Optional	Name of a specific sheet to retrieve detailed information for. If omitted, the active sheet is used.

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"data_only":true,"read_only":true,"sheet_name":"Example Name"}

Output

Parameter	Type	Description
sheet_names	array	Names of all worksheets in the workbook.
active_sheet	string	Title of the currently active worksheet.
sheet_info	object	Dimensions of the requested sheet (max_row, max_column, min_row, min_column).
sheet_info.max_row	integer	Output field: sheet_info.max_row
sheet_info.max_column	integer	Output field: sheet_info.max_column
sheet_info.min_row	integer	Output field: sheet_info.min_row
sheet_info.min_column	integer	Output field: sheet_info.min_column
named_ranges	array	List of named range titles defined in the workbook.

Output Example

{"sheet_names":[],"active_sheet":"string","sheet_info":{"max_row":123,"max_column":123,"min_row":123,"min_column":123},"named_ranges":[]}

Save Workbook

Load an Excel workbook from a file attachment and save it back as a file attachment, optionally under a new filename. Requires attachments.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to save.
attachments.file	string	Optional	Parameter for Save Workbook
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Save Workbook
filename	string	Optional	Output filename for the saved workbook. Defaults to the original filename if not provided.

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"filename":"Example Name"}

Output

Parameter	Type	Description
file	object	Saved Excel workbook file.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource

Output Example

{"file":{"file":"string","file_name":"Example Name"}}

Set Column Widths

Set the width of specified columns in an Excel worksheet using Python Openpyxl and return the updated file as an attachment. Requires attachments and columns inputs.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to update.
attachments.file	string	Optional	Parameter for Set Column Widths
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Set Column Widths
sheet_name	string	Optional	Name of the worksheet to update. Defaults to the active sheet if not provided.
columns	array	Required	List of column width definitions.
columns.column	string	Required	Column letter (e.g. "A", "B", "C").
columns.width	number	Required	Width in character units (e.g. 20 for a medium-width column).
filename	string	Optional	Output filename for the updated workbook. Defaults to the original filename.

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"sheet_name":"Example Name","columns":[{"column":"string","width":123}],"filename":"Example Name"}

Output

Parameter	Type	Description
file	object	Updated Excel workbook file.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource
columns_set	integer	Number of columns whose width was set.

Output Example

{"file":{"file":"string","file_name":"Example Name"},"columns_set":123}

Write Cell Values

Write one or more cell values into an Excel workbook using Python Openpyxl and return the updated file as an attachment. Cells can be addressed by coordinate (e.g., "A1") or by row/column numbers.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to update.
attachments.file	string	Optional	Parameter for Write Cell Values
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Write Cell Values
sheet_name	string	Optional	Name of the worksheet to write to. Defaults to the active sheet if not provided.
cells	array	Required	List of cell write operations.
cells.address	string	Optional	Cell coordinate such as "A1" or "C3". Takes priority over row/column if both are supplied.
cells.row	integer	Optional	1-based row number. Used when address is not provided.
cells.column	integer	Optional	1-based column number. Used when address is not provided.
cells.value	string	Required	Value to write into the cell. Strings, numbers, and booleans are all accepted.
filename	string	Optional	Output filename for the updated workbook. Defaults to the original filename.

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"sheet_name":"Example Name","cells":[{"address":"string","row":123,"column":123,"value":"string"}],"filename":"Example Name"}

Output

Parameter	Type	Description
file	object	Updated Excel workbook file.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource
cells_written	integer	Number of cells that were written.

Output Example

{"file":{"file":"string","file_name":"Example Name"},"cells_written":123}

Write / Read Formulas

Write or read Excel formula strings in cells using Python Openpyxl. Requires attachments and operation inputs to assign or retrieve formulas.

Endpoint

Method: GET

Input

Argument Name	Type	Required	Description
attachments	array	Required	Excel workbook file (.xlsx) to process.
attachments.file	string	Optional	Parameter for Write / Read Formulas
attachments.file_name	string	Optional	Name of the resource
attachments.description	string	Optional	Parameter for Write / Read Formulas
sheet_name	string	Optional	Name of the worksheet to target. Defaults to the active sheet.
operation	string	Required	Operation to perform. "write" assigns formula strings to the specified cells; "read" retrieves the formula string (or cached value) from a cell range.
formulas	array	Optional	List of formula write operations (required for "write" operation).
formulas.address	string	Required	Target cell coordinate (e.g. "A1").
formulas.formula	string	Required	Formula string including the leading "=" (e.g. "=SUM(B1:B10)").
cell_range	string	Optional	Cell range to read from (e.g. "A1:C10"). Used for the "read" operation. If omitted the entire used range of the sheet is read.
data_only	boolean	Optional	When true and operation is "read", return the last cached numeric value instead of the formula string. Note that cached values are only present if the file was last saved by Excel.
filename	string	Optional	Output filename for the updated workbook. Defaults to the original filename.

Input Example

{"attachments":[{"file":"string","file_name":"Example Name","description":"string"}],"sheet_name":"Example Name","operation":"write","formulas":[{"address":"string","formula":"string"}],"cell_range":"string","data_only":true,"filename":"Example Name"}

Output

Parameter	Type	Description
file	object	Updated Excel workbook file.
file.file	string	Output field: file.file
file.file_name	string	Name of the resource
cell_values	object	Map of cell coordinate to formula string or cached value (populated for "read" operation).
formulas_written	integer	Number of formulas written (populated for "write" operation).

Output Example

{"file":{"file":"string","file_name":"Example Name"},"cell_values":{},"formulas_written":123}

Response Headers

Header	Description	Example
Content-Type	The media type of the resource	application/json
Date	The date and time at which the message was originated	Thu, 01 Jan 2024 00:00:00 GMT