piper.xl.WorkBook

class piper.xl.WorkBook(file_name, sheets=None, ts_prefix='date', date_format='dd-mmm-yy', datetime_format='dd-mmm-yy')

Excel WorkBook helper class

Encapsulates pandas DataFrames and XlsxWriter objects

Parameters

• file_name – the name of the Excel file to create. If you don’t specify .xlsx extension, defaults to ‘.xlsx’

• sheets –

  (df, list, dict) multisheet mode

  1. A single DataFrame e.g. df

  file_name = 'outputs/single sheet.xlsx'
  WorkBook(file_name, df);
  

  2. A list of DataFrames e.g. [df1, df2, df3]

  file_name = 'outputs/multi sheet.xlsx'
  WorkBook(file_name, [df, df2]);
  

  3. A dictionary of sheet names and dataframe objects

  file_name = 'outputs/multi sheet with sheet names.xlsx'
  WorkBook(file_name, {'revised': df, 'original': df_original});
  

• ts_prefix –

  timestamp file name prefix.

  • ’date’ (date only) -> default

  • False (no timestamp)

  • True (timestamp prefix)

• date_format – default ‘dd-mmm-yy’

• datetime_format – default ‘dd-mmm-yy’

Examples

Create an Excel workbook without a date/time prefix

with WorkBook('outputs/excel workbook.xlsx', ts_prefix=False) as wb
    wb.add_sheet(df)

# Create an Excel workbook, with two worksheets,
# one with red tab sheet and zoom of 120%

xl_file = f'excel workbook with date prefix.xlsx'
with WorkBook(join('outputs', xl_file), ts_prefix='date') as wb:
    wb.add_sheet(df, sheet_name='revised data', tab_color='red', zoom=120)
    wb.add_sheet(df_original, sheet_name='original', tab_color='red')

Create an Excel workbook, with worksheet containing conditional formatting.

See WorkBook.add_condition for more details.

%%piper
import pandas as pd

data = {'Name':['Tom', 'nick', 'krish', 'jack'], 'Age':[20, 21, 19, 18]}
df = pd.DataFrame(data)

with WorkBook('outputs/excel workbook.xlsx', ts_prefix=False) as wb:

    sheet_name = 'test_worksheet'
    ws = wb.add_sheet(df, sheet_name=sheet_name, zoom=235)

    c = {'type': 'formula', 'criteria': '=$B2<21', 'format': 'accent2', 'range': 'B'}
    wb.add_condition(ws, c)

Methods

add_condition([worksheet, condition])	Add worksheet conditional format (rule)
add_format([worksheet, column_attr])	Add worksheet column format (rule)
add_sheet([dataframe, sheet_name, table, …])	Add DataFrame to WorkBook object
add_sql_sheet([sheet_name, sql])	Add worksheet containing SQL statement(s) used
close()	close (save) workbook
get_default_format(df)	For given dataframe, calculate default format.
get_metadata([meta_file])	Get XL metadata
get_range(sheet_name[, column_range, …])	Given worksheet & column reference, calculate excel sheet range.
get_styles([file_name])	Retrieve dictionary of styles allowing easy access to styles by name.
get_themes()	Get MS Excel standard themes
show_styles()	Show/List internal styles
show_themes()	Show/List themes

__init__(file_name, sheets=None, ts_prefix='date', date_format='dd-mmm-yy', datetime_format='dd-mmm-yy')

WorkBook constructor

Methods

__init__(file_name[, sheets, ts_prefix, …])	WorkBook constructor
add_condition([worksheet, condition])	Add worksheet conditional format (rule)
add_format([worksheet, column_attr])	Add worksheet column format (rule)
add_sheet([dataframe, sheet_name, table, …])	Add DataFrame to WorkBook object
add_sql_sheet([sheet_name, sql])	Add worksheet containing SQL statement(s) used
close()	close (save) workbook
get_default_format(df)	For given dataframe, calculate default format.
get_metadata([meta_file])	Get XL metadata
get_range(sheet_name[, column_range, …])	Given worksheet & column reference, calculate excel sheet range.
get_styles([file_name])	Retrieve dictionary of styles allowing easy access to styles by name.
get_themes()	Get MS Excel standard themes
show_styles()	Show/List internal styles
show_themes()	Show/List themes

Attributes

last_sheet_idx
sheet_dict

xl module piper.xl.WorkBook.add_condition