styleframe
The styleframe module contains a single class StyleFrame which servers as the main interaction point.
- class StyleFrame(obj, styler_obj: Styler | None = None, columns: List[str] | None = None)
A wrapper class that wraps a
pandas.DataFrame
object and represent a stylized dataframe. Stores container objects that have values and styles that will be applied to excel- Parameters:
obj – Any object that pandas’ dataframe can be initialized with: an existing dataframe, a dictionary, a list of dictionaries or another StyleFrame.
styler_obj (
Styler
) – Will be used as the default style of all cells.columns (None or list[str]) – Names of columns to use. Only applicable if
obj
isnumpy.ndarray
- classmethod ExcelWriter(path, **kwargs)
A shortcut for
pandas.ExcelWriter
, and accepts any argument it accepts except forengine
- add_color_scale_conditional_formatting(start_type: str, start_value: int | float, start_color: str, end_type: str, end_value: int | float, end_color: str, mid_type: str | None = None, mid_value: int | float | None = None, mid_color: str | None = None, columns_range=None)
- Parameters:
start_type (str: one of
utils.conditional_formatting_types
or any other type Excel supports) – The type for the minimum boundstart_value – The threshold for the minimum bound
start_color (str: one of
utils.colors
, hex string or color name ie ‘yellow’ Excel supports) – The color for the minimum boundend_type (str: one of
utils.conditional_formatting_types
or any other type Excel supports) – The type for the maximum boundend_value – The threshold for the maximum bound
end_color (str: one of
utils.colors
, hex string or color name ie ‘yellow’ Excel supports) – The color for the maximum boundmid_type (None or str: one of
utils.conditional_formatting_types
or any other type Excel supports) – The type for the middle boundmid_value – The threshold for the middle bound
mid_color (None or str: one of
utils.colors
, hex string or color name ie ‘yellow’ Excel supports) – The color for the middle boundcolumns_range (None or list[str or int] or tuple[str or int])) – A two-elements list or tuple of columns to which the conditional formatting will be added to. If not provided at all the conditional formatting will be added to all columns. If a single element is provided then the conditional formatting will be added to the provided column. If two elements are provided then the conditional formatting will start in the first column and end in the second. The provided columns can be a column name, letter or index.
- Returns:
self
- Return type:
- apply_column_style(cols_to_style: str | List[str] | Tuple[str] | Set[str], styler_obj: Styler, style_header: bool = False, use_default_formats: bool = True, width: int | float | None = None, overwrite_default_style: bool = True)
Apply style to a whole column
- Parameters:
cols_to_style (str or list or tuple or set) – The column names to style.
styler_obj (
Styler
) – A Styler object.style_header (bool) – If
True
, the column(s) header will also be styled.use_default_formats (bool) – If
True
, the default formats for date and times will be used.width (None or int or float) – If provided, the new width for the specified columns.
overwrite_default_style (bool) – (bool) If
True
, the default style (the style used when initializing StyleFrame) will be overwritten. IfFalse
then the default style and the provided style wil be combined usingStyler.combine()
method.
- Returns:
self
- Return type:
- apply_headers_style(styler_obj: Styler, style_index_header: bool = True, cols_to_style: str | List[str] | Tuple[str] | Set[str] | None = None)
Apply style to the headers only
- Parameters:
styler_obj (
Styler
) – The style to apply
New in version 1.6.1.
- Parameters:
style_index_header (bool) – If True then the style will also be applied to the header of the index column
New in version 2.0.5.
- apply_style_by_indexes(indexes_to_style: list | tuple | int | Container, styler_obj: Styler, cols_to_style: str | List[str] | Tuple[str] | Set[str] | None = None, height: int | float | None = None, complement_style: Styler | None = None, complement_height: int | float | None = None, overwrite_default_style: bool = True)
Applies a certain style to the provided indexes in the dataframe in the provided columns
- Parameters:
indexes_to_style (list or tuple or int or Container) –
Indexes to which the provided style will be applied. Usually passed as pandas selecting syntax. For example,
sf[sf['some_col'] == 20]
styler_obj (
Styler
) – Styler object that contains the style that will be applied to indexes in indexes_to_stylecols_to_style (None or str or list[str] or tuple[str] or set[str]) – The column names to apply the provided style to. If
None
all columns will be styled.height (None or int or float) – If provided, set height for rows whose indexes are in indexes_to_style.
New in version 1.5.
- Parameters:
New in version 1.6.
- Parameters:
overwrite_default_style (bool) – If
True
, the default style (the style used when initializing StyleFrame) will be overwritten. IfFalse
then the default style and the provided style wil be combined usingStyler.combine()
method.- Returns:
self
- Return type:
- classmethod read_excel(path: str, sheet_name: str | int = 0, read_style: bool = False, use_openpyxl_styles: bool = False, read_comments: bool = False, **kwargs) StyleFrame
Creates a StyleFrame object from an existing Excel.
Note
read_excel()
also accepts all arguments thatpandas.read_excel()
accepts as kwargs.- Parameters:
path (str) – The path to the Excel file to read.
sheet_name (str or int) – The sheet name to read. If an integer is provided then it be used as a zero-based sheet index. Default is 0.
read_style (bool) – If
True
the sheet’s style will be loaded to the returned StyleFrame object.use_openpyxl_styles (bool) –
If
True
(and read_style is alsoTrue
) then the styles in the returned StyleFrame object will be Openpyxl’s style objects. IfFalse
, the styles will beStyler
objects.Note
Using
use_openpyxl_styles=False
is useful if you are going to filter columns or rows by style, for example:sf = sf[[col for col in sf.columns if col.style.font == utils.fonts.arial]]
read_comments (bool) – If
True
(and read_style is alsoTrue
) cells’ comments will be loaded to the returned StyleFrame object. Note that reading comments without reading styles is currently not supported.
- Returns:
StyleFrame object
- Return type:
- classmethod read_excel_as_template(path: str, df: DataFrame, use_df_boundaries: bool = False, **kwargs) StyleFrame
New in version 3.0.1.
Create a StyleFrame object from an excel template with data of the given DataFrame.
Note
read_excel_as_template()
also accepts all arguments thatread_excel()
accepts as kwargs except forread_style
which must beTrue
.- Parameters:
path (str) – The path to the Excel file to read.
df (
pandas.DataFrame
) – The data to apply to the given template.use_df_boundaries (bool) – If
True
the template will be cut according to the boundaries of the given DataFrame.
- Returns:
StyleFrame object
- Return type:
- rename(columns=None, inplace=False)
Renames the underlying dataframe’s columns
- Parameters:
- Returns:
self if inplace is
True
, new StyleFrame object isFalse
- Return type:
- set_column_width(columns: str | List[str] | Tuple[str] | List[int] | Tuple[int], width: int | float) StyleFrame
Set the width of the given columns
- set_column_width_dict(col_width_dict: Dict[str, int | float]) StyleFrame
- set_row_height(rows: int | List[int] | Tuple[int] | Set[int], height: int | float) StyleFrame
Set the height of the given rows
- set_row_height_dict(row_height_dict: Dict[int, int | float]) StyleFrame
- style_alternate_rows(styles: List[Styler] | Tuple[Styler], **kwargs) StyleFrame
New in version 1.2.
Applies the provided styles to rows in an alternating manner.
Note
style_alternate_rows()
also accepts all arguments thatapply_style_by_indexes()
accepts as kwargs.
- to_excel(excel_writer: str | ExcelWriter | Path = 'output.xlsx', sheet_name: str = 'Sheet1', allow_protection: bool = False, right_to_left: bool = False, columns_to_hide: None | str | list | tuple | set = None, row_to_add_filters: int | None = None, columns_and_rows_to_freeze: str | None = None, best_fit: None | str | list | tuple | set = None, index: bool = False, **kwargs) ExcelWriter
Saves the dataframe to excel and applies the styles.
Note
to_excel()
also accepts all arguments thatpandas.DataFrame.to_excel()
accepts as kwargs.- Parameters:
excel_writer (str or
pandas.ExcelWriter
orpathlib.Path
) – File path or existing ExcelWritersheet_name (str) – Name of sheet the StyleFrame will be exported to
allow_protection (bool) – Allow to protect the cells that specified as protected. If used
protection=True
in a Styler object this must be set toTrue
.right_to_left (bool) – Makes the sheet right-to-left.
columns_to_hide (None or str or list or tuple or set) – Columns names to hide.
row_to_add_filters (None or int) – Add filters to the given row index, starts from 0 (which will add filters to header row).
columns_and_rows_to_freeze (None or str) – Column and row string to freeze. For example “C3” will freeze columns: A, B and rows: 1, 2.
New in version 1.4.
- Parameters:
best_fit (None or str or list or tuple or set) –
single column, list, set or tuple of columns names to attempt to best fit the width for.
Note
best_fit
will attempt to calculate the correct column-width based on the longest value in each provided column. However this isn’t guaranteed to work for all fonts (works best with monospaced fonts). The formula used to calculate a column’s width is equivalent to(len(longest_value_in_column) + A_FACTOR) * P_FACTOR
The default values for
A_FACTOR
andP_FACTOR
are 13 and 1.3 respectively, and can be modified before callingStyleFrame.to_excel
by directly modifyingStyleFrame.A_FACTOR
andStyleFrame.P_FACTOR
New in version 4.2.
- Parameters:
index (bool) – Write row names.
- Return type: