comet.CExcelBook

class comet.CExcelBook

This section describes all functions for the comet.CExcelBook class.

See also

Module comet.excel

Accessor and utility functions for excel files.

CExcelBook represents an opened Excel file in memory. This class can be used to query the Excel sheets for information and content.

Important: When querying cell attributed like border styles etc…, you have to fetch them from the actual indices and not the anchor cell.n e.g. When trying to determine the right border of a cell merged from column 0-1, you have to query the right border color at column 1.

Methods

CExcelBook.getSheetCount()

Returns the number of sheets in the excel document.

Returns

The number of sheets

Return type

int

Raises
Available

CScript

excel::count_sheets

CExcelBook.getSheetName(sheetIndex=0)

Returns the name of the sheet at the provided index

Parameters

sheetIndex (int) – The index of the sheet to get the name for

Returns

The name of the sheet

Return type

str

Raises
Available

CScript

excel::nth_sheet

CExcelBook.getFirstRow(sheet)

Returns the first row in the sheet that contains a used cell.

Parameters

sheet (str) –

Name of data sheet

  • Empty str : First data sheet

Returns

The 0-based row index

Return type

int

Raises
Available

CScript

excel::first_row

CExcelBook.getLastRow(sheet)

Returns the zero-based index of the row after the last row in the sheet that contains a used cell.

Parameters

sheet (str) –

Name of data sheet

  • Empty str : First data sheet

Returns

The 0-based row index

Return type

int

Raises
Available

CScript

excel::last_row

CExcelBook.getFirstColumn(sheet)

Returns the first column in the sheet that contains a used cell.

Parameters

sheet (str) –

Name of data sheet

  • Empty str : First data sheet

Returns

The 0-based column index

Return type

int

Raises
Available

CScript

excel::first_col

CExcelBook.getLastColumn(sheet)

Returns the zero-based index of the column after the last column in the sheet that contains a used cell.

Parameters

sheet (str) –

Name of data sheet

  • Empty str : First data sheet

Returns

The 0-based column index

Return type

int

Raises
Available

CScript

excel::last_col

CExcelBook.getCellType(sheet, column, row)

Get the type of cell of an Excel sheet.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The type of the cell.

One of Excel cell types

Return type

int

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row or column have invalid values

  • CometError – On internal error

Available

CScript

excel::get_type

CExcelBook.getCellFormula(sheet, column, row)

Get the formula of a cell of an Excel sheet.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The cell formula

Return type

str

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row or column have invalid values

  • CometError – On internal error

Available

CScript

excel::get_formula

CExcelBook.getCellFormat(sheet, column, row)

Get the format of a cell of an Excel sheet.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

Format ID and user defined format statement if any.

  • Format ID < 164: Excel standard format

  • Format ID >= 164: User defined format. In this case the result contains the format statement

Return type

tuple[int, str]

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row or column have invalid values

  • CometError –

    • On internal error

    • When cell has no formula set

Available

CScript

excel::get_format

CExcelBook.getCellFormatTags(sheet, column, row, format=0)

Get the format tags determined by the cell format which can be used to insert the cell content in a formatted manner into the host application.

Note that the format tags are determined by the cell settings which are applied to the entire content.

For inline formatting which changes throughout the cell content use getCellContent().

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

  • format (int) –

    The target tag format.

    Default is comet.kExportPlain

    Valid values are:
    • comet.kExportPlain:

      The result is two empty str since plain text does not contain any formatting.

    • comet.kExportTagged

      Result contains opening and closing tags for InDesign® tagged text.

    • comet.kExportHTML

      Result contains opening and closing tags for HTML text with inline CSS applied.

Returns

The opening and closing tags to put before and after the unformatted cell content.

Return type

tuple[str, str]

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row or column have invalid values

  • CometError – On internal error

Available

CScript

excel::get_font

CExcelBook.getCellArea(sheet, column, row)

Determine the merged area of a cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

Indices as tuple: (column start, column end, row start, row end)

Return type

tuple[int, int, int, int]

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row or column have invalid values

  • CometError – On internal error

Available

CScript

excel::get_merge

CExcelBook.getRowHeight(sheet, row)

Get the height of a row of an Excel sheet.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • row (int) – 0-based row of the table

Returns

Height of the row in points

Return type

float

Raises
Available

CScript

excel::get_rowheight

CExcelBook.getColumnWidth(sheet, column)

