def parse_font(self, font_node):
"""Read individual font"""
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
bold = font_node.find('{%s}b' % SHEET_MAIN_NS)
if bold is not None:
font.bold = bool(bold.get('val', True))
italic = font_node.find('{%s}i' % SHEET_MAIN_NS)
if italic is not None:
font.italic = bool(italic.get('val', True))
underline = font_node.find('{%s}u' % SHEET_MAIN_NS)
if underline is not None:
font.underline = underline.get('val', 'single')
strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS)
if strikethrough is not None:
font.strikethrough = True
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
font.color.index = self._get_relevant_color(color)
return font
def parse_font(self, font_node):
"""Read individual font"""
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
bold = font_node.find('{%s}b' % SHEET_MAIN_NS)
if bold is not None:
font.bold = bool(bold.get('val', True))
italic = font_node.find('{%s}i' % SHEET_MAIN_NS)
if italic is not None:
font.italic = bool(italic.get('val', True))
underline = font_node.find('{%s}u' % SHEET_MAIN_NS)
if underline is not None:
font.underline = underline.get('val', 'single')
strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS)
if strikethrough is not None:
font.strikethrough = True
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
font.color.index = self._get_relevant_color(color)
return font
def parse_fonts(root, color_index):
"""Read in the fonts"""
font_list = []
fonts = root.find('{%s}fonts' % SHEET_MAIN_NS)
if fonts is not None:
font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS)
for font_node in font_nodes:
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False
font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False
if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)):
underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val')
font.underline = underline if underline else 'single'
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index):
font.color.index = color_index[int(color.get('indexed'))]
elif color.get('theme') is not None:
if color.get('tint') is not None:
font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
font.color.index = color.get('rgb')
font_list.append(font)
return font_list
def styled_sheet():
from openpyxl import Workbook
from openpyxl.styles import Font, Style, Fill, Color, colors
wb = Workbook()
ws = wb.active
ws.title = 'Test 1'
red_fill = Fill()
red_fill.fill_type = 'solid'
red_fill.start_color = Color(Color.RED),
red_fill.end_color = Color(Color.RED)
empty_fill = Fill()
styles = []
# pregenerate relevant styles
for row in range(ROWS):
_row = []
for col in range(COLUMNS):
cell = ws.cell(row=row + 1, column=col + 1)
cell.value = 1
font = Font()
fill = Fill()
if formatData[row][col] & BOLD:
font.bold = True
if formatData[row][col] & ITALIC:
font.italic = True
if formatData[row][col] & UNDERLINE:
font.underline = 'single'
if formatData[row][col] & RED_BG:
fill = red_fill
style = Style()
style.font = font
style.fill = fill
ws._styles[cell.address] = style
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add(
'A1:A2',
FormulaRule(formula="[A1=1]",
font=whiteFont,
border=blueBorder,
fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
