Come velocizzare le macro di LibreOffice Calc con grandi set di dati

Le macro di LibreOffice Calc spesso risultano lente su fogli di calcolo di grandi dimensioni per un semplice motivo: la macro impiega più tempo ad attraversare il confine dell'oggetto UNO che a eseguire il calcolo vero e proprio. Leggere una cella, modificarla e riscriverla migliaia di volte può essere molto più dispendioso in termini di tempo rispetto allo spostamento degli stessi dati in blocchi più grandi.

L'obiettivo non è rendere ogni macro "veloce" in astratto. Un'ottimizzazione utile dovrebbe produrre lo stesso output, terminare in meno tempo sullo stesso set di dati, mantenere Calc sufficientemente reattivo per il flusso di lavoro ed evitare di creare nuovi problemi di correttezza o di memoria. Le tecniche descritte di seguito si concentrano su questi risultati misurabili.

A partire da ottobre 2026, la documentazione dell'SDK di LibreOffice identifica le interfacce per fogli di calcolo utilizzate nel riferimento API di LibreOffice 26.8. I metodi principali sono interfacce UNO consolidate, non una nuova funzionalità per migliorare le prestazioni.

1. Misura la macro prima di modificarla

Inizia con una base di riferimento ripetibile. LibreOffice Basic offre GetSystemTicks(), che la Guida ufficiale descrive come un modo per misurare i tick di sistema e ottimizzare i processi. Inserisci nella macro la parte che desideri effettivamente migliorare, invece di misurare i tempi di apertura della cartella di lavoro, le richieste all'utente o le impostazioni non correlate.

Dim t0 As Long, elapsed As Long

t0 = GetSystemTicks()

' Run the work you want to measure here.

elapsed = GetSystemTicks() - t0
MsgBox "Elapsed ticks: " & elapsed

Esegui la stessa operazione più volte sulla stessa copia del file. La prima esecuzione può risultare diversa a causa delle cache del file system, dello stato delle formule o di altre operazioni eseguite una tantum. Registra le dimensioni del set di dati, l'intervallo elaborato e se il calcolo automatico è stato abilitato, in modo che i confronti rimangano significativi.

Un buon risultato non si limita a un tempo di elaborazione inferiore. Verificate che il numero di righe, i totali, le formule, i valori di testo, le date e gli intervalli di output corrispondano ancora ai valori di riferimento.

Riferimento ufficiale: Guida di LibreOffice: Funzione GetSystemTicks .

2. Rimuovere prima le operazioni di lettura e scrittura cella per cella

Se una macro scorre un intervallo ampio e chiama ripetutamente metodi come getCellByPosition(), questo schema è solitamente il primo punto da esaminare. L'API UNO espone XCellRangeData.getDataArray()e setDataArray()specificamente per spostare un intero intervallo di celle come un array.

L'API ufficiale afferma che getDataArray()restituisce una sequenza di righe, ciascuna contenente colonne. I valori vengono restituiti come stringhe o numeri decimali. setDataArray()Scrive un array in un intervallo e le dimensioni dell'array devono corrispondere a tale intervallo.

Ad esempio, supponiamo che le colonne dalla A alla E contengano i dati che la macro deve trasformare. Invece di leggere ogni cella singolarmente, seleziona l'intervallo esatto una sola volta:

Dim doc As Object
Dim sheet As Object
Dim sourceRange As Object
Dim data As Variant

doc = ThisComponent
sheet = doc.Sheets.getByName("Data")

' Example only: A2:E50001.
sourceRange = sheet.getCellRangeByPosition(0, 1, 4, 50000)
data = sourceRange.getDataArray()

Elabora i dati datanella memoria Basic, quindi scrivi un array di risultati in un intervallo di destinazione corrispondente in un'unica operazione. Questo riduce drasticamente il numero di chiamate a UNO rispetto a migliaia di singole operazioni di lettura e scrittura.

Riferimenti ufficiali: LibreOffice SDK: XCellRangeData e LibreOffice SDK: XCellRange .

3. Elabora solo l'intervallo di cui hai effettivamente bisogno

L'accesso in blocco agli array è utile, ma trasformare un foglio di calcolo inutilmente grande in un array può semplicemente spostare il collo di bottiglia nella memoria. Evita di inserire colonne intere o l'intera griglia di Calc quando l'operazione riguarda solo una tabella definita.

Determina l'ultima riga significativa utilizzando le informazioni già presenti nella cartella di lavoro: il numero di righe importate, la dimensione del risultato del database, una colonna chiave designata o un intervallo creato dall'applicazione. Quindi richiedi solo le righe e le colonne necessarie alla macro.

