def format_cell(self,cell,type = 'normal'): # my_font = Font(size = 8) my_font = Font(name='Calibri', size=8, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000' # color='FFBB00' ) if type == "error": my_font.color = 'F60' my_font.size = 11 cell.font = my_font
def format_cell(self, cell, type='normal'):
# my_font = Font(size = 8)
my_font = Font(name='Calibri',
size=8,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'
# color='FFBB00'
)
if type == "error":
my_font.color = 'F60'
my_font.size = 11
cell.font = my_font
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 xls_style_to_xlsx(self, xf_ndx):
"""Convert an xls xf_ndx into a 6-tuple of styles for xlsx"""
font = Font()
fill = PatternFill()
border = Border()
alignment = Alignment()
number_format = 'General'
protection = Protection(locked=False, hidden=False)
if xf_ndx < len(self.book.xf_list):
xf = self.book.xf_list[xf_ndx]
xls_font = self.book.font_list[xf.font_index] # Font object
font.b = xls_font.bold
font.i = xls_font.italic
if xls_font.character_set:
font.charset = xls_font.character_set
font.color = self.xls_color_to_xlsx(xls_font.colour_index)
escapement = xls_font.escapement # 1=Superscript, 2=Subscript
family = xls_font.family # FIXME: 0=Any, 1=Roman, 2=Sans, 3=monospace, 4=Script, 5=Old English/Franktur
font.name = xls_font.name
font.sz = self.xls_height_to_xlsx(xls_font.height) # A twip = 1/20 of a pt
if xls_font.struck_out:
font.strike = xls_font.struck_out
if xls_font.underline_type:
font.u = ('single', 'double')[(xls_font.underline_type&3)-1]
xls_format = self.book.format_map[xf.format_key] # Format object
number_format = xls_format.format_str
if False: # xlrd says all cells are locked even if the sheet isn't protected!
protection.locked = xf.protection.cell_locked
protection.hidden = xf.protection.formula_hidden
fill_patterns = {0x00:'none', 0x01:'solid', 0x02:'mediumGray', 0x03:'darkGray', 0x04:'lightGray',
0x05:'darkHorizontal', 0x06:'darkVertical', 0x07:'darkDown', 0x08:'darkUp', 0x09:'darkGrid',
0x0A:'darkTrellis', 0x0B:'lightHorizontal', 0x0C:'lightVertical', 0x0D:'lightDown', 0x0E:'lightUp',
0x0F:'lightGrid', 0x10:'lightTrellis', 0x11:'gray125', 0x12:'gray0625'
}
fill_pattern = xf.background.fill_pattern
fill_background_color = self.xls_color_to_xlsx(xf.background.background_colour_index)
fill_pattern_color = self.xls_color_to_xlsx(xf.background.pattern_colour_index)
fill.patternType = fill_patterns.get(fill_pattern, 'none')
fill.bgColor = fill_background_color
fill.fgColor = fill_pattern_color
horizontal = {0:'general', 1:'left', 2:'center', 3:'right', 4:'fill', 5:'justify', 6:'centerContinuous', 7:'distributed'}
vertical = {0:'top', 1:'center', 2:'bottom', 3:'justify', 4:'distributed'}
hor_align = horizontal.get(xf.alignment.hor_align, None)
if hor_align:
alignment.horizontal = hor_align
vert_align = vertical.get(xf.alignment.vert_align, None)
if vert_align:
alignment.vertical = vert_align
alignment.textRotation = xf.alignment.rotation
alignment.wrap_text = xf.alignment.text_wrapped
alignment.indent = xf.alignment.indent_level
alignment.shrink_to_fit = xf.alignment.shrink_to_fit
border_styles = {0: None, 1:'thin', 2:'medium', 3:'dashed', 4:'dotted',
5:'thick', 6:'double', 7:'hair', 8:'mediumDashed', 9:'dashDot',
10:'mediumDashDot', 11:'dashDotDot', 12:'mediumDashDotDot',
13:'slantDashDot',}
xls_border = xf.border
top = Side(style=border_styles.get(xls_border.top_line_style), color=self.xls_color_to_xlsx(xls_border.top_colour_index))
bottom = Side(style=border_styles.get(xls_border.bottom_line_style), color=self.xls_color_to_xlsx(xls_border.bottom_colour_index))
left = Side(style=border_styles.get(xls_border.left_line_style), color=self.xls_color_to_xlsx(xls_border.left_colour_index))
right = Side(style=border_styles.get(xls_border.right_line_style), color=self.xls_color_to_xlsx(xls_border.right_colour_index))
diag = Side(style=border_styles.get(xls_border.diag_line_style), color=self.xls_color_to_xlsx(xls_border.diag_colour_index))
border.top = top
border.bottom = bottom
border.left = left
border.right = right
border.diagonal = diag
border.diagonalDown = xls_border.diag_down
border.diagonalUp = xls_border.diag_up
return (font, fill, border, alignment, number_format, protection)
