def test_serialise(self, ColorScale, FormatObject):
fo1 = FormatObject(type="min", val="0")
fo2 = FormatObject(type="percent", val="50")
fo3 = FormatObject(type="max", val="0")
col1 = Color(rgb="FFFF0000")
col2 = Color(rgb="FFFFFF00")
col3 = Color(rgb="FF00B050")
cs = ColorScale(cfvo=[fo1, fo2, fo3], color=[col1, col2, col3])
xml = tostring(cs.to_tree())
expected = """
<colorScale>
<cfvo type="min" val="0"/>
<cfvo type="percent" val="50"/>
<cfvo type="max" val="0"/>
<color rgb="FFFF0000"/>
<color rgb="FFFFFF00"/>
<color rgb="FF00B050"/>
</colorScale>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def migrate_sheet(newsheet, outputwb, migratedsheet_name):
col_map = map_headings(newsheet, heading_row=3)
if NEWSHEET_NUMID in col_map:
output_sheet = outputwb.create_sheet(migratedsheet_name)
migrate_heading(newsheet.wsheet, output_sheet)
fill_row(output_sheet, 3, ['logo', 'numid'])
row = 4
while fill_row(output_sheet, row, migrate_row(newsheet, row, col_map)):
rgb, tint = row_color(newsheet, col_map, row)
if rgb is not None:
fill_color = Color(rgb=rgb, tint=tint)
output_sheet.cell(row, OUTPUT_START_COL).fill = \
PatternFill(start_color=fill_color, end_color=fill_color, fill_type='solid')
row += 1
def fill_row(self,
ws,
cell_range,
color="DDD9C4",
set_border=True,
alignment='center'):
rows = ws[cell_range]
for row in rows:
for cell in row:
cell.style = Style(fill=PatternFill(patternType='solid',
fill_type='solid',
fgColor=Color(color)))
cell.border = self.border if set_border == True else print(".")
cell.alignment = Alignment(horizontal=alignment)
def test_write_pattern_fill():
fill = PatternFill(fill_type='solid', start_color=Color(colors.DARKYELLOW))
writer = StyleWriter(DummyWorkbook())
writer._write_pattern_fill(writer._root, fill)
xml = get_xml(writer._root)
expected = """<?xml version="1.0" ?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<patternFill patternType="solid">
<fgColor rgb="00808000" />
</patternFill>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_conditional_font(self):
"""Test to verify font style written correctly."""
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
ws.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
from openpyxl.writer.worksheet import write_conditional_formatting
# First, verify conditional formatting xml
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def make_excel_file(title, level_dict):
if os.path.isfile(title):
wb = load_workbook(title)
ws = wb.active
else:
wb = Workbook()
ws = wb.active
header_list = [
'카메라 ID', '신뢰도 min', '신뢰도 max', 'O(맞음)', 'X(틀림)', 'q(오검지)',
'XX(인식불가)', '합계'
]
for i in range(len(header_list)):
ws.cell(row=1,
column=i + 1).fill = PatternFill(patternType='solid',
fgColor=Color('FFC000'))
ws.cell(row=1, column=i + 1).value = header_list[i]
key_cnt = 0
for key, val_dict in level_dict.items():
key_start = key_cnt + 2
for f in val_dict.values():
list_for_excel = []
list_for_excel.append(key)
list_for_excel.extend(f)
key_cnt += 1
ws.append(list_for_excel)
key_end = key_cnt + 1
#merge camera ID_cells
merge_start = 'A' + str(key_start)
merge_end = 'A' + str(key_end)
merge_range = ':'.join([merge_start, merge_end])
ws.merge_cells(merge_range)
ws[merge_start] = key
ws[merge_start].font = Font(size=13, bold=True)
ws[merge_start].alignment = Alignment(horizontal='center',
vertical='center')
#merge confidence cells
for i in range(key_start, key_end + 1):
merge_start = 'B' + str(i)
merge_end = 'C' + str(i)
start_val = ws.cell(i, 2).value
end_val = ws.cell(i, 3).value
merge_range = ':'.join([merge_start, merge_end])
ws.merge_cells(merge_range)
ws[merge_start] = '-'.join([str(start_val), str(end_val)])
ws[merge_start].alignment = Alignment(horizontal='center',
vertical='center')
wb.save(title)
def conditional_formatting(self, ws, conditional_format_dict):
cond_list = list(conditional_format_dict.items())
self.worksheet = self.wb[ws]
for col in self.worksheet.columns:
for cell in col:
try: # Necessary to avoid error on empty cells
for condition in cond_list:
if str(cell.value) == condition[0]:
cell.style = Style(
fill=PatternFill(patternType='solid',
fill_type='solid',
fgColor=Color(condition[1])))
cell.border = self.border
except:
pass
def format_column_titles(self):
title_font = Font(size=12, bold=True)
alignment = Alignment(horizontal='center', vertical='center')
fill_color = PatternFill(patternType='solid',
fgColor=Color(rgb="BBBBBB"))
border = Border(left=Side('thin'),
right=Side('thin'),
top=Side('thin'),
bottom=Side('thin'))
for column in range(1, 9):
self.active_sheet.cell(2, column).font = title_font
self.active_sheet.cell(2, column).alignment = alignment
self.active_sheet.cell(2, column).fill = fill_color
self.active_sheet.cell(2, column).border = border
def test_write(self, ChartsheetProperties):
from openpyxl.styles import Color
chartsheetPr = ChartsheetProperties()
chartsheetPr.codeName = "Chart Openpyxl"
tabColor = Color(rgb="FFFFFFF4")
chartsheetPr.tabColor = tabColor
expected = """
<sheetPr codeName="Chart Openpyxl">
<tabColor rgb="FFFFFFF4" />
</sheetPr>
"""
xml = tostring(chartsheetPr.to_tree())
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_parse(DifferentialStyle, datadir):
datadir.chdir()
with open("dxf_style.xml") as content:
src = content.read()
xml = fromstring(src)
formats = []
for node in xml.findall("{%s}dxfs/{%s}dxf" %
(SHEET_MAIN_NS, SHEET_MAIN_NS)):
formats.append(DifferentialStyle.from_tree(node))
assert len(formats) == 164
cond = formats[1]
assert cond.font == Font(underline="double",
b=False,
color=Color(auto=1),
strikethrough=True,
italic=True)
assert cond.fill == PatternFill(end_color='FFFFC7CE')
assert cond.border == Border(
left=Side(),
right=Side(),
top=Side(style="thin", color=Color(theme=4)),
bottom=Side(style="thin", color=Color(theme=4)),
diagonal=None,
)
def open_py_excel_pass(sheet_index, index):
wb = openpyxl.load_workbook("C:\Users\sagarkul\Desktop\TestCase.xlsx")
ws = wb.worksheets[sheet_index]
for i in range(2, 10):
if ws.cell(row=i, column=index + 1).value == "Pass":
continue
elif ws.cell(row=i, column=index + 1).value == "Fail":
continue
else:
ws.cell(row=i, column=index + 1).value = "Pass"
ws.cell(row=i, column=index + 1).style = Style(
fill=PatternFill(patternType='solid', fgColor=Color('009900')))
ws.cell(row=1, column=index + 1).value = index
break
wb.save("C:\Users\sagarkul\Desktop\TestCase.xlsx")
def generate_formatted_data(data_path, problems):
wb = load_workbook(data_path)
ws = wb.active
for problem in problems:
cell = ws[problem["cell"]]
if problem["level"] == "error":
cell.fill = PatternFill(patternType="lightUp",
fgColor=Color(indexed=10),
fill_type="solid")
else:
cell.fill = PatternFill(patternType="lightUp",
fgColor=Color(indexed=52),
fill_type="solid")
if cell.comment:
comment = Comment(
cell.comment.text + "\n" + problem["message"],
author="tetramer_validator",
)
cell.comment = comment
else:
cell.comment = Comment(problem["message"],
author="tetramer-validator")
wb.save(data_path)
def _coloring_enrollment_state(ws, row_number, exam_enroll):
"""
Coloring of the non-editable columns
"""
enrollment_state_color = get_line_color(exam_enroll)
if enrollment_state_color:
pattern_fill_enrollment_state = PatternFill(
patternType='solid',
fgColor=Color(enrollment_state_color.lstrip("#")))
style_enrollment_state = Style(fill=pattern_fill_enrollment_state)
column_number = 1
while column_number < 12:
ws.cell(row=row_number,
column=column_number).style = style_enrollment_state
column_number += 1
def test_parse_dxfs():
reference_file = os.path.join(DATADIR, 'reader',
'conditional-formatting.xlsx')
wb = load_workbook(reference_file)
assert isinstance(wb, Workbook)
archive = ZipFile(reference_file, 'r', ZIP_DEFLATED)
read_xml = archive.read(ARC_STYLE)
# Verify length
assert '<dxfs count="164">' in str(read_xml)
assert len(wb.style_properties['dxf_list']) == 164
# Verify first dxf style
reference_file = os.path.join(DATADIR, 'writer', 'expected',
'dxf_style.xml')
with open(reference_file) as expected:
diff = compare_xml(read_xml, expected.read())
assert diff is None, diff
cond_styles = wb.style_properties['dxf_list'][0]
assert cond_styles['font'].color == Color('FF9C0006')
assert not cond_styles['font'].bold
assert not cond_styles['font'].italic
f = Fill()
f.end_color = Color('FFFFC7CE')
assert cond_styles['fill'] == f
# Verify that the dxf styles stay the same when they're written and read back in.
w = StyleWriter(wb)
w._write_dxfs()
write_xml = get_xml(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop['dxf_list']) == len(
wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop['dxf_list']):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def __coloring_non_editable(ws, row_number, score, justification):
"""
Coloring of the non-editable columns
"""
pattern_fill_grey = PatternFill(patternType='solid', fgColor=Color('C1C1C1'))
style_no_modification = Style(fill=pattern_fill_grey)
column_number = 1
while column_number < 12:
if column_number < 9 or column_number > 10:
ws.cell(row=row_number, column=column_number).style = style_no_modification
else:
if not(score is None and justification is None):
ws.cell(row=row_number, column=9).style = style_no_modification
ws.cell(row=row_number, column=10).style = style_no_modification
column_number += 1
def test_parse(DifferentialStyle, datadir):
datadir.chdir()
with open("dxf_style.xml") as content:
src = content.read()
xml = fromstring(src)
formats = []
for node in xml.findall("{%s}dxfs/{%s}dxf" %
(SHEET_MAIN_NS, SHEET_MAIN_NS)):
formats.append(DifferentialStyle.from_tree(node))
assert len(formats) == 164
cond = formats[1]
assert cond.font == Font(underline="double",
color=Color(auto=1),
strikethrough=True,
italic=True)
assert cond.fill == PatternFill(end_color='FFFFC7CE')
def __writeExcelLine(self, fieldList):
line = []
cellToChangeDict = {}
for i, field in enumerate(fieldList):
if i < 26:
column = chr(ord('A') + i)
else:
column = 'A' + chr(ord('A') + i % 26)
if isinstance(field, ExcelCell):
cellToChangeDict['%s%d' %
(column,
self.__lineNb)] = field.color, field.bgColor,
field.style, field.alignment, field.otherParamDict
field = field.value
line.append(field)
self.__fh.append(line)
for cellKey, (color, bgColor, style, alignment, otherParamDict) in \
cellToChangeDict.iteritems():
if color or style:
paramDict = {}
if color:
paramDict['color'] = color
if style:
if style == 'italic':
paramDict['italic'] = True
elif style == 'bold':
paramDict['bold'] = True
if otherParamDict:
paramDict.update(otherParamDict)
try:
newFont = self.__fh[cellKey].font.copy(**paramDict)
except:
print paramDict, cellKey
print len(fieldList), fieldList
raise
self.__fh[cellKey].font = newFont
if bgColor:
self.__fh[cellKey].fill = PatternFill("solid",
fgColor=Color(bgColor))
if alignment or (self.__otherParamDict
and 'alignment' in self.__otherParamDict):
if not alignment:
alignment = self.__otherParamDict['alignment']
self.__fh[cellKey].alignment = Alignment(**alignment)
self.__lineNb += 1
def set_font_color(self, reference, font_color, selected_sheet, workbook):
col, row = Base._get_coordinates_from_reference(reference)
book = self._get_workbook(workbook)
sheet = self._select_sheet(book, selected_sheet)
fill_color = self._get_color(font_color)
if fill_color is None:
try:
fill_color = Color(font_color)
except ValueError as e:
print("color argument must be aRGB hex value or color name!")
font = sheet.cell(row=row, column=col).font
sheet.cell(row=row, column=col).font = Font(size=font.size,
color=fill_color,
bold=font.b,
italic=font.i,
underline=font.u)
book.wb_book.save(book.wb_path)
def create_style(self):
side = Side(border_style=self.border_type, color=utils.colors.black)
border = Border(left=side, right=side, top=side, bottom=side)
return Style(font=Font(name=self.font,
size=self.font_size,
color=Color(self.font_color),
bold=self.bold,
underline=self.underline),
fill=PatternFill(patternType='solid',
fgColor=self.bg_color),
alignment=Alignment(horizontal=self.horizontal_alignment,
vertical=self.vertical_alignment,
wrap_text=True,
shrink_to_fit=True,
indent=0),
border=border,
number_format=self.number_format,
protection=Protection(locked=self.protection))
def format_data(writer, current_date):
workbook = writer.book
sheet = workbook[current_date]
#sheet = workbook.active
#Fill ("NEW") rule
blue_bg = PatternFill(bgColor=Color(indexed=27))
diff_style = DifferentialStyle(fill=blue_bg)
rule = Rule(type="expression", dxf=diff_style)
rule.formula = ['$D2 = "NEW"']
sheet.conditional_formatting.add("D2:E201", rule)
#Arrow rule
rule_arrow = IconSetRule('3Arrows', 'num', [-200, 0, 1])
sheet.conditional_formatting.add("D2:E201", rule_arrow)
write_further_info(sheet)
workbook.save(FILENAME)
def test_xfs_fills(self):
cell = self.worksheet.cell('A1')
cell.fill = fill = PatternFill(fill_type='solid',
start_color=Color(colors.DARKYELLOW))
_ = cell.style_id # update workbook styles
w = StyleWriter(self.workbook)
w._write_cell_styles()
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def set_border(self, ws, cell_range, color="DDD9C4"):
tmp = cell_range.split(":")
a = tmp[0][1:]
b = tmp[1][0] + a
header = tmp[0] + ":" + b
rows = ws[cell_range]
for row in rows:
for cell in row:
cell.border = self.border
head = ws[header]
for row in head:
for cell in row:
cell.style = Style(fill=PatternFill(patternType='solid',
fill_type='solid',
fgColor=Color(color)))
cell.border = self.border
cell.alignment = Alignment(horizontal='center')
def add_data(self,
json_objects,
field_names,
date_from=None,
date_to=None):
if not self.touched:
self.touched = True
else:
self.next_sheet()
self.write_header(date_from, date_to, 1)
current_row = 3
"""
adds data to the current wb sheet
"""
ws = self.wb.active
for i, field_name in enumerate(field_names):
cell = ws.cell(row=current_row, column=i + 1)
cell.value = field_name
cell.fill = PatternFill(fgColor=Color('E7E6E6'),
patternType='solid')
cell.font = Font(bold=True, name='Arial')
cell.border = thin_border
current_row += 1
for row, (json_object, _) in enumerate(json_objects):
for col, field in enumerate(field_names):
tmp = json_object['result'].get(field)
if tmp:
if isinstance(tmp, list):
tmp = unicode(','.join(tmp))
else:
tmp = unicode(tmp)
cell = ws.cell(row=current_row + row, column=col + 1)
cell.value = self.cast_value(tmp)
cell.border = thin_border
if current_row + row >= MAX_ROWS:
break
self.set_filters()
self.set_width()
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_xfs_borders(self):
cell = self.worksheet.cell('A1')
cell.border = Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)))
_ = cell.style_id # update workbook styles
w = StyleWriter(self.workbook)
w._write_cell_styles()
xml = tostring(w._root)
expected = """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<cellXfs count="2">
<xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
<xf borderId="1" fillId="0" fontId="0" numFmtId="0" xfId="0"/>
</cellXfs>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def image_to_xlsx(image_file, xlsx_file):
img = Image.open(image_file)
pixel = img.load()
wb = Workbook()
ws = wb.active
for i in range(img.width):
for j in range(img.height):
ws.cell(row=j + 1,
column=i + 1).fill = PatternFill(fgColor=Color(
'{0:02x}{1:02x}{2:02x}'.format(pixel[i, j][0],
pixel[i, j][1],
pixel[i, j][2])),
fill_type='solid')
for c in range(ws.max_column):
ws.column_dimensions[get_column_letter(c + 1)].width = float(1.89)
for r in range(ws.max_row):
ws.row_dimensions[r + 1].height = float(14.4)
wb.save(xlsx_file)
def setup_sheet(category):
sheet = file.create_sheet(category)
# header 입력
sheet['B1'].value = '상품명'
sheet['C1'].value = '개수'
sheet['D1'].value = '재고'
# header 스타일 지정
font = Font(size=12, bold=True)
align_center = Alignment(horizontal='center', vertical='center')
fill_lightgray = PatternFill(patternType='solid', fgColor=Color('D5D5D5'))
border_thin = Border(left=Side(style='thin'),
right=Side(style='thin'),
bottom=Side(style='thin'))
for index in range(2, 5):
sheet.cell(row=1, column=index).font = font
sheet.cell(row=1, column=index).alignment = align_center
sheet.cell(row=1, column=index).fill = fill_lightgray
sheet.cell(row=1, column=index).border = border_thin
def make_excel_data(self):
count = 2
cell_proposed = {
'alignment': Alignment(horizontal='right'),
'font': Font(bold=True),
'fill': PatternFill(patternType='solid', fgColor=Color('90909000'))
}
for each_boiler_blowdown in self.boiler_blowdown:
for each_column in self.data_column_existing:
cell_val = '%s%s' % (each_column, count)
column_val = self.data_column_existing[each_column]
self.ws[cell_val] = each_boiler_blowdown.get(column_val)
count += 1
for each_column in self.data_column_proposed:
cell_val = '%s%s' % (each_column, count)
column_val = self.data_column_proposed[each_column]
self.ws[cell_val] = each_boiler_blowdown.get(column_val)
self.ws['A%s' % (count, )] = "Proposed Blowdown"
self.ws['A%s' % (count, )].style = Style(**cell_proposed)
self.ws.merge_cells('A%s:B%s' % (count, count))
count += 1
def compare(s1, s2, w1n,w2n):
print "hello nikhil"
pass
rowrange = max(s1.max_row, s2.max_row)
colrange = max(s1.max_column, s2.max_column)
cfill = Style(fill=PatternFill(patternType='solid', fgColor=Color('00ff00')))
for i in range(1,rowrange+1):
for j in range(1,colrange+1):
a = s1.cell(row=i,column=j).value
b = s2.cell(row=i,column=j).value
nt = None
dt = datetime.datetime.today()
tlist = [type(int()), type(float()),type(long()), type(nt)]
numlist = [type(int()), type(float()),type(long())]
if type(a) not in numlist and type(b) not in numlist:
a = str(unicode(a))
b = str(unicode(b))
if a != b:
#s2.cell(row=i,column=j).style = cfill
if type(a) in numlist and type(b) == type(nt):
# print "hi" + str(a)
comtxt = str(w1n)+": " + str(a)+ ". diff: "+ str(a)
elif type(b) in numlist and type(a) == type(nt):
# print "hi" + str(b)
comtxt = str(w1n)+": " + str(a)+ ". diff: "+ str(b)
elif type(a) in numlist and type(b) in numlist:
comtxt = str(w1n)+": " + str(a)+ ". diff: "+ str(b-a)
elif type(a) == type(str()) and type(b) == type(str()):
comtxt = str(w1n)+": " + str(a)+ "."
elif type(a) != type(b):
comtxt = str(w1n)+": " + str(a)+ ". \nIncompatible data"
comment = Comment(comtxt, w2n)
s2.cell(row=i,column=j).comment = comment
def __init__(self, filename):
self.wb = Workbook()
self.ws = self.wb.active
self.filename = filename
self.column_map = {}
self.tier_1_header_row = 1
self.tier_2_header_row = self.tier_1_header_row + 1
self.current_row = self.tier_2_header_row + 1
self.next_column = 1
self.enumeration_properties = []
self.fill = PatternFill(
patternType='solid',
fill_type='solid',
fgColor=Color('EBF1DE') # RGB: 235, 241, 222
)
self.thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
self.thin_border_left = Border(
left=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
self.thin_border_right = Border(
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
self.thin_border_center = Border(
top=Side(style='thin'),
bottom=Side(style='thin')
)
