Platforms to show: All Mac Windows Linux Cross-Platform

Back to XLSheetMBS class.

Next items

XLSheetMBS.AddConditionalFormatting as XLConditionalFormattingMBS   New in 24.2

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 24.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a conditional formatting rules to the sheet.

Only for xlsx files.
Requires LibXL in version 4.x

Some examples using this method:

XLSheetMBS.AddDataValidation(type as Integer, op as integer, rowFirst as integer, colFirst as integer, rowLast as integer, colLast as integer, value1 as String, value2 as String)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a data validation for the specified range (only for xlsx files).

type: the type of data validation.
op: the relational operator of data validation.
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;
value2: the second value for ValidationOpBetween or ValidationOpNotBetween operator;

See also:

XLSheetMBS.AddDataValidation(type as Integer, op as integer, rowFirst as integer, colFirst as integer, rowLast as integer, colLast as integer, value1 as String, value2 as String, allowBlank as Boolean, hideDropDown as Boolean = false, showInputMessage as Boolean = true, showErrorMessage as Boolean = true, promptTitle as String = "", prompt as String = "", errorTitle as string = "", error as string = "", errorStyle as integer = 0)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a data validation for the specified range (only for xlsx files).

type: the type of data validation.
op: the relational operator of data validation.
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;
value2: the second value for ValidationOpBetween or ValidationOpNotBetween 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 (ValidationTypeList);
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:

See also:

