Zakresy komórek

27.07.2020

Uchwyt do zakresu komórek

Zakres komórek możemy uchwycić trzema sposobami:
1) wybierając aktywny obszar komórek
2) określając zakres komórek przez podanie lewej górnej i prawej dolnej komórki
3) podając indeksy kolumn i wierszy ograniczających zakres komórek w kolejności: lewa kolumna, górny wiersz, prawa kolumna, dolny wiersz

Poniższa procedura ilustruje te sposoby, pokazuje różnicę między uchwytem do jednej komórki, a uchwytem do zakresu komórek oraz przedstawia, jak odczytać indeksy kolumn i wierszy ograniczających zakres komórek.

Sub ZakresyKomorek1()
    Dim oSht as Object, oRng as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    oRng = oSht.getCellRangeByName("D9") 'uchwyt do zakresu komórek składającego się z jednej komórki
    Msgbox oRng.getCellAddress().Sheet
    Msgbox oRng.getCellAddress().Row
    Msgbox oRng.getCellAddress().Column
    Msgbox oRng.AbsoluteName 'zwraca adres bezwzględny komórki jako tekst np. "$Arkusz1.$D$9"
     
    '1. wybieramy aktualnie aktywny spójny obszar komórek
    oRng = ThisComponent.getCurrentSelection()
    '2. wybieramy zakres komórek przez podanie nazw lewej górnej i prawej dolnej komórki
    oRng = oSht.getCellRangeByName("B2:D9")
    '3. wybieramy zakres komórek przez podanie indeksów kolumn i wierszy: 
    '   getCellRangeByPosition(colLeft, rowTop, colRight, rowBottom)
    oRng = oSht.getCellRangeByPosition(1, 1, 3, 8) 'zakres komórek od B2 do D9
     
    Msgbox oRng.getRangeAddress().Sheet 'indeks arkusza
    Msgbox oRng.getRangeAddress().StartColumn 'indeks lewej kolumny
    Msgbox oRng.getRangeAddress().StartRow 'indeks górnego wiersza
    Msgbox oRng.getRangeAddress().EndColumn 'indeks prawej kolumny
    Msgbox oRng.getRangeAddress().EndRow 'indeks dolnego wiersza
    Msgbox oRng.AbsoluteName 'zwraca adres bezwzględny zakresu komórek jako tekst np. "$Arkusz1.$B$2:$D$9"
End Sub

Operowanie na komórkach zakresu komórek

Zauważmy, że metody getColumns, getRows, getCellByPosition możemy stosować do zakresu komórek. Umożliwia to adresowanie względne - wewnątrz zakresu komórek. Teraz operowanie na poszczególnych komórkach z zakresu komórek staje się bardzo proste:

Sub ZakresyKomorek2()
    'liczba kolumn i wierszy zakresu komórek; uchwyt do komórki w zakresie komórek
    'wypełnianie zakresu komórek danymi
    Dim oSht as Object, oRng as Object, oCll as Object
    Dim c as Long, r as Long
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    oRng = oSht.getCellRangeByName("B2:D9") 'uchwyt do zakresu komórek
    
    For c = 0 To oRng.getColumns().getCount() - 1
        For r = 0 To oRng.getRows().getCount() - 1
            oCll = oRng.getCellByPosition(c, r) 'uchwyt do komórki w zakresie komórek
            oCll.String = c & r
        Next r
    Next c
    
End Sub

Uwaga: Metodę getCellRangebyName możemy stosować do obiektu oRng, gdy komórka o danej nazwie należy do zakresu komórek Rng, w przeciwnym przypadku wystąpi błąd. Inaczej mówiąc, metoda ta nie pozwala na adresowanie względne w zakresie komórek Rng. Identyfikacja komórki przez nazwę jest jednoznaczna.

Zamiast operować na komórkach zakresu komórek, możemy operować na komórkach arkusza. Tak skonstruowana procedura jest może mniej elegancka, ale równie skuteczna:

Sub ZakresyKomorek3()
    Dim oSht as Object, oRng as Object, oRngAddr as Object, oCll as Object
    Dim c as Long, r as Long
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    oRng = oSht.getCellRangeByName("B2:D9") 'uchwyt do zakresu komórek
    oRngAddr = oRng.getRangeAddress() 'ten obiekt sprawi, że kod będzie bardziej czytelny
    
    For c = oRngAddr.StartColumn To oRngAddr.EndColumn 'od lewej do prawej kolumny
        For r = oRngAddr.StartRow To oRngAddr.EndRow 'od górnego do dolnego wiersza
            oCll = oSht.getCellByPosition(c, r) 'uchwyt do komórki w arkuszu
            oCll.String = c & "-" & r
        Next r
    Next c
    
