Creating an Open Office spreadsheet file .xls

[Rick Lipkin]

To All

In searching the forums I have seen references to xBrowse and Open office but I have not seen an example of testing for the existence of Open Office or Excel .. and being able to create an ADO Open Office spreadsheet .xls

Again, I have seen bits and pieces ( enclosed is a vb script I found in the internet )

CreateObject("com.sun.star.ServiceManager")

but not how to properly create the initial object and how to create the columns and assign the columns data.

Any help would be appreciated !

Code: Select all  Expand view  RUN

This is a VBScript example
  'The service manager is always the starting point
  'If there is no office running then an office is started up
  Set objServiceManager= WScript.CreateObject("com.sun.star.ServiceManager")
 
  'Create the CoreReflection service that is later used to create structs
  Set objCoreReflection= objServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
 
  'Create the Desktop
  Set objDesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop")
 
  'Open a new empty writer document
  Dim args()
  Set objDocument= objDesktop.loadComponentFromURL("private:factory/swriter", "_blank", 0, args)
 
  'Create a text object
  Set objText= objDocument.getText
 
  'Create a cursor object
  Set objCursor= objText.createTextCursor
 
  'Inserting some Text
  objText.insertString objCursor, "The first line in the newly created text document." & vbLf, false
 
  'Inserting a second line
  objText.insertString objCursor, "Now we're in the second line", false
 
  'Create instance of a text table with 4 columns and 4 rows
  Set objTable= objDocument.createInstance( "com.sun.star.text.TextTable")
  objTable.initialize 4, 4
 
  'Insert the table
  objText.insertTextContent objCursor, objTable, false
 
  'Get first row
  Set objRows= objTable.getRows
  Set objRow= objRows.getByIndex( 0)
 
  'Set the table background color
  objTable.setPropertyValue "BackTransparent", false
  objTable.setPropertyValue "BackColor", 13421823
 
  'Set a different background color for the first row
  objRow.setPropertyValue "BackTransparent", false
  objRow.setPropertyValue "BackColor", 6710932
 
  'Fill the first table row
  insertIntoCell "A1","FirstColumn", objTable 'insertIntoCell is a helper function, see below
  insertIntoCell "B1","SecondColumn", objTable
  insertIntoCell "C1","ThirdColumn", objTable
  insertIntoCell "D1","SUM", objTable
 
  objTable.getCellByName("A2").setValue 22.5
  objTable.getCellByName("B2").setValue 5615.3
  objTable.getCellByName("C2").setValue -2315.7
  objTable.getCellByName("D2").setFormula"sum "
 
  objTable.getCellByName("A3").setValue 21.5
  objTable.getCellByName("B3").setValue 615.3
  objTable.getCellByName("C3").setValue -315.7
  objTable.getCellByName("D3").setFormula "sum "
 
  objTable.getCellByName("A4").setValue 121.5
  objTable.getCellByName("B4").setValue -615.3
  objTable.getCellByName("C4").setValue 415.7
  objTable.getCellByName("D4").setFormula "sum "
 
  'Change the CharColor and add a Shadow
  objCursor.setPropertyValue "CharColor", 255
  objCursor.setPropertyValue "CharShadowed", true
 
  'Create a paragraph break
  'The second argument is a com::sun::star::text::ControlCharacter::PARAGRAPH_BREAK constant
  objText.insertControlCharacter objCursor, 0 , false
 
  'Inserting colored Text.
  objText.insertString objCursor, " This is a colored Text - blue with shadow" & vbLf, false
 
  'Create a paragraph break ( ControlCharacter::PARAGRAPH_BREAK).
  objText.insertControlCharacter objCursor, 0, false
 
  'Create a TextFrame.
  Set objTextFrame= objDocument.createInstance("com.sun.star.text.TextFrame")
 
  'Create a Size struct.
  Set objSize= createStruct("com.sun.star.awt.Size") 'helper function, see below
  objSize.Width= 15000
  objSize.Height= 400
  objTextFrame.setSize( objSize)
 
  ' TextContentAnchorType.AS_CHARACTER = 1
  objTextFrame.setPropertyValue "AnchorType", 1
 
  'insert the frame
  objText.insertTextContent objCursor, objTextFrame, false
 
  'Get the text object of the frame
  Set objFrameText= objTextFrame.getText
 
  'Create a cursor object
  Set objFrameTextCursor= objFrameText.createTextCursor
 
  'Inserting some Text
  objFrameText.insertString objFrameTextCursor, "The first line in the newly created text frame.", _
  false
  objFrameText.insertString objFrameTextCursor, _
  vbLf & "With this second line the height of the frame raises.", false
 
  'Create a paragraph break
  'The second argument is a com::sun::star::text::ControlCharacter::PARAGRAPH_BREAK constant
  objFrameText.insertControlCharacter objCursor, 0 , false
 
  'Change the CharColor and add a Shadow
  objCursor.setPropertyValue "CharColor", 65536
  objCursor.setPropertyValue "CharShadowed", false
 
  'Insert another string
  objText.insertString objCursor, " That's all for now !!", false
 
  On Error Resume Next
      If Err Then
      MsgBox "An error occurred"
  End If
 
 
  Sub insertIntoCell( strCellName, strText, objTable)
      Set objCellText= objTable.getCellByName( strCellName)
      Set objCellCursor= objCellText.createTextCursor
      objCellCursor.setPropertyValue "CharColor",16777215
      objCellText.insertString objCellCursor, strText, false
  End Sub
 
  Function createStruct( strTypeName)
      Set classSize= objCoreReflection.forName( strTypeName)
      Dim aStruct
      classSize.createObject aStruct
      Set createStruct= aStruct
  End Function

[Enrico Maria Giordano]

Just trap the CreateObject() error using a TRY/CATCH/END structure.

EMG

[Rick Lipkin]

Enrico

YES .. agreed, Unfortunately, I do not understand the syntax to open the correct service and to build the spreadsheet within the Open Office API

The VB example is a mess and everything on-line has been no help thus far.

Rick

[Enrico Maria Giordano]

Try this, it should work:

Code: Select all  Expand view  RUN

#include "Fivewin.ch"


FUNCTION MAIN()

    LOCAL oSrvMan := CREATEOBJECT( "com.sun.star.ServiceManager" )

    LOCAL oDesktop := oSrvMan:CreateInstance( "com.sun.star.frame.Desktop" )

    LOCAL oDoc := oDesktop:LoadComponentFromURL( "private:factory/scalc", "_blank", 0, {} )

    LOCAL oSheet := oDoc:GetSheets():GetByIndex( 0 )

    oSheet:GetCellByPosition( 0, 0 ):SetString = "TEST"

    RETURN NIL

EMG

[Rick Lipkin]

Enrico

YES .. Your code worked .. I came up with a similar example ( from xbrowse.prg ) for the last parameter on the oDeskTop:Load ...

Code: Select all  Expand view  RUN

  * oDesktop := oCalc:CreateInstance( "com.sun.star.frame.Desktop" )
  *
  * // Create OpenOffice Calc Instance with the Window Hidden Property
  * aProp:={}
  * AAdd(aProp, GetPropertyValue(oCalc, "Hidden", .T. )  )
  * oBook    := oDesktop:LoadComponentFromURL( "private:factory/scalc", "_blank", 0, aProp )
  * oSheet   := oBook:GetSheets():GetByIndex( 0 )
  * oDispatcher:= oCalc:CreateInstance( "com.sun.star.frame.DispatchHelper" )
 

Unfortunately the version of xHarbour I am using does not support GetPropertyValue().. I have both Excel and Open office on my machine and I have no clue what the DispatchHelper is either ??

I was trying to automate this invisibly and to be able to save the file and close when I am done .. I notice when you initialize the CreateObject( "com.sun.star.ServiceManager" ) it starts Open Office ( xbrowse did the same thing ) .. is there a way to avoid Open Office from being visible ?

FYI xBrowse thru a runtime error for me fwh910 ( old ) .. and it blew trying to send data to the clipboard .. never reached my error

Thanks
Rick

Code: Select all  Expand view  RUN

Application
===========
   Path and name: C:\Fox\Yacht\YachtW32.Exe (32 bits)
   Size: 2,399,744 bytes
   Time from start: 0 hours 0 mins 12 secs
   Error occurred at: 11/01/2011, 14:55:46
   Error description: Error BASE/1004  Class: 'ARRAY' has no exported method: HUMANPRESENTABLENAME
   Args:
     [   1] = A   { ... }

Stack Calls
===========
   Called from:  => HUMANPRESENTABLENAME(0)
   Called from: .\source\classes\XBROWSE.PRG => PASTEUNFORMATTEDTEXT(6680)
   Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:TOCALC(6451)
   Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:TOEXCEL(5993)
   Called from: .\source\classes\XBROWSE.PRG => (b)XBROWSE(10935)
 

[Enrico Maria Giordano]

What do you get issuing

Code: Select all  Expand view  RUN

? oSrvMan:Hidden

from my sample?

EMG

[anserkk]

Unfortunately the version of xHarbour I am using does not support GetPropertyValue()

GetPropertyValue() is not a xHarbour/Harbour function, it is a static function available in xBrowse.prg

I have no clue what the DispatchHelper is either ??

The dispatcher provides a simple mechanism for invoking OpenOffice's internal functionality with limited knowledge of how the internals work.

The following sample shows how to open an OpenOffice Calc, Hidden

Code: Select all  Expand view  RUN

oCalc:= CreateObject( "com.sun.star.ServiceManager" )
oDesktop := oCalc:CreateInstance( "com.sun.star.frame.Desktop" )
aProp:={}
AAdd(aProp,GetPropertyValue(oCalc, "Hidden", .T. )  )
oBook    := oDesktop:LoadComponentFromURL("private:factory/scalc", "_blank", 0, aProp )
oSheet   := oBook:GetSheets():GetByIndex( 0 )
 

Regards
Anser

[Rick Lipkin]

Anser and Enrico

Anser .. I cut and paste the GetPropertyValue function from xBrowse and was able to use the

Code: Select all  Expand view  RUN

AAdd(aProp,GetPropertyValue(oCalc, "Hidden", .T. )  )
   oBook    := oDesktop:LoadComponentFromURL( "private:factory/scalc", "_blank", 0, aProp )
 

Enrico .. the oCalc:Hidden() did not work

Unfortunitly .. the Libre Office ( same as open office ) interface starts and the oBook is hidden and I can make it visible again .. It would be nice if the Libre Office interface could be launched invisible .. also .. the oCalc object can not be End(), CLose(), or Quit() .. so you have to close the interface manually .. If there are any suggestions on how to 'close" the oCalc object programatically .. I would appreciate that solution ..