XLSheetMBS.AddHyperlink(hyperlink as string, rowFirst as Integer, rowLast as Integer, colFirst as Integer, colLast as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds the new hyperlink.

XLSheetMBS.AddIgnoredError(rowFirst as Integer, colFirst as Integer, rowLast as Integer, colLast as Integer, iError as Integer) as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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 error info with XLBookMBS.errorMessage.

XLSheetMBS.AddrToRowCol(addr as string, byref row as Integer, byref col as Integer, byref rowRelative as boolean, byref colRelative as boolean)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 12.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Converts a cell reference to row and column.
Example
// create new xls file
Var book as new XLBookMBS(false)

// create sheet
Var sheet as XLSheetMBS = book.AddSheet("Sheet1")

Var row, col as Integer
Var rowRelative, colRelative as boolean

sheet.AddrToRowCol("F7", row, col, rowRelative, colRelative)
MsgBox str(row)+" "+str(col) // shows 6 5

XLSheetMBS.AddSelectionRange(sqref as string)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 23.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a range to the selection.

XLSheetMBS.ApplyFilter

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Applies the AutoFilter to the sheet.

Some examples using this method:

XLSheetMBS.CellFormat(row as Integer, col as Integer) as XLFormatMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
property XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The cell's format.

(Read and Write computed property)

XLSheetMBS.CellType(row as Integer, col as Integer) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns cell's type.

See CellType constants.

Some examples using this method:

XLSheetMBS.Clear(rowFirst as Integer = 0, rowLast as Integer = 1048575, colFirst as Integer = 0, colLast as Integer = 16383) as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Clears cells in specified area.

XLSheetMBS.ClearPrintArea

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Clears the print area.

XLSheetMBS.ClearPrintRepeats

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Clears repeated rows and columns on each page.

XLSheetMBS.ColHidden(col as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
property XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Whether column is hidden.

(Read and Write computed property)

XLSheetMBS.ColWidth(col as Integer) as Double

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns column width.

XLSheetMBS.ColWidthPx(col as integer) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns column width in pixels.

XLSheetMBS.Constructor   Private

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
The private constructor.

XLSheetMBS.CopyCell(rowSrc as Integer, colSrc as Integer, rowDst as Integer, colDst as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies cell with format from (rowSrc, colSrc) to (rowDst, colDst).

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.CopyCellValue(dest as XLSheetMBS, SourceRow as Integer, DestRow as Integer, SourceColumn as Integer, DestColumn as Integer, Options as XLCopyOptionsMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 21.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies a cell from one sheet to another.

Source and destination sheets can be in different books.
This copies most properties and all cells.
If something is missing, please let us know.

If options is nil, we copy everything.

If source and dest sheet are the same, does same as CopyCell function.

XLSheetMBS.CopyColumn(dest as XLSheetMBS, SourceColumn as Integer, DestColumn as Integer, Options as XLCopyOptionsMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies a column from one sheet to another.

Sheets can be in different books.
This copies most properties and all cells.
If something is missing, please let us know.

If options is nil, we copy everything.

XLSheetMBS.CopyRow(dest as XLSheetMBS, SourceRow as Integer, DestRow as Integer, Options as XLCopyOptionsMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies a row from one sheet to another.

Sheets can be in different books.
This copies most properties and all cells.
If something is missing, please let us know.

If options is nil, we copy everything.

XLSheetMBS.CopySheet(dest as XLBookMBS, Options as XLCopyOptionsMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies a sheet to the other book.

This copies most properties and all cells.
If something is missing, please let us know.

If options is nil, we copy everything.

See also:

XLSheetMBS.CopySheet(dest as XLSheetMBS, Options as XLCopyOptionsMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies the sheet content to the other sheet.

This copies most properties and all cells.
If something is missing, please let us know.

If options is nil, we copy everything.

See also:

XLSheetMBS.DelHyperlink(index as Integer) as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Removes hyperlink by index.

Index: The index from 0 to HyperlinkSize.

XLSheetMBS.DelMerge(row as Integer, col as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Removes merged cells.

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.DelMergeByIndex(index as Integer) as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Removes merged cells by index.

Index: From 0 to MergeSize-1.

XLSheetMBS.DelNamedRange(name as string, scopeId as Integer = -2) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Deletes the named range by name.

Returns false if error occurs. Get error info with XLBookMBS.errorMessage().
scopeId - index of sheet for local named range or ScopeWorkbook for global named range.

XLSheetMBS.FormControl(index as integer) as XLFormControlMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 21.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns a form control with the specified index.

The index must be less than the return value of the FormControlCount property.
This allows you to batch pre-fill form fields in an Excel document.
You may need Excel to create the template with the form fields.

XLSheetMBS.GetActiveCell(byref row as integer, byref col as integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 23.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets an active cell of the sheet.

Returns true if an active cell is found otherwise returns false.

XLSheetMBS.GetHorPageBreak(index as Integer) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns row with horizontal page break at position index.

XLSheetMBS.GetMerge(row as Integer, col as Integer, byref rowFirst as Integer, byref rowLast as Integer, byref colFirst as Integer, byref colLast as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets merged cells for cell at row, col.

Result is written in rowFirst, rowLast, colFirst, colLast.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.GetNamedRange(name as string, byref rowFirst as Integer, byref rowLast as Integer, byref colFirst as Integer, byref colLast as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the named range coordianates by name.

Returns false if specified named range isn't found or error occurs. Get error info with XLBookMBS.ErrorMessage property.

See also:

XLSheetMBS.GetNamedRange(name as string, byref rowFirst as Integer, byref rowLast as Integer, byref colFirst as Integer, byref colLast as Integer, ScopeID as Integer, byref Hidden as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the named range coordianates by name.

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.
Returns false if specified named range isn't found or error occurs. Get error info with XLBookMBS.errorMessage.

See also:

XLSheetMBS.GetPicture(index as Integer, byref rowTop as Integer, byref colLeft as Integer, byref rowBottom as Integer, byref colRight as Integer, byref width as Integer, byref height as Integer, byref offsetX as Integer, byref offsetY as Integer) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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 XLBookMBS.getPicture() for extracting binary data of picture by workbook picture index.
Returns -1 if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.GetPrintArea(byref rowFirst as Integer, byref colFirst as Integer, byref rowLast as Integer, byref colLast as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 15.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the print area.

Returns false if print area isn't found.

XLSheetMBS.GetPrintFit(byref wPages as Integer, byref hPages as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
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.

XLSheetMBS.GetPrintRepeatCols(byref colFirst as Integer, byref colLast as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 15.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets repeated columns on each page from colFirst to colLast.

Returns false if repeated columns aren't found.

XLSheetMBS.GetPrintRepeatRows(byref rowFirst as Integer, byref rowLast as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 15.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets repeated rows on each page from rowFirst to rowLast.

Returns false if repeated rows aren't found.

XLSheetMBS.GetTabRgbColor(byref red as Integer, byref green as Integer, byref blue as Integer) as Boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 23.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns the sheet's tab RGB color.

Returns true if the values have been returned.

XLSheetMBS.GetTopLeftView(byref row as Integer, byref col as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 12.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Extracts the first visible row and the leftmost visible column of the sheet.
Example
// create new xls file
Var book as new XLBookMBS(false)

// create sheet
Var sheet as XLSheetMBS = book.AddSheet("Sheet1")

// set top left view
sheet.SetTopLeftView(2,3)

// read values
Var col, row as Integer
sheet.GetTopLeftView(row, col)

MsgBox str(Row)+", "+str(col) // shows 2, 3

XLSheetMBS.GetVerPageBreak(index as Integer) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns column with vertical page break at position index.

XLSheetMBS.GroupCols(colFirst as Integer, colLast as Integer, collapsed as boolean = true) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Groups columns from colFirst to colLast.

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.GroupRows(rowFirst as Integer, rowLast as Integer, collapsed as boolean = true) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Groups rows from rowFirst to rowLast.

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.Hyperlink(index as Integer, byref rowFirst as Integer, byref rowLast as Integer, byref colFirst as Integer, byref colLast as Integer) as String

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Gets the hyperlink and its coordianates by index.

Some examples using this method:

XLSheetMBS.HyperlinkIndex(row as integer, col as integer) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 23.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Checks if the cell contains a hyperlink.

Returns an index of hyperlink if exists, -1 if there is no hyperlink in this cell.

XLSheetMBS.InsertCol(colFirst as Integer, colLast as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Inserts columns from colFirst to colLast.

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Updates existing named ranges.

XLSheetMBS.InsertColAndKeepRanges(colFirst as integer, colLast as integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Inserts columns from colFirst to colLast.

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Doesn't update existing named ranges.

Next items

The items on this page are in the following plugins: MBS XL Plugin.


The biggest plugin in space...