[Solved] Embed a sheet into a dialog window

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
JulianR
Posts: 26
Joined: Mon Mar 12, 2018 9:41 am

[Solved] Embed a sheet into a dialog window

Post by JulianR »

Hi!

I am writing a preview functionality for data import configuration dialog.
And while I have managed to write a decent preview for text/csv files, I am wondering if it is at all possible to do it for spreadsheet documents.
Can I somehow show contents of a chosen source sheet in a dialog window? I don't see controls for either OLE or Spreadsheet.

Can it be done with standard dialog mechanics?
Last edited by JulianR on Mon Oct 29, 2018 5:11 pm, edited 1 time in total.
Apache OpenOffice 4.1.1 / LibreOffice 5.3 / LibreOffice 6.0 / LibreOffice 6.2 on Windows 7
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Embed a sheet into a dialog window

Post by JeJe »

You could arrange a grid of labels and copy the row/column numbers and cell contents.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
hubert lambert
Posts: 145
Joined: Mon Jun 13, 2016 10:50 am

Re: Embed a sheet into a dialog window

Post by hubert lambert »

Hello,

A grid control exists, but it is not available from the dialog designer. So you have to build the dialog from scratch:

Code: Select all

sub main
    data = array(array("City", "Country", "Population"),_
                 array("London", "United-Kingdom", 8850000), _
                 array("Paris", "France", 2500000),_
                 array("Berlin", "Deutschland", 3500000),_
                 array("Roma", "Italia", 2800000))
    createdialog(data)
end sub

sub createdialog(data)
    dialogmodel = createUnoService("com.sun.star.awt.UnoControlDialogModel")
    dialogmodel.Title = "Grid control example"
    dialogview = createUnoService(dialogmodel.DefaultControl)
    dialogview.setModel(dialogmodel)
    dialogview.setPosSize(200, 100, 400, 220, 15)
    
    label = createlabel("Select a cell and push the OK button.")
    dialogview.addControl("lbl", label)
    
    buttonOK = createbutton("OK", 200, 180, 90, 30, 1)
    dialogview.addControl("buttonOK", buttonOK)

    buttonCANCEL = createbutton("CANCEL", 300, 180, 90, 30, 2)
    dialogview.addControl("buttonCANCEL", buttonCANCEL)

    gridview = creategrid(data)
    dialogview.addControl("grid", gridview)

    dialogview.setVisible(True)
    if dialogview.execute() = 1 then
        if ubound(gridview.SelectedRows) > -1 then
            column = gridview.CurrentColumn
            row = gridview.CurrentRow
            selectedcell = gridview.Model.GridDataModel.getCellData(column, row)
            msgbox "you have selected <" & selectedcell & ">."
        else
            msgbox "You didn't select any cell."
        end if
    end if
    dialogview.dispose()
end sub

function createlabel(label)
    model = createUnoService("com.sun.star.awt.UnoControlFixedTextModel")
    model.Label = label
    view = createUnoService(model.DefaultControl)
    view.setModel(model)
    view.setPosSize(10, 20, 380, 30, 15)
    createlabel = view
end function

function createbutton(label, X, Y, width, height, optional buttontype)
    model = createUnoService("com.sun.star.awt.UnoControlButtonModel")
    model.Label = label
    if ismissing(buttontype) then
        buttontype = 0
    end if
    model.PushButtonType = buttontype
    view = createUnoService(model.DefaultControl)
    view.setModel(model)
    view.setPosSize(X, Y, width, height, 15)
    createbutton = view
end function

function creategrid(data)
    gridmodel = createUnoService("com.sun.star.awt.grid.UnoControlGridModel")
    gridmodel.ShowRowHeader = True
    gridview = createUnoService(gridmodel.DefaultControl)
    gridview.setModel(gridmodel)
    gridview.setPosSize(10, 50, 380, 120, 15)

    insertdata(gridmodel, data(0))
    datamodel = gridmodel.GridDataModel
    for n = 1 to ubound(data)
        datamodel.addRow(n, data(n))
    next n
    creategrid = gridview
end function