[

Also, I would like to 'save as' to .xls .. and looking at the xbrowse ToCalc() there is an array aSaveAs .. which does not seem very well documented

Code: Select all  Expand view  RUN

METHOD ToCalc( bProgress, nGroupBy, nPasteMode, aSaveAs, aCols ) CLASS TXBrowse
 

I am trying to figure out what the array containes that is in the aSaveAs parameter .. I see in this xbrowse code some clues ?

Code: Select all  Expand view  RUN

// If you want to convert this to other formats like PDF format or MS Excel
   IF Len(aSaveAs) > 0
      FOR I:=1 TO Len(aSaveAs)
         cFormat:=Upper(aSaveAs[i][1])
         cFileName:=aSaveAs[i][2]
         * Ensure leading slash.
         IF LEFT( cFilename, 1 ) != "/"
            cFileName:= "/" + cFileName
         ENDIF

         cURL:= StrTran( cFilename, "\", "/" )   // change backslashes to forward slashes.
         cURL = "file://" + cURL

         aProp:={} ; nPos:=0
         nPos:=AScan(aOOFilters,{ |x| x[1] == cFormat})
         IF nPos > 0
            AAdd(aProp,GetPropertyValue(oCalc, "FilterName", aOOFilters[nPos][2])  )
            cURL:=cURL+"."+cFormat
            oBook:StoreToURL( cURL, aProp )
         Endif

      Next
   ENDIF
 

It would be nice if I could boil this down to just convert and save the oBook as .xls ?? Here is my working code so far .. any suggestions to solve these last two problems would be appreciated.

Rick Lipkin

Code: Select all  Expand view  RUN

// Open Office

   Try
     oCalc   := CreateObject( "com.sun.star.ServiceManager" )
   Catch
       CLOSE DATABASES
       SAYING := "Sorry .. Open Office\Libre Office Calc"
       SAYING += "does not appear to be loaded on this machine"
       MsgInfo(saying )
       FERASE( cDefa+"\DBTMP\"+SITEiDX)
       FERASE( cDefa+"\DBTMP\"+SITEDBF)
       oDLG:END()
       RETURN(.f.)
   End Try

   oDesktop := oCalc:CreateInstance( "com.sun.star.frame.Desktop" )
   aProp:={}
   AAdd(aProp,GetPropertyValue(oCalc, "Hidden", .T. )  )
   oBook    := oDesktop:LoadComponentFromURL( "private:factory/scalc", "_blank", 0, aProp )
   oSheet   := oBook:GetSheets():GetByIndex( 0 )

   nROW := 1
   nREC := 0

   cLINE := 'Generating Open Office\Libre Calc File '+STR(nREC)
   oLINE:ReFresh()
   SysReFresh()

   oSheet:GetCellByPosition( 0, 0 ):SetString := "ADDRESS"
   oSheet:GetCellByPosition( 1, 0 ):SetString := "STREET"
   oSheet:GetCellByPosition( 2, 0 ):SetString := "BLDG"
   oSheet:GetCellByPosition( 3, 0 ):SetString := "UNIT"
   oSheet:GetCellByPosition( 4, 0 ):SetString := "RATIO"
   oSheet:GetCellByPosition( 5, 0 ):SetString := "YRBUILT"
   oSheet:GetCellByPosition( 6, 0 ):SetString := "MODEL"
   oSheet:GetCellByPosition( 7, 0 ):SetString := "BEDRMS"
   oSheet:GetCellByPosition( 8, 0 ):SetString := "FIREPLACE"

   Do WHILE .not. EOF()

      oSheet:GetCellByPosition( 0, nRow ):SetString  := D->address
      oSheet:GetCellByPosition( 1, nRow ):SetString  := D->street
      oSheet:GetCellByPosition( 2, nRow ):SetString  := D->bldg
      oSheet:GetCellByPosition( 3, nRow ):SetString  := D->unit
      oSheet:GetCellByPosition( 4, nRow ):SetString  := D->ratio
      oSheet:GetCellByPosition( 5, nRow ):SetString  := D->yrbuilt
      oSheet:GetCellByPosition( 6, nRow ):SetString  := D->model
      oSheet:GetCellByPosition( 7, nRow ):SetString  := D->bedrms
      oSheet:GetCellByPosition( 8, nRow ):SetString  := D->fireplace

      nRow++
      nREC++

      cLINE := 'Generating Open Office\Libre Calc File '+STR(nREC)
      oLINE:ReFresh()
      SysReFresh()

      SELECT 4
      SKIP

   ENDDO

   msginfo( "setvisible")
   oBook:GetCurrentController():GetFrame():GetContainerWindow():SetVisible(.T.)
   oBook:CurrentController:select( oSheet:GetCellByPosition( 0,0 ) )

   msginfo( "close")

   oBook:Close(1) // closes the spreadsheet
 
   // need the saveas code here //


  * oCalc:Quit()  // run time error here
  * oCalc:End()
  * oCalc:CLose()

   CLOSE DATABASES
   FERASE( cDefa+"\DBTMP\"+SITEiDX)
   FERASE( cDefa+"\DBTMP\"+SITEDBF)
   oDLG:END()
   RETURN(.t.)

//----------------------------------------------------------------------------//
STATIC FUNCTION GetPropertyValue(oService, cName, xValue )
LOCAL oArg

oArg       := oService:Bridge_GetStruct( "com.sun.star.beans.PropertyValue" )
oArg:Name  := cName
oArg:Value := xValue

RETURN oArg

[Enrico Maria Giordano]

Enrico .. the oCalc:Hidden() did not work

Try

Code: Select all  Expand view  RUN

? oCalc:Hidden

without brackets.

EMG

[Rick Lipkin]

Enrico

oCalc:Hidden will not compile .. incomplete statement or unbalanced delimiters

Rick

[Enrico Maria Giordano]

Please try exactly

Code: Select all  Expand view  RUN

? oCalc:Hidden

It could not execute but must compile.

EMG

[Rick Lipkin]

Enrico

Your suggestion compiled but gave a run-time error and the Libre Office interface still opened ..

Rick

[Enrico Maria Giordano]

Ok, sorry.

EMG

[anserkk]

Dear Mr.Rick,

It would be nice if I could boil this down to just convert and save the oBook as .xls ?? Here is my working code so far .. any suggestions to solve these last two problems would be appreciated.

Rick Lipkin

Try this

Code: Select all  Expand view  RUN

aProp:={}
AAdd(aProp,GetPropertyValue(oCalc, "FilterName", "MS Excel 97")  )
cUrl:="file:///d:/Test.xls"  // Only Forward slashes
oBook:StoreToURL( cURL, aProp )
oBook:Close(1)  // To Close OpenOffice Calc
 

looking at the xbrowse ToCalc() there is an array aSaveAs .. which does not seem very well documented

aSaveAs,the fourth optional parameter while invoking ToCalc(), is a 2 dimensional array. The first element is the SaveAs FileName and the second element is the file format to be used
For Eg:

Code: Select all  Expand view  RUN

aSaveAs:={  {"D:\MyFileName","PDF"},;
           {"D:\MyFileName","XLS"},;
           {"D:\MyFileName","HTML"} }

Regards
Anser

[Rick Lipkin]

Anser

I was able to figure out how to save the file and your solution is correct ! .. The final problem is closing the oCalc object .. end(), quit(), terminate() .. I have exhausted the internet to find a solution to close the oCalc := CreateObject( "com.sun.star.ServiceManager" ) ..

FYI .. I un-installed Libre office and installed Open office ( uses the same API ) and the Servicemanager screen is hidden .. but is resident in memory soffice.exe soffice.bin .. the only difference is that Libre Office stayed open and you had to close the ServiceMaster manually which is not acceptable.

Again .. if I can find a solution to close the oCalc object .. it would solve the final problem.

Thanks
Rick Lipkin

Code: Select all  Expand view  RUN

// Open Office

   Try
     oCalc   := CreateObject( "com.sun.star.ServiceManager" )
   Catch
       CLOSE DATABASES
       SAYING := "Sorry .. Open Office\Libre Office Calc"
       SAYING += "does not appear to be loaded on this machine"
       MsgInfo(saying )
       FERASE( cDefa+"\DBTMP\"+SITEiDX)
       FERASE( cDefa+"\DBTMP\"+SITEDBF)
       oDLG:END()
       RETURN(.f.)
   End Try

   oDesktop := oCalc:CreateInstance( "com.sun.star.frame.Desktop" )
   aProp:={}
   AAdd(aProp,GetPropertyValue(oCalc, "Hidden", .T. )  )
   oBook    := oDesktop:LoadComponentFromURL( "private:factory/scalc", "_blank", 0, aProp )
   oSheet   := oBook:GetSheets():GetByIndex( 0 )

   nROW := 1
   nREC := 0

   cLINE := 'Generating Open Office\Libre Calc File '+STR(nREC)
   oLINE:ReFresh()
   SysReFresh()

   oSheet:GetCellByPosition( 0, 0 ):SetString := "ADDRESS"
   oSheet:GetCellByPosition( 1, 0 ):SetString := "STREET"
   oSheet:GetCellByPosition( 2, 0 ):SetString := "BLDG"
   oSheet:GetCellByPosition( 3, 0 ):SetString := "UNIT"
   oSheet:GetCellByPosition( 4, 0 ):SetString := "RATIO"
   oSheet:GetCellByPosition( 5, 0 ):SetString := "YRBUILT"
   oSheet:GetCellByPosition( 6, 0 ):SetString := "MODEL"
   oSheet:GetCellByPosition( 7, 0 ):SetString := "BEDRMS"
   oSheet:GetCellByPosition( 8, 0 ):SetString := "FIREPLACE"

   Do WHILE .not. EOF()

      oSheet:GetCellByPosition( 0, nRow ):SetString  := D->address
      oSheet:GetCellByPosition( 1, nRow ):SetString  := D->street
      oSheet:GetCellByPosition( 2, nRow ):SetString  := D->bldg
      oSheet:GetCellByPosition( 3, nRow ):SetString  := D->unit
      oSheet:GetCellByPosition( 4, nRow ):SetString  := D->ratio
      oSheet:GetCellByPosition( 5, nRow ):SetString  := D->yrbuilt
      oSheet:GetCellByPosition( 6, nRow ):SetString  := D->model
      oSheet:GetCellByPosition( 7, nRow ):SetString  := D->bedrms
      oSheet:GetCellByPosition( 8, nRow ):SetString  := D->fireplace

      nRow++
      nREC++

      cLINE := 'Generating Open Office\Libre Calc File '+STR(nREC)
      oLINE:ReFresh()
      SysReFresh()

      SELECT 4
      SKIP

   ENDDO

   aProp := {}
   AAdd(aProp,GetPropertyValue(oCalc,"XLS","MS Excel 97" )  )

   oBook:StoreToUrl("file:///c:/dbtmp/rick.xls", aProp )

 *  msginfo( "setvisible")
 *  oBook:GetCurrentController():GetFrame():GetContainerWindow():SetVisible(.T.)
 *  oBook:CurrentController:select( oSheet:GetCellByPosition( 0,0 ) )

   msginfo( "close")

   oBook:Close(1)
 *  oCalc:Terminate()  // can not close ..

   CLOSE DATABASES
   FERASE( cDefa+"\DBTMP\"+SITEiDX)
   FERASE( cDefa+"\DBTMP\"+SITEDBF)
   oDLG:END()
   RETURN(.t.)