Created
May 1, 2017 13:54
-
-
Save Nyahua/2e8776fd828e47c27a20e697dcf54134 to your computer and use it in GitHub Desktop.
some handy excel helping functions with the help of openpyxl
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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