Skip to content

Instantly share code, notes, and snippets.

@Nyahua
Created May 1, 2017 13:54
Show Gist options
  • Save Nyahua/2e8776fd828e47c27a20e697dcf54134 to your computer and use it in GitHub Desktop.
Save Nyahua/2e8776fd828e47c27a20e697dcf54134 to your computer and use it in GitHub Desktop.
some handy excel helping functions with the help of openpyxl
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment