Send data to CALC from VFP or other languages

Java, C++, C#, Delphi, ??? - Using the UNO bridges

Send data to CALC from VFP or other languages

Postby jcarvajal » Sun Jun 26, 2016 8:53 pm

Hi. I have one doubt?
I can send data to CALC without problem from VFP or other language but I need start my application with CALC in Hidden but at end change this mode to visible. This is necessary to increase speed.
I have looked everywhere and I have not found how to do it.
Someone could tell me how this is done?
Thanks very much!!!
Sample:
Code: Select all   Expand viewCollapse view
LOCAL oDoc
*create new CALC
oDoc = OOoOpenURL( "private:factory/scalc" )
*send data
oSheet.getCellByPosition( 0, 0 ).setString( "Month" )
oSheet.getCellByPosition( 1, 0 ).setString( "Sales" )
oSheet.getCellByPosition( 2, 0 ).setString( "End Date" )
*here I need to change Calc to visible! (Hidden in .F.)


FUNCTION OOoOpenURL( cURL )
LOCAL ARRAY aNoArgs[1]
*to assign  property Hidden
  aNoArgs[1] = OOoMakePropertyValue( "Hidden", .T. )
  LOCAL oDesktop
  oDesktop = OOoGetDesktop()
  LOCAL oDoc
  oDoc = oDesktop.LoadComponentFromUrl( cURL, "_blank", 0, @ aNoargs )
  COMARRAY( oDoc, 10 )
  RETURN oDoc
ENDFUNC

FUNCTION OOoMakePropertyValue( cName, uValue, nHandle, nState )
   LOCAL oPropertyValue
   oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" )
   
   oPropertyValue.Name = cName
   oPropertyValue.Value = uValue
   
   IF TYPE([nHandle])="N"
      oPropertyValue.Handle = nHandle
   ENDIF
   IF TYPE([nState])="N"
      oPropertyValue.State = nState
   ENDIF
   
   RETURN oPropertyValue
ENDFUNC

FUNCTION OOoCreateStruct( cTypeName )
   LOCAL oServiceManager
   oServiceManager = OOoGetServiceManager()
   
   LOCAL oStruct
   oStruct = .NULL.

   LOCAL cOldErrHandler
   cOldErrHandler = ON( "ERROR" )
   ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
      oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
   ON ERROR &cOldErrHandler
   
   IF ISNULL( oStruct )
      =__OOoReleaseCachedVars()
      oServiceManager = OOoGetServiceManager()
      oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
   ENDIF

   RETURN oStruct
ENDFUNC

FUNCTION OOoGetDesktop()
   IF (TYPE([goOOoDesktop])!="O")  OR  ISNULL( goOOoDesktop )
      PUBLIC goOOoDesktop
      goOOoDesktop = OOoServiceManager_CreateInstance( "com.sun.star.frame.Desktop" )
      COMARRAY( goOOoDesktop, 10 )
   ENDIF
   RETURN goOOoDesktop
ENDFUNC

FUNCTION OOoGetServiceManager()
   IF (TYPE([goOOoServiceManager])!="O")  OR  ISNULL( goOOoServiceManager )
      PUBLIC goOOoServiceManager
      goOOoServiceManager = CREATEOBJECT( "com.sun.star.ServiceManager" )
   ENDIF
   RETURN goOOoServiceManager
ENDFUNC


* Sugar coated routine to ask the service manager to
*  create you an instance of some other OpenOffice.org UNO object.
FUNCTION OOoServiceManager_CreateInstance( cServiceName )
   LOCAL oServiceManager
   oServiceManager = OOoGetServiceManager()
   
   LOCAL oInstance
   oInstance = .NULL.

   LOCAL cOldErrHandler
   cOldErrHandler = ON( "ERROR" )
   ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
      oInstance = oServiceManager.createInstance( cServiceName )
   ON ERROR &cOldErrHandler
   
   IF ISNULL( oInstance )
      =__OOoReleaseCachedVars()
      oServiceManager = OOoGetServiceManager()
      oInstance = oServiceManager.createInstance( cServiceName )
   ENDIF

   RETURN oInstance
ENDFUNC


PROCEDURE DoNothing__ErrorHandler( pnError, pcErrMessage, pnLineNo, pcProgramFileSys16, pcProgram, pcErrorParamSys2018 )
ENDPROC


PROCEDURE __OOoReleaseCachedVars()
   RELEASE goOOoServiceManager, goOOoDesktop, goOOoCoreReflection
ENDPROC


* Convert a local filename to an OOo URL.
FUNCTION OOoConvertToURL( cFilename )
   * Ensure leading slash.
   IF LEFT( cFilename, 1 ) != "/"
      cFileName = "/" + cFileName
   ENDIF
   
   LOCAL cURL
   cURL = CHRTRAN( cFilename, "\", "/" )   && change backslashes to forward slashes.
   cURL = "file://" + cURL
   RETURN cURL
ENDFUNC
Last edited by Hagar Delest on Sun Jun 26, 2016 9:32 pm, edited 3 times in total.
Reason: Added [code] tags. [RoryOF, Moderator] fixed typos to aid searching.
OpenOffice 3.1
jcarvajal
 
Posts: 2
Joined: Sun Jun 26, 2016 8:35 pm

Re: send data to CALC from VFP or other languages

Postby Villeroy » Sun Jun 26, 2016 9:16 pm

On any system and with any programming language it is easy to store records in a database. With database data you can do anything you want. Storing record sets in spreadsheets is stupid.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: send data to CALC from VFP or other languages

Postby jcarvajal » Sun Jun 26, 2016 9:24 pm

No, no. You not understand me. I don't want store data in spreadsheets. I only want send some data from my database to Calc like a report.
I have all my data in SQL and sometimes the users need some information from this data, so, I need to send some data to Calc.
Example: The user need the sales of the day or one customers list, etc.
I can send this data to Calc but I need send this in mode hidden and at end of this report change mode to visible for show the sheet to user.
The user must decide whether or not to save the sheet generated.
OpenOffice 3.1
jcarvajal
 
Posts: 2
Joined: Sun Jun 26, 2016 8:35 pm

Re: Send data to CALC from VFP or other languages

Postby Villeroy » Mon Jun 27, 2016 8:47 am

File>New>Database...
[X] Connect to existing database
Type: ODBC or JDBC
[X] Register the database (make it accessible for office documents)
Save the database (it is just a config file so far)

Get a Calc window and hit F4 for the data source window
Drag a table icon from the left pane onto a sheet cell
Click any sheet cell and then Data>Refresh to update the import range.

In the database document you can store meaningful queries including parameter queries and use them exactly like tables.
You may also store input forms and reports.
There are plenty of options to get selected data into a spreadheet without writing a single line of code.
[Tutorial] Using registered datasources in Calc
[Example] Loading CSV into preformatted spreadsheets
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest