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 parse_dxfs(root, color_index):
"""Read in the dxfs effects - used by conditional formatting."""
dxf_list = []
dxfs = root.find('{%s}dxfs' % SHEET_MAIN_NS)
if dxfs is not None:
nodes = dxfs.findall('{%s}dxf' % SHEET_MAIN_NS)
for dxf in nodes:
dxf_item = {}
font_node = dxf.find('{%s}font' % SHEET_MAIN_NS)
if font_node is not None:
dxf_item['font'] = {}
dxf_item['font']['bold'] = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False
dxf_item['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')
dxf_item['font']['underline'] = underline if underline else 'single'
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
dxf_item['font']['color'] = Color(Color.BLACK)
if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index):
dxf_item['font']['color'].index = color_index[int(color.get('indexed'))]
elif color.get('theme') is not None:
if color.get('tint') is not None:
dxf_item['font']['color'] .index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
dxf_item['font']['color'] .index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
dxf_item['font']['color'] .index = color.get('rgb')
fill_node = dxf.find('{%s}fill' % SHEET_MAIN_NS)
if fill_node is not None:
dxf_item['fill'] = parse_fills(dxf, color_index, True)
dxf_item['border'] = parse_borders(dxf, color_index, True)
dxf_list.append(dxf_item)
return dxf_list
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
font = Font(name='Arial',
size=12,
bold=True,
underline=Font.UNDERLINE_SINGLE)
border = Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(colors.BLACK)))
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=border,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf._save_styles(self.workbook)
assert len(self.workbook._differential_styles) == 2
ft1, ft2 = self.workbook._differential_styles
assert ft1.font == font
assert ft1.border == border
assert ft1.fill == fill
assert ft2.fill == fill
def test_notEqual(self):
cf = ConditionalFormatting()
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
cf.add(
'U10:U18',
CellIsRule(operator='notEqual',
formula=['U$7'],
stopIfTrue=True,
fill=redFill))
cf.add(
'V10:V18',
CellIsRule(operator='!=',
formula=['V$7'],
stopIfTrue=True,
fill=redFill))
cf.setDxfStyles(self.workbook)
rules = cf.cf_rules
assert 'U10:U18' in rules
assert len(cf.cf_rules['U10:U18']) == 1
assert rules['U10:U18'][0]['priority'] == 1
assert rules['U10:U18'][0]['type'] == 'cellIs'
assert rules['U10:U18'][0]['dxfId'] == 0
assert rules['U10:U18'][0]['operator'] == 'notEqual'
assert rules['U10:U18'][0]['formula'][0] == 'U$7'
assert rules['U10:U18'][0]['stopIfTrue'] == '1'
assert 'V10:V18' in rules
assert len(cf.cf_rules['V10:V18']) == 1
assert rules['V10:V18'][0]['priority'] == 2
assert rules['V10:V18'][0]['type'] == 'cellIs'
assert rules['V10:V18'][0]['dxfId'] == 1
assert rules['V10:V18'][0]['operator'] == 'notEqual'
assert rules['V10:V18'][0]['formula'][0] == 'V$7'
assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def styled_sheet():
from openpyxl import Workbook
from openpyxl.styles import Font, Style, PatternFill, Color, colors
wb = Workbook()
ws = wb.active
ws.title = 'Test 1'
red_fill = PatternFill(fill_type='solid', fgColor=Color(colors.RED), bgColor=Color(colors.RED))
empty_fill = PatternFill()
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 = {}
fill = PatternFill()
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
cell.style = Style(font=Font(**font), fill=fill)
def setCellColorByCharAndRow(self, stringColumn, row, R=255, G=255, B=255):
if (utils.column_index_from_string(stringColumn.strip()) < 0):
raise ColumnIsNotVaild("列 不能小于0")
if (row < 0):
raise ColumnIsNotVaild(str(row) + "不能小于0行")
if (R == 255 and G == 255 and B == 255):
fill = PatternFill(
patternType=fills.FILL_NONE,
fgColor=Color(rgb=self.rgbConvertToHex(R, G, B)))
else:
fill = PatternFill(
patternType=fills.FILL_SOLID,
fgColor=Color(rgb=self.rgbConvertToHex(R, G, B)))
stringColumn = str(stringColumn).upper().strip()
# print("hdsjafaksldf");
# print(self.mergeCellsColumnAndRow);
for i in self.mergeCellsCharAndIndex:
# print("stringcolumn "+str(stringColumn));
if stringColumn >= str(i[0]).upper() and stringColumn <= str(
i[2]).upper() and row >= i[1] and row <= i[3]:
for neiHang in range((i[1]), i[3] + 1):
mybegin = utils.column_index_from_string(i[0])
myend = utils.column_index_from_string(i[2])
for neiCol in range(mybegin, myend + 1):
self.sheet.cell(row=neiHang, column=neiCol).fill = fill
self.wb.save(self.path)
return
self.sheet.cell(str(stringColumn) + str(row)).fill = fill
self.wb.save(self.path)
def setCellColorByColumnAndRow(self, hang, lie, R=255, G=255, B=255):
if (lie < 0):
raise ColumnIsNotVaild("列 不能小于0")
if (hang < 0):
raise ColumnIsNotVaild(str(hang) + "不能小于0行")
if (R == 255 and G == 255 and B == 255):
fill = PatternFill(
patternType=fills.FILL_NONE,
fgColor=Color(rgb=self.rgbConvertToHex(R, G, B)))
else:
fill = PatternFill(
patternType=fills.FILL_SOLID,
fgColor=Color(rgb=self.rgbConvertToHex(R, G, B)))
for i in self.mergeCellsColumnAndRow:
if hang >= i[1] and hang <= i[3] and lie >= i[0] and lie <= i[2]:
for neiHang in range(i[1], i[3] + 1):
for neiCol in range(i[0], i[2] + 1):
self.sheet.cell(row=neiHang, column=neiCol).fill = fill
self.wb.save(self.path)
return
self.sheet.cell(row=hang, column=lie).fill = fill
self.wb.save(self.path)
def test_conditional_font(self):
"""Test to verify font style written correctly."""
class WS():
conditional_formatting = ConditionalFormatting()
worksheet = WS()
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
worksheet.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
worksheet.conditional_formatting.setDxfStyles(self.workbook)
# First, verify conditional formatting xml
cfs = write_conditional_formatting(worksheet)
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">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
# Second, verify conditional formatting dxf styles
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = tostring(w._root)
diff = compare_xml(
xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def test_conditional_font(worksheet_with_cf, write_conditional_formatting):
"""Test to verify font style written correctly."""
# Create cf rule
from openpyxl.styles import PatternFill, Font, Color
from openpyxl.formatting.rule import CellIsRule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType='solid')
whiteFont = Font(color=Color("FFFFFFFF"))
ws = worksheet_with_cf
ws.conditional_formatting.add('A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def write(self, first_row, query, query_index, columns, sheet_name,
table_names):
self.cursor.execute(query)
data = self.cursor.fetchall()
last_row = first_row + len(data) + 1
# Create Pandas dataframe from the data
df = pd.DataFrame(data, columns=columns)
worksheet = self.book[sheet_name]
df[columns[1]] = df[columns[1]].astype(float)
current_row = first_row
for r in dataframe_to_rows(df, index=False, header=True):
worksheet.cell(row=current_row, column=1).value = r[0]
worksheet.cell(row=current_row, column=2).value = r[1]
current_row += 1
# Add table title
worksheet.cell(row=first_row - 1,
column=1,
value=table_names[query_index])
# Add colouring to values
worksheet.conditional_formatting.add(
'B{}:B{}'.format(first_row, last_row),
ColorScaleRule(start_type='min',
start_color=Color('AA0000'),
end_type='max',
end_color=Color('00AA00')))
return last_row
def dos_header_excel_color(self):
t_dos_excel = pyxl.load_workbook(
"C:/Users/sleep/Desktop/Reversing/dos_header.xlsx")
ws = t_dos_excel.sheetnames
ws = t_dos_excel[ws[1]]
INDX = ['C', 3]
c = 0
for v in self.dos_header_dict.values():
indx, size = int(v['Offset'], 16), v['SIZE']
for a in range(0, size):
t_data, t_mov = (indx + a) // 16, (indx + a) % 16
if t_data == 0:
ws[chr(ord(INDX[0]) + t_mov) +
str(INDX[1] + t_data)].fill = PatternFill(
patternType='solid',
fgColor=Color(self.color_list[c]))
elif t_data == 1:
ws[chr(ord(INDX[0]) + t_mov) +
str(INDX[1] + t_data)].fill = PatternFill(
patternType='solid',
fgColor=Color(self.color_list[c]))
elif t_data == 2:
ws[chr(ord(INDX[0]) + t_mov) +
str(INDX[1] + t_data)].fill = PatternFill(
patternType='solid',
fgColor=Color(self.color_list[c]))
else:
ws[chr(ord(INDX[0]) + t_mov) +
str(INDX[1] + t_data)].fill = PatternFill(
patternType='solid',
fgColor=Color(self.color_list[c]))
c = (c + 1) % len(self.color_list)
t_dos_excel.save("C:/Users/sleep/Desktop/Reversing/dos_header.xlsx")
print("success save")
def export_excel():
# data = load_data()
wb = Workbook()
ws = wb.active
cell = ws['A1']
cell.value = 12
thin = Side(border_style="thin", color="000000")
dark_red = PatternFill("solid", fgColor=Color(theme=5, tint=0.4))
light_red = PatternFill("solid", fgColor=Color(theme=5, tint=0.6))
bold = Font(b=True, color="000000")
regular = Font(b=False, color="000000")
styles = {
'fill': PatternFill("solid", fgColor=Color(theme=5, tint=0.4)),
'border': Border(top=thin, left=thin, right=thin, bottom=thin),
'font': Font(b=True, color="000000")
}
style_range(ws, 'A1:C3', **styles)
wb.save(sys.argv[1] + '.xlsx')
def setFontAndColour(self):
cellFont = Font(name='Times New Roman', size=12)
cellFontBold = Font(name='Times New Roman', size=12, bold=True)
headerFillcolor = Color(rgb='00C4D79B')
oddFillcolor = Color(rgb='00E9E17F')
cellHeaderFill = PatternFill(patternType='solid',
fgColor=headerFillcolor)
cellOddFill = PatternFill(patternType='solid', fgColor=oddFillcolor)
for r in range(1, self.ws.max_row + 1):
for c in range(1, self.ws.max_column + 1):
# 1) font , size
if self.headerRow and r == 1:
self.ws.cell(row=r, column=c).font = cellFontBold
else:
self.ws.cell(row=r, column=c).font = cellFont
# 2) color : header row b/g colour , odd row b/g colour
if r == 1:
if self.headerRow:
self.ws.cell(row=r, column=c).fill = cellHeaderFill
else:
self.ws.cell(row=r, column=c).fill = cellOddFill
else:
if r & 1:
self.ws.cell(row=r, column=c).fill = cellOddFill
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
font = Font(name='Arial',
size=12,
bold=True,
underline=Font.UNDERLINE_SINGLE)
border = Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(colors.BLACK)))
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=border,
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': border,
'fill': fill
}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def make_excel_headers(self):
cell_bold = {'font': Font(bold=True)}
# Cell Properties
cell_existing = {
'alignment': Alignment(horizontal='center'),
'font': Font(bold=True),
'fill': PatternFill(patternType='solid', fgColor=Color('90909000'))
}
self.ws['E1'] = "Existing Motor"
self.ws['E1'].style = Style(**cell_existing)
# Mergeing Cells
self.ws.merge_cells('E1:K1')
# Cell Properties
cell_proposed = {
'alignment': Alignment(horizontal='center'),
'font': Font(bold=True),
'fill': PatternFill(patternType='solid', fgColor=Color('90909099'))
}
self.ws['L1'] = "Proposed Premium Efficiency Motor"
self.ws['L1'].style = Style(**cell_proposed)
# Mergeing Cells
self.ws.merge_cells('L1:R1')
for each_cell in self.headers_dict:
self.ws[each_cell] = self.headers_dict[each_cell]
self.ws[each_cell].style = Style(**cell_bold)
column = each_cell.replace('2', '')
self.ws.column_dimensions[column].width = len(
self.headers_dict[each_cell])
def process_item(self, item, spider):
wb = Workbook()
ws = wb.active
ws.title = "Cars"
with open(self.csvfilename, 'r') as f:
reader = csv.reader(f)
for r, row in enumerate(reader, start=1):
for c, val in enumerate(row, start=1):
# r is row number, c, is column number (both 1-indexed)
ws.cell(row=r, column=c).value = val
if r == 1: # headers = blue
ws.cell(row=r, column=c).font = Font(size=16)
ws.cell(row=r, column=c).fill = PatternFill(
fgColor=Color('0087BD'), fill_type='solid')
else: # odd rows = gray
ws.cell(row=r, column=c).font = Font(size=14)
if r % 2 == 1: # row is odd but not equal to 1
ws.cell(row=r, column=c).fill = PatternFill(
fgColor=Color('BFC1C2'), fill_type='solid')
# adjust column lengths in worksheet based on widest cells per column
for col in ws.columns:
max_length = 0
column = col[0].column # Get the column name
for cell in col:
try: # Necessary to avoid error on empty cells
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except:
pass
adjusted_width = (max_length + 2.0) * 1.1
ws.column_dimensions[column].width = adjusted_width
wb.save(self.spreadsheetfilename)
return item
def test_formatting(self, writer):
redFill = PatternFill(
start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType='solid'
)
whiteFont = Font(color=Color("FFFFFFFF"))
ws = writer.ws
ws.conditional_formatting.add('A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill)
)
writer.write_formatting()
xml = writer.read()
expected = """
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<conditionalFormatting sqref="A1:A3">
<cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
</worksheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def _convert_to_color(cls, color_spec):
"""
Convert ``color_spec`` to an openpyxl v2 Color object.
Parameters
----------
color_spec : str, dict
A 32-bit ARGB hex string, or a dict with zero or more of the
following keys.
'rgb'
'indexed'
'auto'
'theme'
'tint'
'index'
'type'
Returns
-------
color : openpyxl.styles.Color
"""
from openpyxl.styles import Color
if isinstance(color_spec, str):
return Color(color_spec)
else:
return Color(**color_spec)
def test_write_conditional_formatting(self):
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
fill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
font = Font(name='Arial',
size=12,
bold=True,
underline=Font.UNDERLINE_SINGLE)
border = Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(colors.BLACK)))
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=border,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
from openpyxl.writer.worksheet import write_conditional_formatting
for _ in write_conditional_formatting(ws):
pass # exhaust generator
wb = ws.parent
assert len(wb._differential_styles.styles) == 2
ft1, ft2 = wb._differential_styles.styles
assert ft1.font == font
assert ft1.border == border
assert ft1.fill == fill
assert ft2.fill == fill
def test_parse_dxfs(datadir):
datadir.chdir()
reference_file = '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 = '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 = PatternFill(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 = tostring(w._root)
read_style_prop = read_style_table(write_xml)
assert len(read_style_prop[2]) == len(wb.style_properties['dxf_list'])
for i, dxf in enumerate(read_style_prop[2]):
assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_read_complex_style(datadir):
datadir.chdir()
wb = load_workbook("complex-styles.xlsx")
ws = wb.active
assert ws.column_dimensions['A'].width == 31.1640625
#assert ws.column_dimensions['I'].font == Font(sz=12.0, color='FF3300FF', scheme='minor')
assert ws.column_dimensions['I'].fill == PatternFill(
patternType='solid', fgColor='FF006600', bgColor=Color(indexed=64))
assert ws['A2'].font == Font(sz=10, name='Arial', color=Color(theme=1))
assert ws['A3'].font == Font(sz=12,
name='Arial',
bold=True,
color=Color(theme=1))
assert ws['A4'].font == Font(sz=14,
name='Arial',
italic=True,
color=Color(theme=1))
assert ws['A5'].font.color.value == 'FF3300FF'
assert ws['A6'].font.color.value == 9
assert ws['A7'].fill.start_color.value == 'FFFFFF66'
assert ws['A8'].fill.start_color.value == 8
assert ws['A9'].alignment.horizontal == 'left'
assert ws['A10'].alignment.horizontal == 'right'
assert ws['A11'].alignment.horizontal == 'center'
assert ws['A12'].alignment.vertical == 'top'
assert ws['A13'].alignment.vertical == 'center'
assert ws['A15'].number_format == '0.00'
assert ws['A16'].number_format == 'mm-dd-yy'
assert ws['A17'].number_format == '0.00%'
assert 'A18:B18' in ws._merged_cells
assert ws['A19'].border == Border(
left=Side(style='thin', color='FF006600'),
top=Side(style='thin', color='FF006600'),
right=Side(style='thin', color='FF006600'),
bottom=Side(style='thin', color='FF006600'),
)
assert ws['A21'].border == Border(
left=Side(style='double', color=Color(theme=7)),
top=Side(style='double', color=Color(theme=7)),
right=Side(style='double', color=Color(theme=7)),
bottom=Side(style='double', color=Color(theme=7)),
)
assert ws['A23'].fill == PatternFill(patternType='solid',
start_color='FFCCCCFF',
end_color=(Color(indexed=64)))
assert ws['A23'].border.top == Side(style='mediumDashed',
color=Color(theme=6))
assert 'A23:B24' in ws._merged_cells
assert ws['A25'].alignment == Alignment(wrapText=True)
assert ws['A26'].alignment == Alignment(shrinkToFit=True)
def bg_color(self, cell, color = None):
if color:
a_fill = PatternFill(start_color=Color(color),
end_color=Color(color),
fill_type='solid')
cell.fill = a_fill
else:
return cell.font.size
def test_read_gradient_fill(StyleReader, datadir):
datadir.chdir()
expected = [
GradientFill(degree=90, stop=[Color(theme=0), Color(theme=4)])
]
with open("bug284-styles.xml") as src:
reader = StyleReader(src.read())
assert list(reader.parse_fills()) == expected
def cwt_term_excel_write(processing_df):
yearly_n_niin_grouped = processing_df.groupby(
['PLAN_YRMON', 'LOINO', 'N_NIIN']).mean()
yearly_n_niin_grouped_in_10days = yearly_n_niin_grouped[
yearly_n_niin_grouped['CWT'] <= 10]['CWT']
yearly_n_niin_grouped_in_30days = \
yearly_n_niin_grouped[(yearly_n_niin_grouped['CWT'] > 10) & (yearly_n_niin_grouped['CWT'] <= 30)]['CWT']
yearly_n_niin_grouped_in_50days = \
yearly_n_niin_grouped[(yearly_n_niin_grouped['CWT'] > 30) & (yearly_n_niin_grouped['CWT'] <= 50)]['CWT']
yearly_n_niin_grouped_up_50days = yearly_n_niin_grouped[
yearly_n_niin_grouped['CWT'] > 50]['CWT']
p_yearly_n_niin_grouped = yearly_n_niin_grouped['CWT']
yearly_n_niin_grouped_df = \
pd.DataFrame(columns=[2013, 2014, 2015, 2016, 2017, 2018], index=['총 개수(개)', '10일 이내(%)', '30일 이내(%)', '50일 이내(%)', '50일 초과(%)'])
yearly_n_niin_grouped_df.fillna(0, inplace=True)
count_grouping_yearly(yearly_n_niin_grouped_df, p_yearly_n_niin_grouped,
"총 개수(개)", 0)
count_grouping_yearly(yearly_n_niin_grouped_df,
yearly_n_niin_grouped_in_10days, "10일 이내(%)", 1)
count_grouping_yearly(yearly_n_niin_grouped_df,
yearly_n_niin_grouped_in_30days, "30일 이내(%)", 1)
count_grouping_yearly(yearly_n_niin_grouped_df,
yearly_n_niin_grouped_in_50days, "50일 이내(%)", 1)
count_grouping_yearly(yearly_n_niin_grouped_df,
yearly_n_niin_grouped_up_50days, "50일 초과(%)", 1)
writer = pd.ExcelWriter('./ResultFile/cwt_by_period.xlsx',
engine='openpyxl')
if len(yearly_n_niin_grouped_in_10days) > 0:
yearly_n_niin_grouped_in_10days.to_excel(writer, sheet_name='10일이내')
if len(yearly_n_niin_grouped_in_30days) > 0:
yearly_n_niin_grouped_in_30days.to_excel(writer, sheet_name='30일이내')
if len(yearly_n_niin_grouped_in_50days) > 0:
yearly_n_niin_grouped_in_50days.to_excel(writer, sheet_name='50일이내')
if len(yearly_n_niin_grouped_up_50days) > 0:
yearly_n_niin_grouped_up_50days.to_excel(writer, sheet_name='50일초과')
if len(yearly_n_niin_grouped_df) > 0:
yearly_n_niin_grouped_df.to_excel(writer, sheet_name='결과현황')
writer.save()
book = load_workbook('./ResultFile/cwt_by_period.xlsx')
sheets = book.get_sheet_names()
sheets.remove('결과현황')
for sheetname in sheets:
ws = book[sheetname]
for cellname in ['A', 'B', 'C', 'D']:
cell = ws[cellname + '1']
cell.fill = PatternFill(patternType='solid',
fgColor=Color('cccccc'))
ws.column_dimensions['A'].width = 14.5
ws.column_dimensions['B'].width = 16.5
ws.column_dimensions['C'].width = 11.75
ws.column_dimensions['D'].width = 12.13
wsr = book['결과현황']
for cellnamer in ['A', 'B', 'C', 'D', 'E', 'F', 'G']:
cell = wsr[cellnamer + '1']
cell.fill = PatternFill(patternType='solid', fgColor=Color('cccccc'))
wsr.column_dimensions['A'].width = 12.75
book.save('./ResultFile/cwt_by_period.xlsx')
def setStyles(cell, bgColor):
cell.font = Font(bold=False, color='000000')
bd = Side(style='thin', color=Color('FFFFFF'))
cell.border = Border(left=bd, top=bd, right=bd, bottom=bd)
cell.fill = PatternFill(fill_type='solid', fgColor=Color(bgColor))
cell.alignment = Alignment(shrinkToFit=True,
horizontal='left',
wrapText=True,
vertical='center')
def test_color_legend(self):
_color_legend(self.worksheet)
self.assertEqual(
self.worksheet.cell(row=FIRST_ROW_LEGEND_ENROLLMENT_STATUS,
column=FIRST_COL_LEGEND_ENROLLMENT_STATUS).style.fill.fgColor,
Color(rgb=ENROLLED_LATE_COLOR.lstrip('#')))
self.assertEqual(
self.worksheet.cell(row=FIRST_ROW_LEGEND_ENROLLMENT_STATUS+1,
column=FIRST_COL_LEGEND_ENROLLMENT_STATUS).style.fill.fgColor,
Color(rgb=NOT_ENROLLED_COLOR.lstrip("#")))
def add_header(cell, value):
gray = Color(rgb='00C2C2C2')
black = Color(rgb='000000')
border = Border(left=Side(border_style='thin', color=black),
right=Side(border_style='thin', color=black),
top=Side(border_style='thin', color=black),
bottom=Side(border_style='thin', color=black))
cell.fill = PatternFill(patternType='solid', fgColor=gray)
cell.alignment = Alignment(horizontal="center")
cell.border = border
cell.value = value
def write_totalrow(r, _r, ws):
ws.cell(row=r, column=1, value="Total")
_a = ws.cell(row=r,
column=7,
value="=SUBTOTAL(109,G{0}:G{1})".format(_r, r - 2))
_a.number_format = FORMAT_CURRENCY_USD_SIMPLE
for c in range(1, 8):
_a = ws.cell(row=r, column=c)
_a.font = Font(b=True, color=Color(rgb="00FFFFFF"))
_a.fill = PatternFill(fill_type='solid',
patternType='solid',
fgColor=Color(rgb="00000000"))
r += 1
return (r)
def wrapper(ws, number_of_row):
# 设置标题样式
ws.merge_cells('a1:f1')
color1 = Color(rgb=title_color)
font = Font(name="Microsoft YaHei UI",
size=34,
b=True,
color=color1)
a1 = ws['a1']
alignment = Alignment(horizontal='center', vertical='center')
a1.font = font
a1.alignment = alignment
# 设置表头样式
color2 = Color(rgb=header_color)
style_for_row2 = NamedStyle(name='header')
style_for_row2.font = Font(name='Calibri', size=16, color='FFFFFF')
style_for_row2.alignment = Alignment(horizontal='center',
vertical='center')
style_for_row2.fill = PatternFill('solid', fgColor=color2)
for each_cell in ws[2]:
each_cell.style = style_for_row2
# 设置表格样式
for i in range(1, number_of_row + 3):
ws.row_dimensions[i].height = 49.5
for i in range(1, 7):
ws.column_dimensions[chr(64 + i)].width = 15
color3 = Color(rgb=body_color)
style_for_body = NamedStyle(name='body')
style_for_body.font = Font(name='Calibri')
style_for_body.alignment = Alignment(horizontal='center',
vertical='center')
style_for_body.fill = PatternFill("solid", fgColor=color3)
style_for_body.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'),
top=Side(border_style='thin',
color='FF000000'))
for i in range(3, number_of_row + 3):
for j in range(1, 7):
ws.cell(row=i, column=j).style = style_for_body
return function(ws, number_of_row)
def get_cell_style(color='FF000000',
bgcolor='FFFFFFFF',
font='Calibri',
size=11,
bold=False,
italic=False,
underline='none',
strike=False,
border=None,
border_style=BORDER_THIN,
border_bottom=None,
border_bottom_style=None,
horizontal='general',
vertical='bottom',
number_format=None):
if not border:
border = 'FFB6B6B4'
if not border_bottom:
border_bottom = border
if not border_bottom_style:
border_bottom_style = border_style
return Style(font=Font(name=font,
size=size,
bold=bold,
italic=italic,
vertAlign=None,
underline=underline,
strike=strike,
color=color),
fill=PatternFill(patternType='solid',
fgColor=Color(bgcolor)),
border=Border(
left=Side(border_style=border_style,
color=Color(border)),
right=Side(border_style=border_style,
color=Color(border)),
top=Side(border_style=border_style,
color=Color(border)),
bottom=Side(border_style=border_bottom_style,
color=Color(border_bottom)),
),
alignment=Alignment(horizontal=horizontal,
vertical=vertical,
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0),
number_format=number_format)
def set_title(ws):
# sheet 에서 특정 셀을 선택해서 병합 정렬을 수행합니다.
ws.merge_cells('A1:E1')
# sheet 에서 A1 셀에 'Report' 라는 값을 입력합니다.
ws['A1'] = 'Report'
title = ws['A1']
# sheet 의 font 설정을 Font 모듈을 사용해서 설정합니다.
title.font = Font(name='nanum gothic', size=18, bold=True)
# sheet 의 정렬 설정을 Alignment 모듈을 사용해서 설정합니다.
title.alignment = Alignment(horizontal='center', vertical='center')
# sheet 의 fill 설정을 PatternFill 모듈을 사용해서 설정합니다.
# 셀의 뒷 배경의 색상을 변경할 수 있습니다.
title.fill = PatternFill(patternType='solid', fgColor=Color('808080'))
# Border 모듈을 사용해서, 셀의 테두리 설정을 할 수 있습니다.
# 셀의 왼쪽, 오른쪽, 윗쪽, 아래쪽에 대한 설정을 개별적으로 할 수 있습니다.
box = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
# 설정한 테두리 값을 border 설정에 적용합니다.
ws['A1'].border = box
ws['B1'].border = box
ws['C1'].border = box
ws['D1'].border = box
ws['E1'].border = box
def parser_conditional_formatting(self, element):
rules = {}
for cf in safe_iterator(element, '{%s}conditionalFormatting' % SHEET_MAIN_NS):
if not cf.get('sqref'):
# Potentially flag - this attribute should always be present.
continue
range_string = cf.get('sqref')
cfRules = cf.findall('{%s}cfRule' % SHEET_MAIN_NS)
rules[range_string] = []
for cfRule in cfRules:
if not cfRule.get('type') or cfRule.get('type') == 'dataBar':
# dataBar conditional formatting isn't supported, as it relies on the complex <extLst> tag
continue
rule = {'type': cfRule.get('type')}
for attr in ConditionalFormatting.rule_attributes:
if cfRule.get(attr) is not None:
rule[attr] = cfRule.get(attr)
formula = cfRule.findall('{%s}formula' % SHEET_MAIN_NS)
for f in formula:
if 'formula' not in rule:
rule['formula'] = []
rule['formula'].append(f.text)
colorScale = cfRule.find('{%s}colorScale' % SHEET_MAIN_NS)
if colorScale is not None:
rule['colorScale'] = {'cfvo': [], 'color': []}
cfvoNodes = colorScale.findall('{%s}cfvo' % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get('type') is not None:
cfvo['type'] = node.get('type')
if node.get('val') is not None:
cfvo['val'] = node.get('val')
rule['colorScale']['cfvo'].append(cfvo)
colorNodes = colorScale.findall('{%s}color' % SHEET_MAIN_NS)
for color in colorNodes:
c = Color(Color.BLACK)
if self.color_index\
and color.get('indexed') is not None\
and 0 <= int(color.get('indexed')) < len(self.color_index):
c.index = self.color_index[int(color.get('indexed'))]
if color.get('theme') is not None:
if color.get('tint') is not None:
c.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
c.index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
c.index = color.get('rgb')
rule['colorScale']['color'].append(c)
iconSet = cfRule.find('{%s}iconSet' % SHEET_MAIN_NS)
if iconSet is not None:
rule['iconSet'] = {'cfvo': []}
for iconAttr in ConditionalFormatting.icon_attributes:
if iconSet.get(iconAttr) is not None:
rule['iconSet'][iconAttr] = iconSet.get(iconAttr)
cfvoNodes = iconSet.findall('{%s}cfvo' % SHEET_MAIN_NS)
for node in cfvoNodes:
cfvo = {}
if node.get('type') is not None:
cfvo['type'] = node.get('type')
if node.get('val') is not None:
cfvo['val'] = node.get('val')
rule['iconSet']['cfvo'].append(cfvo)
rules[range_string].append(rule)
if len(rules):
self.ws.conditional_formatting.setRules(rules)
