Czysty tekst arkusza kalkulacyjnego z możliwością wielokrotnego użytku makro ONLYOFFICE

Aby wielokrotnie czyścić arkusz kalkulacyjny, utwórz małe makro JavaScript w Edytorze arkuszy kalkulacyjnych, dostosuj je tylko do zakresu tekstu, który chcesz zmienić, i przetestuj je najpierw na duplikacie skoroszytu. Poniższy przykład usuwa znaki niedrukowalne i przycina zbędne odstępy z kolumny wiadomości e-mail, zachowując jednocześnie wszystkie wiersze na swoim miejscu.

Makra ONLYOFFICE działają w edytorze i korzystają z interfejsu API JavaScript pakietu Office. Oficjalna dokumentacja nadal opisuje przepływ pracy jako otwieranie widoku > Makra , pisanie lub wklejanie kodu i uruchamianie go w edytorze; żadna z ostatnich zmian zidentyfikowanych w aktualnej dokumentacji nie zmienia tego podstawowego procesu. Samo makro jest przykładowym, wielokrotnego użytku skryptem, a nie raportem z testu oprogramowania. Zobacz szybki start makr ONLYOFFICE i kartę Widok w Edytorze Arkuszy .

Przygotuj kopię bezpieczeństwa i zdefiniuj czyszczenie

Załóżmy, że skoroszyt zawiera listę kontaktów z nagłówkiem w wierszu 1 i adresami e-mail w kolumnie B, od B2 do B500. Kilka zaimportowanych wartości może zawierać podziały wiersza, tabulatory lub spacje na początku i na końcu. Ten przykład czyści tylko tę jedną kolumnę tekstu. Przed uruchomieniem kodu dostosuj zakres do arkusza.

• Zapisz osobną kopię skoroszytu lub zduplikuj arkusz. Zachowaj oryginał w stanie nienaruszonym do momentu sprawdzenia wyników.
• Sprawdź, czy wybrany zakres zawiera surowy tekst, a nie formuły, identyfikatory oparte na spacjach lub dane, które muszą zachowywać dokładne odstępy.
• Nie umieszczaj wiersza nagłówka poza zakresem. Makro nie wykrywa nagłówków ani nie decyduje, które kolumny można bezpiecznie zmienić.

Użyj zakresu specyficznego dla kolumny, takiego jak B2:B500, zamiast całego używanego zakresu. Czyszczenie każdej komórki bez wyjątku może zmienić nazwy, adresy, kody lub wyniki formuł w sposób niezamierzony.

Otwórz edytor makr i dodaj skrypt

Otwórz skoroszyt w Edytorze Arkuszy ONLYOFFICE. Wybierz Widok > Makra , aby otworzyć edytor makr, a następnie utwórz makro lub zmień kod startowy w edytorze. Makra ONLYOFFICE wykorzystują składnię JavaScript i API pakietu Office; nie są to makra VBA. Przepływ pracy z makrami w edytorze i obsługiwane metody API są opisane w oficjalnym przewodniku dla początkujących dotyczącym makr .

Wklej ten przykład, a następnie zmień go B2:B500na rzeczywisty zakres zawierający wyłącznie tekst:

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

Makro odczytuje zakres do dwuwymiarowej tablicy, przetwarza komórki ciągu i zapisuje tablicę z powrotem do tego samego zakresu. CLEANUsuwa znaki niedrukowalne; dwa wyrażenia regularne usuwają spacje na krawędziach i redukują powtarzające się spacje w ciągu do jednej spacji. Dokumentacja API potwierdza, że GetValue​​może zwrócić dwuwymiarową tablicę dla zakresu i SetValueakceptuje tablice; dokumentacja GetValue , SetValue i CLEAN dokumentuje te wywołania.

Ponieważ skrypt zapisuje wartości z powrotem, nie należy kierować go do komórek zawierających formuły: komórki z formułami mogą zostać zastąpione ich aktualnie obliczonymi wartościami. Skrypt nie usuwa również zduplikowanych rekordów, nie standaryzuje wielkości liter, nie weryfikuje, czy adres jest dostarczalny, ani nie zmienia formatów numerów telefonów. Są to oddzielne reguły czyszczenia i powinny być projektowane i testowane niezależnie.

