Macros/ScriptForge/HowToDisplayChartInDialogExample
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to display a chart in a dialog
Authored by Jean-Pierre Ledure.
The chart is built completely from scratch:
- Data is extracted from a database.
- The data is stored in a Calc sheet.
- A pivot table and a chart are derived from the data.
- The chart is stored in a file for loading in the dialog.

How to run it in BASIC
Create a new document.
- Open the Basic IDE
- Select the first available blank module
- Copy and paste the Basic code
- Run Main()
How to run it in Python
Create a new document.
- Run APSO
- Create a new module
- Copy and paste the Python code below
- Save and run the Main() method
Code
=
REM SCRIPTFORGE WIKI EXAMPLE
REM How to display a Calc chart in a dialog ?
REM Minimal required version: LibreOffice 7.6
REM Used services
REM Dialog, DialogControl, Calc, Chart, Database
Option Explicit
'*******************************************************************
'*** Choose whether the Calc chart factory remains hidden or not
'*******************************************************************
Const calc_hidden = True
'*******************************************************************
'* Run the demo below
'*******************************************************************
Sub Main(Optional event As Object)
Dim file As String ' The filename in which the chart is stored
Dim dialog As Object ' The dialog object
Dim image As Object ' The dialog control in which the chart has to be displayed
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
file = MakeChart()
dialog = SetupDialog()
' Load the chart into the dialog image control
image = dialog.Controls("Chart")
image.Picture = file
dialog.Execute()
dialog.Terminate()
End Sub
'*******************************************************************
'*Build the chart in a new Calc sheet
'*******************************************************************
Function MakeChart() As String
' The chart is stored in a temporary file. Filename is returned.
Dim database As Object ' The database class instance where the data us stored
Dim calc As Object ' The Calc class instance where the data is loaded
Dim chart As Object ' The Chart class instance describing the chart to be built
Dim data As Variant ' A 2D array containing the database data
Dim SQL As String ' A SQL SELECT statement
Dim datarange As String ' The Calc range containing the database data after injection
Dim pivot As String ' The Calc range containing the pivot table to be rendered as a chart
Dim file As String ' The file name to return
Const filetype = "png"
Dim ui As Object ' The UI service
Dim fs As Object ' The FileSystem service
Set ui = CreateScriptService("UI")
Set fs = CreateScriptService("FileSystem")
' Get the data
database = CreateScriptService("Database", , "Bibliography")
SQL = "SELECT [Custom1] AS [Language], [Identifier] FROM [biblio] ORDER BY [Language] ASC"
data = database.GetRows(SQL, header := True)
database.CloseDatabase()
' Inject the data in a new Calc sheet
calc = ui.CreateDocument("Calc", hidden := CBool(calc_hidden))
datarange = calc.SetArray("A1", data)
pivot = calc.CreatePivotTable("Pivot1", datarange, targetcell := "D1", datafields := "Identifier;Count", _
rowfields := "Language", rowtotals := False, columntotals := False, _
filterbutton := False)
' Make and export chart
calc.InsertSheet("Chart")
chart = calc.CreateChart("NumberByLanguage", "Chart", pivot, rowheader := True, columnheader := True)
With chart
.ChartType = "Donut"
.Dim3D = True
.Legend = True
End With
file = fs.GetTempName(filetype)
chart.ExportToFile(file, filetype)
' Housekeeping
If calc_hidden Then calc.CloseDocument(SaveAsk := False)
MakeChart = file
End Function
'*******************************************************************
'* May be defined with the Basic IDE
'*******************************************************************
Function SetupDialog() As Object
' Build from scratch the example dialog and its controls
Dim dialog As Object ' The dialog class instance to return
Dim control As Object ' A DialogControl class instance
Dim i As Integer
Set dialog = CreateScriptService("NewDialog", "OnTheFlyChart", Array(20, 20, 200, 200))
dialog.Caption = "Number of books per language"
With dialog
' The close button
Set control = .CreateButton("CloseButton", place := Array(.Width - 40, 10, 30, 10), push := "OK")
control.Caption = "Close"
' The chart
Set control = .CreateImageControl("Chart", Border := "3D", Place := Array(10, 10, .Width - 60, .Height - 50), _
scale := "KEEPRATIO")
End With
Set SetupDialog = dialog
End Function
=
# coding: utf-8
from __future__ import unicode_literals
from scriptforge import CreateScriptService
### SCRIPTFORGE WIKI EXAMPLE
### How to display a Calc chart in a dialog ?
### Minimal required version: LibreOffice 7.6
### Used services
### Dialog, DialogControl, Calc, Chart, Database
# *******************************************************************
# *** Choose whether the Calc chart factory remains hidden or not
# *******************************************************************
calc_hidden = True
# *******************************************************************
# * Run the demo below
# *******************************************************************
def Main(event = None):
# file The filename in which the chart is stored
# dialog The dialog object
# image The dialog control in which the chart has to be displayed
file = MakeChart()
dialog = SetupDialog()
# Load the chart into the dialog image control
image = dialog.Controls('Chart')
image.Picture = file
dialog.Execute()
dialog.Terminate()
# *******************************************************************
# * Build the chart in a new Calc sheet
# *******************************************************************
def MakeChart():
# The chart is stored in a temporary file. The file name is returned.
# database The database class instance where the data us stored
# calc The Calc class instance where the data is loaded
# chart The Chart class instance describing the chart to be built
# data A tuple of tuples containing the database data
# sql A SQL SELECT statement
# datarange The Calc range as a string containing the database data after injection
# pivot The Calc range as a string containing the pivot table to be rendered as a chart
# file The file name to return
# filetype The suffix of the file name
# ui The UI service
# fs The FileSystem service
filetype = 'png'
ui = CreateScriptService('UI')
fs = CreateScriptService('FileSystem')
# Get the data
database = CreateScriptService('Database', registrationname = 'Bibliography')
sql = 'SELECT [Custom1] AS [Language], [Identifier] FROM [biblio] ORDER BY [Language] ASC'
data = database.GetRows(sql, header = True)
database.CloseDatabase()
# Inject the data in a new Calc sheet
calc = ui.CreateDocument('Calc', hidden = calc_hidden)
datarange = calc.SetArray('A1', data)
pivot = calc.CreatePivotTable('Pivot1', datarange, targetcell = 'D1', datafields = 'Identifier;Count',
rowfields = 'Language', rowtotals = False, columntotals = False,
filterbutton = False)
# Make and export chart
calc.InsertSheet('Chart')
chart = calc.CreateChart('NumberByLanguage', 'Chart', pivot, rowheader = True, columnheader = True)
chart.ChartType, chart.Dim3D, chart.Legend = 'Donut', True, True
file = fs.GetTempName(filetype)
chart.ExportToFile(file, filetype)
# Housekeeping
if calc_hidden:
calc.CloseDocument(saveask = False)
return file
# *******************************************************************
# * May be defined with the Basic IDE
# *******************************************************************
def SetupDialog():
# Build from scratch the example dialog and its controls
# dialog The dialog class instance to return
# control A DialogControl class instance
dialog = CreateScriptService('NewDialog', 'OnTheFlyChart', (20, 20, 200, 200))
dialog.Caption = 'Number of books per language'
# The close button
control = dialog.CreateButton('CloseButton', place = (dialog.Width - 40, 10, 30, 10), push = 'OK')
control.Caption = 'Close'
# The chart
control = dialog.CreateImageControl('Chart', border = '3D', place = (10, 10, dialog.Width - 60, dialog.Height - 50),
scale = 'KEEPRATIO')
return dialog
g_exportedScripts = (Main,)
if __name__ == "__main__":
Main()