Python 实现 table style2种格式

shining_game_149

于 2024-05-05 16:15:58 发布

阅读量167

点赞数 1

文章标签： python 数据库 linux

本文链接：https://blog.csdn.net/shining_game_149/article/details/138468478

版权

可以用 Python的xw包实现2种固定的表样式

## -------------------------------------------------------------------------------------------------------- ##
## ******************************************************************************************************** ##
def tbsty3(format_row,header,color_header,color_line,sleep_time):
    app = xw.apps.active
    wb = xw.books.active
    sht = xw.sheets.active
    # sleep_time = 0.01
    # start_col = index_col + 1

    rng_current = wb.selection.current_region
    row_count = rng_current.rows.count
    col_count = rng_current.columns.count

    rng_current_start_row = rng_current.row
    rng_current_start_col = rng_current.column
    rng_current_last_row = rng_current.row + (row_count - 1)
    rng_current_last_col = rng_current.column + (col_count - 1)

    scol = rng_current_start_col
    ecol = rng_current_last_col
    srow = rng_current_start_row + header
    erow = rng_current_last_row

    # data_region
    r0 = rng_current_start_row
    r1 = srow
    r2 = erow
    for c in range(scol,ecol + 1):
        format_cell = sht.range(r0,c)
        if format_cell.value == 'str':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = middle_alignment
            sht.range((r1,c),(r2,c)).api.Font.Color = color_tx_black
        elif format_cell.value == 'n0':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = number_format0
        elif format_cell.value == 'n1':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = number_format1
        elif format_cell.value == 'n2':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = number_format2
        elif format_cell.value == 'p0':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = percent_format0
        elif format_cell.value == 'p1':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = percent_format1
        elif format_cell.value == 'p2':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = percent_format2
        elif format_cell.value == 'p3':
            sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = percent_format3
        elif format_cell.value == 'th':
            # sht.range((r1,c),(r2,c)).api.HorizontalAlignment = right_alignment
            sht.range((r1,c),(r2,c)).api.NumberFormat = thousands_format
        time.sleep(sleep_time)
    # header 2
    r = rng_current_start_row + 1
    for c in range(rng_current_start_col,rng_current_last_col + 1):
        cell = sht.range(r,c)
        if cell.value != None:
            cell.api.HorizontalAlignment = middle_alignment
            cell.color = color_header
            cell.api.Font.Color = color_tx_white
            cell.api.Font.Bold = True
            # left line
            r1 = rng_current_start_row + 1
            r2 = erow
            sheet_range = sht.range((r1,c),(r2,c))
            sheet_range.api.Borders(border_left).LineStyle = linestyle_1
            sheet_range.api.Borders(border_left).Color = color_line

    # header 3
    r = rng_current_start_row + 2
    c1 = rng_current_start_col
    c2 = rng_current_last_col
    sheet_range = sht.range((r,c1),(r,c2))
    sheet_range.api.Font.Color = color_tx_black
    sheet_range.api.Font.Bold = True
    sheet_range.api.HorizontalAlignment = middle_alignment
    sheet_range.api.Borders(border_up).LineStyle = linestyle_1
    sheet_range.api.Borders(border_up).Color = color_line
    sheet_range.api.Borders(border_bottom).LineStyle = linestyle_1
    sheet_range.api.Borders(border_bottom).Color = color_line
    
    print('****************Done!****************')

## -------------------------------------------------------------------------------------------------------- ##
## ******************************************************************************************************** ##
def tbsty4(format_col,header,index_col,color_header,color_line,sleep_time):
    app = xw.apps.active
    wb = xw.books.active
    sht = xw.sheets.active
    # sleep_time = 0.01
    # start_col = index_col + 1

    rng_current = wb.selection.current_region
    row_count = rng_current.rows.count
    col_count = rng_current.columns.count

    rng_current_start_row = rng_current.row
    rng_current_start_col = rng_current.column
    rng_current_last_row = rng_current.row + (row_count - 1)
    rng_current_last_col = rng_current.column + (col_count - 1)

    scol = rng_current_start_col + format_col + index_col
    ecol = rng_current_last_col
    srow = rng_current_start_row + header
    erow = rng_current_last_row

    # data region
    c0 = rng_current_start_col
    c1 = scol
    c2 = ecol
    for r in range(srow,erow + 1):
        format_cell = sht.range(r,c0)
        if format_cell.value == 'str':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = middle_alignment
            sht.range((r,c1),(r2,c2)).api.Font.Color = color_tx_black
        elif format_cell.value == 'n0':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = number_format0
        elif format_cell.value == 'n1':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = number_format1
        elif format_cell.value == 'n2':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = number_format2
        elif format_cell.value == 'p0':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = percent_format0
        elif format_cell.value == 'p1':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = percent_format1
        elif format_cell.value == 'p2':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = percent_format2
        elif format_cell.value == 'p3':
            sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = percent_format3
        elif format_cell.value == 'th':
            # sht.range((r,c1),(r,c2)).api.HorizontalAlignment = right_alignment
            sht.range((r,c1),(r,c2)).api.NumberFormat = thousands_format
        time.sleep(sleep_time)
    
    # format_col
    c = rng_current_start_col + format_col - 1
    for r in range(rng_current_start_row,rng_current_last_row + 1):
            cell = sht.range(r,c)
            if cell.value != None:
                cell.api.HorizontalAlignment = middle_alignment
                cell.api.Font.Color = color_tx_deep_grey
    # index_col
    c = rng_current_start_col + format_col
    for r in range(rng_current_start_row,rng_current_last_row + 1):
        cell = sht.range(r,c)
        if cell.value != None:
            cell.api.HorizontalAlignment = middle_alignment
            cell.api.Font.Bold = True
    if index_col > 1:
        c = rng_current_start_col + format_col + 1
        for r in range(rng_current_start_row,rng_current_last_row + 1):
            cell = sht.range(r,c)
            if cell.value != None:
                cell.api.HorizontalAlignment = left_alignment
                cell.api.Font.Bold = False
                time.sleep(sleep_time)

    # header 1
    r = rng_current_start_row
    for c in range(rng_current_start_col,rng_current_last_col + 1):
        cell = sht.range(r,c)
        if cell.value != None:
            cell.api.HorizontalAlignment = middle_alignment
            cell.color = color_header
            cell.api.Font.Color = color_tx_white
            cell.api.Font.Bold = True
            # left line
            r1 = rng_current_start_row + 1
            r2 = erow
            sheet_range = sht.range((r1,c),(r2,c))
            sheet_range.api.Borders(border_left).LineStyle = linestyle_1
            sheet_range.api.Borders(border_left).Color = color_line
            time.sleep(sleep_time)

    # header 2
    r = rng_current_start_row + 1
    c1 = rng_current_start_col
    c2 = rng_current_last_col
    sheet_range = sht.range((r,c1),(r,c2))
    sheet_range.api.Font.Color = color_tx_black
    sheet_range.api.Font.Bold = True
    sheet_range.api.HorizontalAlignment = middle_alignment
    sheet_range.api.Borders(border_up).LineStyle = linestyle_1
    sheet_range.api.Borders(border_up).Color = color_line
    sheet_range.api.Borders(border_bottom).LineStyle = linestyle_1
    sheet_range.api.Borders(border_bottom).Color = color_line

    print('****************Done!****************')