Get the width of a column of an Excel sheet.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

Returns

Width of the column in points

Return type

float

Raises
Available

CScript

excel::get_colwidth_pt

CExcelBook.getColor(sheet, column, row, which)

Determine the configured colors of a cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

  • which (int) –

    Which color data to fetch.

    One of Excel selectors

Returns

RGB Color

Return type

CColor

Raises

  • TypeError – When parameter types are invalid

  • ValueError –

    • When parameter sheet points to invalid sheet

    • When parameter row or column are out of bounds

    • When parameter which has invalid value

  • CometError – On internal error

Available

CScript

excel::get_color

CExcelBook.getBorderStyle(sheet, column, row, which)

Determine the border style of a cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

  • which (int) –

    Which attribute to query.

    One of Excel selectors in range (0, 3)

Returns

The border style for that cell border.

One of Excel border styles

Return type

int

Raises

  • TypeError – When parameter types are invalid

  • ValueError –

    • When parameter sheet points to invalid sheet

    • When parameter row or column are out of bounds

    • When parameter which has invalid value

  • CometError – On internal error

Available

CScript

excel::get_border

CExcelBook.getFontFamily(sheet, column, row)

Get the name of the font family of the given cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The name of the font family

Return type

str

Raises
Available

CScript

excel::get_font

CExcelBook.getFontFace(sheet, column, row)

Get the name of the font face of the given cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • row (int) – 0-based row of the table

  • column (int) – 0-based column of the table

Returns

Name of the font face with taking bold and italic into account.

The font face is useful for the <cTypeface:…> tag of TaggedText.

For HTML output the font face is not needed.

Return type

str

Raises
Available

CScript

excel::get_font

CExcelBook.getFontSize(sheet, column, row)

Get the font size of a given cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The font size of the given cell

Return type

int

Raises
Available

CScript

excel::get_font

CExcelBook.getFontColor(sheet, column, row)

Get the font color of the given cell.

Parameters

  • sheet (str) –

    Name of data sheet.

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The font color of the given cell as an RGB color.

Return type

CColor

Raises
Available

CScript

excel::get_font

CExcelBook.getFontStrikeThrough(sheet, column, row)

Get whether the font in the cell is striked through

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

Whether the font in the cell is striked through

Return type

bool

Raises
Available

CScript

excel::get_font

CExcelBook.getFontUnderline(sheet, column, row)

Get the underline state of the cell.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The underline state.

One of Excel underline styles

Return type

int

Raises
Available

CScript

excel::get_font

CExcelBook.getFontPosition(sheet, column, row)

Get the font position

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The font position state.

One of Excel font positions

Return type

int

Raises
Available

CScript

excel::get_font

CExcelBook.getFontWeight(sheet, column, row)

Get whether font weight

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

The font weight

Return type

int

Raises
Available

CScript

excel::get_font

CExcelBook.getFontItalic(sheet, column, row)

Get whether the font is italic

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

Returns

Whether the font is italic

Return type

bool

Raises
Available

CScript

excel::get_font

CExcelBook.getCellContent(sheet, column, row, format=None)

Get the content of a cell.

The cell contents are output without applying number formatting.

Parameters

  • sheet (str) –

    Name of data sheet

    • Empty str : First data sheet

  • column (int) – 0-based column of the table

  • row (int) – 0-based row of the table

  • format (int) –

    Output format to apply.

    When this parameter is not None, the result is forced to str with formatting applied from the cell settings.

    Valid values are:

    Any other value will raise a ValueError

Returns

The fetched value from the Excel file. Type depends on the type of the cell.

See also

getCellType()

The result is None when the cell has never has any content.

Return type

str | int | float | bool | None

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row, column or type have invalid values

  • CometError – On internal error

Available

CScript

excel::read

CExcelBook.setCellContent(sheet, column, row, value)

Set the content of a cell.

Parameters
Returns

None

Raises

  • TypeError – When parameter types are invalid

  • ValueError – When parameter row or column or have invalid values

  • CometError – On internal error

Available

CScript

excel::write

CExcelBook.save()

Save an Excel file.

Returns

None

Raises

CometError – On internal error

Available

CScript

excel::save

CExcelBook.close()

Close an Excel file and remove the object from memory.

Any subsequent calls to this object will raise an exception!

Returns

None

Raises

CometError – On internal error

Available

CScript

excel::close