comet.CExcelBook

class 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

getSheetCount()

Returns the number of sheets in the excel document.

Returns:

The number of sheets

Return type:

int

Raises:
Available:

InDesign® comet_pdf® Illustrator®

CScript:

excel::count_sheets

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::nth_sheet

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::first_row

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::last_row

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::first_col

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::last_col

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_type

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_formula

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_format

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 constants.kExportPlain

    Valid values are:

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_merge

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_rowheight

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_colwidth_pt

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_color

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_border

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::get_font

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::read

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::write

save()

Save an Excel file.

Returns:

None

Raises:

CometError – On internal error

Available:

InDesign® comet_pdf® Illustrator®

CScript:

excel::save

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::close

Special methods

__repr__()

String representation.

Returns:

<CExcelBook> File: File name | Sheets: Sheet count

Sheet 0: Sheet 0 name

…

[Sheet n: Sheet n name]

Examples
<CExcelBook> File: Mappe1.xlsx | Sheets: 1
Sheet 0: Tabelle1

Return type:

str