LibXL: Sheet functions

Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl
ConditionalFormatting ConditionalFormat Examples

Sheet functions

int xlSheetCellType(SheetHandle handle, int row, int col)

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

int xlSheetIsFormula(SheetHandle handle, int row, int col)
Checks that cell contains a formula. Return value: 1 - formula cell, 0 - usual cell.
FormatHandle xlSheetCellFormat(SheetHandle handle, int row, int col)
Returns cell's format. It can be changed by user.
void xlSheetSetCellFormat(SheetHandle handle, int row, int col, FormatHandle format)
Sets cell's format.
const wchar_t* xlSheetReadStr(SheetHandle handle, int row, int col, FormatHandle* format)
Reads a string and its format from cell. Memory is allocated internally and valid until a new workbook is loaded or xlBookRelease() is called for binary implementation (xls). But it's needed to copy a result string every time in xml implementation (xlsx). Returns NULL if specified cell doesn't contain string or error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteStr(SheetHandle handle, int row, int col, const wchar_t* value, FormatHandle format)
Writes a string into cell with specified format. If format equals 0 then format is ignored. String is copied internally and can be destroyed after call this method. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
RichStringHandle xlSheetReadRichStr(SheetHandle handle, int row, int col, FormatHandle* 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. 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 xlBookErrorMessage().
int xlSheetWriteRichStr(SheetHandle handle, int row, int col, RichStringHandle richString, FormatHandle 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. If format equals 0 then format is ignored. Returns 0 if an error occurs. Get an error info with the xlBookErrorMessage().
double xlSheetReadNum(SheetHandle handle, int row, int col, FormatHandle* format)
Reads a number or date/time and its format from the cell. Use xlBookDateUnpack() for extract date/time parts from double. If *format == 0 then error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteNum(SheetHandle handle, int row, int col, double value, FormatHandle format)
Writes a number or date/time into the cell with the specified format. If format equals 0 then format is ignored. Use the xlBookDatePack() for packing date/time parts to double. Returns 0 if an error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetReadBool(SheetHandle handle, int row, int col, FormatHandle* format)
Reads a bool value and its format from cell. If *format == 0 then error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteBool(SheetHandle handle, int row, int col, int value, FormatHandle format)
Writes a bool value into cell with specified format. If format equals 0 then format is ignored. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetReadBlank(SheetHandle handle, int row, int col, FormatHandle* format)
Reads format from blank cell. Returns 0 if specified cell isn't blank or error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteBlank(SheetHandle handle, int row, int col, FormatHandle format)
Writes blank cell with specified format. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
const wchar_t* xlSheetReadFormula(SheetHandle handle, int row, int col, FormatHandle* format)
Reads a formula and its format from cell. Returns NULL if specified cell doesn't contain formula or error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteFormula(SheetHandle handle, int row, int col, const wchar_t* value, FormatHandle format)
Writes a formula into cell with specified format. If format equals 0 then format is ignored. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteFormulaNum(SheetHandle handle, int row, int col, const wchar_t* expr, double value, FormatHandle format)
Writes a formula expression with precalculated double value into cell with specified format. If format equals 0 then format is ignored. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteFormulaStr(SheetHandle handle, int row, int col, const wchar_t* expr, const wchar_t* value, FormatHandle format)
Writes a formula expression with precalculated string value into cell with specified format. If format equals 0 then format is ignored. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetWriteFormulaBool(SheetHandle handle, int row, int col, const wchar_t* expr, int value, FormatHandle format)
Writes a formula expression with precalculated bool value into cell with specified format. If format equals 0 then format is ignored. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
const char* xlSheetReadComment(SheetHandle handle, int row, int col)
Reads a comment from specified cell (only for xls format).
void xlSheetWriteComment(SheetHandle handle, int row, int col, const wchar_t* value, const wchar_t* 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.
void xlSheetRemoveComment(SheetHandle handle, int row, int col)
Removes a comment from the cell (only for xls format).
int xlSheetIsDate(SheetHandle handle, 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.
int xlSheetIsRichStr(SheetHandle handle, 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.

int xlSheetReadError(SheetHandle handle, int row, int col)

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 xlSheetWriteError(SheetHandle handle, int row, int col, int error, FormatHandle format)
Writes error into the cell with specified format. If format equals 0 then format is ignored.
double xlSheetColWidth(SheetHandle handle, 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.
double xlSheetRowHeight(SheetHandle handle, int row)
Returns row height in typographical points. Point is 1/72 inch.
int xlSheetColWidthPx(SheetHandle handle, int col)
Returns column width in pixels.
int xlSheetRowHeightPx(SheetHandle handle, int row)
Returns row height in pixels.
int xlSheetSetCol(SheetHandle handle, int colFirst, int colLast, double width, FormatHandle format, int 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. Value -1 is used for autofit column widths. If format equals 0 then format is ignored. Columns may be hidden. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetSetColPx(SheetHandle handle, int colFirst, int colLast, int widthPx, FormatHandle format, int hidden)
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 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetSetRow(SheetHandle handle, int row, double height, FormatHandle format, int hidden)
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 xlBookErrorMessage().
int xlSheetSetRowPx(SheetHandle handle, int row, int heightPx, FormatHandle format, int hidden)
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 xlBookErrorMessage().
int xlSheetRowHidden(SheetHandle handle, int row)
Returns whether row is hidden.
int xlSheetSetRowHidden(SheetHandle handle, int row, int hidden)
Hides row.
int xlSheetColHidden(SheetHandle handle, int col)
Returns whether column is hidden.
int xlSheetSetColHidden(SheetHandle handle, int col, int hidden)
Hides column.
double xlSheetDefaultRowHeight(SheetHandle handle)
Returns the default row height measured in point size.
void xlSheetSetDefaultRowHeight(SheetHandle handle, double height)
Sets the default row height measured in point size.
int xlSheetGetMerge(SheetHandle handle, 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 1 if specified cell is in a merged area else returns 0. Get an error info with the xlBookErrorMessage().
int xlSheetSetMerge(SheetHandle handle, int rowFirst, int rowLast, int colFirst, int colLast)
Sets merged cells for range: rowFirst - rowLast, colFirst - colLast. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetDelMerge(SheetHandle handle, int row, int col)
Removes merged cells. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetMergeSize(SheetHandle handle)
Returns a number of merged cells in this worksheet.
int xlSheetMerge(SheetHandle handle, int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast)
Gets the merged cells by index.
int xlSheetDelMergeByIndex(SheetHandle handle, int index)
Removes merged cells by index.
int xlSheetPictureSize(SheetHandle handle)
Returns a number of pictures in this worksheet.
int xlSheetGetPicture(SheetHandle handle, 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 xlBookGetPicture() for extracting binary data of picture by workbook picture index.
Returns -1 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetRemovePictureByIndex(SheetHandle handle, int index)
Removes a picture by specified index. Returns 0 if an error occurs.

void xlSheetSetPicture(SheetHandle handle, int row, int col, int pictureId, double scale, int offset_x, int offset_y, int 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 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 xlSheetSetPicture2(SheetHandle handle, int row, int col, int pictureId, int width, int height, int offset_x, int offset_y, int 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.
int xlSheetRemovePicture(SheetHandle handle, int row, int col)
Removes a picture in the specified position. Returns 0 if an error occurs.
int xlSheetGetHorPageBreak(SheetHandle handle, int index)
Returns row with horizontal page break at position index.
int xlSheetGetHorPageBreakSize(SheetHandle handle)
Returns a number of horizontal page breaks in the sheet.
int xlSheetGetVerPageBreak(SheetHandle handle, int index)
Returns column with vertical page break at position index.
int xlSheetGetVerPageBreakSize(SheetHandle handle)
Returns a number of vertical page breaks in the sheet.
int xlSheetSetHorPageBreak(SheetHandle handle, int row, int pageBreak)
Sets/removes a horizontal page break (sets if pageBreak == 1, removes if pageBreak == 0). Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetSetVerPageBreak(SheetHandle handle, int col, int pageBreak)
Sets/removes a vertical page break (sets if pageBreak == 1, removes if pageBreak == 0). Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
void xlSheetSplit(SheetHandle handle, 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.
int xlSheetSplitInfo(SheetHandle handle, int* row, 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.
int xlSheetGroupRows(SheetHandle handle, int rowFirst, int rowLast, int collapsed)
Groups rows from rowFirst to rowLast. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetGroupCols(SheetHandle handle, int colFirst, int colLast, int collapsed)
Groups columns from colFirst to colLast. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetGroupSummaryBelow(SheetHandle handle)
Returns whether grouping rows summary is below. Returns 1 if summary is below and 0 if isn't.
void xlSheetSetGroupSummaryBelow(SheetHandle handle, int below)
Sets a flag of grouping rows summary: 1 - below, 0 - above.
int xlSheetGroupSummaryRight(SheetHandle handle)
Returns whether grouping columns summary is right. Returns 1 if summary is right and 0 if isn't.
void xlSheetSetGroupSummaryRight(SheetHandle handle, int right)
Sets a flag of grouping columns summary: 1 - right, 0 - left.
int xlSheetClear(SheetHandle handle, int rowFirst, int rowLast, int colFirst, int colLast)
Clears all cells in the specified area.
int xlSheetInsertRow(SheetHandle handle, int rowFirst, int rowLast)
Inserts rows from rowFirst to rowLast. Updates existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetInsertCol(SheetHandle handle, int colFirst, int colLast)
Inserts columns from colFirst to colLast. Updates existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetRemoveRow(SheetHandle handle, int rowFirst, int rowLast)
Removes rows from rowFirst to rowLast. Updates existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetRemoveCol(SheetHandle handle, int colFirst, int colLast)
Removes columns from colFirst to colLast. Updates existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetInsertRowAndKeepRanges(SheetHandle handle, int rowFirst, int rowLast)
Inserts rows from rowFirst to rowLast. Doesn't update existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetInsertColAndKeepRanges(SheetHandle handle, int colFirst, int colLast)
Inserts columns from colFirst to colLast. Doesn't update existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetRemoveRowAndKeepRanges(SheetHandle handle, int rowFirst, int rowLast)
Removes rows from rowFirst to rowLast. Doesn't update existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetRemoveColAndKeepRanges(SheetHandle handle, int colFirst, int colLast)
Removes columns from colFirst to colLast. Doesn't update existing named ranges. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetCopyCell(SheetHandle handle, int rowSrc, int colSrc, int rowDst, int colDst)
Copies cell with format from (rowSrc, colSrc) to (rowDst, colDst). Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetFirstRow(SheetHandle handle)
Returns the zero-based index of the first row in the sheet that contains a used cell, including blank cells only with formatting.
int xlSheetLastRow(SheetHandle handle)
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 xlSheetFirstCol(SheetHandle handle)
Returns the zero-based index of the first column in the sheet that contains a used cell, including blank cells only with formatting.
int xlSheetLastCol(SheetHandle handle)
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 xlSheetFirstFilledRow(SheetHandle handle)
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 xlSheetLastFilledRow(SheetHandle handle)
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 xlSheetFirstFilledCol(SheetHandle handle)
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 xlSheetLastFilledCol(SheetHandle handle)
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.
int xlSheetDisplayGridlines(SheetHandle handle)
Returns whether the gridlines are displayed. Returns 1 if gridlines are displayed and 0 if aren't.
void xlSheetSetDisplayGridlines(SheetHandle handle, int show)
Sets gridlines for displaying, 1 - gridlines are displayed, 0 - gridlines aren't displayed.
int xlSheetPrintGridlines(SheetHandle handle)
Returns whether the gridlines are printed. Return 1 if gridlines are printed and 0 if aren't.
void xlSheetSetPrintGridlines(SheetHandle handle, int print)
Sets gridlines for printing, 1 - gridlines are printed, 0 - gridlines aren't printed.
int xlSheetZoom(SheetHandle handle)
Returns the zoom level of the current view as a percentage.
void xlSheetSetZoom(SheetHandle handle, int zoom)
Sets the zoom level of the current view. 100 is a usual view.
int xlSheetPrintZoom(SheetHandle handle)
Returns the scaling factor for printing as a percentage.
void xlSheetSetPrintZoom(SheetHandle handle, int zoom)
Sets the scaling factor for printing as a percentage.
int xlSheetGetPrintFit(SheetHandle handle, int* wPages, 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.
void xlSheetSetPrintFit(SheetHandle handle, int wPages, int hPages)
Fits sheet width and sheet height to wPages and hPages respectively.
int xlSheetLandscape(SheetHandle handle)
Returns a page orientation mode, 1 - landscape mode, 0 - portrait mode.
void xlSheetSetLandscape(SheetHandle handle, int landscape)
Sets landscape or portrait mode for printing, 1 - pages are printed using landscape mode, 0 - pages are printed using portrait mode.
int xlSheetPaper(SheetHandle handle)
Retrurns the paper size.

void xlSheetSetPaper(SheetHandle handle, int paper)

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* xlSheetHeader(SheetHandle handle)
Returns the header text of the sheet when printed. Get an error info with the xlBookErrorMessage().

int xlSheetSetHeader(SheetHandle handle, const wchar_t* header, double margin)

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 xlSheetHeaderMargin(SheetHandle handle)
Returns the header margin in inches.
const wchar_t* xlSheetFooter(SheetHandle handle)
Returns the footer text of the sheet when printed. Get an error info with the xlBookErrorMessage().
int xlSheetSetFooter(SheetHandle handle, const wchar_t* footer, double margin)
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 xlSheetSetHeader() for details. Margin is specified in inches.
double xlSheetFooterMargin(SheetHandle handle)
Returns the footer margin in inches.
int xlSheetHCenter(SheetHandle handle)
Returns whether the sheet is centered horizontally when printed: 1 - yes, 0 - no.
void xlSheetSetHCenter(SheetHandle handle, int hCenter)
Sets a flag that the sheet is centered horizontally when printed: 1 - yes, 0 - no.
int xlSheetVCenter(SheetHandle handle)
Returns whether the sheet is centered vertically when printed: 1 - yes, 0 - no.
void xlSheetSetVCenter(SheetHandle handle, int vCenter)
Sets a flag that the sheet is centered vertically when printed: 1 - yes, 0 - no.
double xlSheetMarginLeft(SheetHandle handle)
Returns the left margin of the sheet in inches.
void xlSheetSetMarginLeft(SheetHandle handle, double margin)
Sets the left margin of the sheet in inches.
double xlSheetMarginRight(SheetHandle handle)
Returns the right margin of the sheet in inches.
void xlSheetSetMarginRight(SheetHandle handle, double margin)
Sets the right margin of the sheet in inches.
double xlSheetMarginTop(SheetHandle handle)
Returns the top margin of the sheet in inches.
void xlSheetSetMarginTop(SheetHandle handle, double margin)
Sets the top margin of the sheet in inches.
double xlSheetMarginBottom(SheetHandle handle)
Returns the bottom margin of the sheet in inches.
void xlSheetSetMarginBottom(SheetHandle handle, double margin)
Sets the bottom margin of the sheet in inches.
int xlSheetPrintRowCol(SheetHandle handle)
Returns whether the row and column headers are printed: 1 - yes, 0 - no.
void xlSheetSetPrintRowCol(SheetHandle handle, int print)
Sets a flag that the row and column headers are printed: 1 - yes, 0 - no.
int xlSheetPrintRepeatRows(SheetHandle handle, int* rowFirst, int* rowLast)
Gets repeated rows on each page from rowFirst to rowLast. Returns 0 if repeated rows aren't found.
void xlSheetSetPrintRepeatRows(SheetHandle handle, int rowFirst, int rowLast)
Sets repeated rows on each page from rowFirst to rowLast.
int xlSheetPrintRepeatCols(SheetHandle handle, int* colFirst, int* colLast)
Gets repeated columns on each page from colFirst to colLast. Returns 0 if repeated columns aren't found.
void xlSheetSetPrintRepeatCols(SheetHandle handle, int colFirst, int colLast)
Sets repeated columns on each page from colFirst to colLast.
int xlSheetPrintArea(SheetHandle handle, int* rowFirst, int* rowLast, int* colFirst, int* colLast)
Gets the print area. Returns 0 if print area isn't found.
void xlSheetSetPrintArea(SheetHandle handle, int rowFirst, int rowLast, int colFirst, int colLast)
Sets the print area.
void xlSheetClearPrintRepeats(SheetHandle handle)
Clears repeated rows and columns on each page.
void xlSheetClearPrintArea(SheetHandle handle)
Clears the print area.
int xlSheetGetNamedRange(SheetHandle handle, const wchar_t* name, int* rowFirst, int* rowLast, int* colFirst, int* colLast, int scopeId, int* hidden)
Gets the named range coordianates by name.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for a global named range.
hidden - 1 if named range is hidden and 0 if isn't.
Returns 0 if specified named range isn't found or error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetSetNamedRange(SheetHandle handle, const wchar_t* name, int rowFirst, int rowLast, int colFirst, int colLast, int scopeId)
Sets the named range.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for a global named range.
Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetDelNamedRange(SheetHandle handle, const wchar_t* name, int scopeId)
Deletes the named range by name.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for a global named range.
Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().
int xlSheetNamedRangeSize(SheetHandle handle)
Returns the number of named ranges in the sheet.
const wchar_t* xlSheetNamedRange(SheetHandle handle, int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast, int* scopeId, int* hidden)
Gets the named range coordianates by index.
scopeId - index of sheet for local named range or SCOPE_WORKBOOK for global named range.
hidden - 1 if named range is hidden and 0 if isn't.
int xlSheetGetTable(SheetHandle handle, 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 1 if the table is found.
int xlSheetTableSize(SheetHandle handle)
Returns the number of tables in the sheet.
const wchar_t* xlSheetTable(SheetHandle handle, 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 xlSheetHyperlinkSize(SheetHandle handle)
Returns the number of hyperlinks in the sheet.
const wchar_t* xlSheetHyperlink(SheetHandle handle, int index, int* rowFirst, int* rowLast, int* colFirst, int* colLast)
Gets the hyperlink and its coordianates by index.
int xlSheetDelHyperlink(SheetHandle handle, int index)
Removes hyperlink by index.
void xlSheetAddHyperlink(SheetHandle handle, const wchar_t* hyperlink, int rowFirst, int rowLast, int colFirst, int colLast)
Adds the new hyperlink.
int xlSheetHyperlinkIndex(SheetHandle handle, 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.
int xlSheetIsAutoFilter(SheetHandle handle)
Returns true if the AutoFilter already exists (only for xlsx files).
AutoFilterHandle xlSheetAutoFilter(SheetHandle handle)
Returns the AutoFilter. Creates it if it doesn't exist (only for xlsx files).
void xlSheetApplyFilter(SheetHandle handle)
Applies the AutoFilter to the sheet (only for xlsx files).
void xlSheetRemoveFilter(SheetHandle handle)
Removes the AutoFilter from the sheet (only for xlsx files).
const wchar_t* xlSheetName(SheetHandle handle)
Returns the name of the sheet.
void xlSheetSetName(SheetHandle handle, 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.
int xlSheetProtect(SheetHandle handle)
Returns whether sheet is protected: 1 - yes, 0 - no.
void xlSheetSetProtect(SheetHandle handle, int protect)
Protects (protect = 1) or unprotects (protect = 0) the sheet.

void xlSheetSetProtectEx(SheetHandle handle, int protect, const wchar_t* password, int enhancedProtection)

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

int xlSheetRightToLeft(SheetHandle handle)
Returns whether the text is displayed in right-to-left mode: 1 - yes, 0 - no.
void xlSheetSetRightToLeft(SheetHandle handle, int rightToLeft)
Sets the right-to-left mode:
1 - the text is displayed in right-to-left mode,
0 - the text is displayed in left-to-right mode.
int xlSheetHidden(SheetHandle handle)
Returns whether sheet is hidden.

int xlSheetSetHidden(SheetHandle handle, int hidden)

Hides/unhides the sheet. Returns 0 if error occurs. Get an error info with the xlBookErrorMessage().

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 xlSheetGetTopLeftView(SheetHandle handle, int* row, int* col)
Extracts the first visible row and the leftmost visible column of the sheet.
void xlSheetSetTopLeftView(SheetHandle handle, int row, int col)
Sets the first visible row and the leftmost visible column of the sheet.
void xlSheetSetAutoFitArea(SheetHandle handle, int rowFirst, int colFirst, int rowLast, int colLast)
Sets the borders for autofit column widths feature. The function xlSheetSetCol() with -1 width value will affect only to the specified limited area.
void xlSheetAddrToRowCol(SheetHandle handle, const wchar_t* addr, int* row, int* col, int* rowRelative, int* 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.
const wchar_t* xlSheetRowColToAddr(SheetHandle handle, int row, int col, int rowRelative, int 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.
Returns the sheet's tab color.
void xlSheetSetTabColor(SheetHandle handle, int color)
Sets the color for the sheet's tab.
int xlSheetGetTabRgbColor(int* red, int* green, int* blue)
Returns the sheet's tab RGB color.
void xlSheetSetTabRgbColor(SheetHandle handle, int red, int green, int blue)
Sets the RGB color for the sheet's tab.

int xlSheetAddIgnoredError(SheetHandle handle, int rowFirst, int colFirst, int rowLast, int colLast, int 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 0 if error occurs. Get an error info with the xlBookErrorMessage().

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 xlSheetAddDataValidation(SheetHandle handle, int type, int op, int rowFirst, int rowLast, int colFirst, int colLast, const wchar_t* value1, const wchar_t* value2)

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.

void xlSheetAddDataValidationEx(SheetHandle handle, int type, int op, int rowFirst, int rowLast, int colFirst, int colLast, const wchar_t* value1, const wchar_t* value2, int allowBlank, int hideDropDown, int showInputMessage, int showErrorMessage, const wchar_t* promptTitle, const wchar_t* prompt, const wchar_t* errorTitle, const wchar_t* error, int 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 xlSheetAddDataValidation();
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 xlSheetAddDataValidationDouble(SheetHandle handle, int type, int 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 xlSheetAddDataValidation() method.
void xlSheetAddDataValidationDoubleEx(SheetHandle handle, int type, int op, int rowFirst, int rowLast, int colFirst, int colLast, double value1, double value2, int allowBlank, int hideDropDown, int showInputMessage, int showErrorMessage, const wchar_t* promptTitle, const wchar_t* prompt, const wchar_t* errorTitle, const wchar_t* error, int 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 xlSheetAddDataValidationEx() method.
void xlSheetRemoveDataValidations(SheetHandle handle)
Removes all data validations for the sheet (only for xlsx files).
int xlSheetFormControlSize(SheetHandle handle)
Returns a number of form controls in this worksheet (only for xlsx files).
FormControlHandle xlSheetFormControl(SheetHandle handle, 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 xlSheetFormControlSize() function.
ConditionalFormattingHandle xlSheetAddConditionalFormatting(SheetHandle handle)
Adds a conditional formatting rules to the sheet (only for xlsx files).
int xlSheetGetActiveCell(SheetHandle handle, int* row, int* col)
Gets an active cell of the sheet. Returns 1 if an active cell is found otherwise returns 0.
void xlSheetSetActiveCell(SheetHandle handle, int row, int col)
Sets an active cell of the sheet.
const wchar_t* xlSheetSelectionRange(SheetHandle handle)
Returns a range of the selection.
void xlSheetAddSelectionRange(SheetHandle handle, const wchar_t* sqref)
Adds a range to the selection.
void xlSheetRemoveSelection(SheetHandle handle)
Removes all selection.

Home Index Book Sheet Format Font AutoFilter FilterColumn RichString FormControl ConditionalFormatting ConditionalFormat Examples