Pulisci il testo del foglio di calcolo con una macro ONLYOFFICE riutilizzabile

Per una pulizia ripetibile del foglio di calcolo, crea una piccola macro JavaScript nell'Editor di fogli di calcolo, seleziona solo l'intervallo di testo che intendi modificare e testala prima su una copia della cartella di lavoro. L'esempio seguente rimuove i caratteri non stampabili ed elimina gli spazi bianchi in eccesso da una colonna di indirizzi email, mantenendo tutte le righe al loro posto.

Le macro ONLYOFFICE vengono eseguite all'interno dell'editor e utilizzano l'API JavaScript di Office. La documentazione ufficiale descrive ancora il flusso di lavoro come segue: aprire Visualizza > Macro , scrivere o incollare il codice ed eseguirlo nell'editor; nessuna modifica recente identificata nella documentazione attuale altera questo processo di base. La macro stessa è uno script illustrativo e riutilizzabile, non un report di un test del software. Consultare la guida rapida alle macro ONLYOFFICE e il riferimento alla scheda Visualizza dell'Editor di fogli di calcolo .

Prepara una copia sicura e definisci la pulizia

Supponiamo che una cartella di lavoro contenga un elenco di contatti con un'intestazione nella riga 1 e indirizzi e-mail nella colonna B, da B2 a B500. Alcuni valori importati potrebbero contenere interruzioni di riga, tabulazioni o spazi iniziali e finali. Questo esempio pulisce solo quella specifica colonna di testo. Modifica l'intervallo in modo che corrisponda al tuo foglio di calcolo prima di eseguire il codice.

• Salva una copia separata della cartella di lavoro oppure duplica il foglio di lavoro. Conserva l'originale intatto finché non avrai verificato il risultato.
• Verificare che l'intervallo selezionato contenga testo semplice e non formule, ID che si basano su spazi o dati che devono preservare una spaziatura precisa.
• Mantieni la riga di intestazione fuori dall'intervallo. La macro non rileva le intestazioni né decide quali colonne possono essere modificate in sicurezza.

Utilizza un intervallo specifico per la colonna, ad esempio B2:B500, anziché l'intero intervallo utilizzato. La pulizia indiscriminata di tutte le celle può modificare nomi, indirizzi, codici o risultati di formule in modi non desiderati.

Apri l'editor di macro e aggiungi lo script

Apri la cartella di lavoro nell'editor di fogli di calcolo ONLYOFFICE. Seleziona Visualizza > Macro per aprire l'editor di macro, quindi crea una macro o sostituisci il codice iniziale nell'editor. Le macro di ONLYOFFICE utilizzano la sintassi JavaScript e le API di Office; non sono macro VBA. Il flusso di lavoro delle macro dell'editor e i metodi API supportati sono descritti nella guida introduttiva ufficiale alle macro .

Incolla questo esempio, quindi modificalo B2:B500con il tuo intervallo di solo testo effettivo:

(function () {
    var sheet = Api.GetActiveSheet();
    var range = sheet.GetRange("B2:B500");
    var rows = range.GetValue();
    var functions = Api.WorksheetFunction;

    for (var i = 0; i < rows.length; i++) {
        var value = rows[i][0];
        if (typeof value === "string") {
            value = functions.CLEAN(value);
            rows[i][0] = value.replace(/^\s+|\s+$/g, "")
                              .replace(/\s{2,}/g, " ");
        }
    }

    range.SetValue(rows);
})();

La macro legge l'intervallo in un array bidimensionale, elabora le celle di stringa e riscrive l'array nello stesso intervallo. CLEANRimuove i caratteri non stampabili; le due sostituzioni con espressioni regolari rimuovono gli spazi bianchi ai bordi e riducono gli spazi bianchi ripetuti all'interno di una stringa a un singolo spazio. La documentazione API conferma GetValueche può restituire un array bidimensionale per un intervallo e che SetValueaccetta array; i riferimenti GetValue , SetValue e CLEAN documentano queste chiamate.

