some handy excel helping functions with the help of openpyxl

handyexcel.py

import openpyxl
import numpy as np
import pandas as pd

def get_column_letter(col_idx):
    """Convert a column number into a column letter (3 -> 'C')
    Right shift the column col_idx by 26 to find column letters in reverse
    order.  These numbers are 1-based, and can be converted to ASCII
    ordinals by adding 64.
    """
    # these indicies corrospond to A -> ZZZ and include all allowed
    # columns
    if not 1 <= col_idx <= 18278:
        raise ValueError("Invalid column index {0}".format(col_idx))
    letters = []
    while col_idx > 0:
        col_idx, remainder = divmod(col_idx, 26)
        # check for exact division and borrow if needed
        if remainder == 0:
            remainder = 26
            col_idx -= 1
        letters.append(chr(remainder+64))
    return ''.join(reversed(letters))

def num_to_coord(row_num, col_num):
    # Convert a cell row and col number into an Excel style coordinate
    # all started from 1
    col_letter = get_column_letter(col_num)
    return '{}{}'.format(col_letter, row_num)

def rng_num_to_coord(min_row_num, min_col_num, max_row_num, max_col_num):
    # Convert a cell row and col number into an Excel style coordinate
    # all started from 1
    col_letter = get_column_letter(min_col_num)
    min_coord = '{}{}'.format(col_letter, min_row_num)
    col_letter = get_column_letter(max_col_num)
    max_coord = '{}{}'.format(col_letter, max_row_num)
    return '{}:{}'.format(min_coord, max_coord)

def sheet_coord(worksheet):
    min_row_num = worksheet.min_row
    min_col_num = worksheet.min_column
    max_row_num = worksheet.max_row
    max_col_num = worksheet.max_column
    return rng_num_to_coord(
        min_row_num, min_col_num, max_row_num, max_col_num
    )

from openpyxl.worksheet.table import Table, TableStyleInfo
def set_sheet_table(worksheet, table_name):
    tab = Table(displayName=table_name, ref=sheet_coord(worksheet))
    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                           showLastColumn=False, showRowStripes=True, showColumnStripes=False)
    tab.tableStyleInfo = style
    worksheet.add_table(tab)
    
def set_columns_width(worksheet, width_list):
    for col, width in enumerate(width_list):
        col_letter = get_column_letter(col+1)
        worksheet.column_dimensions[col_letter].width = width
        
def freeze_head_row(worksheet):
    worksheet.freeze_panes = worksheet['A2']

def dataframe_to_table(dataframe, filename, sheet_name, table_name=None):
    writer = pd.ExcelWriter(filename, engine='openpyxl')
    dataframe.to_excel(writer, sheet_name=sheet_name)
    workbook  = writer.book
    worksheet = workbook[sheet_name]
    freeze_head_row(worksheet)
    if table_name is not None:
        set_sheet_table(worksheet, table_name)
    return writer, worksheet

def set_column_autofit(worksheet):
    def as_text(value):
        if value is None:
            return ""
        return str(value)
    for column_cells in worksheet.columns:
        length = max(len(as_text(cell.value)) for cell in column_cells)
        length = min((length + 2) * 1.2, 65)
        worksheet.column_dimensions[column_cells[0].column].width = length