Uruchom go na kontrolowanym zakresie

1. Sprawdź literał zakresu jeszcze raz. Jeśli lista kończy się na przykład na wierszu 240, użyj B2:B240 zamiast przetwarzać setki nieużywanych wierszy.
2. Kliknij „Uruchom” w edytorze makr. Jeśli edytor zgłosi błąd składni, sprawdź cudzysłowy, nawiasy i nawiasy klamrowe przed zmianą zakresu lub dodaniem dalszej logiki.
3. Wróć do arkusza kalkulacyjnego i sprawdź kilka komórek, w tym wartość, która wymagała oczyszczenia, wartość, która już ma poprawny format, i pustą komórkę.

Rozpoczęcie od wąskiego zakresu ułatwia zrozumienie efektu. Gdy wynik będzie poprawny, możesz ponownie użyć makra na innym arkuszu, celowo edytując zakres. Nie zakładaj, że litera kolumny lub granica wiersza pozostanie prawidłowa w innym skoroszycie.

Jeśli brakuje polecenia Makra lub kod nie może zostać uruchomiony, sprawdź, czy makra są włączone w edytorze, czy wyłączone w konfiguracji aplikacji hosta. W edytorze online administrator może kontrolować dostępność makr; zaawansowane ustawienia Edytora Arkuszy Kalkulacyjnych opisują opcje makr widoczne dla użytkownika. Unikaj włączania automatycznego uruchamiania plików z nieznanych źródeł; uruchamiaj tylko kod, który rozumiesz.

Sprawdź wynik przed ponownym użyciem makra

Porównaj oczyszczoną kolumnę z oryginalną kopią, zamiast oceniać sukces tylko po wyglądzie. Upewnij się, że każdy niepusty adres e-mail nadal znajduje się w tym samym wierszu, żaden kontakt nie zniknął, a jedynymi różnicami są znaki i odstępy, które zamierzałeś znormalizować. Sprawdź próbkę w górnej, środkowej i końcowej części wybranego zakresu. Wyszukaj spację na początku lub na końcu wartości, jeśli edytor ją uwidocznił, i sprawdź zaimportowane wartości, które wcześniej zawierały podziały wiersza lub znaki kontrolne.

Porównaj również liczbę wierszy i wszelkie zależne formuły, filtry lub wyszukiwania korzystające z tej kolumny. Jeśli wynik formuły uległ nieoczekiwanej zmianie, cofnij edycję lub przywróć kopię roboczą; makro powinno być ograniczone do surowych komórek tekstowych. W przypadku większego lub krytycznego dla firmy skoroszytu porównaj całą wyczyszczoną kolumnę z kopią zapasową lub użyj najpierw osobnej kolumny wyjściowej, aby zmiany pozostały możliwe do przejrzenia.

Utrzymuj powtarzalność czyszczenia

Po poprawnym pierwszym uruchomieniu zapisz skoroszyt, aby makro pozostało dostępne w dokumencie, zgodnie z obiegiem pracy edytora. Nie należy zakładać, że makro zapisane w jednym skoroszycie pojawi się automatycznie w każdym nowym pliku. W przypadku cyklicznych importów należy zachować czysty szablon lub zabezpieczoną kopię źródłową, udokumentować zakres oczekiwany przez makro i aktualizować go po zmianie układu arkusza. Makra ONLYOFFICE są przydatne w przypadku deterministycznych reguł czyszczenia; nie potrafią one określić, czy wartość jest poprawna pod względem faktycznym, ani czy zmiana jest odpowiednia dla danego zestawu danych.

Niezawodny schemat jest prosty: zachowaj kopię źródłową, wybierz znany zakres, wykonaj jedną zdefiniowaną transformację i porównaj wynik przed udostępnieniem lub zaimportowaniem danych gdzie indziej.