Ciò è particolarmente importante quando il foglio di calcolo contiene una formattazione molto al di sotto dei dati reali o quando il contenuto obsoleto ha ampliato l'area apparentemente utilizzata. Gli array in memoria di grandi dimensioni consumano RAM e un'allocazione sovradimensionata può risultare più onerosa rispetto all'elaborazione di blocchi di dimensioni moderate.

Quando passare alla suddivisione in blocchi

Se un singolo array provoca un brusco aumento dell'utilizzo della memoria, causa lunghe pause prima dell'inizio dell'elaborazione o si avvicina ai limiti pratici della macchina, elabora i dati a blocchi. Ad esempio, elabora diverse migliaia di righe, scrivi i risultati, quindi continua con il blocco successivo. Non esiste una dimensione ideale universale per i blocchi; esegui delle prove con la cartella di lavoro e l'hardware effettivi.

4. Sospendi le notifiche di aggiornamento dello schermo durante i periodi di lavoro intenso

Un documento Calc implementa l'interfaccia di LibreOffice XModel. Il suo lockControllers()metodo sospende alcune notifiche del controller utilizzate per gli aggiornamenti della visualizzazione, mentre il unlockControllers()metodo le riattiva. Questo può ridurre il lavoro visivo non necessario mentre una macro esegue molte modifiche.

Dim doc As Object
doc = ThisComponent

doc.lockControllers()

' Perform bulk changes here.

doc.unlockControllers()

L'API ufficiale specifica esplicitamente che i blocchi e gli sblocchi del controller devono essere accoppiati. Ciò significa che le macro di produzione dovrebbero includere una gestione degli errori che garantisca unlockControllers()il raggiungimento dell'obiettivo anche in caso di errore di elaborazione. Lasciare il documento bloccato non è un'ottimizzazione delle prestazioni, bensì un percorso di pulizia errato.

Riferimento ufficiale: LibreOffice SDK: XModel .

5. Controllare il ricalcolo automatico quando le formule dominano il tempo di esecuzione

Se la macro modifica ripetutamente le celle che alimentano molte formule, Calc potrebbe impiegare molto tempo per ricalcolare mentre la macro sta ancora aggiornando i valori intermedi. Il documento del foglio di calcolo espone l' XCalculatableinterfaccia, inclusi isAutomaticCalculationEnabled(), enableAutomaticCalculation(), calculate(), e calculateAll().

Un approccio più sicuro consiste nel memorizzare la modalità di calcolo corrente dell'utente, disabilitare il calcolo automatico solo durante l'aggiornamento e ripristinare lo stato originale in seguito:

Dim doc As Object
Dim wasAutoCalc As Boolean

doc = ThisComponent
wasAutoCalc = doc.isAutomaticCalculationEnabled()

doc.enableAutomaticCalculation(False)

' Perform bulk updates.

doc.enableAutomaticCalculation(wasAutoCalc)

If wasAutoCalc Then
    doc.calculate()
End If

calculate()Ricalcola le celle modificate. calculateAll()Ricalcola ogni cella, operazione che può essere dispendiosa in termini di tempo, quindi non richiamarla automaticamente solo perché una macro ha terminato. Utilizza il ricalcolo completo solo quando la logica della cartella di lavoro lo richiede effettivamente.

Come per il blocco del controller, ripristinare lo stato di calcolo in un percorso di pulizia degli errori. Una macro non dovrebbe lasciare silenziosamente la cartella di lavoro di un utente in modalità di calcolo manuale.

Riferimento ufficiale: LibreOffice SDK: XCalculatable .

6. Combinare le ottimizzazioni in modo sicuro

La struttura più efficace è solitamente la seguente: determinare un intervallo preciso, leggerlo una sola volta, elaborare i valori in memoria, scrivere i risultati in blocco e sopprimere le operazioni di interfaccia utente o di ricalcolo non necessarie solo per la durata dell'operazione.

Un esempio pratico di procedura di pulizia in LibreOffice Basic è il seguente:

Sub ProcessLargeData
    Dim doc As Object
    Dim sheet As Object
    Dim dataRange As Object
    Dim data As Variant
    Dim wasAutoCalc As Boolean

    On Error GoTo CleanUp

    doc = ThisComponent
    sheet = doc.Sheets.getByName("Data")
    dataRange = sheet.getCellRangeByPosition(0, 1, 4, 50000)

    wasAutoCalc = doc.isAutomaticCalculationEnabled()

    doc.lockControllers()
    doc.enableAutomaticCalculation(False)

    data = dataRange.getDataArray()

    ' Transform data in memory here.
    ' Build an output array with dimensions matching its target range.

CleanUp:
    doc.enableAutomaticCalculation(wasAutoCalc)

    If doc.hasControllersLocked() Then
        doc.unlockControllers()
    End If

    If wasAutoCalc Then
        doc.calculate()
    End If
End Sub

Nell'esempio, la logica di trasformazione non viene specificata intenzionalmente perché dipende dalla cartella di lavoro. L'ottimizzazione importante riguarda la struttura del flusso di lavoro, non un calcolo aziendale arbitrario.

7. Prima di implementarle, considera le funzioni integrate di Calc.

LibreOffice Basic può richiamare le funzioni di Calc tramite il com.sun.star.sheet.FunctionAccessservizio. Se la macro implementa manualmente una ricerca, un'aggregazione o un'altra operazione già fornita da Calc, l'utilizzo della funzione integrata può semplificare il codice e ridurre la necessità di cicli personalizzati.

Ciò non garantisce che ogni funzione integrata superi in prestazioni ogni algoritmo personalizzato. Esegui dei benchmark con un carico di lavoro reale. La Guida ufficiale mostra come richiamare le funzioni Calc da Basic e precisa che i nomi delle funzioni sono forniti in inglese.

Riferimento ufficiale: Guida di LibreOffice: Utilizzo delle funzioni Calc nelle macro .

Come valutare se l'ottimizzazione è sufficientemente buona

Errori comuni che annullano il beneficio

• Caricare in memoria un intero foglio di calcolo quando sono necessarie solo poche colonne.
• Utilizzando getDataArray()ma scrivendo comunque ogni risultato cella per cella.
• Disattivare il calcolo automatico e dimenticare di ripristinare l'impostazione precedente.
• Controller di blocco senza un percorso di pulizia affidabile.
• Effettuare una chiamata calculateAll()dopo ogni piccolo lotto, anche quando è necessario ricalcolare solo le celle sporche.
• Valutare la velocità da una singola prova anziché da misurazioni ripetute sugli stessi dati.
• Ottimizzare le prestazioni senza verificare che l'output rimanga invariato.

Quando queste tecniche smettono di essere d'aiuto

I trasferimenti di array riducono principalmente il sovraccarico di accesso agli oggetti del foglio di calcolo. Non risolvono però i problemi intrinseci di un algoritmo oneroso. Un confronto annidato di ogni riga con ogni altra riga, ad esempio, può rimanere lento anche quando tutti i valori sono in memoria. In tal caso, è necessario modificare l'algoritmo: utilizzare dizionari o indici, ordinare una sola volta anziché ripetutamente, ridurre il lavoro duplicato o spostare il carico di lavoro su uno strumento di elaborazione dati più adatto quando il foglio di calcolo non è più l'ambiente di esecuzione appropriato.

Lo stesso vale per le macro dominate da chiamate di rete, query di database, I/O di file o servizi esterni. Misura prima in modo da ottimizzare il collo di bottiglia effettivo piuttosto che il ciclo visibile del foglio di calcolo.

Ordine di ottimizzazione consigliato

1. Misura la macro corrente con un set di dati ripetibile.
2. Sostituisci le letture e scritture ripetute delle celle con array di intervalli.
3. Limita l'elaborazione alle sole righe e colonne necessarie.
4. Utilizzare la suddivisione in blocchi (chunking) se l'array di grandi dimensioni è troppo esteso per un utilizzo agevole della memoria.
5. Blocca i controller durante la produzione di grandi lotti, con pulizia garantita.
6. Sospendere temporaneamente il calcolo automatico quando il ricalcolo ripetuto delle formule si rivela un collo di bottiglia.
7. Ricalcola solo quanto necessario, ripristina lo stato dell'applicazione e verifica l'output.
8. Rimisurare. Se il tempo di esecuzione è ancora dominato dai calcoli piuttosto che dall'accesso a UNO, migliorare l'algoritmo.

Per i grandi set di dati di Calc, la riduzione del numero di chiamate tra oggetti Basic e oggetti foglio di calcolo è solitamente il miglioramento strutturale più importante. Ogni ulteriore ottimizzazione va considerata come qualcosa da misurare, non come un rituale. Una macro più veloce è utile solo se rimane corretta, prevedibile e manutenibile.