XLBookMBS.AddConditionalFormat as XLConditionalFormatMBS

// create a rule to make cells starting with A to be bold
Var cFormat As XLConditionalFormatMBS = book.addConditionalFormat()
cFormat.Font.bold = true

Var cf As XLConditionalFormattingMBS  = sheet.addConditionalFormatting()
cf.addRange(2, 10, 1, 1)
cf.addRule(cf.FormatTypeBeginWith, cFormat, "a")

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

Some examples using this method:

• /XL/Conditional formatting

XLBookMBS.AddCustomNumFormat(customNumFormat as string) as Integer

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:

• /XL/Custom Number Formats
• /XL/Writing data

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

Returns nil if error occurs.

The initFont can be from another book.

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

// define a format for red fill
Var redBackgroundFormat As XLFormatMBS = book.AddFormat
Var c As Color = &cFF0000
// will give back numeric value of color for RGB mode or color index
Var 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

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

Call sheet.WriteNumber row, col, value, redBackgroundFormat

Returns nil if error occurs.

The initFormat can be from another book.

Some examples using this method:

• /XL/Custom Number Formats
• /XL/Format
• /XL/Generate
• /XL/Invoice
• /XL/Invoice xml
• /XL/Writing data

XLBookMBS.AddFormatFromStyle(style as Integer) as XLFormatMBS

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

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.

Supports BMP, DIB, PNG, JPG and WMF picture formats.
For XLSX files you can embed SVG graphics with LibXL 4.2 or newer.

See also:

• AddPicture(path as string) as Integer

XLBookMBS.AddPicture(path as string) as Integer

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.

Supports BMP, DIB, PNG, JPG and WMF picture formats.
For XLSX files you can embed SVG graphics with LibXL 4.2 or newer.

See also:

• AddPicture(path as folderitem) as Integer

XLBookMBS.AddPictureData(data as string) as Integer

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

Supports BMP, DIB, PNG, JPG and WMF picture formats.
For XLSX files you can embed SVG graphics with LibXL 4.2 or newer.

XLBookMBS.AddRichString as XLRichStringMBS

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

// create new Excel file, pass true for XLSX format or false for XLS
Var book As New XLBookMBS(True)

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

// write file and launch
Var 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:

• /XL/AutoFilter by Values
• /XL/Conditional formatting
• /XL/Custom Number Formats
• /XL/Generate
• /XL/Invoice
• /XL/Invoice with Template/Invoice with Template
• /XL/Invoice xml
• /XL/Performance
• /XL/Styled Text
• /XL/Writing data

XLBookMBS.AddStyledText(StyledText as StyledText) as XLRichStringMBS

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

XLBookMBS.BookRemoveVBA as boolean

It should be called after loading xlsm or xlsb files. Returns true if VBA scripts were found and removed.
Returns true on success and false on failure.

XLBookMBS.BookVersion as Integer

Var b as new XLBookMBS
Var v as Integer = b.BookVersion

Var h as Integer = v \ 256 \ 256 \ 256
Var m as Integer = v \ 256 \ 256       mod 256
Var 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)

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)

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:

• /XL/Convert

XLBookMBS.CoreProperties as XLCorePropertiesMBS   New in 25.0

XLBookMBS.CustomNumFormat(fmt as Integer) as string

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

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

XLBookMBS.DelSheet(index as Integer) as boolean

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

XLBookMBS.Font(index as Integer) as XLFontMBS

Index must be less than return value of fontCount method.

XLBookMBS.FontCount as Integer

XLBookMBS.Fonts as XLFontMBS()

XLBookMBS.Format(index as Integer) as XLFormatMBS

Index must be less than return value of formatCount method.

XLBookMBS.FormatCount as Integer

XLBookMBS.Formats as XLFormatMBS()

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

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

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:

• Load(path as string) as boolean

XLBookMBS.Load(path as string) as boolean

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:

• Load(path as folderitem) as boolean

