def test_format_comparisions(NumberFormat):
format1 = NumberFormat('m/d/yyyy')
format2 = NumberFormat('m/d/yyyy')
format3 = NumberFormat('mm/dd/yyyy')
assert format1 == format2
assert format1 == 'm/d/yyyy' and format1 != 'mm/dd/yyyy'
assert format3 != 'm/d/yyyy' and format3 == 'mm/dd/yyyy'
assert format1 != format3
def test_is_not_date_color_format():
wb = Workbook()
ws = Worksheet(wb)
cell = Cell(ws, 'A', 1)
cell.value = -13.5
cell.style = cell.style.copy(number_format=NumberFormat('0.00_);[Red]\(0.00\)'))
assert cell.is_date() is False
def parse_cell_xfs(self):
"""Read styles from the shared style table"""
cell_xfs = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
styles_list = self.style_prop['list']
if cell_xfs is None: # can happen on bad OOXML writers (e.g. Gnumeric)
return
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs_nodes = safe_iterator(cell_xfs, '{%s}xf' % SHEET_MAIN_NS)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
_style = {}
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
format_code = builtin_formats.get(number_format_id, 'General')
else:
fmt_code = self.custom_num_formats.get(number_format_id)
if fmt_code is not None:
format_code = fmt_code
else:
raise MissingNumberFormat('%s' % number_format_id)
_style['number_format'] = NumberFormat(format_code=format_code)
if bool(cell_xfs_node.get('applyAlignment')):
alignment = {}
al = cell_xfs_node.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
alignment = al.attrib
_style['alignment'] = Alignment(**alignment)
if bool(cell_xfs_node.get('applyFont')):
_style['font'] = self.font_list[int(
cell_xfs_node.get('fontId'))].copy()
if bool(cell_xfs_node.get('applyFill')):
_style['fill'] = self.fill_list[int(
cell_xfs_node.get('fillId'))].copy()
if bool(cell_xfs_node.get('applyBorder')):
_style['border'] = self.border_list[int(
cell_xfs_node.get('borderId'))].copy()
if bool(cell_xfs_node.get('applyProtection')):
protection = {}
prot = cell_xfs_node.find('{%s}protection' % SHEET_MAIN_NS)
if prot is not None:
protection.update(prot.attrib)
_style['protection'] = Protection(**protection)
self.style_prop['table'][index] = styles_list.add(Style(**_style))
def setup_class(cls):
now = datetime.datetime.now()
cls.workbook = Workbook(guess_types=True)
cls.worksheet = cls.workbook.create_sheet()
cls.worksheet.cell(coordinate='A1').value = '12.34%' # 2
cls.worksheet.cell(coordinate='B4').value = now # 3
cls.worksheet.cell(coordinate='B5').value = now
cls.worksheet.cell(coordinate='C14').value = 'This is a test' # 1
cls.worksheet.cell(coordinate='D9').value = '31.31415' # 3
st = Style(number_format=NumberFormat(NumberFormat.FORMAT_NUMBER_00),
protection=Protection(locked=True)) # 4
cls.worksheet.cell(coordinate='D9').style = st
st2 = Style(protection=Protection(hidden=True)) # 5
cls.worksheet.cell(coordinate='E1').style = st2
cls.writer = StyleWriter(cls.workbook)
def test_write_number_formats():
wb = DummyWorkbook()
from openpyxl.xml.functions import Element
from openpyxl.styles import NumberFormat, Style
wb.shared_styles = [Style(), Style(number_format=NumberFormat('YYYY'))]
writer = StyleWriter(wb)
writer._write_number_format(writer._root, 0, "YYYY")
xml = get_xml(writer._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmt formatCode="YYYY" numFmtId="0"></numFmt>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def _convert_to_number_format(cls, number_format_dict):
"""
Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
initializer.
Parameters
----------
number_format_dict : dict
A dict with zero or more of the following keys.
'format_code' : str
Returns
-------
number_format : str
"""
try:
# >= 2.0.0 < 2.1.0
from openpyxl.styles import NumberFormat
return NumberFormat(**number_format_dict)
except:
# >= 2.1.0
return number_format_dict['format_code']
def number_format(self, format_code):
"""Set a new formatting code for numeric values"""
self.style = self.style.copy(number_format=NumberFormat(
format_code=format_code))
def test_builtin_format(NumberFormat):
fmt = NumberFormat(format_code='0.00')
assert fmt.builtin_format_code(2) == fmt.format_code
EXCEL_NUMBER_FORMAT = '0.00'
TITLE_STYLE = Style(font=Font(size=24, bold=True))
HEADER_STYLE = Style(
font=Font(bold=True),
fill=PatternFill(
fill_type=fills.FILL_SOLID,
start_color=Color(rgb=Color.header)
)
)
BOLD_CELL = Style(
font=Font(bold=True)
)
NUMBER_CELL = Style(
number_format=NumberFormat(format_code=EXCEL_NUMBER_FORMAT)
)
FOOTER_CELL = Style(
font=Font(bold=True),
fill=PatternFill(
fill_type=fills.FILL_SOLID,
start_color=Color(rgb=Color.footer)
),
number_format=NumberFormat(format_code=EXCEL_NUMBER_FORMAT)
)
LARGE_FOOTER_CELL = Style(
font=Font(bold=True, size=16),
fill=PatternFill(
fill_type=fills.FILL_SOLID,
start_color=Color(rgb=Color.footer)
),
def test_change_existing_styles(datadir):
wb = load_workbook("complex-styles.xlsx")
ws = wb.get_active_sheet()
ws.column_dimensions['A'].width = 20
i_style = ws.get_style('I')
ws.set_style('I', i_style.copy(fill=PatternFill(fill_type='solid',
start_color=Color('FF442200')),
font=Font(color=Color('FF002244'))))
ws.cell('A2').style = ws.cell('A2').style.copy(font=Font(name='Times New Roman',
size=12,
bold=True,
italic=True))
ws.cell('A3').style = ws.cell('A3').style.copy(font=Font(name='Times New Roman',
size=14,
bold=False,
italic=True))
ws.cell('A4').style = ws.cell('A4').style.copy(font=Font(name='Times New Roman',
size=16,
bold=True,
italic=False))
ws.cell('A5').style = ws.cell('A5').style.copy(font=Font(color=Color('FF66FF66')))
ws.cell('A6').style = ws.cell('A6').style.copy(font=Font(color=Color(theme='1')))
ws.cell('A7').style = ws.cell('A7').style.copy(fill=PatternFill(fill_type='solid',
start_color=Color('FF330066')))
ws.cell('A8').style = ws.cell('A8').style.copy(fill=PatternFill(fill_type='solid',
start_color=Color(theme='2')))
ws.cell('A9').style = ws.cell('A9').style.copy(alignment=Alignment(horizontal='center'))
ws.cell('A10').style = ws.cell('A10').style.copy(alignment=Alignment(horizontal='left'))
ws.cell('A11').style = ws.cell('A11').style.copy(alignment=Alignment(horizontal='right'))
ws.cell('A12').style = ws.cell('A12').style.copy(alignment=Alignment(vertical='bottom'))
ws.cell('A13').style = ws.cell('A13').style.copy(alignment=Alignment(vertical='top'))
ws.cell('A14').style = ws.cell('A14').style.copy(alignment=Alignment(vertical='center'))
ws.cell('A15').style = ws.cell('A15').style.copy(number_format=NumberFormat('0.00%'))
ws.cell('A16').style = ws.cell('A16').style.copy(number_format=NumberFormat('0.00'))
ws.cell('A17').style = ws.cell('A17').style.copy(number_format=NumberFormat('mm-dd-yy'))
ws.unmerge_cells('A18:B18')
ws.cell('A19').style = ws.cell('A19').style.copy(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600')),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600')),
left=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600')),
right=Side(border_style=borders.BORDER_THIN,
color=Color('FF006600'))))
ws.cell('A21').style = ws.cell('A21').style.copy(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7)),
left=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7)),
right=Side(border_style=borders.BORDER_THIN,
color=Color(theme=7))))
ws.cell('A23').style = ws.cell('A23').style.copy(border=Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(theme=6))),
fill=PatternFill(fill_type='solid',
start_color=Color('FFCCCCFF')))
ws.unmerge_cells('A23:B24')
ws.cell('A25').style = ws.cell('A25').style.copy(alignment=Alignment(wrap_text=False))
ws.cell('A26').style = ws.cell('A26').style.copy(alignment=Alignment(shrink_to_fit=False))
saved_wb = save_virtual_workbook(wb)
new_wb = load_workbook(BytesIO(saved_wb))
ws = new_wb.get_active_sheet()
assert ws.column_dimensions['A'].width == 20.0
style = partial(ws.get_style)
assert ws.get_style('I').fill.start_color.value == 'FF442200'
assert ws.get_style('I').font.color.value == 'FF002244'
assert style('A2').font.name == 'Times New Roman'
assert style('A2').font.size == 12
assert style('A2').font.bold
assert style('A2').font.italic
assert style('A3').font.name == 'Times New Roman'
assert style('A3').font.size == 14
assert not style('A3').font.bold
assert style('A3').font.italic
assert style('A4').font.name == 'Times New Roman'
assert style('A4').font.size == 16
assert style('A4').font.bold
assert not style('A4').font.italic
assert style('A5').font.color.value == 'FF66FF66'
assert style('A6').font.color.value == 1
assert style('A7').fill.start_color.value == 'FF330066'
assert style('A8').fill.start_color.value == 2
assert style('A9').alignment.horizontal == 'center'
assert style('A10').alignment.horizontal == 'left'
assert style('A11').alignment.horizontal == 'right'
assert style('A12').alignment.vertical == 'bottom'
assert style('A13').alignment.vertical == 'top'
assert style('A14').alignment.vertical == 'center'
assert style('A15').number_format == '0.00%'
assert style('A16').number_format == '0.00'
assert style('A17').number_format == 'mm-dd-yy'
assert 'A18:B18' not in ws._merged_cells
assert not ws.cell('B18').merged
assert style('A19').border.top.color.value == 'FF006600'
assert style('A19').border.bottom.color.value == 'FF006600'
assert style('A19').border.left.color.value == 'FF006600'
assert style('A19').border.right.color.value == 'FF006600'
assert style('A21').border.top.color.value == 7
assert style('A21').border.bottom.color.value == 7
assert style('A21').border.left.color.value == 7
assert style('A21').border.right.color.value == 7
assert style('A23').fill.start_color.value == 'FFCCCCFF'
assert style('A23').border.top.color.value == 6
assert 'A23:B24' not in ws._merged_cells
assert not ws.cell('A24').merged
assert not ws.cell('B23').merged
assert not ws.cell('B24').merged
assert not style('A25').alignment.wrap_text
assert not style('A26').alignment.shrink_to_fit
# Verify that previously duplicate styles remain the same
assert ws.column_dimensions['C'].width == 31.1640625
assert style('C2').font.name == 'Arial'
assert style('C2').font.size == 10
assert not style('C2').font.bold
assert not style('C2').font.italic
assert style('C3').font.name == 'Arial'
assert style('C3').font.size == 12
assert style('C3').font.bold
assert not style('C3').font.italic
assert style('C4').font.name == 'Arial'
assert style('C4').font.size == 14
assert not style('C4').font.bold
assert style('C4').font.italic
assert style('C5').font.color.value == 'FF3300FF'
assert style('C6').font.color.value == 9
assert style('C7').fill.start_color.value == 'FFFFFF66'
assert style('C8').fill.start_color.value == 8
assert style('C9').alignment.horizontal == 'left'
assert style('C10').alignment.horizontal == 'right'
assert style('C11').alignment.horizontal == 'center'
assert style('C12').alignment.vertical == 'top'
assert style('C13').alignment.vertical == 'center'
assert style('C14').alignment.vertical == 'bottom'
assert style('C15').number_format == '0.00'
assert style('C16').number_format == 'mm-dd-yy'
assert style('C17').number_format == '0.00%'
assert 'C18:D18' in ws._merged_cells
assert ws.cell('D18').merged
assert style('C19').border.top.color.value == 'FF006600'
assert style('C19').border.bottom.color.value == 'FF006600'
assert style('C19').border.left.color.value == 'FF006600'
assert style('C19').border.right.color.value == 'FF006600'
assert style('C21').border.top.color.value == 7
assert style('C21').border.bottom.color.value == 7
assert style('C21').border.left.color.value == 7
assert style('C21').border.right.color.value == 7
assert style('C23').fill.start_color.value == 'FFCCCCFF'
assert style('C23').border.top.color.value == 6
assert 'C23:D24' in ws._merged_cells
assert ws.cell('C24').merged
assert ws.cell('D23').merged
assert ws.cell('D24').merged
assert style('C25').alignment.wrap_text
assert style('C26').alignment.shrink_to_fit
def parse_cell_xfs(self):
"""Read styles from the shared style table"""
cell_xfs = self.root.find('{%s}cellXfs' % SHEET_MAIN_NS)
styles_list = self.style_prop['list']
if cell_xfs is None: # can happen on bad OOXML writers (e.g. Gnumeric)
return
builtin_formats = NumberFormat._BUILTIN_FORMATS
cell_xfs_nodes = safe_iterator(cell_xfs, '{%s}xf' % SHEET_MAIN_NS)
for index, cell_xfs_node in enumerate(cell_xfs_nodes):
_style = {}
number_format_id = int(cell_xfs_node.get('numFmtId'))
if number_format_id < 164:
format_code = builtin_formats.get(number_format_id, 'General')
else:
fmt_code = self.custom_num_formats.get(number_format_id)
if fmt_code is not None:
format_code = fmt_code
else:
raise MissingNumberFormat('%s' % number_format_id)
_style['number_format'] = NumberFormat(format_code=format_code)
if bool(cell_xfs_node.get('applyAlignment')):
alignment = {}
al = cell_xfs_node.find('{%s}alignment' % SHEET_MAIN_NS)
if al is not None:
for key in ('horizontal', 'vertical', 'indent'):
_value = al.get(key)
if _value is not None:
alignment[key] = _value
alignment['wrap_text'] = bool(al.get('wrapText'))
alignment['shrink_to_fit'] = bool(al.get('shrinkToFit'))
text_rotation = al.get('textRotation')
if text_rotation is not None:
alignment['text_rotation'] = int(text_rotation)
# ignore justifyLastLine option when horizontal = distributed
_style['alignment'] = Alignment(**alignment)
if bool(cell_xfs_node.get('applyFont')):
_style['font'] = self.font_list[int(
cell_xfs_node.get('fontId'))].copy()
if bool(cell_xfs_node.get('applyFill')):
_style['fill'] = self.fill_list[int(
cell_xfs_node.get('fillId'))].copy()
if bool(cell_xfs_node.get('applyBorder')):
_style['border'] = self.border_list[int(
cell_xfs_node.get('borderId'))].copy()
if bool(cell_xfs_node.get('applyProtection')):
protection = {}
prot = cell_xfs_node.find('{%s}protection' % SHEET_MAIN_NS)
# Ignore if there are no protection sub-nodes
if prot is not None:
protection['locked'] = bool(prot.get('locked'))
protection['hidden'] = bool(prot.get('hidden'))
_style['protection'] = Protection(**protection)
self.style_prop['table'][index] = styles_list.add(Style(**_style))
Color.LightCyan = "FFE0FFFF"
Color.LightCoral = "FFF08080"
Color.LightGreen = "FF90EE90"
Color.Crimson = "FFDC143C"
Color.header = "FFD9EDF7"
Color.footer = "FFFCF8E3"
EXCEL_NUMBER_FORMAT = '0.00'
TITLE_STYLE = Style(font=Font(size=24, bold=True))
HEADER_STYLE = Style(font=Font(bold=True),
fill=PatternFill(fill_type=fills.FILL_SOLID,
start_color=Color(rgb=Color.header)))
BOLD_CELL = Style(font=Font(bold=True))
NUMBER_CELL = Style(number_format=NumberFormat(
format_code=EXCEL_NUMBER_FORMAT))
FOOTER_CELL = Style(
font=Font(bold=True),
fill=PatternFill(fill_type=fills.FILL_SOLID,
start_color=Color(rgb=Color.footer)),
number_format=NumberFormat(format_code=EXCEL_NUMBER_FORMAT))
LARGE_FOOTER_CELL = Style(
font=Font(bold=True, size=16),
fill=PatternFill(fill_type=fills.FILL_SOLID,
start_color=Color(rgb=Color.footer)),
number_format=NumberFormat(format_code=EXCEL_NUMBER_FORMAT))
# A, B, C, ..., AA, AB, AC, ..., ZZ
ASCII_UPPERCASE = list(ascii_uppercase) + list(
''.join(duple)
for duple in itertools.combinations_with_replacement(ascii_uppercase, 2))
class Axis(object):
POSITION_BOTTOM = 'b'
POSITION_LEFT = 'l'
ORIENTATION_MIN_MAX = "minMax"
position = None
tick_label_position = None
crosses = None
auto = None
label_align = None
label_offset = None
cross_between = None
orientation = ORIENTATION_MIN_MAX
number_format = NumberFormat()
delete_axis = False
def __init__(self, auto_axis=True):
self.auto_axis = auto_axis
self.min = 0
self.max = 0
self.unit = None
self.title = ''
def _max_min(self):
"""
Calculate minimum and maximum for the axis adding some padding.
There are always a maximum of ten units for the length of the axis.
"""
value = length = self._max - self._min
sign = value / value
zoom = less_than_one(value) or 1
value = value * zoom
ab = abs(value)
value = math.ceil(ab * 1.1) * sign
# calculate tick
l = math.log10(abs(value))
exp = int(l)
mant = l - exp
unit = math.ceil(math.ceil(10**mant) * 10**(exp - 1))
# recalculate max
value = math.ceil(value / unit) * unit
unit = unit / zoom
if value / unit > 9:
# no more that 10 ticks
unit *= 2
self.unit = unit
scale = value / length
mini = math.floor(self._min * scale) / zoom
maxi = math.ceil(self._max * scale) / zoom
return mini, maxi
@property
def min(self):
if self.auto_axis:
return self._max_min()[0]
return self._min
@min.setter
def min(self, value):
self._min = value
@property
def max(self):
if self.auto_axis:
return self._max_min()[1]
return self._max
@max.setter
def max(self, value):
self._max = value
@property
def unit(self):
if self.auto_axis:
self._max_min()
return self._unit
@unit.setter
def unit(self, value):
self._unit = value
from openpyxl.worksheet import Worksheet
from openpyxl.xml.functions import (XMLGenerator, start_tag, end_tag, tag)
from openpyxl.date_time import (to_excel, timedelta_to_days, time_to_days)
from openpyxl.xml.constants import MAX_COLUMN, MAX_ROW, PACKAGE_XL
from openpyxl.units import NUMERIC_TYPES
from openpyxl.exceptions import WorkbookAlreadySaved
from openpyxl.writer.excel import ExcelWriter
from openpyxl.writer.strings import write_string_table
from openpyxl.writer.styles import StyleWriter
from openpyxl.styles import Style, NumberFormat, DEFAULTS
from openpyxl.xml.constants import (ARC_SHARED_STRINGS, PACKAGE_WORKSHEETS)
ITERABLES = (list, tuple)
DATETIME_STYLE = Style(number_format=NumberFormat(
format_code=NumberFormat.FORMAT_DATE_YYYYMMDD2))
STYLES = {
'datetime': {
'type': Cell.TYPE_NUMERIC,
'style': DATETIME_STYLE
},
'string': {
'type': Cell.TYPE_STRING,
'style': DEFAULTS
},
'numeric': {
'type': Cell.TYPE_NUMERIC,
'style': DEFAULTS
},
'formula': {
'type': Cell.TYPE_FORMULA,