sub insertdata(grid, header)
    columnfactory = grid.ColumnModel
    columnfactory.setDefaultColumns(ubound(header)+1)
    for n = 0 to columnfactory.ColumnCount-1
        column = columnfactory.Columns(n)
        column.Title = header(n)
    next n
end sub
Regards.
Attachments
gridcontrol.ods
(10.37 KiB) Downloaded 314 times
AOOo 4.1.2 on Win7 | LibreOffice on various Linux systems
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Embed a sheet into a dialog window

Post by Villeroy »

14 year old code from a user called "bibek" on oooforum.org but still fascinating.
Copy to any module and run the Main routine.

Code: Select all

REM  *****  BASIC  *****

global component1
global component2

Sub Main
	component1 = Null
	component2 = Null
	
   ' Get an AWT toolkit.
   ' OOo's AWT, not a Java AWT.
   oAwtToolkit = CreateUnoService( "com.sun.star.awt.Toolkit" )
   
   ' Create a top level window.
   oWindow = CreateTopWindow( oAwtToolkit, 100, 200, 800, 700 )
   
   ' Create a sub-window.
   oSubWindow = CreateSubWindow( oAwtToolkit, oWindow, 100, 200, 300, 400 )
   
   ' Create a sub-window.
   oSubWindow2 = CreateSubWindow( oAwtToolkit, oWindow, 450, 200, 300, 400 )
   
   ' Create a Window Listener
   oTopWindowListener = CreateUnoListener( "TopWindowListener_","com.sun.star.awt.XTopWindowListener" )
   
   oWindow.addTopWindowListener(oTopWindowListener)
   
   ' At this point, you should check out the interfaces
   '  and all of the methods that they give you for
   '  manipulating this new oWindow.
   ' See comment in CreateTopWindow() below.
   
   ' Change the background color of the window to baby blue.
   oWindow.setBackground( RGB( 240, 240, 255 ) )
   
   ' At this point, if you stop the program, you will have a
   '  new OOo window on the screen, but you cannot do anything
   '  with it.  You cannot even close it!
   ' In fact, you have to kill the OOo process, as even OOo
   '  cannot close this window since it has no Frame and
   '  therefore is not integrated into the desktop environment.
   ' So I had to go on writing additional code....
   
   ' Create a new frame.
   oFrame = CreateUnoService( "com.sun.star.frame.Frame" )
   ' Initialize this frame with our new window.
   oFrame.initialize( oWindow )
   ' Tell the frame that its parent is the Desktop.
   oFrame.setCreator( StarDesktop )
   
   ' At this point you have a window that can be resized, moved, or closed.
   
   ' Create a new frame.
   oSubFrame = CreateUnoService( "com.sun.star.frame.Frame" )
   ' Initialize this frame with our new window.
   oSubFrame.initialize( oSubWindow )
   ' Tell the frame that its parent is the Desktop.
   oSubFrame.setCreator( StarDesktop )
   
   ' Create a new frame.
   oSubFrame2 = CreateUnoService( "com.sun.star.frame.Frame" )
   ' Initialize this frame with our new window.
   oSubFrame2.initialize( oSubWindow2 )
   ' Tell the frame that its parent is the Desktop.
   oSubFrame2.setCreator( StarDesktop )
   
   
   ' add subframe to main frame
   oFrame.getFrames().append(oSubFrame)
   oFrame.getFrames().append(oSubFrame2)
   
   frames = oFrame.getFrames()
   
   'wait(500)
   'msgbox "Num Frames: " & frames.getCount()
   
   ' Note that if you wanted to, you could now load a component
   '  into your newly created window!
   component1 = oSubFrame.loadComponentFromURL( "private:factory/simpress","_self", 0, Array() )

   ' Note that if you wanted to, you could now load a component
   '  into your newly created window!
   component2 = oSubFrame2.loadComponentFromURL( "private:factory/scalc","_self", 0, Array() )

   ' Create a new button.
   oBtnCtrl = MakeButtonCtrl( oAwtToolkit, oWindow, "Meow",_
         MakeRectangle( 20, 30, 100, 40 ) )
   
   ' Create a menu.
