API Reference

Tools for creating and formatting spreadsheets with Python and OpenPyXL.

Functions:

append_to_xlsx(csv_input_file, xlsx_output_file)	Add CSV file to xlsx file as a new worksheet.
col_width_from_cm(width)	Returns an estimate of the width of the column in pixels, given the size in centimeters.
format_header(ws, alignment_list[, ...])	Format the alignment of the header rows of a worksheet.
format_sheet(ws[, number_format_list, ...])	Format columns of an xlsx worksheet.
make_column_property_dict(indict[, outdict, ...])	Generate property lists from integer values.

append_to_xlsx(csv_input_file, xlsx_output_file, sheet_title=None, separator=',', overwrite=False, to_floats=False, encoding='UTF-8')

Add CSV file to xlsx file as a new worksheet.

Parameters

• csv_input_file (Union[str, Path, PathLike]) – filepath of CSV file.

• xlsx_output_file (Union[str, Path, PathLike]) – filepath of xlsx file.

• sheet_title (Optional[str]) – Title of sheet to append. Default is the value of csv_input_file.

• separator (str) – Separator for reading CSV file. Default ','.

• overwrite (bool) – Whether to overwrite the xlsx output file (i.e. create a new file containing just the new sheet). Default False.

• to_floats (bool) – Whether to read strings with thousand separators as floats. Default False.

• encoding (str) – The encoding to read the file as. Default 'UTF-8'.

col_width_from_cm(width)

Returns an estimate of the width of the column in pixels, given the size in centimeters.

Parameters

width (float) – The width in centimeters.

New in version 0.2.0.

Return type

float

format_header(ws, alignment_list, start_row=1, end_row=1)

Format the alignment of the header rows of a worksheet.

Parameters

• ws (openpyxl.worksheet.worksheet.Worksheet.) – The worksheet to format.

• alignment_list (Dict[str, str]) – dictionary of alignments (left, right, center) for each column letter.

• start_row (int) – The row to start formatting on. Default 1.

• end_row (int) – The row to end formatting on. Default 1.

format_sheet(ws, number_format_list=None, width_list=None, alignment_list=None)

Format columns of an xlsx worksheet.

Parameters

• ws (openpyxl.worksheet.worksheet.Worksheet.) – The worksheet to format.

• number_format_list (Optional[Dict[str, str]]) – dictionary of number format strings for each column letter. Default None.

• width_list (Optional[Dict[str, float]]) – dictionary of widths for each column letter. Default None.

• alignment_list (Optional[Dict[str, str]]) – dictionary of alignments (left, right, or center) for each column letter. Default None.

make_column_property_dict(indict, outdict=None, offset_dict=None, repeat=1, length=1)

Generate property lists from integer values.

Parameters

• indict (Mapping) – Property values to add to the property dict.

• outdict (Optional[Dict[str, Any]]) – Dictionary of properties for each column letter. Default None.

• offset_dict (Optional[Mapping]) – Default None.

• repeat (int) – Default 1.

• length (int) – Default 1.

Return type

Dict[str, Any]