Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
OpenOffice
- Basic OpenOffice Sample
- Mapping ADO to OpenOffice database
- Open Office Samples Zip
- OpenOffice Constant Enumeration
- OpenOffice Introspection
- OpenOffice OLE COM Code
- OpenOffice Pivot
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Basic OpenOffice Sample
Keywords: OpenOffice Workbook Calc getCellRangeByName getCellByPosition insertNewByName createCursorByRange com.sun.star.ServiceManager com.sun.star.frame.Desktop com.sun.star.lang.Locale com.sun.star.beans.PropertyValue LibreOffice
;Winbatch - Tested in Winbatch 2010A
; should work with versions 2004B or grester
;
;Requires OpenOffice be installed - version 2.x or greater
;
;
;Stan Littlefield April 7, 2010
;////////////////////////////////////////////////////////////////////////////////////
;////////////////////////////////////////////////////////////////////////////////////
;RUNNING THE SCRIPT
;The script uses a few subroutines and is commented throughout as it walks through
;creating an OpenOffice workbook (calc), entering data, formatting data, adding an
;addtional sheet, defining a database range and saving the workbook.
;Familiarity with working with Objects is assumed and experience with Excel is
;helpful. If you plan on migrating Excel to OpenOffice it is a bit of a learning curve
;since OO does not translate Excel macros (at least the free Open Source doesn't - the
;Novell edition supposedly does, but you pay as much for that as for Office).
;The script performs certain tasks then stops with a message before continuing.
;////////////////////////////////////////////////////////////////////////////////////
;////////////////////////////////////////////////////////////////////////////////////
;NEXT STEPS:
;You really get spoiled with methods like CopyFromRecordset() in Excel which make
;importing data a snap. OO is different, and slower, but I hope to have an example
;posted in a bit.
;////////////////////////////////////////////////////////////////////////////////////
GoSub udfs
IntControl(73,1,0,0,0)
defaults()
;check for OpenOffice Service Manager, otherwise quit
If ! IsInReg("com.sun.star.ServiceManager") Then qApp("OpenOffice Services Not Registered")
;create initial objects
oSM = CreateObject("com.sun.star.ServiceManager")
oDesk= oSM.createInstance("com.sun.star.frame.Desktop")
;create a new book
cellempty=0
celltext=2
cellvalue=1
cellformula=3
cFile=DirScript():"NewOO" ;note: no extension
NewWkb()
;reference first worksheet, rename it from Sheet1
nameSheet("Sheet1","Sales_Data")
;Delete other Sheets (by default Sheet2,Sheet3)
If oSheets.hasByName("Sheet2") Then oSheets.removeByName("Sheet2")
If oSheets.hasByName("Sheet3") Then oSheets.removeByName("Sheet3")
;populate cells with data (columns and rows are 0-based)
;OpenOffice uses c,r designation rather than r,c which Excel uses
;PutSheet is a UDF to work with different input methods
PutSheet(0,0,1,"Month")
PutSheet(1,0,1,"Sales")
PutSheet(2,0,1,"End Date")
PutSheet(0,1,1,"Jan")
PutSheet(0,2,1,"Feb")
PutSheet(0,3,1,"Mar")
PutSheet(0,4,1,"Apr")
PutSheet(0,5,1,"May")
PutSheet(0,6,1,"Jun")
PutSheet(0,7,1,"Jul")
PutSheet(0,8,1,"Aug")
PutSheet(0,9,1,"Sep")
PutSheet(0,10,1,"Oct")
PutSheet(0,11,1,"Nov")
PutSheet(0,12,1,"Dec")
PutSheet(1,1,2,3826.37)
PutSheet(1,2,2,3504.21)
PutSheet(1,3,2,2961.45)
PutSheet(1,4,2,2504.12)
PutSheet(1,5,2,2713.98)
PutSheet(1,6,2,2248.17)
PutSheet(1,7,2,1802.13)
PutSheet(1,8,2,2003.22)
PutSheet(1,9,2,1502.54)
PutSheet(1,10,2,1207.68)
PutSheet(1,11,2,1319.71)
PutSheet(1,12,2,786.03)
PutSheet(1,12,2,786.03)
PutSheet(2,1,3,"=DATE(2009;01;31)")
PutSheet(2,2,3,"=DATE(2009;02;28)")
PutSheet(2,3,3,"=DATE(2009;03;31)")
PutSheet(2,4,3,"=DATE(2009;04;30)")
PutSheet(2,5,3,"=DATE(2009;05;31)" )
PutSheet(2,6,3,"=DATE(2009;06;30)")
PutSheet(2,7,3,"=DATE(2009;07;31)")
PutSheet(2,8,3,"=DATE(2009;08;31)")
PutSheet(2,9,3,"=DATE(2009;09;30)" )
PutSheet(2,10,3,"10/31/2009")
PutSheet(2,11,3,"11/30/2009")
PutSheet(2,12,"C13","12/31/2009")
;data is in, pause and look at it
Message("","Raw Data is In")
;Now Format currency and dates
oFormats = oDoc.getNumberFormats()
oLocale = oSM.Bridge_GetStruct("com.sun.star.lang.Locale")
nFormat = oFormats.getStandardFormat( 2, oLocale ) ;com.sun.star.util.NumberFormat.DATE
oCell = oSheet.getCellRangeByName( "C2:C13" )
oCell.NumberFormat = nFormat
;currency
nFormat = oFormats.getStandardFormat( 8, oLocale ) ;com.sun.star.util.NumberFormat.CURRENCY
oCell = oSheet.getCellRangeByName( "B2:B13" )
oCell.NumberFormat = nFormat
;change the background color for the top row
oCell = oSheet.getCellRangeByName( "A1:C1" )
oCell.CellBackColor = OORGB(0, 255, 64)
Message("","Some Formatting")
;insert a new worksheet
oSheets.insertNewByName("Sales Archive", oSheets.getCount())
;insert a column - NOTE: OO is zero-based unlike Excel which is 1-based
oSheet.Columns.insertByIndex(2, 1)
oCell = oSheet.getCellRangeByName( "C2" )
;get the equivalent of Excel's usedrange and define a Database Name to apply to it
;NOTE: OO has both range names and database names, and I could not get the functions
;that create a name to work - kept getting data type errors - but a database name
;is much the same a more powerful since it is used for sorting/filtering
oCell = oSheet.GetCellbyPosition( 0, 0 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(@TRUE)
oCellAddress = oCursor.RangeAddress
oDoc.DatabaseRanges.addNewByName("NewSales", oCellAddress)
;finally, using the Range name, change the font and make it bold
oSheet.getCellRangeByName("NewSales").CharWeight=150 ;Bold
oSheet.getCellRangeByName("NewSales").CharFontName="Tahoma"
oSheet.getCellRangeByName("NewSales").CharHeight=9
;create header for inserted Column, and populate with commission formula
PutSheet(2,0,1,"Commission")
For i=1 To 12
var = "=B":i+1:"*.03"
oSheet.getCellByPosition( 2, i ).setFormula( "%var%" )
Next
Message("","Database Range 'NewSales' added":@CRLF:".03%% Commission Added":@CRLF:"Font changed to Tahoma/bold":@CRLF:"Ready to Save")
;save the workbook in OO format, need helper udf to convert file name
cURL = Cvt2Url(cFile:".sxc")
oDoc.storeAsURL( cURL, aBlank )
;and for fun, save it in Excel Format
cURL = Cvt2Url(cFile:".xls")
oDoc.storeAsURL( cURL, aXL )
;this closes everything
oDoc.dispose()
defaults()
Exit
:WBERRORHANDLER
ErrorProcessing(0,1,0,0)
defaults()
Exit
;/////////////////////////////////////////////////////////////////////////////////////////////////
:udfs
#DefineSubRoutine NewWkb()
;create 'empty' property value array used with several methods
aBlank = ArrDimension(1)
aBlank[0]=makeprop(oSM,"","")
;create MS Office Excel filter property value, for later use
aXL = ArrDimension(1)
aXL[0]=makeprop(oSM,"FilterName","MS Excel 97")
;create Date Format
aDate = ArrDimension(1)
aDate[0]=makeprop(oSM,"NumberFormat","mm/dd/yyyy")
;Create a Blank workbook
oDoc = oDesk.loadComponentFromURL( "private:factory/scalc", "_blank", 0, aBlank )
Return(1)
#EndSubRoutine
#DefineSubRoutine nameSheet(ref,name)
oSheets = oDoc.getSheets()
If IsNumber(ref)
oSheet = oSheets.getByIndex( 0 ) ;Sheet 1
Else
oSheet = oDoc.getSheets().getByName( "Sheet1" )
EndIf
oSheet.Name = name
Return(1)
#EndSubRoutine
#DefineSubRoutine PutSheet(c,r,nMode,cData)
If IsNumber(nMode)
If nMode==1 Then oSheet.getCellByPosition( c, r ).setString( cData )
If nMode==2 Then oSheet.getCellByPosition( c, r ).setValue( cData )
If nMode==3 Then oSheet.getCellByPosition( c, r ).setFormula( cData )
Else
oSheet.getCellRangeByName( nMode ).setFormula( cData )
EndIf
Return(1)
#EndSubRoutine
;standard error handler
#DefineSubRoutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If VarType(Err_Array) ==256
WbError = Err_Array[0]
wberrorhandlerline = Err_Array[1]
wberrorhandleroffset = Err_Array[2]
wberrorhandlerassignment = Err_Array[3]
wberrorhandlerfile = Err_Array[4]
wberrortextstring = Err_Array[5]
wberroradditionalinfo = Err_Array[6]
wberrorinsegment = Err_Array[7]
Else
WbError = LastError()
EndIf
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
If deleteIni
WbErrorFile = StrCat(ShortCutDir( 'AppData', 0, 0 ),'\WinBatch\Settings\')
If ! DirExist(WbErrorFile) Then WbErrorFile = DirWindows(0)
WbErrorFile = StrCat(WbErrorFile,"WWWBATCH.INI")
FileDelete(WbErrorFile)
IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorValue" ,WbError ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ErrorString" ,WbErrorString ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptLine" ,WbErrorHandlerLine ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset ,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
IniWritePvt("","","",WbErrorFile)
EndIf
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
cSep = StrCat(StrFill("=",50),@CRLF)
cLogFile = StrCat(DirScript(),"log.err")
If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
Display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
If showerr
WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
Message("An Error Was Encountered",WbErrorMsgText)
EndIf
EndIf
Return(1)
#EndSubRoutine
#DefineFunction isInReg(cProg)
Return( RegExistKey(@REGCLASSES,cProg) )
#EndFunction
;re-format files for OpenOffice processing
#DefineFunction Cvt2Url(cInfile)
Return( StrCat("file:///",StrReplace(cInfile,"\","/")) )
#EndFunction
;UDF to create PropertyValue pair structure
;each structure is assigned to an array element when
;used as arguments in a star method - ie loadComponentFromURL(_
;_url
;_window
;_flags
;_property type arguments <---- this parameter)
#DefineFunction makeProp(oSM,cName,uValue)
oStruct = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oStruct.Name = cName
oStruct.Value = uValue
Return(oStruct)
#EndFunction
#DefineSubRoutine defaults()
oCell=0
oCursor=0
oDoc=0
oObj=0
oDesk=0
oSM=0
Return(1)
#EndSubRoutine
#DefineFunction isInReg(cProg)
Return( RegExistKey(@REGCLASSES,cProg) )
#EndFunction
#DefineFunction qApp(cProg)
EXFormat=`WWWDLGED,6.1`
EXCaption=`Program is Being Terminated...`
EXX=-01
EXY=-01
EXWidth=238
EXHeight=045
EXNumControls=003
EXProcedure=`DEFAULT`
EXFont=`Microsoft Sans Serif|6144|70|34`
EXTextColor=`255|255|0`
EXBackground=`DEFAULT,0|0|255`
EXConfig=0
EX001=`197,003,036,036,PUSHBUTTON,DEFAULT,"Quit",1,1,32,"Microsoft Sans Serif|6144|70|34","0|0|255","255|255|255"`
EX002=`005,003,188,012,STATICTEXT,DEFAULT,"The script cannot continue on this PC because",DEFAULT,2,512,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|255"`
EX003=`005,019,188,020,VARYTEXT,cProg,DEFAULT,DEFAULT,4,512,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|255"`
ButtonPushed=Dialog("EX")
Exit
#EndFunction
#DefineFunction OORGB(r,g,b)
Return((r & 255) * 65536) + ((g & 255) * 256) + (b & 255)
#EndFunction
Return
;/////////////////////////////////////////////////////////////////////////////////////////////////
Article ID: W18155
Filename: Basic OpenOffice Sample.txt
File Created: 2011:07:11:08:53:36
Last Updated: 2011:07:11:08:53:36