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 https //openpyxl readthedocs io/en/stable/openpyxl on pypi https //pypi org/project/openpyxl/ 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