LO-Calc-Arbeitsblätter mit wenigen Zeilen lassen sich manuell flott und fehlerfrei bearbeiten. Bei sehr umfangreichen Tabellen empfiehlt es sich, sie mithilfe von Makros anzupassen.

Eine Tabellenkalkulation wie LibreOffice Calc [1] mag zwar nicht die erste Wahl zum Analysieren großer Datenbestände sein, aber es ist immer noch sinnvoller, Makros einzusetzen, als Arbeitsblätter mit mehreren Hundert Zeilen manuell zu bearbeiten. Gerade bei großen Arbeitsblättern lassen sich so Fehler vermeiden.

Makros (siehe Kasten “Bibliotheken”) eignen sich unter anderem dazu, Bestelllisten zu iterieren und den Gesamtpreis zu berechnen. Des Weiteren können Makros Daten in ein anderes Dateiformat exportieren oder massenweise Datensätze bearbeiten. Um den Fortschritt eines Makros zu visualisieren, erstellen Sie ein Dialogfenster mit Fortschrittsbalken.

PDF-Export

Um beispielsweise einen Beleg einer Bestellliste (Abbildung 1) im PDF-Format anzufertigen, deklarieren Sie ein Makro als Subroutine und speichern es in einem zuvor erstellten Modul ab. Unter Extras | Makros | Makros verwalten | Basic klicken Sie auf Verwalten und gelangen zum Fenster Basic-Makro-Organizer. Dort markieren Sie unter Module die Bibliothek, unter der das Modul abgelegt werden soll. Unter Neu können Sie ein eigenes Modul erstellen und passend benennen. Um Makros zu entwickeln, öffnen Sie unter Extras | Makros | Makros bearbeiten die Basic-IDE von LibreOffice.

Abbildung 1: Makros können Bestelllisten ins PDF-Format exportieren.

Bei mehreren Bestelllisten sollten Sie zudem ein Präfix in Form eines Strings definieren. Um für jede Bestellliste automatisch einen Dateinamen zu generieren, lässt sich beim Iterieren durch die einzelnen Arbeitsblätter ein passendes Suffix an das Präfix hängen (Listing 1). Die für Dateiexporte relevanten Eigenschaften definieren Sie in der Basic-IDE als Structure (Listing 2). Entscheidende Eigenschaften sind hier das neue Dateiformat und die zu exportierende Auswahl, die das gesamte Arbeitsblatt umfassen kann.

Dim sFilename as String
Dim sURL as String
sFilename = prefixFilename & separator & snewDate & separator & sheets.ElementNames(i)
sURL = FileDirectory & "/" & sFilename & ".pdf"

Dim fileProps(1) as new com.sun.star.beans.PropertyValue
fileProps(0).Name = "FilterName"
fileProps(0).Value = "calc_pdf_Export"

Möchten Sie mehrere Arbeitsblätter exportieren, speichern Sie vor dem Iterieren alle Arbeitsblätter zunächst in einer Variablen. Sobald Sie den Dateinamen definiert haben, veranlassen Sie den Export, indem Sie als Argumente den Dateipfad sowie die Structure übergeben. Ein Beispiel, das sämtliche Bestelllisten eines Calc-Dokuments ins PDF-Format exportiert, zeigt Listing 3. Den kompletten Code finden Sie im Download-Bereich zu diesem Artikel. Das Makro führen Sie manuell aus, indem Sie im Objektkatalog der Basic-IDE die Subroutine markieren und auf Play klicken oder [F5] drücken (Abbildung 2).

Abbildung 2: Wenn Sie im Objektkatalog eine Subroutine auswählen, können Sie diese über Play ausführen.

[...]
GlobalScope.BasicLibraries.loadLibrary("Tools")
FileDirectory = Tools.Strings.DirectoryNameoutofPath(doc.getURL(), "/")
Dim fileProps(1) as new com.sun.star.beans.PropertyValue
fileProps(0).Name = "FilterName"
fileProps(0).Value = "calc_pdf_Export"
Dim aFilterData(0) as new com.sun.star.beans.PropertyValue
i = 3
Do While sheets.Count > i
   sheet = sheets.getByIndex(i)
   sCompany = sheet.Name
   sDate = FindDate(sCompany)
   snewDate=ModifyDate(sDate)
   cntrllr = doc.getCurrentController()
   cntrllr.ActiveSheet = sheet
   sFilename = prefixFilename & separator & snewDate & separator &  sheets.ElementNames(i)
   sURL = FileDirectory & "/" & sFilename & ".pdf"
   aFilterData(0).Name = "Selection"
   aFilterData(0).Value = sheet
   fileProps(1).Name = "FilterData"
   fileProps(1).Value = aFilterData
   ThisComponent.storeToURL(sURL, fileProps)
   i = i + 1