End Sub

Formatowanie zakresu komórek

Przedstawiamy niżej formatowanie zakresu komórek tak, aby można było zapisywać w komórkach tekst w określonym formacie. Formatując zakres komórek w przypadku dat i liczb postępujemy tak samo, jak to robiliśmy w przypadku jednej komórki.

Sub ZakresyKomorek4()
    'formatowanie zakresu komórek
    Dim oSht as Object, oRng as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    oRng = oSht.getCellRangeByName("B2:D9") 'uchwyt do zakresu komórek
    oRng.CellBackColor = RGB(255, 0, 0) 'kolor tła zakresu komórek
    
    'rozmiar czcionki
    oRng.CharHeight = 16
    'grubość czcionki (DONTKNOW, THIN, ULTRALIGHT, LIGHT, SEMILIGHT, NORMAL, SEMIBOLD, BOLD, ULTRABOLD, BLACK) (LINK1), (LINK2)
    oRng.CharWeight = com.sun.star.awt.FontWeight.BOLD
        
    'ustawienie tekstu w poziomie (STANDARD, LEFT, CENTER, RIGHT, BLOCK, REPEAT) (LINK1), (LINK2)
    oRng.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    'ustawienie tekstu w pionie (STANDARD, TOP, CENTER, BOTTOM) (LINK1), (LINK2)
    oRng.VertJustify = com.sun.star.table.CellVertJustify.CENTER
    
    'zawijanie tekstu
    oRng.setPropertyValue( "IsTextWrapped", True ) 'zawija tekst automatycznie
    'orientacja tekstu
    oRng.RotateAngle = 9000 '90 stopni obrotu w lewo
End Sub

Operacja „Znajdź i zamień”

Poniżej przedstawiamy przykład procedury, która przeszukuje wskazany zakres komórek i zastępuje podany ciąg znaków innym ciągiem znaków. Procedurę zamiast do zakresu komórek oRng możemy zastosować do całego arkusza oSht lub do jednej komórki. Ustawienie opcji SearchCaseSensitive na TRUE powoduje, że przy przeszukiwaniu rozróżniane są wielkie i małe litery. Ustawienie opcji SearchWords na TRUE powoduje, że wyszukiwane są jedynie całe słowa (np. słowo „tron” nie zostanie odnalezione w słowie „strona”). Ustawienie opcji SearchRegularExpression na TRUE powoduje, że nie są wyszukiwane ciągi znaków zgodne z innym ciągiem, ale wszystkie ciągi pasujące do podanego wzorca. (LINK)

Sub ZakresyKomorek5()
    Dim c as Long, r as Long, n as Long
    Dim oSht as Object, oRng as Object, oReplaceDescr as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    'wypełniamy komórki arkusza przykładowym tekstem
    For c = 0 To 10
        For r = 0 To 25
            oSht.getCellByPosition(c, r).String = "Ala ma kota."
        Next r
    Next c
    
    'realizacja operacji "znajdź i zmień" (zwykły tekst)
    oRng = oSht.getCellRangeByName("B2:F20")
    oReplaceDescr = oRng.createReplaceDescriptor()
    oReplaceDescr.SearchString = "kota"
    oReplaceDescr.ReplaceString = "psa"
    oReplaceDescr.SearchCaseSensitive = True
    oReplaceDescr.SearchWords = False
    oReplaceDescr.SearchRegularExpression = False
    n = oRng.replaceAll( oReplaceDescr )
    MsgBox "Wykonano " & n & " zastąpień"
End Sub

Wyrażenie regularne: w przykładzie zamieniamy wiele spacji na dokładnie jedną spację.

Sub ZakresyKomorek6()
    Dim c as Long, r as Long, n as Long
    Dim oSht as Object, oRng as Object, oReplaceDescr as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    'wypełniamy komórki arkusza przykładowym tekstem
    For c = 0 To 10
        For r = 0 To 25
            oSht.getCellByPosition(c, r).String = "Ala  ma    kota."
        Next r
    Next c
    
    'realizacja operacji "znajdź i zmień" (wyrażenie regularne)
    oRng = oSht.getCellRangeByName("B2:F20")
    oReplaceDescr = oRng.createReplaceDescriptor()
    oReplaceDescr.SearchString = " +"
    oReplaceDescr.ReplaceString = " "
    oReplaceDescr.SearchCaseSensitive = True
    oReplaceDescr.SearchWords = False
    oReplaceDescr.SearchRegularExpression = True
    n = oRng.replaceAll( oReplaceDescr )
    MsgBox "Wykonano " & n & " zastąpień"
