Platforms to show: All Mac Windows Linux Cross-Platform

Back to XLBookMBS class.

Next items

XLBookMBS.AddCustomNumFormat(customNumFormat as string) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a new custom number format to the workbook.

The format string customNumFormat indicates how to format and render the numeric value of a cell. See custom format strings guidelines (in FAQ). Returns the custom format identifier. It's used in XLFormatMBS.NumFormat. Returns 0 if error occurs. Get error info with XLBookMBS.ErrorMessage property.

Some examples using this method:

XLBookMBS.AddFont(initFont as XLFontMBS = nil) as XLFontMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a new font to the workbook, initial parameters can be copied from other font.

Returns nil if error occurs.

The initFont can be from another book.

XLBookMBS.AddFormat(initFormat as XLFormatMBS = nil) as XLFormatMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a new format to the workbook, initial parameters can be copied from other format.
Example
// define a format for red fill
Dim redBackgroundFormat As XLFormatMBS = book.AddFormat
Dim c As Color = &cFF0000
// will give back numeric value of color for RGB mode or color index
Dim n As Integer = book.PackColor(c)
// we set forecolor of the pattern to fill background!
redBackgroundFormat.PatternForegroundColor = n
redBackgroundFormat.PatternBackgroundColor = n
redBackgroundFormat.FillPattern = XLFormatMBS.FillPatternSolid

Dim row As Integer = 5 // 6th row
Dim col As Integer = 1 // Column B
Dim value As Double = 123

Call sheet.WriteNumber row, col, value, redBackgroundFormat

Returns nil if error occurs.

The initFormat can be from another book.

XLBookMBS.AddFormatFromStyle(style as Integer) as XLFormatMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 23.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a new format to the workbook from the predefined style.

Returns nil if error occurs.
Please note that after loading a file it will be removed.
See kCellStyle* constants.

XLBookMBS.AddPicture(path as folderitem) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a picture to the workbook.

Returns a picture identifier. Supports BMP, DIB, PNG, JPG and WMF picture formats. Use picture identifier with XLSheetMBS.setPicture(). Returns -1 if error occurs. Get error info with XLBookMBS.ErrorMessage property.

See also:

XLBookMBS.AddPicture(path as string) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a picture to the workbook.

Returns a picture identifier. Supports BMP, DIB, PNG, JPG and WMF picture formats. Use picture identifier with XLSheetMBS.setPicture(). Returns -1 if error occurs. Get error info with XLBookMBS.ErrorMessage property.

See also:

XLBookMBS.AddPictureData(data as string) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a picture to the workbook from memory buffer:

Returns a picture identifier. Use picture identifier with XLSheetMBS.setPicture(). Returns -1 if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLBookMBS.AddRichString as XLRichStringMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Creates a new rich string to be used in the book.

XLBookMBS.AddSheet(name as string, initSheet as XLSheetMBS = nil) as XLSheetMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Adds a new sheet to this book, returns the sheet.
Example
// create new Excel file, pass true for XLSX format or false for XLS
Dim book As New XLBookMBS(True)

// add sheet
Dim sheet As XLSheetMBS = book.addSheet("Test")
Call sheet.WriteString(2, 1, "Hello World", Nil)

// write file and launch
Dim file As FolderItem = SpecialFolder.Desktop.Child("Writing data.xlsx")

If book.Save(file) Then
file.Launch
Else
MsgBox "Failed to create file."+EndOfLine+EndOfLine+book.ErrorMessage
End If

Use initSheet parameter if you wish to copy an existing sheet.
Must be from the same book to copy sheet.

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

Some examples using this method:

XLBookMBS.AddStyledText(StyledText as StyledText) as XLRichStringMBS

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

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

XLBookMBS.BookVersion as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 13.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns the version number of this book.
Example
dim b as new XLBookMBS
dim v as Integer = b.BookVersion

dim h as Integer = v \ 256 \ 256 \ 256
dim m as Integer = v \ 256 \ 256 mod 256
dim l as Integer = v \ 256 mod 256

MsgBox "libXL "+str(h)+"."+str(m)+"."+str(l)

Version number is encoded as hex number with major version, minor version and bugfix.

XLBookMBS.Constructor(xml as boolean = false)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Creates a new XLBookMBS object.

xml: If true, creates a book in xlsx format (xml), else one in xls format (binary).