Loop
End Sub

Preiskalkulationen

Bei ellenlangen Angeboten mit unterschiedlicher Größe erweist es sich als umständlich, den Gesamtpreis manuell mit Formeln zu berechnen. Um sich dabei von Makros unterstützen zu lassen, erstellen Sie im ersten Schritt eine Vorlage oder ein konkretes Angebot, das Sie in einem Arbeitsblatt abspeichern. Dabei spielt es keine Rolle, ob sich das Angebot im Nachhinein vergrößert oder verkleinert, weil das Makro das berücksichtigt. Zusätzlich lassen sich Marker für das Makro setzen, die ihm anzeigen, dass bei einer bestimmten Zeile der Abbruch der Schleife erfolgt. In unserem Beispiel weiß das Makro, dass in die Zeile mit dem Wort TOTAL der Gesamtpreis gehört.

Schreiben Sie das Makro in eine Subroutine, indem Sie das Makro innerhalb der Schlagwörter Sub Prozedur ... End Sub eintragen. Zu Beginn des Makros definieren Sie Variablen, die später die Preise sowie Mengen aufnehmen. Anschließend initialisieren Sie die Variablen mit Standardwerten, beispielsweise priceA = 0 und amount = 0.

Darüber hinaus benötigen Sie Konstanten für die Indizes der Spalten, auf die das Makro während des Iterierens zugreift. So entspricht die Spalte A dem Index 0, die Spalte B dem Index 1 und so weiter. Damit das Makro nicht durch alle Zeilen iteriert, legen Sie einen Maximalwert fest, etwa 500. Dementsprechend umfasst die Angebotsliste höchstens 500 Zeilen.

Das Makro aus Listing 4 iteriert zuerst durch die Spalte Menge, der der Index g zugewiesen wird, und speichert die Zelle bei Zeile i in der Variablen oCell ab (Zeile 24). Da sich in unserem Beispiel lediglich in den Zeilen Preise befinden, in denen auch eine Mengenangabe steht, sollte das Makro anfangs prüfen, ob die Zelle leer ist (Zeile 25). Falls nicht, wird der Preis in einer Variablen abgelegt und der Endpreis aus Menge mal Preis berechnet. Beim Iterieren trägt das Makro den Endpreis in die entsprechende Zelle des Calc-Dokuments ein (Listing 5). Außerdem addiert es die Endpreise in der Variablen subtotal auf, um den Gesamtpreis zu ermitteln (Zeile 32). Den kompletten Code des Makros finden Sie im Download-Bereich zu diesem Artikel.

Sub CalcPrice()
  [...]
  oDoc = ThisComponent
  oSheet = oDoc.CurrentController.ActiveSheet
  priceA = 0
  priceB = 0
  temp = 0
  i = 0
  jPricesA = 1
  jPricesB = 2
  g = 3
  hsubTotalA = 4
  hsubTotalB = 5
  subtotalA = 0
  subtotalB  = 0
  sTotal = "total"
  sEconomic = "günstigstes Computer-System"
  total = 0
  economic = "X"
  maxItems = 500
  searchTotal = -1
  searchEconomic = -1
  For i = 1 to maxItems Step 1
    oCell = oSheet.getCellByPosition(g,i)
    If oCell.String <> "" Then
      priceA = oSheet.getCellByPosition(jPricesA,i).Value
      priceB = oSheet.getCellByPosition(jPricesB,i).Value
      if priceA = 0 then
        temp = priceB * oCell.Value
        oCell = oSheet.getCellByPosition(hsubTotalB,i)
        oCell.Value = temp
        subtotalB = subtotalB + temp
      elseif priceB = 0 then
        temp = priceA * oCell.Value
        oCell = oSheet.getCellByPosition(hsubTotalA,i)
        oCell.Value = temp
        subtotalA = subtotalA + temp
      end if
    End If
    oCell = oSheet.getCellByPosition(0,i)
    descr = oCell.String
    searchTotal = StrComp(sTotal, descr, 0)
    searchEconomic = StrComp(sEconomic, descr, 0)
    if searchTotal = 0 Then
      oCell = oSheet.getCellByPosition(hsubTotalA,i)
      oCell.Value = subtotalA
      oCell = oSheet.getCellByPosition(hsubTotalB,i)
      oCell.Value = subtotalB
    elseif searchEconomic = 0 Then
      if subtotalA > subtotalB Then
        oCell = oSheet.getCellByPosition(hsubTotalB,i)
        oCell.String = economic
      else
        oCell = oSheet.getCellByPosition(hsubTotalA,i)
        oCell.String = economic
      end if
    End if
    searchTotal = -1
    searchEconomic = -1
  Next
  oDoc.store
