Xojo Developer Conference
25/27th April 2018 in Denver.
MBS Xojo Conference
6/7th September 2018 in Munich, Germany.

Platforms to show: All Mac Windows Linux Cross-Platform

Previous items Next items

XLSheetMBS.ReadString(row as Integer, col as Integer, byref format as XLFormatMBS) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Reads a string and its format from cell.
Notes:
Returns "" if specified cell doesn't contain string or 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.

See also:

Feedback, Comments & Corrections

XLSheetMBS.RemoveCol(colFirst as Integer, colLast as Integer) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Removes columns from colFirst to colLast.
Notes: Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Feedback, Comments & Corrections

XLSheetMBS.RemoveComment(row as Integer, col as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 16.5 Yes Yes Yes Yes No
Function: Removes a comment from the cell (only for xls format).

Feedback, Comments & Corrections

XLSheetMBS.RemoveDataValidations
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 17.2 Yes Yes Yes Yes No
Function: Removes all data validations for the sheet (only for xlsx files).

Feedback, Comments & Corrections

XLSheetMBS.RemoveFilter
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 16.5 Yes Yes Yes Yes No
Function: Removes the AutoFilter from the sheet.

Feedback, Comments & Corrections

XLSheetMBS.RemoveRow(rowFirst as Integer, rowLast as Integer) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Removes rows from rowFirst to rowLast.
Notes: Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Feedback, Comments & Corrections

XLSheetMBS.RightToLeft as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
property XL MBS XL Plugin 14.0 Yes Yes Yes Yes No
Function: Whether the text is displayed in right-to-left mode.
Notes:
true - the text is displayed in right-to-left mode,
false - the text is displayed in left-to-right mode.
(Read and Write computed property)

Feedback, Comments & Corrections

XLSheetMBS.RowColToAddr(row as Integer, col as Integer, rowRelative as boolean = true, colRelative as boolean = true) as string
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 12.2 Yes Yes Yes Yes No
Function: Converts row and column to a cell reference.
Example:
// create new xls file
dim book as new XLBookMBS(false)

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

MsgBox sheet.RowColToAddr(5,5) // shows F6
MsgBox sheet.RowColToAddr(5,5,false,false) // shows $F$6

Feedback, Comments & Corrections

XLSheetMBS.RowHeight(row as Integer) as Double
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Returns row height.

Feedback, Comments & Corrections

XLSheetMBS.RowHidden(row as Integer) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
property XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Whether row is hidden.
Notes: (Read and Write computed property)

Feedback, Comments & Corrections

XLSheetMBS.SetAutoFitArea(rowFirst as Integer = 0, colFirst as Integer = 0, rowLast as Integer = -1, colLast as Integer = -1)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 15.1 Yes Yes Yes Yes No
Function: Sets the borders for autofit column widths feature.
Notes: The method setCol with -1 width value will affect only to the specified limited area.

Feedback, Comments & Corrections

XLSheetMBS.SetCol(colFirst as Integer, colLast as Integer, width as Double, format as XLFormatMBS = nil, hidden as boolean = false) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets column width and format for all columns from colFirst to colLast.
Notes:
If format is nil then format is ignored. Columns may be hidden. Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

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.

Feedback, Comments & Corrections

XLSheetMBS.SetFooter(footer as string, margin as Double) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets the footer text for the sheet when printed.
Notes:
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 (&)

Feedback, Comments & Corrections

XLSheetMBS.SetHeader(header as string, margin as Double) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets the header text of the sheet when printed.
Notes:
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 (&)

Feedback, Comments & Corrections

XLSheetMBS.SetHorPageBreak(row as Integer, pageBreak as boolean = true) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets/removes a horizontal page break.
Example:
// new document
dim x as new XLBookMBS

// add a sheet
dim 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
Notes: Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Feedback, Comments & Corrections

XLSheetMBS.SetMerge(rowFirst as Integer, rowLast as Integer, colFirst as Integer, colLast as Integer) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets merged cells for range: rowFirst - rowLast, colFirst - colLast.
Notes: Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Feedback, Comments & Corrections

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 Console & Web iOS
method XL MBS XL Plugin 14.0 Yes Yes Yes Yes No
Function: Sets the named range.
Notes:
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.

Feedback, Comments & Corrections

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 Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets a picture with pictureId identifier at position row and col with scale factor.
Notes: Use XLBookMBS.addPicture() for getting picture identifier.

See also:

Feedback, Comments & Corrections

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 Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets a picture with pictureId identifier at position row and col with custom size.
Notes: Use XLBookMBS.addPicture() for getting a picture identifier.

See also:

Feedback, Comments & Corrections

XLSheetMBS.SetPrintArea(rowFirst as Integer, rowLast as Integer, colFirst as Integer, colLast as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets the print area.

Feedback, Comments & Corrections

XLSheetMBS.SetPrintFit(wPages as Integer, hPages as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Fits sheet width and sheet height to wPages and hPages respectively.

Feedback, Comments & Corrections

XLSheetMBS.SetPrintRepeatCols(colFirst as Integer, colLast as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets repeated columns on each page from colFirst to colLast.

Feedback, Comments & Corrections

XLSheetMBS.SetPrintRepeatRows(rowFirst as Integer, rowLast as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets repeated rows on each page from rowFirst to rowLast.

Feedback, Comments & Corrections

XLSheetMBS.SetProtectEx(protect as boolean = true, password as string = "", enhancedProtection as Integer = -1)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 16.5 Yes Yes Yes Yes No
Function: Protects/unprotects the sheet with password and enchanced parameters below.
Notes: It is possible to combine a few EnhancedProtection values with operator BitwiseOr.

Feedback, Comments & Corrections

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 Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets row height and format.
Notes:
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.

Feedback, Comments & Corrections

XLSheetMBS.SetTabColor(colorValue as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 16.5 Yes Yes Yes Yes No
Function: Sets the color for the sheet's tab.

Feedback, Comments & Corrections

XLSheetMBS.SetTabRgbColor(red as Integer, green as Integer, blue as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 16.5 Yes Yes Yes Yes No
Function: Sets the color for the sheet's tab.

Feedback, Comments & Corrections

XLSheetMBS.SetTopLeftView(row as Integer, col as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 12.2 Yes Yes Yes Yes No
Function: Sets the first visible row and the leftmost visible column of the sheet.
Example:
// create new xls file
dim book as new XLBookMBS(false)

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

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

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

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

Feedback, Comments & Corrections

XLSheetMBS.SetVerPageBreak(row as Integer, pageBreak as boolean = true) as boolean
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Sets/removes a vertical page break.
Notes: Returns false if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Feedback, Comments & Corrections

XLSheetMBS.Split(row as Integer, col as Integer)
Type Topic Plugin Version macOS Windows Linux Console & Web iOS
method XL MBS XL Plugin 11.3 Yes Yes Yes Yes No
Function: Splits a sheet at position (row, col).
Notes: 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.

Feedback, Comments & Corrections

Previous items Next items

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




Links
MBS Xojo Plugins