Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Vilhelm-Ion Praisach
  Where is Vilhelm-Ion Praisach?
 Resita
 Romania
 Vilhelm-Ion Praisach
 To: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 Tags
Subject: RE: excell automation widout MS-office
Thread ID: 395683 Message ID: 395727 # Views: 46 # Ratings: 3
Version: Visual FoxPro 9 SP2 Category: Microsoft Office Automation
Date: Friday, January 17, 2014 4:42:07 PM         
   


> Dear Expert
>
> In my Project, I had user certain excel application which r working fine
> as desired.
> But Yesterday I had an isssue that My prospective client had not MS-Excell
> installed but he was using Open-Office.
>
> Hence My application failed at Excel Automation point.
>
> What else can be done.
>
> Please.
>
> *Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*

A short sample

DECLARE INTEGER ShellExecute IN SHELL32.DLL INTEGER nWinHandle,STRING cOperation,STRING cFileName,STRING cParameters,STRING cDirectory,INTEGER nShowWindow

LOCAL loOfcMgr,loDesktop,loDocument,args(1),aArgs[1],loCoreReflection,loPropertyValue,loText,loCursor,loSheet,cFile,lnNrlMax2,lnNrlPag,lnNrlMax3
ON ERROR llErrExcel=ertable()
loOfcMgr=CreateObject("com.sun.star.ServiceManager")
ON ERROR
IF VARTYPE(loOfcMgr)="O" 
    * The args array is an array of "PropertyValue" objects - create by invoking OO.o reflection
    loCoreReflection=loOfcMgr.createInstance("com.sun.star.reflection.CoreReflection" )
    loPropertyValue=CREATEOBJECT("Empty")
    loCoreReflection.forName("com.sun.star.beans.PropertyValue").createobject(@loPropertyValue)
    args[1]=loPropertyValue
    args[1].name="Hidden"
    args[1].value=.T.
    * Tell VFP to pass arrays to the loDesktop object as zero-based, by reference
    loDesktop = loOfcMgr.createInstance( "com.sun.star.frame.Desktop")
    COMARRAY(loDesktop,10)
    loDocument=loDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, @args) &&Open a new empty calc document
    loSheet=loDocument.getSheets().getByIndex(0)&& get the zero'th sheet
    * Header row
    loSheet.getCellByPosition(0,0).setFormula("Name") &&Row 1
    loSheet.getCellByPosition(0,0).Charheight="14"
    loSheet.getCellByPosition(0,0).CharWeight="150" && bold
    loSheet.getCellByPosition(0,0).CharColor = RGB( 255, 0, 0 ) &&blue
    loSheet.getCellByPosition(0,0).HoriJustify=1 &&align left
    
    loSheet.getCellByPosition(1,0).setFormula("Date") 
    loSheet.getCellByPosition(1,0).Charheight="14"
    loSheet.getCellByPosition(1,0).CharWeight=150 &&bold
    loSheet.getCellByPosition(1,0).CharPosture=2 &&italic
    loSheet.getCellByPosition(1,0).CharColor = RGB( 0, 0, 255 ) &&red
    loSheet.getCellByPosition(1,0).HoriJustify=2 && align center
    loSheet.getCellByPosition(2,0).setFormula("Value") 
    loSheet.getCellByPosition(2,0).Charheight="14"
    loSheet.getCellByPosition(2,0).CharPosture=2 &&italic
    loSheet.getCellByPosition(2,0).HoriJustify=3 && align right
    * Detail rows
    loSheet.getCellByPosition(0,1).setFormula("First")
    loSheet.getCellByPosition(0,1).HoriJustify=4 && align justified
    loSheet.getCellByPosition(1,1).setFormula("=DATE(2010;12;31)") && function example
    loSheet.getCellByPosition(1,1).RotateAngle=30*100 && rotate by 30 degrees
    loSheet.getCellByPosition(2,1).setValue(1200)
    loSheet.getCellByPosition(0,2).setString("First") &&SetString example
    loSheet.getCellByPosition(1,2).setFormula("=DATE(2011;12;31)")
    loSheet.getCellByPosition(2,2).setValue(1780)
    loSheet.getCellByPosition(0,3).setFormula("First")
    loSheet.getCellByPosition(1,3).setFormula("=DATE(2012;12;31)")
    loSheet.getCellByPosition(2,3).setValue(1999)
    loSheet.getCellByPosition(0,4).setFormula("Second")
    loSheet.getCellByPosition(1,4).setFormula("=DATE(2010;12;31)")
    loSheet.getCellByPosition(2,4).setValue(100)
    loSheet.getCellByPosition(0,5).setFormula("Second")
    loSheet.getCellByPosition(1,5).setFormula("=DATE(2011;12;31)")
    loSheet.getCellByPosition(2,5).setValue(120)
    loSheet.getCellByPosition(0,6).setFormula("Second")
    loSheet.getCellByPosition(1,6).setFormula("=DATE(2012;12;31)")
    loSheet.getCellByPosition(2,6).setValue(110)
    * Total
    loSheet.getCellByPosition(0,7).setFormula("Total")
    loSheet.getCellByPosition(2,7).setFormula("=SUM(C2:C7)") && another function example
    * Save
    cFile="file:///"+SYS(5)+"\Model.ods"
    cFile=CHRTRAN(cFile,"\","/") && converting backslash into slash
    aArgs[1]=loPropertyValue && Change the export filter
    aArgs(1).Name=""
    aArgs(1).Value=""
    loDocument.storeToURL(cFile,@aArgs) &&Now save it 
    * Export XLS
    cFile="file:///"+SYS(5)+"\Model.xls"
    cFile=CHRTRAN(cFile,"\","/") && converting backslash into slash
    aArgs[1]=loPropertyValue && Change the export filter
    aArgs(1).Name="FilterName"
    aArgs(1).Value="MS Excel 97"
    loDocument.storeToURL(cFile,@aArgs) &&Now save it in Excel format. 
    * Close
    loDocument.close(.T.)
    loDocument=.Null.
    ShellExecute(0,"Open","Model.xls","","",1)
ENDIF


******************************************************************
PROCEDURE ertable
MESSAGEBOX("Yahooo!")
RETURN


P/S. I saw that placing links in Foxpro code's comment, slow the thread
http://udk.openoffice.org/common/man/tutorial/office_automation.html
http://www.openoffice.org/api/docs/common/ref/com/sun/star/table/CellRange.html
http://www.oooforum.org/forum/viewtopic.phtml?p=28389#28389
https://www.foxite.com/archives/openoffice-writer-automation-with-vfp9-0000204779.htm

My respects

ENTIRE THREAD

excell automation widout MS-office Posted by Binod Binani @ 1/17/2014 7:43:02 AM
RE: excell automation widout MS-office Posted by Borislav Borissov @ 1/17/2014 8:23:48 AM
RE: excell automation widout MS-office Posted by David Mustakim @ 1/17/2014 11:45:39 AM
RE: excell automation widout MS-office Posted by Binod Binani @ 1/17/2014 2:17:36 PM
RE: excell automation widout MS-office Posted by Borislav Borissov @ 1/17/2014 2:43:18 PM
RE: excell automation widout MS-office Posted by Tore Bleken @ 1/17/2014 8:55:26 AM
RE: excell automation widout MS-office Posted by Binod Binani @ 1/17/2014 2:16:18 PM
RE: excell automation widout MS-office Posted by Tore Bleken @ 1/17/2014 3:10:00 PM
RE: excell automation widout MS-office Posted by Vilhelm-Ion Praisach @ 1/17/2014 11:39:41 AM
RE: excell automation widout MS-office Posted by David Mustakim @ 1/17/2014 12:18:47 PM
RE: excell automation widout MS-office Posted by Binod Binani @ 1/17/2014 2:15:16 PM
RE: excell automation widout MS-office Posted by David Mustakim @ 1/17/2014 3:04:16 PM
RE: excell automation widout MS-office Posted by Vilhelm-Ion Praisach @ 1/17/2014 4:42:07 PM