End Sub

if priceA = 0 then
  temp = priceB * oCell.Value
  oCell = oSheet.getCellByPosition(hsubTotalB,i)
  oCell.Value = temp
  [...]

Um die letzte Zeile des Angebots zu ermitteln, sucht das Makro bei jeder Iteration nach dem Wort TOTAL. Dazu kommt die Funktion StrComp zum Einsatz, die die Zeichenkette aus der aktuellen Zelle mit dem Suchwort TOTAL vergleicht. Bei einem Treffer werden die Gesamtpreise für jedes Angebot eingetragen. Sie können die Schleife vorzeitig beenden, indem Sie beim Erfüllen einer Bedingung die entsprechende Anweisung verwenden. Für For-Schleifen lautet sie Exit For, für Do-While-Schleifen Exit Do. Mittels des Befehls oDoc.store speichern Sie die Änderungen im Calc-Dokument.

Um das Dokument benutzerfreundlicher zu gestalten, bietet es sich an, einen Schalter einzufügen, der die Kalkulation auslöst (Abbildung 3). Unter Einfügen | Formular-Steuerelement | Schaltfläche lässt er sich an eine beliebige Stelle innerhalb des Arbeitsblatts setzen. Per Rechtsklick auf den Schalter rufen Sie das Kontextmenü auf und wählen dort Steuerelement-Eigenschaften. Danach wechseln Sie zum Tab Ereignisse und geben im Feld Aktion ausführen das Makro an, indem Sie auf … und ein weiteres Mal auf Makro klicken. Suchen Sie das entsprechende Makro und bestätigen Sie die Änderungen mit OK. Nach einem Neustart des Dokuments lässt sich der Schalter benutzen.

Abbildung 3: Gesamtpreise lassen sich per Klick auf einen Schalter automatisch berechnen.

Big Data

Calc ist zwar nicht auf Big Data beziehungsweise Datenanalysen ausgelegt, lässt sich zur Not aber auch dazu nutzen. So kann man per Makro tatsächlich aus Hunderten von Datenpunkten mehrere Hunderttausend Datenreihen generieren.

Angenommen, ein Datensatz soll so oft in ein neues Arbeitsblatt kopiert werden, wie es am Anfang der Zeile angegeben ist. Ebenso soll das Makro mit den restlichen Datensätzen verfahren (Abbildung 4). Bei einem größeren Makro erstellen Sie wie bereits beschrieben ein eigenes Modul, damit sich der Code leichter warten lässt. Für dieses Beispiel beginnen Sie mit den Hilfsmethoden.

Abbildung 4: Dieses Makro kopiert einzelne Zeilen mehrmals in ein neues Arbeitsblatt.

Um ein neues Arbeitsblatt einzufügen, gehen Sie wie in Listing 6 vor. Das Kommando aus Zeile 5 setzt das neue Arbeitsblatt ganz an den Anfang. Das Laden eines vorhandenen Arbeitsblatts funktioniert ebenso einfach (Listing 7). Es empfiehlt sich allerdings, dafür eine eigene Funktion zu erstellen, um den Code leserlich zu halten.

Function insertSheet(oDoc as Object,sheetname as String)
  Dim exists as Boolean
  exists = SheetExists(oDoc ,sheetname,False)
  If NOT exists Then
    oDoc.Sheets.insertNewByName(sheetname,0)
  End If
End Function

Function GetSheet(oDoc as Object,sheetName as String) AS Object
  Dim oSheet as Object
  oSheet = oDoc.Sheets.getByName(sheetName)
  GetSheet = oSheet
End Function

Zu Beginn des Moduls ergänzen Sie einige globale Variablen (Listing 8). Dadurch müssen Sie diese Variablen anderen Funktionen nicht als Argumente übergeben, da sich deren Wert innerhalb des gesamten Moduls abrufen lässt. Der global definierte Zähler currentRow speichert die aktuelle Zeile des neuen Arbeitsblatts ab. Ein weiterer global definierter Zähler currentColumn zeigt an, aus welcher Spalte einer gegebenen Zeile das Makro die Daten einer Zelle ins neue Arbeitsblatt kopieren soll.

Global currentColumn as Long
Global currentRow as Long
Global oProgressBarModel as Object

