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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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 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

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::write

CExcelBook.save()

Save an Excel file.

Returns:

None

Raises:

CometError – On internal error

Available:

InDesign® comet_pdf® Illustrator®

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:

InDesign® comet_pdf® Illustrator®

CScript:

excel::close

Special methods

CExcelBook.__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