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.
OpenOffice Pivot
Keywords: OpenOffice Pivot OO Excel Pivot Tables com.sun.star com.sun.star.sheet.DataPilotFieldOrientation.ROW LibreOffice
;Winbatch - OpenOffice:Create Spreadsheet and Data Pilot
;
;Stan Littlefield, April 14, 2010
;
;a definite Work-In-Progress:
;One of the biggest hassles of working with OpenOffice from a WB
;viewpoint (same as saying from a VBA viewpoint) is OO has no
;TypeLibs where you can see enumerations for constants. The aim of this
;script was to determine a way to code a Pivot Table from a database
;range. OO doesn't convert Excel Pivot Tables, but has a substitute
;called a Data Pilot, with a GUI similar to Pivots. The problem is that
;although you can find 'code' that simulates a VBA interface, there are
;lines that will error out in VBA or WB. For example: OO has a property called
;Orientation which applies to data - will it be a Page, a Column, a Row
;or Data. Assuming the data field is named oField, you will see
;something like:
;oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW
;which cannot be executed in WB. but
;oField.Orientation = 2 works. And it only took 1-2 hours of googling
;to find this out. Same thing with the .function SUM=2 AVERAGE=4
;
;The script produces a most elementary Data Pilot, which is limited by
;the actual data being represented. But, it's a start.
;//////////////////////////////////////////////////////////////////////////////////////////
GoSub udfs
IntControl(73,1,0,0,0)
;initialize Object vars
oSM=0
oDesk=0
oDoc=0
oStruct=0
;check for OpenOffice Service Manager
If ! IsInReg("com.sun.star.ServiceManager") Then qApp("OpenOffice Services Missing")
cFile=DirScript():"NewOO" ;note: no extension
;create initial Objects
oSM = CreateObject("com.sun.star.ServiceManager")
oDesk= oSM.createInstance("com.sun.star.frame.Desktop")
;create 'empty' property value array used with several methods
aBlank = ArrDimension(1)
aBlank[0]=makeprop(oSM,"","")
;create MS Office Excel filter property value
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, then reference Sheet1
oDoc = oDesk.loadComponentFromURL( "private:factory/scalc", "_blank", 0, aBlank )
oSheets = oDoc.getSheets()
oSheet = oSheets.getByIndex( 0 ) ;Sheet 1
;rename the sheet
oDoc.getSheets().getByName( "Sheet1" ).Name = "Sales_Data"
;Delete other Sheets
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)
oSheet.getCellByPosition( 0, 0 ).setString( "Month" )
oSheet.getCellByPosition( 1, 0 ).setString( "Sales" )
oSheet.getCellByPosition( 2, 0 ).setString( "End Date" )
oSheet.getCellByPosition( 0, 1 ).setString( "Jan" )
oSheet.getCellByPosition( 0, 2 ).setString( "Feb" )
oSheet.getCellByPosition( 0, 3 ).setString( "Mar" )
oSheet.getCellByPosition( 0, 4 ).setString( "Apr" )
oSheet.getCellByPosition( 0, 5 ).setString( "May" )
oSheet.getCellByPosition( 0, 6 ).setString( "Jun" )
oSheet.getCellByPosition( 0, 7 ).setString( "Jul" )
oSheet.getCellByPosition( 0, 8 ).setString( "Aug" )
oSheet.getCellByPosition( 0, 9 ).setString( "Sep" )
oSheet.getCellByPosition( 0, 10 ).setString( "Oct" )
oSheet.getCellByPosition( 0, 11 ).setString( "Nov" )
oSheet.getCellByPosition( 0, 12 ).setString( "Dec" )
oSheet.getCellByPosition( 1, 1 ).setValue( 3826.37 )
oSheet.getCellByPosition( 1, 2 ).setValue( 3504.21 )
oSheet.getCellByPosition( 1, 3 ).setValue( 2961.45 )
oSheet.getCellByPosition( 1, 4 ).setValue( 2504.12 )
oSheet.getCellByPosition( 1, 5 ).setValue( 2713.98 )
oSheet.getCellByPosition( 1, 6 ).setValue( 2248.17 )
oSheet.getCellByPosition( 1, 7 ).setValue( 1802.13 )
oSheet.getCellByPosition( 1, 8 ).setValue( 2003.22 )
oSheet.getCellByPosition( 1, 9 ).setValue( 1502.54 )
oSheet.getCellByPosition( 1, 10 ).setValue( 1207.68 )
oSheet.getCellByPosition( 1, 11 ).setValue( 1319.71 )
oSheet.getCellByPosition( 1, 12 ).setValue( 786.03 )
oSheet.getCellByPosition( 2, 1 ).setFormula( "=DATE(2009;01;31)" )
oSheet.getCellByPosition( 2, 2 ).setFormula( "=DATE(2009;02;28)" )
oSheet.getCellByPosition( 2, 3 ).setFormula( "=DATE(2009;03;31)" )
oSheet.getCellByPosition( 2, 4 ).setFormula( "=DATE(2009;04;30)" )
oSheet.getCellByPosition( 2, 5 ).setFormula( "=DATE(2009;05;31)" )
oSheet.getCellByPosition( 2, 6 ).setFormula( "=DATE(2009;06;30)" )
oSheet.getCellByPosition( 2, 7 ).setFormula( "=DATE(2009;07;31)" )
oSheet.getCellByPosition( 2, 8 ).setFormula( "=DATE(2009;08;31)" )
oSheet.getCellByPosition( 2, 9 ).setFormula( "=DATE(2009;09;30)" )
;note different date type
oSheet.getCellByPosition( 2, 10 ).setFormula( "10/31/2009" )
oSheet.getCellByPosition( 2, 11 ).setFormula( "11/30/2009" )
oSheet.getCellRangeByName( "C13" ).setFormula( "12/31/2009" )
oSheet.getCellByPosition( 0, 13 ).setString( "Jan" )
oSheet.getCellByPosition( 0, 14 ).setString( "Feb" )
oSheet.getCellByPosition( 0, 15).setString( "Mar" )
oSheet.getCellByPosition( 0, 16 ).setString( "Apr" )
oSheet.getCellByPosition( 0, 17 ).setString( "May" )
oSheet.getCellByPosition( 0, 18 ).setString( "Jun" )
oSheet.getCellByPosition( 0, 19 ).setString( "Jul" )
oSheet.getCellByPosition( 0, 20 ).setString( "Aug" )
oSheet.getCellByPosition( 0, 21).setString( "Sep" )
oSheet.getCellByPosition( 0, 22 ).setString( "Oct" )
oSheet.getCellByPosition( 0, 23 ).setString( "Nov" )
oSheet.getCellByPosition( 0, 24 ).setString( "Dec" )
oSheet.getCellByPosition( 1, 13 ).setValue( 1987.37 )
oSheet.getCellByPosition( 1, 14).setValue( 4000.21 )
oSheet.getCellByPosition( 1, 15).setValue( 3167.45 )
oSheet.getCellByPosition( 1, 16 ).setValue( 978.12 )
oSheet.getCellByPosition( 1, 17 ).setValue( 1358.98 )
oSheet.getCellByPosition( 1, 18 ).setValue( 1456.17 )
oSheet.getCellByPosition( 1, 19).setValue( 4023.13 )
oSheet.getCellByPosition( 1, 20 ).setValue( 770.22 )
oSheet.getCellByPosition( 1, 21 ).setValue( 423.54 )
oSheet.getCellByPosition( 1, 22 ).setValue( 3971.68 )
oSheet.getCellByPosition( 1, 23 ).setValue( 2000.71 )
oSheet.getCellByPosition( 1, 24 ).setValue( 2000.03 )
oSheet.getCellByPosition( 2, 13 ).setFormula( "=DATE(2009;01;31)" )
oSheet.getCellByPosition( 2, 14 ).setFormula( "=DATE(2009;02;28)" )
oSheet.getCellByPosition( 2, 15 ).setFormula( "=DATE(2009;03;31)" )
oSheet.getCellByPosition( 2, 16 ).setFormula( "=DATE(2009;04;30)" )
oSheet.getCellByPosition( 2, 17 ).setFormula( "=DATE(2009;05;31)" )
oSheet.getCellByPosition( 2, 18 ).setFormula( "=DATE(2009;06;30)" )
oSheet.getCellByPosition( 2, 19 ).setFormula( "=DATE(2009;07;31)" )
oSheet.getCellByPosition( 2, 20 ).setFormula( "=DATE(2009;08;31)" )
oSheet.getCellByPosition( 2, 21 ).setFormula( "=DATE(2009;09;30)" )
;note different date type
oSheet.getCellByPosition( 2, 22 ).setFormula( "10/31/2009" )
oSheet.getCellByPosition( 2, 23 ).setFormula( "11/30/2009" )
oSheet.getCellRangeByName( "C25" ).setFormula( "12/31/2010" )
;now set formatting for dates, currency
oFormats = oDoc.getNumberFormats()
;hopefully next line is workaround for createUnoStruct( "com.sun.star.lang.Locale" )
oLocale = oSM.Bridge_GetStruct("com.sun.star.lang.Locale")
;NOTE: I had to flesh out the numeric equivalent of the structure constant as it looks
; like it exceeds a Winbatch variable limit.
;cannot use nFormat = oFormats.getStandardFormat( com.sun.star.util.NumberFormat.DATE, oLocale )
nFormat = oFormats.getStandardFormat( 2, oLocale ) ;com.sun.star.util.NumberFormat.DATE
oCell = oSheet.getCellRangeByName( "C2:C25" )
oCell.NumberFormat = nFormat
sCharLocale = oDoc.getPropertyValue("CharLocale")
nFormatId = oDoc.getNumberFormats.queryKey("MM/DD/YYYY", sCharLocale, @FALSE)
If nFormatId == -1 Then nFormatId = oDoc.getNumberFormats.addNew(sNumberFormat, sCharLocale)
oCell.SetPropertyValue("NumberFormat",nFormatId)
nFormat = oFormats.getStandardFormat( 8, oLocale ) ;com.sun.star.util.NumberFormat.CURRENCY
oCell = oSheet.getCellRangeByName( "B2:B25" )
oCell.NumberFormat = nFormat
oCell = oSheet.getCellRangeByName( "A1:C1" )
oCell.CellBackColor = OORGB(0, 255, 64)
oSheets.insertNewByName("Sales Pivot", oSheets.getCount())
oCell = oSheet.GetCellbyPosition( 0, 0 )
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(@TRUE)
oCellAddress = oCursor.RangeAddress
oDoc.DatabaseRanges.addNewByName("NewSales", oCellAddress)
;data has been entered and a range name created
;so now create a Data Pilot in the 2nd tab
;a Data Pilot is a poor man's Pivot Table
oSheet1 = oSheets.getByIndex( 1 )
oCell = oSheet1.GetCellbyPosition( 0, 0 )
oDPAddress = oCell.getcellAddress()
oTables = oSheet.GetDataPilotTables()
oTdescriptor = oTables.CreateDataPilotDescriptor()
oTdescriptor.setSourceRange(oCellAddress)
oFields = oTdescriptor.getDataPilotFields()
oField = oFields.getbyIndex(0)
oField.Orientation = 2
oField = oFields.getbyIndex(1)
oField.Orientation = 4
oField.Function = 2
oField = oFields.getbyIndex(1)
oField.Orientation = 4
oField.Function = 4
oTables.insertNewByName("DataPilot1",oDPAddress,oTdescriptor)
oSheet1.Columns.OptimalWidth = @TRUE
;no bells and whistles
Message("","Data Added,not saved":@CRLF:"Data Pilot On 2nd Tab")
:End ;release vars
oTables=0
oFields=0
oField=0
oTdescriptor=0
oDPAddress=0
oCell=0
oSheet=0
oSheet1=0
oCursor=0
oDoc=0
oObj=0
oDesk=0
oSM=0
Exit
:WBERRORHANDLER
ErrorProcessing(0,1,0,0)
oSM=0
oDesk=0
oDoc=0
oStruct=0
Exit
;////////////////////////////////////////////////////////////////////////////////////////////////////////
:udfs
#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
#DefineFunction qApp(cProg)
oCell=0
oCursor=0
oDoc=0
oObj=0
oDesk=0
oSM=0
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
;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
#DefineFunction OORGB(r,g,b)
Return((r & 255) * 65536) + ((g & 255) * 256) + (b & 255)
#EndFunction
Return
;////////////////////////////////////////////////////////////////////////////////
Article ID: W18161
Filename: OpenOffice Pivot.txt
File Created: 2011:07:11:08:55:22
Last Updated: 2011:07:11:08:55:22