Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl
ConditionalFormatting ConditionalFormat CoreProperties Examples
ConditionalFormatting ConditionalFormat CoreProperties Examples
Sheet class reference
-
CellType cellType(int row, int col) const Returns cell's type.
CellType value Description CELLTYPE_EMPTY empty, the cell doesn't exist CELLTYPE_NUMBER number value CELLTYPE_STRING string value CELLTYPE_BOOLEAN boolean value CELLTYPE_BLANK blank, the cell contains only format information CELLTYPE_ERROR error -
bool isFormula(int row, int col) const Checks that cell contains a formula.
-
Format* cellFormat(int row, int col) const Returns cell's format. It can be changed by user.
-
void setCellFormat(int row, int col, Format* format) Sets cell's format.
-
const wchar_t* readStr(int row, int col, Format** format = 0) Reads a string and its format from the cell. Don't release a return pointer manually. Copy a return value to your memory buffer or variable immediately if neccessary. Returns NULL if the specified cell doesn't contain a string or error occurs. Get an error info with the Book::errorMessage().
-
bool writeStr(int row, int col, const wchar_t* value, Format* format = 0, CellType type = CELLTYPE_STRING) Writes a string into the cell with the specified format. If format equals 0 then format is ignored. The string will be copied internally and can be destroyed. Returns false if an error occurs. Get an error info with the Book::errorMessage().
-
RichString* readRichStr(int row, int col, Format** format = 0) 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. Don't release a return pointer manually. Returns NULL if the specified cell doesn't contain a string or error occurs. Get an error info with the Book::errorMessage().
-
bool writeRichStr(int row, int col, RichString* richString, Format* format = 0) Writes a rich string with multiple fonts into the cell with the specified format. Add a new rich string with the Book::addRichString() method. If format equals 0 then format is ignored. Returns false if an error occurs. Get an error info with the Book::errorMessage().
-
double readNum(int row, int col, Format** format = 0) const Reads a number or date/time and its format from the cell. Use Book::dateUnpack() for extract date/time parts from double. If *format == 0 then an error occurs. Get an error info with the Book::errorMessage().
-
bool writeNum(int row, int col, double value, Format* format = 0) Writes a number or date/time into the cell with the specified format. If format equals 0 then format is ignored. Use the Book::datePack() for packing date/time parts to double. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool readBool(int row, int col, Format** format = 0) const Reads a bool value and its format from cell. If *format == 0 then error occurs. Get an error info with the Book::errorMessage().
-
bool writeBool(int row, int col, bool value, Format* format = 0) const Writes a bool value into cell with specified format. If format equals 0 then format is ignored. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool readBlank(int row, int col, Format** format = 0) const Reads format from blank cell. Returns false if specified cell isn't blank or error occurs. Get an error info with the Book::errorMessage().
-
bool writeBlank(int row, int col, Format* format) Writes blank cell with specified format. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
const wchar_t* readFormula(int row, int col, Format** format = 0) Reads a formula string and its format from cell. Returns NULL if specified cell doesn't contain formula or error occurs. Get an error info with the Book::errorMessage().
-
bool writeFormula(int row, int col, const wchar_t* value, Format* format = 0) Writes a formula into cell with specified format. If format equals 0 then format is ignored. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool writeFormulaNum(int row, int col, const wchar_t* expr, double value, Format* format = 0) Writes a formula expression with precalculated double value into cell with specified format. If format equals 0 then format is ignored. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool writeFormulaStr(int row, int col, const wchar_t* expr, const wchar_t* value, Format* format = 0) Writes a formula expression with precalculated string value into cell with specified format. If format equals 0 then format is ignored. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool writeFormulaBool(int row, int col, const wchar_t* expr, bool value, Format* format = 0) Writes a formula expression with precalculated bool value into cell with specified format. If format equals 0 then format is ignored. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
const wchar_t* readComment(int row, int col) const Reads a comment from specified cell (only for xls format).
-
void writeComment(int row, int col, const wchar_t* value, const wchar_t* author = 0, int width = 129, int height = 75) 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. -
void removeComment(int row, int col) Removes a comment from the cell (only for xls format).
-
bool isDate(int row, int col) const 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.
-
bool isRichStr(int row, int col) const Checks that the cell contains a rich string with multiple fonts. If return value is true read it with the Sheet::readRichStr() method.
-
ErrorType readError(int row, int col) const Reads error from cell.
ErrorType value Description 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_NOERROR no error -
void writeError(int row, int col, ErrorType error, Format* format = 0) Writes error into the cell with specified format. If format equals 0 then format is ignored.
-
double colWidth(int col) const 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.
-
double rowHeight(int row) const Returns row height in typographical points. Point is 1/72 inch.
-
int colWidthPx(int col) const Returns column width in pixels.
-
int rowHeightPx(int row) const Returns row height in pixels.
-
Format* colFormat(int col) const Returns column's format.
-
Format* rowFormat(int row) const Returns row's format.
-
bool setCol(int colFirst, int colLast, double width, Format* format = 0, bool hidden = false) 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. Value -1 is used for autofit column widths. If format equals 0 then format is ignored. Columns may be hidden. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool setColPx(int colFirst, int colLast, int widthPx, Format* format = 0, bool hidden = false) Sets column width in pixels and format for all columns from colFirst to colLast. Value -1 is used for autofit column widths. If format equals 0 then format is ignored. Columns may be hidden. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool setRow(int row, double height, Format* format = 0, bool hidden = false) Sets row height and format. Row height measured in point size. If format equals 0 then format is ignored. Row may be hidden. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool setRowPx(int row, int heightPx, Format* format = 0, bool hidden = false) Sets row height in pixels. If format equals 0 then format is ignored. Row may be hidden. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool rowHidden(int row) const Returns whether row is hidden.
-
bool setRowHidden(int row, bool hidden) Hides row.
-
bool colHidden(int col) const Returns whether column is hidden.
-
bool setColHidden(int col, bool hidden) Hides column.
-
double defaultRowHeight() const Returns the default row height measured in point size.
-
void setDefaultRowHeight(double height) Sets the default row height measured in point size.
-
bool getMerge(int row, int col, int* rowFirst, int* rowLast, int* colFirst, 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. Get an error info with the Book::errorMessage().
-
bool setMerge(int rowFirst, int rowLast, int colFirst, int colLast) Sets merged cells for range: rowFirst - rowLast, colFirst - colLast. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool delMerge(int row, int col) Removes merged cells. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
int mergeSize() const Returns a number of merged cells in this worksheet.
-
bool merge(int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast) Gets the merged cells by index.
-
bool delMergeByIndex(int index) Removes merged cells by index.
-
int pictureSize() const Returns a number of pictures in this worksheet.
-
int getPicture(int index, int* rowTop, int* colLeft, int* rowBottom, int* colRight, int* width, int* height, int* offset_x, 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.
Returns -1 if error occurs. Get an error info with the Book::errorMessage(). -
bool removePictureByIndex(int index) Removes a picture by specified index. Returns false if an error occurs.
-
void setPicture(int row, int col, int pictureId, double scale = 1.0, int offset_x = 0, int offset_y = 0, Position pos = POSITION_MOVE_AND_SIZE) 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 value Description POSITION_MOVE_AND_SIZE move and resize with the anchor cells POSITION_ONLY_MOVE move with the cells but do not resize POSITION_ABSOLUTE do not move or resize with the underlying rows/columns -
void setPicture2(int row, int col, int pictureId, int width = -1, int height = -1, int offset_x = 0, int offset_y = 0, Position pos = POSITION_MOVE_AND_SIZE) 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.
-
bool removePicture(int row, int col) Removes a picture in the specified position. Returns false if an error occurs.
-
int getHorPageBreak(int index) const Returns row with horizontal page break at position index.
-
int getHorPageBreakSize() const Returns a number of horizontal page breaks in the sheet.
-
int getVerPageBreak(int index) const Returns column with vertical page break at position index.
-
int getVerPageBreakSize() const Returns a number of vertical page breaks in the sheet.
-
bool setHorPageBreak(int row, bool pageBreak = true) Sets/removes a horizontal page break. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool setVerPageBreak(int col, bool pageBreak = true) Sets/removes a vertical page break. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
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.
-
bool splitInfo(int* row, int* col) const Gets the split information (position of frozen pane) in the sheet:
row - vertical position of the split;
col - horizontal position of the split. -
bool groupRows(int rowFirst, int rowLast, bool collapsed = true) Groups rows from rowFirst to rowLast. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool groupCols(int colFirst, int colLast, bool collapsed = true) Groups columns from colFirst to colLast. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool groupSummaryBelow() const Returns whether grouping rows summary is below. Returns true if summary is below and false if isn't.
-
void setGroupSummaryBelow(bool below) Sets a flag of grouping rows summary: true - below, false - above.
-
bool groupSummaryRight() const Returns whether grouping columns summary is right. Returns true if summary is right and false if isn't.
-
void setGroupSummaryRight(bool right) Sets a flag of grouping rows summary: true - right, false - left.
-
bool clear(int rowFirst = 0, int rowLast = 1048575, int colFirst = 0, int colLast = 16383) Clears all cells in the specified area.
-
bool insertRow(int rowFirst, int rowLast, bool updateNamedRanges = true) Inserts rows from rowFirst to rowLast. If updateNamedRanges is false it doesn't update existing named ranges. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool insertCol(int colFirst, int colLast, bool updateNamedRanges = true) Inserts columns from colFirst to colLast. If updateNamedRanges is false it doesn't update existing named ranges. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool removeRow(int rowFirst, int rowLast, bool updateNamedRanges = true) Removes rows from rowFirst to rowLast. If updateNamedRanges is false it doesn't update existing named ranges. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool removeCol(int colFirst, int colLast, bool updateNamedRanges = true) Removes columns from colFirst to colLast. If updateNamedRanges is false it doesn't update existing named ranges. Returns false if error occurs. Get an error info with the Book::errorMessage().
-
bool copyCell(int rowSrc, int colSrc, int rowDst, int colDst) Copies cell with format from (rowSrc, colSrc) to (rowDst, colDst). Returns false if error occurs. Get an error info with the Book::errorMessage().
-
int firstRow() const Returns the zero-based index of the first row in the sheet that contains a used cell, including blank cells only with formatting.
-
int lastRow() const 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.
-
int firstCol() const Returns the zero-based index of the first column in the sheet that contains a used cell, including blank cells only with formatting.
-
int lastCol() const 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.
-
int firstFilledRow() const 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.
-
int lastFilledRow() const 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.
-
int firstFilledCol() const 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.
-
int lastFilledCol() const 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.
-
bool displayGridlines() const Returns whether the gridlines are displayed. Returns true if gridlines are displayed and false if aren't.
-
void setDisplayGridlines(bool show = true) Sets gridlines for displaying, true - gridlines are displayed, false - gridlines aren't displayed.
-
bool printGridlines() const Returns whether the gridlines are printed. Return true if gridlines are printed and false if aren't.
-
void setPrintGridlines(bool print = true) Sets gridlines for printing, true - gridlines are printed, false - gridlines aren't printed.
-
int zoom() const Returns the zoom level of the current view as a percentage.
-
void setZoom(int zoom) Sets the zoom level of the current view. 100 is a usual view.
-
int printZoom() const Returns the scaling factor for printing as a percentage.
-
void setPrintZoom(int zoom) Sets the scaling factor for printing as a percentage.
-
bool getPrintFit(int* wPages, int* hPages) const 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. -
void setPrintFit(int wPages = 1, int hPages = 1) Fits sheet width and sheet height to wPages and hPages respectively.
-
bool landscape() const Returns a page orientation mode, true - landscape mode, false - portrait mode.
-
void setLandscape(bool landscape = true) Sets landscape or portrait mode for printing, true - pages are printed using landscape mode, false - pages are printed using portrait mode.
-
Paper paper() const Retrurns the paper size.
-
void setPaper(Paper paper = PAPER_DEFAULT) Sets the paper size.
Paper value Description PAPER_DEFAULT Default paper size PAPER_LETTER US Letter 8 1/2 x 11 in PAPER_LETTERSMALL US Letter Small 8 1/2 x 11 in PAPER_TABLOID US Tabloid 11 x 17 in PAPER_LEDGER US Ledger 17 x 11 in PAPER_LEGAL US Legal 8 1/2 x 14 in PAPER_STATEMENT US Statement 5 1/2 x 8 1/2 in PAPER_EXECUTIVE US Executive 7 1/4 x 10 1/2 in PAPER_A3 A3 297 x 420 mm PAPER_A4 A4 210 x 297 mm PAPER_A4SMALL A4 Small 210 x 297 mm PAPER_A5 A5 148 x 210 mm PAPER_B4 B4 (JIS) 250 x 354 PAPER_B5 B5 (JIS) 182 x 257 mm PAPER_FOLIO Folio 8 1/2 x 13 in PAPER_QUATRO Quarto 215 x 275 mm PAPER_10x14 10 x 14 in PAPER_10x17 11 x 17 in PAPER_NOTE US Note 8 1/2 x 11 in PAPER_ENVELOPE_9 US Envelope #9 3 7/8 x 8 7/8 PAPER_ENVELOPE_10 US Envelope #10 4 1/8 x 9 1/2 PAPER_ENVELOPE_11 US Envelope #11 4 1/2 x 10 3/8 PAPER_ENVELOPE_12 US Envelope #12 4 3/4 x 11 PAPER_ENVELOPE_14 US Envelope #14 5 x 11 1/2 PAPER_C_SIZE C size sheet PAPER_D_SIZE D size sheet PAPER_E_SIZE E size sheet PAPER_ENVELOPE_DL Envelope DL 110 x 220mm PAPER_ENVELOPE_C5 Envelope C5 162 x 229 mm PAPER_ENVELOPE_C3 Envelope C3 324 x 458 mm PAPER_ENVELOPE_C4 Envelope C4 229 x 324 mm PAPER_ENVELOPE_C6 Envelope C6 114 x 162 mm PAPER_ENVELOPE_C65 Envelope C65 114 x 229 mm PAPER_ENVELOPE_B4 Envelope B4 250 x 353 mm PAPER_ENVELOPE_B5 Envelope B5 176 x 250 mm PAPER_ENVELOPE_B6 Envelope B6 176 x 125 mm PAPER_ENVELOPE Envelope 110 x 230 mm PAPER_ENVELOPE_MONARCH US Envelope Monarch 3.875 x 7.5 in PAPER_US_ENVELOPE US Envelope 3 5/8 x 6 1/2 in PAPER_FANFOLD US Std Fanfold 14 7/8 x 11 in PAPER_GERMAN_STD_FANFOLD German Std Fanfold 8 1/2 x 12 in PAPER_GERMAN_LEGAL_FANFOLD German Legal Fanfold 8 1/2 x 13 in -
const wchar_t* header() const Returns the header text of the sheet when printed. Get an error info with the Book::errorMessage().
-
bool setHeader(const wchar_t* header, double margin = 0.5) 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.
Code Description &L specifies the beginning of the left section &P specifies the current page number &N specifies 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 &S specifies whether the strikethrough text style is on or off &X specifies whether the superscript text style is on or off &Y specifies whether the subscript text style is on or off &C specifies the beginning of the center section &D specifies a date &T specifies a time &U specifies whether the single underline text style is on or off &E specifies whether the double underline text style is on or off &R specifies the beginning of the right section &Z specifies a workbook file path &F specifies a workbook file name &A specifies a sheet name &"fontname" specifies the text font, for example: &"Comic Sans MS" &B specifies whether the bold text style is on or off &I specifies whether the italic text style is on or off && specifies an ampersand character (&) -
double headerMargin() const Returns the header margin in inches.
-
const wchar_t* footer() const Returns the footer text of the sheet when printed. Get an error info with the Book::errorMessage().
-
bool setFooter(const wchar_t* footer, double margin = 0.5) 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.
-
double footerMargin() const Returns the footer margin in inches.
-
bool hCenter() const Returns whether the sheet is centered horizontally when printed.
-
void setHCenter(bool hCenter = true) Sets a flag that the sheet is centered horizontally when printed.
-
bool vCenter() const Returns whether the sheet is centered vertically when printed.
-
void setVCenter(bool vCenter = true) Sets a flag that the sheet is centered vertically when printed.
-
double marginLeft() const Returns the left margin of the sheet in inches.
-
void setMarginLeft(double margin) Sets the left margin of the sheet in inches.
-
double marginRight() const Returns the right margin of the sheet in inches.
-
void setMarginRight(double margin) Sets the right margin of the sheet in inches.
-
double marginTop() const Returns the top margin of the sheet in inches.
-
void setMarginTop(double margin)
Sets the top margin of the sheet in inches.
-
double marginBottom() const Returns the bottom margin of the sheet in inches.
-
void setMarginBottom(double margin) Sets the bottom margin of the sheet in inches.
-
bool printRowCol() const Returns whether the row and column headers are printed.
-
void setPrintRowCol(bool print = true) Sets a flag that the row and column headers are printed.
-
bool printRepeatRows(int* rowFirst, int* rowLast) Gets repeated rows on each page from rowFirst to rowLast. Returns false if repeated rows aren't found.
-
void setPrintRepeatRows(int rowFirst, int rowLast) Sets repeated rows on each page from rowFirst to rowLast.
-
bool printRepeatCols(int* colFirst, int* colLast) Gets repeated columns on each page from colFirst to colLast. Returns false if repeated columns aren't found.
-
void setPrintRepeatCols(int colFirst, int colLast) Sets repeated columns on each page from colFirst to colLast.
-
bool printArea(int* rowFirst, int* rowLast, int* colFirst, int* colLast) Gets the print area. Returns false if print area isn't found.
-
void setPrintArea(int rowFirst, int rowLast, int colFirst, int colLast) Sets the print area.
-
void clearPrintRepeats() Clears repeated rows and columns on each page.
-
void clearPrintArea() Clears the print area.
-
bool getNamedRange(const wchar_t* name, int* rowFirst, int* rowLast, int* colFirst, int* colLast, int scopeId = SCOPE_UNDEFINED, bool* hidden = 0) Gets the named range coordianates by name.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK 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 or error occurs. Get an error info with the Book::errorMessage(). -
bool setNamedRange(const wchar_t* name, int rowFirst, int rowLast, int colFirst, int colLast, int scopeId = SCOPE_UNDEFINED) Sets the named range.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for a global named range.
Returns false if error occurs. Get an error info with the Book::errorMessage(). -
bool delNamedRange(const wchar_t* name, int scopeId = SCOPE_UNDEFINED) Deletes the named range by name.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for a global named range.
Returns false if error occurs. Get an error info with the Book::errorMessage(). -
int namedRangeSize() const Returns the number of named ranges in the sheet.
-
const wchar_t* namedRange(int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast, int* scopeId = 0, bool* hidden = 0) Gets the named range coordianates by index.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for global named range.
hidden - true if named range is hidden and false if isn't.
-
bool getTable(const wchar_t* name, int* rowFirst, int* rowLast, int* colFirst, int* colLast, int* headerRowCount, 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. -
int tableSize() const Returns the number of tables in the sheet.
-
const wchar_t* table(int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast, int* headerRowCount, 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. -
int hyperlinkSize() const Returns the number of hyperlinks in the sheet.
-
const wchar_t* hyperlink(int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast) Gets the hyperlink and its coordianates by index.
-
bool delHyperlink(int index) Removes hyperlink by index.
-
void addHyperlink(const wchar_t* hyperlink, int rowFirst, int rowLast, int colFirst, int colLast) Adds the new hyperlink.
-
int hyperlinkIndex(int row, int col) const Checks if the cell contains a hyperlink. Returns an index of hyperlink if exists, -1 if there is no hyperlink in this cell.
-
bool isAutoFilter() const Returns true if the AutoFilter already exists (only for xlsx files).
-
AutoFilter autoFilter() Returns the AutoFilter. Creates it if it doesn't exist (only for xlsx files).
-
void applyFilter() Applies the AutoFilter to the sheet (only for xlsx files).
-
void removeFilter() Removes the AutoFilter from the sheet (only for xlsx files).
-
const wchar_t* name() const Returns the name of the sheet.
-
void setName(const wchar_t* name) 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. -
bool protect() const Returns whether sheet is protected.
-
void setProtect(bool protect = true, const wchar_t* password = 0, EnhancedProtection prot = PROT_DEFAULT) Protects/unprotects the sheet with password and enchanced parameters below. It is possible to combine a few EnhancedProtection values with operator |.
EnhancedProtection value Description PROT_DEFAULT Default protection. PROT_ALL Nothing is allowed except cell selections. PROT_OBJECTS Objects are locked when the sheet is protected. PROT_SCENARIOS Scenarios are locked when the sheet is protected. PROT_FORMAT_CELLS Formatting cells is allowed when the sheet is protected. PROT_FORMAT_COLUMNS Formatting columns is allowed when the sheet is protected. PROT_FORMAT_ROWS Formatting rows is allowed when the sheet is protected. PROT_INSERT_COLUMNS Inserting columns is allowed when the sheet is protected. PROT_INSERT_ROWS Inserting rows is allowed when the sheet is protected. PROT_INSERT_HYPERLINKS Inserting hyperlinks is allowed when the sheet is protected. PROT_DELETE_COLUMNS Deleting columns is allowed when the sheet is protected. PROT_DELETE_ROWS Deleting rows is allowed when the sheet is protected. PROT_SEL_LOCKED_CELLS Selection of locked cells is locked when the sheet is protected. PROT_SORT Sorting is allowed when the sheet is protected. PROT_AUTOFILTER Autofilters are allowed when the sheet is protected. PROT_PIVOTTABLES Pivot tables are allowed when the sheet is protected. PROT_SEL_UNLOCKED_CELLS Selection of unlocked cells is locked when the sheet is protected. -
bool rightToLeft() const Returns whether the text is displayed in right-to-left mode.
-
void setRightToLeft(bool rightToLeft = true) Sets the right-to-left mode:
true - the text is displayed in right-to-left mode,
false - the text is displayed in left-to-right mode. -
SheetState hidden() const Returns whether sheet is hidden.
-
bool setHidden(SheetState state = SHEETSTATE_HIDDEN) Hides/unhides the sheet. Returns false if error occurs. Get an error info with the Book::errorMessage().
SheetState value Description SHEETSTATE_VISIBLE sheet is visible SHEETSTATE_HIDDEN sheet is hidden, but can be shown via the user interface SHEETSTATE_VERYHIDDEN sheet is hidden and cannot be shown in the user interface -
void getTopLeftView(int* row, int* col) const Extracts the first visible row and the leftmost visible column of the sheet.
-
void setTopLeftView(int row, int col) Sets the first visible row and the leftmost visible column of the sheet.
-
void setAutoFitArea(int rowFirst = 0, int colFirst = 0, int rowLast = -1, int colLast = -1) Sets the borders for autofit column widths feature. The method Sheet::setCol() with -1 width value will affect only to the specified limited area.
-
void addrToRowCol(const wchar_t* addr, int* row, int* col, bool* rowRelative = 0, bool* colRelative = 0) 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. -
const wchar_t* rowColToAddr(int row, int col, bool rowRelative = true, bool colRelative = true) 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. -
Color tabColor() const Returns the sheet's tab color.
-
void setTabColor(Color color) Sets the color for the sheet's tab.
-
bool getTabColor(int* red, int* green, int* blue) const Returns the sheet's tab RGB color.
-
void setTabColor(int red, int green, int blue) Sets the RGB color for the sheet's tab.
-
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 value Description IERR_EVAL_ERROR Ignore errors when cells contain formulas that result in an error. IERR_EMPTY_CELLREF Ignore errors when formulas refer to empty cells. IERR_NUMBER_STORED_AS_TEXT Ignore errors when numbers are formatted as text or are preceded by an apostrophe. IERR_INCONSIST_RANGE Ignore errors when formulas omit certain cells in a region. IERR_INCONSIST_FMLA Ignore errors when a formula in a region of your worksheet differs from other formulas in the same region. IERR_TWODIG_TEXTYEAR Ignore errors when formulas contain text formatted cells with years represented as 2 digits. IERR_UNLOCK_FMLA Ignore errors when unlocked cells contain formulas. IERR_DATA_VALIDATION Ignore errors when a cell's value in a Table does not comply with the Data Validation rules specified. -
void addDataValidation(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, const wchar_t* value1, const wchar_t* value2, bool allowBlank = true, bool hideDropDown = false, bool showInputMessage = true, bool showErrorMessage = true, const wchar_t* promptTitle, const wchar_t* prompt = 0, const wchar_t* errorTitle = 0, const wchar_t* error = 0, DataValidationErrorStyle errorStyle = VALIDATION_ERRSTYLE_STOP)
Adds a data validation for the specified range (only for xlsx files).
type - the type of data validation:DataValidationType value Description VALIDATION_TYPE_NONE No data validation. VALIDATION_TYPE_WHOLE Data validation which checks for whole number values satisfying the given condition. VALIDATION_TYPE_DECIMAL Data validation which checks for decimal values satisfying the given condition. VALIDATION_TYPE_LIST Data validation which checks for a value matching one of list of values. VALIDATION_TYPE_DATE Data validation which checks for date values satisfying the given condition. VALIDATION_TYPE_TIME Data validation which checks for time values satisfying the given condition. VALIDATION_TYPE_TEXTLENGTH Data validation which checks for text values, whose length satisfies the given condition. VALIDATION_TYPE_CUSTOM Data validation which uses a custom formula to check the cell value.
op - the relational operator of data validation:DataValidationOperator value Description VALIDATION_OP_BETWEEN Data validation which checks if a value is between two other values. VALIDATION_OP_NOTBETWEEN Data validation which checks if a value is not between two other values. VALIDATION_OP_EQUAL Data validation which checks if a value is equal to a specified value. VALIDATION_OP_NOTEQUAL Data validation which checks if a value is not equal to a specified value. VALIDATION_OP_LESSTHAN Data validation which checks if a value is less than a specified value. VALIDATION_OP_LESSTHANOREQUAL Data validation which checks if a value is less than or equal to a specified value. VALIDATION_OP_GREATERTHAN Data validation which checks if a value is greater than a specified value. VALIDATION_OP_GREATERTHANOREQUAL Data 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;
value1 - the first 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);
value2 - the second value for VALIDATION_OP_BETWEEN or VALIDATION_OP_NOTBETWEEN operator;
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 value Description VALIDATION_ERRSTYLE_STOP This data validation error style uses a stop icon in the error alert. VALIDATION_ERRSTYLE_WARNING This data validation error style uses a warning icon in the error alert. VALIDATION_ERRSTYLE_INFORMATION This data validation error style uses an information icon in the error alert.
-
void addDataValidationDouble(DataValidationType type, DataValidationOperator op, int rowFirst, int rowLast, int colFirst, int colLast, double value1, double value2, bool allowBlank = true, bool hideDropDown = false, bool showInputMessage = true, bool showErrorMessage = true, const wchar_t* promptTitle, const wchar_t* prompt = 0, const wchar_t* errorTitle = 0, const wchar_t* error = 0, DataValidationErrorStyle errorStyle = VALIDATION_ERRSTYLE_STOP)
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. -
void removeDataValidations() Removes all data validations for the sheet (only for xlsx files).
-
int formControlSize() const Returns a number of form controls in this worksheet (only for xlsx files).
-
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.
-
ConditionalFormatting* addConditionalFormatting() Adds a conditional formatting rules to the sheet (only for xlsx files).
-
bool getActiveCell(int* row, int* col) const Gets an active cell of the sheet. Returns true if an active cell is found otherwise returns false.
-
void setActiveCell(int row, int col) Sets an active cell of the sheet.
-
const wchar_t* selectionRange() const Returns a range of the selection.
-
void addSelectionRange(const wchar_t* sqref) Adds a range to the selection.
-
void removeSelection() Removes all selection.