ExcelExporter

class qf_lib.documents_utils.excel.excel_exporter.ExcelExporter(settings: Settings)[source]

Bases: object

Methods:

apply_font_style_to_area(file_path, ...[, ...])

Apply a font style to a certain area in excel

export_container(container, file_path[, ...])

Exports the container (QFSeries, QFDataFrame) to the excel file.

get_workbook(file_path, write_mode)

Takes a path to the file (creates it if necessary), opens the file and retrieves a Workbook object from it.

get_worksheet(work_book[, sheet_name])

Gets a worksheet of given name from a provided workbook.

write_cell(file_path, cell_reference, value)

Writes a value into the specified cell_reference.

write_to_worksheet(exported_value, ...)

Exports a given value to Excel worksheet.

apply_font_style_to_area(file_path: str, specified_area: str, write_mode: WriteMode = WriteMode.CREATE_IF_DOESNT_EXIST, sheet_name: Optional[str] = None, **font_setting)[source]

Apply a font style to a certain area in excel

Parameters:
  • file_path – path to the xlsl file where the cell should be written in

  • specified_area (str) – the specified area where the formatting should take place in the following format: ‘A1:Z12’

  • write_mode – mode in which the file should be opened

  • sheet_name (str) – the name of the sheet where the cell should be written. If a sheet of this name doesn’t exist it will be created. If it does: it will be edited (but not cleared). If no sheet_name is specified, then the currently active one will be picked

  • font_setting – additional font settings for the cell fonts

export_container(container: Union[QFSeries, QFDataFrame], file_path: str, write_mode: WriteMode = WriteMode.CREATE_IF_DOESNT_EXIST, starting_cell: str = 'A1', sheet_name: Optional[str] = None, include_index: bool = True, include_column_names: bool = False, remove_old_file=False) Union[bytes, str][source]

Exports the container (QFSeries, QFDataFrame) to the excel file. Returns the absolute file path of the exported file.

Parameters:
  • container – container with data to be exported

  • file_path – path (relative to the output root directory) to the file to which data should be exported

  • write_mode – mode in which the file should be opened; default: WriteMode.CREATE_IF_DOESNT_EXIST

  • starting_cell – the address of the cell which should be the top left corner of the exporter container default: ‘A1’

  • sheet_name – the name of the sheet to which the container should be exported. If a sheet of this name doesn’t exist it will be created. If it does: it will be edited (but not cleared). If no sheet_name is specified, then the currently active one will be picked

  • include_index – determines whether the index should be written together with the data.

  • include_column_names – determines whether the column names should be written together with the data. For series containers the column names are always “Index” and “Values”.

  • remove_old_file – if true it first deletes the old file before creating new

get_workbook(file_path: str, write_mode: WriteMode) Workbook[source]

Takes a path to the file (creates it if necessary), opens the file and retrieves a Workbook object from it.

get_worksheet(work_book: Workbook, sheet_name: Optional[str] = None) Worksheet[source]

Gets a worksheet of given name from a provided workbook. If :sheet_name is None, then the active sheet from the workbook is returned.

write_cell(file_path: str, cell_reference: str, value: Any, write_mode: WriteMode = WriteMode.CREATE_IF_DOESNT_EXIST, sheet_name: Optional[str] = None)[source]

Writes a value into the specified cell_reference.

Parameters:
  • file_path – path to the xlsl file where the cell should be written in

  • cell_reference – the address of the cell where the value should be placed, for example ‘C10’

  • value – the value to write in the cell

  • write_mode – mode in which the file should be opened

  • sheet_name – the name of the sheet where the cell should be written. If a sheet of this name doesn’t exist it will be created. If it does: it will be edited (but not cleared). If no sheet_name is specified, then the currently active one will be picked

write_to_worksheet(exported_value: Any, work_sheet: Worksheet, starting_row: int, starting_column: int, include_index: bool, include_column_names: bool)[source]

Exports a given value to Excel worksheet. If the :exported_value is a series or dataframe, then the :starting_row and :starting_column correspond to the top left corner of the container’s values in the worksheet. If the :exported_value isn’t a series nor dataframe, then the :include_index and :include_column_names parameters should be False.