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 test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=borders,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {
'font': font,
'border': borders,
'fill': fill
}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
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 _output_excel():
query_text = request.args.get('query_text')
input_sort = request.args.get('input_sort')
results = _get_data_from_db(query_text, input_sort)
if results=='fetch_db_error':
print('Fetching database fails. Please check the log...')
return ''
# *** Output to a Excel file
from openpyxl import Workbook
from openpyxl.styles import Font, Color
wb = Workbook()
# grab the active worksheet
ws = wb.active
ws.title = 'Yahoo Buy Search Results' # 設置worksheet的標題
# write header
excelHeader = ['category', 'item_title', 'item_price', 'item_info', 'staying_period']
ws.append(excelHeader)
ws.append(['-----', '-----', '-----', '-----', '-----'])
ft = Font()
ft.underline = 'single' # add single underline
ft.color = Color(rgb='000000FF') # add blue color
for one_result in results:
item_info_list = ['‧' + each_line for each_line in one_result[3].split(';;;')]
control_characters = dict.fromkeys(range(32))
remove_control = lambda x: x.translate(control_characters)
ws.append([remove_control(one_result[0]), remove_control(one_result[1]), remove_control(one_result[2]), remove_control("\r\n".join(item_info_list)), one_result[5]])
ws['A' + str(ws._current_row)] = '=HYPERLINK("{}", "{}")'.format(one_result[4], one_result[0])
ws['A' + str(ws._current_row)].font = ft
import datetime
filename = 'yahoo_buy_search_result_' + datetime.datetime.now().strftime("%Y-%m-%d") + '.xlsx'
wb.save('static/' + filename)
# ___ Output to a Excel file
from flask import send_from_directory
return send_from_directory('static', filename)
def test_conditional_formatting_addDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = cf.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = cf.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
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