XLBookMBS.CopyContent(dest as XLBookMBS, Options as XLCopyOptionsMBS = nil)

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 15.1 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Copies all content from one book to other book.

The plugin loops through all sheets and creates matching sheets on new book.
Formats and fonts are created on the fly.

This allows you to convert from XLS to XLSX or back.
Please report if something is not copied. Of course plugin can only copy what libXL supports, so stuff like movies or diagrams are not copied.

Some examples using this method:

XLBookMBS.CustomNumFormat(fmt as Integer) as string

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns a custom format string for specified custom format identifier fmt.

See custom format string guidelines (in FAQ). Returns NULL if error occurs. Get error info with XLBookMBS.ErrorMessage property.

XLBookMBS.DefaultFont(byref fontSize as Integer) as string

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns a default font name and size for this workbook.

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

XLBookMBS.DelSheet(index 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
Deletes a sheet with specified index.

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

XLBookMBS.Font(index as Integer) as XLFontMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns a font with defined index.

Index must be less than return value of fontCount method.

XLBookMBS.FontCount 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 number of fonts in this book.

XLBookMBS.Fonts as XLFontMBS()

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns array with all defined fonts.

XLBookMBS.Format(index as Integer) as XLFormatMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns a format with defined index.

Index must be less than return value of formatCount method.

XLBookMBS.FormatCount 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 number of formats in this book.

XLBookMBS.Formats as XLFormatMBS()

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Returns array with all defined formats.

XLBookMBS.InsertSheet(index as Integer, name as string = "", initSheet as XLSheetMBS = nil) as XLSheetMBS

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Inserts a new sheet to this book at position index, returns the sheet.

Use initSheet parameter if you wish to copy an existing sheet. Must be from the same book to copy sheet.

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

XLBookMBS.Load(path as folderitem) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a xls-file into memory.

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

With the constructor of the XLBookMBS object you decide if you want to load XML format or older binary format. If the format of the book object doesn't match the file, it will fail.

Loading a file into the book object clears existing formats, sheets and fonts from previous book. You can recreate the ones you need or search them in the existing formats, fonts or sheets.

See also:

XLBookMBS.Load(path as string) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a xls-file into memory.

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

With the constructor of the XLBookMBS object you decide if you want to load XML format or older binary format. If the format of the book object doesn't match the file, it will fail.

Loading a file into the book object clears existing formats, sheets and fonts from previous book. You can recreate the ones you need or search them in the existing formats, fonts or sheets.

See also:

XLBookMBS.LoadMT(path as folderitem) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads from file.

Same as the other method without MT in name, except:

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

See also:

XLBookMBS.LoadMT(path as string) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads from file.

Same as the other method without MT in name, except:

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

See also:

XLBookMBS.LoadPartially(path as folderitem, sheetIndex as Integer, firstRow as Integer, lastRow as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a file only with specified sheet index and row range into memory.

Returns false if error occurs or true on success.. Get error info with errorMessage function.

See also:

XLBookMBS.LoadPartially(path as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a file only with specified sheet index and row range into memory.

Returns false if error occurs or true on success.. Get error info with errorMessage function.

See also:

XLBookMBS.LoadPartiallyUsingTempFile(path as folderitem, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, TempFile as folderitem) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a file only with specified sheet index and row range into memory.

Specify a temporary file for reducing memory consumption.
Returns false if error occurs or true on success.. Get error info with errorMessage function.

See also:

XLBookMBS.LoadPartiallyUsingTempFile(path as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, TempFile as String) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a file only with specified sheet index and row range into memory.

Specify a temporary file for reducing memory consumption.
Returns false if error occurs or true on success.. Get error info with errorMessage function.

See also:

XLBookMBS.LoadRaw(data as MemoryBlock) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 14.4 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a xls-file from user's memory buffer.

Returns false if error occurs. Get error info with errorMessage property.

With the constructor of the XLBookMBS object you decide if you want to load XML format or older binary format. If the format of the book object doesn't match the file, it will fail.

Loading a file into the book object clears existing formats, sheets and fonts from previous book. You can recreate the ones you need or search them in the existing formats, fonts or sheets.

See also:

XLBookMBS.LoadRaw(data as string) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a xls-file from user's memory buffer.

Returns false if error occurs. Get error info with errorMessage property.

With the constructor of the XLBookMBS object you decide if you want to load XML format or older binary format. If the format of the book object doesn't match the file, it will fail.

Loading a file into the book object clears existing formats, sheets and fonts from previous book. You can recreate the ones you need or search them in the existing formats, fonts or sheets.

See also:

XLBookMBS.LoadRawMT(data as MemoryBlock) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads from memoryblock.

Same as the other method without MT in name, except:

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

See also:

XLBookMBS.LoadRawMT(data as string) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads from string.

Same as the other method without MT in name, except:

The work is performed on a preemptive thread, so this function does not block the application and can yield time to other Xojo threads. Must be called in a Xojo thread to enjoy benefits. If called in main thread will block, but keep other background threads running.

See also:

XLBookMBS.LoadRawPartially(data as MemoryBlock, sheetIndex as Integer, firstRow as Integer, lastRow as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a file from user's memory buffer.

data: Data buffer
sheetIndex: loads a file only with specified sheet index, -1 loads all sheets
firstRow: the first row of loaded range, -1 loads all rows until lastRow
lastRow: the last row of loaded range, -1 loads all rows after firstRow.
Returns false if error occurs or true on success. Get error info with errorMessage function.

See also:

XLBookMBS.LoadRawPartially(data as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads a file from user's memory buffer.

data: Data buffer
sheetIndex: loads a file only with specified sheet index, -1 loads all sheets
firstRow: the first row of loaded range, -1 loads all rows until lastRow
lastRow: the last row of loaded range, -1 loads all rows after firstRow.
Returns false if error occurs or true on success. Get error info with errorMessage function.

See also:

XLBookMBS.LoadUsingTempFile(path as folderitem, TempFile as folderitem) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads an entire file into memory.

Specify a temporary file for reducing memory consumption.
Returns false if error occurs or true on success. Get error info with errorMessage function.

See also:

XLBookMBS.LoadUsingTempFile(path as string, TempFile as String) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Loads an entire file into memory.

Specify a temporary file for reducing memory consumption.
Returns false if error occurs or true on success. Get error info with errorMessage function.

See also:

XLBookMBS.MoveSheet(SourceIndex as integer, DestIndex as Integer) as boolean

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 17.2 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Takes a sheet with SourceIndex and insert it in front of a sheet with DestIndex.

Returns false if error occurs and true on success.

XLBookMBS.PackColor(ColorValue as color) as Integer

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Packs red, green and blue components in color type.
Example
// define a format for red fill
Dim redBackgroundFormat As XLFormatMBS = book.AddFormat
Dim c As Color = &cFF0000
// will give back numeric value of color for RGB mode or color index
Dim n As Integer = book.PackColor(c)
// we set forecolor of the pattern to fill background!
redBackgroundFormat.PatternForegroundColor = n
redBackgroundFormat.PatternBackgroundColor = n
redBackgroundFormat.FillPattern = XLFormatMBS.FillPatternSolid

Dim row As Integer = 5 // 6th row
Dim col As Integer = 1 // Column B
Dim value As Double = 123

Call sheet.WriteNumber row, col, value, redBackgroundFormat

See also:

Some examples using this method:

XLBookMBS.PackColor(red as Integer, green as Integer, blue 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
Packs red, green and blue components in color type.

See also:

XLBookMBS.PackDate(d as date) as Double

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ❌ No Desktop, Console & Web
Packs date and time information into double type.

See also:

Some examples using this method:

XLBookMBS.PackDate(year as Integer, month as Integer, day as Integer, hour as Integer = 0, min as Integer = 0, sec as Integer = 0, msec as Integer = 0) as Double

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 11.3 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Packs date and time information into double type.

See also:

XLBookMBS.PackDateTime(d as dateTime) as double

Type Topic Plugin Version macOS Windows Linux iOS Targets
method XL MBS XL Plugin 20.5 ✅ Yes ✅ Yes ✅ Yes ✅ Yes All
Packs date and time information into double type.

XLBookMBS.Picture(index as Integer, byref data as string) 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 picture at position index in memory buffer.

Parameters:
index: position in the workbook
data: reference to buffer

Returns type of picture. See PictureType* constants.

XLBookMBS.PictureCount 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 number of pictures in this workbook.

Next items

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


The biggest plugin in space...