예제 #1
0
 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
예제 #2
0
    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')
예제 #3
0
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
예제 #4
0
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
예제 #5
0
 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
예제 #6
0
 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
예제 #7
0
 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 == []
예제 #8
0
 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))))
예제 #9
0
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
예제 #10
0
 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'
     }
예제 #11
0
 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))
예제 #12
0
 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)]
예제 #13
0
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
예제 #14
0
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
예제 #15
0
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
예제 #16
0
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')
예제 #17
0
 def test_invalid_sequence(self, GradientFill):
     colors = [BLACK, WHITE]
     with pytest.raises(TypeError):
         gf = GradientFill(stop=colors)
예제 #18
0
 def test_sequence(self, GradientFill):
     colors = [Color(BLACK), Color(WHITE)]
     gf = GradientFill(stop=colors)
     assert gf.stop == colors
예제 #19
0
 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
예제 #20
0
 def test_sequence(self, GradientFill, colors):
     gf = GradientFill(stop=colors)
     assert gf.stop[0].rgb == BLACK
     assert gf.stop[1].rgb == WHITE