'   CreateMenuBar( oWindow )
' menubar to be provided later....
' okay, well maybe not, see below...

	'wait(10 * 1000)	' wait ten seconds
	
	'msgbox "Click okay to close first frame..."
	'component1.close(false)
	
	'msgbox "Click okay to close second frame..."
	'component2.close(false)
	
	exit sub
	
Main_error:
	msgbox "error!"
	resume next
	
End Sub


Sub TopWindowListener_TopWindowListener(oEventObject)
End Sub

Sub TopWindowListener_windowClosing(oEventObject)
	'msgbox "window closing!"

	'wait(1000)
	
	'msgbox "Click okay to close first frame..."
	if not isNull(component1) then
		component1.close(false)
	end if
	
	'msgbox "Click okay to close second frame..."
	if not isNull(component2) then
		component2.close(false)
	end if
	
	'wait(10000)
End Sub

Sub TopWindowListener_windowClosed(oEventObject)
End Sub

Sub TopWindowListener_windowMinimized(oEventObject)
End Sub

Sub TopWindowListener_windowNormalized(oEventObject)
End Sub

Sub TopWindowListener_windowActivated(oEventObject)
End Sub

Sub TopWindowListener_windowDeactivated(oEventObject)
End Sub

Sub TopWindowListener_disposing(oEventObject)
	'msgbox "Disposing..."
	'wait(1000)
End Sub



Function CreateTopWindow( oAwtToolkit, x, y, w, h )
   oWindowDesc = CreateUnoStruct( "com.sun.star.awt.WindowDescriptor" )
   With oWindowDesc
      ' specifies a top level window on the desktop. It is also a container.
      .Type = com.sun.star.awt.WindowClass.TOP
      ' is a modal top level window on the desktop. It is also a container.
'      .Type = com.sun.star.awt.WindowClass.MODALTOP
      ' is a container that may contain other components. It is not a top window.
'      .Type = com.sun.star.awt.WindowClass.CONTAINER
      ' is the simplest window. It can be a container.
'      .Type = com.sun.star.awt.WindowClass.SIMPLE
      
      ' specifies the name of the component service.
      ' A zero length name means that the vcl creates a blank top,
      '  a container, or a simple window.
      .WindowServiceName = ""
      
      ' specifies the parent of the component.
      ' If Parent == 0 && ParentIndex == -1 , then the window is on the desktop.
'      .Parent = null
'      .Parent = createUnoValue( "com.sun.star.awt.XWindowPeer", 0 )
'      .Parent = oAwtToolkit.getDesktopWindow()
      ' specifies the index of the parent window, if available.
      ' If Parent == 0 and this struct is a member of an array,
      '  then this is the offset from the beginning of the array to the parent.
      '  A value of -1 means desktop.
