---------------------------------------------------------------------- This is the API documentation for the fastpyxl library. ---------------------------------------------------------------------- ## Classes Main classes provided by the package Workbook(write_only=False, iso_dates=False) Workbook is the container for all other parts of the document. Cell(worksheet, row=None, column=None, value=None, style_array=None) Describes cell associated properties. Properties of interest include style, type, value, and address. MergedCell(worksheet, row=None, column=None) Describes the properties of a cell in a merged cell and helps to display the borders of the merged cell. The value of a MergedCell is always None. ReadOnlyCell(sheet, row, column, value, data_type='n', style_id=0, cached_value=None) AreaChart(axId=None, extLst=None, **kw) AreaChart3D(gapDepth=None, **kw) BarChart(gapWidth=150, overlap=None, serLines=None, extLst=None, **kw) BarChart3D(gapWidth=150, gapDepth=150, shape=None, serLines=None, extLst=None, **kw) BubbleChart(varyColors=None, ser=(), dLbls=None, bubble3D=None, bubbleScale=None, showNegBubbles=None, sizeRepresents=None, extLst=None, **kw) DoughnutChart(firstSliceAng=0, holeSize=10, extLst=None, **kw) LineChart(hiLowLines=None, upDownBars=None, marker=None, smooth=None, extLst=None, **kw) LineChart3D(gapDepth=None, hiLowLines=None, upDownBars=None, marker=None, smooth=None, extLst=None, **kw) PieChart(firstSliceAng=0, extLst=None, **kw) PieChart3D(extLst=None, **kw) ProjectedPieChart(ofPieType='pie', gapWidth=None, splitType='auto', splitPos=None, custSplit=None, secondPieSize=75, serLines=None, extLst=None, **kw) From the spec 21.2.2.126 This element contains the pie of pie or bar of pie series on this chart. Only the first series shall be displayed. The splitType element shall determine whether the splitPos and custSplit elements apply. RadarChart(radarStyle='standard', varyColors=None, ser=(), dLbls=None, extLst=None, **kw) Reference(worksheet=None, min_col=None, min_row=None, max_col=None, max_row=None, range_string=None, sheet_title=None) Normalise cell range references ScatterChart(scatterStyle=None, varyColors=None, ser=(), dLbls=None, extLst=None, **kw) StockChart(ser=(), dLbls=None, dropLines=None, hiLowLines=None, upDownBars=None, extLst=None, **kw) SurfaceChart(**kw) SurfaceChart3D(extLst=None, **kw) Chartsheet(sheetPr=None, sheetViews=None, sheetProtection=None, customSheetViews=None, pageMargins=None, pageSetup=None, headerFooter=None, drawing=None, drawingHF=None, picture=None, webPublishItems=None, extLst=None, parent=None, title='', sheet_state='visible') Comment(text, author, height=79, width=144) Drawing() a drawing object - eg container for shapes or charts we assume user specifies dimensions in pixels; units are converted to EMU in the drawing part Rule(type, dxfId=None, priority=0, stopIfTrue=None, aboveAverage=None, percent=None, bottom=None, operator=None, text=None, timePeriod=None, rank=None, stdDev=None, equalAverage=None, formula=(), colorScale=None, dataBar=None, iconSet=None, extLst=None, dxf=None) Tokenizer(formula) A tokenizer for Excel worksheet formulae. Converts a str string representing an Excel formula (in A1 notation) into a sequence of `Token` objects. `formula`: The str string to tokenize Tokenizer defines a method `._parse()` to parse the formula into tokens, which can then be accessed through the `.items` attribute. Alignment(horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0, relativeIndent=0, justifyLastLine=None, readingOrder=0, text_rotation=None, wrap_text=None, shrink_to_fit=None, mergeCell=None) Alignment options for use in styles. Border(left=None, right=None, top=None, bottom=None, diagonal=None, diagonal_direction=None, vertical=None, horizontal=None, diagonalUp=False, diagonalDown=False, outline=True, start=None, end=None) Border positioning for use in styles. Color(rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, index=None, type='rgb') Named colors for use in styles. Fill(**kwargs: 'Any') -> 'None' Base class Font(name=None, sz=None, b=None, i=None, charset=None, u=None, strike=None, color=None, scheme=None, family=None, size=None, bold=None, italic=None, strikethrough=None, underline=None, vertAlign=None, outline=None, shadow=None, condense=None, extend=None) Font options used in styles. GradientFill(type='linear', degree=0, left=0, right=0, top=0, bottom=0, stop=()) Fill areas with gradient Two types of gradient fill are supported: - A type='linear' gradient interpolates colours between a set of specified Stops, across the length of an area. The gradient is left-to-right by default, but this orientation can be modified with the degree attribute. A list of Colors can be provided instead and they will be positioned with equal distance between them. - A type='path' gradient applies a linear gradient from each edge of the area. Attributes top, right, bottom, left specify the extent of fill from the respective borders. Thus top="0.2" will fill the top 20% of the cell. NamedStyle(name='Normal', font=None, fill=None, border=None, alignment=None, number_format=None, protection=None, builtinId=None, hidden=False) Named and editable styles NumberFormatDescriptor() PatternFill(patternType=None, fgColor=, bgColor=, fill_type=None, start_color=None, end_color=None) Area fill patterns for use in styles. Caution: if you do not specify a fill_type, other attributes will have no effect ! Protection(locked=True, hidden=False) Protection options for use in styles. Side(style=None, color=None, border_style=None) Border options for use in styles. Caution: if you do not specify a border_style, other attributes will have no effect ! ElementList(iterable=(), /) Extension(uri=None) ExtensionList(ext=()) MetaSerialisable(name, bases, namespace) NestedValInt(val=None) Nested integer element with a val attribute. Serialisable(**kwargs: 'Any') -> 'None' ## Dataclasses Dataclass definitions FieldInfo(name: 'str', kind: 'FieldKind', expected_type: 'Any' = , allow_none: 'bool' = False, default: 'Any' = None, xml_name: 'str | None' = None, namespace: 'str | None' = None, hyphenated: 'bool' = False, count: 'bool' = False, container_factory: 'Callable[..., Any] | type | None' = , validator: 'Callable[[Any], None] | None' = None, converter: 'Callable[[Any], Any] | None' = None, parser: 'Callable[[Any], Any] | None' = None, renderer: 'Callable[[str, Any, str | None], Any] | None' = None, alias_target: 'str | None' = None, parts: 'dict[str, Any] | None' = None, sequence_item_is_model: 'bool' = False, value_attribute: 'str' = 'val', sequence_primitive_attribute: 'str | None' = None, serialize: 'bool' = True) -> None FieldInfo(name: 'str', kind: 'FieldKind', expected_type: 'Any' = , allow_none: 'bool' = False, default: 'Any' = None, xml_name: 'str | None' = None, namespace: 'str | None' = None, hyphenated: 'bool' = False, count: 'bool' = False, container_factory: 'Callable[..., Any] | type | None' = , validator: 'Callable[[Any], None] | None' = None, converter: 'Callable[[Any], Any] | None' = None, parser: 'Callable[[Any], Any] | None' = None, renderer: 'Callable[[str, Any, str | None], Any] | None' = None, alias_target: 'str | None' = None, parts: 'dict[str, Any] | None' = None, sequence_item_is_model: 'bool' = False, value_attribute: 'str' = 'val', sequence_primitive_attribute: 'str | None' = None, serialize: 'bool' = True) ## Workbook Methods Methods for the Workbook class create_sheet(self, title=None, index=None) Create a worksheet (at an optional index). :param title: optional title of the sheet :type title: str :param index: optional position at which the sheet will be inserted :type index: int move_sheet(self, sheet, offset=0) Move a sheet or sheetname remove(self, worksheet) Remove `worksheet` from this workbook. remove_sheet(self, worksheet) Remove `worksheet` from this workbook. create_chartsheet(self, title=None, index=None) get_sheet_by_name(self, name) Returns a worksheet by its name. :param name: the name of the worksheet to look for :type name: string __contains__(self, key) index(self, worksheet) Return the index of a worksheet. get_index(self, worksheet) Return the index of the worksheet. __getitem__(self, key) Returns a worksheet by its name. :param name: the name of the worksheet to look for :type name: string __delitem__(self, key) __iter__(self) get_sheet_names(self) create_named_range(self, name, worksheet=None, value=None, scope=None) Create a new named_range on a worksheet add_named_style(self, style) Add a named style save(self, filename) Save the current workbook under the given `filename`. Use this function instead of using an `ExcelWriter`. .. warning:: When creating your workbook using `write_only` set to True, you will only be able to call this function once. Subsequent attempts to modify or save the file will raise an :class:`fastpyxl.shared.exc.WorkbookAlreadySaved` exception. materialize_pending_style_components(self, styleable) -> None Register shared style parts for *styleable* from deferred assignments. Styling attributes on cells and dimensions defer pushing fonts, fills, borders, number formats, alignments, protections, and named styles into the workbook until this method runs or until :attr:`~fastpyxl.styles.styleable.StyleableObject.style_id` is read. Saving a workbook calls this automatically for every standard worksheet, in the same order as sheet XML serialization, so shared-table indices stay stable. Write-only streams still resolve styles when each row is written. copy_worksheet(self, from_worksheet) Copy an existing worksheet in the current workbook .. warning:: This function cannot copy worksheets between workbooks. worksheets can only be copied within the workbook that they belong :param from_worksheet: the worksheet to be copied from :return: copy of the initial worksheet close(self) Close workbook file if open. Only affects read-only, indexed, and write-only modes. ## Functions Utility functions WriteOnlyCell(ws=None, value=None) SeriesFactory(values, xvalues=None, zvalues=None, title=None, title_from_data=False) Convenience Factory for creating chart data series. deprecated(message: str, /, *, category: Optional[Type[Warning]] = , stacklevel: int = 1) -> None Indicate that a class, function or overload is deprecated. When this decorator is applied to an object, the type checker will generate a diagnostic on usage of the deprecated object. Usage: @deprecated("Use B instead") class A: pass @deprecated("Use g instead") def f(): pass @overload @deprecated("int support is deprecated") def g(x: int) -> int: ... @overload def g(x: str) -> int: ... The warning specified by *category* will be emitted at runtime on use of deprecated objects. For functions, that happens on calls; for classes, on instantiation and on creation of subclasses. If the *category* is ``None``, no warning is emitted at runtime. The *stacklevel* determines where the warning is emitted. If it is ``1`` (the default), the warning is emitted at the direct caller of the deprecated object; if it is higher, it is emitted further up the stack. Static type checker behavior is not affected by the *category* and *stacklevel* arguments. The deprecation message passed to the decorator is saved in the ``__deprecated__`` attribute on the decorated object. If applied to an overload, the decorator must be after the ``@overload`` decorator for the attribute to exist on the overload as returned by ``get_overloads()``. See PEP 702 for details. safe_string(value) Safely and consistently format numeric values load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True, rich_text=False, keep_formula_cache=False, access=None) Open the given filename and return the workbook :param filename: the path to open or a file-like object :type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile` :param read_only: optimised for reading, content cannot be edited :type read_only: bool :param keep_vba: preserve VBA content (this does NOT mean you can use it). ``True`` stores a filtered VBA cache of macro-relevant package parts in ``Workbook.vba_archive``. ``"full"`` (fastpyxl-specific) copies the entire package archive, matching openpyxl's ``keep_vba=True`` namelist behaviour. ``False`` discards VBA parts. :type keep_vba: bool or ``"full"`` :param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet :type data_only: bool :param keep_links: whether links to external workbooks should be preserved. The default is True :type keep_links: bool :param rich_text: if set to True fastpyxl will preserve any rich text formatting in cells. The default is False :type rich_text: bool :param keep_formula_cache: if True, keep both the formula (on ``cell.value``) and the last calculated cache from the file (on ``cell.cached_value``) in one parse. Mutually exclusive with ``data_only=True``. fastpyxl-only. :type keep_formula_cache: bool :param access: optional fastpyxl-only read strategy. ``None`` (default) keeps openpyxl-compatible eager or ``read_only`` behaviour. ``"indexed"`` builds row/SST byte-offset indexes over decompressed sheet and shared-string parts for sparse / random cell access. Implies read-only worksheets. :type access: ``None`` or ``"indexed"`` :rtype: :class:`fastpyxl.workbook.Workbook` .. note:: When using read-only mode, all worksheets will be :class:`fastpyxl.worksheet._read_only.ReadOnlyWorksheet` and the returned workbook will be read-only. When ``access="indexed"``, worksheets are :class:`fastpyxl.worksheet._indexed.IndexedWorksheet` (a read-only subclass) and resources must be released with :meth:`Workbook.close`. load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True, rich_text=False, keep_formula_cache=False, access=None) Open the given filename and return the workbook :param filename: the path to open or a file-like object :type filename: string or a file-like object open in binary mode c.f., :class:`zipfile.ZipFile` :param read_only: optimised for reading, content cannot be edited :type read_only: bool :param keep_vba: preserve VBA content (this does NOT mean you can use it). ``True`` stores a filtered VBA cache of macro-relevant package parts in ``Workbook.vba_archive``. ``"full"`` (fastpyxl-specific) copies the entire package archive, matching openpyxl's ``keep_vba=True`` namelist behaviour. ``False`` discards VBA parts. :type keep_vba: bool or ``"full"`` :param data_only: controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet :type data_only: bool :param keep_links: whether links to external workbooks should be preserved. The default is True :type keep_links: bool :param rich_text: if set to True fastpyxl will preserve any rich text formatting in cells. The default is False :type rich_text: bool :param keep_formula_cache: if True, keep both the formula (on ``cell.value``) and the last calculated cache from the file (on ``cell.cached_value``) in one parse. Mutually exclusive with ``data_only=True``. fastpyxl-only. :type keep_formula_cache: bool :param access: optional fastpyxl-only read strategy. ``None`` (default) keeps openpyxl-compatible eager or ``read_only`` behaviour. ``"indexed"`` builds row/SST byte-offset indexes over decompressed sheet and shared-string parts for sparse / random cell access. Implies read-only worksheets. :type access: ``None`` or ``"indexed"`` :rtype: :class:`fastpyxl.workbook.Workbook` .. note:: When using read-only mode, all worksheets will be :class:`fastpyxl.worksheet._read_only.ReadOnlyWorksheet` and the returned workbook will be read-only. When ``access="indexed"``, worksheets are :class:`fastpyxl.worksheet._indexed.IndexedWorksheet` (a read-only subclass) and resources must be released with :meth:`Workbook.close`. is_builtin(fmt) is_date_format(fmt) is_timedelta_format(fmt) AliasField(target: 'str', *, xml_name: 'str | None' = None, default: 'Any' = None) -> 'Any' explicit_none_element(tagname, value, namespace=None) Serialise explicit none values required by some chart elements. defusedxml_available() defusedxml_env_set() lxml_available() lxml_env_set() ## Constants Module-level constants and data compat.NUMERIC_TYPES Built-in immutable sequence. If no argument is given, the constructor returns an empty tuple. If iterable is specified the tuple is initialized from iterable's items. If the argument is a tuple, the return value is the same object. styles.DEFAULT_FONT Font options used in styles. typed_serialisable.Field xml.DEFUSEDXML bool(x) -> bool Returns True when the argument x is true, False otherwise. The builtins True and False are the only two instances of the class bool. The class bool is a subclass of the class int, and cannot be subclassed. xml.LXML bool(x) -> bool Returns True when the argument x is true, False otherwise. The builtins True and False are the only two instances of the class bool. The class bool is a subclass of the class int, and cannot be subclassed. ## Other Additional exports fastpyxl.packaging Stuff related to Office OpenXML packaging: relationships, archive, content types. fastpyxl.pivot fastpyxl.reader Workbook(write_only=False, iso_dates=False) Workbook is the container for all other parts of the document. fastpyxl.worksheet fastpyxl.writer ---------------------------------------------------------------------- This is the User Guide documentation for the package. ---------------------------------------------------------------------- ## Introduction # Tutorial ## Installation Install fastpyxl using pip. It is advisable to do this in a Python virtualenv without system packages: $ pip install fastpyxl :::: note ::: title Note ::: There is support for the popular [lxml](http://lxml.de) library which will be used if it is installed. This is particular useful when creating large files. :::: :::: warning ::: title Warning ::: To be able to include images (jpeg, png, bmp,\...) into an fastpyxl file, you will also need the \"pillow\" library that can be installed with: $ pip install pillow or browse , pick the latest version and head to the bottom of the page for Windows binaries. :::: ### Working with a checkout Sometimes you might want to work with the checkout of a particular version. This may be the case if bugs have been fixed but a release has not yet been made. ::: parsed-literal \$ uv pip install -e \"git+https://github.com/fastpyxl/fastpyxl@v[\|version\|](##SUBST##1.0)#egg=fastpyxl\" ::: ## Create a workbook There is no need to create a file on the filesystem to get started with fastpyxl. Just import the `Workbook`{.interpreted-text role="class"} class and start work: >>> from fastpyxl import Workbook >>> wb = Workbook() A workbook is always created with at least one worksheet. You can get it by using the `Workbook.active`{.interpreted-text role="obj"} property: >>> ws = wb.active :::: note ::: title Note ::: This is set to 0 by default. Unless you modify its value, you will always get the first worksheet by using this method. :::: You can create new worksheets using the `Workbook.create_sheet`{.interpreted-text role="meth"} method: >>> ws1 = wb.create_sheet("Mysheet") # insert at the end (default) # or >>> ws2 = wb.create_sheet("Mysheet", 0) # insert at first position # or >>> ws3 = wb.create_sheet("Mysheet", -1) # insert at the penultimate position Sheets are given a name automatically when they are created. They are numbered in sequence (Sheet, Sheet1, Sheet2, \...). You can change this name at any time with the `Worksheet.title`{.interpreted-text role="obj"} property: ws.title = "New Title" Once you gave a worksheet a name, you can get it as a key of the workbook: >>> ws3 = wb["New Title"] You can review the names of all worksheets of the workbook with the `Workbook.sheetname`{.interpreted-text role="obj"} attribute : >>> print(wb.sheetnames) ['Sheet2', 'New Title', 'Sheet1'] You can loop through worksheets : >>> for sheet in wb: ... print(sheet.title) You can create copies of worksheets **within a single workbook**: `Workbook.copy_worksheet`{.interpreted-text role="meth"} method: >>> source = wb.active >>> target = wb.copy_worksheet(source) :::: note ::: title Note ::: Only cells (including values, styles, hyperlinks and comments) and certain worksheet attributes (including dimensions, format and properties) are copied. All other workbook / worksheet attributes are not copied - e.g. Images, Charts. You also **cannot** copy worksheets between workbooks. You cannot copy a worksheet if the workbook is open in [read-only]{.title-ref} or [write-only]{.title-ref} mode. :::: ## Playing with data ### Accessing one cell Now we know how to get a worksheet, we can start modifying cells content. Cells can be accessed directly as keys of the worksheet: >>> c = ws['A4'] This will return the cell at A4, or create one if it does not exist yet. Values can be directly assigned: >>> ws['A4'] = 4 There is also the `Worksheet.cell`{.interpreted-text role="meth"} method. This provides access to cells using row and column notation: >>> d = ws.cell(row=4, column=2, value=10) :::: note ::: title Note ::: When a worksheet is created in memory, it contains no [cells]{.title-ref}. They are created when first accessed. :::: :::: warning ::: title Warning ::: Because of this feature, scrolling through cells instead of accessing them directly will create them all in memory, even if you don\'t assign them a value. Something like : >>> for x in range(1,101): ... for y in range(1,101): ... ws.cell(row=x, column=y) will create 100x100 cells in memory, for nothing. :::: ### Accessing many cells Ranges of cells can be accessed using slicing: >>> cell_range = ws['A1':'C2'] Ranges of rows or columns can be obtained similarly: >>> colC = ws['C'] >>> col_range = ws['C:D'] >>> row10 = ws[10] >>> row_range = ws[5:10] You can also use the `Worksheet.iter_rows`{.interpreted-text role="meth"} method: >>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2): ... for cell in row: ... print(cell) Likewise the `Worksheet.iter_cols`{.interpreted-text role="meth"} method will return columns: >>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2): ... for cell in col: ... print(cell) :::: note ::: title Note ::: For performance reasons the `Worksheet.iter_cols()`{.interpreted-text role="obj"} method is not available in read-only mode. :::: If you need to iterate through all the rows or columns of a file, you can instead use the `Worksheet.rows`{.interpreted-text role="obj"} property: >>> ws = wb.active >>> ws['C9'] = 'hello world' >>> tuple(ws.rows) ((, , ), (, , ), (, , ), (, , ), (, , ), (, , ), (, , ), (, , ), (, , )) or the `Worksheet.columns`{.interpreted-text role="obj"} property: >>> tuple(ws.columns) ((, , , , , , ... , , ), (, , , , , , , , )) :::: note ::: title Note ::: For performance reasons the `Worksheet.columns`{.interpreted-text role="obj"} property is not available in read-only mode. :::: ### Values only If you just want the values from a worksheet you can use the `Worksheet.values`{.interpreted-text role="obj"} property. This iterates over all the rows in a worksheet but returns just the cell values: for row in ws.values: for value in row: print(value) Both `Worksheet.iter_rows`{.interpreted-text role="meth"} and `Worksheet.iter_cols`{.interpreted-text role="meth"} can take the `values_only` parameter to return just the cell\'s value: >>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True): ... print(row) (None, None, None) (None, None, None) ## Data storage Once we have a `Cell`{.interpreted-text role="class"}, we can assign it a value: >>> c.value = 'hello, world' >>> print(c.value) 'hello, world' >>> d.value = 3.14 >>> print(d.value) 3.14 ### Saving to a file The simplest and safest way to save a workbook is by using the `Workbook.save`{.interpreted-text role="func"} method of the `Workbook`{.interpreted-text role="class"} object: >>> wb = Workbook() >>> wb.save('balances.xlsx') :::: warning ::: title Warning ::: This operation will overwrite existing files without warning. :::: :::: note ::: title Note ::: The filename extension is not forced to be xlsx or xlsm, although you might have some trouble opening it directly with another application if you don\'t use an official extension. As OOXML files are basically ZIP files, you can also open it with your favourite ZIP archive manager. :::: If required, you can specify the attribute [wb.template=True]{.title-ref}, to save a workbook as a template: >>> wb = load_workbook('document.xlsx') >>> wb.template = True >>> wb.save('document_template.xltx') ### Saving as a stream If you want to save the file to a stream, e.g. when using a web application such as Pyramid, Flask or Django then you can simply provide a `NamedTemporaryFile`{.interpreted-text role="func"}: >>> from tempfile import NamedTemporaryFile >>> from fastpyxl import Workbook >>> wb = Workbook() >>> with NamedTemporaryFile() as tmp: wb.save(tmp.name) tmp.seek(0) stream = tmp.read() :::: warning ::: title Warning ::: You should monitor the data attributes and document extensions for saving documents in the document templates and vice versa, otherwise the result table engine can not open the document. :::: :::: note ::: title Note ::: The following will fail: >>> wb = load_workbook('document.xlsx') >>> # Need to save with the extension *.xlsx >>> wb.save('new_document.xlsm') >>> # MS Excel can't open the document >>> >>> # or >>> >>> # Need specify attribute keep_vba=True >>> wb = load_workbook('document.xlsm') >>> wb.save('new_document.xlsm') >>> # MS Excel will not open the document >>> >>> # or >>> >>> wb = load_workbook('document.xltm', keep_vba=True) >>> # If we need a template document, then we must specify extension as *.xltm. >>> wb.save('new_document.xlsm') >>> # MS Excel will not open the document :::: ## Loading from a file You can use the `fastpyxl.load_workbook`{.interpreted-text role="func"} to open an existing workbook: >>> from fastpyxl import load_workbook >>> wb = load_workbook(filename = 'empty_book.xlsx') >>> sheet_ranges = wb['range names'] >>> print(sheet_ranges['D18'].value) 3 > There are several flags that can be used in load_workbook. > > - > > [data_only]{.title-ref} controls whether cells with formulae have either the > > : formula (default) or the value stored the last time Excel read > the sheet. > > - > > [keep_formula_cache]{.title-ref} (fastpyxl-only) keeps both the formula on > > : [cell.value]{.title-ref} and the last calculated cache on > [cell.cached_value]{.title-ref} in one parse. Mutually exclusive with > [data_only=True]{.title-ref}. Default is [False]{.title-ref}. > > - > > [keep_vba]{.title-ref} controls whether any Visual Basic elements are preserved or > > : not (default). If they are preserved they are still not > editable. With `True`, fastpyxl stores a filtered VBA cache > (macro-relevant parts only) in `Workbook.vba_archive`, not a > full package snapshot. Pass `keep_vba="full"` for an > openpyxl-identical full-archive mirror. > > - > > [read-only]{.title-ref} opens workbooks in a read-only mode. This uses much less > > : memory and is faster but not all features are available (charts, > images, etc.) > > - > > [rich_text]{.title-ref} controls whether any rich-text formatting in cells is > > : preserved. The default is [False]{.title-ref}. > > - > > [keep_links]{.title-ref} controls whether data cached from external workbooks is > > : preserved. > fastpyxl does currently not read all possible items in an Excel file > so shapes will be lost from existing files if they are opened and > saved with the same name. ## Errors loading workbooks Sometimes fastpyxl will fail to open a workbook. This is usually because there is something wrong with the file. If this is the case then fastpyxl will try and provide some more information. Fastpyxl follows the OOXML specification closely and will reject files that do not because they are invalid. When this happens you can use the exception from fastpyxl to inform the developers of whichever application or library produced the file. As the OOXML specification is publicly available it is important that developers follow it. You can find the spec by searching for ECMA-376, most of the implementation specifics are in Part 4. This ends the tutorial for now, you can proceed to the `usage`{.interpreted-text role="doc"} section # Simple usage ## Example: Creating a simple spreadsheet and bar chart In this example we\'re going to create a sheet from scratch and add some data and then plot it. We\'ll also explore some limited cell style and formatting. The data we\'ll be entering on the sheet is below: Species Leaf Color Height (cm) --------- ------------ ------------- Maple Red 549 Oak Green 783 Pine Green 1204 To start, let\'s load in fastpyxl and create a new workbook. and get the active sheet. We\'ll also enter our tree data. \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> treeData = \[\[\"Type\", \"Leaf Color\", \"Height\"\], \[\"Maple\", \"Red\", 549\], \[\"Oak\", \"Green\", 783\], \[\"Pine\", \"Green\", 1204\]\] Next we\'ll enter this data onto the worksheet. As this is a list of lists, we can simply use the `Worksheet.append`{.interpreted-text role="func"} function. \>\>\> for row in treeData: \... ws.append(row) Now we should make our heading Bold to make it stand out a bit more, to do that we\'ll need to create a `styles.Font`{.interpreted-text role="class"} and apply it to all the cells in our header row. \>\>\> from fastpyxl.styles import Font \>\>\> ft = Font(bold=True) \>\>\> for row in ws\[\"A1:C1\"\]: \... for cell in row: \... cell.font = ft It\'s time to make some charts. First, we\'ll start by importing the appropriate packages from `fastpyxl.chart`{.interpreted-text role="class"} then define some basic attributes \>\>\> from fastpyxl.chart import BarChart, Series, Reference \>\>\> chart = BarChart() \>\>\> chart.type = \"col\" \>\>\> chart.title = \"Tree Height\" \>\>\> chart.y_axis.title = \'Height (cm)\' \>\>\> chart.x_axis.title = \'Tree Type\' \>\>\> chart.legend = None That\'s created the skeleton of what will be our bar chart. Now we need to add references to where the data is and pass that to the chart object \>\>\> data = Reference(ws, min_col=3, min_row=2, max_row=4, max_col=3) \>\>\> categories = Reference(ws, min_col=1, min_row=2, max_row=4, max_col=1) \>\>\> chart.add_data(data) \>\>\> chart.set_categories(categories) Finally we can add it to the sheet. \>\>\> ws.add_chart(chart, \"E1\") \>\>\> wb.save(\"TreeData.xlsx\") And there you have it. If you open that doc now it should look something like this ![image](exercise-1-result.png) # Compatibility with openpyxl fastpyxl is a drop-in replacement for **openpyxl >= 3.0.0** on **Python >= 3.11**. Users on openpyxl 3.x should be able to change imports only: ```python # before from openpyxl import Workbook, load_workbook # after from fastpyxl import Workbook, load_workbook ``` No other code changes are required for supported openpyxl 3.x usage. ## Supported baseline | Constraint | Value | | --- | --- | | openpyxl API surface | `>= 3.0.0` | | Python | `>= 3.11` | | CI enforcement pin | openpyxl `3.1.5` | The public claim is compatibility with the openpyxl 3.x API family starting at 3.0.0. Continuous integration pins the current openpyxl 3.1.x release used for benchmarks and runs the dedicated compatibility suite against that pin. ## Intentional non-goals fastpyxl does **not** aim to preserve: - APIs removed before openpyxl 3.0 - Behaviour only present in openpyxl 2.x - Legacy load options such as `use_iterators` (replaced by `read_only`) - Historical dual worksheet implementations (`IterableWorksheet` vs `Worksheet`); read-only mode uses `ReadOnlyWorksheet` ## Running the compatibility suite The openpyxl drop-in contract is enforced by tests under `fastpyxl/tests/compat`, marked `openpyxl_compat`. CI runs them in a dedicated job; locally: ```bash uv sync --all-groups uv run pytest fastpyxl/tests/compat -m openpyxl_compat ``` `openpyxl==3.1.5` is installed via the `dev` dependency group. ## Documenting intentional divergences When fastpyxl deliberately differs from openpyxl, document that difference as a reviewed `xfail` (or an allowlist entry in the API-surface tests) rather than allowing silent drift. Prefer: 1. A short comment next to the `xfail` / allowlist entry explaining why 2. A note in this page if the divergence affects end users 3. A changelog entry when the divergence is introduced or removed ### `vba_archive` filtering (`keep_vba`) With `keep_vba=True`, openpyxl 3.1.5 copies the **entire** package into `Workbook.vba_archive`. fastpyxl instead stores a **filtered VBA cache**: only macro-relevant parts (`xl/vba*`, VML drawings, ActiveX, ctrlProps, customUI, EMF media, plus `[Content_Types].xml` and `_rels/.rels`). That filtered cache is enough for load→edit→save of macros; the writer already applies the same `ARC_VBA` allowlist on save. Filtering avoids cloning unused worksheet/XML parts on large `.xlsm` files. Callers that inspect non-VBA members of `wb.vba_archive` can opt into openpyxl-identical behaviour with the fastpyxl-specific escape hatch: ```python wb = load_workbook("macros.xlsm", keep_vba=True) # filtered cache (default) wb = load_workbook("macros.xlsm", keep_vba="full") # full package mirror wb = load_workbook("macros.xlsm", keep_vba=False) # no vba_archive ``` `True` / `False` remain drop-in compatible with openpyxl's bool API. `keep_vba="full"` is fastpyxl-only. ### Dual-load formulas and caches (`keep_formula_cache`) Excel sheet XML often stores both a formula (``) and a last-calculated cache (``) on the same cell. openpyxl (and default fastpyxl) keep only one or the other via `data_only`. fastpyxl adds an opt-in dual-load mode that keeps both in a single parse: ```python wb = load_workbook("book.xlsx", keep_formula_cache=True) assert wb.data_only is False c = wb.active["D2"] c.value # formula text, e.g. "='Sheet2'!D5" c.cached_value # cached result from the file, or None ``` Compatibility constraints: - `load_workbook(path)` and `load_workbook(path, data_only=True)` remain unchanged (and openpyxl-compatible) for `.value`, `.data_type`, iteration, and save output. In those modes `cell.cached_value` is always `None`. - `data_only` stays a bool; do not pass `"both"`. - `data_only=True` and `keep_formula_cache=True` together raise `ValueError`. - Assigning `.value` clears `.cached_value` (the cache is stale). - On standard worksheets, caches are stored in a sheet-level side map (`ws._formula_caches`) rather than a per-cell slot, so default-mode loads do not pay a pointer per cell. Only the live sheet cell at a coordinate may read or write that map entry; detached helpers such as `WriteOnlyCell` are ignored. `read_only` cells keep instance storage because they are ephemeral and must not accumulate a sheet-wide map. - Save still writes empty `` for formula cells in v1; emitting real caches is deferred. `keep_formula_cache` is fastpyxl-only. ### Indexed / on-demand reads (`access`) Default `load_workbook` and `read_only=True` keep their openpyxl semantics. fastpyxl adds an opt-in indexed read strategy for sparse / random access: ```python wb = load_workbook("book.xlsx", access="indexed") assert wb.read_only is True print(wb.active["Z100000"].value) wb.close() ``` Compatibility constraints: - `access=None` (default) is unchanged and openpyxl-compatible. - `access="indexed"` implies read-only worksheets; `read_only=True` may be passed together and is accepted. - Unknown `access` values raise `ValueError`. - Misses return `EMPTY_CELL` (no densifying writes). - Styles stay eagerly loaded; only sheet rows and shared strings are indexed. - Like `read_only`, indexed worksheets do not bind merges, comments, tables, drawings, or other worksheet relationship metadata. `access` is fastpyxl-only. ## Environment variables Prefer the `FASTPYXL_*` names: | Setting | Preferred | Legacy alias (deprecated) | | --- | --- | --- | | Use lxml when available | `FASTPYXL_LXML` | `OPENPYXL_LXML` | | Use defusedxml when available | `FASTPYXL_DEFUSEDXML` | `OPENPYXL_DEFUSEDXML` | | Keep VBA on load (tests) | `FASTPYXL_KEEP_VBA` | `OPENPYXL_KEEP_VBA` | Using a legacy `OPENPYXL_*` name still works for one release cycle but emits a `DeprecationWarning`. Migrate to the `FASTPYXL_*` equivalent. ## Removal plan for inherited compatibility cruft The openpyxl lineage left several deprecated call sites and shims. With the 3.0+ contract published, removals proceed in phases: ### Phase 1 — warn (current) - Emit `DeprecationWarning` for legacy `OPENPYXL_*` environment aliases - Keep deprecated openpyxl 3.x workbook helpers (`get_sheet_by_name`, `get_sheet_names`, `remove_sheet`, `get_index`, `create_named_range`) and `Worksheet.merged_cell_ranges` while they remain part of the openpyxl 3.x surface that callers still use - Clean obsolete comments/docstrings that refer to pre-3.0 dual implementations (`IterableWorksheet`, `use_iterators`) ### Phase 2 — remove aliases - Drop `OPENPYXL_*` environment aliases after at least one released version has carried the Phase 1 warnings - Refactor `reader/excel.py` so production code does not import test helpers such as `KEEP_VBA` from `fastpyxl.tests` ### Phase 3 — major-version API trim - Remove deprecated openpyxl 3.x methods/properties only in a major release (v2.0), with release notes listing replacements (`wb[sheetname]`, `wb.sheetnames`, `wb.remove()`, `wb.index()`, etc.) - Revisit type-checker carve-outs in `pyproject.toml` that exist only for legacy Python 2 / transitional code once that code is gone Deprecated APIs that openpyxl itself still exposes in 3.x remain available in fastpyxl v1.x so import-only migrations keep working. They are not a promise of indefinite support beyond the next major version. ## Styling # Working with styles ## Introduction Styles are used to change the look of your data while displayed on screen. They are also used to determine the formatting for numbers. Styles can be applied to the following aspects: > - font to set font size, color, underlining, etc. > - fill to set a pattern or color gradient > - border to set borders on a cell > - cell alignment > - protection The following are the default values \>\>\> from fastpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font \>\>\> font = Font(name=\'Calibri\', \... size=11, \... bold=False, \... italic=False, \... vertAlign=None, \... underline=\'none\', \... strike=False, \... color=\'FF000000\') \>\>\> fill = PatternFill(fill_type=None, \... start_color=\'FFFFFFFF\', \... end_color=\'FF000000\') \>\>\> border = Border(left=Side(border_style=None, \... color=\'FF000000\'), \... right=Side(border_style=None, \... color=\'FF000000\'), \... top=Side(border_style=None, \... color=\'FF000000\'), \... bottom=Side(border_style=None, \... color=\'FF000000\'), \... diagonal=Side(border_style=None, \... color=\'FF000000\'), \... diagonal_direction=0, \... outline=Side(border_style=None, \... color=\'FF000000\'), \... vertical=Side(border_style=None, \... color=\'FF000000\'), \... horizontal=Side(border_style=None, \... color=\'FF000000\') \... ) \>\>\> alignment=Alignment(horizontal=\'general\', \... vertical=\'bottom\', \... text_rotation=0, \... wrap_text=False, \... shrink_to_fit=False, \... indent=0) \>\>\> number_format = \'General\' \>\>\> protection = Protection(locked=True, \... hidden=False) \>\>\> ## Cell Styles and Named Styles There are two types of styles: cell styles and named styles, also known as style templates. ### Cell Styles Cell styles are shared between objects and once they have been assigned they cannot be changed. This stops unwanted side-effects such as changing the style for lots of cells when only one changes. \>\>\> from fastpyxl.styles import colors \>\>\> from fastpyxl.styles import Font, Color \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> a1 = ws\[\'A1\'\] \>\>\> d4 = ws\[\'D4\'\] \>\>\> ft = Font(color=\"FF0000\") \>\>\> a1.font = ft \>\>\> d4.font = ft \>\>\> \>\>\> a1.font.italic = True \# is not allowed \# doctest: +SKIP \>\>\> \>\>\> \# If you want to change the color of a Font, you need to reassign it:: \>\>\> \>\>\> a1.font = Font(color=\"FF0000\", italic=True) \# the change only affects A1 ## Copying styles Styles can also be copied \>\>\> from fastpyxl.styles import Font \>\>\> from copy import copy \>\>\> \>\>\> ft1 = Font(name=\'Arial\', size=14) \>\>\> ft2 = copy(ft1) \>\>\> ft2.name = \"Tahoma\" \>\>\> ft1.name \'Arial\' \>\>\> ft2.name \'Tahoma\' \>\>\> ft2.size \# copied from the 14.0 ## Colours Colours for fonts, backgrounds, borders, etc. can be set in three ways: indexed, aRGB or theme. Indexed colours are the legacy implementation and the colours themselves depend upon the index provided with the workbook or with the application default. Theme colours are useful for complementary shades of colours but also depend upon the theme being present in the workbook. It is, therefore, advisable to use aRGB colours. ### aRGB colours RGB colours are set using hexadecimal values for red, green and blue. \>\>\> from fastpyxl.styles import Font \>\>\> font = Font(color=\"FF0000\") The alpha value refers in theory to the transparency of the colour but this is not relevant for cell styles. The default of 00 will prepended to any simple RGB value: \>\>\> from fastpyxl.styles import Font \>\>\> font = Font(color=\"00FF00\") \>\>\> font.color.rgb \'0000FF00\' There is also support for legacy indexed colours as well as themes and tints. \>\>\> from fastpyxl.styles.colors import Color \>\>\> c = Color(indexed=32) \>\>\> c = Color(theme=6, tint=0.5) ### Indexed Colours
Standard Colours
Index
0-4 00000000 00FFFFFF 00FF0000 0000FF00 000000FF
5-9 00FFFF00 00FF00FF 0000FFFF 00000000 00FFFFFF
10-14 00FF0000 0000FF00 000000FF 00FFFF00 00FF00FF
15-19 0000FFFF 00800000 00008000 00000080 00808000
20-24 00800080 00008080 00C0C0C0 00808080 009999FF
25-29 00993366 00FFFFCC 00CCFFFF 00660066 00FF8080
30-34 000066CC 00CCCCFF 00000080 00FF00FF 00FFFF00
35-39 0000FFFF 00800080 00800000 00008080 000000FF
40-44 0000CCFF 00CCFFFF 00CCFFCC 00FFFF99 0099CCFF
45-49 00FF99CC 00CC99FF 00FFCC99 003366FF 0033CCCC
50-54 0099CC00 00FFCC00 00FF9900 00FF6600 00666699
55-60 00969696 00003366 00339966 00003300 00333300
60-63 00993300 00993366 00333399 00333333
The indices 64 and 65 cannot be set and are reserved for the system foreground and background colours respectively. ## Applying Styles Styles are applied directly to cells \>\>\> from fastpyxl.workbook import Workbook \>\>\> from fastpyxl.styles import Font, Fill \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> c = ws\[\'A1\'\] \>\>\> c.font = Font(size=12) ## Columns and Rows Styles can also applied to columns and rows but note that this applies only to cells created (in Excel) after the file is closed. If you want to apply styles to entire rows and columns then you **must** apply the style to each cell individually. This is a restriction of the file format: >>> col = ws.column_dimensions['A'] >>> col.font = Font(bold=True) >>> row = ws.row_dimensions[1] >>> row.font = Font(underline="single") :::: note ::: title Note ::: Column dimensions can be grouped, although this is primarily for outline purposes, it can also be used for other attributes, which can be confusing because only the first column of the group will be listed. Use [ws.column_groups]{.title-ref} to check. :::: ## Styling Merged Cells The merged cell behaves similarly to other cell objects. Its value and format is defined in its top-left cell. In order to change the border of the whole merged cell, change the border of its top-left cell. The formatting is generated for the purpose of writing. \>\>\> from fastpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment \>\>\> from fastpyxl import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> ws.merge_cells(\'B2:F4\') \>\>\> \>\>\> top_left_cell = ws\[\'B2\'\] \>\>\> top_left_cell.value = \"My Cell\" \>\>\> \>\>\> thin = Side(border_style=\"thin\", color=\"000000\") \>\>\> double = Side(border_style=\"double\", color=\"ff0000\") \>\>\> \>\>\> top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double) \>\>\> top_left_cell.fill = PatternFill(\"solid\", fgColor=\"DDDDDD\") \>\>\> top_left_cell.fill = fill = GradientFill(stop=(\"000000\", \"FFFFFF\")) \>\>\> top_left_cell.font = Font(b=True, color=\"FF0000\") \>\>\> top_left_cell.alignment = Alignment(horizontal=\"center\", vertical=\"center\") \>\>\> \>\>\> wb.save(\"styled.xlsx\") ## Using number formats You can specify the number format for cells, or for some instances (ie datetime) it will automatically format. \>\>\> import datetime \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \# set date using a Python datetime \>\>\> ws\[\'A1\'\] = datetime.datetime(2010, 7, 21) \>\>\> \>\>\> ws\[\'A1\'\].number_format \'yyyy-mm-dd h:mm:ss\' \>\>\> \>\>\> ws\[\"A2\"\] = 0.123456 \>\>\> ws\[\"A2\"\].number_format = \"0.00\" \# Display to 2dp ## Edit Page Setup \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE \>\>\> ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID \>\>\> ws.page_setup.fitToHeight = 0 \>\>\> ws.page_setup.fitToWidth = 1 ### Named Styles In contrast to Cell Styles, Named Styles are mutable. They make sense when you want to apply formatting to lots of different cells at once. NB. once you have assigned a named style to a cell, additional changes to the style will **not** affect the cell. Once a named style has been registered with a workbook, it can be referred to simply by name. ## Creating a Named Style \>\>\> from fastpyxl.styles import NamedStyle, Font, Border, Side \>\>\> highlight = NamedStyle(name=\"highlight\") \>\>\> highlight.font = Font(bold=True, size=20) \>\>\> bd = Side(style=\'thick\', color=\"000000\") \>\>\> highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) Once a named style has been created, it can be registered with the workbook: \>\>\> wb.add_named_style(highlight) But named styles will also be registered automatically the first time they are assigned to a cell: \>\>\> ws\[\'A1\'\].style = highlight Once registered, assign the style using just the name: \>\>\> ws\[\'D5\'\].style = \'highlight\' ## Using builtin styles The specification includes some builtin styles which can also be used. Unfortunately, the names for these styles are stored in their localised forms. fastpyxl will only recognise the English names and only exactly as written here. These are as follows: - \'Normal\' \# same as no style ### Number formats - \'Comma\' - \'Comma \[0\]\' - \'Currency\' - \'Currency \[0\]\' - \'Percent\' ### Informative - \'Calculation\' - \'Total\' - \'Note\' - \'Warning Text\' - \'Explanatory Text\' ### Text styles - \'Title\' - \'Headline 1\' - \'Headline 2\' - \'Headline 3\' - \'Headline 4\' - \'Hyperlink\' - \'Followed Hyperlink\' - \'Linked Cell\' ### Comparisons - \'Input\' - \'Output\' - \'Check Cell\' - \'Good\' - \'Bad\' - \'Neutral\' ### Highlights - \'Accent1\' - \'20 % - Accent1\' - \'40 % - Accent1\' - \'60 % - Accent1\' - \'Accent2\' - \'20 % - Accent2\' - \'40 % - Accent2\' - \'60 % - Accent2\' - \'Accent3\' - \'20 % - Accent3\' - \'40 % - Accent3\' - \'60 % - Accent3\' - \'Accent4\' - \'20 % - Accent4\' - \'40 % - Accent4\' - \'60 % - Accent4\' - \'Accent5\' - \'20 % - Accent5\' - \'40 % - Accent5\' - \'60 % - Accent5\' - \'Accent6\' - \'20 % - Accent6\' - \'40 % - Accent6\' - \'60 % - Accent6\' - \'Pandas\' For more information about the builtin styles please refer to the `fastpyxl.styles.builtins`{.interpreted-text role="mod"} # Working with Rich Text ## Introduction Normally styles apply to everything in an individual cell. However, rich text allows formatting of parts of the text in a string. This section covers adding rich-text formatting to worksheet cells. Rich-text formatting in existing workbooks has to be enabled when loading them with the [rich_text=True]{.title-ref} parameter. Rich Text objects can contain a mix of unformatted text and `TextBlock`{.interpreted-text role="class"} objects that contains an `InlineFont`{.interpreted-text role="class"} style and a the text which is to be formatted like this. The result is a `CellRichText`{.interpreted-text role="class"} object. \>\>\> from fastpyxl.cell.text import InlineFont \>\>\> from fastpyxl.cell.rich_text import TextBlock, CellRichText \>\>\> rich_string1 = CellRichText( \... \'This is a test \', \... TextBlock(InlineFont(b=True), \'xxx\'), \... \'yyy\' \... ) `InlineFont`{.interpreted-text role="class"} objects are virtually identical to the `Font`{.interpreted-text role="class"} objects, but use a different attribute name, [rFont]{.title-ref}, for the name of the font. Unfortunately, this is required by OOXML and cannot be avoided. \>\>\> inline_font = InlineFont(rFont=\'Calibri\', \# Font name \... sz=22, \# in 1/144 in. (1/2 point) units, must be integer \... charset=None, \# character set (0 to 255), less required with UTF-8 \... family=None, \# Font family \... b=True, \# Bold (True/False) \... i=None, \# Italics (True/False) \... strike=None, \# strikethrough \... outline=None, \... shadow=None, \... condense=None, \... extend=None, \... color=None, \... u=None, \... vertAlign=None, \... scheme=None, \... ) Fortunately, if you already have a `Font`{.interpreted-text role="class"} object, you can simply initialize an `InlineFont`{.interpreted-text role="class"} object with an existing `Font`{.interpreted-text role="class"} object: \>\>\> from fastpyxl.cell.text import Font \>\>\> font = Font(name=\'Calibri\', \... size=11, \... bold=False, \... italic=False, \... vertAlign=None, \... underline=\'none\', \... strike=False, \... color=\'00FF0000\') \>\>\> inline_font = InlineFont(font) You can create `InlineFont`{.interpreted-text role="class"} objects on their own, and use them later. This makes working with Rich Text cleaner and easier: \>\>\> big = InlineFont(sz=\"30.0\") \>\>\> medium = InlineFont(sz=\"20.0\") \>\>\> small = InlineFont(sz=\"10.0\") \>\>\> bold = InlineFont(b=True) \>\>\> b = TextBlock \>\>\> rich_string2 = CellRichText( \... b(big, \'M\'), \... b(medium, \'i\'), \... b(small, \'x\'), \... b(medium, \'e\'), \... b(big, \'d\') \... ) For example: \>\>\> red = InlineFont(color=\'00FF0000\') \>\>\> rich_string1 = CellRichText(\[\'When the color \', TextBlock(red, \'red\'), \' is used, you can expect \', TextBlock(red, \'danger\')\]) The `CellRichText`{.interpreted-text role="class"} object is derived from [list]{.title-ref}, and can be used as such. ### Whitespace CellRichText objects do not add whitespace between elements when rendering them as strings or saving files. \>\>\> t = CellRichText() \>\>\> t.append(\'xx\') \>\>\> t.append(TextBlock(red, \"red\")) You can also cast it to a [str]{.title-ref} to get only the text, without formatting. \>\>\> str(t) \'xxred\' ## Editing Rich Text As editing large blocks of text with formatting can be tricky, the [as_list()]{.title-ref} method returns a list of strings to make indexing easy. \>\>\> l = rich_string1.as_list() \>\>\> l \[\'When the color \', \'red\', \' is used, you can expect \', \'danger\'\] \>\>\> l.index(\"danger\") 3 \>\>\> rich_string1\[3\].text = \"fun\" \>\>\> str(rich_string1) \'When the color red is used, you can expect fun\' ## Rich Text assignment to cells Rich Text objects can be assigned directly to cells \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> ws\[\'A1\'\] = rich_string1 \>\>\> ws\[\'A2\'\] = \'Simple string\' # Conditional Formatting Excel supports three different types of conditional formatting: builtins, standard and custom. Builtins combine specific rules with predefined styles. Standard conditional formats combine specific rules with custom formatting. In additional it is possible to define custom formulae for applying custom formats using differential styles. :::: note ::: title Note ::: The syntax for the different rules varies so much that it is not possible for fastpyxl to know whether a rule makes sense or not. :::: The basic syntax for creating a formatting rule is: \>\>\> from fastpyxl.formatting import Rule \>\>\> from fastpyxl.styles import Font, PatternFill, Border \>\>\> from fastpyxl.styles.differential import DifferentialStyle \>\>\> dxf = DifferentialStyle(font=Font(bold=True), fill=PatternFill(start_color=\'EE1111\', end_color=\'EE1111\')) \>\>\> rule = Rule(type=\'cellIs\', dxf=dxf, formula=\[\"10\"\]) Because the signatures for some rules can be quite verbose there are also some convenience factories for creating them. ## Builtin formats The builtins conditional formats are: > - ColorScale > - IconSet > - DataBar Builtin formats contain a sequence of formatting settings which combine a type with an integer for comparison. Possible types are: [\'num\', \'percent\', \'max\', \'min\', \'formula\', \'percentile\']{.title-ref}. ### ColorScale You can have color scales with 2 or 3 colors. 2 color scales produce a gradient from one color to another; 3 color scales use an additional color for 2 gradients. The full syntax for creating a ColorScale rule is: \>\>\> from fastpyxl.formatting.rule import ColorScale, FormatObject \>\>\> from fastpyxl.styles import Color \>\>\> first = FormatObject(type=\'min\') \>\>\> last = FormatObject(type=\'max\') \>\>\> \# colors match the format objects: \>\>\> colors = \[Color(\'AA0000\'), Color(\'00AA00\')\] \>\>\> cs2 = ColorScale(cfvo=\[first, last\], color=colors) \>\>\> \# a three color scale would extend the sequences \>\>\> mid = FormatObject(type=\'num\', val=40) \>\>\> colors.insert(1, Color(\'00AA00\')) \>\>\> cs3 = ColorScale(cfvo=\[first, mid, last\], color=colors) \>\>\> \# create a rule with the color scale \>\>\> from fastpyxl.formatting.rule import Rule \>\>\> rule = Rule(type=\'colorScale\', colorScale=cs3) There is a convenience function for creating ColorScale rules \>\>\> from fastpyxl.formatting.rule import ColorScaleRule \>\>\> rule = ColorScaleRule(start_type=\'percentile\', start_value=10, start_color=\'FFAA0000\', \... mid_type=\'percentile\', mid_value=50, mid_color=\'FF0000AA\', \... end_type=\'percentile\', end_value=90, end_color=\'FF00AA00\') ### IconSet Choose from the following set of icons: [\'3Arrows\', \'3ArrowsGray\', \'3Flags\', \'3TrafficLights1\', \'3TrafficLights2\', \'3Signs\', \'3Symbols\', \'3Symbols2\', \'4Arrows\', \'4ArrowsGray\', \'4RedToBlack\', \'4Rating\', \'4TrafficLights\', \'5Arrows\', \'5ArrowsGray\', \'5Rating\', \'5Quarters\']{.title-ref} The full syntax for creating an IconSet rule is: \>\>\> from fastpyxl.formatting.rule import IconSet, FormatObject \>\>\> first = FormatObject(type=\'percent\', val=0) \>\>\> second = FormatObject(type=\'percent\', val=33) \>\>\> third = FormatObject(type=\'percent\', val=67) \>\>\> iconset = IconSet(iconSet=\'3TrafficLights1\', cfvo=\[first, second, third\], showValue=None, percent=None, reverse=None) \>\>\> \# assign the icon set to a rule \>\>\> from fastpyxl.formatting.rule import Rule \>\>\> rule = Rule(type=\'iconSet\', iconSet=iconset) There is a convenience function for creating IconSet rules: \>\>\> from fastpyxl.formatting.rule import IconSetRule \>\>\> rule = IconSetRule(\'5Arrows\', \'percent\', \[10, 20, 30, 40, 50\], showValue=None, percent=None, reverse=None) ### DataBar Currently, fastpyxl supports the DataBars as defined in the original specification. Borders and directions were added in a later extension. The full syntax for creating a DataBar rule is: \>\>\> from fastpyxl.formatting.rule import DataBar, FormatObject \>\>\> first = FormatObject(type=\'min\') \>\>\> second = FormatObject(type=\'max\') \>\>\> data_bar = DataBar(cfvo=\[first, second\], color=\"638EC6\", showValue=None, minLength=None, maxLength=None) \>\>\> \# assign the data bar to a rule \>\>\> from fastpyxl.formatting.rule import Rule \>\>\> rule = Rule(type=\'dataBar\', dataBar=data_bar) There is a convenience function for creating DataBar rules: \>\>\> from fastpyxl.formatting.rule import DataBarRule \>\>\> rule = DataBarRule(start_type=\'percentile\', start_value=10, end_type=\'percentile\', end_value=\'90\', \... color=\"FF638EC6\", showValue=\"None\", minLength=None, maxLength=None) ## Standard conditional formats The standard conditional formats are: > - Average > - Percent > - Unique or duplicate > - Value > - Rank \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.styles import Color, PatternFill, Font, Border \>\>\> from fastpyxl.styles.differential import DifferentialStyle \>\>\> from fastpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> \# Create fill \>\>\> redFill = PatternFill(start_color=\'EE1111\', \... end_color=\'EE1111\', \... fill_type=\'solid\') \>\>\> \>\>\> \# Add a two-color scale \>\>\> \# Takes colors in excel \'RRGGBB\' style. \>\>\> ws.conditional_formatting.add(\'A1:A10\', \... ColorScaleRule(start_type=\'min\', start_color=\'AA0000\', \... end_type=\'max\', end_color=\'00AA00\') \... ) \>\>\> \>\>\> \# Add a three-color scale \>\>\> ws.conditional_formatting.add(\'B1:B10\', \... ColorScaleRule(start_type=\'percentile\', start_value=10, start_color=\'AA0000\', \... mid_type=\'percentile\', mid_value=50, mid_color=\'0000AA\', \... end_type=\'percentile\', end_value=90, end_color=\'00AA00\') \... ) \>\>\> \>\>\> \# Add a conditional formatting based on a cell comparison \>\>\> \# addCellIs(range_string, operator, formula, stopIfTrue, wb, font, border, fill) \>\>\> \# Format if cell is less than \'formula\' \>\>\> ws.conditional_formatting.add(\'C2:C10\', \... CellIsRule(operator=\'lessThan\', formula=\[\'C\$1\'\], stopIfTrue=True, fill=redFill)) \>\>\> \>\>\> \# Format if cell is between \'formula\' \>\>\> ws.conditional_formatting.add(\'D2:D10\', \... CellIsRule(operator=\'between\', formula=\[\'1\',\'5\'\], stopIfTrue=True, fill=redFill)) \>\>\> \>\>\> \# Format using a formula \>\>\> ws.conditional_formatting.add(\'E1:E10\', \... FormulaRule(formula=\[\'ISBLANK(E1)\'\], stopIfTrue=True, fill=redFill)) \>\>\> \>\>\> \# Aside from the 2-color and 3-color scales, format rules take fonts, borders and fills for styling: \>\>\> myFont = Font() \>\>\> myBorder = Border() \>\>\> ws.conditional_formatting.add(\'E1:E10\', \... FormulaRule(formula=\[\'E1=0\'\], font=myFont, border=myBorder, fill=redFill)) \>\>\> \>\>\> \# Highlight cells that contain particular text by using a special formula \>\>\> red_text = Font(color=\"9C0006\") \>\>\> red_fill = PatternFill(bgColor=\"FFC7CE\") \>\>\> dxf = DifferentialStyle(font=red_text, fill=red_fill) \>\>\> rule = Rule(type=\"containsText\", operator=\"containsText\", text=\"highlight\", dxf=dxf) \>\>\> rule.formula = \[\'NOT(ISERROR(SEARCH(\"highlight\",A1)))\'\] \>\>\> ws.conditional_formatting.add(\'A1:F40\', rule) \>\>\> wb.save(\"test.xlsx\") ## Formatting Entire Rows Sometimes you want to apply a conditional format to more than one cell, say a row of cells which contain a particular value. \>\>\> ws.append(\[\'Software\', \'Developer\', \'Version\'\]) \>\>\> ws.append(\[\'Excel\', \'Microsoft\', \'2016\'\]) \>\>\> ws.append(\[\'fastpyxl\', \'Open source\', \'2.6\'\]) \>\>\> ws.append(\[\'OpenOffice\', \'Apache\', \'4.1.4\'\]) \>\>\> ws.append(\[\'Word\', \'Microsoft\', \'2010\'\]) We want to highlight the rows where the developer is Microsoft. We do this by creating an expression rule and using a formula to identify which rows contain software developed by Microsoft. \>\>\> red_fill = PatternFill(bgColor=\"FFC7CE\") \>\>\> dxf = DifferentialStyle(fill=red_fill) \>\>\> r = Rule(type=\"expression\", dxf=dxf, stopIfTrue=True) \>\>\> r.formula = \[\'\$A2=\"Microsoft\"\'\] \>\>\> ws.conditional_formatting.add(\"A1:C10\", r) :::: note ::: title Note ::: The formula uses an **absolute** reference to the column referred to, `B` in this case; but a **relative** row number, in this case `1` to the range over which the format is applied. It can be tricky to get this right but the rule can be adjusted even after it has been added to the worksheet\'s conditional format collection. :::: ## Worksheets # Inserting and deleting rows and columns, moving ranges of cells ## Inserting rows and columns You can insert rows or columns using the relevant worksheet methods: > - `fastpyxl.worksheet.worksheet.Worksheet.insert_rows`{.interpreted-text > role="func"} > - `fastpyxl.worksheet.worksheet.Worksheet.insert_cols`{.interpreted-text > role="func"} > - `fastpyxl.worksheet.worksheet.Worksheet.delete_rows`{.interpreted-text > role="func"} > - `fastpyxl.worksheet.worksheet.Worksheet.delete_cols`{.interpreted-text > role="func"} The default is one row or column. For example to insert a row at 7 (before the existing row 7): >>> ws.insert_rows(7) ## Deleting rows and columns To delete the columns `F:H`: >>> ws.delete_cols(6, 3) :::: note ::: title Note ::: Fastpyxl does not manage dependencies, such as formulae, tables, charts, etc., when rows or columns are inserted or deleted. This is considered to be out of scope for a library that focuses on managing the file format. As a result, client code **must** implement the functionality required in any particular use case. :::: ## Moving ranges of cells You can also move ranges of cells within a worksheet: >>> ws.move_range("D4:F10", rows=-1, cols=2) This will move the cells in the range `D4:F10` up one row, and right two columns. The cells will overwrite any existing cells. If cells contain formulae you can let fastpyxl translate these for you, but as this is not always what you want it is disabled by default. Also only the formulae in the cells themselves will be translated. References to the cells from other cells or defined names will not be updated; you can use the `formula`{.interpreted-text role="doc"} translator to do this: >>> ws.move_range("G4:H10", rows=1, cols=1, translate=True) This will move the relative references in formulae in the range by one row and one column. ## Merge / Unmerge cells When you merge cells all cells but the top-left one are **removed** from the worksheet. To carry the border-information of the merged cell, the boundary cells of the merged cell are created as MergeCells which always have the value None. See `styling-merged-cells`{.interpreted-text role="ref"} for information on formatting merged cells. \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.merge_cells(\'A2:D2\') \>\>\> ws.unmerge_cells(\'A2:D2\') \>\>\> \>\>\> \# or equivalently \>\>\> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4) \>\>\> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4) # Additional Worksheet Properties These are advanced properties for particular behaviours, the most used ones are the \"fitTopage\" page setup property and the tabColor that define the background color of the worksheet tab. ## Available properties for worksheets - \"enableFormatConditionsCalculation\" - \"filterMode\" - \"published\" - \"syncHorizontal\" - \"syncRef\" - \"syncVertical\" - \"transitionEvaluation\" - \"transitionEntry\" - \"tabColor\" ## Available fields for page setup properties \"autoPageBreaks\" \"fitToPage\" ## Available fields for outlines - \"applyStyles\" - \"summaryBelow\" - \"summaryRight\" - \"showOutlineSymbols\" Search [ECMA-376 pageSetup]{.title-ref} for more details. :::: note ::: title Note ::: By default, outline properties are intitialized so you can directly modify each of their 4 attributes, while page setup properties don\'t. If you want modify the latter, you should first initialize a `fastpyxl.worksheet.properties.PageSetupProperties`{.interpreted-text role="class"} object with the required parameters. Once done, they can be directly modified by the routine later if needed. :::: \>\>\> from fastpyxl.workbook import Workbook \>\>\> from fastpyxl.worksheet.properties import WorksheetProperties, PageSetupProperties \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> wsprops = ws.sheet_properties \>\>\> wsprops.tabColor = \"1072BA\" \>\>\> wsprops.filterMode = False \>\>\> wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True, autoPageBreaks=False) \>\>\> wsprops.outlinePr.summaryBelow = False \>\>\> wsprops.outlinePr.applyStyles = True \>\>\> wsprops.pageSetUpPr.autoPageBreaks = True ## Worksheet Views There are also several convenient properties defined as worksheet views. You can use `ws.sheet_view`{.interpreted-text role="class"} to set sheet attributes such as zoom, show formulas or if the tab is selected. [ws.sheet_view]{.title-ref} returns the first view. \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.sheet_view.zoomScale = 85 \# Sets 85% zoom \>\>\> ws.sheet_view.showFormulas = True \>\>\> ws.sheet_view.tabSelected = True Worksheets can have multiple views which are rendered in Excel as \"views.xlsx:1\", \"views.xlsx:2\". These correspond to [ws.views.sheetView\[0\]]{.title-ref} and [ws.views.sheetView\[1\]]{.title-ref}, because they are zero-indexed. Custom Sheetviews can also be defined. ## Fold (outline) \>\>\> import fastpyxl \>\>\> wb = fastpyxl.Workbook() \>\>\> ws = wb.create_sheet() \>\>\> ws.column_dimensions.group(\'A\',\'D\', hidden=True) \>\>\> ws.row_dimensions.group(1,10, hidden=True) \>\>\> wb.save(\'group.xlsx\') # Validating cells Data validators can be applied to ranges of cells but are not enforced or evaluated. Ranges do not have to be contiguous: eg. \"A1 B2:B5\" is contains A1 and the cells B2 to B5 but not A2 or B2. ## Examples \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.worksheet.datavalidation import DataValidation \>\>\> \>\>\> \# Create the workbook and worksheet we\'ll be working with \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> \# Create a data-validation object with list validation \>\>\> dv = DataValidation(type=\"list\", formula1=\'\"Dog,Cat,Bat\"\', allow_blank=True) \>\>\> \>\>\> \# Optionally set a custom error message \>\>\> dv.error =\'Your entry is not in the list\' \>\>\> dv.errorTitle = \'Invalid Entry\' \>\>\> \>\>\> \# Optionally set a custom prompt message \>\>\> dv.prompt = \'Please select from the list\' \>\>\> dv.promptTitle = \'List Selection\' \>\>\> \>\>\> \# Add the data-validation object to the worksheet \>\>\> ws.add_data_validation(dv) \>\>\> \# Create some cells, and add them to the data-validation object \>\>\> c1 = ws\[\"A1\"\] \>\>\> c1.value = \"Dog\" \>\>\> dv.add(c1) \>\>\> c2 = ws\[\"A2\"\] \>\>\> c2.value = \"An invalid value\" \>\>\> dv.add(c2) \>\>\> \>\>\> \# Or, apply the validation to a range of cells \>\>\> dv.add(\'B1:B1048576\') \# This is the same as for the whole of column B \>\>\> \>\>\> \# Check with a cell is in the validator \>\>\> \"B4\" in dv True > Validations without any cell ranges will be ignored when saving a > workbook. > Excel and LibreOffice interpret the parameter > [showDropDown=True]{.title-ref} as the dropdown arrow should be > hidden. ## Other validation examples Any whole number: : dv = DataValidation(type="whole") Any whole number above 100: : dv = DataValidation(type="whole", operator="greaterThan", formula1=100) Any decimal number: : dv = DataValidation(type="decimal") Any decimal number between 0 and 1: : dv = DataValidation(type="decimal", operator="between", formula1=0, formula2=1) Any date: : dv = DataValidation(type="date") or time: : dv = DataValidation(type="time") Any string at most 15 characters: : dv = DataValidation(type="textLength", operator="lessThanOrEqual"), formula1=15) Cell range validation: : from fastpyxl.utils import quote_sheetname dv = DataValidation(type="list", formula1="{0}!$B$1:$B$10".format(quote_sheetname(sheetname)) ) Custom rule: : dv = DataValidation(type="custom", formula1"=SOMEFORMULA") :::: note ::: title Note ::: See for custom rules :::: # Worksheet Tables Worksheet tables are references to groups of cells. This makes certain operations such as styling the cells in a table easier. ## Creating a table ::: literalinclude table.py ::: Table names must be unique within a workbook. By default tables are created with a header from the first row and filters for all the columns and table headers and column headings must always contain strings. :::: warning ::: title Warning ::: In write-only mode you must add column headings to tables manually and the values must always be the same as the values of the corresponding cells (ee below for an example of how to do this), otherwise Excel may consider the file invalid and remove the table. :::: Styles are managed using the the [TableStyleInfo]{.title-ref} object. This allows you to stripe rows or columns and apply the different colour schemes. ## Working with Tables `ws.tables` is a dictionary-like object of all the tables in a particular worksheet: >>> ws.tables {"Table1", } ### Get Table by name or range ``` >>> ws.tables["Table1"] or >>> ws.tables["A1:D10"] ``` ### Iterate through all tables in a worksheet ``` >>> for table in ws.tables.values(): >>> print(table) ``` ### Get table name and range of all tables in a worksheet Returns a list of table name and their ranges. ``` >>> ws.tables.items() >>> [("Table1", "A1:D10")] ``` ### Delete a table ``` >>> del ws.tables["Table1"] ``` ### The number of tables in a worksheet ``` >>> len(ws.tables) >>> 1 ``` ## Manually adding column headings In write-only mode you can either only add tables without headings: >>> table.headerRowCount = False Or initialise the column headings manually: >>> headings = ["Fruit", "2011", "2012", "2013", "2014"] # all values must be strings >>> table._initialise_columns() >>> for column, value in zip(table.tableColumns, headings): column.name = value ### Filters Filters will be added automatically to tables that contain header rows. It is **not** possible to create tables with header rows without filters. ## Table as a Print Area Excel can produce documents with the print area set to the table name. Fastpyxl cannot, however, resolve such dynamic defintions and will raise a warning when trying to do so. If you need to handle this you can extract the range of the table and define the print area as the appropriate cell range. ``` >>> from fastpyxl import load_workbook >>> wb = load_workbook("QueryTable.xlsx") >>> ws = wb.active >>> table_range = ws.tables["InvoiceData"] >>> ws.print_area = table_range.ref # Ref is the cell range the table currently covers ``` # Using filters and sorts It\'s possible to filter single range of values in a worksheet by adding an autofilter. If you need to filter multiple ranges, you can use tables and apply a separate filter for each table. :::: note ::: title Note ::: Filters and sorts can only be configured by fastpyxl but will need to be applied in applications like Excel. This is because they actually rearrange, format and hide rows in the range. :::: To add a filter you define a range and then add columns. You set the range over which the filter by setting the `ref` attribute. Filters are then applied to columns in the range using a zero-based index, eg. in a range from A1:H10, colId 1 refers to column B. Fastpyxl does not check the validity of such assignments. ::: literalinclude filters.py ::: This will add the relevant instructions to the file but will **neither actually filter nor sort**. ![\"Filter and sort prepared but not executed for a range of cells\"](filters.png) ## Advanced filters The following predefined filters can be used: `CustomFilter, DateGroupItem, DynamicFilter, ColorFilter, IconFilter and Top10` `ColorFilter, IconFilter and Top10` all interact with conditional formats. The signature and structure of the different kinds of filter varies significantly. As such it makes sense to familiarise yourself with either the fastpyxl source code or the OOXML specification. ### CustomFilter CustomFilters can have one or two conditions which will operate either independently (the default), or combined by setting the `and_` attribute. Filter can use the following operators: `equal, lessThan, lessThanOrEqual, notEqual, greaterThanOrEqual, greaterThan`. Filter values \< 10 and \> 90: from fastpyxl.worksheet.filters import CustomFilter, CustomFilters flt1 = CustomFilter(operator="lessThan", val=10) flt2 = CustomFilter(operator="greaterThan", val=90) cfs = CustomFilters(customFilter=[flt1, flt2]) col = FilterColumn(colId=2, customFilters=cfs) # apply to **third** column in the range filters.filter.append(col) To combine the filters: cfs.and_ = True In addition, Excel has non-standardised functionality for pattern matching with strings. The options in Excel: begins with, ends with, contains and their negatives are all implemented using the `equal` (or for negatives `notEqual`) operator and wildcard in the value. For this to work properly, the value is always a string. For example: for \"begins with a\", is actually `a*`; for \"ends with a\", `*a`; and for \"contains a\"\", `*a*`. `?` can be used to represent a single character. In regular expressions `*` is called *greedy* and `?` *non-greedy*. Wildcards are escaped with the `~` (tilde) so that `contains ~*`is serialised as `~~~*`. To simplify creating filters in client code, Fastpyxl provides three specialised filters: `NumberFilter`; `BlankFilter` and `StringFilter`. These filters are all used only when **creating** filters. For convenience, you can use the the `CustomFilter.convert()` method to convert from a CustomFilter to a more specific filter. ### NumberFilters NumericFilters differ from CustomFilters only in that they are explicitly numerical: from fastpyxl.worksheet.filters import NumberFilter, CustomerFilter flt1 = CustomFilter(operator="lessThan", val=10) flt1.val == "10" flt2 = NumberFilter(operator="lessThan", val=10) flt2.val == 10.0 ### BlankFilters BlankFilters are used for excluding blanks and are not editable: from fastpyxl.worksheet.filters import BlankFilter blank = BlankFilter() ### StringFilters StringFilters have the folowing operators: `contains` `startwith` `endswith` and `wildcard`. To apply the filter in the negative, set the exclude attribue to `True`. Fastpyxl handles escaping automatically: from fastpyxl.worksheet.filters import StringFilter fil = StringFilter("contains", "xml", exclude=True) StringFilters with the wildcard operator are the same as CustomFilters. This allows for allows for more sophisticated uses of the wildcards such as `c?n` which would match the terms `cancan` and `contains`, but not `curtains`; or `c*n` which would match all terms. Fastpyxl does **not** escape filters that use the wildcard operator. :::: note ::: title Note ::: The wildcard syntax allows for even more sophisticated patterns with multiple wildcards. This functionality cannot be easily expressed using StringFilters and is not a design goal. :::: ### DateGroupItem Date filters can be set to allow filtering by different datetime criteria such as year, month or hour. As they are similar to lists of values you can have multiple items. To filter by the month of March: from fastpyxl.worksheet.filters import DateGroupItem df1 = DateGroupItem(month=3, dateTimeGrouping="month") col = FilterColumn(colId=1) # second column col.filters.dateGroupItem.append(df1) df2 = DateGroupItem(year=1984, dateTimeGrouping="year") # add another element col.filters.dateGroupItem.append(df2) filters.filter.append(col) # Print Settings fastpyxl provides reasonably full support for print settings. ## Edit Print Options \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.print_options.horizontalCentered = True \>\>\> ws.print_options.verticalCentered = True ## Headers and Footers Headers and footers use their own formatting language. This is fully supported when writing them but, due to the complexity and the possibility of nesting, only partially when reading them. There is support for the font, size and color for a left, centre/center, or right element. Granular control (highlighting individuals words) will require applying control codes manually. \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.oddHeader.left.text = \"Page &\[Page\] of &N\" \>\>\> ws.oddHeader.left.size = 14 \>\>\> ws.oddHeader.left.font = \"Tahoma,Bold\" \>\>\> ws.oddHeader.left.color = \"CC3366\" Also supported are [evenHeader]{.title-ref} and [evenFooter]{.title-ref} as well as [firstHeader]{.title-ref} and [firstFooter]{.title-ref}. ## Add Print Titles You can print titles on every page to ensure that the data is properly labelled. \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.print_title_cols = \'A:B\' \# the first two cols \>\>\> ws.print_title_rows = \'1:1\' \# the first row ## Add a Print Area You can select a part of a worksheet as the only part that you want to print \>\>\> from fastpyxl.workbook import Workbook \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.print_area = \'A1:F10\' ## Change page layout and size You can adjust the size and print orientation per sheet of a workbook. \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \>\>\> ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE \>\>\> ws.page_setup.paperSize = ws.PAPERSIZE_A5 The table size is stored internally as an integer, a number of alias variables are also available for common sizes (refer to [PAPERSIZE]()\* in `fastpyxl.worksheet.worksheet`{.interpreted-text role="mod"} ). If you need a non-standard size, a full list can be found by searching [ECMA-376 pageSetup]{.title-ref} and setting that value as the paperSize # Pivot Tables fastpyxl provides read-support for pivot tables so that they will be preserved in existing files. The specification for pivot tables, while extensive, is not very clear and it is not intended that client code should be able to create pivot tables. However, it should be possible to edit and manipulate existing pivot tables, eg. change their ranges or whether they should update automatically settings. As is the case for charts, images and tables there is currently no management API for pivot tables so that client code will have to loop over the `_pivots` list of a worksheet. ## Example ``` from fastpyxl import load_workbook wb = load_workbook("campaign.xlsx") ws = wb["Results"] pivot = ws._pivots[0] # any will do as they share the same cache pivot.cache.refreshOnLoad = True ``` For further information see `fastpyxl.pivot.cache.CacheDefinition`{.interpreted-text role="class"} # Comments :::: warning ::: title Warning ::: Fastpyxl currently supports the reading and writing of comment text only. Formatting information is lost. Comment dimensions are lost upon reading, but can be written. Comments are not currently supported if [read_only=True]{.title-ref} is used. :::: ## Adding a comment to a cell Comments have a text attribute and an author attribute, which must both be set \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.comments import Comment \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> comment = ws\[\"A1\"\].comment \>\>\> comment = Comment(\'This is the comment text\', \'Comment Author\') \>\>\> comment.text \'This is the comment text\' \>\>\> comment.author \'Comment Author\' If you assign the same comment to multiple cells then fastpyxl will automatically create copies \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.comments import Comment \>\>\> wb=Workbook() \>\>\> ws=wb.active \>\>\> comment = Comment(\"Text\", \"Author\") \>\>\> ws\[\"A1\"\].comment = comment \>\>\> ws\[\"B2\"\].comment = comment \>\>\> ws\[\"A1\"\].comment is comment True \>\>\> ws\[\"B2\"\].comment is comment False ## Loading and saving comments Comments present in a workbook when loaded are stored in the comment attribute of their respective cells automatically. Formatting information such as font size, bold and italics are lost, as are the original dimensions and position of the comment\'s container box. Comments remaining in a workbook when it is saved are automatically saved to the workbook file. Comment dimensions can be specified for write-only. Comment dimension are in pixels. \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.comments import Comment \>\>\> from fastpyxl.utils import units \>\>\> \>\>\> wb=Workbook() \>\>\> ws=wb.active \>\>\> \>\>\> comment = Comment(\"Text\", \"Author\") \>\>\> comment.width = 300 \>\>\> comment.height = 50 \>\>\> \>\>\> ws\[\"A1\"\].comment = comment \>\>\> \>\>\> wb.save(\'commented_book.xlsx\') If needed, `fastpyxl.utils.units` contains helper functions for converting from other measurements such as mm or points to pixels: \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.comments import Comment \>\>\> from fastpyxl.utils import units \>\>\> \>\>\> wb=Workbook() \>\>\> ws=wb.active \>\>\> \>\>\> comment = Comment(\"Text\", \"Author\") \>\>\> comment.width = units.points_to_pixels(300) \>\>\> comment.height = units.points_to_pixels(50) \>\>\> \>\>\> ws\[\"A1\"\].comment = comment # Dates and Times Dates and times can be stored in two distinct ways in XLSX files: as an ISO 8601 formatted string or as a single number. [fastpyxl]{.title-ref} supports both representations and translates between them and Python\'s datetime module representations when reading from and writing to files. In either representation, the maximum date and time precision in XLSX files is millisecond precision. XLSX files are not suitable for storing historic dates (before 1900), due to bugs in Excel that cannot be fixed without causing backward compatibility problems. To discourage users from trying anyway, Excel deliberately refuses to recognize and display such dates. Consequently, it is not advised to use [fastpyxl]{.title-ref} for such purposes either, especially when exchanging files with others. ## Timezones The date and time representations in Excel do not support timezones, therefore [fastpyxl]{.title-ref} can only deal with naive datetime/time objects. Any timezone information attached to Python datetimes must be stripped off by the user before datetimes can be stored in XLSX files. ## Using the ISO 8601 format To make [fastpyxl]{.title-ref} store dates and times in the ISO 8601 format on writing your file, set the workbook\'s `iso_dates` flag to `True`: > \>\>\> import fastpyxl \>\>\> wb = fastpyxl.Workbook() \>\>\> > wb.iso_dates = True The benefit of using this format is that the meaning of the stored information is not subject to interpretation, as it is with the single number format[^1]. The Office Open XML standard does not specify a supported subset of the ISO 8601 duration format for representing time interval durations. [fastpyxl]{.title-ref} therefore always uses the single number format for timedelta values when writing them to file. ## The 1900 and 1904 date systems The \'date system\' of an XLSX file determines how dates and times in the single number representation are interpreted. XLSX files always use one of two possible date systems: > - In the 1900 date system (the default), the reference date (with > number 1) is 1900-01-01. > - In the 1904 date system, the reference date (with number 0) is > 1904-01-01. Complications arise not only from the different start numbers of the reference dates, but also from the fact that the 1900 date system has a built-in (but wrong) assumption that the year 1900 had been a leap year. Excel deliberately refuses to recognize and display dates before the reference date correctly, in order to discourage people from storing historical data. More information on this issue is available from Microsoft: : - - In workbooks using the 1900 date system, [fastpyxl]{.title-ref} behaves the same as Excel when translating between the worksheets\' date/time numbers and Python datetimes in January and February 1900. The only exception is 29 February 1900, which cannot be represented as a Python datetime object since it is not a valid date. You can get the date system of a workbook like this: > \>\>\> import fastpyxl \>\>\> wb = fastpyxl.Workbook() \>\>\> if > wb.epoch == fastpyxl.utils.datetime.CALENDAR_WINDOWS_1900: \... > print(\"This workbook is using the 1900 date system.\") \... This > workbook is using the 1900 date system. and set it like this: > \>\>\> wb.epoch = fastpyxl.utils.datetime.CALENDAR_MAC_1904 ## Handling timedelta values Excel users can use number formats resembling `[h]:mm:ss` or `[mm]:ss` to display time interval durations, which [fastpyxl]{.title-ref} considers to be equivalent to timedeltas in Python. [fastpyxl]{.title-ref} recognizes these number formats when reading XLSX files and returns datetime.timedelta values for the corresponding cells. When writing timedelta values from worksheet cells to file, [fastpyxl]{.title-ref} uses the `[h]:mm:ss` number format for these cells. **Footnotes** [^1]: For example, the serial 1 in an Excel worksheet can be interpreted as 00:00, as 24:00, as 1900-01-01, as 1440 (minutes), etc., depending solely on the formatting applied. # Simple Formualae ## Using formulae Formualae may be parsed and modified as well. \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> \# add a simple formula \>\>\> ws\[\"A1\"\] = \"=SUM(1, 1)\" \>\>\> wb.save(\"formula.xlsx\") :::: warning ::: title Warning ::: NB you must use the English name for a function and function arguments *must* be separated by commas and not other punctuation such as semi-colons. :::: fastpyxl **never** evaluates formula but it is possible to check the name of a formula: \>\>\> from fastpyxl.utils import FORMULAE \>\>\> \"HEX2DEC\" in FORMULAE True To read both the formula text and the last calculated cache Excel wrote into the file (without opening the workbook twice), use [keep_formula_cache=True]{.title-ref}: ```python from fastpyxl import load_workbook wb = load_workbook("formula.xlsx", keep_formula_cache=True) cell = wb.active["A1"] cell.value # "=SUM(1, 1)" cell.cached_value # 2, or None if the file has no cache ``` Default and [data_only=True]{.title-ref} loads leave [cell.cached_value]{.title-ref} as [None]{.title-ref}. See [Compatibility](03-compatibility.qmd) for the drop-in contract. If you\'re trying to use a formula that isn\'t known this could be because you\'re using a formula that was not included in the initial specification. Such formulae must be prefixed with [\_xlfn.]{.title-ref} to work. ### Special formulae Fastpyxl also supports two special kinds of formulae: [Array Formulae](https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7#ID0EAAEAAA=Office_2010_-_Office_2019) and [Data Table Formulae](https://support.microsoft.com/en-us/office/calculate-multiple-results-by-using-a-data-table-e95e2487-6ca6-4413-ad12-77542a5ea50b). Given the frequent use of \"data tables\" within OOXML the latter are particularly confusing. In general, support for these kinds of formulae is limited to preserving them in Excel files but the implementation is complete. #### Array Formulae Although array formulae are applied to a range of cells, they will only be visible for the top-left cell of the array. This can be confusing and a source of errors. To check for array formulae in a worksheet you can use the [ws.array_formulae]{.title-ref} property which returns a dictionary of cells with array formulae definitions and the ranges they apply to. Creating your own array formulae is fairly straightforward \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.worksheet.formula import ArrayFormula \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> ws\[\"E2\"\] = ArrayFormula(\"E2:E11\", \"=SUM(C2:C11\*D2:D11)\") > The top-left most cell of the array formula must be the cell you > assign it to, otherwise you will get errors on workbook load. > In Excel the formula will appear in all the cells in the range in > curly brackets [{}]{.title-ref} but you should **never** use these in > your own formulae. #### Data Table Formulae As with array formulae, data table formulae are applied to a range of cells. The table object themselves contain no formulae but only the definition of table: the cells covered and whether it is one dimensional or not, etc. For further information refer to the OOXML specification. To find out whether a worksheet has any data tables, use the [ws.table_formulae]{.title-ref} property. ## Workbooks # Defined Names The specification has the following to say about defined names: > \"Defined names are descriptive text that is used to represents a > cell, range of cells, formula, or constant value.\" This means they are very loosely defined. They might contain a constant, a formula, a single cell reference, a range of cells or multiple ranges of cells across different worksheets. Or all of the above. Cell references or ranges must use absolute coordinates and **always** include the name of the worksheet they\'re in. Use the utilities `absolute_coordinate()`{.interpreted-text role="obj"} and `quote_sheetname()`{.interpreted-text role="obj"} to do this. Defined names can either be restricted to individual worksheets or available globally for the whole workbook. Names must be unique within a collection; new items will replace existing ones with the name. ## Accessing Global Definitions Global definitions are stored in the workbook collection: defn = wb.defined_names["my_range"] # the destinations attribute contains a list of ranges in the definitions dests = defn.destinations # returns a generator of (worksheet title, cell range) tuples cells = [] for title, coord in dests: ws = wb[title] cells.append(ws[coord]) ## Accessing Worksheet Definitions Definitions are assigned to a specific worksheet are only accessible from that worksheet: ws = wb["Sheet"] defn = ws.defined_names["private_range"] ## Creating a Global Definition Global definitions are assigned to the workbook collection: from fastpyxl import Workbook from fastpyxl.workbook.defined_name import DefinedName from fastpyxl.utils import quote_sheetname, absolute_coordinate wb = Workbook() ws = wb.active # make sure sheetnames and cell references are quoted correctly ref = "{quote_sheetname(ws.title)}!{absolute_coordinate('A1:A5')}" defn = DefinedName("global_range", attr_text=ref) wb.defined_names["global_range"] = defn # key and `name` must be the same, the `.add()` method makes this easy wb.defined_names.add(new_range) ## Creating a Worksheet Definition Definitions are assigned to a specific worksheet are only accessible from that worksheet: # create a local named range (only valid for a specific sheet) ws = wb["Sheet"] ws.title = "My Sheet" # make sure sheetnames and cell referencesare quoted correctly ref = f"{quote_sheetname(ws.title)}!{absolute_coordinate('A6')}" defn = DefinedName("private_range", attr_text=ref) ws.defined_names.add(defn) print(ws.defined_names["private_range"].attr_text) ## Dynamic Named Ranges Wherever relevant and possible, fastpyxl will try and convert names that contain cell ranges into relevant object. For example, print areas and print titles, which are special cases of defined names, are mapped to print title and print area objects within a worksheet. It is, however, possible to define ranges dynamically using other defined names, or objects such as tables. As fastpyxl is unable to resolve such definitions, it will skip the definition and raise a warning. If you need to handle this you can extract the range of the defined name and set the print area as the appropriate cell range. ``` >>> from fastpyxl import load_workbook >>> wb = load_workbook("Example.xlsx") >>> ws = wb.active >>> area = ws.defined_names["TestArea"] # Globally defined named ranges can be used too >>> ws.print_area = area.value # value is the cell range the defined name currently covers ``` # Custom Document Properties It is possible to add one or more CustomDocumentProperty objects to a workbook. These require a unique name (string) and can be one of 6 types: > - StringProperty > - IntProperty > - FloatProperty > - DateTimeProperty > - BoolProperty > - LinkProperty LinkProperties are always associated with a defined name range. These properties are globally for a workbook and accessed from the [custom_doc_props]{.title-ref} attribute. ## Sample use Looping over all the custom properties (\"custom_doc_props\"): >>> for prop in wb.custom_doc_props.props: >>> print(f"{prop.name}: {prop.value}") Adding a new property: ``` from fastpyxl.packaging.custom import ( BoolProperty, DateTimeProperty, FloatProperty, IntProperty, LinkProperty, StringProperty, CustomPropertyList, ) props = CustomePropertyList() props.append(StringProperty(name="PropName1", value="Something")) ``` ## Deleting properties ``` wb.custom_doc_props.append(StringProperty(name="PropName6", value="Something")) # check the property prop = wb.custom_doc_props["PropName6"] # delete the string property: del prop["PropName6"] # save the file wb.save('outfile.xlsx') ``` :::: note ::: title Note ::: Currently not all possible property types are supported. If fastpyxl cannot read a particular type, it will provide a warning and ignore it. :::: # Protection :::: warning ::: title Warning ::: Password protecting a workbook or worksheet only provides a quite basic level of security. The data is not encrypted, so can be modified by any number of freely available tools. In fact the specification states: \"Worksheet or workbook element protection should not be confused with file security. It is meant to make your workbook safe from unintentional modification, and cannot protect it from malicious modification.\" :::: Fastpyxl provides support for protecting a workbook and worksheet from modification. The Open XML \"Legacy Password Hash Algorithm\" is used to generate hashed password values unless another algorithm is explicitly configured. ## Workbook Protection To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your workbook with a password. The password can be set using the `fastpyxl.workbook.protection.WorkbookProtection.workbookPassword`{.interpreted-text role="func"} property : >>> wb.security.workbookPassword = '...' >>> wb.security.lockStructure = True Similarly removing change tracking and change history from a shared workbook can be prevented by setting another password. This password can be set using the `fastpyxl.workbook.protection.WorkbookProtection.revisionsPassword`{.interpreted-text role="func"} property : >>> wb.security.revisionsPassword = '...' Other properties on the `fastpyxl.workbook.protection.WorkbookProtection`{.interpreted-text role="class"} object control exactly what restrictions are in place, but these will only be enforced if the appropriate password is set. Specific setter functions are provided if you need to set the raw password value without using the default hashing algorithm - e.g. : hashed_password = ... wb.security.set_workbook_password(hashed_password, already_hashed=True) ## Worksheet Protection Various aspects of a worksheet can also be locked by setting attributes on the `fastpyxl.worksheet.protection.SheetProtection`{.interpreted-text role="class"} object. Unlike workbook protection, sheet protection may be enabled with or without using a password. Sheet protection is enabled using the `openpxyl.worksheet.protection.SheetProtection.sheet`{.interpreted-text role="attr"} attribute or calling [enable()]{.title-ref} or \`disable()\`: >>> ws = wb.active >>> ws.protection.sheet = True >>> ws.protection.enable() >>> ws.protection.disable() If no password is specified, users can disable configured sheet protection without specifying a password. Otherwise they must supply a password to change configured protections. The password is set using the `openpxyl.worksheet.protection.SheetProtection.password`{.interpreted-text role="func"} property : >>> ws = wb.active >>> ws.protection.password = '...' ## Charts # Charts ## Chart types The following charts are available: ::: toctree area bar bubble line scatter pie doughnut radar stock surface ::: ## Creating a chart Charts are composed of at least one series of one or more data points. Series themselves are comprised of references to cell ranges. \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> for i in range(10): \... ws.append(\[i\]) \>\>\> \>\>\> from fastpyxl.chart import BarChart, Reference, Series \>\>\> values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10) \>\>\> chart = BarChart() \>\>\> chart.add_data(values) \>\>\> ws.add_chart(chart, \"E15\") \>\>\> wb.save(\"SampleChart.xlsx\") By default the top-left corner of a chart is anchored to cell E15 and the size is 15 x 7.5 cm (approximately 5 columns by 14 rows). This can be changed by setting the [anchor]{.title-ref}, [width]{.title-ref} and [height]{.title-ref} properties of the chart. The actual size will depend on operating system and device. Other anchors are possible; see `fastpyxl.drawing.spreadsheet_drawing`{.interpreted-text role="mod"} for further information. ## Working with axes ::: toctree limits_and_scaling secondary ::: ## Change the chart layout ::: toctree chart_layout ::: ## Styling charts ::: toctree pattern ::: ## Advanced charts Charts can be combined to create new charts: ::: toctree gauge ::: ## Using chartsheets Charts can be added to special worksheets called chartsheets: ::: toctree chartsheet ::: ## Positioning charts Position charts using anchors: ::: toctree anchors ::: ## Advanced chart formatting Use graphical properties for advanced chart formatting: ::: toctree graphical ::: # Positioning Charts with Anchors You can position charts using one of three different kinds of anchor: > - OneCell -- where the top-left of a chart is anchored to a single > cell. This is the default for fastpyxl and corresponds to the layout > option \"Move but don\'t size with cells\". > - TwoCell -- where the top-left of a chart is anchored to one cell, > and the bottom-right to another cell. This corresponds to the layout > option \"Move and size with cells\". > - Absolute -- where the chart is placed relative to the worksheet\'s > top-left corner and not any particular cell. You can change anchors quite easily on a chart like this. Let\'s assume we have created a bar chart using the sample code: ::: literalinclude bar.py ::: Let\'s take the first chart. Instead of anchoring it to A10, we want it to keep it with our table of data, say A9 to C20. We can do this by creating a TwoCellAnchor for those two cells.: from fastpyxl.drawing.spreadsheet_drawing import TwoCellAnchor anchor = TwoCellAnchor() anchor._from.col = 0 #A anchor._from.row = 8 # row 9, using 0-based indexing anchor.to.col = 2 #C anchor.to.row = 19 # row 20 chart.anchor = anchor You can also use this to change the anchors of existing charts. # Area Charts ## 2D Area Charts Area charts are similar to line charts with the addition that the area underneath the plotted line is filled. Different variants are available by setting the grouping to \"standard\", \"stacked\" or \"percentStacked\"; \"standard\" is the default. ::: literalinclude area.py ::: ![\"Sample area charts\"](area.png) ## 3D Area Charts You can also create 3D area charts ::: literalinclude area3d.py ::: This produces a simple 3D area chart where the third axis can be used to replace the legend: ![\"Sample 3D area chart with a series axis\"](area3D.png) # Bar and Column Charts In bar charts values are plotted as either horizontal bars or vertical columns. ## Vertical, Horizontal and Stacked Bar Charts :::: note ::: title Note ::: The following settings affect the different chart types. Switch between vertical and horizontal bar charts by setting [type]{.title-ref} to [col]{.title-ref} or [bar]{.title-ref} respectively. When using stacked charts the [overlap]{.title-ref} needs to be set to 100. If bars are horizontal, x and y axes are reversed. :::: ![\"Sample bar charts\"](bar.png) ::: literalinclude bar.py ::: This will produce four charts illustrating the various possibilities. ## 3D Bar Charts You can also create 3D bar charts ::: literalinclude bar3d.py ::: This produces a simple 3D bar chart ![\"Sample 3D bar chart\"](bar3D.png) # Bubble Charts Bubble charts are similar to scatter charts but use a third dimension to determine the size of the bubbles. Charts can include multiple series. ::: literalinclude bubble.py ::: This will produce a bubble chart with two series and should look something like this: ![\"Sample bubble chart\"](bubble.png) # Changing the layout of plot area and legend The layout of the chart within the canvas can be set by using the layout property of an instance of a layout class. ## Chart layout ### Size and position The chart can be positioned within its container. `x` and `y` adjust position, `w` and `h` adjust the size . The units are proportions of the container. A chart cannot be positioned outside of its container and the width and height are the dominant constraints: if x + w \> 1, then x = 1 - w. | x is the horizontal position from the left | y is the vertical position from the top | h is the height of the chart relative to its container | w is the width of the box ### Mode In addition to the size and position, the mode for the relevant attribute can also be set to either [factor]{.title-ref} or [edge]{.title-ref}. Factor is the default: ``` layout.xMode = edge ``` ### Target The layoutTarget can be set to `outer` or `inner`. The default is `outer`: ``` layout.layoutTarget = inner ``` ## Legend layout The position of the legend can be controlled either by setting its position: `r`, `l`, `t`, `b`, and `tr`, for right, left, top, bottom and top right respectively. The default is `r`. ``` legend.position = 'tr' ``` or applying a manual layout: ``` legend.layout = ManualLayout() ``` ::: literalinclude chart_layout.py ::: This produces four charts illustrating various possibilities: ![\"Different chart and legend layouts\"](chart_layout.png) # Chartsheets Chartsheets are special worksheets which only contain charts. All the data for the chart must be on a different worksheet. ::: literalinclude chartsheet.py ::: ![\"Sample chartsheet\"](chartsheet.png) By default in Microsoft Excel, charts are chartsheets are designed to fit the page format of the active printer. By default in fastpyxl, charts are designed to fit window in which they\'re displayed. You can flip between these using the [zoomToFit]{.title-ref} attribute of the active view, typically [cs.sheetViews.sheetView\[0\].zoomToFit]{.title-ref} # Doughnut Charts Doughnut charts are similar to pie charts except that they use a ring instead of a circle. They can also plot several series of data as concentric rings. ::: literalinclude doughnut.py ::: ![\"Sample doughnut charts\"](doughnut.png) # Gauge Charts Gauge charts combine a pie chart and a doughnut chart to create a \"gauge\". The first chart is a doughnut chart with four slices. The first three slices correspond to the colours of the gauge; the fourth slice, which is half of the doughnut, is made invisible. A pie chart containing three slices is added. The first and third slice are invisible so that the second slice can act as the needle on the gauge. The effects are done using the graphical properties of individual data points in a data series. ::: literalinclude gauge.py ::: ![\"Sample gauge chart\"](gauge.png) # Advanced Options with Graphical Properties Many advanced options require using the Graphical Properties of OOXML. This is a much more abstract API than the chart API itself and may require considerable studying of the OOXML specification to get right. It is often unavoidable to look at the XML source of some charts you\'ve made. However, as fastpyxl tries very hard to implement the OOXML specification correctly, you should be able to do most things quite easily. To things easier to read, fastpyxl includes some aliases for some of the more obscure element or attribute names, eg. [GraphicalProperties for \`spPr]{.title-ref} or [line]{.title-ref} for [line]{.title-ref}. ## Make the chart background transparent from fastpyxl.chart.shapes import GraphicalProperties chart.graphical_properties = GraphicalProperties() chart.graphical_properties.noFill = True ## Remove the border from a chart from fastpyxl.chart.shapes import GraphicalProperties chart.graphical_properties = GraphicalProperties() chart.graphical_properties.line.noFill = True chart.graphical_properties.line.prstDash = None ## Reusing XML Due to the high degree of abstraction, DrawingML is used in different office programs, it can be tedious and frustrating to set the relevant properties for the desired effect. Fortunately, because fastpyxl is very close to the specification, it is often possible to use XML from source. For example, adding a single, formatted data label to a series. xml = """ """ from fastpyxl.chart.text import RichText from fastpyxl.xml.functions import fromstring xml = fromstring(txt) text_props = RichText.from_tree(xml) # Assuming that this is for the third data series for a chart and we want to add a label below the fourth data point. highlight = chart.series[2] highlight.graphicalProperties.line.prstDash = "solid" highlight.graphicalProperties.ln.solidFill = "0070C0" highlight.graphicalProperties.line.width = 40000 # make the line thicker than normal highlight.dLbls = DataLabelList() highlight = DataLabel(idx=3, showSerName=True, dLblPos="b", txPr=text_props) highlight.dLbls.dLbl.append(label) ![\"Highlighting a single value on a single series\"](highlighted.png) # Line Charts ## Line Charts Line charts allow data to be plotted against a fixed axis. They are similar to scatter charts, the main difference is that with line charts each data series is plotted against the same values. Different kinds of axes can be used for the secondary axes. Similar to bar charts there are three kinds of line charts: standard, stacked and percentStacked. ::: literalinclude line.py ::: ![\"Sample line charts\"](line.png) ## 3D Line Charts In 3D line charts the third axis is the same as the legend for the series. ::: literalinclude line3D.py ::: ![\"Sample 3D line chart\"](line3D.png) # Axis Limits and Scale ## Minima and Maxima Axis minimum and maximum values can be set manually to display specific regions on a chart. ::: literalinclude limits_and_scaling_minmax.py ::: ![\"Sample charts with examples of axis clipping\"](limits_and_scaling_minmax.png) :::: note ::: title Note ::: In some cases such as the one shown, setting the axis limits is effectively equivalent to displaying a sub-range of the data. For large datasets, rendering of scatter plots (and possibly others) will be much faster when using subsets of the data rather than axis limits in both Excel and Open/Libre Office. :::: ## Logarithmic Scaling Both the x- and y-axes can be scaled logarithmically. The base of the logarithm can be set to any valid float. If the x-axis is scaled logarithmically, negative values in the domain will be discarded. ::: literalinclude limits_and_scaling_log.py ::: This produces five charts that look something like this: ![\"Sample charts with examples of axis log scaling\"](limits_and_scaling_log.png) The first four charts show the same data unscaled, scaled logarithmically in each axis and in both axes, with the logarithm base set to 10. The final chart shows the same data with both axes scaled, but the base of the logarithm set to `e`. ## Axis Orientation Axes can be displayed \"normally\" or in reverse. Axis orientation is controlled by the scaling `orientation` property, which can have a value of either `'minMax'` for normal orientation or `'maxMin'` for reversed. ::: literalinclude limits_and_scaling_orientation.py ::: This produces four charts with the axes in each possible combination of orientations that look something like this: ![\"Sample charts with different axis orientations\"](limits_and_scaling_orientation.png) # Adding Patterns Whole data series and individual data points can be extensively styled through the [graphicalProperties]{.title-ref}. Getting things just right may take some time. ::: literalinclude pattern.py ::: ![\"Sample bar chart with patterned columns\"](pattern.png) # Pie Charts ## Pie Charts Pie charts plot data as slices of a circle with each slice representing the percentage of the whole. Slices are plotted in a clockwise direction with 0° being at the top of the circle. Pie charts can only take a single series of data. The title of the chart will default to being the title of the series. ::: literalinclude pie.py ::: ![\"Sample pie chart\"](pie.png) ## Projected Pie Charts Projected pie charts extract some slices from a pie chart and project them into a second pie or bar chart. This is useful when there are several smaller items in the data series. The chart can be split according to percent, val(ue) or pos(ition). If nothing is set then the application decides which to use. In addition custom splits can be defined. ![\"Sample pie chart with projections\"](projected-pie.png) ## 3D Pie Charts Pie charts can also be created with a 3D effect. ::: literalinclude pie3D.py ::: ![\"Sample 3D pie chart\"](pie3D.png) ## Gradient Pie Charts Pie charts can also be created with gradient series. ::: literalinclude pie-gradient.py ::: ![\"Sampe gradient pie chart\"](pie-gradient.png) # Radar Charts Data that is arranged in columns or rows on a worksheet can be plotted in a radar chart. Radar charts compare the aggregate values of multiple data series. It is effectively a projection of an area chart on a circular x-axis. There are two types of radar chart: standard, where the area is marked with a line; and filled, where the whole area is filled. The additional type \"marker\" has no effect. If markers are desired these can be set for the relevant series. ::: literalinclude radar.py ::: ![\"Sample radar chart\"](radar.png) # Scatter Charts Scatter, or xy, charts are similar to some line charts. The main difference is that one series of values is plotted against another. This is useful where values are unordered. ::: literalinclude scatter.py ::: ![\"Sample scatter chart\"](scatter.png) :::: note ::: title Note ::: The specification says that there are the following types of scatter charts: \'line\', \'lineMarker\', \'marker\', \'smooth\', \'smoothMarker\'. However, at least in Microsoft Excel, this is just a shortcut for other settings that otherwise have no effect. For consistency with line charts, the style for each series should be set manually. :::: # Adding a second axis Adding a second axis actually involves creating a second chart that shares a common x-axis with the first chart but has a separate y-axis. ::: literalinclude secondary.py ::: This produces a combined line and bar chart looking something like this: ![\"Sample chart with a second y-axis\"](secondary.png) # Stock Charts Data that is arranged in columns or rows in a specific order on a worksheet can be plotted in a stock chart. As its name implies, a stock chart is most often used to illustrate the fluctuation of stock prices. However, this chart may also be used for scientific data. For example, you could use a stock chart to indicate the fluctuation of daily or annual temperatures. You must organize your data in the correct order to create stock charts. The way stock chart data is organized in the worksheet is very important. For example, to create a simple high-low-close stock chart, you should arrange your data with High, Low, and Close entered as column headings, in that order. Although stock charts are a distinct type, the various types are just shortcuts for particular formatting options: > - high-low-close is essentially a line chart with no lines and the > marker set to XYZ. It also sets hiLoLines to True > - open-high-low-close is the same as a high-low-close chart with the > marker for each data point set to XZZ and upDownLines. Volume can be added by combining the stock chart with a bar chart for the volume. ::: literalinclude stock.py ::: :::: warning ::: title Warning ::: Due to a bug in Excel high-low lines will only be shown if at least one of the data series has some dummy values. This can be done with the following hack: from fastpyxl.chart.data_source import NumData, NumVal pts = [NumVal(idx=i) for i in range(len(data) - 1)] cache = NumData(pt=pts) c1.series[-1].val.numRef.numCache = cache :::: ![\"Sample stock charts\"](stock.png) # Surface charts Data that is arranged in columns or rows on a worksheet can be plotted in a surface chart. A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values. By default all surface charts are 3D. 2D wireframe and contour charts are created by setting the rotation and perspective. ::: literalinclude surface.py ::: ![\"Sample surface charts\"](surface.png) ## Images # Working with Images ## Inserting an image \>\>\> from fastpyxl import Workbook \>\>\> from fastpyxl.drawing.image import Image \>\>\> \>\>\> wb = Workbook() \>\>\> ws = wb.active \>\>\> ws\[\'A1\'\] = \'You should see three logos below\' \>\>\> \>\>\> \# create an image \>\>\> img = Image(\'logo.png\') \>\>\> \>\>\> \# add to worksheet and anchor next to cells \>\>\> ws.add_image(img, \'A1\') \>\>\> wb.save(\'logo.xlsx\') ## Pandas # Working with Pandas and NumPy fastpyxl is able to work with the popular libraries [Pandas](http://pandas.pydata.org) and [NumPy](http://numpy.org) ## NumPy Support fastpyxl has builtin support for the NumPy types float, integer and boolean. DateTimes are supported using the Pandas\' Timestamp type. ## Working with Pandas Dataframes The `fastpyxl.utils.dataframe.dataframe_to_rows`{.interpreted-text role="func"} function provides a simple way to work with Pandas Dataframes: from fastpyxl.utils.dataframe import dataframe_to_rows wb = Workbook() ws = wb.active for r in dataframe_to_rows(df, index=True, header=True): ws.append(r) While Pandas itself supports conversion to Excel, this gives client code additional flexibility including the ability to stream dataframes straight to files. To convert a dataframe into a worksheet highlighting the header and index: wb = Workbook() ws = wb.active for r in dataframe_to_rows(df, index=True, header=True): ws.append(r) for cell in ws['A'] + ws[1]: cell.style = 'Pandas' wb.save("pandas_fastpyxl.xlsx") Alternatively, if you just want to convert the data you can use write-only mode: from fastpyxl.cell.cell import WriteOnlyCell wb = Workbook(write_only=True) ws = wb.create_sheet() cell = WriteOnlyCell(ws) cell.style = 'Pandas' def format_first_row(row, cell): for c in row: cell.value = c yield cell rows = dataframe_to_rows(df) first_row = format_first_row(next(rows), cell) ws.append(first_row) for row in rows: row = list(row) cell.value = row[0] row[0] = cell ws.append(row) wb.save("fastpyxl_stream.xlsx") This code will work just as well with a standard workbook. ## Converting a worksheet to a Dataframe To convert a worksheet to a Dataframe you can use the [values]{.title-ref} property. This is very easy if the worksheet has no headers or indices: df = DataFrame(ws.values) If the worksheet does have headers or indices, such as one created by Pandas, then a little more work is required: from itertools import islice data = ws.values cols = next(data)[1:] data = list(data) idx = [r[0] for r in data] data = (islice(r, 1, None) for r in data) df = DataFrame(data, index=idx, columns=cols) ## Performance # Optimised Modes ## Read-only mode Sometimes, you will need to open or write extremely large XLSX files, and the common routines in fastpyxl won\'t be able to handle that load. Fortunately, there are optimised modes that enable you to read and write unlimited amounts of data with (near) constant memory consumption. Introducing `fastpyxl.worksheet._read_only.ReadOnlyWorksheet`{.interpreted-text role="class"}: from fastpyxl import load_workbook wb = load_workbook(filename='large_file.xlsx', read_only=True) ws = wb['big_data'] for row in ws.rows: for cell in row: print(cell.value) # Close the workbook after reading wb.close() :::: warning ::: title Warning ::: - `fastpyxl.worksheet._read_only.ReadOnlyWorksheet`{.interpreted-text role="class"} is read-only - Unlike a normal workbook, a read-only workbook will use lazy loading. The workbook must be explicitly closed with the `close()`{.interpreted-text role="func"} method. :::: Cells returned are not regular `fastpyxl.cell.cell.Cell`{.interpreted-text role="class"} but `fastpyxl.cell.read_only.ReadOnlyCell`{.interpreted-text role="class"}. ### Worksheet dimensions Read-only mode relies on applications and libraries that created the file providing correct information about the worksheets, specifically the used part of it, known as the dimensions. Some applications set this incorrectly. You can check the apparent dimensions of a worksheet using [ws.calculate_dimension()]{.title-ref}. If this returns a range that you know is incorrect, say [A1:A1]{.title-ref} then simply resetting the max_row and max_column attributes should allow you to work with the file: ws.reset_dimensions() ## Indexed mode For sparse or random cell access, `read_only=True` can be expensive: every lookup re-opens the worksheet ZIP member and re-scans from the start of the sheet. fastpyxl adds an opt-in indexed strategy that builds row and shared-string byte-offset indexes over decompressed package parts, then seeks to the needed row/`` on demand: from fastpyxl import load_workbook wb = load_workbook("large_file.xlsx", access="indexed") ws = wb["big_data"] print(ws["Z100000"].value) wb.close() `access="indexed"` implies read-only worksheets (`fastpyxl.worksheet._indexed.IndexedWorksheet`{.interpreted-text role="class"}, a subclass of `ReadOnlyWorksheet`{.interpreted-text role="class"}). Styles remain eagerly loaded. Missing cells still return the `EMPTY_CELL`{.interpreted-text role="class"} singleton and never densify the sheet. As with `read_only`, merges, comments, tables, and drawings are not bound. Large sheet / shared-string parts (>16 MiB uncompressed) stream to a temp file instead of staying in a Python `bytes` object. Index construction scans that file via `mmap`, so spooling avoids a second full in-process copy—but peak RSS during inflate/index can still be on the order of the decompressed part while the ZIP member is being expanded and scanned. Prefixed SpreadsheetML tags such as `` are not indexed in v1 (unprefixed Excel-style tags only); those cells appear missing. Indexed mode is **not** aimed at beating a full sequential dump: index build plus a second pass can lose to normal or `read_only` iteration. Prefer it when the workload is dominated by sparse / random hits. There is no row-level parse cache yet, so many hits inside one row re-parse that row each time. ## Write-only mode Here again, the regular `fastpyxl.worksheet.worksheet.Worksheet`{.interpreted-text role="class"} has been replaced by a faster alternative, the `fastpyxl.worksheet._write_only.WriteOnlyWorksheet`{.interpreted-text role="class"}. When you want to dump large amounts of data make sure you have [lxml]{.title-ref} installed. \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook(write_only=True) \>\>\> ws = wb.create_sheet() \>\>\> \>\>\> \# now we\'ll fill it with 100 rows x 200 columns \>\>\> \>\>\> for irow in range(100): \... ws.append(\[\'%d\' % i for i in range(200)\]) \>\>\> \# save the file \>\>\> wb.save(\'new_big_file.xlsx\') \# doctest: +SKIP If you want to have cells with styles or comments then use a `fastpyxl.cell.WriteOnlyCell`{.interpreted-text role="func"} \>\>\> from fastpyxl import Workbook \>\>\> wb = Workbook(write_only = True) \>\>\> ws = wb.create_sheet() \>\>\> from fastpyxl.cell import WriteOnlyCell \>\>\> from fastpyxl.comments import Comment \>\>\> from fastpyxl.styles import Font \>\>\> cell = WriteOnlyCell(ws, value=\"hello world\") \>\>\> cell.font = Font(name=\'Courier\', size=36) \>\>\> cell.comment = Comment(text=\"A comment\", author=\"Author\'s Name\") \>\>\> ws.append(\[cell, 3.14, None\]) \>\>\> wb.save(\'write_only_file.xlsx\') This will create a write-only workbook with a single sheet, and append a row of 3 cells: one text cell with a custom font and a comment, a floating-point number, and an empty cell (which will be discarded anyway). :::: warning ::: title Warning ::: - Unlike a normal workbook, a newly-created write-only workbook does not contain any worksheets; a worksheet must be specifically created with the `create_sheet()`{.interpreted-text role="func"} method. - In a write-only workbook, rows can only be added with `append()`{.interpreted-text role="func"}. It is not possible to write (or read) cells at arbitrary locations with `cell()`{.interpreted-text role="func"} or `iter_rows()`{.interpreted-text role="func"}. - It is able to export unlimited amount of data (even more than Excel can handle actually), while keeping memory usage under 10Mb. - A write-only workbook can only be saved once. After that, every attempt to save the workbook or append() to an existing worksheet will raise an `fastpyxl.utils.exceptions.WorkbookAlreadySaved`{.interpreted-text role="class"} exception. - Everything that appears in the file before the actual cell data must be created before cells are added because it must written to the file before then. For example, [freeze_panes]{.title-ref} should be set before cells are added. :::: # Performance fastpyxl attempts to balance functionality and performance. Where in doubt, we have focused on functionality over optimisation: performance tweaks are easier once an API has been established. Memory use is fairly high in comparison with other libraries and applications and is approximately 50 times the original file size, e.g. 2.5 GB for a 50 MB Excel file. As many use cases involve either only reading or writing files, the `optimized`{.interpreted-text role="doc"} modes mean this is less of a problem. ## Benchmarks All benchmarks are synthetic and extremely dependent upon the hardware but they can nevertheless give an indication. ### Write Performance The benchmark code used for write-performance testing can be adjusted to use more sheets and adjust the proportion of data that is strings. Because the version of Python being used can also significantly affect performance, a driver script can also be used to test with different Python versions in separate environments. Performance is compared with the excellent alternative library xlsxwriter ::: literalinclude write_performance.txt ::: ### Read Performance Performance is measured using a file provided with a previous [bug report #494](https://github.com/fastpyxl/fastpyxl/issues/494) and compared with the older xlrd library. xlrd is primarily for the older BIFF file format of .XLS files but it does have limited support for XLSX. The benchmark code shows the importance of choosing the right options when working with a file. In this case disabling external links stops fastpyxl opening cached copies of the linked worksheets. One major difference between the libraries is that fastpyxl\'s read-only mode opens a workbook almost immediately making it suitable for multiple processes, this also reduces memory use significantly. xlrd does also not automatically convert dates and times into Python datetimes, though it does annotate cells accordingly but to do this in client code significantly reduces performance. For sparse random access, prefer `access=\"indexed\"` (fastpyxl-only): it pays an up-front inflate/index cost, then seeks to row and shared-string offsets instead of re-scanning the sheet. Parts over 16 MiB spool to disk and are indexed through `mmap`, but inflate/index peak memory is still roughly proportional to decompressed part size. See `optimized`{.interpreted-text role="doc"} and `scripts/bench_indexed_access.py` for the microbenchmark matrix versus normal and `read_only` loads. ::: literalinclude read_performance.txt ::: ### Parallelisation Reading worksheets is fairly CPU-intensive which limits any benefits to be gained by parallelisation. However, if you are mainly interested in dumping the contents of a workbook then you can use fastpyxl\'s read-only mode and open multiple instances of a workbook and take advantage of multiple CPUs. Sample code using the same source file as for read performance shows that performance scales reasonably with only a slight overhead due to creating additional Python processes. ``` Parallised Read Workbook loaded 1.12s >>DATA>> 2.27s Output Model 2.30s Store days 100% 37.18s OptimizationData 44.09s Store days 0% 45.60s Total time 46.76s ``` ## Developers # Development If you find the fastpyxl project intriguing and want to contribute a new awesome feature, fix a nasty bug or improve the documentation this section will guide you in setting up your development environment. We will look into the coding standards and version control system workflows used, as well as cloning the fastpyxl code to your local machine, setting up a virtual Python environment, running tests and building the documentation. ## Getting the source The source code is on GitHub: Clone the repository and install with `uv` (recommended): $ git clone https://github.com/Promptly-Technologies-LLC/fastpyxl.git $ cd fastpyxl $ uv sync --all-groups $ uv pip install -e . For installing a specific revision from another project, pin a tag or commit in the dependency URL (see also `tutorial`{.interpreted-text role="doc"}). Please base bug fixes on the branch for the current release when applicable; new features on the main development line. If in doubt, open an issue to discuss. ## Running tests Note that contributions to the project without tests will **not** be accepted. We use `pytest` as the test runner with `pytest-cov` for coverage. To run the full suite from the repo root: $ uv run pytest To stop at the first failure: $ uv run pytest -xrf fastpyxl To run the openpyxl drop-in compatibility contract (also enforced in CI): $ uv run pytest fastpyxl/tests/compat -m openpyxl_compat See `compatibility`{.interpreted-text role="doc"} for the supported openpyxl baseline and how intentional divergences are recorded. ### Coverage The goal is 100 % coverage for unit tests - data types and utility functions. Coverage information can be obtained using: $ uv run pytest --cov fastpyxl ### Organisation Tests should be preferably at package / module level e.g `fastpyxl/cell`. This makes testing and getting statistics for code under development easier: $ uv run pytest --cov fastpyxl/cell fastpyxl/cell ### Checking XML Use the `fastpyxl.tests.helper.compare_xml` function to compare generated and expected fragments of XML. ### Schema validation When working on code to generate XML it is possible to validate that the generated XML conforms to the published specification. Note, this won\'t necessarily guarantee that everything is fine but is preferable to reverse engineering! ### Microsoft Tools Along with the SDK, Microsoft also has a [\"Productivity Tool\"](http://www.microsoft.com/en-us/download/details.aspx?id=30425) for working with Office OpenXML. This allows you to quickly inspect or compare whole Excel files. Unfortunately, validation errors contain many false positives. The tool also contain links to the specification and implementers\' notes. ## File Support and Specifications The primary aim of fastpyxl is to support reading and writing Microsoft Excel 2010 files. These are zipped OOXML files that are specified by [ECMA 376](http://www.ecma-international.org/publications/standards/Ecma-376.htm) and [ISO 29500](http://standards.iso.org/ittf/PubliclyAvailableStandards/index.html). Where possible we try to support files generated by other libraries or programs, but can\'t guarantee it, because often these do not strictly adhere to the above format. ## Support of Python Versions Python 3.11 and upwards are supported. fastpyxl targets openpyxl >= 3.0.0 as a drop-in replacement; see `compatibility`{.interpreted-text role="doc"}. ## Coding style We orient ourselves at PEP-8 for the coding style, except when implementing attributes for round tripping. Despite that you are encouraged to use Python data conventions (boolean, None, etc.). Note exceptions from this convention in docstrings. ## Contributing Contributions in the form of pull requests are always welcome. Don\'t forget to add yourself to the list of authors! ## Branch naming convention We use a \"major.minor.patch\" numbering system, ie. [\|release\|](##SUBST##1.0.9). Development branches are named after \"major.minor\" releases. In general, API change will only happen major releases but there will be exceptions. Always communicate API changes to the mailing list before making them. If you are changing an API try and an implement a fallback (with deprecation warning) for the old behaviour. The \"default branch\" is used for releases and always has changes from a development branch merged in. It should never be the target for a pull request. ## Pull Requests Pull requests should be submitted to the current, unreleased development branch. Eg. if the current release is [\|release\|](##SUBST##1.0.9), pull requests should be made to the [\|version\|](##SUBST##1.0) branch. Exceptions are bug fixes to released versions which should be made to the relevant release branch and merged upstream into development. Please run the test suite locally before opening a pull request. CI exercises multiple Python versions; matching that locally is easiest with several `uv` installations or by relying on CI after you push. ### Documentation Remember to update the documentation when adding or changing features. Narrative guides live in `user_guide/` as Quarto Markdown (`.qmd`) files. The API reference is generated automatically from docstrings by [great-docs]{.title-ref}. Build the site locally: $ uv sync --group docs $ uv run great-docs build $ uv run great-docs preview The built HTML is written to `great-docs/_site/`. ## Benchmarking Benchmarking and profiling are ongoing tasks. Contributions to these are very welcome as we know there is a lot to do. ### Pympler As fastpyxl does not include any internal memory benchmarking tools, the python *pympler* package was used during the testing of styles to profile the memory usage in `fastpyxl.reader.excel.read_style_table()`: # in fastpyxl/reader/style.py from pympler import muppy, summary def read_style_table(xml_source): ... if cell_xfs is not None: # ~ line 47 initialState = summary.summarize(muppy.get_objects()) # Capture the initial state for index, cell_xfs_node in enumerate(cell_xfs_nodes): ... table[index] = new_style finalState = summary.summarize(muppy.get_objects()) # Capture the final state diff = summary.get_diff(initialState, finalState) # Compare summary.print_(diff) `pympler.summary.print_()` prints to the console a report of object memory usage, allowing the comparison of different methods and examination of memory usage. A useful future development would be to construct a benchmarking package to measure the performance of different components. # Parsing Formulas [fastpyxl]{.title-ref} supports limited parsing of formulas embedded in cells. The [fastpyxl.formula]{.title-ref} package contains a [Tokenizer]{.title-ref} class to break formulas into their constituent tokens. Usage is as follows: \>\>\> from fastpyxl.formula import Tokenizer \>\>\> tok = Tokenizer(\"\"\"=IF(\$A\$1,\"then True\",MAX(DEFAULT_VAL,\'Sheet 2\'!B1))\"\"\") \>\>\> print(\"n\".join(\"%12s%11s%9s\" % (t.value, t.type, t.subtype) for t in tok.items)) IF( FUNC OPEN \$A\$1 OPERAND RANGE , SEP ARG \"then True\" OPERAND TEXT , SEP ARG MAX( FUNC OPEN DEFAULT_VAL OPERAND RANGE , SEP ARG \'Sheet 2\'!B1 OPERAND RANGE ) FUNC CLOSE ) FUNC CLOSE As shown above, tokens have three attributes of interest: - `.value`: The substring of the formula that produced this token - `.type`: The type of token this represents. Can be one of - `Token.LITERAL`: If the cell does not contain a formula, its value is represented by a single `LITERAL` token. - `Token.OPERAND`: A generic term for any value in the Excel formula. (See `.subtype` below for more details). - `Token.FUNC`: Function calls are broken up into tokens for the opener (e.g., `SUM(`), followed by the arguments, followed by the closer (i.e., `)`). The function name and opening parenthesis together form one `FUNC` token, and the matching parenthesis forms another `FUNC` token. - `Token.ARRAY`: Array literals (enclosed between curly braces) get two `ARRAY` tokens each, one for the opening `{` and one for the closing `}`. - `Token.PAREN`: When used for grouping subexpressions (and not to denote function calls), parentheses are tokenized as `PAREN` tokens (one per character). - `Token.SEP`: These tokens are created from either commas (`,`) or semicolons (`;`). Commas create `SEP` tokens when they are used to separate function arguments (e.g., `SUM(a,b)`) or when they are used to separate array elements (e.g., `{a,b}`). (They have another use as an infix operator for joining ranges). Semicolons are always used to separate rows in an array literal, so always create `SEP` tokens. - `Token.OP_PRE`: Designates a prefix unary operator. Its value is always `+` or `-` - `Token.OP_IN`: Designates an infix binary operator. Possible values are `>=`, `<=`, `<>`, `=`, `>`, `<`, `*`, `/`, `+`, `-`, `^`, or `&`. - `Token.OP_POST`: Designates a postfix unary operator. Its value is always `%`. - `Token.WSPACE`: Created for any whitespace encountered. Its value is always a single space, regardless of how much whitespace is found. - `.subtype`: Some of the token types above use the subtype to provide additional information about the token. Possible subtypes are: - `Token.TEXT`, `Token.NUMBER`, `Token.LOGICAL`, `Token.ERROR`, `Token.RANGE`: these subtypes describe the various forms of `OPERAND` found in formulae. `LOGICAL` is either `TRUE` or `FALSE`, `RANGE` is either a named range or a direct reference to another range. `TEXT`, `NUMBER`, and `ERROR` all refer to literal values in the formula - `Token.OPEN` and `Token.CLOSE`: these two subtypes are used by `PAREN`, `FUNC`, and `ARRAY`, to describe whether the token is opening a new subexpression or closing it. - `Token.ARG` and `Token.ROW`: are used by the `SEP` tokens, to distinguish between the comma and semicolon. Commas produce tokens of subtype `ARG` whereas semicolons produce tokens of subtype `ROW` ## Translating formulae from one location to another It is possible to translate (in the mathematical sense) formulae from one location to another using the `fastpyxl.formulas.translate.Translator`{.interpreted-text role="class"} class. For example, there a range of cells `B2:E7` with a sum of each row in column `F`: >>> from fastpyxl.formula.translate import Translator >>> ws['F2'] = "=SUM(B2:E2)" >>> # move the formula one colum to the right >>> ws['G2'] = Translator("=SUM(B2:E2)", origin="F2").translate_formula("G2") >>> ws['G2'].value '=SUM(C2:F2)' :::: note ::: title Note ::: This is limited to the same general restrictions of formulae: [A1]{.title-ref} cell-references only and no support for defined names. :::: ## Release Notes # Changelog The following changelog is inherited from [openpyxl](https://pypi.org/project/openpyxl/), from which fastpyxl was forked. Issue and PR links refer to the original openpyxl repositories. For fastpyxl-specific changes, see the [GitHub releases](https://github.com/Promptly-Technologies-LLC/fastpyxl/releases). ## 3.1.5 (2024-06-28) - [#2187](https://github.com/openpyxl/openpyxl/issues/2187) Test fails due to change in Numpy API - [#2198](https://github.com/openpyxl/openpyxl/issues/2198) Excel is very fussy about the version number - [#2200](https://github.com/openpyxl/openpyxl/issues/2200) Poor perfomance when reading workbooks with lots of named styles ## 3.1.4 (2024-06-12) - [#2189](https://github.com/openpyxl/openpyxl/issues/2189) Assigning named styles doesn\'t work - [#2190](https://github.com/openpyxl/openpyxl/issues/2190) Problems caused when saving workbooks created by LibreOffice ## Changes - Python 3.6 and 3.7 are no longer supported. ## 3.1.3 (2024-05-29) ## Bugfixes - [#1401](https://github.com/openpyxl/openpyxl/issues/1401) Column name caches are slow and use a lot of memory - [#1457](https://github.com/openpyxl/openpyxl/issues/1457) Improved handling of duplicate named styles - [#1842](https://github.com/openpyxl/openpyxl/issues/1842) Rich-text can be saved if lxml is not installed - [#1954](https://github.com/openpyxl/openpyxl/issues/1954) Documentation for sheet views is incorrect - [#1973](https://github.com/openpyxl/openpyxl/issues/1973) Timedeltas not read properly in read-only mode - [#1987](https://github.com/openpyxl/openpyxl/issues/1987) List of formulae names contains mistakes - [#1967](https://github.com/openpyxl/openpyxl/issues/1967) Filters does not handle non-numerical filters - [#2054](https://github.com/openpyxl/openpyxl/issues/2054) Type checking increases exponentially - [#2057](https://github.com/openpyxl/openpyxl/issues/2057) Loading pivot tables can be unnecessarily slow - [#2102](https://github.com/openpyxl/openpyxl/issues/2102) Improve performance when reading files with lots of custom properties - [#2106](https://github.com/openpyxl/openpyxl/issues/2106) Setting Trendline.name attribute raises exception when saving - [#2120](https://github.com/openpyxl/openpyxl/issues/2120) Timezone and Zombie formatting cannot be combined. - [#2107](https://github.com/openpyxl/openpyxl/issues/2107) Column name generation is inefficient and slow - [#2122](https://github.com/openpyxl/openpyxl/issues/2122) File handlers not always released in read-only mode - [#2149](https://github.com/openpyxl/openpyxl/issues/2149) Workbook files not properly closed on Python ≥ 3.11.8 and Windows - [#2161](https://github.com/openpyxl/openpyxl/issues/2161) Pivot cache definitions using tupleCache had serialisation issues ## Changes - Add a [\_\_repr\_\_]{.title-ref} method for Row and Column dimension objects so you don\'t need to check every time. ## 3.1.2 (2023-03-11) - [#1963](https://github.com/openpyxl/openpyxl/issues/1963) Cannot read worksheets in read-only mode with locally scoped definitions - [#1974](https://github.com/openpyxl/openpyxl/issues/1974) Empty custom properties cause invalid files ## 3.1.1 (2023-02-13) ## Bugfixes - [#1881](https://github.com/openpyxl/openpyxl/issues/1881) DocumentProperties times set by module import only - [#1947](https://github.com/openpyxl/openpyxl/issues/1947) Worksheet-specific definitions are missing ## 3.1.0 (2023-01-31) ## New Features - Added support for data table formulae - Mapped chartspace graphical properties to charts for advanced formatting ## Bugfixes - [#1156](https://github.com/openpyxl/openpyxl/issues/1156) Table filters are always overriden - [#1360](https://github.com/openpyxl/openpyxl/issues/1360) Can\'t read some ScatterCharts if n - [#1724](https://github.com/openpyxl/openpyxl/issues/1724) Problem with multilevel indices in dataframes - [#1763](https://github.com/openpyxl/openpyxl/issues/1763) Make calculating worksheet sizes slightly faster - [#1772](https://github.com/openpyxl/openpyxl/issues/1772) Problem with category indices in dataframes - [#1786](https://github.com/openpyxl/openpyxl/issues/1786) NamedStyles share attributes - mutables gotcha - [#1851](https://github.com/openpyxl/openpyxl/issues/1851) Allow print area to be set to None - [#1852](https://github.com/openpyxl/openpyxl/issues/1852) Worksheet for print title and print areas can\'t be found - [#1853](https://github.com/openpyxl/openpyxl/issues/1853) Custom document properties that are strings can be empty - [#1858](https://github.com/openpyxl/openpyxl/issues/1858) ConditionalFormatting lost when pivot table updated - [#1864](https://github.com/openpyxl/openpyxl/issues/1864) Better handling of defined names - [#1904](https://github.com/openpyxl/openpyxl/issues/1904) dataframe_to_rows() misalignment on multiindex - [#1908](https://github.com/openpyxl/openpyxl/issues/1908) Ditto - [#1912](https://github.com/openpyxl/openpyxl/issues/1912) Excel doesn\'t like xmlns:space on nodes with only whitespace, which it treats as empty. - [#1942](https://github.com/openpyxl/openpyxl/issues/1942) Exception when print areas use table references. ## Pull Requests - [PR409](https://github.com/openpyxl/openpyxl/pull/409/) Support for Rich Text in cells - [PR411](https://github.com/openpyxl/openpyxl/pull/411/) Provide more information when workbook cannot be loaded - [PR407](https://github.com/openpyxl/openpyxl/pull/407/) Support for Custom Document Properties ## Deprecations The following properties have been removed from worksheets: formula_attributes, page_breaks, show_summary_below, show_summary_right, page_size orientation. Client code should use the relevant objects. ## Removals The following deprecated methods have been removed from workbooks: get_named_range, add_named_range, remove_named_range. And the get_emu_dimesions from images. ## 3.0.10 (2022-05-19) ## Bugfixes - [#1684](https://github.com/openpyxl/openpyxl/issues/1684) Image files not closed when workbooks are saved - [#1778](https://github.com/openpyxl/openpyxl/issues/1778) Problem with missing scope attribute in Pivot Table formats - [#1821](https://github.com/openpyxl/openpyxl/issues/1821) Excel unhappy when multiple sorts are defined - [#2014](https://github.com/openpyxl/openpyxl/issues/2014) Accounting format interpreted as datetime ## 3.0.9 (2021-09-22) ## Bugfixes - [#1284](https://github.com/openpyxl/openpyxl/issues/1284) Ignore blank ignored in existing Data Validations - [#1539](https://github.com/openpyxl/openpyxl/issues/1539) Add support for cell protection for merged cell ranges - [#1645](https://github.com/openpyxl/openpyxl/issues/1645) Timezone-aware datetimes raise an Exception - [#1666](https://github.com/openpyxl/openpyxl/issues/1666) Improved normalisation of chart series - [#1670](https://github.com/openpyxl/openpyxl/issues/1670) Catch OverflowError for out of range datetimes - [#1708](https://github.com/openpyxl/openpyxl/issues/1708) Alignment.relativeIndent can be negative - [#1736](https://github.com/openpyxl/openpyxl/issues/1769) Incorrect default value [groupBy]{.title-ref} attribute ## 3.0.8 (brown bag) Deleted because it contained breaking changes from 3.1 ## 3.0.7 (2021-03-09) ## Bugfixes - [#1510](https://github.com/openpyxl/openpyxl/issues/1510) Problems with zero time values - [#1588](https://github.com/openpyxl/openpyxl/issues/1588) Not possible to correctly convert excel dates to timedelta - [#1589](https://github.com/openpyxl/openpyxl/issues/1589) Exception raised when merging cells which do not have borders all the way round. - [#1594](https://github.com/openpyxl/openpyxl/issues/1594) Python 2 print statement in the tutorial ## Pull Requests - [PR392](https://github.com/openpyxl/openpyxl/pull/392/) Add documentation on datetime handling - [PR393](https://github.com/openpyxl/openpyxl/pull/393/) Drop dependency on jdcal - [PR394](https://github.com/openpyxl/openpyxl/pull/394/) Datetime rounding - [PR395](https://github.com/openpyxl/openpyxl/pull/395/) Unify handling of 1900 epoch - [PR397](https://github.com/openpyxl/openpyxl/pull/397/) Add explicit support for reading datetime deltas - [PR399](https://github.com/openpyxl/openpyxl/pull/399/) Millisecond precision for datetimes ## 3.0.6 (2021-01-14) ## Bugfixes - [#1154](https://github.com/openpyxl/openpyxl/issues/1154) Borders in differential styles are incorrect - [#1287](https://github.com/openpyxl/openpyxl/issues/1528) Error when opening some pivot tables - [#1366](https://github.com/openpyxl/openpyxl/issues/1366) Resave breaks the border format in conditional formatting rules - [#1450](https://github.com/openpyxl/openpyxl/issues/1450) Read-only workbook not closed properly if generator interrupted - [#1547](https://github.com/openpyxl/openpyxl/issues/1547) Pandas.Multiindex.labels deprecated - [#1552](https://github.com/openpyxl/openpyxl/issues/1557) Pandas.Multiinex not expanded correctly - [#1557](https://github.com/openpyxl/openpyxl/issues/1557) Cannot read rows with exponents - [#1568](https://github.com/openpyxl/openpyxl/issues/1568) numpy.float is deprecated - [#1571](https://github.com/openpyxl/openpyxl/issues/1571) Cells without coordinate attributes not always correctly handled ## Pull Requests - [PR385](https://github.com/openpyxl/openpyxl/pull/385/) Improved handling of borders for differential styles - [PR386](https://github.com/openpyxl/openpyxl/pull/386/) Support subclasses of datetime objects - [PR387](https://github.com/openpyxl/openpyxl/pull/387/) Improved handling of cells without coordinates ## 3.0.5 (2020-08-21) ## Bugfixes - [#1413](https://github.com/openpyxl/openpyxl/issues/1413) Incorrectly consider currency format as datetime - [#1490](https://github.com/openpyxl/openpyxl/issues/1490) Cannot copy worksheets with merged cells - [#1492](https://github.com/openpyxl/openpyxl/issues/1492) Empty worksheets do not return generators when looping. - [#1496](https://github.com/openpyxl/openpyxl/issues/1496) Hyperlinks duplicated on multiple saves - [#1500](https://github.com/openpyxl/openpyxl/issues/1500) Incorrectly literal format as datetime - [#1502](https://github.com/openpyxl/openpyxl/issues/1502) Links set to range of cells not preserved - [#1507](https://github.com/openpyxl/openpyxl/issues/1507) Exception when opening workbook with chartsheets and tables ## 3.0.4 (2020-06-24) ## Bugfixes - [#844](https://github.com/openpyxl/openpyxl/issues/844) Find tables by name - [#1414](https://github.com/openpyxl/openpyxl/issues/1414) Worksheet protection missing in existing files - [#1439](https://github.com/openpyxl/openpyxl/issues/1439) Exception when reading files with external images - [#1452](https://github.com/openpyxl/openpyxl/issues/1452) Reading lots of merged cells is very slow. - [#1455](https://github.com/openpyxl/openpyxl/issues/1455) Read support for Bubble Charts. - [#1458](https://github.com/openpyxl/openpyxl/issues/1458) Preserve any indexed colours - [#1473](https://github.com/openpyxl/openpyxl/issues/1473) Reading many thousand of merged cells is really slow. - [#1474](https://github.com/openpyxl/openpyxl/issues/1474) Adding tables in write-only mode raises an exception. ## Pull Requests - [PR377](https://github.com/openpyxl/openpyxl/pull/377/) Add support for finding tables by name or range. ## 3.0.3 (2020-01-20) ## Bugfixes - [#1260](https://github.com/openpyxl/openpyxl/issues/1260) Exception when handling merged cells with hyperlinks - [#1373](https://github.com/openpyxl/openpyxl/issues/1373) Problems when both lxml and defusedxml are installed - [#1385](https://github.com/openpyxl/openpyxl/issues/1385) CFVO with incorrect values cannot be processed ## 3.0.2 (2019-11-25) ## Bug fixes - [#1267](https://github.com/openpyxl/openpyxl/issues/1267) DeprecationError if both defusedxml and lxml are installed - [#1345](https://github.com/openpyxl/openpyxl/issues/1345) ws.\_current_row is higher than ws.max_row - [#1365](https://github.com/openpyxl/openpyxl/issues/1365) Border bottom style is not optional when it should be - [#1367](https://github.com/openpyxl/openpyxl/issues/1367) Empty cells in read-only, values-only mode are sometimes returned as ReadOnlyCells - [#1368](https://github.com/openpyxl/openpyxl/issues/1368) Cannot add page breaks to existing worksheets if none exist already ## Pull Requests - [PR359](https://github.com/openpyxl/openpyxl/pull/359/) Improvements to the documentation ## 3.0.1 (2019-11-14) ## Bugfixes - [#1250](https://github.com/openpyxl/openpyxl/issues/1250) Cannot read empty charts. ## Pull Requests - [PR354](https://github.com/openpyxl/openpyxl/pull/354/) Fix for #1250 - [PR352](https://github.com/openpyxl/openpyxl/pull/354/) TableStyleElement is a sequence ## 3.0.0 (2019-09-25) ## Python 3.6+ only release ## 2.6.4 (2019-09-25) ## Final release for Python 2.7 and 3.5 ## Bugfixes - [#1330](https://github.com/openpyxl/openpyxl/issues/1330) Cannot save workbooks with comments more than once. ## 2.6.3 (2019-08-19) ## Bugfixes - [#1237](https://github.com/openpyxl/openpyxl/issues/1237) Fix 3D charts. - [#1290](https://github.com/openpyxl/openpyxl/issues/1290) Minimum for holeSize in Doughnut charts too high - [#1291](https://github.com/openpyxl/openpyxl/issues/1291) Warning for MergedCells with comments - [#1296](https://github.com/openpyxl/openpyxl/issues/1296) Pagebreaks duplicated - [#1309](https://github.com/openpyxl/openpyxl/issues/1309) Workbook has no default CellStyle - [#1330](https://github.com/openpyxl/openpyxl/issues/1330) Workbooks with comments cannot be saved multiple times ## Pull Requests - [PR344](https://github.com/openpyxl/openpyxl/pull/345/) Make sure NamedStyles number formats are correctly handled ## 2.6.2 (2019-03-29) ## Bugfixes - [#1173](https://github.com/openpyxl/openpyxl/issues/1173) Workbook has no [date_formats]{#date_formats} attribute - [#1190](https://github.com/openpyxl/openpyxl/issues/1190) Cannot create charts for worksheets with quotes in the title - [#1228](https://github.com/openpyxl/openpyxl/issues/1228) MergedCells not removed when range is unmerged - [#1232](https://github.com/openpyxl/openpyxl/issues/1232) Link to pivot table lost from charts - [#1233](https://github.com/openpyxl/openpyxl/issues/1233) Chart colours change after saving - [#1236](https://github.com/openpyxl/openpyxl/issues/1236) Cannot use ws.cell in read-only mode with Python 2.7 ## 2.6.1 (2019-03-04) ## Bugfixes - [#1174](https://github.com/openpyxl/openpyxl/issues/1174) ReadOnlyCell.is_date does not work properly - [#1175](https://github.com/openpyxl/openpyxl/issues/1175) Cannot read Google Docs spreadsheet with a Pivot Table - [#1180](https://github.com/openpyxl/openpyxl/issues/1180) Charts created with fastpyxl cannot be styled - [#1181](https://github.com/openpyxl/openpyxl/issues/1181) Cannot handle some numpy number types - [#1182](https://github.com/openpyxl/openpyxl/issues/1182) Exception when reading unknowable number formats - [#1186](https://github.com/openpyxl/openpyxl/issues/1186) Only last formatting rule for a range loaded - [#1191](https://github.com/openpyxl/openpyxl/issues/1191) Give MergedCell a [value]{.title-ref} attribute - [#1193](https://github.com/openpyxl/openpyxl/issues/1193) Cannot process worksheets with comments - [#1197](https://github.com/openpyxl/openpyxl/issues/1197) Cannot process worksheets with both row and page breaks - [#1204](https://github.com/openpyxl/openpyxl/issues/1204) Cannot reset dimensions in ReadOnlyWorksheets - [#1211](https://github.com/openpyxl/openpyxl/issues/1211) Incorrect descriptor in ParagraphProperties - [#1213](https://github.com/openpyxl/openpyxl/issues/1213) Missing [hier]{.title-ref} attribute in PageField raises an exception ## 2.6.0 (2019-02-06) ## Bugfixes - [#1162](https://github.com/openpyxl/openpyxl/issues/1162) Exception on tables with names containing spaces. - [#1170](https://github.com/openpyxl/openpyxl/issues/1170) Cannot save files with existing images. ## 2.6.-b1 (2019-01-08) ## Bugfixes - [#1141](https://github.com/openpyxl/openpyxl/issues/1141) Cannot use read-only mode with stream - [#1143](https://github.com/openpyxl/openpyxl/issues/1143) Hyperlinks always set on A1 - [#1151](https://github.com/openpyxl/openpyxl/issues/1151) Internal row counter not initialised when reading files - [#1152](https://github.com/openpyxl/openpyxl/issues/1152) Exception raised on out of bounds date ## 2.6-a1 (2018-11-21) ## Major changes - Implement robust for merged cells so that these can be formatted the way Excel does without confusion. Thanks to Magnus Schieder. ## Minor changes - Add support for worksheet scenarios - Add read support for chartsheets - Add method for moving ranges of cells on a worksheet - Drop support for Python 3.4 - Last version to support Python 2.7 ## Deprecations - Type inference and coercion for cell values ## 2.5.14 (2019-01-23) ## Bugfixes - [#1150](https://github.com/openpyxl/openpyxl/issues/1150) Correct typo in LineProperties - [#1142](https://github.com/openpyxl/openpyxl/issues/1142) Exception raised for unsupported image files - [#1159](https://github.com/openpyxl/openpyxl/issues/1159) Exception raised when cannot find source for non-local cache object ## Pull Requests - [PR301](https://github.com/openpyxl/openpyxl/pull/301/) Add support for nested brackets to the tokeniser - [PR303](https://github.com/openpyxl/openpyxl/pull/301/) Improvements on handling nested brackets in the tokeniser ## 2.5.13 (brown bag) ## 2.5.12 (2018-11-29) ## Bugfixes - [#1130](https://github.com/openpyxl/openpyxl/issues/1130) Overwriting default font in Normal style affects library default - [#1133](https://github.com/openpyxl/openpyxl/issues/1133) Images not added to anchors. - [#1134](https://github.com/openpyxl/openpyxl/issues/1134) Cannot read pivot table formats without dxId - [#1138](https://github.com/openpyxl/openpyxl/issues/1138) Repeated registration of simple filter could lead to memory leaks ## Pull Requests - [PR300](https://github.com/openpyxl/openpyxl/pull/300/) Use defusedxml if available ## 2.5.11 (2018-11-21) ## Pull Requests - [PR295](https://github.com/openpyxl/openpyxl/pull/295) Improved handling of missing rows - [PR296](https://github.com/openpyxl/openpyxl/pull/296) Add support for defined names to tokeniser ## 2.5.10 (2018-11-13) ## Bugfixes - [#1114](https://github.com/openpyxl/openpyxl/issues/1114) Empty column dimensions should not be saved. ## Pull Requests - [PR285](https://github.com/openpyxl/openpyxl/pull/285) Tokenizer failure for quoted sheet name in second half of range - [PR289](https://github.com/openpyxl/openpyxl/pull/289) Improved error detection in ranges. ## 2.5.9 (2018-10-19) ## Bugfixes - [#1000](https://github.com/openpyxl/openpyxl/issues/1000) Clean AutoFilter name definitions - [#1106](https://github.com/openpyxl/openpyxl/issues/1106) Attribute missing from Shape object - [#1109](https://github.com/openpyxl/openpyxl/issues/1109) Failure to read all DrawingML means workbook can\'t be read ## Pull Requests - [PR281](https://github.com/openpyxl/openpyxl/pull/281) Allow newlines in formulae - [PR284](https://github.com/openpyxl/openpyxl/pull/284) Fix whitespace in front of infix operator in formulae ## 2.5.8 (2018-09-25) - [#877](https://github.com/openpyxl/openpyxl/issues/877) Cannot control how missing values are displayed in charts. - [#948](https://github.com/openpyxl/openpyxl/issues/948) Cell references can\'t be used for chart titles - [#1095](https://github.com/openpyxl/openpyxl/issues/1095) Params in iter_cols and iter_rows methods are slightly wrong. ## 2.5.7 (2018-09-13) - [#954](https://github.com/openpyxl/openpyxl/issues/954) Sheet title containing % need quoting in references - [#1047](https://github.com/openpyxl/openpyxl/issues/1047) Cannot set quote prefix - [#1093](https://github.com/openpyxl/openpyxl/issues/1093) Pandas timestamps raise KeyError ## 2.5.6 (2018-08-30) - [#832](https://github.com/openpyxl/openpyxl/issues/832) Read-only mode can leave find-handles open when reading dimensions - [#933](https://github.com/openpyxl/openpyxl/issues/933) Set a worksheet directly as active - [#1086](https://github.com/openpyxl/openpyxl/issues/1086) Internal row counter not adjusted when rows are deleted or inserted ## 2.5.5 (2018-08-04) ## Bugfixes - [#1049](https://github.com/openpyxl/openpyxl/issues/1049) Files with Mac epoch are read incorrectly - [#1058](https://github.com/openpyxl/openpyxl/issues/1058) Cannot copy merged cells - [#1066](https://github.com/openpyxl/openpyxl/issues/1066) Cannot access ws.active_cell ## Pull Requests - [PR267](https://github.com/openpyxl/openpyxl/pull/267/image-read) Introduce read-support for images ## 2.5.4 (2018-06-07) ## Bugfixes - [#1025](https://github.com/openpyxl/openpyxl/issues/1025) Cannot read files with 3D charts. - [#1030](https://github.com/openpyxl/openpyxl/issues/1030) Merged cells take a long time to parse ## Minor changes - Improve read support for pivot tables and don\'t always create a Filters child for filterColumn objects. - [Support folding rows]{.title-ref} \\`\_ ## 2.5.3 (2018-04-18) ## Bugfixes - [#983](https://github.com/openpyxl/openpyxl/issues/983) Warning level too aggressive. - [#1015](https://github.com/openpyxl/openpyxl/issues/1015) Alignment and protection values not saved for named styles. - [#1017](https://github.com/openpyxl/openpyxl/issues/1017) Deleting elements from a legend doesn\'t work. - [#1018](https://github.com/openpyxl/openpyxl/issues/1018) Index names repeated for every row in dataframe. - [#1020](https://github.com/openpyxl/openpyxl/issues/1020) Worksheet protection not being stored. - [#1023](https://github.com/openpyxl/openpyxl/issues/1023) Exception raised when reading a tooltip. ## 2.5.2 (2018-04-06) ## Bugfixes - [#949](https://github.com/openpyxl/openpyxl/issues/949) High memory use when reading text-heavy files. - [#970](https://github.com/openpyxl/openpyxl/issues/970) Copying merged cells copies references. - [#978](https://github.com/openpyxl/openpyxl/issues/978) Cannot set comment size. - [#985](https://github.com/openpyxl/openpyxl/issues/895) Exception when trying to save workbooks with no views. - [#995](https://github.com/openpyxl/openpyxl/issues/995) Cannot delete last row or column. - [#1002](https://github.com/openpyxl/openpyxl/issues/1002) Cannot read Drawings containing embedded images. ## Minor changes - Support for dataframes with multiple columns and multiple indices. ## 2.5.1 (2018-03-12) ## Bugfixes - [#934](https://github.com/openpyxl/openpyxl/issues/934) Headers and footers not included in write-only mode. - [#960](https://github.com/openpyxl/openpyxl/issues/960) Deprecation warning raised when using ad-hoc access in read-only mode. - [#964](https://github.com/openpyxl/openpyxl/issues/964) Not all cells removed when deleting multiple rows. - [#966](https://github.com/openpyxl/openpyxl/issues/966) Cannot read 3d bar chart correctly. - [#967](https://github.com/openpyxl/openpyxl/issues/967) Problems reading some charts. - [#968](https://github.com/openpyxl/openpyxl/issues/968) Worksheets with SHA protection become corrupted after saving. - [#974](https://github.com/openpyxl/openpyxl/issues/974) Problem when deleting ragged rows or columns. - [#976](https://github.com/openpyxl/openpyxl/issues/976) GroupTransforms and GroupShapeProperties have incorrect descriptors - Make sure that headers and footers in chartsheets are included in the file ## 2.5.0 (2018-01-24) ## Minor changes - Correct definition for Connection Shapes. Related to \# 958 ## 2.5.0-b2 (2018-01-19) ## Bugfixes - [#915](https://github.com/openpyxl/openpyxl/issues/915) TableStyleInfo has no required attributes - [#925](https://github.com/openpyxl/openpyxl/issues/925) Cannot read files with 3D drawings - [#926](https://github.com/openpyxl/openpyxl/issues/926) Incorrect version check in installer - Cell merging uses transposed parameters - [#928](https://github.com/openpyxl/openpyxl/issues/928) ExtLst missing keyword for PivotFields - [#932](https://github.com/openpyxl/openpyxl/issues/932) Inf causes problems for Excel - [#952](https://github.com/openpyxl/openpyxl/issues/952) Cannot load table styles with custom names ## Major Changes - You can now insert and delete rows and columns in worksheets ## Minor Changes - pip now handles which Python versions can be used. ## 2.5.0-b1 (2017-10-19) ## Bugfixes - [#812](https://github.com/openpyxl/openpyxl/issues/812) Explicitly support for multiple cell ranges in conditonal formatting - [#827](https://github.com/openpyxl/openpyxl/issues/827) Non-contiguous cell ranges in validators get merged - [#837](https://github.com/openpyxl/openpyxl/issues/837) Empty data validators create invalid Excel files - [#860](https://github.com/openpyxl/openpyxl/issues/860) Large validation ranges use lots of memory - [#876](https://github.com/openpyxl/openpyxl/issues/876) Unicode in chart axes not handled correctly in Python 2 - [#882](https://github.com/openpyxl/openpyxl/issues/882) ScatterCharts have defective axes - [#885](https://github.com/openpyxl/openpyxl/issues/885) Charts with empty numVal elements cannot be read - [#894](https://github.com/openpyxl/openpyxl/issues/894) Scaling options from existing files ignored - [#895](https://github.com/openpyxl/openpyxl/issues/895) Charts with PivotSource cannot be read - [#903](https://github.com/openpyxl/openpyxl/issues/903) Cannot read gradient fills - [#904](https://github.com/openpyxl/openpyxl/issues/904) Quotes in number formats could be treated as datetimes ## Major Changes [worksheet.cell()]{.title-ref} no longer accepts a [coordinate]{.title-ref} parameter. The syntax is now [ws.cell(row, column, value=None)]{.title-ref} ## Minor Changes Added CellRange and MultiCellRange types (thanks to Laurent LaPorte for the suggestion) as a utility type for things like data validations, conditional formatting and merged cells. ## Deprecations ws.merged_cell_ranges has been deprecated because MultiCellRange provides sufficient functionality ## 2.5.0-a3 (2017-08-14) ## Bugfixes - [#848](https://github.com/openpyxl/openpyxl/issues/848) Reading workbooks with Pie Charts raises an exception - [#857](https://github.com/openpyxl/openpyxl/issues/857) Pivot Tables without Worksheet Sources raise an exception ## 2.5.0-a2 (2017-06-25) ## Major Changes - Read support for charts ## Bugfixes - [#833](https://github.com/openpyxl/openpyxl/issues/833) Cannot access chartsheets by title - [#834](https://github.com/openpyxl/openpyxl/issues/834) Preserve workbook views - [#841](https://github.com/openpyxl/openpyxl/issues/841) Incorrect classification of a datetime ## 2.5.0-a1 (2017-05-30) ## Compatibility - Dropped support for Python 2.6 and 3.3. fastpyxl will not run with Python 2.6 ## Major Changes - Read/write support for pivot tables ## Deprecations - Dropped the anchor method from images and additional constructor arguments ## Bugfixes - [#779](https://github.com/openpyxl/openpyxl/issues/779) Fails to recognise Chinese date format\` - [#828](https://github.com/openpyxl/openpyxl/issues/828) Include hidden cells in charts\` ## Pull requests - [163](https://github.com/openpyxl/openpyxl/pull/163) Improved GradientFill ## Minor changes - Remove deprecated methods from Cell - Remove deprecated methods from Worksheet - Added read/write support for the datetime type for cells ## 2.4.11 (2018-01-24) - #957 Relationship type for tables is borked ## 2.4.10 (2018-01-19) ## Bugfixes - #912 Copying objects uses shallow copy - #921 API documentation not generated automatically - #927 Exception raised when adding coloured borders together - #931 Number formats not correctly deduplicated ## Pull requests - 203 Correction to worksheet protection description - 210 Some improvements to the API docs - 211 Improved deprecation decorator - 218 Fix problems with deepcopy ## 2.4.9 (2017-10-19) ## Bugfixes - [#809](https://github.com/openpyxl/openpyxl/issues/809) Incomplete documentation of [copy_worksheet]{.title-ref} method - [#811](https://github.com/openpyxl/openpyxl/issues/811) Scoped definedNames not removed when worksheet is deleted - [#824](https://github.com/openpyxl/openpyxl/issues/824) Raise an exception if a chart is used in multiple sheets - [#842](https://github.com/openpyxl/openpyxl/issues/842) Non-ASCII table column headings cause an exception in Python 2 - [#846](https://github.com/openpyxl/openpyxl/issues/846) Conditional formats not supported in write-only mode - [#849](https://github.com/openpyxl/openpyxl/issues/849) Conditional formats with no sqref cause an exception - [#859](https://github.com/openpyxl/openpyxl/issues/859) Headers that start with a number conflict with font size - [#902](https://github.com/openpyxl/openpyxl/issues/902) TableStyleElements don\'t always have a condtional format - [#908](https://github.com/openpyxl/openpyxl/issues/908) Read-only mode sometimes returns too many cells ## Pull requests - [#179](https://github.com/openpyxl/openpyxl/pull/179) Cells kept in a set - [#180](https://github.com/openpyxl/openpyxl/pull/180) Support for Workbook protection - [#182](https://github.com/openpyxl/openpyxl/pull/182) Read support for page breaks - [#183](https://github.com/openpyxl/openpyxl/pull/183) Improve documentation of [copy_worksheet]{.title-ref} method - [#198](https://github.com/openpyxl/openpyxl/pull/198) Fix for #908 ## 2.4.8 (2017-05-30) ## Bugfixes - AutoFilter.sortState being assignd to the ws.sortState - [#766](https://github.com/openpyxl/openpyxl/issues/666) Sheetnames with apostrophes need additional escaping - [#729](https://github.com/openpyxl/openpyxl/issues/729) Cannot open files created by Microsoft Dynamics - [#819](https://github.com/openpyxl/openpyxl/issues/819) Negative percents not case correctly - [#821](https://github.com/openpyxl/openpyxl/issues/821) Runtime imports can cause deadlock - [#855](https://github.com/openpyxl/openpyxl/issues/855) Print area containing only columns leads to corrupt file ## Minor changes - Preserve any table styles ## 2.4.7 (2017-04-24) ## Bugfixes - [#807](https://github.com/openpyxl/openpyxl/issues/807) Sample files being included by mistake in sdist ## 2.4.6 (2017-04-14) ## Bugfixes - [#776](https://github.com/openpyxl/openpyxl/issues/776) Cannot apply formatting to plot area - [#780](https://github.com/openpyxl/openpyxl/issues/780) Exception when element attributes are Python keywords - [#781](https://github.com/openpyxl/openpyxl/issues/781) Exception raised when saving files with styled columns - [#785](https://github.com/openpyxl/openpyxl/issues/785) Number formats for data labels are incorrect - [#788](https://github.com/openpyxl/openpyxl/issues/788) Worksheet titles not quoted in defined names - [#800](https://github.com/openpyxl/openpyxl/issues/800) Font underlines not read correctly ## 2.4.5 (2017-03-07) ## Bugfixes - [#750](https://github.com/openpyxl/openpyxl/issues/750) Adding images keeps file handles open - [#772](https://github.com/openpyxl/openpyxl/issues/772) Exception for column-only ranges - [#773](https://github.com/openpyxl/openpyxl/issues/773) Cannot copy worksheets with non-ascii titles on Python 2 ## Pull requests - [161](https://github.com/openpyxl/openpyxl/pull/161) Support for non-standard names for Workbook part. - [162](https://github.com/openpyxl/openpyxl/pull/162) Documentation correction ## 2.4.4 (2017-02-23) ## Bugfixes - [#673](https://github.com/openpyxl/openpyxl/issues/673) Add close method to workbooks - [#762](https://github.com/openpyxl/openpyxl/issues/762) fastpyxl can create files with invalid style indices - [#729](https://github.com/openpyxl/openpyxl/issues/729) Allow images in write-only mode - [#744](https://github.com/openpyxl/openpyxl/issues/744) Rounded corners for charts - [#747](https://github.com/openpyxl/openpyxl/issues/747) Use repr when handling non-convertible objects - [#764](https://github.com/openpyxl/openpyxl/issues/764) Hashing function is incorrect - [#765](https://github.com/openpyxl/openpyxl/issues/765) Named styles share underlying array ## Minor Changes - Add roundtrip support for worksheet tables. ## Pull requests - [160](https://github.com/openpyxl/openpyxl/pull/160) Don\'t init mimetypes more than once. ## 2.4.3 (unreleased) bad release ## 2.4.2 (2017-01-31) ## Bug fixes - [#727](https://github.com/openpyxl/openpyxl/issues/727) DeprecationWarning is incorrect - [#734](https://github.com/openpyxl/openpyxl/issues/734) Exception raised if userName is missing - [#739](https://github.com/openpyxl/openpyxl/issues/739) Always provide a date1904 attribute - [#740](https://github.com/openpyxl/openpyxl/issues/740) Hashes should be stored as Base64 - [#743](https://github.com/openpyxl/openpyxl/issues/743) Print titles broken on sheetnames with spaces - [#748](https://github.com/openpyxl/openpyxl/issues/748) Workbook breaks when active sheet is removed - [#754](https://github.com/openpyxl/openpyxl/issues/754) Incorrect descriptor for Filter values - [#756](https://github.com/openpyxl/openpyxl/issues/756) Potential XXE vulerability - [#758](https://github.com/openpyxl/openpyxl/issues/758) Cannot create files with page breaks and charts - [#759](https://github.com/openpyxl/openpyxl/issues/759) Problems with worksheets with commas in their titles ## Minor Changes - Add unicode support for sheet name incrementation. ## 2.4.1 (2016-11-23) ## Bug fixes - [#643](https://github.com/openpyxl/openpyxl/issues/643) Make checking for duplicate sheet titles case insensitive - [#647](https://github.com/openpyxl/openpyxl/issues/647) Trouble handling LibreOffice files with named styles - [#687](https://github.com/openpyxl/openpyxl/issues/682) Directly assigned new named styles always refer to \"Normal\" - [#690](https://github.com/openpyxl/openpyxl/issues/690) Cannot parse print titles with multiple sheet names - [#691](https://github.com/openpyxl/openpyxl/issues/691) Cannot work with macro files created by LibreOffice - Prevent duplicate differential styles - [#694](https://github.com/openpyxl/openpyxl/issues/694) Allow sheet titles longer than 31 characters - [#697](https://github.com/openpyxl/openpyxl/issues/697) Cannot unset hyperlinks - [#699](https://github.com/openpyxl/openpyxl/issues/699) Exception raised when format objects use cell references - [#703](https://github.com/openpyxl/openpyxl/issues/703) Copy height and width when copying comments - [#705](https://github.com/openpyxl/openpyxl/issues/705) Incorrect content type for VBA macros - [#707](https://github.com/openpyxl/openpyxl/issues/707) IndexError raised in read-only mode when accessing individual cells - [#711](https://github.com/openpyxl/openpyxl/issues/711) Files with external links become corrupted - [#715](https://github.com/openpyxl/openpyxl/issues/715) Cannot read files containing macro sheets - [#717](https://github.com/openpyxl/openpyxl/issues/717) Details from named styles not preserved when reading files - [#722](https://github.com/openpyxl/openpyxl/issues/722) Remove broken Print Title and Print Area definitions ## Minor changes - Add support for Python 3.6 - Correct documentation for headers and footers ## Deprecations Worksheet methods [get_named_range()]{.title-ref} and [get_sqaured_range()]{.title-ref} ## Bug fixes ## 2.4.0 (2016-09-15) ## Bug fixes - [#652](https://github.com/openpyxl/openpyxl/issues/652) Exception raised when epoch is 1904 - [#642](https://github.com/openpyxl/openpyxl/issues/642) Cannot handle unicode in headers and footers in Python 2 - [#646](https://github.com/openpyxl/openpyxl/issues/646) Cannot handle unicode sheetnames in Python 2 - [#658](https://github.com/openpyxl/openpyxl/issues/658) Chart styles, and axis units should not be 0 - [#663](https://github.com/openpyxl/openpyxl/issues/663) Strings in external workbooks not unicode ## Major changes - Add support for builtin styles and include one for Pandas ## Minor changes - Add a [keep_links]{.title-ref} option to [load_workbook]{.title-ref}. External links contain cached copies of the external workbooks. If these are big it can be advantageous to be able to disable them. - Provide an example for using cell ranges in DataValidation. - PR 138 - add copy support to comments. ## 2.4.0-b1 (2016-06-08) ## Minor changes - Add an the alias [hide_drop_down]{.title-ref} to DataValidation for [showDropDown]{.title-ref} because that is how Excel works. ## Bug fixes - [#625](https://github.com/openpyxl/openpyxl/issues/625) Exception raises when inspecting EmptyCells in read-only mode - [#547](https://github.com/openpyxl/openpyxl/issues/547) Functions for handling OOXML \"escaped\" ST_XStrings - [#629](https://github.com/openpyxl/openpyxl/issues/629) Row Dimensions not supported in write-only mode - [#530](https://github.com/openpyxl/openpyxl/issues/530) Problems when removing worksheets with charts - [#630](https://github.com/openpyxl/openpyxl/issues/630) Cannot use SheetProtection in write-only mode ## Features - Add write support for worksheet tables ## 2.4.0-a1 (2016-04-11) ## Minor changes - Remove deprecated methods from DataValidation - Remove deprecated methods from PrintPageSetup - Convert AutoFilter to Serialisable and extend support for filters - Add support for SortState - Removed [use_iterators]{.title-ref} keyword when loading workbooks. Use [read_only]{.title-ref} instead. - Removed [optimized_write]{.title-ref} keyword for new workbooks. Use [write_only]{.title-ref} instead. - Improve print title support - Add print area support - New implementation of defined names - New implementation of page headers and footers - Add support for Python\'s NaN - Added iter_cols method for worksheets - ws.rows and ws.columns now always return generators and start at the top of the worksheet - Add a [values]{.title-ref} property for worksheets - Default column width changed to 8 as per the specification ## Deprecations - Cell anchor method - Worksheet point_pos method - Worksheet add_print_title method - Worksheet HeaderFooter attribute, replaced by individual ones - Flatten function for cells - Workbook get_named_range, add_named_range, remove_named_range, get_sheet_names, get_sheet_by_name - Comment text attribute - Use of range strings deprecated for ws.iter_rows() - Use of coordinates deprecated for ws.cell() - Deprecate .copy() method for StyleProxy objects ## Bug fixes - [#152](https://github.com/openpyxl/openpyxl/issues/152) Hyperlinks lost when reading files - [#171](https://github.com/openpyxl/openpyxl/issues/171) Add function for copying worksheets - [#386](https://github.com/openpyxl/openpyxl/issues/386) Cells with inline strings considered empty - [#397](https://github.com/openpyxl/openpyxl/issues/397) Add support for ranges of rows and columns - [#446](https://github.com/openpyxl/openpyxl/issues/446) Workbook with definedNames corrupted by fastpyxl - [#481](https://github.com/openpyxl/openpyxl/issues/481) \"safe\" reserved ranges are not read from workbooks - [#501](https://github.com/openpyxl/openpyxl/issues/501) Discarding named ranges can lead to corrupt files - [#574](https://github.com/openpyxl/openpyxl/issues/574) Exception raised when using the class method to parse Relationships - [#579](https://github.com/openpyxl/openpyxl/issues/579) Crashes when reading defined names with no content - [#597](https://github.com/openpyxl/openpyxl/issues/597) Cannot read worksheets without coordinates - [#617](https://github.com/openpyxl/openpyxl/issues/617) Customised named styles not correctly preserved ## 2.3.5 (2016-04-11) ## Bug fixes - [#618](https://github.com/openpyxl/openpyxl/issues/618) Comments not written in write-only mode ## 2.3.4 (2016-03-16) ## Bug fixes - [#594](https://github.com/openpyxl/openpyxl/issues/594) Content types might be missing when keeping VBA - [#599](https://github.com/openpyxl/openpyxl/issues/599) Cells with only one cell look empty - [#607](https://github.com/openpyxl/openpyxl/issues/607) Serialise NaN as \'\' ## Minor changes - Preserve the order of external references because formualae use numerical indices. - Typo corrected in cell unit tests (PR 118) ## 2.3.3 (2016-01-18) ## Bug fixes - [#540](https://github.com/openpyxl/openpyxl/issues/540) Cannot read merged cells in read-only mode - [#565](https://github.com/openpyxl/openpyxl/issues/565) Empty styled text blocks cannot be parsed - [#569](https://github.com/openpyxl/openpyxl/issues/569) Issue warning rather than raise Exception raised for unparsable definedNames - [#575](https://github.com/openpyxl/openpyxl/issues/575) Cannot open workbooks with embdedded OLE files - [#584](https://github.com/openpyxl/openpyxl/issues/584) Exception when saving borders with attributes ## Minor changes - [PR 103](https://github.com/openpyxl/openpyxl/pull/103/) Documentation about chart scaling and axis limits - Raise an exception when trying to copy cells from other workbooks. ## 2.3.2 (2015-12-07) ## Bug fixes - [#554](https://github.com/openpyxl/openpyxl/issues/554) Cannot add comments to a worksheet when preserving VBA - [#561](https://github.com/openpyxl/openpyxl/issues/561) Exception when reading phonetic text - [#562](https://github.com/openpyxl/openpyxl/issues/562) DARKBLUE is the same as RED - [#563](https://github.com/openpyxl/openpyxl/issues/563) Minimum for row and column indexes not enforced ## Minor changes - [PR 97](https://github.com/openpyxl/openpyxl/pull/97/) One VML file per worksheet. - [PR 96](https://github.com/openpyxl/openpyxl/pull/96/) Correct descriptor for CharacterProperties.rtl - [#498](https://github.com/openpyxl/openpyxl/issues/498) Metadata is not essential to use the package. ## 2.3.1 (2015-11-20) ## Bug fixes - [#534](https://github.com/openpyxl/openpyxl/issues/534) Exception when using columns property in read-only mode. - [#536](https://github.com/openpyxl/openpyxl/issues/536) Incorrectly handle comments from Google Docs files. - [#539](https://github.com/openpyxl/openpyxl/issues/539) Flexible value types for conditional formatting. - [#542](https://github.com/openpyxl/openpyxl/issues/542) Missing content types for images. - [#543](https://github.com/openpyxl/openpyxl/issues/543) Make sure images fit containers on all OSes. - [#544](https://github.com/openpyxl/openpyxl/issues/544) Gracefully handle missing cell styles. - [#546](https://github.com/openpyxl/openpyxl/issues/546) ExternalLink duplicated when editing a file with macros. - [#548](https://github.com/openpyxl/openpyxl/issues/548) Exception with non-ASCII worksheet titles - [#551](https://github.com/openpyxl/openpyxl/issues/551) Combine multiple LineCharts ## Minor changes - [PR 88](https://github.com/openpyxl/openpyxl/pull/88/) Fix page margins in parser. ## 2.3.0 (2015-10-20) ## Major changes - Support the creation of chartsheets ## Bug fixes - [#532](https://github.com/openpyxl/openpyxl/issues/532) Problems when cells have no style in read-only mode. ## Minor changes - PR 79 Make PlotArea editable in charts - Use graphicalProperties as the alias for spPr ## 2.3.0-b2 (2015-09-04) ## Bug fixes - [#488](https://bitbucket.org/openpyxl/openpyxl/issue/488) Support hashValue attribute for sheetProtection - [#493](https://bitbucket.org/openpyxl/openpyxl/issue/493) Warn that unsupported extensions will be dropped - [#494](https://github.com/openpyxl/openpyxl/issues/494/) Cells with exponentials causes a ValueError - [#497](https://github.com/openpyxl/openpyxl/issues/497/) Scatter charts are broken - [#499](https://github.com/openpyxl/openpyxl/issues/499/) Inconsistent conversion of localised datetimes - [#500](https://github.com/openpyxl/openpyxl/issues/500/) Adding images leads to unreadable files - [#509](https://github.com/openpyxl/openpyxl/issues/509/) Improve handling of sheet names - [#515](https://github.com/openpyxl/openpyxl/issues/515/) Non-ascii titles have bad repr - [#516](https://github.com/openpyxl/openpyxl/issues/516/) Ignore unassigned worksheets ## Minor changes - Worksheets are now iterable by row. - Assign individual cell styles only if they are explicitly set. ## 2.3.0-b1 (2015-06-29) ## Major changes - Shift to using (row, column) indexing for cells. Cells will at some point *lose* coordinates. - New implementation of conditional formatting. Databars now partially preserved. - et_xmlfile is now a standalone library. - Complete rewrite of chart package - Include a tokenizer for fomulae to be able to adjust cell references in them. PR 63 ## Minor changes - Read-only and write-only worksheets renamed. - Write-only workbooks support charts and images. - [PR76](https://bitbucket.org/openpyxl/openpyxl/pull-request/76) Prevent comment images from conflicting with VBA ## Bug fixes - [#81](https://bitbucket.org/openpyxl/openpyxl/issue/81) Support stacked bar charts - [#88](https://bitbucket.org/openpyxl/openpyxl/issue/88) Charts break hyperlinks - [#97](https://bitbucket.org/openpyxl/openpyxl/issue/97) Pie and combination charts - [#99](https://bitbucket.org/openpyxl/openpyxl/issue/99) Quote worksheet names in chart references - [#150](https://bitbucket.org/openpyxl/openpyxl/issue/150) Support additional chart options - [#172](https://bitbucket.org/openpyxl/openpyxl/issue/172) Support surface charts - [#381](https://bitbucket.org/openpyxl/openpyxl/issue/381) Preserve named styles - [#470](https://bitbucket.org/openpyxl/openpyxl/issue/470) Adding more than 10 worksheets with the same name leads to duplicates sheet names and an invalid file ## 2.2.6 (unreleased) ## Bug fixes - [#502](https://bitbucket.org/openpyxl/openpyxl/issue/502) Unexpected keyword \"mergeCell\" - [#503](https://bitbucket.org/openpyxl/openpyxl/issue/503) tostring missing in dump_worksheet - [#506](https://github.com/openpyxl/openpyxl/issues/506) Non-ASCII formulae cannot be parsed - [#508](https://github.com/openpyxl/openpyxl/issues/508) Cannot save files with coloured tabs - Regex for ignoring named ranges is wrong (character class instead of prefix) ## 2.2.5 (2015-06-29) ## Bug fixes - [#463](https://bitbucket.org/openpyxl/openpyxl/issue/463) Unexpected keyword \"mergeCell\" - [#484](https://bitbucket.org/openpyxl/openpyxl/issue/484) Unusual dimensions breaks read-only mode - [#485](https://bitbucket.org/openpyxl/openpyxl/issue/485) Move return out of loop ## 2.2.4 (2015-06-17) ## Bug fixes - [#464](https://bitbucket.org/openpyxl/openpyxl/issue/464) Cannot use images when preserving macros - [#465](https://bitbucket.org/openpyxl/openpyxl/issue/465) ws.cell() returns an empty cell on read-only workbooks - [#467](https://bitbucket.org/openpyxl/openpyxl/issue/467) Cannot edit a file with ActiveX components - [#471](https://bitbucket.org/openpyxl/openpyxl/issue/471) Sheet properties elements must be in order - [#475](https://bitbucket.org/openpyxl/openpyxl/issue/475) Do not redefine class \_\_slots\_\_ in subclasses - [#477](https://bitbucket.org/openpyxl/openpyxl/issue/477) Write-only support for SheetProtection - [#478](https://bitbucket.org/openpyxl/openpyxl/issue/477) Write-only support for DataValidation - Improved regex when checking for datetime formats ## 2.2.3 (2015-05-26) ## Bug fixes - [#451](https://bitbucket.org/openpyxl/openpyxl/issue/451) fitToPage setting ignored - [#458](https://bitbucket.org/openpyxl/openpyxl/issue/458) Trailing spaces lost when saving files. - [#459](https://bitbucket.org/openpyxl/openpyxl/issue/459) setup.py install fails with Python 3 - [#462](https://bitbucket.org/openpyxl/openpyxl/issue/462) Vestigial rId conflicts when adding charts, images or comments - [#455](https://bitbucket.org/openpyxl/openpyxl/issue/455) Enable Zip64 extensions for all versions of Python ## 2.2.2 (2015-04-28) ## Bug fixes - [#447](https://bitbucket.org/openpyxl/openpyxl/issue/447) Uppercase datetime number formats not recognised. - [#453](https://bitbucket.org/openpyxl/openpyxl/issue/453) Borders broken in shared_styles. ## 2.2.1 (2015-03-31) ## Minor changes - [PR54](https://bitbucket.org/openpyxl/openpyxl/pull-request/54) Improved precision on times near midnight. - [PR55](https://bitbucket.org/openpyxl/openpyxl/pull-request/55) Preserve macro buttons ## Bug fixes - [#429](https://bitbucket.org/openpyxl/openpyxl/issue/429) Workbook fails to load because header and footers cannot be parsed. - [#433](https://bitbucket.org/openpyxl/openpyxl/issue/433) File-like object with encoding=None - [#434](https://bitbucket.org/openpyxl/openpyxl/issue/434) SyntaxError when writing page breaks. - [#436](https://bitbucket.org/openpyxl/openpyxl/issue/436) Read-only mode duplicates empty rows. - [#437](https://bitbucket.org/openpyxl/openpyxl/issue/437) Cell.offset raises an exception - [#438](https://bitbucket.org/openpyxl/openpyxl/issue/438) Cells with pivotButton and quotePrefix styles cannot be read - [#440](https://bitbucket.org/openpyxl/openpyxl/issue/440) Error when customised versions of builtin formats - [#442](https://bitbucket.org/openpyxl/openpyxl/issue/442) Exception raised when a fill element contains no children - [#444](https://bitbucket.org/openpyxl/openpyxl/issue/442) Styles cannot be copied ## 2.2.0 (2015-03-11) ## Bug fixes - [#415](https://bitbucket.org/openpyxl/openpyxl/issue/415) Improved exception when passing in invalid in memory files. ## 2.2.0-b1 (2015-02-18) ## Major changes - Cell styles deprecated, use formatting objects (fonts, fills, borders, etc.) directly instead - Charts will no longer try and calculate axes by default - Support for template file types - PR21 - Moved ancillary functions and classes into utils package - single place of reference - [PR 34](https://bitbucket.org/openpyxl/openpyxl/pull-request/34/) Fully support page setup - Removed SAX-based XML Generator. Special thanks to Elias Rabel for implementing xmlfile for xml.etree - Preserve sheet view definitions in existing files (frozen panes, zoom, etc.) ## Bug fixes - [#103](https://bitbucket.org/openpyxl/openpyxl/issue/103) Set the zoom of a sheet - [#199](https://bitbucket.org/openpyxl/openpyxl/issue/199) Hide gridlines - [#215](https://bitbucket.org/openpyxl/openpyxl/issue/215) Preserve sheet view setings - [#262](https://bitbucket.org/openpyxl/openpyxl/issue/262) Set the zoom of a sheet - [#392](https://bitbucket.org/openpyxl/openpyxl/issue/392) Worksheet header not read - [#387](https://bitbucket.org/openpyxl/openpyxl/issue/387) Cannot read files without styles.xml - [#410](https://bitbucket.org/openpyxl/openpyxl/issue/410) Exception when preserving whitespace in strings - [#417](https://bitbucket.org/openpyxl/openpyxl/issue/417) Cannot create print titles - [#420](https://bitbucket.org/openpyxl/openpyxl/issue/420) Rename confusing constants - [#422](https://bitbucket.org/openpyxl/openpyxl/issue/422) Preserve color index in a workbook if it differs from the standard ## Minor changes - Use a 2-way cache for column index lookups - Clean up tests in cells - [PR 40](https://bitbucket.org/openpyxl/openpyxl/pull-request/40/) Support frozen panes and autofilter in write-only mode - Use ws.calculate_dimension(force=True) in read-only mode for unsized worksheets ## 2.1.5 (2015-02-18) ## Bug fixes - [#403](https://bitbucket.org/openpyxl/openpyxl/issue/403) Cannot add comments in write-only mode - [#401](https://bitbucket.org/openpyxl/openpyxl/issue/401) Creating cells in an empty row raises an exception - [#408](https://bitbucket.org/openpyxl/openpyxl/issue/408) from_excel adjustment for Julian dates 1 \< x \< 60 - [#409](https://bitbucket.org/openpyxl/openpyxl/issue/409) refersTo is an optional attribute ## Minor changes - Allow cells to be appended to standard worksheets for code compatibility with write-only mode. ## 2.1.4 (2014-12-16) ## Bug fixes - [#393](https://bitbucket.org/openpyxl/openpyxl/issue/393) IterableWorksheet skips empty cells in rows - [#394](https://bitbucket.org/openpyxl/openpyxl/issue/394) Date format is applied to all columns (while only first column contains dates) - [#395](https://bitbucket.org/openpyxl/openpyxl/issue/395) temporary files not cleaned properly - [#396](https://bitbucket.org/openpyxl/openpyxl/issue/396) Cannot write \"=\" in Excel file - [#398](https://bitbucket.org/openpyxl/openpyxl/issue/398) Cannot write empty rows in write-only mode with LXML installed ## Minor changes - Add relation namespace to root element for compatibility with iWork - Serialize comments relation in LXML-backend ## 2.1.3 (2014-12-09) ## Minor changes - [PR 31](https://bitbucket.org/openpyxl/openpyxl/pull-request/31/) Correct tutorial - [PR 32](https://bitbucket.org/openpyxl/openpyxl/pull-request/32/) See #380 - [PR 37](https://bitbucket.org/openpyxl/openpyxl/pull-request/37/) Bind worksheet to ColumnDimension objects ## Bug fixes - [#379](https://bitbucket.org/openpyxl/openpyxl/issue/379) ws.append() doesn\'t set RowDimension Correctly - [#380](https://bitbucket.org/openpyxl/openpyxl/issue/379) empty cells formatted as datetimes raise exceptions ## 2.1.2 (2014-10-23) ## Minor changes - [PR 30](https://bitbucket.org/openpyxl/openpyxl/pull-request/30/) Fix regex for positive exponentials - [PR 28](https://bitbucket.org/openpyxl/openpyxl/pull-request/28/) Fix for #328 ## Bug fixes - [#120](https://bitbucket.org/openpyxl/openpyxl/issue/120), [#168](https://bitbucket.org/openpyxl/openpyxl/issue/168) defined names with formulae raise exceptions, [#292](https://bitbucket.org/openpyxl/openpyxl/issue/292) - [#328](https://bitbucket.org/openpyxl/openpyxl/issue/328/) ValueError when reading cells with hyperlinks - [#369](https://bitbucket.org/openpyxl/openpyxl/issue/369) IndexError when reading definedNames - [#372](https://bitbucket.org/openpyxl/openpyxl/issue/372) number_format not consistently applied from styles ## 2.1.1 (2014-10-08) ## Minor changes - PR 20 Support different workbook code names - Allow auto_axis keyword for ScatterCharts ## Bug fixes - [#332](https://bitbucket.org/openpyxl/openpyxl/issue/332) Fills lost in ConditionalFormatting - [#360](https://bitbucket.org/openpyxl/openpyxl/issue/360) Support value=\"none\" in attributes - [#363](https://bitbucket.org/openpyxl/openpyxl/issue/363) Support undocumented value for textRotation - [#364](https://bitbucket.org/openpyxl/openpyxl/issue/364) Preserve integers in read-only mode - [#366](https://bitbucket.org/openpyxl/openpyxl/issue/366) Complete read support for DataValidation - [#367](https://bitbucket.org/openpyxl/openpyxl/issue/367) Iterate over unsized worksheets ## 2.1.0 (2014-09-21) ## Major changes - \"read_only\" and \"write_only\" new flags for workbooks - Support for reading and writing worksheet protection - Support for reading hidden rows - Cells now manage their styles directly - ColumnDimension and RowDimension object manage their styles directly - Use xmlfile for writing worksheets if available - around 3 times faster - Datavalidation now part of the worksheet package ## Minor changes - Number formats are now just strings - Strings can be used for RGB and aRGB colours for Fonts, Fills and Borders - Create all style tags in a single pass - Performance improvement when appending rows - Cleaner conversion of Python to Excel values - PR6 reserve formatting for empty rows - standard worksheets can append from ranges and generators ## Bug fixes - [#153](https://bitbucket.org/openpyxl/openpyxl/issue/153) Cannot read visibility of sheets and rows - [#181](https://bitbucket.org/openpyxl/openpyxl/issue/181) No content type for worksheets - [241](https://bitbucket.org/openpyxl/openpyxl/issue/241) Cannot read sheets with inline strings - [322](https://bitbucket.org/openpyxl/openpyxl/issue/322) 1-indexing for merged cells - [339](https://bitbucket.org/openpyxl/openpyxl/issue/339) Correctly handle removal of cell protection - [341](https://bitbucket.org/openpyxl/openpyxl/issue/341) Cells with formulae do not round-trip - [347](https://bitbucket.org/openpyxl/openpyxl/issue/347) Read DataValidations - [353](https://bitbucket.org/openpyxl/openpyxl/issue/353) Support Defined Named Ranges to external workbooks ## 2.0.5 (2014-08-08) ## Bug fixes - [#348](https://bitbucket.org/openpyxl/openpyxl/issue/348) incorrect casting of boolean strings - [#349](https://bitbucket.org/openpyxl/openpyxl/issue/349) roundtripping cells with formulae ## 2.0.4 (2014-06-25) ## Minor changes - Add a sample file illustrating colours ## Bug fixes - [#331](https://bitbucket.org/openpyxl/openpyxl/issue/331) DARKYELLOW was incorrect - Correctly handle extend attribute for fonts ## 2.0.3 (2014-05-22) ## Minor changes - Updated docs ## Bug fixes - [#319](https://bitbucket.org/openpyxl/openpyxl/issue/319) Cannot load Workbooks with vertAlign styling for fonts ## 2.0.2 (2014-05-13) ## 2.0.1 (2014-05-13) brown bag ## 2.0.0 (2014-05-13) brown bag ## Major changes - This is last release that will support Python 3.2 - Cells are referenced with 1-indexing: A1 == cell(row=1, column=1) - Use jdcal for more efficient and reliable conversion of datetimes - Significant speed up when reading files - Merged immutable styles - Type inference is disabled by default - RawCell renamed ReadOnlyCell - ReadOnlyCell.internal_value and ReadOnlyCell.value now behave the same as Cell - Provide no size information on unsized worksheets - Lower memory footprint when reading files ## Minor changes - All tests converted to pytest - Pyflakes used for static code analysis - Sample code in the documentation is automatically run - Support GradientFills - BaseColWidth set ## Pull requests - #70 Add filterColumn, sortCondition support to AutoFilter - #80 Reorder worksheets parts - #82 Update API for conditional formatting - #87 Add support for writing Protection styles, others - #89 Better handling of content types when preserving macros ## Bug fixes - [#46](https://bitbucket.org/openpyxl/openpyxl/issue/46) ColumnDimension style error - [#86](https://bitbucket.org/openpyxl/openpyxl/issue/86) reader.worksheet.fast_parse sets booleans to integers - [#98](https://bitbucket.org/openpyxl/openpyxl/issue/98) Auto sizing column widths does not work - [#137](https://bitbucket.org/openpyxl/openpyxl/issue/137) Workbooks with chartsheets - [#185](https://bitbucket.org/openpyxl/openpyxl/issue/185) Invalid PageMargins - [#230](https://bitbucket.org/openpyxl/openpyxl/issue/230) Using v in cells creates invalid files - [#243](https://bitbucket.org/openpyxl/openpyxl/issue/243) - IndexError when loading workbook - [#263](https://bitbucket.org/openpyxl/openpyxl/issue/263) - Forded conversion of line breaks - [#267](https://bitbucket.org/openpyxl/openpyxl/issue/267) - Raise exceptions when passed invalid types - [#270](https://bitbucket.org/openpyxl/openpyxl/issue/270) - Cannot open files which use non-standard sheet names or reference Ids - [#269](https://bitbucket.org/openpyxl/openpyxl/issue/269) - Handling unsized worksheets in IterableWorksheet - [#270](https://bitbucket.org/openpyxl/openpyxl/issue/270) - Handling Workbooks with non-standard references - [#275](https://bitbucket.org/openpyxl/openpyxl/issue/275) - Handling auto filters where there are only custom filters - [#277](https://bitbucket.org/openpyxl/openpyxl/issue/277) - Harmonise chart and cell coordinates - [#280](https://bitbucket.org/openpyxl/openpyxl/issue/280)- Explicit exception raising for invalid characters - [#286](https://bitbucket.org/openpyxl/openpyxl/issue/286) - Optimized writer can not handle a datetime.time value - [#296](https://bitbucket.org/openpyxl/openpyxl/issue/296) - Cell coordinates not consistent with documentation - [#300](https://bitbucket.org/openpyxl/openpyxl/issue/300) - Missing column width causes load_workbook() exception - [#304](https://bitbucket.org/openpyxl/openpyxl/issue/304) - Handling Workbooks with absolute paths for worksheets (from Sharepoint) ## 1.8.6 (2014-05-05) ## Minor changes Fixed typo for import Elementtree ## Bugfixes - [#279](https://bitbucket.org/openpyxl/openpyxl/issue/279) Incorrect path for comments files on Windows ## 1.8.5 (2014-03-25) ## Minor changes - The \'=\' string is no longer interpreted as a formula - When a client writes empty xml tags for cells (e.g. \\), reader will not crash ## 1.8.4 (2014-02-25) ## Bugfixes - [#260](https://bitbucket.org/openpyxl/openpyxl/issue/260) better handling of undimensioned worksheets - [#268](https://bitbucket.org/openpyxl/openpyxl/issue/268) non-ascii in formualae - [#282](https://bitbucket.org/openpyxl/openpyxl/issue/282) correct implementation of register_namepsace for Python 2.6 ## 1.8.3 (2014-02-09) ## Major changes Always parse using cElementTree ## Minor changes Slight improvements in memory use when parsing - [#256](https://bitbucket.org/openpyxl/openpyxl/issue/256) - error when trying to read comments with optimised reader - [#260](https://bitbucket.org/openpyxl/openpyxl/issue/260) - unsized worksheets - [#264](https://bitbucket.org/openpyxl/openpyxl/issue/264) - only numeric cells can be dates ## 1.8.2 (2014-01-17) - [#247](https://bitbucket.org/openpyxl/openpyxl/issue/247) - iterable worksheets open too many files - [#252](https://bitbucket.org/openpyxl/openpyxl/issue/252) - improved handling of lxml - [#253](https://bitbucket.org/openpyxl/openpyxl/issue/253) - better handling of unique sheetnames ## 1.8.1 (2014-01-14) - [#246](https://bitbucket.org/openpyxl/openpyxl/issue/246) ## 1.8.0 (2014-01-08) ## Compatibility Support for Python 2.5 dropped. ## Major changes - Support conditional formatting - Support lxml as backend - Support reading and writing comments - pytest as testrunner now required - Improvements in charts: new types, more reliable ## Minor changes - load_workbook now accepts data_only to allow extracting values only from formulae. Default is false. - Images can now be anchored to cells - Docs updated - Provisional benchmarking - Added convenience methods for accessing worksheets and cells by key ## 1.7.0 (2013-10-31) ## Major changes Drops support for Python \< 2.5 and last version to support Python 2.5 ## Compatibility Tests run on Python 2.5, 2.6, 2.7, 3.2, 3.3 ## Merged pull requests - 27 Include more metadata - 41 Able to read files with chart sheets - 45 Configurable Worksheet classes - 3 Correct serialisation of Decimal - 36 Preserve VBA macros when reading files - 44 Handle empty oddheader and oddFooter tags - 43 Fixed issue that the reader never set the active sheet - 33 Reader set value and type explicitly and TYPE_ERROR checking - 22 added page breaks, fixed formula serialization - 39 Fix Python 2.6 compatibility - 47 Improvements in styling ## Known bugfixes - [#109](https://bitbucket.org/openpyxl/openpyxl/issue/109) - [#165](https://bitbucket.org/openpyxl/openpyxl/issue/165) - [#209](https://bitbucket.org/openpyxl/openpyxl/issue/209) - [#112](https://bitbucket.org/openpyxl/openpyxl/issue/112) - [#166](https://bitbucket.org/openpyxl/openpyxl/issue/166) - [#109](https://bitbucket.org/openpyxl/openpyxl/issue/109) - [#223](https://bitbucket.org/openpyxl/openpyxl/issue/223) - [#124](https://bitbucket.org/openpyxl/openpyxl/issue/124) - [#157](https://bitbucket.org/openpyxl/openpyxl/issue/157) ## Miscellaneous Performance improvements in optimised writer Docs updated