XLBookMBS.LoadMT(path as folderitem) as boolean

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:

• LoadMT(path as string) as boolean

XLBookMBS.LoadMT(path as string) as boolean

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:

• LoadMT(path as folderitem) as boolean

XLBookMBS.LoadPartially(path as folderitem, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

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

keepAllSheets parameter new in 14.2:
If this is true, all sheets are saved back with XL.Book.Save function,
But only one specified sheet is available for processing, thus LibXL can save more memory. It works only for xlsx files, LibXL ignores this flag for xls files.

See also:

• LoadPartially(path as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

XLBookMBS.LoadPartially(path as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

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

keepAllSheets parameter new in 14.2:
If this is true, all sheets are saved back with XL.Book.Save function,
But only one specified sheet is available for processing, thus LibXL can save more memory. It works only for xlsx files, LibXL ignores this flag for xls files.

See also:

• LoadPartially(path as folderitem, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

XLBookMBS.LoadPartiallyUsingTempFile(path as folderitem, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, TempFile as folderitem, keepAllSheets as Boolean = false) as boolean

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

keepAllSheets parameter new in 14.2:
If this is true, all sheets are saved back with XL.Book.Save function,
But only one specified sheet is available for processing, thus LibXL can save more memory. It works only for xlsx files, LibXL ignores this flag for xls files.

See also:

• LoadPartiallyUsingTempFile(path as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, TempFile as String, keepAllSheets as Boolean = false) as boolean

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

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

keepAllSheets parameter new in 14.2:
If this is true, all sheets are saved back with XL.Book.Save function,
But only one specified sheet is available for processing, thus LibXL can save more memory. It works only for xlsx files, LibXL ignores this flag for xls files.

See also:

• LoadPartiallyUsingTempFile(path as folderitem, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, TempFile as folderitem, keepAllSheets as Boolean = false) as boolean

XLBookMBS.LoadRaw(data as MemoryBlock) as boolean

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:

• LoadRaw(data as string) as boolean

XLBookMBS.LoadRaw(data as string) as boolean

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:

• LoadRaw(data as MemoryBlock) as boolean

XLBookMBS.LoadRawMT(data as MemoryBlock) as boolean

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:

• LoadRawMT(data as string) as boolean

XLBookMBS.LoadRawMT(data as string) as boolean

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:

• LoadRawMT(data as MemoryBlock) as boolean

XLBookMBS.LoadRawPartially(data as MemoryBlock, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

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.

keepAllSheets parameter new in 14.2:
If this is true, all sheets are saved back with XL.Book.Save function,
But only one specified sheet is available for processing, thus LibXL can save more memory. It works only for xlsx files, LibXL ignores this flag for xls files.

See also:

• LoadRawPartially(data as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

XLBookMBS.LoadRawPartially(data as string, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

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.

keepAllSheets parameter new in 14.2:
If this is true, all sheets are saved back with XL.Book.Save function,
But only one specified sheet is available for processing, thus LibXL can save more memory. It works only for xlsx files, LibXL ignores this flag for xls files.

See also:

• LoadRawPartially(data as MemoryBlock, sheetIndex as Integer, firstRow as Integer, lastRow as Integer, keepAllSheets as Boolean = false) as boolean

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

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:

• LoadUsingTempFile(path as string, TempFile as String) as boolean

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

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:

• LoadUsingTempFile(path as folderitem, TempFile as folderitem) as boolean

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

Returns false if error occurs and true on success.

XLBookMBS.PackColor(ColorValue as color) as Integer

// define a format for red fill
Var redBackgroundFormat As XLFormatMBS = book.AddFormat
Var c As Color = &cFF0000
// will give back numeric value of color for RGB mode or color index
Var 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

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

Call sheet.WriteNumber row, col, value, redBackgroundFormat

See also:

• PackColor(red as Integer, green as Integer, blue as Integer) as Integer

Some examples using this method:

• /XL/Conditional formatting
• /XL/Invoice xml