End Sub

Scalanie komórek

W wyniku scalenia lewa górna komórka scalonego zakresu komórek jest wyświetlana na całym obszarze, który wcześniej zajmowały te komórki. Scalone komórki, poza lewą górną komórką, przestają być widoczne, ale nadal istnieją i są w pełni dostępne. Metoda getIsMerged() pozwala ustalić, czy komórki zostały scalone, jednak należy pamiętać, że wartość TRUE jest zwracana tylko dla lewej górnej komórki ze scalonego zakresu. Przedstawia to poniższa procedura.

Sub ZakresyKomorek7()
    Dim c as Long, r as Long
    Dim oSht as Object, oRng as Object, oCll as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oCll = oSht.getCellRangeByName("B1")
    oRng = oSht.getCellRangeByName("B2:D4")
    For c = 0 To oRng.getColumns().getCount() - 1
        For r = 0 To oRng.getRows().getCount() - 1
            oRng.getCellByPosition(c, r).String = Replace(oRng.getCellByPosition(c, r).AbsoluteName, "$", "")
        Next r
    Next c
    oCll.String = "Komórki zakresu zostały oznaczone"
    MsgBox oCll.String
    oRng.merge(True) 'scalamy komórki z zakresu oRng
    oCll.String = "Komórki zakresu zostały scalone"
    MsgBox oCll.String
    For c = 0 To oRng.getColumns().getCount() - 1
        For r = 0 To oRng.getRows().getCount() - 1
            oRng.getCellByPosition(c, r).String = oRng.getCellByPosition(c, r).getIsMerged()
        Next r
    Next c
    oCll.String = "Do komórek zakresu zostały wpisane wartości metody getIsMerged()"
    MsgBox oCll.String
    oRng.merge(False) 'dzielimy komórki z zakresu oRng
    oCll.String = "Komórki zakresu zostały podzielone"
    MsgBox oCll.String
End Sub

Zamieszona wyżej procedura pokazuje, że metoda getIsMerged() nie udzieli nam (w ogólności) odpowiedzi na pytanie, czy wybrana komórka należy do scalonego zakresu komórek. Poniższa procedura przedstawia, jak możemy ustalić rozmiar scalonego obszaru, w skład którego wchodzi wybrana przez nas komórka, a tym samym ustalić, czy ten zakres jest większy od zakresu złożonego tylko z jednej komórki.

Sub ZakresyKomorek8()
    Dim oSht as Object, oRng as Object, oCll as Object, oCursor as Object
    oSht = ThisComponent.getSheets.getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oCll = oSht.getCellRangeByName("B1")
    oRng = oSht.getCellRangeByName("B2:D4")
    oRng.merge(True) 'scalamy komórki z zakresu oRng
    oCursor = oSht.createCursorByRange( oSht.getCellRangeByName("D4") )
    oCursor.collapseToMergedArea() 'powiększa zakres kursora do obszaru scalenia
    oCll.String = "Zakres scalonych komórek: " & Replace(oCursor.AbsoluteName, "$", "")
    MsgBox oCll.String
End Sub

Kasowanie zawartości zakresu komórek

Nim wykonamy kasowanie zawartości zakresu komórek, musimy ustalić co chcemy skasować. Różnym typom zawartości przypisane zostały różne liczby:
VALUE (1) - liczby niesformatowane jako data i czas
DATETIME (2) - liczby sformatowane jako data i czas
STRING (4) - tekst
ANNOTATION (8) - komentarze
FORMULA (16) - formuły
Zobacz więcej: (LINK)

Ustalamy, co chcemy skasować, wybieramy odpowiednie liczby i sumujemy. Znaleziona suma jest parametrem dla metody clearContents.

Sub ZakresyKomorek9()
    'kasowanie zawartości zakresu komórek
    Dim oSht as Object, oRng as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    oRng = oSht.getCellRangeByName("B2:D9") 'tworzymy uchwyt do zakresu komórek
    oRng.clearContents(7) 'usuwa liczby, liczby sformatowane jako data i czas oraz tekst
End Sub

Kopiowanie zakresu komórek

Musimy przygotować uchwyty do kopiowanego zakresu komórek oraz uchwyt do komórki, która wskaże początek obszaru (czyli lewy górny róg), do którego zakres komórek zostanie skopiowany. Następnie wywołujemy metodę arkusza copyRange z odpowiednimi parametrami (LINK).

