Example: /XL/Writing data

Online Documentation   -   Statistics   -   FAQ   -   Plugin Parts (All, Dependencies)   -   Class hierarchy

New in Version 22.2 22.3 22.4 22.5 23.0 23.1 23.2 23.3 23.4 23.5 24.0 24.1

The list of the   topics,   classes,   interfaces,   controls,   modules,   global methods by category,   global methods by name,   screenshots,   licenses   and   examples.

Platforms to show: All Mac Windows Linux Cross-Platform

/XL/Writing data


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.


💬 Ask a question or report a problem