def basic_example2():
df = pd.DataFrame(dict(a=[10, 20, 30, 40, 50], b=[0.1, 0.9,0.2, 0.6,0.3]), index=[1,2,3,4,5])
def style_func(idx, col):
if col == 'b':
return OpenPyxlStyleHelper.get_style(number_format='0.00%')
else:
# for 'a' we do dollar format
return OpenPyxlStyleHelper.get_style(number_format='$#,##.00')
# create a presentation model
# note the OpenPyxlStyleHelper function available in xlsx_styles module. But a return value of style function
# can be any dict whose keys are attributes of the OpenPyxl cell object.
presentation_model = build_presentation_model(
df=df,
data_value_func=lambda idx, col: df.loc[idx, col] * 10 if col == 'a' else df.loc[idx, col],
data_style_func=style_func,
header_value_func=lambda node: node.value.capitalize(),
header_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_value_func=lambda node: node.value * 100,
index_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_name_func=lambda _: 'Basic Example',
index_name_style_func=lambda _: OpenPyxlStyleHelper.default_header_style())
# create a layout, which is usually a nested list of presentation models
layout = [presentation_model]
# render to xlsx
output_fp = os.path.join(tempfile.gettempdir(), 'basic_example2.xlsx')
OpenPyxlCompositor.to_xlsx(layout=layout, output_fp=output_fp)
def layout_example1():
df = pd.DataFrame(dict(a=[10, 20, 30, 40, 50], b=[0.1, 0.9, 0.2, 0.6,
0.3]),
index=[1, 2, 3, 4, 5])
def style_func(idx, col):
if col == 'b':
return OpenPyxlStyleHelper.get_style(number_format='0.00%')
else:
# for 'a' we do dollar format
return OpenPyxlStyleHelper.get_style(number_format='$#,##.00')
# create a presentation model
# note the OpenPyxlStyleHeloer function available in xlsx_styles module. But a return value of style function
# can be any dict whose keys are attributes of the OpenPyxl cell object.
presentation_model = build_presentation_model(
df=df,
data_value_func=lambda idx, col: df.loc[idx, col] * 10
if col == 'a' else df.loc[idx, col],
data_style_func=style_func,
header_value_func=lambda node: node.value.capitalize(),
header_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_value_func=lambda node: node.value * 100,
index_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_name_func=lambda _: 'Basic Example',
index_name_style_func=lambda _: OpenPyxlStyleHelper.
default_header_style())
# start_layout_code_1
# create a layout, which is usually a nested list of presentation models
layout = [[presentation_model], [[presentation_model],
[presentation_model]]]
# render to xlsx
output_fp = os.path.join(tempfile.gettempdir(),
'layout_vertical_example1.xlsx')
# the default value for orientation is 'vertical'
XLSXWriter.to_xlsx(layout, output_fp=output_fp, orientation='vertical')
output_fp = os.path.join(tempfile.gettempdir(),
'layout_horizontal_example1.xlsx')
XLSXWriter.to_xlsx(layout, output_fp=output_fp, orientation='horizontal')
print('Writing xlsx file=', output_fp)
# mutiple nesting
layout_complex = [
presentation_model,
[presentation_model, [presentation_model, presentation_model]]
]
output_fp = os.path.join(tempfile.gettempdir(),
'layout_complex_example1.xlsx')
XLSXWriter.to_xlsx(layout_complex,
output_fp=output_fp,
orientation='vertical')
print('Writing xlsx file=', output_fp)
def data_style_func(self, i, c):
number_format = '_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)'
if c == 'b':
return OpenPyxlStyleHelper.get_style(
number_format=number_format,
border=OpenPyxlStyleHelper.CustomBorders.thin_black_border)
if self.df.loc[i, c] >= 0.2 and c == 'a':
return OpenPyxlStyleHelper.get_style(
bg_color='B8B8B8',
border=OpenPyxlStyleHelper.CustomBorders.thin_black_border)
return OpenPyxlStyleHelper.get_style(
border=OpenPyxlStyleHelper.CustomBorders.thin_black_border)
def header_style_func(node):
bg_color = 'FFFFFF'
if node.key == ('A',) or node.key == ('B',):
bg_color = '9E80B8'
elif node.key[1] == 'x':
bg_color = '4F90C1'
elif node.key[1] == 'y':
bg_color = '6DC066'
return OpenPyxlStyleHelper.get_style(bg_color=bg_color)
def index_style_func(self, node):
color = 'FFFF00'
if node.value == 'a':
color = '9BC2E6'
if node.value == 'b':
color = 'A9D08E'
return OpenPyxlStyleHelper.get_style(
bg_color=color,
border=OpenPyxlStyleHelper.CustomBorders.thin_black_border)
def index_style_func(node):
# node.key here could be one of (1,), (1, 100), (2,), (2, 100), (2, 200)
bg_color = 'FFFFFF'
if node.key == (1,) or node.key == (2,):
bg_color = '9E80B8'
elif node.key[1] == 100:
bg_color = '4F90C1'
elif node.key[1] == 200:
bg_color = '6DC066'
return OpenPyxlStyleHelper.get_style(bg_color=bg_color)
def style_func(idx, col):
if col == 'b':
return OpenPyxlStyleHelper.get_style(number_format='0.00%')
else:
# for 'a' we do dollar format
return OpenPyxlStyleHelper.get_style(number_format='$#,##.00')
def index_name_style_func(self, node):
color = '9BC2E6'
return OpenPyxlStyleHelper.get_style(
bg_color=color,
border=OpenPyxlStyleHelper.CustomBorders.thin_black_border)
def data_style_func_column_level(self, c):
return OpenPyxlStyleHelper.get_style(
number_format=NUMBER_FORMAT,
border=OpenPyxlStyleHelper.CustomBorders.thin_black_border)