GitLocker: The Coding Marketplace

Description:

openpyxlstylewriter 1.1.4

openpyxl_style_writer

This is a wrapper base on openpyxl package. The original feature to create resuable style (NameStyled) is not avaliable for write only mode. Thus this package aimed to provide a easy way for user to create resuable styles and use it on write only mode easily
Installation
$ pip install openpyxl_style_writer
Usage
Example
from openpyxl_style_writer import CustomStyle, DefaultStyle, RowWriter

if __name__ == '__main__':
workbook = RowWriter()
# enable protection by protection=True
workbook.create_sheet('ExampleSheet', protection=True)

title = 'This is an example'
row_title_1 = ['fruits', 'fruits', 'animals', 'animals']
row_title_2 = ['apple', 'banana', 'cat', 'dog']
percent_data = [0.1, 0.6, 0.225, 0.4755, 0.9, 1]
data = [10, 20, 30, 40]

# append single cell with Default Style
workbook.row_append(title)
workbook.set_cell_width(1, 30)
workbook.create_row()
for item in row_title_1:
workbook.row_append(item)
workbook.create_row()

# set custom Default Style and append list in a row
blue_font_style = {'color': '0000ff', 'bold': True, 'size': 8}
DefaultStyle.set_default(font_params=blue_font_style)
workbook.row_append_list(row_title_2)
workbook.create_row()

# create new Custom Style and give row_append_list a style
pink_fill_style = {'patternType': 'solid', 'fgColor': 'd25096'}
# add protect to pink_style
pink_style = CustomStyle(fill_params=pink_fill_style, protect=True)
workbook.row_append_list(data, pink_style)
workbook.create_row()

# create number_format style
percent_style = CustomStyle(font_size=8, number_format='0.0%')
workbook.row_append_list(percent_data, percent_style)
workbook.create_row()

workbook.save('example.xlsx')

CustomStyle & DefaultStyle
You can either set the CustomStyle and DefaultStyle by the key words from openpyxl or from this package. Here is an example to create a CustomStyle by the openpyxl key words.
from openpyxl_style_writer import CustomStyle

# Create the style by the original key words from openpyxl

blue_title_font = {
'color': '0000ff',
'bold': True,
'size': 15,
}
cyan_title_pattern = {
'patternType': 'solid',
'fgColor': '00ffff '
}

# Use font_params and fill_params to create a reusable style with the blue font and cyan fill.
# The style you do not set will use the style of DefaultStyle.

custom_title_style = CustomStyle(
font_params=blue_title_font,
fill_params=cyan_titl_patter,
)

You could also set your DefaultStyle first, and the rest of the CustomStyle will follow the settings of DefaultStyle if the style do not set in CustomStyle.
from openpyxl_style_writer import CustomStyle, DefaultStyle

cyan_title_pattern = {
'patternType': 'solid',
'fgColor': '00ffff '
}

blue_title_font = {
'color': '0000ff',
'bold': True,
'size': 15,
}

# set default style with cyan fill
DefaultStyle.set_default(fill_params=cyan_title_pattern)

# This custom style will show blue font and cyan fill, although it only set the font_params
custom_title_style = CustomStyle(
font_params=blue_title_font,
)

If you want to do it in simple way, openpyxl_style_writer offer a map for some common key words. You can use the key words of openpyxl_style_writer.
from openpyxl_style_writer import CustomStyle

custom_title_style = CustomStyle(
font_size=15,
font_name='Calibri',
)

Or you can use both methods
from openpyxl_style_writer import CustomStyle

blue_title_font = {
'color': '0000ff',
'bold': True,
'size': 15,
}

custom_title_style = CustomStyle(
font_size=15,
font_name='Calibri',
fill_params=cyan_title_pattern
)

