def __init__(self, value, styler=None):
self.value = value
if styler is None:
if isinstance(self.value, pd_timestamp):
self.style = Styler(number_format=utils.number_formats.default_date_time_format)
elif isinstance(self.value, dt.date):
self.style = Styler(number_format=utils.number_formats.default_date_format)
elif isinstance(self.value, dt.time):
self.style = Styler(number_format=utils.number_formats.default_time_format)
else:
self.style = Styler()
else:
self.style = styler
def _get_style_object(sheet, theme_colors, row, column):
cell = sheet.cell(row=row, column=column)
if use_openpyxl_styles:
return cell
else:
return Styler.from_openpyxl_style(
cell, theme_colors, read_comments and cell.comment)
def apply_column_style(self,
cols_to_style,
styler_obj,
style_header=False,
use_default_formats=True,
width=None,
overwrite_default_style=True):
"""apply style to a whole column
:param str|list|tuple|set cols_to_style: the columns to apply the style to
:param Styler styler_obj: the styler object that contains the style to be applied
:param bool style_header: if True, style the headers as well
:param bool use_default_formats: if True, use predefined styles for dates and times
:param None|int|float width: non-default width for the given columns
:param bool overwrite_default_style: If True, the default style (the style used when initializing StyleFrame)
will be overwritten. If False then the default style and the provided style wil be combined using
Styler.combine method.
:return: self
:rtype: StyleFrame
"""
if not isinstance(styler_obj, Styler):
raise TypeError('styler_obj must be {}, got {} instead.'.format(
Styler.__name__,
type(styler_obj).__name__))
if not isinstance(cols_to_style, (list, tuple, set, pd.Index)):
cols_to_style = [cols_to_style]
if not all(col in self.columns for col in cols_to_style):
raise KeyError(
"one of the columns in {} wasn't found".format(cols_to_style))
if overwrite_default_style:
style_to_apply = styler_obj
else:
style_to_apply = Styler.combine(self._default_style, styler_obj)
for col_name in cols_to_style:
if style_header:
self.columns[self.columns.get_loc(
col_name)].style = style_to_apply
self._has_custom_headers_style = True
for index in self.index:
if use_default_formats:
if isinstance(self.at[index, col_name].value,
pd_timestamp):
style_to_apply.number_format = utils.number_formats.date_time
elif isinstance(self.at[index, col_name].value, dt.date):
style_to_apply.number_format = utils.number_formats.date
elif isinstance(self.at[index, col_name].value, dt.time):
style_to_apply.number_format = utils.number_formats.time_24_hours
self.at[index, col_name].style = style_to_apply
if width:
self.set_column_width(columns=cols_to_style, width=width)
return self
def apply_style_by_indexes(self,
indexes_to_style,
styler_obj,
cols_to_style=None,
height=None,
complement_style=None,
complement_height=None,
overwrite_default_style=True):
"""Applies a certain style to the provided indexes in the dataframe in the provided columns
:param list|tuple|int|Container indexes_to_style: indexes to which the provided style will be applied
:param Styler styler_obj: the styler object that contains the style which will be applied to indexes in indexes_to_style
:param None|str|list|tuple|set cols_to_style: the columns to apply the style to, if not provided all the columns will be styled
:param None|int|float height: height for rows whose indexes are in indexes_to_style
:param None|Styler complement_style: the styler object that contains the style which will be applied to indexes not in indexes_to_style
:param None|int|float complement_height: height for rows whose indexes are not in indexes_to_style. If not provided then
height will be used (if provided).
:param bool overwrite_default_style: If True, the default style (the style used when initializing StyleFrame)
will be overwritten. If False then the default style and the provided style wil be combined using
Styler.combine method.
:return: self
:rtype: StyleFrame
"""
if not isinstance(styler_obj, Styler):
raise TypeError('styler_obj must be {}, got {} instead.'.format(
Styler.__name__,
type(styler_obj).__name__))
if isinstance(indexes_to_style, (list, tuple, int)):
indexes_to_style = self.index[indexes_to_style]
elif isinstance(indexes_to_style, Container):
indexes_to_style = pd.Index([indexes_to_style])
default_number_formats = {
pd_timestamp: utils.number_formats.default_date_time_format,
dt.date: utils.number_formats.default_date_format,
dt.time: utils.number_formats.default_time_format
}
orig_number_format = styler_obj.number_format
if cols_to_style is not None and not isinstance(
cols_to_style, (list, tuple, set)):
cols_to_style = [cols_to_style]
elif cols_to_style is None:
cols_to_style = list(self.data_df.columns)
if overwrite_default_style:
style_to_apply = deepcopy(styler_obj)
else:
style_to_apply = Styler.combine(self._default_style, styler_obj)
for index in indexes_to_style:
if orig_number_format == utils.number_formats.general:
style_to_apply.number_format = default_number_formats.get(
type(index.value), utils.number_formats.general)
index.style = style_to_apply
for col in cols_to_style:
cell = self.iloc[self.index.get_loc(index),
self.columns.get_loc(col)]
if orig_number_format == utils.number_formats.general:
style_to_apply.number_format = default_number_formats.get(
type(cell.value), utils.number_formats.general)
cell.style = style_to_apply
if height:
# Add offset 2 since rows do not include the headers and they starts from 1 (not 0).
rows_indexes_for_height_change = [
self.index.get_loc(idx) + 2 for idx in indexes_to_style
]
self.set_row_height(rows=rows_indexes_for_height_change,
height=height)
if complement_style:
self.apply_style_by_indexes(
self.index.difference(indexes_to_style), complement_style,
cols_to_style,
complement_height if complement_height else height)
return self
def to_excel(self,
excel_writer='output.xlsx',
sheet_name='Sheet1',
allow_protection=False,
right_to_left=False,
columns_to_hide=None,
row_to_add_filters=None,
columns_and_rows_to_freeze=None,
best_fit=None,
**kwargs):
"""Saves the dataframe to excel and applies the styles.
:param str|pandas.ExcelWriter excel_writer: File path or existing ExcelWriter
:param str sheet_name: Name of sheet the StyleFrame will be exported to
:param bool right_to_left: sets the sheet to be right to left.
:param None|str|list|tuple|set columns_to_hide: single column, list, set or tuple of columns to hide, may be column index (starts from 1)
column name or column letter.
:param bool allow_protection: allow to protect the sheet and the cells that specified as protected.
:param None|int row_to_add_filters: add filters to the given row, starts from zero (zero is to add filters to columns).
:param None|str columns_and_rows_to_freeze: column and row string to freeze for example: C3 will freeze columns: A,B and rows: 1,2.
:param None|str|list|tuple|set best_fit: single column, list, set or tuple of columns names to attempt to best fit the width
for.
See Pandas.DataFrame.to_excel documentation about other arguments
"""
# dealing with needed pandas.to_excel defaults
header = kwargs.pop('header', True)
index = kwargs.pop('index', False)
startcol = kwargs.pop('startcol', 0)
startrow = kwargs.pop('startrow', 0)
na_rep = kwargs.pop('na_rep', '')
def get_values(x):
if isinstance(x, Container):
return x.value
else:
try:
if np.isnan(x):
return na_rep
else:
return x
except TypeError:
return x
def within_sheet_boundaries(row=1, column='A'):
return (1 <= int(row) <= sheet.max_row and 1 <=
cell.column_index_from_string(column) <= sheet.max_column)
def get_range_of_cells(row_index=None, columns=None):
if columns is None:
start_letter = self._get_column_as_letter(
sheet, self.data_df.columns[0], startcol)
end_letter = self._get_column_as_letter(
sheet, self.data_df.columns[-1], startcol)
else:
start_letter = self._get_column_as_letter(
sheet, columns[0], startcol)
end_letter = self._get_column_as_letter(
sheet, columns[-1], startcol)
if row_index is None: # returns cells range for the entire dataframe
start_index = startrow + 1
end_index = start_index + len(self)
else:
start_index = startrow + row_index + 1
end_index = start_index
return '{start_letter}{start_index}:{end_letter}{end_index}'.format(
start_letter=start_letter,
start_index=start_index,
end_letter=end_letter,
end_index=end_index)
if len(self.data_df) > 0:
export_df = self.data_df.applymap(get_values)
else:
export_df = deepcopy(self.data_df)
export_df.columns = [col.value for col in export_df.columns]
# noinspection PyTypeChecker
export_df.index = [row_index.value for row_index in export_df.index]
export_df.index.name = self.data_df.index.name
if isinstance(excel_writer, (str, pathlib.Path)):
excel_writer = self.ExcelWriter(excel_writer)
export_df.to_excel(excel_writer,
sheet_name=sheet_name,
engine='openpyxl',
header=header,
index=index,
startcol=startcol,
startrow=startrow,
na_rep=na_rep,
**kwargs)
sheet = excel_writer.sheets[sheet_name]
sheet.sheet_view.rightToLeft = right_to_left
self.data_df.fillna(Container('NaN'), inplace=True)
if index:
if self.data_df.index.name:
index_name_cell = sheet.cell(row=startrow + 1,
column=startcol + 1)
index_name_cell.style = self._index_header_style.to_openpyxl_style(
)
for row_index, index in enumerate(self.data_df.index):
try:
style_to_apply = index.style.to_openpyxl_style()
except AttributeError:
style_to_apply = index.style
current_cell = sheet.cell(row=startrow + row_index + 2,
column=startcol + 1)
current_cell.style = style_to_apply
if isinstance(index.style, Styler):
current_cell.comment = index.style.generate_comment()
else:
if hasattr(index.style, 'comment'):
index.style.comment.parent = None
current_cell.comment = index.style.comment
startcol += 1
if header and not self._has_custom_headers_style:
self.apply_headers_style(Styler.default_header_style())
# Iterating over the dataframe's elements and applying their styles
# openpyxl's rows and cols start from 1,1 while the dataframe is 0,0
for col_index, column in enumerate(self.data_df.columns):
try:
style_to_apply = column.style.to_openpyxl_style()
except AttributeError:
style_to_apply = Styler.from_openpyxl_style(
column.style, [],
openpyxl_comment=column.style.comment).to_openpyxl_style()
column_header_cell = sheet.cell(row=startrow + 1,
column=col_index + startcol + 1)
column_header_cell.style = style_to_apply
if isinstance(column.style, Styler):
column_header_cell.comment = column.style.generate_comment()
else:
if hasattr(column.style,
'comment') and column.style.comment is not None:
column_header_cell.comment = column.style.comment
for row_index, index in enumerate(self.data_df.index):
current_cell = sheet.cell(row=row_index + startrow + 2,
column=col_index + startcol + 1)
data_df_style = self.data_df.at[index, column].style
try:
if '=HYPERLINK' in str(current_cell.value):
data_df_style.font_color = utils.colors.blue
data_df_style.underline = utils.underline.single
else:
if best_fit and column.value in best_fit:
data_df_style.wrap_text = False
data_df_style.shrink_to_fit = False
try:
style_to_apply = data_df_style.to_openpyxl_style()
except AttributeError:
style_to_apply = Styler.from_openpyxl_style(
data_df_style, [],
openpyxl_comment=data_df_style.comment
).to_openpyxl_style()
current_cell.style = style_to_apply
if isinstance(data_df_style, Styler):
current_cell.comment = data_df_style.generate_comment()
else:
if hasattr(data_df_style, 'comment'
) and data_df_style.comment is not None:
current_cell.comment = data_df_style.comment
except AttributeError: # if the element in the dataframe is not Container creating a default style
current_cell.style = Styler().to_openpyxl_style()
if best_fit:
if not isinstance(best_fit, (list, set, tuple)):
best_fit = [best_fit]
self.set_column_width_dict({
column: (max(self.data_df[column].astype(str).str.len()) +
self.A_FACTOR) * self.P_FACTOR
for column in best_fit
})
for column in self._columns_width:
column_letter = self._get_column_as_letter(sheet, column, startcol)
sheet.column_dimensions[column_letter].width = self._columns_width[
column]
for row in self._rows_height:
if within_sheet_boundaries(row=(row + startrow)):
sheet.row_dimensions[startrow +
row].height = self._rows_height[row]
else:
raise IndexError('row: {} is out of range'.format(row))
if row_to_add_filters is not None:
try:
row_to_add_filters = int(row_to_add_filters)
if not within_sheet_boundaries(row=(row_to_add_filters +
startrow + 1)):
raise IndexError('row: {} is out of rows range'.format(
row_to_add_filters))
sheet.auto_filter.ref = get_range_of_cells(
row_index=row_to_add_filters)
except (TypeError, ValueError):
raise TypeError("row must be an index and not {}".format(
type(row_to_add_filters)))
if columns_and_rows_to_freeze is not None:
if not isinstance(columns_and_rows_to_freeze,
str) or len(columns_and_rows_to_freeze) < 2:
raise TypeError(
"columns_and_rows_to_freeze must be a str for example: 'C3'"
)
if not within_sheet_boundaries(
column=columns_and_rows_to_freeze[0]):
raise IndexError("column: %s is out of columns range." %
columns_and_rows_to_freeze[0])
if not within_sheet_boundaries(row=columns_and_rows_to_freeze[1]):
raise IndexError("row: %s is out of rows range." %
columns_and_rows_to_freeze[1])
sheet.freeze_panes = sheet[columns_and_rows_to_freeze]
if allow_protection:
sheet.protection.autoFilter = False
sheet.protection.enable()
# Iterating over the columns_to_hide and check if the format is columns name, column index as number or letter
if columns_to_hide:
if not isinstance(columns_to_hide, (list, set, tuple)):
columns_to_hide = [columns_to_hide]
for column in columns_to_hide:
column_letter = self._get_column_as_letter(
sheet, column, startcol)
sheet.column_dimensions[column_letter].hidden = True
for cond_formatting in self._cond_formatting:
sheet.conditional_formatting.add(
get_range_of_cells(columns=cond_formatting.columns),
cond_formatting.rule)
return excel_writer
def __init__(self, obj, styler_obj=None):
from_another_styleframe = False
from_pandas_dataframe = False
if styler_obj and not isinstance(styler_obj, Styler):
raise TypeError('styler_obj must be {}, got {} instead.'.format(
Styler.__name__,
type(styler_obj).__name__))
if isinstance(obj, pd.DataFrame):
from_pandas_dataframe = True
if obj.empty:
self.data_df = deepcopy(obj)
else:
self.data_df = obj.applymap(
lambda x: Container(x, deepcopy(styler_obj))
if not isinstance(x, Container) else x)
elif isinstance(obj, pd.Series):
self.data_df = obj.apply(
lambda x: Container(x, deepcopy(styler_obj))
if not isinstance(x, Container) else x)
elif isinstance(obj, (dict, list)):
self.data_df = pd.DataFrame(obj).applymap(
lambda x: Container(x, deepcopy(styler_obj))
if not isinstance(x, Container) else x)
elif isinstance(obj, StyleFrame):
self.data_df = deepcopy(obj.data_df)
from_another_styleframe = True
else:
raise TypeError("{} __init__ doesn't support {}".format(
type(self).__name__,
type(obj).__name__))
self.data_df.columns = [
Container(col, deepcopy(styler_obj))
if not isinstance(col, Container) else deepcopy(col)
for col in self.data_df.columns
]
self.data_df.index = [
Container(index, deepcopy(styler_obj))
if not isinstance(index, Container) else deepcopy(index)
for index in self.data_df.index
]
if from_pandas_dataframe:
self.data_df.index.name = obj.index.name
self._columns_width = obj._columns_width if from_another_styleframe else OrderedDict(
)
self._rows_height = obj._rows_height if from_another_styleframe else OrderedDict(
)
self._has_custom_headers_style = obj._has_custom_headers_style if from_another_styleframe else False
self._cond_formatting = []
self._default_style = styler_obj or Styler()
self._index_header_style = obj._index_header_style if from_another_styleframe else self._default_style
self._known_attrs = {
'at': self.data_df.at,
'loc': self.data_df.loc,
'iloc': self.data_df.iloc,
'applymap': self.data_df.applymap,
'groupby': self.data_df.groupby,
'index': self.data_df.index,
'fillna': self.data_df.fillna
}
def apply_column_style(self,
cols_to_style,
styler_obj,
style_header=False,
use_default_formats=True,
width=None,
overwrite_default_style=True):
"""Apply style to a whole column
:param cols_to_style: The column names to style.
:type cols_to_style: str or list or tuple or set
:param styler_obj: A `Styler` object.
:type styler_obj: :class:`.Styler`
:param bool style_header: If ``True``, the column(s) header will also be styled.
:param bool use_default_formats: If ``True``, the default formats for date and times will be used.
:param width: If provided, the new width for the specified columns.
:type width: None or int or float
:param bool overwrite_default_style: (bool) If ``True``, the default style (the style used when initializing StyleFrame)
will be overwritten. If ``False`` then the default style and the provided style wil be combined using
:meth:`.Styler.combine` method.
:return: self
:rtype: :class:`StyleFrame`
"""
if not isinstance(styler_obj, Styler):
raise TypeError('styler_obj must be {}, got {} instead.'.format(
Styler.__name__,
type(styler_obj).__name__))
if not isinstance(cols_to_style, (list, tuple, set, pd.Index)):
cols_to_style = [cols_to_style]
if not all(col in self.columns for col in cols_to_style):
raise KeyError(
"one of the columns in {} wasn't found".format(cols_to_style))
if overwrite_default_style:
style_to_apply = styler_obj
else:
style_to_apply = Styler.combine(self._default_style, styler_obj)
for col_name in cols_to_style:
if style_header:
self.columns[self.columns.get_loc(
col_name)].style = style_to_apply
self._has_custom_headers_style = True
for index in self.index:
if use_default_formats:
if isinstance(self.at[index, col_name].value,
pd_timestamp):
style_to_apply.number_format = utils.number_formats.date_time
elif isinstance(self.at[index, col_name].value, dt.date):
style_to_apply.number_format = utils.number_formats.date
elif isinstance(self.at[index, col_name].value, dt.time):
style_to_apply.number_format = utils.number_formats.time_24_hours
self.at[index, col_name].style = style_to_apply
if width:
self.set_column_width(columns=cols_to_style, width=width)
return self
def apply_style_by_indexes(self,
indexes_to_style,
styler_obj,
cols_to_style=None,
height=None,
complement_style=None,
complement_height=None,
overwrite_default_style=True):
"""
Applies a certain style to the provided indexes in the dataframe in the provided columns
:param indexes_to_style: Indexes to which the provided style will be applied.
Usually passed as pandas selecting syntax. For example,
::
sf[sf['some_col'] = 20]
:type indexes_to_style: list or tuple or int or Container
:param styler_obj: `Styler` object that contains the style that will be applied to indexes in `indexes_to_style`
:type styler_obj: :class:`.Styler`
:param cols_to_style: The column names to apply the provided style to. If ``None`` all columns will be styled.
:type cols_to_style: None or str or list[str] or tuple[str] or set[str]
:param height: If provided, set height for rows whose indexes are in `indexes_to_style`.
:type height: None or int or float
.. versionadded:: 1.5
:param complement_style: `Styler` object that contains the style which will be applied to indexes not in `indexes_to_style`
:type complement_style: None or :class:`.Styler`
:param complement_height: Height for rows whose indexes are not in `indexes_to_style`. If not provided then
`height` will be used (if provided).
:type complement_height: None or int or float
.. versionadded:: 1.6
:param bool overwrite_default_style: If ``True``, the default style (the style used when initializing StyleFrame)
will be overwritten. If ``False`` then the default style and the provided style wil be combined using
:meth:`.Styler.combine` method.
:return: self
:rtype: :class:`StyleFrame`
"""
if not isinstance(styler_obj, Styler):
raise TypeError('styler_obj must be {}, got {} instead.'.format(
Styler.__name__,
type(styler_obj).__name__))
if isinstance(indexes_to_style, (list, tuple, int)):
indexes_to_style = self.index[indexes_to_style]
elif isinstance(indexes_to_style, Container):
indexes_to_style = pd.Index([indexes_to_style])
default_number_formats = {
pd_timestamp: utils.number_formats.default_date_time_format,
dt.date: utils.number_formats.default_date_format,
dt.time: utils.number_formats.default_time_format
}
orig_number_format = styler_obj.number_format
if cols_to_style is not None and not isinstance(
cols_to_style, (list, tuple, set)):
cols_to_style = [cols_to_style]
elif cols_to_style is None:
cols_to_style = list(self.data_df.columns)
if overwrite_default_style:
style_to_apply = deepcopy(styler_obj)
else:
style_to_apply = Styler.combine(self._default_style, styler_obj)
for index in indexes_to_style:
if orig_number_format == utils.number_formats.general:
style_to_apply.number_format = default_number_formats.get(
type(index.value), utils.number_formats.general)
index.style = style_to_apply
for col in cols_to_style:
cell = self.iloc[self.index.get_loc(index),
self.columns.get_loc(col)]
if orig_number_format == utils.number_formats.general:
style_to_apply.number_format = default_number_formats.get(
type(cell.value), utils.number_formats.general)
cell.style = style_to_apply
if height:
# Add offset 2 since rows do not include the headers and they starts from 1 (not 0).
rows_indexes_for_height_change = [
self.index.get_loc(idx) + 2 for idx in indexes_to_style
]
self.set_row_height(rows=rows_indexes_for_height_change,
height=height)
if complement_style:
self.apply_style_by_indexes(
self.index.difference(indexes_to_style), complement_style,
cols_to_style,
complement_height if complement_height else height)
return self