mcetl.excel_writer¶

ExcelWriterHandler class, used to safely open and close files and apply styles.

@author : Donald Erb Created on Dec 9, 2020

Notes

openpyxl is imported within methods of ExcelWriterHandler in order to reduce the import time of mcetl since the writer is only needed if writing to Excel.

Module Contents¶

Classes¶

ExcelWriterHandler

A helper for pandas's ExcelWriter for opening/saving files and applying styles.

class mcetl.excel_writer.ExcelWriterHandler(file_name=None, new_file=False, styles=None, writer=None, **kwargs)¶

A helper for pandas's ExcelWriter for opening/saving files and applying styles.

This class is used for ensuring that an existing file is closed before saving and/or writing, if appending, and that all desired styles are ready for usage before writing to Excel. Styles can either be openpyxl NamedStyle objects in order to make the style available in the Excel workbook, or dictionaries detailing the style, such as {'font': Font(...), 'border': Border(...), ...}.

Parameters

file_name (str or Path) -- The file name or path for the Excel file to be created.
new_file (bool, optional) -- If False (default), will append to an existing file. If True, or if no file currently exists with the given file_name, a new file will be created, even if a file with the same name currently exists.
styles (dict(str, None or dict or str or openpyxl.styles.named_styles.NamedStyle)) -- A dictionary of either nested dictionaries used to create openpyxl style objects (Alignment, Font, etc.), a string indicating the name of an openpyxl NamedStyle to use, a NamedStyle object or None (will use the default style if None). All styles in the dictionary will be added to the Excel workbook if they do not currently exist in the workbook. See Examples below to see various valid inputs for styles.
writer (pd.ExcelWriter or None) -- The ExcelWriter (_OpenpyxlWriter from pandas) used for writing to Excel. If it is a pd.ExcelWriter, its engine must be "openpyxl".
**kwargs -- Any additional keyword arguments to pass to pd.ExcelWriter.

styles¶

A nested dictionary of dictionaries, used to create openpyxl NamedStyle objects to include in self.writer.book. The styles are used as a class attribute to ensure that the necessary styles are always included in the Excel book.

Type: dict(str, dict)

style_cache¶

The currently implemented styles within the Excel workbook. Used to quickly apply styles to cells without having to constanly set all of the cell attributes (cell.font, cell.fill, etc.). The dictionary value is a tuple of the cell attribute name and the value to set. Will either be ('style', string indicating NamedStyle.name) if using NamedStyles or ('_style', openpyxl StyleArray) to indicate an anonomous style. The call to set the cell attribute for a desired key would be setattr(cell, *style_cache[key]).

Type: dict(str, tuple(str, str or openpyxl.styles.cell_style.StyleArray))

writer¶

The ExcelWriter (_OpenpyxlWriter from pandas) used for writing to Excel.

Type: pd.ExcelWriter

Notes

Either file_name or writer must be specified at initialization.

Examples

Below is a partial example of various allowable input styles. Can be openpyxl NamedStyle, str, None, or dictionary. (Note that NamedStyle, Font, Border, and Side are gotten by importing from openpyxl.styles)

>>> styles = {
        # Would make the style 'Even Header' available in the output Excel file
        'fitting_header_even': NamedStyle(
            name='Even Header',
            font=Font(size=12, bold=True),
            border=Border(bottom=Side(style='thin')),
            number_format='0.0'
        ),
        # Would use same format as 'fitting_header_even'
        'fitting_header_odd': 'Even Header',
        # Basically just replaces NamedStyle from 'fitting_header_even' with
        # dict and removes the 'name' key. A new style would not be created
        # in the output Excel file.
        'fitting_subheader_even': dict(
            font=Font(size=12, bold=True),
            aligment=Aligment(bottom=Side(style='thin')),
            number_format='0.0'
        ),
        # Same as 'fitting_subheader_even', but doesn't require importing
        # from openpyxl. Basically just replaces all openpyxl objects with dict.
        'fitting_subheader_odd': dict(
            font=dict(size=12, bold=True),
            aligment=dict(bottom=dict(style='thin')),
            number_format='0.0'
        ),
        # Same as 'fitting_subheader_odd', but will create a NamedStyle (and
        # add the style to the Excel file) since 'name' is within the dictionary.
        'fitting_columns_even': dict(
            name='New Style',
            font=dict(size=12, bold=True),
            aligment=dict(bottom=dict(style='thin')),
            number_format='0.0'
        ),
        # Would use default style set by openpyxl
        'fitting_columns_odd': {},
        # Would also use default style
        'fitting_descriptors_even': None
    }

Raises

TypeError -- Raised if both file_name and writer are None.
ValueError -- Raised if the engine of the input writer is not "openpyxl".

add_styles(self, styles)¶

Adds styles to the Excel workbook and update self.style_cache.

Parameters: styles (dict(str, None or dict or str or openpyxl.styles.named_styles.NamedStyle)) -- A dictionary of either nested dictionaries used to create openpyxl style objects (Alignment, Font, etc.), a string indicating the name of an openpyxl NamedStyle to use, a NamedStyle object or None (will use the default style if None). All styles in the dictionary will be added to the Excel workbook if they do not currently exist in the workbook.

Notes

The ordering of items within styles will be preserved, so that if two NamedStyles are input with the same name, the one appearing first in the dictionary will be created, and the second will be made to refer to the first.

save_excel_file(self)¶

Tries to save the Excel file, and handles any PermissionErrors.

Saving can be cancelled if other changes to self.writer are desired before saving, or if saving is no longer desired (the file must be open while trying to save to allow cancelling the save).

classmethod test_excel_styles(cls, styles)¶

Tests whether the input styles create valid Excel styles using openpyxl.

Parameters: styles (dict(str, None or dict or str or openpyxl.styles.named_styles.NamedStyle)) -- The dictionary of styles to test. Values in the dictionary can either be None, a nested dictionary with the necessary keys and values to create an openpyxl NamedStyle, a string (which would refer to another NamedStyle.name), or openpyxl.styles.NamedStyle objects.
Returns: success -- Returns True if all input styles successfully create openpyxl NamedStyle objects; otherwise, returns False.
Return type: bool