Tekst in spreadsheets opschonen met een herbruikbare ONLYOFFICE-macro

Voor het herhaaldelijk opschonen van spreadsheets kunt u een kleine JavaScript-macro maken in de spreadsheeteditor. Selecteer alleen het tekstbereik dat u wilt wijzigen en test de macro eerst op een kopie van het werkblad. Het onderstaande voorbeeld verwijdert niet-afdrukbare tekens en overtollige witruimte uit een kolom met e-mailadressen, terwijl alle rijen behouden blijven.

ONLYOFFICE-macro's worden uitgevoerd binnen de editor en maken gebruik van de Office JavaScript API. De officiële documentatie beschrijft de workflow nog steeds als het openen van Weergave > Macro's , het schrijven of plakken van code en het uitvoeren ervan in de editor; geen enkele recente wijziging in de huidige documentatie verandert dit basisproces. De macro zelf is een illustratief, herbruikbaar script, geen rapport van een softwaretest. Zie de snelstartgids voor ONLYOFFICE-macro's en de referentie voor het tabblad Weergave in de spreadsheeteditor .

Maak een veilige kopie en beschrijf de opruimprocedure.

Stel dat een werkmap een contactenlijst bevat met een koptekst in rij 1 en e-mailadressen in kolom B, van B2 tot en met B500. Enkele geïmporteerde waarden kunnen regeleinden, tabs of spaties aan het begin en einde bevatten. Dit voorbeeld verwijdert alleen die ene tekstkolom. Pas het bereik aan uw werkblad aan voordat u de code uitvoert.

• Sla een aparte kopie van het werkblad op of dupliceer het werkblad. Bewaar het origineel totdat u de uitvoer hebt gecontroleerd.
• Controleer of het geselecteerde bereik onbewerkte tekst bevat en geen formules, ID's die afhankelijk zijn van spaties of gegevens waarbij de exacte spaties behouden moeten blijven.
• Houd de kopregel buiten het bereik. De macro detecteert geen kopregels en bepaalt niet welke kolommen veilig gewijzigd kunnen worden.

Gebruik een kolomspecifiek bereik, bijvoorbeeld B2:B500, in plaats van het volledige bereik. Het lukraak opschonen van elke cel kan namen, adressen, codes of formule-uitkomsten op onbedoelde manieren wijzigen.

Open de macro-editor en voeg het script toe.

Open de werkmap in de ONLYOFFICE Spreadsheet Editor. Kies Weergave > Macro's om de macro-editor te openen en maak vervolgens een macro aan of vervang de startcode in de editor. ONLYOFFICE-macro's gebruiken JavaScript-syntaxis en de Office API; het zijn geen VBA-macro's. De macroworkflow van de editor en de ondersteunde API-methoden worden beschreven in de officiële handleiding voor het gebruik van macro's .

Plak dit voorbeeld en pas het vervolgens aan B2:B500naar uw eigen tekstbereik:

(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);
})();

De macro leest het bereik in een tweedimensionale array, verwerkt tekenreekscellen en schrijft de array terug naar datzelfde bereik. CLEANNiet-afdrukbare tekens worden verwijderd; de twee reguliere expressies verwijderen witruimte aan de randen en reduceren herhaalde witruimte binnen een tekenreeks tot één spatie. De API-referentie bevestigt GetValuedat een tweedimensionale array voor een bereik kan worden geretourneerd en dat SetValuearrays worden geaccepteerd; de referenties GetValue , SetValue en CLEAN documenteren deze aanroepen.

Omdat het script waarden terugschrijft, mag u het niet gebruiken voor cellen met formules: cellen met formules kunnen worden vervangen door hun huidige berekende waarden. Het script verwijdert ook geen dubbele records, standaardiseert geen hoofdletters, controleert niet of een adres bezorgbaar is en wijzigt geen telefoonnummerformaten. Dit zijn aparte opschoonregels die onafhankelijk moeten worden ontworpen en getest.

Test het op een gecontroleerde temperatuur.

1. Controleer de bereikwaarde nogmaals. Als de lijst bijvoorbeeld eindigt bij rij 240, gebruik dan B2:B240 in plaats van honderden ongebruikte rijen te verwerken.
2. Klik op 'Uitvoeren' in de macro-editor. Als de editor een syntaxfout meldt, controleer dan de aanhalingstekens, haakjes en accolades voordat u het bereik wijzigt of meer logica toevoegt.
3. Ga terug naar het werkblad en controleer verschillende cellen, waaronder een waarde die moet worden opgeschoond, een waarde die al in de juiste opmaak staat en een lege cel.

Beginnen met een beperkt bereik maakt het effect gemakkelijker te begrijpen. Zodra de uitvoer correct is, kunt u de macro hergebruiken op een ander werkblad door het bereik bewust aan te passen. Ga er niet vanuit dat een kolomletter of rijgrens geldig blijft in een andere werkmap.

Als de opdracht Macros ontbreekt of de code niet kan worden uitgevoerd, controleer dan of macro's zijn ingeschakeld in de editor of zijn uitgeschakeld door de configuratie van de hosttoepassing. In de online editor kan een beheerder de beschikbaarheid van macro's beheren; de geavanceerde instellingen van de spreadsheeteditor van het product beschrijven de macro-opties die voor de gebruiker zichtbaar zijn. Schakel geen automatische uitvoering in voor bestanden van onbekende bronnen; voer alleen code uit die u begrijpt.

Controleer het resultaat voordat u de macro opnieuw gebruikt.

Vergelijk de opgeschoonde kolom met de originele kopie in plaats van het succes alleen op basis van het uiterlijk te beoordelen. Controleer of elk niet-leeg e-mailadres nog steeds op dezelfde regel staat, of er geen contactpersonen zijn verdwenen en of de enige verschillen de tekens en spaties zijn die u wilde normaliseren. Controleer een voorbeeld bovenaan, in het midden en aan het einde van het gekozen bereik. Zoek naar een spatie aan het begin of einde van een waarde als uw editor dit zichtbaar maakt, en inspecteer geïmporteerde waarden die voorheen regeleinden of besturingstekens bevatten.

Vergelijk ook het aantal rijen en alle afhankelijke formules, filters of opzoekfuncties die de kolom gebruiken. Als het resultaat van een formule onverwacht is gewijzigd, maak de bewerking dan ongedaan of herstel de werkversie; de ​​macro moet beperkt blijven tot onbewerkte tekstcellen. Voor een grotere of bedrijfskritische werkmap kunt u de volledige opgeschoonde kolom vergelijken met de back-up of eerst een aparte uitvoerkolom gebruiken, zodat de wijzigingen controleerbaar blijven.

Zorg ervoor dat de schoonmaak herhaalbaar is.

Nadat de eerste uitvoering correct is verlopen, slaat u de werkmap op zodat de macro beschikbaar blijft bij het document volgens de macroworkflow van de editor. Een macro die in één werkmap is opgeslagen, verschijnt niet automatisch in elk nieuw bestand. Voor terugkerende importen kunt u een schone sjabloon of een beveiligde bronkopie bewaren, het bereik documenteren dat de macro verwacht en dit bijwerken wanneer de bladindeling verandert. ONLYOFFICE-macro's zijn nuttig voor deterministische opschoonregels; ze kunnen niet bepalen of een waarde feitelijk correct is of dat een wijziging geschikt is voor uw gegevensset.

Het betrouwbare patroon is eenvoudig: bewaar een kopie van het bronbestand, richt je op een bekend bereik, voer één gedefinieerde transformatie uit en vergelijk het resultaat voordat je de gegevens elders deelt of importeert.