Send data to CALC from VFP or other languages

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
jcarvajal
Posts: 2
Joined: Sun Jun 26, 2016 8:35 pm

Send data to CALC from VFP or other languages

Post by jcarvajal »

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

 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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: send data to CALC from VFP or other languages

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jcarvajal
Posts: 2
Joined: Sun Jun 26, 2016 8:35 pm

Re: send data to CALC from VFP or other languages

Post by jcarvajal »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Send data to CALC from VFP or other languages

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply