Platforms to show: All Mac Windows Linux Cross-Platform

Back to XLSheetMBS class.

Previous items

XLSheetMBS.SetFooter(footer as string, margin as Double) as boolean

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

CodeDescription
&Lspecifies the beginning of the left section
&Pspecifies the current page number
&Nspecifies the total number of pages
&\d{1,3}specifies the text font size, where font size is measured in points, for example: &9 or &36
&Sspecifies whether the strikethrough text style is on or off
&Xspecifies whether the superscript text style is on or off
&Yspecifies whether the subscript text style is on or off
&Cspecifies the beginning of the center section
&Dspecifies a date
&Tspecifies a time
&Gspecifies a picture
&Uspecifies whether the single underline text style is on or off
&Especifies whether the double underline text style is on or off
&Rspecifies the beginning of the right section
&Zspecifies a workbook file path
&Fspecifies a workbook file name
&Aspecifies a sheet name
&"fontname"specifies the text font, for example: &"Comic Sans MS"
&Bspecifies whether the bold text style is on or off
&Ispecifies whether the italic text style is on or off
&&specifies an ampersand character (&)

XLSheetMBS.SetHeader(header as string, margin as Double) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets the header text of the sheet when printed.

The text appears at the top of every page when printed. The length of the text must be less than or equal to 255. The header text can contain special commands, for example a placeholder for the page number, current date or text formatting attributes. Special commands are represented by single letter with a leading ampersand ("&"). Margin is specified in inches.

CodeDescription
&Lspecifies the beginning of the left section
&Pspecifies the current page number
&Nspecifies the total number of pages
&\d{1,3}specifies the text font size, where font size is measured in points, for example: &9 or &36
&Sspecifies whether the strikethrough text style is on or off
&Xspecifies whether the superscript text style is on or off
&Yspecifies whether the subscript text style is on or off
&Cspecifies the beginning of the center section
&Dspecifies a date
&Tspecifies a time
&Gspecifies a picture
&Uspecifies whether the single underline text style is on or off
&Especifies whether the double underline text style is on or off
&Rspecifies the beginning of the right section
&Zspecifies a workbook file path
&Fspecifies a workbook file name
&Aspecifies a sheet name
&"fontname"specifies the text font, for example: &"Comic Sans MS"
&Bspecifies whether the bold text style is on or off
&Ispecifies whether the italic text style is on or off
&&specifies an ampersand character (&)

XLSheetMBS.SetHorPageBreak(row as Integer, pageBreak 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
Sets/removes a horizontal page break.
Example
// new document
Var x as new XLBookMBS

// add a sheet
Var s as XLSheetMBS = x.AddSheet

// write a cell
call s.WriteString(5,5, "Hello")

// now put a page break there
if s.SetHorPageBreak(5, true) then
// and now we have one at row 5
MsgBox str(s.GetHorPageBreakCount)+" "+str(s.GetHorPageBreak(0))
end if

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

XLSheetMBS.SetMerge(rowFirst as Integer, rowLast as Integer, 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
Sets merged cells for range: rowFirst - rowLast, colFirst - colLast.
Example
// create new xls file
Dim book As New XLBookMBS(True)

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

// create a format with border
Dim borderFormat As XLFormatMBS = book.AddFormat
borderFormat.SetBorder 2
borderFormat.SetBorderColor &hFF0000

// now fill a cell and merge a few more cells.
Dim r1 As Boolean = sheet.WriteString(2, 2, "Hello World", borderFormat)
Dim r2 As Boolean = sheet.SetMerge(2, 4, 2, 4)

// write file
Dim file As FolderItem = SpecialFolder.Desktop.Child("Generate.xlsx")

if book.Save(file) then
file.Launch
else
MsgBox "Failed to create file."+EndOfLine+EndOfLine+book.ErrorMessage
end if

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

Some examples using this method:

XLSheetMBS.SetNamedRange(name as string, rowFirst as Integer, rowLast as Integer, colFirst as Integer, colLast as Integer, 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
Sets the named range.

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

XLSheetMBS.SetPicture(row as Integer, col as Integer, PictureID as Integer, scale as Double = 1.0, OffsetX as Integer = 0, OffsetY as Integer = 0, pos as Integer = 0)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets a picture with pictureId identifier at position row and col with scale factor.

Use XLBookMBS.addPicture() for getting picture identifier.

See also:

XLSheetMBS.SetPicture(row as Integer, col as Integer, PictureID as Integer, width as Integer, height as Integer, OffsetX as Integer = 0, OffsetY as Integer = 0, pos as Integer = 0)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets a picture with pictureId identifier at position row and col with custom size.

Use XLBookMBS.addPicture() for getting a picture identifier.

Width and height can be -1 for default size of picture.

See also:

XLSheetMBS.SetPrintArea(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 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets the print area.

XLSheetMBS.SetPrintFit(wPages as Integer, hPages as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Fits sheet width and sheet height to wPages and hPages respectively.

XLSheetMBS.SetPrintRepeatCols(colFirst as Integer, colLast as Integer)

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

XLSheetMBS.SetPrintRepeatRows(rowFirst as Integer, rowLast as Integer)

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

XLSheetMBS.SetProtectEx(protect as boolean = true, password as string = "", enhancedProtection as Integer = -1)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Protects/unprotects the sheet with password and enchanced parameters below.

It is possible to combine a few EnhancedProtection values with operator BitwiseOr.

XLSheetMBS.SetRow(row as Integer, height as Double, format as XLFormatMBS = nil, hidden as boolean = false) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets row height and format.

If format is nil then format is ignored. Row may be hidden. Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Height is in points. If you want to convert from pixels to points you can use this calculation: points = pixels * 72 / 96. So for a height of 72 pixel, you pass 54 points.

XLSheetMBS.SetRowPx(row as integer, height as integer, format as XLFormatMBS = nil, hidden as boolean = false) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 23.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets row height in pixels.

If format equals nil then format is ignored. Row may be hidden. Returns false if error occurs. Get an error info with the errorMessage function in XLBookMBS class.

XLSheetMBS.SetTabColor(colorValue as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets the color for the sheet's tab.

XLSheetMBS.SetTabRgbColor(red as Integer, green as Integer, blue as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets the color for the sheet's tab.

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

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 12.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Sets 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.SetVerPageBreak(row as Integer, pageBreak 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
Sets/removes a vertical page break.

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

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

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Splits a sheet at position (row, 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.

XLSheetMBS.SplitInfo(byref row as Integer, byref col 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
Gets the split information (position of frozen pane) in the sheet.

row: vertical position of the split;
col: horizontal position of the split.
Returns true on success.

XLSheetMBS.Table(index as Integer, byref rowFirst as Integer, byref rowLast as Integer, byref ColFirst as Integer, byref ColLast as Integer, byref headerRowCount as Integer, byref totalsRowCount as Integer) as string

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

See also:

XLSheetMBS.Table(name as string, byref rowFirst as integer, byref rowLast as integer, byref ColFirst as integer, byref ColLast as integer, byref headerRowCount as integer, byref totalsRowCount as integer) as Boolean

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

See also:

XLSheetMBS.WriteBlank(row as Integer, col as Integer, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes blank cell with specified format.

Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

Some examples using this method:

XLSheetMBS.WriteBoolean(row as Integer, col as Integer, value as boolean, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a bool value into cell with specified format.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteBoolean(2, 5, true, format)

If format is nil then format is ignored.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

XLSheetMBS.WriteComment(row as Integer, col as Integer, value as string, author as string, width as Integer, height as Integer)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a comment to the cell.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteComment(2, 5, "Sales Worldwide", "Peter", 30, 10)

row and col: cell's position
value: comment string
author: author string
width: width of text box in pixels
height: height of text box in pixels

Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

XLSheetMBS.WriteDate(row as Integer, col as Integer, value as date, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 15.0 ✅ Yes ✅ Yes ✅ Yes ❌ No Desktop, Console & Web
Writes a date/time into cell with specified format.

If format is nil then format is ignored. Internally uses XLBookMBS.datePack() for packing date/time parts to double.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

XLSheetMBS.WriteDateTime(row as integer, col as integer, value as dateTime, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a date/time into cell with specified format.

If format is nil then format is ignored. Internally uses XLBookMBS.datePack() for packing date/time parts to double.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

XLSheetMBS.WriteError(row as Integer, col as Integer, Error as Integer, format as XLFormatMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes error into the cell with specified format.

If format equals nil then format is ignored.

XLSheetMBS.WriteFormula(row as Integer, col as Integer, value as string, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a formula into cell with specified format.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteFormula(2, 5, "SUM(E16:E38)", format)

If format is nil then format is ignored. Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.
As LibXL does not calculate the result of the formula, it will not encode the result in the Excel file. It does only store the formula, so an app opening the file will have to do the calculation. But QuickLook on Mac OS X does not calculate, so results do not show there.
Examples for formulas are: "TODAY()", "IF(C1>0;ABS(C1*D1);"""")", "SUM(E16:E38)" or "E39+E39*E40".

Some examples using this method:

XLSheetMBS.WriteFormulaBool(row as Integer, col as Integer, Expression as string, value as Boolean, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a formula expression with precalculated bool value into cell with specified format.

If format equals nil then format is ignored.
Returns false if error occurs. Get error info with XLBookMBS.errorMessage.

XLSheetMBS.WriteFormulaNum(row as Integer, col as Integer, Expression as string, value as Double, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a formula expression with precalculated double value into cell with specified format.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteFormulaNum(2, 5, "SUM(E16:E38)", 123.45, format)

If format equals nil then format is ignored. Returns false if error occurs. Get error info with XLBookMBS.errorMessage.

XLSheetMBS.WriteFormulaString(row as Integer, col as Integer, Expression as string, value as String, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 16.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a formula expression with precalculated string value into cell with specified format.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim DollarFormat as XLFormatMBS // the format to use

Var Success as Boolean = Sheet.WriteFormulaNum(2, 5, "SUM(E16:E38)", "$123.45"", DollarFormat)

If format equals nil then format is ignored. Returns false if error occurs. Get error info with XLBookMBS.errorMessage.

XLSheetMBS.WriteNumber(row as Integer, col as Integer, value as Double, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a number or date/time into cell with specified format.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteNumber(2, 5, 123.45, format)

If format is nil then format is ignored. Use XLBookMBS.datePack() for packing date/time parts to double.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

Some examples using this method:

XLSheetMBS.WriteRichString(row as integer, col as integer, RichString as XLRichStringMBS, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a rich string with multiple fonts into the cell with the specified format.

Add a new rich string with the addRichString() method. If format equals nil then format is ignored.
Returns false if an error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLSheetMBS.WriteString(row as Integer, col as Integer, value as string, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a string into cell with specified format.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteString(2, 5, "Hello World", format)

If format is nil then format is ignored.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

XLSheetMBS.WriteStringAsNumber(row as integer, col as integer, value as string, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Writes a number into cell with specified format and passes it as text.
Example
Dim Sheet as XLSheetMBS // your current sheet
Dim format as XLFormatMBS // optional format

Var Success as Boolean = Sheet.WriteStringAsNumber(2, 5, "123.45", format)

Please pass number as text here to avoid running into rounding errors with floating point numbers.

If format is nil then format is ignored. Use XLBookMBS.datePack() for packing date/time parts to double.
Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.
Col and Row are zero based.
Please remember that Excel files have limits like 65536 rows and 256 columns.

XLSheetMBS.WriteStyledText(row as integer, col as integer, StyledText as StyledText, format as XLFormatMBS = nil) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.2 ✅ Yes ✅ Yes ✅ Yes ❌ No Desktop, Console & Web
Writes a styled text to a cell.

Same as building a rich string based in the StyledText and assigning it to the cell.

We convert attributes like bold, italic, underline, color, font name and size.

Previous items

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


The biggest plugin in space...