Jump to content

Macros/ScriptForge/HowToDisplayChartInDialogExample

From The Document Foundation Wiki


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.


A on-the-fly chart displayed inside a dialog
A on-the-fly chart displayed inside a dialog


How to run it in BASIC

Create a new document.

  1. Open the Basic IDE
  2. Select the first available blank module
  3. Copy and paste the Basic code
  4. Run Main()

How to run it in Python

Create a new document.

  1. Run APSO
  2. Create a new module
  3. Copy and paste the Python code below
  4. 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()


See also