Poiché lo script riscrive i valori, non indirizzarlo a celle contenenti formule: le celle con formule potrebbero essere sostituite dai valori calcolati correnti. Inoltre, lo script non rimuove i record duplicati, non standardizza la capitalizzazione, non verifica se un indirizzo è valido per la consegna né modifica i formati dei numeri di telefono. Queste sono regole di pulizia separate e devono essere progettate e testate indipendentemente.

Eseguilo su un poligono di tiro controllato

1. Verifica ancora una volta l'intervallo letterale. Se l'elenco termina alla riga 240, ad esempio, usa B2:B240 invece di elaborare centinaia di righe non utilizzate.
2. Fai clic su Esegui nell'editor di macro. Se l'editor segnala un errore di sintassi, rivedi virgolette, parentesi e parentesi graffe prima di modificare l'intervallo o aggiungere ulteriore logica.
3. Torna al foglio di lavoro ed esamina diverse celle, tra cui un valore che necessitava di essere corretto, un valore già nel formato corretto e una cella vuota.

Iniziare con un intervallo ristretto rende più facile comprendere l'effetto. Una volta ottenuto il risultato corretto, è possibile riutilizzare la macro su un altro foglio modificando l'intervallo intenzionalmente. Non dare per scontato che una lettera di colonna o un limite di riga rimangano validi in una cartella di lavoro diversa.

Se il comando Macro non è presente o il codice non può essere eseguito, verificare se le macro sono abilitate nell'editor o disabilitate dalla configurazione dell'applicazione host. Nell'editor online, un amministratore può controllare la disponibilità delle macro; le impostazioni avanzate dell'editor di fogli di calcolo del prodotto descrivono le opzioni relative alle macro visibili all'utente. Evitare di abilitare l'esecuzione automatica per i file provenienti da fonti sconosciute; eseguire solo codice che si comprende.

Verificare il risultato prima di riutilizzare la macro

Confronta la colonna ripulita con la copia originale anziché giudicare il successo solo dall'aspetto. Verifica che ogni indirizzo email non vuoto sia ancora sulla stessa riga, che nessun contatto sia scomparso e che le uniche differenze siano i caratteri e la spaziatura che intendevi normalizzare. Controlla un campione vicino all'inizio, al centro e alla fine dell'intervallo selezionato. Cerca uno spazio all'inizio o alla fine di un valore, se il tuo editor lo rende visibile, e ispeziona i valori importati che in precedenza contenevano interruzioni di riga o caratteri di controllo.

Confronta anche il numero di righe e le eventuali formule, filtri o ricerche dipendenti che utilizzano la colonna. Se il risultato di una formula è cambiato inaspettatamente, annulla la modifica o ripristina la copia di lavoro; la macro dovrebbe essere limitata alle celle di testo semplice. Per una cartella di lavoro più grande o critica per l'azienda, confronta prima la colonna completamente ripulita con il backup oppure utilizza prima una colonna di output separata in modo che le modifiche rimangano verificabili.

Mantenere la pulizia ripetibile

Dopo la prima esecuzione, se tutto va bene, salva la cartella di lavoro in modo che la macro rimanga disponibile con il documento, secondo il flusso di lavoro delle macro dell'editor. Non si deve presumere che una macro memorizzata in una cartella di lavoro venga visualizzata automaticamente in ogni nuovo file. Per le importazioni ricorrenti, mantieni un modello pulito o una copia sorgente protetta, documenta l'intervallo previsto dalla macro e aggiornalo quando cambia il layout del foglio. Le macro ONLYOFFICE sono utili per regole di pulizia deterministiche; non possono stabilire se un valore è effettivamente corretto o se una modifica è appropriata per il tuo set di dati.

Il metodo affidabile è semplice: conservare una copia di origine, selezionare un intervallo noto, eseguire una trasformazione definita e confrontare il risultato prima di condividere o importare i dati altrove.