Skip to content

class beautifulexcel.ExcelWriter()

Class for writing DataFrame objects into excel sheets.

Example

Output pandas dataframe quickly with beautiful formatting.

from beautifulexcel import ExcelWriter

with ExcelWriter('workbook.xlsx', mode='r', style='elegant_blue') as writer:
    ws1 = writer.to_excel(df1, sheetname='My Sheet', mode='a', startrow=0, startcol=0)
    ws2 = writer.to_excel(df2, sheetname='My Sheet', mode='a', startrow=20, startcol=0)

__init__(file, mode='replace', if_sheet_exists='replace', theme='elegant_blue', ref_warnings=True, date_format=None, datetime_format=None, engine_kwargs={}, **kwargs)

Parameters:

Name Type Description Default
file str

Path to xls or xlsx or ods file

 required
mode str

If the file already exists you can either "replace" or "modify" it

 'replace'
if_sheet_exists str

If a excel sheet already exists raise an "error", create a "new" sheet with a different name, "replace" the existing sheet with the new one, or "overlay" the new contents with the old ones

 'replace'
theme str

Excel style name or path to theme yaml file

 'elegant_blue'
date_format str

Format string for dates written into Excel files (e. g. 'YYYY-MM-DD')

 None
datetime_format str

Format string for datetime objects written into Excel files. (e. g. 'YYYY-MM-DD HH:MM:SS')

 None
engine_kwargs str

keywords passed though to openpyxl in "replace"-mode: openpyxl.Workbook(engine_kwargs); "modify"-mode: openpyxl.load_workbook(file, engine_kwargs)

 {}
Example 
from beautifulexcel import ExcelWriter

with ExcelWriter('workbook.xlsx', mode='r', style='elegant_blue') as writer:
    ...

to_excel(df, sheet_name, startrow=0, startcol=0, index=False, header=True, style={}, use_base_style=True, col_widths={}, col_autofit=True, auto_number_formatting=True)

Export pandas Datafame to excel.

Parameters:

Name Type Description Default
df DataFrame

Pandas Dataframe to export

 required
sheet_name str

Sheet name

 required
startrow int

Upper left cell row to dump dataframe (zero indexed)

 0
startcol int

Upper left cell column to dump data rame (zero indexed)

 0
index bool

Write row names/index as first column/s

 False
header bool

Write column names/header as first row/s

 True
style dict

Style dictionary with key referencing the cell/column/row and value the style to apply e.g. {'RoE': 'bg_light_blue', 'D:E': {'fill': 'FFEEB7'}}

 {}
use_base_style bool

Apply the excel workbook "theme" set in ExcelWriter()

 True
col_widths dict

Define column widths manually with key referencing the column and value the width e.g. {'A:C': 20, 'F': 10, 'employees': 40}

 {}
col_autofit bool

Automatically change column width to fit content best

 True
auto_number_formatting bool

Automatically detect number format and change excel format

 True

Returns:

Type Description
DataframeSheet

beautifulexcel.DataframeSheet
Example 
from beautifulexcel import ExcelWriter

with ExcelWriter('workbook.xlsx', mode='r', style='elegant_blue') as writer:
    ws1 = writer.to_excel(df1, sheetname='My Sheet', mode='a', startrow=0, startcol=0)