OpenOffice.info

Code: Alles auswählen

#python
def fill_emtpties():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheet = sel.Spreadsheet
    empties = sel.queryEmptyCells()
    for i in range(empties.Count):
        loc = empties.getByIndex(i)
        if loc.CellStyle != 'Pivot-Tabelle Kategorie':
            continue 
        cursor = sheet.createCursorByRange(loc)
        cursor.gotoOffset(0,-1) # 1 nach oben
        cursor.collapseToSize(1,cursor.Rows.Count+1) 
        cursor.fillSeries(0,0,0,0,0)

Code: Alles auswählen

'basic
sub fill_emtpties()
    doc = ThisComponent
    sel = doc.CurrentSelection
    sheet = sel.Spreadsheet
    empties = sel.queryEmptyCells()
    for i = 0 to empties.Count-1
        em = empties.getByIndex(i)
        if em.CellStyle = "Pivot-Tabelle Kategorie" then             
        	cursor = sheet.createCursorByRange(em)
        	cursor.gotoOffset(0,-1) '# 1 nach oben
        	cursor.collapseToSize(1,cursor.Rows.Count+1) 
        	cursor.fillSeries(0,0,0,0,0)
        end if
    next i
end sub

Code: Alles auswählen

Sub Main
	tcs = ThisComponent.CurrentController.ActiveSheet
	i = 1
	j = 1 'obere Zeile/Index für Summe 
	Do 
		i = i + 1
		IF tcs.getCellByPosition(0,i).String <> tcs.getCellByPosition(0,i-1).String AND tcs.getCellByPosition(0,i).String <> "" Then
				tcs.Rows.InsertByIndex(i, 1)
				tcs.getCellByPosition(0,i).String = tcs.getCellByPosition(0, i-1).String & " Ergebnis"
				tcs.getCellByPosition(0,i).HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
				tcs.getCellByPosition(3,i).Formula = "=SUM(D" & j+1 & ":D" & i & ")/2"
				tcs.getCellByPosition(4,i).Formula = "=SUM(E" & j+1 & ":E" & i & ")/2"
				i = i + 1
				j = i 
			ElseIf tcs.getCellByPosition(0,i).String = "" AND tcs.getCellByPosition(0,i+1).String = "" Then
				tcs.getCellByPosition(0,i).String = tcs.getCellByPosition(0, i-1).String & " Ergebnis"
				tcs.getCellByPosition(0,i).HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
				tcs.getCellByPosition(3,i).Formula = "=SUM(D" & j+1 & ":D" & i & ")/2"
				tcs.getCellByPosition(4,i).Formula = "=SUM(E" & j+1 & ":E" & i & ")/2"
				Exit Do
		End If
	Loop
	
	i = 1
	j = 1
	hg = 0
	Do 
		i = i + 1
		IF tcs.getCellByPosition(1,i).String <> tcs.getCellByPosition(1,i-1).String AND tcs.getCellByPosition(1,i-1).String <> "" Then
				If tcs.getCellByPosition(1,i).String = "" Then 'Übergang Hauptgruppe
						hg = 1
					ElseIf tcs.getCellByPosition(1,i).String <> "" Then
						hg = 0
				End If
				tcs.Rows.InsertByIndex(i, 1)
				tcs.getCellByPosition(1,i).String = tcs.getCellByPosition(1, i-1).String & " Ergebnis"
				tcs.getCellByPosition(1,i).HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
				tcs.getCellByPosition(0,i).String = tcs.getCellByPosition(0,i-1).String
				tcs.getCellByPosition(3,i).Formula = "=SUM(D" & j+1 & ":D" & i & ")"
				tcs.getCellByPosition(4,i).Formula = "=SUM(E" & j+1 & ":E" & i & ")"
				i = i + 1
				j = i
				If hg = 1 Then
					tmp = tcs.getCellByPosition(3,i).Formula
					tmp2 = Split(tmp, ":")
					laenge = LEN(tmp2(1))
					tmp3 = Left(tmp2(1), laenge-1) '")" weg
					tmp3 = Right(tmp3, laenge-2) '"D" weg
					tmp3 = VAL(tmp3) + 1
					tcs.getCellByPosition(3,i).Formula = tmp2(0) & ":D" & tmp3 & ")/2"
					
					tmp = tcs.getCellByPosition(4,i).Formula
					tmp2 = Split(tmp, ":")
					laenge = LEN(tmp2(1))
					tmp3 = Left(tmp2(1), laenge-1) '")" weg
					tmp3 = Right(tmp3, laenge-2) '"D" weg
					tmp3 = VAL(tmp3) + 1
					tcs.getCellByPosition(4,i).Formula = tmp2(0) & ":E" & tmp3 & ")/2"
					hg = 0
					j = j + 1
				End If	
			ElseIf tcs.getCellByPosition(1,i).String = "" AND tcs.getCellByPosition(1,i+1).String = "" Then
				Exit Do
		End If
	Loop
	
End Sub