Danach legen Sie den Adressbereich fest, dessen Zellen im Array oRows() gespeichert werden – in unserem Beispiel A2:F100 (Listing 9). Die Variable oAddr ermöglicht, die Daten einer Zelle in einem Objekt abzuspeichern. So landet beispielsweise die erste Zelle der Reihe i im Objekt oCell.

oCellRange = oSheetFrom.getCellRangeByName("A2:F100")
oRows() = oCellRange.getData()
oAddr = SetRowAddress(oCellRange)
oCell =  oSheetFrom.getCellByPosition(oAddr.StartColumn, i+oAddr.StartRow)

In unserem Beispiel gibt die erste Zelle einer Reihe an, wie oft diese Reihe ins neue Arbeitsblatt kopiert wird. Diesen Wert rufen Sie mittels oCell.String oder oCell.Value ab. Anschließend bestimmt die Methode SetRangeofRow die Reichweite der Zeile i im Ursprungsarbeitsblatt, sodass die Methode SetRowAddress daraus eine Adresse erzeugt. Über die Methode CreateRows stoßen Sie das Kopieren an, wobei Sie ihr die Häufigkeit einer Reihe und den dazugehörigen Adressbereich übergeben. Anschließend gilt es noch, die Spaltenbreite im neuen Arbeitsblatt anzupassen und das geänderte Dokument zu speichern. Den kompletten Code des Makros CopySheet() finden Sie im Download-Bereich zu diesem Artikel.

Damit das Makro läuft, braucht es allerdings weitere Hilfsmethoden. Listing 10 enthält diverse für einen Fortschrittsbalken relevante Funktionen. Die Funktion ProgressBar initialisiert den Fortschrittsbalken, indem sie den Startwert (in der Regel 0) und den maximalen Wert (in unserem Beispiel die Anzahl der Reihen) definiert. Die Funktion IncreaseProgressBar aktualisiert den Fortschrittsbalken. Neben der grafischen Visualisierung informiert auch noch ein Label den Benutzer über die aktuelle Reihe, sodass er auf einen Blick erkennt, wie viele Reihen noch ausstehen (Zeile 12).

Function ProgressBar(max as Long)
  Dim ProgressValue As Long
  Const ProgressValueMin = 0
  oProgressBarModel = oDialog1.getModel().getByName( "progressBar" )
  oProgressBarModel.setPropertyValue( "ProgressValueMin", ProgressValueMin)
  oProgressBarModel.setPropertyValue( "ProgressValueMax", max)
  oDialog1.setVisible( True )
End Function
Function IncreaseProgressBar(pvalue as Long)
  oProgressBarModel.setPropertyValue( "ProgressValue", pvalue )
End Function
Function SetState(iVal as Integer, iRow,  numRows as Long, xRow as Long)
  Dim s as String
  Dim oLblState as Object
  oLblState = oDialog1.GetControl("lblState")
  Select Case iVal
    Case 1
      s = "copying row " & iRow & " / " & numRows & " >>> " & xRow & " times"
      oLblState.setText(s)
    Case 2
      oLblState.setText("adjusting column width for the new sheet")
    Case 3
      oLblState.setText("done")
    Case Else
      oLblState.setText("nothing to do")
  End Select
End Function

Die beiden Funktionen aus Listing 11 hängen zusammen: Zunächst bestimmt SetRangeofRow die erste und letzte Spalte einer zu kopierenden Zeile (Zeile 1). Danach übergeben Sie das resultierende Objekt der Funktion SetRowAddress (Zeile 14), um die Adresse der gewünschten Reihe zu erhalten.

Function SetRangeofRow(oSheet as Object, iRow as Long, oAddr as Object) AS OBJECT
  Dim start_column as Integer
  Dim start_row as Integer
  Dim end_column as Integer
  Dim end_row as Integer
  Dim rangeRow as Object
  start_column = oAddr.StartColumn
  start_row = iRow+oAddr.StartRow
  end_column = oAddr.EndColumn
  end_row = iRow+oAddr.StartRow
  rangeRow = oSheet.getCellRangeByPosition(start_column, start_row, end_column, end_row)
  SetRangeofRow = rangeRow
End Function
Function SetRowAddress(oRange as Object) AS OBJECT
  Dim oAddr as Object
  oAddr = oRange.RangeAddress
  SetRowAddress = oAddr
End Function

Listing 12 zeigt den eigentlichen Kopiervorgang. So definiert die Funktion SetCurrentRow die nächste Zeile des neuen Arbeitsblatts, um die Datensätze ab dieser Zeile hineinzukopieren. Danach übergeben Sie der Funktion CreateRows als Argumente die Adresse der zu kopierenden Reihe und deren Häufigkeit. In einer For-Schleife rufen Sie die Funktion copyRowtoSheet auf, die eine Zeile ins neue Arbeitsblatt kopiert. Über die Funktion SetColumnWidth (Listing 13) legen Sie die optimale Breite für die Spalten des neuen Arbeitsblatts fest.