Sub ZakresyKomorek10()
    'kopiowanie zakresu komórek Rng do obszaru, którego początek wyznacza komórka Cll
    Dim oSht as Object, oRng as Object, oCll as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    
    'tworzymy uchwyty do zakresu komórek Rng i komórki Cll:
    oRng = oSht.getCellRangeByName("B2:D9")
    oCll = oSht.getCellRangeByName("F5")
    
    'kopiowanie wymaga wywołania metody arkusza copyRange z następującymi parametrami:
    oSht.copyRange( oCll.getCellAddress(), oRng.getRangeAddress() )
End Sub

Gdybyśmy z jakiegoś powodu nie mieli uchwytu do arkusza z zakresem komórek Rng i komórką wyznaczającą obszar docelowy Cll, to możemy zrobić tak:

Sub ZakresyKomorek11()
    'kopiowanie zakresu komórek Rng do obszaru, którego początek wyznacza komórka Cll
    Dim oRng as Object, oCll as Object
    oRng = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("B2:D9")
    oCll = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("F5")
    oRng.Spreadsheet.copyRange( oCll.getCellAddress(), oRng.getRangeAddress() )
End Sub

Jeżeli chcemy zakres komórek przekopiować do innego arkusza, to postępujemy analogicznie:

Sub ZakresyKomorek12()
    'kopiowanie zakresu komórek Rng pierwszego arkusza do obszaru,
    'którego początek wyznacza komórka Cll drugiego arkusza
    Dim oSht1 as Object, oSht2 as Object, oRng as Object, oCll as Object
    oSht1 = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    oSht2 = ThisComponent.getSheets().getByIndex(1) 'uchwyt do drugiego arkusza
    
    'tworzymy uchwyty do zakresu komórek Rng i komórki Cll:
    oRng = oSht1.getCellRangeByName("B2:D9")
    oCll = oSht2.getCellRangeByName("F5")
    
    'tak samo, jak to uczyniliśmy wyżej, wywołujemy metodę copyRange (dowolnego arkusza):
    oSht2.copyRange( oCll.getCellAddress(), oRng.getRangeAddress() )
End Sub

Przesuwanie zakresu komórek

Przesuwanie zakresu komórek z jednego miejsca arkusza w inne lub z jednego arkusza na drugi wykonujemy dokładnie tak samo, jak kopiowanie. Musimy tym razem użyć metody arkusza moveRange (LINK). Dlatego poprzestaniemy na jednym przykładzie.

Musimy przygotować uchwyty do przesuwanego zakresu komórek oraz uchwyt do komórki, która wskaże początek obszaru (czyli lewy górny róg), do którego zakres komórek zostanie przesunięty. Następnie wywołujemy metodę arkusza moveRange z odpowiednimi parametrami.

Sub ZakresyKomorek13()
    'przesuwanie zakresu komórek Rng do obszaru, którego początek wyznacza komórka Cll
    Dim oSht as Object, oRng as Object, oCll as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    
    'tworzymy uchwyty do zakresu komórek Rng i komórki Cll:
    oRng = oSht.getCellRangeByName("B2:D9")
    oCll = oSht.getCellRangeByName("F5")
    
    'przesuwanie wymaga wywołania metody arkusza moveRange z następującymi parametrami:
    oSht.moveRange( oCll.getCellAddress(), oRng.getRangeAddress() )
End Sub

Usuwanie komórek

Metoda arkusza removeRange usuwa "fizycznie" wybrany zakres komórek (jeśli można tak powiedzieć o zakresie komórek, który istnieje tylko wirtualnie). Dlatego musimy określić jakie dodatkowe działanie zostanie wykonane, aby w arkuszu nie pozostała "dziura". Mamy do wyboru następujące opcje:

NONE - removeRange nie robi nic
UP - zakres komórek zostaje usunięty, komórki leżące poniżej zostaną przesunięte w górę
LEFT - zakres komórek zostaje usunięty, komórki leżące na prawo zostaną przesunięte w lewo
ROWS - zakres komórek zostaje usunięty, wiersze leżące poniżej zostaną przesunięte w górę
COLUMNS - zakres komórek zostaje usunięty, kolumny leżące na prawo zostaną przesunięte w lewo

Opcja ROWS odpowiada usunięciu wierszy, w których położona był usuwany zakres komórek. Opcja COLUMNS odpowiada usunięciu kolumn, w których położony był usuwany zakres komórek. (LINK1), (LINK2)

Sub ZakresyKomorek14()
    'usuwanie zakresu komórek B2:D9
    Dim oSht as Object, oRng as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    oRng = oSht.getCellRangeByName("B2:D9") 'uchwyt do zakresu komórek B2:D9
    oSht.removeRange( oRng.getRangeAddress(), com.sun.star.sheet.CellDeleteMode.UP ) 'metoda removeRange z parametrem UP
End Sub