def test_ctor(self, GradientFill):
gf = GradientFill(degree=90, left=1, right=2, top=3, bottom=4)
assert gf.degree == 90
assert gf.left == 1
assert gf.right == 2
assert gf.top == 3
assert gf.bottom == 4
def run(self):
search_results = self.find_repeats(self.genomes)
wb = Workbook()
ws = wb.active
self.add_headers(ws)
# Сборка таблицы
colors_table = [['', *[gen.genome_id for gen in self.genome_colors]]]
for index, first_gen in enumerate(self.genome_colors):
row = [first_gen.genome_id]
for second_gen in self.genome_colors:
first_color = Color(self.get_color(first_gen.genome_id),
type='rgba')
second_color = Color(self.get_color(second_gen.genome_id),
type='rgba')
row.append(
GradientFill(type='linear',
degree=45,
stop=(Stop(first_color,
0), Stop(second_color, 1))))
colors_table.append(row)
# Запись таблицы
for row_index, row in enumerate(colors_table):
for col_index, value in enumerate(row):
cell = ws.cell(row_index + 1, col_index + 1)
if isinstance(value, GradientFill):
cell.fill = value
else:
cell.value = value
wb.save('results/result.xlsx')
def add_phase_row(phase_name: str, worksheet: openpyxl.worksheet.worksheet.Worksheet):
row = util.get_next_empty_row_in_col(col=1, worksheet=worksheet)
worksheet.row_dimensions[row].height = 21.75
bg_fill = GradientFill(
type="linear",
degree=90,
stop=[
Stop(position=0, color=openpyxl.styles.colors.Color(rgb="FF344756")),
Stop(position=1, color=openpyxl.styles.colors.Color(rgb="FF1A2D3B")),
],
)
bg_font = Font(
name="Calibri", sz=16, family=2.0, b=True, color=Color(rgb="FFFFFFFF")
)
phase_row_value = "Phase - {}".format(phase_name)
for col in range(1, 17):
# Non-hyperlinked cells
active_cell = worksheet.cell(row=row, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
if col == 1:
util_borders.set_top_bot_l_borders(active_cell)
active_cell.value = phase_row_value
elif col == 16:
util_borders.set_top_bot_r_borders(active_cell)
else:
util_borders.set_top_bot_borders(active_cell)
return worksheet
def add_worksheet_header(worksheet: openpyxl.worksheet.worksheet.Worksheet):
"""
Adds a PowerPlan header for any tab on the PowerPlan DCW. This only adds
the PowerPlan header and not the links to the different tabs
"""
bg_fill = GradientFill(type='linear',
degree=90,
stop=[
Stop(position=0, color=Color(rgb='FF2A92D0')),
Stop(position=1, color=Color(rgb='FF0F6EB4'))
])
bg_font = Font(name='Segoe UI Light',
sz=20,
family=2.0,
b=True,
color=Color(rgb='FFFFFFFF'))
for col in range(1, 17):
active_cell = worksheet.cell(row=1, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
# PowerPlan name is already denoted on the Table of Contents tab, so
# refer to that tab for the name
worksheet['A1'].value = '=\'Table of Contents\'!B2'
return worksheet
def test_serialise(self, GradientFill):
gf = GradientFill(degree=90, left=1, right=2, top=3, bottom=4, stop=[BLACK, WHITE])
xml = tostring(gf.to_tree())
expected = """
<fill>
<gradientFill bottom="4" degree="90" left="1" right="2" top="3" type="linear">
<stop position="0">
<color rgb="00000000"></color>
</stop>
<stop position="1">
<color rgb="00FFFFFF"></color>
</stop>
</gradientFill>
</fill>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_serialise(self, GradientFill):
gf = GradientFill(degree=90, left=1, right=2, top=3, bottom=4, stop=[BLACK, WHITE])
xml = tostring(gf.to_tree())
expected = """
<fill>
<gradientFill bottom="4" degree="90" left="1" right="2" top="3" type="linear">
<stop position="0">
<color rgb="00000000"></color>
</stop>
<stop position="1">
<color rgb="00FFFFFF"></color>
</stop>
</gradientFill>
</fill>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_empty_ctor(self, GradientFill):
gf = GradientFill()
assert gf.type == 'linear'
assert gf.degree == 0
assert gf.left == 0
assert gf.right == 0
assert gf.top == 0
assert gf.bottom == 0
assert gf.stop == []
def test_hashable(self, GradientFill):
gf = GradientFill(degree=90,
left=1,
right=2,
top=3,
bottom=4,
stop=[BLACK, WHITE])
assert hash(gf) == hash(
('linear', 90, 1.0, 2.0, 3.0, 4.0, (Color(BLACK), Color(WHITE))))
def format_components_tab(workbook: openpyxl.Workbook,
output_file: str=None):
'''
Formats the Components tab of the DCW
'''
sheet_name = 'Components'
util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
# Column dimensions:
worksheet.column_dimensions['A'].width = 15.85546875
worksheet.column_dimensions['B'].width = 44.0
worksheet.column_dimensions['C'].width = 21.28515625
worksheet.column_dimensions['D'].width = 40
worksheet.column_dimensions['E'].width = 40
worksheet.column_dimensions['F'].width = 9.140625
worksheet.column_dimensions['G'].width = 8.7109375
worksheet.column_dimensions['H'].width = 8.7109375
worksheet.column_dimensions['I'].width = 9.42578125
worksheet.column_dimensions['J'].width = 15.85546875
worksheet.column_dimensions['K'].width = 15.0
worksheet.column_dimensions['L'].width = 11.28515625
worksheet.column_dimensions['M'].width = 9.42578125
# Row dimensions
worksheet.row_dimensions[1].height = 33.75
worksheet.row_dimensions[2].height = 21.75
worksheet.row_dimensions[3].height = 45
# Row 1 formatting
bg_fill = GradientFill(type='linear', degree=90,
stop=[
Stop(
position=0, color=openpyxl.styles.colors.Color(rgb='FF2A92D0')
),
Stop(
position=1, color=openpyxl.styles.colors.Color(rgb='FF0F6EB4')
)
])
bg_font = Font(name='Segoe UI Light', sz=20,
family=2.0, b=True, color=Color(rgb='FFFFFFFF'))
for col in range(1, 17):
# Non-hyperlinked cells
active_cell = worksheet.cell(row=1, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
worksheet = add_links_header_to_components_tab(worksheet,
output_file=output_file)
return workbook
def test_dict_interface(self, GradientFill):
gf = GradientFill(degree=90, left=1, right=2, top=3, bottom=4)
assert dict(gf) == {
'bottom': "4",
'degree': "90",
'left': "1",
'right': "2",
'top': "3",
'type': 'linear'
}
def test_create(self, GradientFill):
src = """
<fill>
<gradientFill xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" degree="90">
<stop position="0">
<color theme="0"/>
</stop>
<stop position="1">
<color theme="4"/>
</stop>
</gradientFill>
</fill>
"""
xml = fromstring(src)
fill = GradientFill.from_tree(xml)
assert fill.stop == (Color(theme=0), Color(theme=4))
def test_create(self, GradientFill):
src = """
<fill>
<gradientFill xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" degree="90">
<stop position="0">
<color theme="0"/>
</stop>
<stop position="1">
<color theme="4"/>
</stop>
</gradientFill>
</fill>
"""
xml = fromstring(src)
fill = GradientFill.from_tree(xml)
assert fill.stop == [Color(theme=0), Color(theme=4)]
def format_toc_tab(workbook: openpyxl.Workbook):
"""
Formats the table of contents tab for PowerPlan DCWs
"""
sheet_name = "Table of Contents"
util.check_toc_tab_exists(workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
# Column/row dimensions
worksheet.column_dimensions["A"].width = 45
worksheet.column_dimensions["B"].width = 101.42578125
worksheet.row_dimensions[1].height = 28.5
# Background color
bg_fill = GradientFill(
type="linear",
degree=90,
stop=[
Stop(position=0,
color=openpyxl.styles.colors.Color(rgb="FF344756")),
Stop(position=1,
color=openpyxl.styles.colors.Color(rgb="FF1A2D3B")),
],
)
worksheet["A1"].fill = bg_fill
worksheet["B1"].fill = bg_fill
# Font style
worksheet["A1"].font = Font(
name="Calibri",
family=2.0,
b=True,
sz=22,
color=openpyxl.styles.colors.Color(rgb="FFFFFFFF"),
)
worksheet["A1"] = sheet_name
worksheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2)
for row in range(2, 9):
active_cell = worksheet.cell(row=row, column=1)
active_cell.font = Font(name="Calibri", sz=11, b=True)
return workbook
def test_write_gradient_fill():
fill = GradientFill(degree=90, stop=[Color(theme=0), Color(theme=4)])
writer = StyleWriter(DummyWorkbook())
writer._write_gradient_fill(writer._root, fill)
xml = get_xml(writer._root)
expected = """<?xml version="1.0" ?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<gradientFill degree="90" type="linear">
<stop position="0">
<color theme="0"/>
</stop>
<stop position="1">
<color theme="4"/>
</stop>
</gradientFill>
</styleSheet>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def format_components_tab_row_2(workbook: openpyxl.Workbook):
'''
Formats row 2 in the Components tab of the DCW
'''
sheet_name = 'Components'
util.check_toc_tab_exists(workbook=workbook, sheet_name=sheet_name)
worksheet = workbook[sheet_name]
bg_fill = GradientFill(type='linear', degree=90,
stop=[
Stop(
position=0, color=openpyxl.styles.colors.Color(rgb='FF344756')
),
Stop(
position=1, color=openpyxl.styles.colors.Color(rgb='FF1A2D3B')
)
])
bg_font = Font(name='Calibri', sz=16,
family=2.0, b=True, color=Color(rgb='FFFFFFFF'))
for col in range(1, 17):
# Non-hyperlinked cells
active_cell = worksheet.cell(row=2, column=col)
active_cell.font = bg_font
active_cell.fill = bg_fill
if (col == 1):
util_borders.set_top_bot_l_borders(active_cell)
elif (col == 16):
util_borders.set_top_bot_r_borders(active_cell)
else:
util_borders.set_top_bot_borders(active_cell)
return workbook
wb.save('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
#将本地图片添加到指定位置
from openpyxl.drawing.image import Image
wb = load_workbook('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
wb.create_sheet('image')
sheet = wb['image']
img = Image('D:/BaiduNetdiskDownload/壁纸/1418704224888.jpeg')
sheet.add_image(img, 'B2')
wb.save('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
#修改单元格样式
from openpyxl.styles import Alignment, Border, Side, Font
from openpyxl.styles.fills import GradientFill
wb = load_workbook('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
sheet = wb['各省销量']
#设置单元格字体
sheet['A1'].font = Font(
name='黑体', #设置单元格字体
size=36, #设置字体字号
bold=True, #加粗
color='FFaa8844' #单元格文本颜色
)
#填充渐变颜色（起始颜色，结束颜色）
sheet['B1'].fill = GradientFill(stop=('00ffff', '00ffff'))
wb.save('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
def test_invalid_sequence(self, GradientFill):
colors = [BLACK, WHITE]
with pytest.raises(TypeError):
gf = GradientFill(stop=colors)
def test_sequence(self, GradientFill):
colors = [Color(BLACK), Color(WHITE)]
gf = GradientFill(stop=colors)
assert gf.stop == colors
def test_stop_sequence(self, GradientFill, Stop, colors):
gf = GradientFill(stop=[Stop(colors[0], 0), Stop(colors[1], .5)])
assert gf.stop[0].color.rgb == BLACK
assert gf.stop[1].color.rgb == WHITE
assert gf.stop[0].position == 0
assert gf.stop[1].position == .5
def test_sequence(self, GradientFill, colors):
gf = GradientFill(stop=colors)
assert gf.stop[0].rgb == BLACK
assert gf.stop[1].rgb == WHITE
