OpenOffice.info

Code: Alles auswählen

function pivot_special( optional arg )

URL_Main = "vnd.sun.star.script:_py_module_.py$"
URL_Args = "?language=Python&location=document"


	doc = thisComponent
   sURL = URL_Main & "data_pivot_special" & URL_Args
   oSP = doc.getScriptProvider()
   oScript = oSP.getScript(sURL)
   oScript.invoke( Array( ),Array(),Array())
end function

Code: Alles auswählen

...
pivot_special
....

Code: Alles auswählen

from collections import defaultdict

context = XSCRIPTCONTEXT


def set_Array( sheet , olist,startcol=0,startrow=0):
    rowsize = len( olist ) -1 + startrow
    colsize = len( olist[0] )-1 + startcol
    oRange = sheet.getCellRangeByPosition(startcol, startrow, colsize , rowsize)
    oRange.setDataArray( olist )




def data_pivot_special(dummy=None):

    """
    transforms data from order:

    vars    keys
    var1    1
    var2    1
    var3    1
    varx    1
    ...
    var1    2
    var3    2
    var1    3
    ...

    to:

    key     count   vars    .       .       .
    1       4       var1    var2    var3    varx    ...
    2       2       var1    var3

    ! eliminates keylines with only one variation !
    """
    doc = context.getDocument()
    sel = doc.getCurrentSelection()
    datasheet = sel.Spreadsheet
    dataarray = sel.getDataArray()

    datadict = defaultdict(list)

    for line in dataarray:
        datadict[ line[1] ].append(line[0])

    maxcount = max(len(value) for value in datadict.itervalues())

    output = []
    for key, values in datadict.iteritems():
        if len(values) > 1:  # only keys with more then one variations
            row = [key, len(values)]
            row.extend(values)
            row.extend(['']* (maxcount-row[1]))
            output.append(row)

    output = [tuple(row ) for row in output]

    set_Array(datasheet, tuple(output), 3, 1)

Code: Alles auswählen

Sub Formel
	oDoc = thisComponent
	oSheet = oDoc.Sheets.getByName ("Tabelle1")
	myView = oDoc.CurrentController
	oCursor = oSheet.createCursor
	oCursor.gotoStart(True)
	oCursor.GotoEndOfUsedArea(True)
	letzte_Zeile = oCursor.getRangeAddress.EndRow+1
	letzte_Spalte= oCursor.getRangeAddress.EndColumn
	oRange = oSheet.getCellRangeByPosition(0,0,1,letzte_Zeile)

REM Sortieren

  	Dim aSortFields(0) As New com.sun.star.util.SortField
  	Dim aSortDesc(1) As New com.sun.star.beans.PropertyValue

  	aSortFields(0).Field = 1
  	aSortFields(0).SortAscending = Ascending
  	aSortFields(0).FieldType = com.sun.star.util.SortFieldType.AUTOMATIC
  	aSortDesc(0).Name = "SortFields"
  	aSortDesc(0).Value = aSortFields()
  	aSortDesc(1).Name = "ContainsHeader"
  	aSortDesc(1).Value = bhasHeader
 
  	oRange.sort(aSortDesc())
  	SortCalcTable=True	
	
	myCell=oSheet.getCellbyposition(1,letzte_Zeile)
	myView.select(oCell)
	
	myCell=oDoc.getCurrentSelection().getCellAddress()
	oRow=myCell.Row
	oColumn=myCell.column
	myCell=oSheet.getCellByPosition(ocolumn,orow)
	oColumnname=myCell.getColumns.getByIndex(0).getName()

	cellname=ocolumnname+ltrim(str(orow))

REM Formel Häufigkeit in Spalte C 
	
	oCell = oSheet.getCellbyposition(2,1)
	oCell.Formula = "=countif($B$2:$B$"+letzte_Zeile+";B2)"
	oRange = oSheet.getCellRangeByPosition(2,1,2,letzte_Zeile) 
	oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)

	aDat = oRange.getDataArray()
	oRange.setDataArray(aDat)
	
	For i = 1 To letzte_Zeile
	If oSheet.GetCellByPosition(2, i).Value = "1" Then
	oSheet.Rows.removeByIndex(i,1)
	i = i - 1
	letzte_Zeile = letzte_Zeile - 1
	End If
	Next i 

REM Formel Zustand 1

	oCell = oSheet.getCellbyposition(3,1)
	oCell.FormulaLocal = "=WENN(C2>1;A2;"""")"
	oRange = oSheet.getCellRangeByPosition(3,1,3,letzte_Zeile) 
	oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)
	
REM Formel Zustand 2

	oCell = oSheet.getCellbyposition(4,1)
	oCell.FormulaLocal = "=WENN(C2>1;A3;"""")"
	oRange = oSheet.getCellRangeByPosition(4,1,4,letzte_Zeile) 
	oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)
	
REM Formel Zustand 3

	oCell = oSheet.getCellbyposition(5,1)
	oCell.FormulaLocal = "=WENN(C2>2;A4;"""")"
	oRange = oSheet.getCellRangeByPosition(5,1,5,letzte_Zeile) 
	oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)

REM Formel zur Zustand X?

REM Formeln gegen Werte austauschen

   oRange = oSheet.getCellRangeByPosition(3,1,5,letzte_Zeile)
	aDat = oRange.getDataArray()
	oRange.setDataArray(aDat)	


end sub