TXLSheet class reference

  • function getCellType(row: Integer; col: Integer): CellType
    Returns cell's type.
    CellType valueDescription
    CELLTYPE_EMPTYempty, the cell doesn't exist
    CELLTYPE_NUMBERnumber value
    CELLTYPE_STRINGstring value
    CELLTYPE_BOOLEANboolean value
    CELLTYPE_BLANKblank, the cell contains only format information
    CELLTYPE_ERRORerror
  • function isFormula(row: Integer; col: Integer): boolean
    Checks that cell contains a formula.
  • function cellFormat(row: Integer; col: Integer): TXLFormat
    Returns cell's format. It can be changed by user.
  • procedure setCellFormat(row: Integer; col: Integer; format: TXLFormat)
    Sets cell's format.
  • function readStr(row: Integer; col: Integer): WideString
    Reads a string from cell.
  • function readStr(row: Integer; col: Integer; var format: TXLFormat): WideString
    Reads a string and its format from cell.
  • function writeStr(row: Integer; col: Integer; value: PWideChar): boolean
    Writes a string into cell.
  • function writeStr(row: Integer; col: Integer; value: PWideChar; format: TXLFormat): boolean
    Writes a string into cell with specified format.
  • function readRichStr(row: Integer; col: Integer): TXLRichString
    Reads a rich string with multiple fonts from the cell. It's possible to check if the specified cell contains a rich string with the Sheet::isRichString() method.
  • function readRichStr(row: Integer; col: Integer; var format: TXLFormat): TXLRichString
    Reads a rich string with multiple fonts and its format from the cell. It's possible to check if the specified cell contains a rich string with the Sheet::isRichString() method.
  • function writeRichStr(row: Integer; col: Integer; value: TRichString): boolean
    Writes a rich string with multiple fonts into the cell with the specified format. Add a new rich string with the Book::addRichString() method. Returns false if an error occurs.
  • function writeRichStr(row: Integer; col: Integer; value: TRichString; format: TFormat): boolean
    Writes a rich string with multiple fonts into the cell with the specified format. Add a new rich string with the Book::addRichString() method. Returns false if an error occurs.
  • function readNum(row: Integer; col: Integer): double
    Reads a number or date/time from the cell. Use Book::dateUnpack() for extract date/time parts from double.
  • function readNum(row: Integer; col: Integer; var format: TXLFormat): double
    Reads a number or date/time and its format from cell. Use Book::dateUnpack() for extract date/time parts from double.
  • function writeNum(row: Integer; col: Integer; value: double): boolean
    Writes a number or date/time into the cell. Use the Book::datePack() for packing date/time parts to double.
  • function writeNum(row: Integer; col: Integer; value: double; format: TXLFormat): boolean
    Writes a number or date/time into cell with specified format. Use Book::datePack() for packing date/time parts to double.
  • function readBool(row: Integer; col: Integer): boolean
    Reads a bool value from cell.
  • function readBool(row: Integer; col: Integer; var format: TXLFormat): boolean
    Reads a bool value and its format from cell.
  • function writeBool(row: Integer; col: Integer; value: boolean): boolean
    Writes a bool value into cell.
  • function writeBool(row: Integer; col: Integer; value: boolean; format: TFormat): boolean
    Writes a bool value into cell with specified format.
  • function readBlank(row: Integer; col: Integer; var format: TXLFormat): boolean
    Reads format from blank cell.
  • function writeBlank(row: Integer; col: Integer; format: TXLFormat): boolean
    Writes blank cell with specified format.
  • function readFormula(row: Integer; col: Integer): WideString
    Reads a formula string from cell.
  • function readFormula(row: Integer; col: Integer; var format: TXLFormat): WideString
    Reads a formula string and its format from cell.
  • function writeFormula(row: Integer; col: Integer; value: PWideChar): boolean
    Writes a formula into cell.
  • procedure writeFormula(row: Integer; col: Integer; value: PWideChar; format: TXLFormat)
    Writes a formula into cell with specified format.
  • function writeFormulaNum(row: Integer; col: Integer; expr: PWideChar; value: double): boolean
    Writes a formula expression with precalculated double value into cell.
  • procedure writeFormulaNum(row: Integer; col: Integer; expr: PWideChar; value: double; format: TXLFormat)
    Writes a formula expression with precalculated double value into cell with specified format.
  • function writeFormulaStr(row: Integer; col: Integer; expr: PWideChar; value: PWideChar): boolean
    Writes a formula expression with precalculated string value into cell.
  • function writeFormulaStr(row: Integer; col: Integer; expr: PWideChar; value: PWideChar; format: TXLFormat): boolean
    Writes a formula expression with precalculated string value into cell with specified format.
  • function writeFormulaBool(row: Integer; col: Integer; expr: PWideChar; value: boolean): boolean
    Writes a formula expression with precalculated boolean value into cell.
  • function writeFormulaBool(row: Integer; col: Integer; expr: PWideChar; value: boolean; format: TXLFormat): boolean
    Writes a formula expression with precalculated boolean value into cell with specified format.
  • function readComment(row: Integer; col: Integer): WideString
    Reads a comment from specified cell (only for xls format).
  • procedure writeComment(row: Integer; col: Integer; value: PWideChar; author: PWideChar; width, height: Integer)
    Writes a comment to the cell (only for xls format).
    Parameters:
    (row, col) - cell's position;
    value - comment string;
    author - author string;
    width - width of text box in pixels;
    height - height of text box in pixels.
  • procedure removeComment(row: Integer; col: Integer)
    Removes a comment from the cell (only for xls format).
  • function isDate(row, col: Integer): boolean
    Checks that the cell contains a date or time value. If return value is true read it with the Sheet::readNum() method and unpack it with the Book::dateUnpack() method.
  • function isRichStr(row, col: Integer): boolean
    Checks that the cell contains a rich string with multiple fonts. If return value is true read it with the Sheet::readRichStr() method.
  • function readError(row, col: Integer): ErrorType
    Reads error from cell.
    ErrorType valueDescription
    ERRORTYPE_NULL#NULL!
    ERRORTYPE_DIV_0#DIV/0!
    ERRORTYPE_VALUE#VALUE!
    ERRORTYPE_REF#REF!
    ERRORTYPE_NAME#NAME?
    ERRORTYPE_NUM#NUM!
    ERRORTYPE_NA#N/A
    ERRORTYPE_NOERRORno error
  • procedure writeError(row, col: Integer; error: ErrorType)
    Writes error into the cell.
  • procedure writeError(row, col: Integer; error: ErrorType; format: TXLFormat)
    Writes error into the cell with specified format.
  • function colWidth(col: Integer): double
    Returns column width. Column width is measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font.
  • function rowHeight(col: Integer): double
    Returns row height in typographical points. Point is 1/72 inch.
  • function colWidthPx(col: Integer): Integer
    Returns column width in pixels.
  • function rowHeightPx(col: Integer): Integer
    Returns row height in pixels.
  • function colFormat(col: Integer): TXLFormat
    Returns column's format.
  • function rowFormat(row: Integer): TXLFormat
    Returns row's format.
  • function setCol(col: Integer; width: double): boolean
    Sets column width for column col. Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. Value -1 is used for autofit column widths.
  • function setCol(colFirst, colLast: Integer; width: double): boolean
    Sets column width for all columns from colFirst to colLast. Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font.
  • function setCol(col: Integer; width: double; format: TXLFormat): boolean
    Sets column width and format for colum col. Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font.
  • function setCol(colFirst, colLast: Integer; width: double; format: TXLFormat): boolean
    Sets column width and format for all columns from colFirst to colLast. Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font.
  • function setCol(col: Integer; width: double; format: TXLFormat; hidden: boolean): boolean
    Sets column width and format for column col. Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. Column may be hidden.
  • function setCol(colFirst, colLast: Integer; width: double; format: TXLFormat; hidden: boolean): boolean
    Sets column width and format for all columns from colFirst to colLast. Column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. Columns may be hidden.
  • function setColPx(col: Integer; widthPx: Integer): boolean
    Sets column width in pixels for column col. Value -1 is used for autofit column widths.
  • function setColPx(colFirst, colLast: Integer; widthPx: Integer): boolean
    Sets column width in pixels for all columns from colFirst to colLast.
  • function setColPx(col: Integer; widthPx: Integer; format: TXLFormat): boolean
    Sets column width in pixels and format for colum col.
  • function setColPx(colFirst, colLast: Integer; widthPx: Integer; format: TXLFormat): boolean
    Sets column width in pixels and format for all columns from colFirst to colLast.
  • function setColPx(col: Integer; widthPx: Integer; format: TXLFormat; hidden: boolean): boolean
    Sets column width in pixels and format for column col. Column may be hidden.
  • function setColPx(colFirst, colLast: Integer; widthPx: Integer; format: TXLFormat; hidden: boolean): boolean
    Sets column width in pixels and format for all columns from colFirst to colLast. Columns may be hidden.
  • function setRow(row: Integer; height: double): boolean
    Sets row height. Row height measured in point size.
  • function setRow(row: Integer; height: double; format: TXLFormat): boolean
    Sets row height and format. Row height measured in point size.
  • function setRow(row: Integer; height: double; format: TXLFormat; hidden: boolean): boolean
    Sets row height and format. Row height measured in point size. Row may be hidden.
  • function setRowPx(row: Integer; heightPx: Integer): boolean
    Sets row height in pixels.
  • function setRowPx(row: Integer; heightPx: Integer; format: TXLFormat): boolean
    Sets row height in pixels and format.
  • function setRowPx(row: Integer; heightPx: Integer; format: TXLFormat; hidden: boolean): boolean
    Sets row height in pixels and format. Row may be hidden.
  • function rowHidden(row: Integer): boolean
    Returns whether row is hidden.
  • function setRowHidden(row: Integer; hidden: boolean): boolean
    Hides row.
  • function colHidden(row: Integer): boolean
    Returns whether column is hidden.
  • function setColHidden(col: Integer; hidden: boolean): boolean
    Hides column.
  • function defaultRowHeight(): double
    Returns the default row height measured in point size.
  • procedure setDefaultRowHeight(height: double)
    Sets the default row height measured in point size.
  • function getMerge(row, col: Integer; var rowFirst, rowLast, colFirst, colLast: Integer): boolean
    Gets merged cells for cell at row, col. Result is written in rowFirst, rowLast, colFirst, colLast.
  • function setMerge(rowFirst, rowLast, colFirst, colLast: Integer): boolean
    Sets merged cells for range: rowFirst - rowLast, colFirst - colLast.
  • function delMerge(row, col: Integer): boolean
    Removes merged cells.
  • function mergeSize(): Integer
    Returns a number of merged cells in this worksheet.
  • function merge(index: Integer; var rowFirst, rowLast, colFirst, colLast: Integer): boolean
    Gets the merged cells by index.
  • function delMergeByIndex(index: Integer): boolean
    Removes merged cells by index.
  • function pictureSize(): Integer
    Returns a number of pictures in this worksheet.
  • function getPicture(index: Integer): Integer
    Returns a workbook picture index at position index in worksheet.
    Use Book::getPicture() for extracting binary data of picture by workbook picture index.
  • function getPicture(index: Integer; var rowTop, colLeft, rowBottom, colRight: Integer): Integer
    Returns a workbook picture index at position index in worksheet. Output parameters:
    (rowTop, colLeft) - top left position of picture;
    (rowBottom, colRight) - bottom right position of picture.
    Use Book::getPicture() for extracting binary data of picture by workbook picture index.
  • function getPicture(index: Integer; var rowTop, colLeft, rowBottom, colRight, width, height: Integer): Integer
    Returns a workbook picture index at position index in worksheet. Output parameters:
    (rowTop, colLeft) - top left position of picture;
    (rowBottom, colRight) - bottom right position of picture;
    width - width of picture in pixels;
    height - height of picture in pixels.
    Use Book::getPicture() for extracting binary data of picture by workbook picture index.
  • function getPicture(index: Integer; var rowTop, colLeft, rowBottom, colRight, width, height, offset_x, offset_y: Integer): Integer
    Returns a workbook picture index at position index in worksheet. Output parameters:
    (rowTop, colLeft) - top left position of picture;
    (rowBottom, colRight) - bottom right position of picture;
    width - width of picture in pixels;
    height - height of picture in pixels;
    offset_x - horizontal offset of picture in pixels;
    offset_y - vertical offset of picture in pixels.
    Use Book::getPicture() for extracting binary data of picture by workbook picture index.
  • function removePictureByIndex(index: Integer): boolean
    Removes a picture from the worbook by index.
  • procedure setPicture(row, col, pictureId: Integer)
    Sets a picture with pictureId identifier at position row and col. Use Book::addPicture() for adding a new picture and getting an identifier.
  • procedure setPicture(row, col, pictureId: Integer; scale: double)
    Sets a picture with pictureId identifier at position row and col with scale factor. Use Book::addPicture() for adding a new picture and getting an identifier. The picture can be aligned to the left top corner of the cell, to the center of the cell or stretched inside the cell:
    scale > 0 - the picture is aligned to the left top corner of the cell or merged area with the specified scale factor
    scale = 0 - the picture is stretched inside the specified cell or merged area
    scale < 0 - the picture is aligned to the center of the cell or merged area with the specified scale factor
  • procedure setPicture(row, col, pictureId, width, height: Integer)
    Sets a picture with pictureId identifier at position row and col with custom size. Use Book::addPicture() for adding a new picture and getting an identifier.
  • function removePicture(row, col: Integer): boolean
    Removes a picture from the worbook at the specified position.
  • function getHorPageBreak(index: Integer): Integer
    Returns row with horizontal page break at position index.
  • function getHorPageBreakSize(): Integer
    Returns a number of horizontal page breaks in the sheet.
  • function getVerPageBreak(index: Integer): Integer
    Returns column with vertical page break at position index.
  • function getVerPageBreakSize(): Integer
    Returns a number of vertical page breaks in the sheet.
  • function setHorPageBreak(row: Integer): boolean
    Sets a horizontal page break.
  • function delHorPageBreak(row: Integer): boolean
    Removes a horizontal page break.
  • function setVerPageBreak(col: Integer): boolean
    Sets a vertical page break.
  • function delVerPageBreak(col: Integer): boolean
    Removes a vertical page break.
  • procedure split(row, col: Integer)
    Splits a sheet at position (row, col) or specifies the position of frozen pane. This function allows to freeze a header at top position or freeze some columns on the right.
  • function splitInfo(var row, col: Integer): boolean
    Gets the split information (position of frozen pane) in the sheet:
    row - vertical position of the split;
    col - horizontal position of the split.
  • function groupRows(rowFirst, rowLast: Integer; collapsed: boolean): boolean
    Groups rows from rowFirst to rowLast.
  • function groupCols(colFirst, colLast: Integer; collapsed: boolean): boolean
    Groups columns from colFirst to colLast.
  • property groupSummaryBelow: boolean
    If true then grouping rows summary is below else summary is above.
  • property groupSummaryRight: boolean
    If true then grouping columns summary is right else summary is left.
  • function clear(rowFirst, rowLast: Integer; colFirst, colLast: Integer): boolean
    Clears all cells in the specified area.
  • function insertRow(rowFirst, rowLast: Integer): boolean
    Inserts rows from rowFirst to rowLast.
  • function insertCol(colFirst, colLast: Integer): boolean
    Inserts columns from colFirst to colLast.
  • function removeRow(rowFirst, rowLast: Integer): boolean
    Removes rows from rowFirst to rowLast.
  • function removeCol(colFirst, colLast: Integer): boolean
    Removes columns from colFirst to colLast.
  • function copyCell(rowSrc, colSrc, rowDst, colDst: Integer): boolean
    Copies cell with format from (rowSrc, colSrc) to (rowDst, colDst).
  • function firstRow: Integer
    Returns the zero-based index of the first row in the sheet that contains a used cell, including blank cells only with formatting.
  • function lastRow: Integer
    Returns the zero-based index of the row after the last row in the sheet that contains a used cell, including blank cells only with formatting.
  • function firstCol: Integer
    Returns the zero-based index of the first column in the sheet that contains a used cell, including blank cells only with formatting.
  • function lastCol: Integer
    Returns the zero-based index of the column after the last column in the sheet that contains a used cell, including blank cells only with formatting.
  • function firstFilledRow: Integer
    Returns the zero-based index of the first row in the sheet that contains a filled cell with a value. Ignores blank cells only with formatting.
  • function lastFilledRow: Integer
    Returns the zero-based index of the row after the last row in the sheet that contains a filled cell with a value. Ignores blank cells only with formatting.
  • function firstFilledCol: Integer
    Returns the zero-based index of the first column in the sheet that contains a filled cell with a value. Ignores blank cells only with formatting.
  • function lastFilledCol: Integer
    Returns the zero-based index of the column after the last column in the sheet that contains a filled cell with a value. Ignores blank cells only with formatting.
  • property displayGridlines: boolean
    Display gridlines property: true if gridlines are displayed and false if aren't.
  • property printGridlines: boolean
    Print gridlines property: true if gridlines are printed and false if aren't.
  • property zoom: Integer
    The zoom level of the current view as a percentage. 100 is a usual view.
  • property printZoom: Integer
    The scaling factor for printing as a percentage.
  • function getPrintFit(var wPages, hPages: Integer): boolean
    Returns whether fit to page option is enabled. Output parameters:
    wPages - number of pages the sheet width is fit to;
    hPages - number of pages the sheet height is fit to.
  • procedure setPrintFit(wPages, hPages: Integer)
    Fits sheet width and sheet height to wPages and hPages respectively.
  • property landscape: boolean
    Page orientation mode: true - landscape mode, false - portrait mode.
  • property paper: Paper
    The paper size.
    Paper valueDescription
    PAPER_DEFAULTDefault paper size
    PAPER_LETTERUS Letter 8 1/2 x 11 in
    PAPER_LETTERSMALLUS Letter Small 8 1/2 x 11 in
    PAPER_TABLOIDUS Tabloid 11 x 17 in
    PAPER_LEDGERUS Ledger 17 x 11 in
    PAPER_LEGALUS Legal 8 1/2 x 14 in
    PAPER_STATEMENTUS Statement 5 1/2 x 8 1/2 in
    PAPER_EXECUTIVEUS Executive 7 1/4 x 10 1/2 in
    PAPER_A3A3 297 x 420 mm
    PAPER_A4A4 210 x 297 mm
    PAPER_A4SMALLA4 Small 210 x 297 mm
    PAPER_A5A5 148 x 210 mm
    PAPER_B4B4 (JIS) 250 x 354
    PAPER_B5B5 (JIS) 182 x 257 mm
    PAPER_FOLIOFolio 8 1/2 x 13 in
    PAPER_QUATROQuarto 215 x 275 mm
    PAPER_10x1410 x 14 in
    PAPER_10x1711 x 17 in
    PAPER_NOTEUS Note 8 1/2 x 11 in
    PAPER_ENVELOPE_9US Envelope #9 3 7/8 x 8 7/8
    PAPER_ENVELOPE_10US Envelope #10 4 1/8 x 9 1/2
    PAPER_ENVELOPE_11US Envelope #11 4 1/2 x 10 3/8
    PAPER_ENVELOPE_12US Envelope #12 4 3/4 x 11
    PAPER_ENVELOPE_14US Envelope #14 5 x 11 1/2
    PAPER_C_SIZEC size sheet
    PAPER_D_SIZED size sheet
    PAPER_E_SIZEE size sheet
    PAPER_ENVELOPE_DLEnvelope DL 110 x 220mm
    PAPER_ENVELOPE_C5Envelope C5 162 x 229 mm
    PAPER_ENVELOPE_C3Envelope C3 324 x 458 mm
    PAPER_ENVELOPE_C4Envelope C4 229 x 324 mm
    PAPER_ENVELOPE_C6Envelope C6 114 x 162 mm
    PAPER_ENVELOPE_C65Envelope C65 114 x 229 mm
    PAPER_ENVELOPE_B4Envelope B4 250 x 353 mm
    PAPER_ENVELOPE_B5Envelope B5 176 x 250 mm
    PAPER_ENVELOPE_B6Envelope B6 176 x 125 mm
    PAPER_ENVELOPEEnvelope 110 x 230 mm
    PAPER_ENVELOPE_MONARCHUS Envelope Monarch 3.875 x 7.5 in
    PAPER_US_ENVELOPEUS Envelope 3 5/8 x 6 1/2 in
    PAPER_FANFOLDUS Std Fanfold 14 7/8 x 11 in
    PAPER_GERMAN_STD_FANFOLDGerman Std Fanfold 8 1/2 x 12 in
    PAPER_GERMAN_LEGAL_FANFOLDGerman Legal Fanfold 8 1/2 x 13 in
  • property header: AnsiString
    The header text of the sheet when printed. The text appears at the top of every page when printed. The length of the text must be less than or equal to 255. The header text can contain special commands, for example a placeholder for the page number, current date or text formatting attributes. Special commands are represented by single letter with a leading ampersand ("&").
    CodeDescription
    &Lspecifies the beginning of the left section
    &Pspecifies the current page number
    &Nspecifies the total number of pages
    &\d{1,3}specifies the text font size, where font size is measured in points, for example: &9 or &36
    &Sspecifies whether the strikethrough text style is on or off
    &Xspecifies whether the superscript text style is on or off
    &Yspecifies whether the subscript text style is on or off
    &Cspecifies the beginning of the center section
    &Dspecifies a date
    &Tspecifies a time
    &Uspecifies whether the single underline text style is on or off
    &Especifies whether the double underline text style is on or off
    &Rspecifies the beginning of the right section
    &Zspecifies a workbook file path
    &Fspecifies a workbook file name
    &Aspecifies a sheet name
    &"fontname"specifies the text font, for example: &"Comic Sans MS"
    &Bspecifies whether the bold text style is on or off
    &Ispecifies whether the italic text style is on or off
    &&specifies an ampersand character (&)
  • property headerMargin: double
    The header margin in inches.
  • property footer: AnsiString
    The footer text of the sheet when printed. The footer text appears at the bottom of every page when printed. The length of the text must be less than or equal to 255. The footer text can contain special commands, for example a placeholder for the page number, current date or text formatting attributes. See header for details.
  • property footerMargin: double
    The footer margin in inches.
  • property hCenter: boolean
    If true then the sheet is centered horizontally when printed.
  • property vCenter: boolean
    If true then the sheet is centered vertically when printed.
  • property marginLeft: double
    The left margin of the sheet in inches.
  • property marginRight: double
    The right margin of the sheet in inches.
  • property marginTop: double
    The top margin of the sheet in inches.
  • property marginBottom: double
    The bottom margin of the sheet in inches.
  • property printRowCol: boolean
    If true then the row and column headers are printed.
  • function printRepeatRows(var rowFirst, rowLast: Integer): boolean
    Gets repeated rows on each page from rowFirst to rowLast. Returns false if repeated rows aren't found.
  • procedure setPrintRepeatRows(rowFirst, rowLast: Integer)
    Sets repeated rows on each page from rowFirst to rowLast.
  • function printRepeatCols(var colFirst, colLast: Integer): boolean
    Gets repeated columns on each page from colFirst to colLast. Returns false if repeated columns aren't found.
  • procedure setPrintRepeatCols(colFirst, colLast: Integer)
    Sets repeated columns on each page from colFirst to colLast.
  • function printArea(var rowFirst, rowLast, colFirst, colLast: Integer): boolean
    Gets the print area. Returns false if print area isn't found.
  • procedure setPrintArea(rowFirst, rowLast, colFirst, colLast: Integer)
    Sets the print area.
  • procedure clearPrintRepeats()
    Clears repeated rows and columns on each page.
  • procedure clearPrintArea()
    Clears the print area.
  • function getNamedRange(const name: PWideChar; var rowFirst, rowLast, colFirst, colLast: Integer): boolean
    Gets the named range coordianates by name. Returns false if specified named range isn't found.
  • function getNamedRange(const name: PWideChar; var rowFirst, rowLast, colFirst, colLast: Integer; scopeId: Integer): boolean
    Gets the named range coordianates by local or global name.
    scopeId - index of sheet for local named range or -1 for a global named range.
    Returns false if specified named range isn't found.
  • function getNamedRange(const name: PWideChar; var rowFirst, rowLast, colFirst, colLast: Integer; scopeId: Integer; var hidden: boolean): boolean
    Gets the named range coordianates by local or global name.
    scopeId - index of sheet for local named range or -1 for a global named range.
    hidden - true if named range is hidden and false if isn't.
    Returns false if specified named range isn't found.
  • procedure setNamedRange(const name: PWideChar; rowFirst, rowLast, colFirst, colLast: Integer)
    Sets the named range.
  • procedure setNamedRange(const name: PWideChar; rowFirst, rowLast, colFirst, colLast, scopeId: Integer)
    Sets the named range.
    scopeId - index of sheet for local named range or -1 for a global named range.
  • procedure delNamedRange(const name: PWideChar)
    Deletes the named range by name.
  • procedure delNamedRange(const name: PWideChar; scopeId: Integer)
    Deletes the named range by name.
    scopeId - index of sheet for local named range or -1 for a global named range.
  • function namedRangeSize(): Integer
    Returns the number of named ranges in the sheet.
  • function namedRange(index: Integer; var rowFirst, rowLast, colFirst, colLast: Integer): WideString
    Gets the named range coordianates by index.
  • function namedRange(index: Integer; var rowFirst, rowLast, colFirst, colLast, scopeId: Integer): WideString
    Gets the named range coordianates by index.
    scopeId - index of sheet for local named range or -1 for a global named range.
  • function namedRange(index: Integer; var rowFirst, rowLast, colFirst, colLast, scopeId: Integer; var hidden: boolean): WideString
    Gets the named range coordianates by index.
    scopeId - index of sheet for local named range or -1 for a global named range.
    hidden - true if named range is hidden and false if isn't.
  • function getTable(const name: PWideChar; var rowFirst, rowLast, colFirst, colLast, headerRowCount, totalsRowCount: Integer): boolean
    Gets the table parameters by name.
    headerRowCount - the number of header rows showing at the top of the table. 0 means that the header row is not shown.
    totalsRowCount - the number of totals rows that shall be shown at the bottom of the table. 0 means that the totals row is not shown.
    Returns true if the table is found.
  • function tableSize(): Integer
    Returns the number of tables in the sheet.
  • function table(index: Integer; var rowFirst, rowLast, colFirst, colLast, headerRowCount, totalsRowCount: Integer): WideString
    Gets the table parameters by index.
    headerRowCount - the number of header rows showing at the top of the table. 0 means that the header row is not shown.
    totalsRowCount - the number of totals rows that shall be shown at the bottom of the table. 0 means that the totals row is not shown.
    Returns a string representing the name of the table.
  • Adds a table to the sheet with the specified name, range and style (only for xlsx files).
    name - the name of the table;
    rowFirst - the first row of the table;
    rowLast - the last row of the table;
    rowLast - the last row of the table;
    colFirst - the first column of the table;
    colLast - the last column of the table;
    hasHeaders - does the specified range include headers or not;
    tableStyle - the table style.
    Returns the table if successed, 0 if failed.
  • Gets a table by the specified name (only for xlsx files). Returns the table if successed, 0 if failed.
  • Gets a table by the specified index (only for xlsx files). Returns the table if successed, 0 if failed.
  • function hyperlinkSize(): Integer
    Returns the number of hyperlinks in the sheet.
  • function hyperlink(index: Integer; var rowFirst, rowLast, colFirst, colLast: Integer): WideString
    Gets the hyperlink and its coordianates by index.
  • function delHyperlink(index: Integer): boolean
    Removes hyperlink by index.
  • procedure addHyperlink(const hyperlink: PWideChar; rowFirst, rowLast, colFirst, colLast: Integer)
    Adds the new hyperlink.
  • function hyperlinkIndex(row, col: Integer): Integer
    Checks if the cell contains a hyperlink. Returns an index of hyperlink if exists, -1 if there is no hyperlink in this cell.
  • function isAutoFilter(): boolean
    Returns true if the AutoFilter already exists (only for xlsx files).
  • function autoFilter(): TXLAutoFilter
    Returns the AutoFilter. Creates it if it doesn't exist (only for xlsx files).
  • procedure applyFilter()
    Applies all available autofilters to the sheet (only for xlsx files).
  • Applies only the specified AutoFilter to the sheet (only for xlsx files).
  • procedure removeFilter()
    Removes the AutoFilter from the sheet (only for xlsx files).
  • property name: WideString
    The name of the sheet.
  • property protect: boolean
    If true then the sheet is protected.
  • procedure setProtect(protect: boolean; const password: PWideChar)
    Protects the sheet with the password.
  • procedure setProtect(protect: boolean; const password: PWideChar; const prot: EnhancedProtection)
    Protects the sheet with the password and enchanced parameters below.
    EnhancedProtection valueDescription
    PROT_DEFAULTDefault protection.
    PROT_ALLNothing is allowed except cell selections.
    PROT_OBJECTSObjects are locked when the sheet is protected.
    PROT_SCENARIOSScenarios are locked when the sheet is protected.
    PROT_FORMAT_CELLSFormatting cells is allowed when the sheet is protected.
    PROT_FORMAT_COLUMNSFormatting columns is allowed when the sheet is protected.
    PROT_FORMAT_ROWSFormatting rows is allowed when the sheet is protected.
    PROT_INSERT_COLUMNSInserting columns is allowed when the sheet is protected.
    PROT_INSERT_ROWSInserting rows is allowed when the sheet is protected.
    PROT_INSERT_HYPERLINKSInserting hyperlinks is allowed when the sheet is protected.
    PROT_DELETE_COLUMNSDeleting columns is allowed when the sheet is protected.
    PROT_DELETE_ROWSDeleting rows is allowed when the sheet is protected.
    PROT_SEL_LOCKED_CELLSSelection of locked cells is locked when the sheet is protected.
    PROT_SORTSorting is allowed when the sheet is protected.
    PROT_AUTOFILTERAutofilters are allowed when the sheet is protected.
    PROT_PIVOTTABLESPivot tables are allowed when the sheet is protected.
    PROT_SEL_UNLOCKED_CELLSSelection of unlocked cells is locked when the sheet is protected.
  • property rightToLeft: boolean
    Property that specifies whether the text is displayed in right-to-left mode:
    true - the text is displayed in right-to-left mode,
    false - the text is displayed in left-to-right mode.
  • property hidden: SheetState
    Specifies the visible state of this sheet.
    SheetState valueDescription
    SHEETSTATE_VISIBLEsheet is visible
    SHEETSTATE_HIDDENsheet is hidden, but can be shown via the user interface
    SHEETSTATE_VERYHIDDENsheet is hidden and cannot be shown in the user interface
  • procedure getTopLeftView(var row, col: Integer)
    Extracts the first visible row and the leftmost visible column of the sheet.
  • procedure setTopLeftView(row, col: Integer)
    Sets the first visible row and the leftmost visible column of the sheet.
  • procedure setAutoFitArea(rowFirst, colFirst, rowLast, colLast: Integer)
    Sets the borders for autofit column widths feature. The procedure Sheet::setCol() with -1 width value will affect only to the specified limited area.
  • procedure addrToRowCol(const addr: PWideChar; var row, col: Integer)
    Converts a cell reference to row and column:
    addr - the cell reference, can be relative and absolute, for example C5 or $C$5;
    row - the extracted row from the cell reference;
    col - the extracted column from the cell reference;
    rowRelative - true if row is relative, false if row is absolute;
    colRelative - true if column is relative, false if column is absolute.
  • procedure addrToRowCol(const addr: PWideChar; var row, col: Integer; var rowRelative, colRelative: boolean)
    Converts a cell reference to row and column:
    addr - the cell reference, can be relative and absolute, for example C5 or $C$5;
    row - the extracted row from the cell reference;
    col - the extracted column from the cell reference;
    rowRelative - true if row is relative, false if row is absolute;
    colRelative - true if column is relative, false if column is absolute.
  • function rowColToAddr(row, col: Integer): WideString
    Converts row and column to a cell reference:
    row - the row for the cell reference;
    col - the column for the cell reference;
    rowRelative - true if row should be relative, false if row should be absolute;
    colRelative - true if column should be relative, false if column should be absolute;
    Returns the cell reference, can be relative and absolute, for example C5 or $C$5.
  • function rowColToAddr(row, col: Integer; rowRelative, colRelative: boolean): WideString
    Converts row and column to a cell reference:
    row - the row for the cell reference;
    col - the column for the cell reference;
    rowRelative - true if row should be relative, false if row should be absolute;
    colRelative - true if column should be relative, false if column should be absolute;
    Returns the cell reference, can be relative and absolute, for example C5 or $C$5.
  • function tabColor(): Color
    Returns the sheet's tab color.
  • procedure setTabColor(color: Color)
    Sets the color for the sheet's tab.
  • function getTabColor(var red, green, blue: Integer): boolean
    Returns the sheet's tab RGB color.
  • procedure setTabColor(red: Integer; green: Integer; blue: Integer)
    Sets the RGB color for the sheet's tab.
  • function setBorder(rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; borderStyle: BorderStyle; borderColor: Color): boolean
    Sets the border in the specified range of cells.
    rowFirst - the first row of range;
    rowLast - the last row of range;
    colFirst - the first column of range;
    colLast - the last column of range;
    borderStyle - the border style;
    borderColor - the border color.
  • function addIgnoredError(rowFirst: Integer; colFirst: Integer; rowLast: Integer; colLast: Integer; iError: IgnoredError): boolean
    Adds the ignored error for specified range. It allows to hide green triangles on left sides of cells. For example, if a cell is formatted as text but contains a numeric value, this is considered to be a potential error because the number won't be treated as a number, for example, in calculations. Returns false if error occurs.
    IgnoredError valueDescription
    IERR_EVAL_ERRORIgnore errors when cells contain formulas that result in an error.
    IERR_EMPTY_CELLREFIgnore errors when formulas refer to empty cells.
    IERR_NUMBER_STORED_AS_TEXTIgnore errors when numbers are formatted as text or are preceded by an apostrophe.
    IERR_INCONSIST_RANGEIgnore errors when formulas omit certain cells in a region.
    IERR_INCONSIST_FMLAIgnore errors when a formula in a region of your worksheet differs from other formulas in the same region.
    IERR_TWODIG_TEXTYEARIgnore errors when formulas contain text formatted cells with years represented as 2 digits.
    IERR_UNLOCK_FMLAIgnore errors when unlocked cells contain formulas.
    IERR_DATA_VALIDATIONIgnore errors when a cell's value in a Table does not comply with the Data Validation rules specified.
  • procedure addDataValidation(vtype: DataValidationType; op: DataValidationOperator; rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; const value: PWideChar)

    Adds a data validation for the specified range (only for xlsx files).

    type - the type of data validation:
    DataValidationType valueDescription
    VALIDATION_TYPE_NONENo data validation.
    VALIDATION_TYPE_WHOLEData validation which checks for whole number values satisfying the given condition.
    VALIDATION_TYPE_DECIMALData validation which checks for decimal values satisfying the given condition.
    VALIDATION_TYPE_LISTData validation which checks for a value matching one of list of values.
    VALIDATION_TYPE_DATEData validation which checks for date values satisfying the given condition.
    VALIDATION_TYPE_TIMEData validation which checks for time values satisfying the given condition.
    VALIDATION_TYPE_TEXTLENGTHData validation which checks for text values, whose length satisfies the given condition.
    VALIDATION_TYPE_CUSTOMData validation which uses a custom formula to check the cell value.

    op - the relational operator of data validation:
    DataValidationOperator valueDescription
    VALIDATION_OP_BETWEENData validation which checks if a value is between two other values.
    VALIDATION_OP_NOTBETWEENData validation which checks if a value is not between two other values.
    VALIDATION_OP_EQUALData validation which checks if a value is equal to a specified value.
    VALIDATION_OP_NOTEQUALData validation which checks if a value is not equal to a specified value.
    VALIDATION_OP_LESSTHANData validation which checks if a value is less than a specified value.
    VALIDATION_OP_LESSTHANOREQUALData validation which checks if a value is less than or equal to a specified value.
    VALIDATION_OP_GREATERTHANData validation which checks if a value is greater than a specified value.
    VALIDATION_OP_GREATERTHANOREQUALData validation which checks if a value is greater than or equal to a specified value.

    rowFirst - the first row of range;
    rowLast - the last row of range;
    colFirst - the first column of range;
    colLast - the last column of range;
    value - the value for relational operator, use double quotes if you want to specify a list of values directly
    (for example "A,B,C") and don't use quotes if you want to specify a reference to area with values
    (for example A1:A6).
  • procedure addDataValidation(vtype: DataValidationType; op: DataValidationOperator; rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; const value1: PWideChar; const value2: PWideChar)

    Adds a data validation for the specified range with two values for relational operator (only for xlsx files). See parameter description in the Sheet::addDataValidation() function.
  • procedure addDataValidation(vtype: DataValidationType; op: DataValidationOperator; rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; const value1: PWideChar; const value2: PWideChar; allowBlank: boolean; hideDropDown: boolean; showInputMessage: boolean; showErrorMessage: boolean; const promptTitle: PWideChar; const prompt: PWideChar; const errorTitle: PWideChar; const error: PWideChar; errorStyle: DataValidationErrorStyle)

    Adds a data validation for the specified range with extended parameters (only for xlsx files).

    type, op, rowFirst, rowLast, colFirst, colLast, value1, value2 - see description in Sheet::addDataValidation();
    allowBlank - a boolean value indicating whether the data validation treats empty or blank entries as valid, 'true' means empty entries are OK and do not violate the validation constraints;
    hideDropDown - a boolean value indicating whether to display the dropdown combo box for a list type data validation (VALIDATION_TYPE_LIST);
    showInputMessage - a boolean value indicating whether to display the input prompt message;
    showErrorMessage - a boolean value indicating whether to display the error alert message when an invalid value has been entered, according to the criteria specified;
    promptTitle - title bar text of input prompt;
    prompt - message text of input prompt;
    errorTitle - title bar text of error alert;
    error - message text of error alert;
    errorStyle - the style of error alert used for this data validation:
    DataValidationErrorStyle valueDescription
    VALIDATION_ERRSTYLE_STOPThis data validation error style uses a stop icon in the error alert.
    VALIDATION_ERRSTYLE_WARNINGThis data validation error style uses a warning icon in the error alert.
    VALIDATION_ERRSTYLE_INFORMATIONThis data validation error style uses an information icon in the error alert.

  • procedure addDataValidationDouble(vtype: DataValidationType; op: DataValidationOperator; rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; value: double)

    Adds a data validation for the specified range with double or date value for the relational operator (only for xlsx files). See parameters in the Sheet::addDataValidation() method.
  • procedure addDataValidationDouble(vtype: DataValidationType; op: DataValidationOperator; rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; value1: double; value2: double)

    Adds a data validation for the specified range with double or date values for the relational operator (only for xlsx files). See parameters in the Sheet::addDataValidation() method.
  • procedure addDataValidationDouble(vtype: DataValidationType; op: DataValidationOperator; rowFirst: Integer; rowLast: Integer; colFirst: Integer; colLast: Integer; value1: double; value2: double; allowBlank: boolean; hideDropDown: boolean; showInputMessage: boolean; showErrorMessage: boolean; const promptTitle: PWideChar; const prompt: PWideChar; const errorTitle: PWideChar; const error: PWideChar; errorStyle: DataValidationErrorStyle)

    Adds a data validation for the specified range with double or date values for the relational operator with extended parameters (only for xlsx files). See parameters in the Sheet::addDataValidation() method.
  • procedure removeDataValidations()
    Removes all data validations for the sheet (only for xlsx files).
  • function formControlSize(): Integer
    Returns a number of form controls in this worksheet (only for xlsx files).
  • function formControl(index: Integer): TXLFormControl
    Returns a form control with the specified index (only for xlsx files). The index must be less than the return value of the formControlSize() method.
  • function addConditionalFormatting(): TXLConditionalFormatting
    Adds a conditional formatting rules to the sheet (only for xlsx files).
  • function getActiveCell(var row, col: Integer): boolean
    Gets an active cell of the sheet. Returns true if an active cell is found otherwise returns false.
  • procedure setActiveCell(row, col: Integer)
    Sets an active cell of the sheet.
  • function selectionRange(): WideString
    Returns a range of the selection.
  • procedure addSelectionRange(sqref: PWideChar)
    Adds a range to the selection.
  • procedure removeSelection()
    Removes all selection.
Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl ConditionalFormatting 
ConditionalFormat CoreProperties Table Examples