Sheet class reference

  • public CellType cellType(int row, int col)
    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
  • public bool isFormula(int row, int col)
    Checks that cell contains a formula.
  • public Format cellFormat(int row, int col)
    Returns cell's format. It can be changed by user.
  • public void setCellFormat(int row, int col, Format format)
    Sets cell's format.
  • public string readStr(int row, int col)
    Reads a string from cell.
  • public string readStr(int row, int col, ref Format format)
    Reads a string and its format from cell.
  • public bool writeStr(int row, int col, string value)
    Writes a string into cell.
  • public bool writeStr(int row, int col, string value, Format format)
    Writes a string into cell with specified format.
  • public RichString readRichStr(int row, int col)
    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.
  • public RichString readRichStr(int row, int col, ref Format format)
    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.
  • public bool writeRichStr(int row, int col, RichString value)
    Writes a rich string with multiple fonts into the cell. Add a new rich string with the Book::addRichString() method. Returns false if an error occurs.
  • public bool writeRichStr(int row, int col, RichString value, Format format)
    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.
  • public double readNum(int row, int col)
    Reads a number or date/time from the cell. Use Book::dateUnpack() for extract date/time parts from double.
  • public double readNum(int row, int col, ref Format format)
    Reads a number or date/time and its format from cell. Use Book::dateUnpack() for extract date/time parts from double.
  • public bool writeNum(int row, int col, double value)
    Writes a number or date/time into the cell. Use the Book::datePack() for packing date/time parts to double.
  • public bool writeNum(int row, int col, double value, Format format)
    Writes a number or date/time into cell with specified format. Use Book::datePack() for packing date/time parts to double.
  • public bool readBool(int row, row col)
    Reads a bool value from cell.
  • public bool readBool(int row, row col, ref Format format)
    Reads a bool value and its format from cell.
  • public bool writeBool(int row, int col, bool value)
    Writes a bool value into cell.
  • public bool writeBool(int row, int col, bool value, Format format)
    Writes a bool value into cell with specified format.
  • public bool readBlank(int row, int col, ref Format format)
    Reads format from blank cell.
  • public bool writeBlank(int row, int col, Format format)
    Writes blank cell with specified format.
  • public string readFormula(int row, int col)
    Reads a formula string from cell.
  • public string readFormula(int row, int col, ref Format format)
    Reads a formula string and its format from cell.
  • public bool writeFormula(int row, int col, string value)
    Writes a formula into cell.
  • public bool writeFormula(int row, int col, string value, Format format)
    Writes a formula into cell with specified format.
  • public bool writeFormulaNum(int row, int col, string expr, double value)
    Writes a formula expression with precalculated double value into cell. Returns false if error occurs.
  • public bool writeFormulaNum(int row, int col, string expr, double value, Format format)
    Writes a formula expression with precalculated double value into cell with specified format. Returns false if error occurs.
  • public bool writeFormulaStr(int row, int col, string expr, string value)
    Writes a formula expression with precalculated string value into cell. Returns false if error occurs.
  • public bool writeFormulaStr(int row, int col, string expr, string value, Format format)
    Writes a formula expression with precalculated string value into cell with specified format. Returns false if error occurs.
  • public bool writeFormulaBool(int row, int col, string expr, bool value)
    Writes a formula expression with precalculated bool value into cell. Returns false if error occurs.
  • public bool writeFormulaBool(int row, int col, string expr, bool value, Format format)
    Writes a formula expression with precalculated bool value into cell with specified format. Returns false if error occurs.
  • public string readComment(int row, int col)
    Reads a comment from specified cell (only for xls format).
  • public void writeComment(int row, int col, string value, string author, int width, int height)
    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.
  • public void removeComment(int row, int col)
    Removes a comment from the cell (only for xls format).
  • public bool isDate(int row, int col)
    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.
  • public bool isRichStr(int row, int col)
    Checks that the cell contains a rich string with multiple fonts. If return value is true read it with the Sheet::readRichStr() method.
  • public ErrorType readError(int row, int col)
    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
  • public void writeError(int row, int col, ErrorType error)
    Writes error into the cell.
  • public void writeError(int row, int col, ErrorType error, Format format)
    Writes error into the cell with specified format.
  • public double colWidth(int col)
    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.
  • public double rowHeight(int row)
    Returns row height in typographical points. Point is 1/72 inch.
  • public int colWidthPx(int col)
    Returns column width in pixels.
  • public int rowHeightPx(int row)
    Returns row height in pixels.
  • public Format colFormat(int col)
    Returns column's format.
  • public Format rowFormat(int row)
    Returns row's format.
  • public bool setCol(int col, double width)
    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.
  • public bool setCol(int colFirst, int colLast, double width)
    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.
  • public bool setCol(int col, double width, Format format)
    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.
  • public bool setCol(int colFirst, int colLast, double width, Format format)
    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.
  • public bool setCol(int col, double width, Format format, bool hidden)
    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.
  • public bool setCol(int colFirst, int colLast, double width, Format format, bool hidden)
    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.
  • public bool setColPx(int col, int widthPx)
    Sets column width in pixels for column col. Value -1 is used for autofit column widths.
  • public bool setColPx(int colFirst, int colLast, int widthPx)
    Sets column width in pixels for all columns from colFirst to colLast.
  • public bool setColPx(int col, int widthPx, Format format)
    Sets column width in pixels and format for colum col.
  • public bool setColPx(int colFirst, int colLast, int widthPx, Format format)
    Sets column width in pixels and format for all columns from colFirst to colLast.
  • public bool setColPx(int col, int widthPx, Format format, bool hidden)
    Sets column width in pixels and format for column col. Column may be hidden.
  • public bool setColPx(int colFirst, int colLast, int widthPx, Format format, bool hidden)
    Sets column width in pixels and format for all columns from colFirst to colLast. Columns may be hidden.
  • public bool setRow(int row, double height)
    Sets row height. Row height measured in point size.
  • public bool setRow(int row, double height, Format format)
    Sets row height and format. Row height measured in point size.
  • public bool setRow(int row, double height, Format format, bool hidden)
    Sets row height and format. Row height measured in point size. Row may be hidden.
  • public bool setRowPx(int row, int heightPx)
    Sets row height in pixels.
  • public bool setRowPx(int row, int heightPx, Format format)
    Sets row height in pixels and format.
  • public bool setRowPx(int row, int heightPx, Format format, bool hidden)
    Sets row height in pixels and format. Row may be hidden.
  • public bool rowHidden(int row)
    Returns whether row is hidden.
  • public bool setRowHidden(int row, bool hidden)
    Hides row.
  • public bool colHidden(int col)
    Returns whether column is hidden.
  • public bool setColHidden(int col, bool hidden)
    Hides column.
  • public double defaultRowHeight
    The default row height measured in point size.
  • public bool getMerge(int row, int col, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast)
    Gets merged cells for cell at row, col. Result is written in rowFirst, rowLast, colFirst, colLast. Returns true if specified cell is in a merged area else returns false.
  • public bool setMerge(int rowFirst, int rowLast, int colFirst, int colLast)
    Sets merged cells for range: rowFirst - rowLast, colFirst - colLast.
  • public bool delMerge(int row, int col)
    Removes merged cells.
  • public int mergeSize()
    Returns a number of merged cells in this worksheet.
  • public bool merge(int index, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast)
    Gets the merged cells by index.
  • public bool delMergeByIndex(int index)
    Removes merged cells by index.
  • public int pictureSize()
    Returns a number of pictures in this worksheet.
  • public int getPicture(int index)
    Returns a workbook picture index at position index in worksheet.
    Use Book::getPicture() for extracting binary data of picture by workbook picture index.
  • public int getPicture(int index, ref int rowTop, ref int colLeft, ref int rowBottom, ref int colRight)
    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.
  • public int getPicture(int index, ref int rowTop, ref int colLeft, ref int rowBottom, ref int colRight, ref int width, ref int height)
    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.
  • public int getPicture(int index, ref int rowTop, ref int colLeft, ref int rowBottom, ref int colRight, ref int width, ref int height, ref int offset_x, ref int offset_y)
    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.
  • public bool removePictureByIndex(int index)
    Removes a picture from the worbook by index.
  • public void setPicture(int row, int col, int pictureId)
    Sets a picture with pictureId identifier at position row and col. Use Book::addPicture() for adding a new picture and getting an identifier.
  • public void setPicture(int row, int col, int pictureId, double scale)
    Sets a picture with pictureId identifier at position row and col with scale factor. Use Book::addPicture() for adding a new picture and getting a 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
  • public void setPicture(int row, int col, int pictureId, double scale, int offset_x, int offset_y)
    Sets a picture with pictureId identifier at position row and col with scale factor and offsets in pixels. 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
  • public void setPicture(int row, int col, int pictureId, double scale, int offset_x, int offset_y, Position pos)
    Sets a picture with pictureId identifier at position row and col with scale factor and offsets in pixels. 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
    pos - specifies how the picture should be moved or resized when the rows and columns are resized:
    Position valueDescription
    POSITION_MOVE_AND_SIZEmove and resize with the anchor cells
    POSITION_ONLY_MOVEmove with the cells but do not resize
    POSITION_ABSOLUTEdo not move or resize with the underlying rows/columns
  • public void setPicture2(int row, int col, int pictureId, int width, int height)
    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.
  • public void setPicture2(int row, int col, int pictureId, int width, int height, int offset_x, int offset_y)
    Sets a picture with pictureId identifier at position row and col with custom size and offsets in pixels. Use Book::addPicture() for adding a new picture and getting an identifier.
  • public void setPicture2(int row, int col, int pictureId, int width, int height, int offset_x, int offset_y, Position pos)
    Sets a picture with pictureId identifier at position row and col with custom size and offsets in pixels. Use Book::addPicture() for adding a new picture and getting an identifier.
    pos - specifies how the picture should be moved or resized when the rows and columns are resized:
    Position valueDescription
    POSITION_MOVE_AND_SIZEmove and resize with the anchor cells
    POSITION_ONLY_MOVEmove with the cells but do not resize
    POSITION_ABSOLUTEdo not move or resize with the underlying rows/columns
  • public bool removePicture(int row, int col)
    Removes a picture from the worbook at the specified position.
  • public int getHorPageBreak(int index)
    Returns row with horizontal page break at position index.
  • public int getHorPageBreakSize()
    Returns a number of horizontal page breaks in the sheet.
  • public int getVerPageBreak(int index)
    Returns column with vertical page break at position index.
  • public int getVerPageBreakSize()
    Returns a number of vertical page breaks in the sheet.
  • public bool setHorPageBreak(int row)
    Sets a horizontal page break.
  • public bool delHorPageBreak(int row)
    Removes a horizontal page break.
  • public bool setVerPageBreak(int col)
    Sets a vertical page break.
  • public bool delVerPageBreak(int col)
    Removes a vertical page break.
  • public void split(int row, int col)
    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.
  • public bool splitInfo(ref int row, ref int col)
    Gets the split information (position of frozen pane) in the sheet:
    row - vertical position of the split;
    col - horizontal position of the split.
  • public bool groupRows(int rowFirst, int rowLast, bool collapsed)
    Groups rows from rowFirst to rowLast.
  • public bool groupCols(int colFirst, int colLast, bool collapsed)
    Groups columns from colFirst to colLast.
  • public bool groupSummaryBelow
    If true then grouping rows summary is below else summary is above.
  • public bool groupSummaryRight
    If true then grouping columns summary is right else summary is left.
  • public bool clear(int rowFirst, int rowLast, int colFirst, int colLast)
    Clears all cells in the specified area.
  • public bool insertRow(int rowFirst, int rowLast)
    Inserts rows from rowFirst to rowLast.
  • public bool insertCol(int colFirst, int colLast)
    Inserts columns from colFirst to colLast.
  • public bool removeRow(int rowFirst, int rowLast)
    Removes rows from rowFirst to rowLast.
  • public bool removeCol(int colFirst, int colLast)
    Removes columns from colFirst to colLast.
  • public bool copyCell(int rowSrc, int colSrc, int rowDst, int colDst)
    Copies cell with format from (rowSrc, colSrc) to (rowDst, colDst).
  • public int firstRow()
    Returns the zero-based index of the first row in the sheet that contains a used cell, including blank cells only with formatting.
  • public int lastRow()
    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.
  • public int firstCol()
    Returns the zero-based index of the first column in the sheet that contains a used cell, including blank cells only with formatting.
  • public int lastCol()
    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.
  • public int firstFilledRow()
    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.
  • public int lastFilledRow()
    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.
  • public int firstFilledCol()
    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.
  • public int lastFilledCol()
    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.
  • public bool displayGridlines
    Display gridlines property: true if gridlines are displayed and false if aren't.
  • public bool printGridlines
    Print gridlines property: true if gridlines are printed and false if aren't.
  • public int zoom
    The zoom level of the current view as a percentage. 100 is a usual view.
  • public int printZoom
    The scaling factor for printing as a percentage.
  • public bool getPrintFit(ref int wPages, ref int hPages)
    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.
  • public void setPrintFit(int wPages, int hPages)
    Fits sheet width and sheet height to wPages and hPages respectively.
  • public bool landscape
    Page orientation mode: true - landscape mode, false - portrait mode.
  • public 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
  • public string header
    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 (&)
  • public double headerMargin
    The header margin in inches.
  • public string footer
    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.
  • public double footerMargin
    The footer margin in inches.
  • public bool hCenter
    If true then the sheet is centered horizontally when printed.
  • public bool vCenter
    If true then the sheet is centered vertically when printed.
  • public double marginLeft
    The left margin of the sheet in inches.
  • public double marginRight
    The right margin of the sheet in inches.
  • public double marginTop
    The top margin of the sheet in inches.
  • public double marginBottom
    The bottom margin of the sheet in inches.
  • public bool printRowCol
    If true then the row and column headers are printed.
  • public bool printRepeatRows(ref int rowFirst, ref int rowLast)
    Gets repeated rows on each page from rowFirst to rowLast. Returns false if repeated rows aren't found.
  • public void setPrintRepeatRows(int rowFirst, int rowLast)
    Sets repeated rows on each page from rowFirst to rowLast.
  • public bool printRepeatCols(ref int colFirst, ref int colLast)
    Gets repeated columns on each page from colFirst to colLast. Returns false if repeated columns aren't found.
  • public void setPrintRepeatCols(int colFirst, int colLast)
    Sets repeated columns on each page from colFirst to colLast.
  • public bool printArea(ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast)
    Gets the print area. Returns false if print area isn't found.
  • public void setPrintArea(int rowFirst, int rowLast, int colFirst, int colLast)
    Sets the print area.
  • public void clearPrintRepeats()
    Clears repeated rows and columns on each page.
  • public void clearPrintArea()
    Clears the print area.
  • public bool getNamedRange(string name, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast)
    Gets the named range coordianates by name. Returns false if specified named range isn't found.
  • public bool getNamedRange(string name, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast, int scopeId)
    Gets the named range coordianates by 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.
  • public bool getNamedRange(string name, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast, int scopeId, ref bool hidden)
    Gets the named range coordianates by 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.
  • public bool setNamedRange(string name, int rowFirst, int rowLast, int colFirst, int colLast)
    Sets the named range.
  • public bool setNamedRange(string name, int rowFirst, int rowLast, int colFirst, int colLast, int scopeId)
    Sets the named range.
    scopeId - index of sheet for local named range or -1 for a global named range.
  • public bool delNamedRange(string name)
    Deletes the named range by name.
  • public bool delNamedRange(string name, int scopeId)
    Deletes the named range by name.
    scopeId - index of sheet for local named range or -1 for a global named range.
  • public int namedRangeSize
    Returns the number of named ranges in the sheet.
  • public string namedRange(int index, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast)
    Gets the named range coordianates by index.
  • public string namedRange(int index, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast, ref int scopeId)
    Gets the named range coordianates by index.
    scopeId - index of sheet for local named range or -1 for a global named range.
  • public string namedRange(int index, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast, ref int scopeId, ref bool hidden)
    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.
  • public bool getTable(string name, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast, ref int headerRowCount, ref int totalsRowCount)
    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.
  • public int tableSize()
    Returns the number of tables in the sheet.
  • public string table(int index, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast, ref int headerRowCount, ref int totalsRowCount)
    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.
  • public Table addTable(string name, int rowFirst, int rowLast, int colFirst, int colLast, bool hasHeaders, TableStyle tableStyle)
    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.
  • public Table getTableByName(string name)
    Gets a table by the specified name (only for xlsx files). Returns the table if successed, 0 if failed.
  • public Table getTableByIndex(int index)
    Gets a table by the specified index (only for xlsx files). Returns the table if successed, 0 if failed.
  • public int hyperlinkSize
    Returns the number of hyperlinks in the sheet.
  • public string hyperlink(int index, ref int rowFirst, ref int rowLast, ref int colFirst, ref int colLast)
    Gets the hyperlink and its coordianates by index.
  • public void delHyperlink(int index)
    Removes hyperlink by index.
  • public void addHyperlink(string hyperlink, int rowFirst, int rowLast, int colFirst, int colLast)
    Adds the new hyperlink.
  • public int hyperlinkIndex(int row, int col)
    Checks if the cell contains a hyperlink. Returns an index of hyperlink if exists, -1 if there is no hyperlink in this cell.
  • public bool isAutoFilter()
    Returns true if the AutoFilter already exists (only for xlsx files).
  • public AutoFilter autoFilter()
    Returns the AutoFilter. Creates it if it doesn't exist (only for xlsx files).
  • public void applyFilter()
    Applies all available autofilters to the sheet (only for xlsx files).
  • public void applyFilter2(AutoFilter autoFilter)
    Applies only the specified AutoFilter to the sheet (only for xlsx files).
  • public void removeFilter()
    Removes the AutoFilter from the sheet (only for xlsx files).
  • public string name
    The name of the sheet.
  • public bool protect
    If true then the sheet is protected.
  • public void setProtect(bool protect, string password)
    Protects the sheet with the password.
  • public void setProtect(bool protect, string password, EnhancedProtection prot)
    Protects the sheet with the password and enchanced parameters below. It is possible to combine a few EnhancedProtection values with operator |.
    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.
  • public bool rightToLeft
    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.
  • public SheetState hidden
    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
  • public void getTopLeftView(ref int row, ref int col)
    Extracts the first visible row and the leftmost visible column of the sheet.
  • public void setTopLeftView(int row, int col)
    Sets the first visible row and the leftmost visible column of the sheet.
  • public void setAutoFitArea(int rowFirst, int colFirst, int rowLast, int colLast)
    Sets the borders for autofit column widths feature. The method Sheet::setCol() with -1 width value will affect only to the specified limited area.
  • public void addrToRowCol(string addr, ref int row, ref int col)
    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.
  • public void addrToRowCol(string addr, ref int row, ref int col, ref bool rowRelative, ref bool colRelative)
    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.
  • public string rowColToAddr(int row, int col)
    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.
  • public string rowColToAddr(int row, int col, bool rowRelative, bool colRelative)
    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.
  • public Color tabColor()
    Returns the sheet's tab color.
  • public void setTabColor(Color color)
    Sets the color for the sheet's tab.
  • public bool getTabColor(ref int red, ref int green, ref int blue)
    Returns the sheet's tab RGB color.
  • public void setTabColor(int red, int green, int blue)
    Sets the RGB color for the sheet's tab.
  • public bool setBorder(int rowFirst, int rowLast, int colFirst, int colLast, BorderStyle borderStyle, Color borderColor)
    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.
  • public bool addIgnoredError(int rowFirst, int colFirst, int rowLast, int colLast, IgnoredError iError)
    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. It is possible to combine a few IgnoredError values with operator |. Returns false if error occurs. Get an error info with the Book::errorMessage().
    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.
  • public void addDataValidation(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, string value)

    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).
  • public void addDataValidation(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, string value1, string value2)

    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.
  • public void addDataValidation(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, string value1, string value2, bool allowBlank, bool hideDropDown, bool showInputMessage, bool showErrorMessage, string promptTitle, string prompt, string errorTitle, string error, DataValidationErrorStyle errorStyle)

    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.

  • public void addDataValidationDouble(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, double value)

    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.
  • public void addDataValidationDouble(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, double value1, double value2)

    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.
  • public void addDataValidationDouble(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, double value1, double value2, bool allowBlank, bool hideDropDown, bool showInputMessage, bool showErrorMessage, string promptTitle, string prompt, string errorTitle, string error, DataValidationErrorStyle errorStyle)

    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.
  • public void removeDataValidations()
    Removes all data validations for the sheet (only for xlsx files).
  • public int formControlSize()
    Returns a number of form controls in this worksheet (only for xlsx files).
  • public FormControl formControl(int index)
    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.
  • public ConditionalFormatting addConditionalFormatting()
    Adds a conditional formatting rules to the sheet (only for xlsx files).
  • public bool getActiveCell(ref int row, ref int col)
    Gets an active cell of the sheet. Returns true if an active cell is found otherwise returns false.
  • public void setActiveCell(int row, int col)
    Sets an active cell of the sheet.
  • public string selectionRange()
    Returns a range of the selection.
  • public void addSelectionRange(string sqref)
    Adds a range to the selection.
  • public void removeSelection()
    Removes all selection.
Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl ConditionalFormatting 
ConditionalFormat CoreProperties Table Examples