Platforms to show: All Mac Windows Linux Cross-Platform
Required plugins for this example: MBS XL Plugin
You find this example project in your Plugins Download as a Xojo project file within the examples folder: /XL/Writing data
This example is the version from Sun, 20th Jan 2018.
Project "Writing data.xojo_binary_project"
Class App Inherits Application
Const kEditClear = "&Löschen"
Const kFileQuit = "Beenden"
Const kFileQuitShortcut = ""
EventHandler Sub Open()
RegisterLibXL
// create new xls file
dim book as new XLBookMBS(false)
dim logofile as FolderItem = FindFile("logo.jpg")
dim logoId as integer = book.addPicture(logofile)
// fonts
dim textFont as XLFontMBS = book.addFont()
textFont.size = 8
textFont.name = "Century Gothic"
dim titleFont as XLFontMBS = book.addFont(textFont)
titleFont.size = 38
titleFont.ColorValue = XLFontMBS.ColorGray25
dim font12 as XLFontMBS = book.addFont(textFont)
font12.size = 12
dim font10 as XLFontMBS = book.addFont(textFont)
font10.size = 10
// formats
dim textFormat as XLFormatMBS = book.addFormat
textFormat.font = textFont
textFormat.AlignH = XLFormatMBS.AlignHLeft
dim titleFormat as XLFormatMBS = book.addFormat
titleFormat.font = titleFont
titleFormat.AlignH = XLFormatMBS.AlignHRight
dim companyFormat as XLFormatMBS = book.addFormat
companyFormat.font = font12
dim dateFormat as XLFormatMBS = book.addFormat(textFormat)
dateFormat.NumFormat = book.addCustomNumFormat("mmmm\ d\,\ yyyy")
dim phoneFormat as XLFormatMBS = book.addFormat(textFormat)
phoneFormat.NumFormat = book.addCustomNumFormat("[<=9999999]###\-####;\(###\)\ ###\-####")
dim borderFormat as XLFormatMBS = book.addFormat(textFormat)
borderFormat.setBorder()
borderFormat.setBorderColor(XLFontMBS.ColorGray25)
borderFormat.AlignV = XLFormatMBS.AlignVCenter
dim percentFormat as XLFormatMBS = book.addFormat(borderFormat)
percentFormat.numFormat = book.addCustomNumFormat("#%_)")
percentFormat.AlignH = XLFormatMBS.AlignHRight
dim textRightFormat as XLFormatMBS = book.addFormat(textFormat)
textRightFormat.AlignH = XLFormatMBS.AlignHRight
textRightFormat.AlignV = XLFormatMBS.AlignVCenter
dim thankFormat as XLFormatMBS = book.addFormat
thankFormat.font = font10
thankFormat.AlignH = XLFormatMBS.AlignHCenter
dim dollarFormat as XLFormatMBS = book.addFormat(borderFormat)
dollarFormat.NumFormat = book.addCustomNumFormat("_($* # ##0.00_);_($* (# ##0.00);_($* -??_);_(@_)")
dollarFormat.AlignH = XLFormatMBS.AlignHRight
// actions
dim sheet as XLSheetMBS = book.addSheet("Sales Receipt")
sheet.DisplayGridlines = false
r sheet.setCol(1, 1, 36)
r sheet.setCol(0, 0, 10)
r sheet.setCol(2, 4, 11)
r sheet.setRow(2, 47.25)
r sheet.WriteString(2, 1, "Sales Receipt", titleFormat)
r sheet.setMerge(2, 2, 1, 4)
sheet.setPicture(2, 1, logoId, 0.2)
r sheet.WriteString(4, 0, "Apricot Ltd.", companyFormat)
r sheet.WriteString(4, 3, "Date:", textFormat)
r sheet.writeFormula(4, 4, "TODAY()", dateFormat)
r sheet.WriteString(5, 3, "Receipt #:", textFormat)
r sheet.WriteNumber(5, 4, 652, textFormat)
r sheet.WriteString(8, 0, "Sold to:", textFormat)
r sheet.WriteString(8, 1, "John Smith", textFormat)
r sheet.WriteString(9, 1, "Pineapple Ltd.", textFormat)
r sheet.WriteString(10, 1, "123 Dreamland Street", textFormat)
r sheet.WriteString(11, 1, "Moema, 52674", textFormat)
r sheet.WriteNumber(12, 1, 2659872055, phoneFormat)
r sheet.WriteString(14, 0, "Item #", textFormat)
r sheet.WriteString(14, 1, "Description", textFormat)
r sheet.WriteString(14, 2, "Qty", textFormat)
r sheet.WriteString(14, 3, "Unit Price", textFormat)
r sheet.WriteString(14, 4, "Line Total", textFormat)
for row as integer = 15 to 37
r sheet.setRow(row, 15)
for col as integer = 0 to 2
r sheet.writeBlank(row, col, borderFormat)
next
r sheet.writeBlank(row, 3, dollarFormat)
dim rowString as string = str(row+1)
r sheet.writeFormula(row, 4, "IF(C"+rowString+">0;ABS(C"+rowString+ "*D" +rowString+");"""")", dollarFormat)
next
r sheet.WriteString(38, 3, "Subtotal ", textRightFormat)
r sheet.WriteString(39, 3, "Sales Tax ", textRightFormat)
r sheet.WriteString(40, 3, "Total ", textRightFormat)
r sheet.writeFormula(38, 4, "SUM(E16:E38)", dollarFormat)
r sheet.WriteNumber(39, 4, 0.2, percentFormat)
r sheet.writeFormula(40, 4, "E39+E39*E40", dollarFormat)
r sheet.setRow(38, 15)
r sheet.setRow(39, 15)
r sheet.setRow(40, 15)
r sheet.WriteString(42, 0, "Thank you for your business!", thankFormat)
r sheet.setMerge(42, 42, 0, 4)
// items
r sheet.WriteNumber(15, 0, 45, borderFormat)
r sheet.WriteString(15, 1, "Grapes", borderFormat)
r sheet.WriteNumber(15, 2, 250, borderFormat)
r sheet.WriteNumber(15, 3, 4.5, dollarFormat)
r sheet.WriteNumber(16, 0, 12, borderFormat)
r sheet.WriteString(16, 1, "Bananas", borderFormat)
r sheet.WriteNumber(16, 2, 480, borderFormat)
r sheet.WriteNumber(16, 3, 1.4, dollarFormat)
r sheet.WriteNumber(17, 0, 19, borderFormat)
r sheet.WriteString(17, 1, "Apples", borderFormat)
r sheet.WriteNumber(17, 2, 180, borderFormat)
r sheet.WriteNumber(17, 3, 2.8, dollarFormat)
// write file
dim file as FolderItem = SpecialFolder.Desktop.Child("Writing data.xls")
if book.Save(file) then
file.Launch
else
MsgBox "Failed to create file."+EndOfLine+EndOfLine+book.ErrorMessage
end if
quit
End EventHandler
Function FindFile(name as string) As FolderItem
// Look for file in parent folders from executable on
dim parent as FolderItem = app.ExecutableFile.Parent
while parent<>Nil
dim file as FolderItem = parent.Child(name)
if file<>Nil and file.Exists then
Return file
end if
parent = parent.Parent
wend
End Function
Sub RegisterLibXL()
// once you bought a LibXL license, you can put your license key here
// see order links on our website
// http://www.monkeybreadsoftware.de/xojo/plugin-xls.shtml
#if TargetMacOS then
XLBookMBS.SetKeyGlobal "your name", "your Mac LibXL license key"
#elseif TargetWin32 then
XLBookMBS.SetKeyGlobal "your name", "your Windows LibXL license key"
#elseif TargetLinux then
XLBookMBS.SetKeyGlobal "your name", "your Linux LibXL license key"
#endif
End Sub
Sub r(b as Boolean)
// break on error
if b = false then
break
end if
End Sub
End Class
MenuBar MenuBar1
MenuItem FileMenu = "&Ablage"
MenuItem FileQuit = "#App.kFileQuit"
MenuItem EditMenu = "&Bearbeiten"
MenuItem EditUndo = "&Rückgängig"
MenuItem UntitledMenu1 = "-"
MenuItem EditCut = "&Ausschneiden"
MenuItem EditCopy = "&Kopieren"
MenuItem EditPaste = "&Einfügen"
MenuItem EditClear = "#App.kEditClear"
MenuItem UntitledMenu0 = "-"
MenuItem EditSelectAll = "&Alles auswählen"
End MenuBar
End Project
The items on this page are in the following plugins: MBS XL Plugin.