Function SetCurrentRow(numRows as Long, nStep as Integer)
  currentRow = currentRow + nStep
End Function
Function copyRowtoSheet(oDoc as Object, oRangeAddress as Object, sheetName as String )
  Dim oCellAddress
  Dim oSheet
  oSheet = GetSheet(oDoc,sheetName)
  oCellAddress = oSheet.getCellByPosition(currentColumn, currentRow).getCellAddress()
  oSheet.copyRange(oCellAddress, oRangeAddress)
End Function
Function CreateRows(numRows as Long, oDoc as Object,oRangeAddress as Object )
  Dim n as Integer
  n = 1
  For i = 1 To numRows Step n
    copyRowtoSheet(oDoc, oRangeAddress)
    SetCurrentRow(numRows,n)
  Next i
End Function

Function SetColumnWidth(numColumns as Integer, sheetName as String, oDoc as Object)
  Dim oSheet as Object
  Dim n as Integer
  n = 1
  oSheet = GetSheet(oDoc,sheetName)
  For i = currentColumn To numColumns Step n
    oSheet.Columns(i).OptimalWidth = true
  Next i
End Function

Dialogfenster

Für den Fortschrittsbalken erstellen Sie ein Dialogfenster, das ein Beschriftungsfeld und eine Schaltfläche beherbergt. Zuvor müssen Sie jedoch ähnlich wie bei den Modulen einen Dialog einfügen, mit dem Unterschied, dass Sie diesmal unter Dialoge auf Neu klicken und einen Namen vergeben. Den sollten Sie im Hinterkopf behalten, da Sie darüber das Dialogfenster im Makro ansprechen.

Danach wechseln Sie wie gewohnt zur Basic-IDE und wählen den angelegten Dialog aus. Darin passen Sie mit gedrückter linker Maustaste zunächst die Fenstergröße an. Danach zeichnen Sie die Kontrollfelder ein. Dazu markieren Sie den Fortschrittsbalken und ziehen bei gedrückter linker Maustaste einen Rahmen innerhalb des Dialogfensters. Im Eigenschaftsfenster unter Allgemein | Name benennen Sie den Fortschrittsbalken. Dessen Namen brauchen Sie später, damit das Makro darauf zugreifen kann (Abbildung 5). Analog gehen Sie mit dem Label vor und vergeben dafür ebenfalls einen Namen.

Abbildung 5: Den Fortschrittsbalken sprechen Sie im Makro über einen eigens definierten Namen an.

Im Arbeitsblatt, das die zu bearbeitenden Datenreihen enthält, zeichnen Sie wie oben beschrieben einen Button und geben unter Ereignisse des dazugehörigen Kontextmenüs das Makro StartDialog() aus Listing 14 an. Um das Dialogfenster nach Beenden des Makros zu schließen, lässt sich ein weiterer Schalter ins Dialogfenster integrieren. Dazu nutzen Sie erneut Basic-IDE: Geben Sie unter Ereignisse im Feld Aktion ausführen das Makro ExitProgram() an (Listing 14). Daneben benötigen Sie zum Auslösen des Kopiervorgangs einen weiteren Schalter, den Sie mit dem Makro CopySheet() verknüpfen (Abbildung 6).

Abbildung 6: In der Basic-IDE können Sie für einen Schalter ein Makro hinterlegen.

Sub StartDialog()
  Dim oTxtNewSheet as Object
  BasicLibraries.LoadLibrary("Tools")
  oDialog1 = LoadDialog("MyLibrary", "Dialog_Sheet_Properties")
  oDialog1.Execute()
End Sub
Sub ExitProgram()
  oDialog1.endExecute()
End Sub

Fazit

Makros für LibreOffice Calc gibt es zwar zuhauf, aber einige Makro-Funktionen kommen im Alltag immer wieder vor. Dazu gehören die Automatisierung von Kalkulationen, das Kopieren einzelner Zeilen in ein anderes Arbeitsblatt und Exporte in ein anderes Dateiformat. Bei großen Arbeitsblättern lässt sich der Fortschritt nicht mehr so leicht überblicken. Hier hilft es weiter, mittels eines Dialogfensters den Fortschritt einer Operation in Form eines Balkens anzuzeigen. Alle hier behandelten Makros sind zu LibreOffice Calc 7.4.2.3 kompatibel. (csi)