If you need to declare multiple styles, and many of them are very similar, you
can use the clone_and_modify function to streamline the process. For example,
if you need to create blue_title_style, red_title_style, and green_title_style,
the straightforward method involves creating each one individually:
blue_title_style = CustomStyle(
font_bold=True,
font_color='0000ff',
font_size=15
)
red_title_style = CustomStyle(
font_bold=True,
font_color='ff0000',
font_size=15
)
green_title_style = CustomStyle(
font_bold=True,
font_color='00ff00',
font_size=15
)

However, with the clone_and_modify method, you can create them more efficiently:
blue_title_style = CustomStyle(
font_bold=True,
font_color='0000ff',
font_size=15
)
red_title_style = blue_title_style.clone_and_modify(font_color='ff0000')
green_title_style = blue_title_style.clone_and_modify(font_color='00ff00')

This approach is especially helpful when there are many parameters to input in
a CustomStyle instance.
Advanced Usage
In scenarios where you want to establish a collection of reusable styles for your Excel documents or if you have a variety of different Excel outputs, you can define a base class containing multiple custom styles and then inherit from this base class in your Excel class.
The following example demonstrates how to achieve this using the openpyxl_style_writer library:
from openpyxl_style_writer import CustomStyle, RowWriter

class BaseExcelWriter(RowWriter):
blue_font = {
'color': '0000ff',
'bold': True,
'size': 15,
}
cyan_title_pattern = {
'patternType': 'solid',
'fgColor': '00ffff '
}
blue_font_style = CustomStyle(font_params=blue_font)
cyan_fill_style = CustomStyle(fill_params=cyan_title_pattern)
# Clone a existed style and modify font params
cyan_fill_with_blue_font = cyan_fill_style.cloned_and_modify(
font_params=blue_font
)

class ExampleExcel(BaseExcelWriter):
first_row = ['Apple', 'Banana', 'Cat']

def create(self, file_name='output.xlsx'):
self.create_sheet('ExampleSheet', protection=True)
self.row_append_list(self.first_row, style=self.cyan_fill_style)
self.create_row()
for idx, _ in enumerate(self.first_row):
self.row_append(idx, style=self.blue_font_style)
self.row_append(
'Clone_and_modify_cell',
style=self.cyan_fill_with_blue_font
)
self.create_row()
self.save(file_name)

class ExampleExcel2(BaseExcelWriter):

def create(self, file_name='output.xlsx'):
# create a content of excel2...

if __name__ == '__main__':
example = ExampleExcel()
example.create('example.xlsx')

List of Key words in openpyxl_style_writer
This is a list of the key words in openpyxl_style_writer and how it map to the attributes of openpyxl

class
Key
datatype
map to

font
font_size
int
openpyxl.styles.Font.size

font_name
str
openpyxl.styles.Font.name

font_bold
bool
openpyxl.styles.Font.bold

font_italic
bool
openpyxl.styles.Font.italic

font_underline
str
openpyxl.styles.Font.underline

font_strike
bool
openpyxl.styles.Font.strike

font_vertAlign
str
openpyxl.styles.Font.vertAlign

font_color
str
openpyxl.styles.Font.color

fill
fill_color
str
openpyxl.styles.PatternFill.color

alignment
ali_horizontal
str
openpyxl.styles.Alignment.color

ali_vertical
str
openpyxl.styles.Alignment.color

ali_wrap_text
str
openpyxl.styles.Alignment.color

border
border_style_top
str
openpyxl.styles.Border.top with openpyxl.styles.Side.border_style

border_style_right
str
openpyxl.styles.Border.right with openpyxl.styles.Side.border_style

border_style_left
str
openpyxl.styles.Border.left with openpyxl.styles.Side.border_style

border_style_bottom
str
openpyxl.styles.Border.bottom with openpyxl.styles.Side.border_style

border_color_top
str
openpyxl.styles.Border.top with openpyxl.styles.Side.color

border_color_right
str
openpyxl.styles.Border.right with openpyxl.styles.Side.color

border_color_left
str
openpyxl.styles.Border.left with openpyxl.styles.Side.color

border_color_bottom
str
openpyxl.styles.Border.bottom with openpyxl.styles.Side.color