'      .ParentIndex = -1
      
      ' specifies the position and size of the window.
      ' This member is ignored if the window attribute is
      '  WindowAttribute::FULLSIZE .
      .Bounds = MakeRectangle( x, y, w, h )
      
      ' specifies the window attributes.
      .WindowAttributes = 0
      ' specifies that the window is initially visible.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.SHOW
      ' specifies that the window fills the complete desktop area.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.FULLSIZE
      ' specifies that the window is optimum size.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.OPTIMUMSIZE 
      ' specifies that the window is minimum size.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.MINSIZE
      ' specifies that the window has visible borders.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.BORDER
      ' specifies that the size of the window can be changed by the user.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.SIZEABLE
      ' specifies that the window can be moved by the user.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.MOVEABLE
      ' specifies that the window can be closed by the user.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.CLOSEABLE
      '[ DEPRECATED ] specifies that the window should support the XSystemDependentWindowPeer interface.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.SYSTEMDEPENDENT
   End With
   
   oXWindowPeer = oAwtToolkit.createWindow( oWindowDesc )
   
   ' Look at the capabilities that these interfaces give you
   '  for manipulating your newly created window.
   ' Interfaces supported by oXWindowPeer...
   '  com.sun.star.awt.XTopWindow
   '  com.sun.star.awt.XWindow
   '  com.sun.star.awt.XWindowPeer
   '  com.sun.star.awt.XVclContainer
   '  com.sun.star.awt.XVclContainerPeer
   '  com.sun.star.awt.XVclWindowPeer
   '  com.sun.star.awt.XLayoutConstraints
   '  com.sun.star.awt.XView
   '  com.sun.star.awt.XDevice
   '  com.sun.star.lang.XEventListener
   '  com.sun.star.lang.XComponent
   '  com.sun.star.lang.XTypeProvider
   '  com.sun.star.accessibility.XAccessible
   '
   ' Properties...
   '  <object> MenuBar
   '  <array>  Windows
   '  <array>  Group
   '  <object> PosSize
   '  Boolean  Visible
   '  Boolean  Enable
   '  <object> Toolkit
   '  <object> Pointer
   '  Long     Background
   '  Boolean  DesignMode
   '  Long     Foreground
   '  <object> ControlFont
   '  <object> MinimumSize
   '  <object> PreferredSize
   '  <object> AccessibleContext
   '  <object> Graphics
   '  <object> Size
   '  <object> Info
   '  <array>  FontDescriptors
   
   CreateTopWindow = oXWindowPeer
End Function



Function CreateSubWindow( oAwtToolkit, parentWindow, x, y, w, h )
   oWindowDesc = CreateUnoStruct( "com.sun.star.awt.WindowDescriptor" )
   With oWindowDesc
      ' specifies a top level window on the desktop. It is also a container.
'      .Type = com.sun.star.awt.WindowClass.TOP
      ' is a modal top level window on the desktop. It is also a container.
'      .Type = com.sun.star.awt.WindowClass.MODALTOP
      ' is a container that may contain other components. It is not a top window.
'      .Type = com.sun.star.awt.WindowClass.CONTAINER
      ' is the simplest window. It can be a container.
      .Type = com.sun.star.awt.WindowClass.SIMPLE
      
      ' specifies the name of the component service.
      ' A zero length name means that the vcl creates a blank top,
      '  a container, or a simple window.
      .WindowServiceName = ""
      
      ' specifies the parent of the component.
      ' If Parent == 0 && ParentIndex == -1 , then the window is on the desktop.
'      .Parent = 0
'      .Parent = createUnoValue( "com.sun.star.awt.XWindowPeer", 0 )
'      .Parent = oAwtToolkit.getDesktopWindow()
      .Parent = parentWindow
      ' specifies the index of the parent window, if available.
      ' If Parent == 0 and this struct is a member of an array,
      '  then this is the offset from the beginning of the array to the parent.
      '  A value of -1 means desktop.
'      .ParentIndex = -1
      
      ' specifies the position and size of the window.
      ' This member is ignored if the window attribute is
      '  WindowAttribute::FULLSIZE .
      .Bounds = MakeRectangle( x, y, w, h )
      
      ' specifies the window attributes.
      .WindowAttributes = 0
      ' specifies that the window is initially visible.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.SHOW
      ' specifies that the window fills the complete desktop area.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.FULLSIZE
      ' specifies that the window is optimum size.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.OPTIMUMSIZE
      ' specifies that the window is minimum size.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.MINSIZE
      ' specifies that the window has visible borders.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.BORDER
      ' specifies that the size of the window can be changed by the user.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.SIZEABLE
      ' specifies that the window can be moved by the user.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.MOVEABLE
      ' specifies that the window can be closed by the user.
      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.CLOSEABLE
      '[ DEPRECATED ] specifies that the window should support the XSystemDependentWindowPeer interface.
