Sheet class reference

  • 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
  • Checks that cell contains a formula.
  • Returns cell's format. It can be changed by user.
  • Sets cell's format.
  • Reads a comment from specified cell (only for xls format).
  • 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.
  • Removes a comment from the cell (only for xls format).
  • 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.
  • Checks that the cell contains a rich string with multiple fonts. If return value is true read it with the Sheet::readRichStr() method.
  • 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
  • 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.
  • Returns row height in typographical points. Point is 1/72 inch.
  • Returns column width in pixels.
  • Returns row height in pixels.
  • Returns column's format.
  • Returns row's format.
  • Returns whether row is hidden.
  • Hides row.
  • Returns whether column is hidden.
  • Hides column.
  • Returns the default row height measured in point size.
  • Sets the default row height measured in point size.
  • Returns a number of merged cells in this worksheet.
  • Gets the merged cells by index.
  • Removes merged cells by index.
  • Returns a number of pictures in this worksheet.
  • Returns row with horizontal page break at position index.
  • Returns a number of horizontal page breaks in the sheet.
  • Returns column with vertical page break at position index.
  • Returns a number of vertical page breaks in the sheet.
  • 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.
  • Gets the split information (position of frozen pane) in the sheet:
    row - vertical position of the split;
    col - horizontal position of the split.
  • Clears all cells in the specified area.
  • Returns the zero-based index of the first row in the sheet that contains a used cell, including blank cells only with formatting.
  • 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.
  • Returns the zero-based index of the first column in the sheet that contains a used cell, including blank cells only with formatting.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • Sets gridlines for displaying, true - gridlines are displayed, false - gridlines aren't displayed.
  • Sets gridlines for printing, true - gridlines are printed, false - gridlines aren't printed.
  • Sets the zoom level of the current view. 100 is a usual view.
  • Sets the scaling factor for printing as a percentage.
  • 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.
  • Fits sheet width and sheet height to wPages and hPages respectively.
  • Sets landscape or portrait mode for printing, true - pages are printed using landscape mode, false - pages are printed using portrait mode.
  • Sets 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
  • Sets 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 ("&"). Margin is specified in inches.
    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 (&)
  • Sets the footer text for 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 setHeader for details. Margin is specified in inches.
  • Sets a flag that the sheet is centered horizontally when printed.
  • Sets a flag that the sheet is centered vertically when printed.
  • Sets the left margin of the sheet in inches.
  • Sets the right margin of the sheet in inches.
  • Sets the top margin of the sheet in inches.
  • Sets the bottom margin of the sheet in inches.
  • Sets a flag that the row and column headers are printed.
  • Sets repeated rows on each page from rowFirst to rowLast.
  • Sets repeated columns on each page from colFirst to colLast.
  • Sets the print area.
  • Clears repeated rows and columns on each page.
  • Clears the print area.
  • Returns the number of named ranges in the sheet.
  • 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.
  • Returns the number of tables in the sheet.
  • 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.
  • Returns the number of hyperlinks in the sheet.
  • Gets the hyperlink and its coordianates by index.
  • Removes hyperlink by index.
  • Adds the new hyperlink.
  • Checks if the cell contains a hyperlink. Returns an index of hyperlink if exists, -1 if there is no hyperlink in this cell.
  • Returns true if the AutoFilter already exists (only for xlsx files).
  • Returns the AutoFilter. Creates it if it doesn't exist (only for xlsx files).
  • Applies all available autofilters to the sheet (only for xlsx files).
  • Applies only the specified AutoFilter to the sheet (only for xlsx files).
  • Removes the AutoFilter from the sheet (only for xlsx files).
  • Sets the name of the sheet. You can't use the following characters in sheet names:
    - colon (:)
    - backslash (\)
    - asterisk (*)
    - question mark (?)
    - forward slash (/)
    - opening square bracket ([)
    - closing square bracket (])
    The sheet name must not begin or end with the single quote (') character.
  • Extracts the first visible row and the leftmost visible column of the sheet.
  • Sets the first visible row and the leftmost visible column of the sheet.
  • 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.
  • 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.
  • Returns the sheet's tab color.
  • Sets the color for the sheet's tab.
  • Returns the sheet's tab RGB color.
  • Sets the RGB color for the sheet's tab.
  • Returns a number of data validations in this worksheet (only for xlsx files).
  • Returns a number of form controls in this worksheet (only for xlsx files).
  • Adds conditional formatting rules to the sheet (only for xlsx files).
  • Returns conditional formatting rules with the specified index (only for xlsx files). The index must be less than the return value of the conditionalFormattingSize() method.
  • Removes conditional formatting rules with the specified index for the sheet (only for xlsx files).
  • Returns a number of conditional formatting rules in this worksheet (only for xlsx files).
  • Gets an active cell of the sheet. Returns true if an active cell is found otherwise returns false.
  • Sets an active cell of the sheet.
  • Returns a range of the selection.
  • Adds a range to the selection.
  • Removes all selection.
Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl ConditionalFormatting 
ConditionalFormat CoreProperties Table Examples