'      .WindowAttributes = .WindowAttributes + com.sun.star.awt.WindowAttribute.SYSTEMDEPENDENT
   End With
   
   oXWindowPeer = oAwtToolkit.createWindow( oWindowDesc )
   
   ' Look at the capabilities that these interfaces give you
   '  for manipulating your newly created window.
   ' Interfaces supported by oXWindowPeer...
   '  com.sun.star.awt.XTopWindow
   '  com.sun.star.awt.XWindow
   '  com.sun.star.awt.XWindowPeer
   '  com.sun.star.awt.XVclContainer
   '  com.sun.star.awt.XVclContainerPeer
   '  com.sun.star.awt.XVclWindowPeer
   '  com.sun.star.awt.XLayoutConstraints
   '  com.sun.star.awt.XView
   '  com.sun.star.awt.XDevice
   '  com.sun.star.lang.XEventListener
   '  com.sun.star.lang.XComponent
   '  com.sun.star.lang.XTypeProvider
   '  com.sun.star.accessibility.XAccessible
   '
   ' Properties...
   '  <object> MenuBar
   '  <array>  Windows
   '  <array>  Group
   '  <object> PosSize
   '  Boolean  Visible
   '  Boolean  Enable
   '  <object> Toolkit
   '  <object> Pointer
   '  Long     Background
   '  Boolean  DesignMode
   '  Long     Foreground
   '  <object> ControlFont
   '  <object> MinimumSize
   '  <object> PreferredSize
   '  <object> AccessibleContext
   '  <object> Graphics
   '  <object> Size
   '  <object> Info
   '  <array>  FontDescriptors
   
   CreateSubWindow = oXWindowPeer
End Function




Function MakeButtonCtrl( oAwtToolkit, oWindow, Optional cLabel, Optional oPosSizeRect )
   ' Create a new button model.
   oButtonModel = CreateUnoService( "com.sun.star.awt.UnoControlButtonModel")
   
   ' Create a new button control.
   oButtonCtrl = CreateUnoService( "com.sun.star.awt.UnoControlButton" )
   
   ' Tell the control that it has a model.
   oButtonCtrl.setModel( oButtonModel )
   
   ' Tell the control to create its window peer.
   ' (To understand what this means, it is helpful to
   '  read some Java documention about how Java's AWT works
   '  even though we're not working with Java AWT here.)
   ' Basically, this puts something visible onto the window.
   oButtonCtrl.createPeer( oAwtToolkit, oWindow )
   
   If Not IsMissing( cName ) Then
      oButtonModel.Label = cLabel
      oButtonModel.ImageURL = "file:///c:/tmp/test-small.jpg"
   EndIf
   If Not IsMissing( oPosSizeRect ) Then
      oButtonCtrl.setPosSize( _
                  oPosSizeRect.X, oPosSizeRect.Y,_
                  oPosSizeRect.Width, oPosSizeRect.Height,_
                  com.sun.star.awt.PosSize.POSSIZE )
   EndIf
   
   MakeButtonCtrl = oButtonCtrl
End Function


' ripped from my library....

Function MakeRectangle( ByVal nX As Long, ByVal nY As Long,_
                  ByVal nWidth As Long, ByVal nHeight As Long ) As com.sun.star.awt.Rectangle
   oRectangle = createUnoStruct( "com.sun.star.awt.Rectangle" )
   With oRectangle
      .X = nX
      .Y = nY
      .Width = nWidth
      .Height = nHeight
   End With
   MakeRectangle() = oRectangle
End Function 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Embed a sheet into a dialog window

Post by JeJe »

All a spreadsheet is is a 2 dimensional array with relationships between the elements. This thread's inspired me to write a very elementary one using labels and functionaccess. If the other solutions don't pan out then adapting the display code might suffice for displaying some data.
Attachments
DialogLabelSpreadsheet.odt
(16.83 KiB) Downloaded 290 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JulianR
Posts: 26
Joined: Mon Mar 12, 2018 9:41 am

Re: Embed a sheet into a dialog window

Post by JulianR »

Thank you all for all your help. The code samples look really interesting and do almost exactly, what I wanted to do.

Since my project was nearly complete, I had to to go with simple solution - displaying the document for preview and reloading it as hidden if the user closes the document before using import functionality. But if it gets another iteration I will use this code.

Thanks again.
Apache OpenOffice 4.1.1 / LibreOffice 5.3 / LibreOffice 6.0 / LibreOffice 6.2 on Windows 7
Post Reply