Komórki

Z Henryk Dąbrowski
Skocz do: nawigacja, szukaj
03.05.2020



Uchwyt do komórek przez indeks lub przez nazwę, wpisywanie danych do komórek

Sub Komorki1()
    'wpisywanie do komórek liczb, tekstu i formuł
    Dim oSht as Object
    Dim oA1 as Object, oB2 as Object, oC3 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'uaktywnienie pierwszego arkusza
    oSht.getColumns().insertByIndex(0, 4) 'dodajemy cztery kolumny na pozycję pierwszą
    
    'komórki możemy uchwycić przez indeks lub przez nazwę, dla komórek A1, B2 i C3 będziemy mieli:
    oA1 = oSht.getCellByPosition(0, 0) 'uchwyt do komórki A1 przez indeks
    oB2 = oSht.getCellRangebyName("B2") 'uchwyt do komórki B2 przez nazwę
    oC3 = oSht.getCellByPosition(2, 2) 'uchwyt do komórki C3 przez indeks
    
    'mając dostęp do komórek możemy wpisywać do nich liczby, tekst i formuły;
    'zauważmy, że nie musimy korzystać ze zdefiniowanych wyżej obiektów oA1, oB2 i oC3
    oSht.getCellByPosition(0, 0).Value = 3/2 'wpisujemy liczbę do komórki A1 - nie korzystamy z obiektu oA1
    oB2.String = "komórka B2" 'wpisujemy tekst do komórki B2
    oC3.Formula = "=2*A1" 'wpisujemy do komórki C3 formułę, która podwaja wartość komórki A1
    
    MsgBox oSht.getCellRangebyName("B2").String
    MsgBox oC3.Value 'mamy dostęp do wartości, która wynika z wpisanej do komórki formuły
End Sub


Uchwyt przez nazwę jest bardzo czytelny, zdefiniowanie obiektu związanego z komórką skraca kod i upraszcza dostęp do komórki, ale uchwyt przez indeks ma ogromną przewagę: umożliwia przetwarzanie komórek za pomocą instrukcji For ... Next i z tego prostego powodu jest najczęściej stosowany.



Sprawdzanie zawartości komórki i określanie jej typu

Function TypKomorki(oCell as Object) as String
    'informuje o rodzaju danych zapisanych w komórce (LINK1), (LINK2)
    Dim s as String
    Select Case oCell.Type 
    Case com.sun.star.table.CellContentType.EMPTY
        s = "Pusta"
    Case com.sun.star.table.CellContentType.VALUE
        s = "Liczba"
    Case com.sun.star.table.CellContentType.TEXT
        s = "Tekst"
    Case com.sun.star.table.CellContentType.FORMULA
        s = "Formuła"
    End Select
    TypKomorki = s
End Function
Sub Komorki2()
    Dim oSht as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    MsgBox TypKomorki( oSht.getCellByPosition(0,0) ) 'A1
    MsgBox TypKomorki( oSht.getCellByPosition(1,1) ) 'B2
    MsgBox TypKomorki( oSht.getCellByPosition(2,2) ) 'C3
    MsgBox TypKomorki( oSht.getCellByPosition(3,3) ) 'D4
End Sub


Wpisywanie daty do komórki

Datę możemy wpisać do komórki używając dwóch funkcji: DateSerial() i DateValue(). Parametrami dla funkcji DateSerial() są liczby całkowite: rok, miesiąc i dzień danej daty i wywołujemy ją następująco DateSerial(YYYY, MM, DD). Parametrem dla funkcji DateValue() jest tekst zawierający te same dane i wywołujemy tę funkcję następująco DateValue("YYYY‑RR‑DD").

Sub Komorki3()
    'wpisywanie daty
    Dim oSht as Object, oA5 as Object, oA6 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oA5 = oSht.getCellByPosition(0,4) 'uchwyt do komórki A5
    oA6 = oSht.getCellByPosition(0,5) 'uchwyt do komórki A6
    oA5.Value = DateSerial(2000, 1, 1) 'A5
    oA6.String = DateSerial(2000, 1, 1) 'A6
    MsgBox oA5.Value + 7 '36533 - liczba odpowiadająca dacie 08.01.2000
    MsgBox oA6.String + 7 'ciąg znaków "01.01.20007"
    'formuły wpisane do arkusza, które wykonują takie same obliczenia jak wyżej, nie zwrócą otrzymanych wyżej wartości
    oSht.getCellByPosition(1,4).Formula = "=A5 + 7" '36533 - wynik w komórce B5
    oSht.getCellByPosition(1,5).Formula = "=A6 + 7" '36533 - wynik w komórce B6
End Sub


Datę wpisujemy jako wartość (.Value). W komórce pojawi się liczba całkowita odpowiadająca danej dacie i dopiero zmiana formatu komórki (Formatuj komórki → Liczby → Kategoria → Data) sprawi, że zostanie pokazana data w wybranym formacie.

Wpisanie daty jako ciąg znaków (.String) jest możliwe. Data zostanie zapisana w komórce jako tekst (standardowo dosunięty do lewej krawędzi komórki). Oczywiście wszelkie obliczenia na tak zapisanej dacie nie będą już możliwe. Wynika to stąd, że kod makra jest precyzyjny – musimy określić typ wartości zapisanej w komórce (.Value, .String, .Formula), do której się odwołujemy.

W arkuszu wygląda to zupełnie inaczej, bo formuła odwołuje się do komórki, ale w komórce może być wpisana liczba, tekst lub formuła. Problem ten rozwiązano, przyjmując zasadę, że w arkuszu wszystko, co da się zinterpretować jako liczba, należy tak interpretować.

Dlatego w arkuszu zostanie wykonane dodawanie: "777" + 1 = 778, jak również dodawanie: "01.01.2000" + 7 = 8.01.2000


Wpisywanie daty i formatowanie postaci, w jakiej zostanie wyświetlona

Możemy wpisać do komórki datę i ustawić format, w jakim zostanie wyświetlona. W tym celu należy dla komórki, do której wpisujemy datę, określić jej własność NumberFormat. Poniżej podaję jakie liczby trzeba wpisać dla najbardziej popularnych formatów daty w przypadku języka polskiego:

36 – 31.12.1999
37 – 31.12.99
38 – piątek 31 grudnia 1999
76 – 31 grudnia 1999
81 – 31 grudnia 1999 r.
84 – 1999-12-31


Inne wartości łatwo znaleźć rejestrując makro i zmieniając formatowanie komórki. W kodzie zarejestrowanego makra odnajdziemy zawsze tekst NumberFormatValue i odpowiednią liczbę poniżej. Przykład formatowania:

Sub Komorki4()
    'wpisywanie daty i formatowanie postaci w jakiej zostanie wyświetlona
    Dim oSht as Object, oA7 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oA7 = oSht.getCellByPosition(0,6) 'uchwyt do komórki A7
    oA7.Value = DateValue("1999-12-31") '36525 - liczba odpowiadająca dacie 31.12.1999
    oA7.NumberFormat = 36
End Sub


Wpisywanie wartości logicznej do komórki

Sub Komorki5()
    'wpisywanie wartości logicznej
    Dim oSht as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oSht.getCellByPosition(0,9).Value = True 'A10
    oSht.getCellByPosition(0,10).Value = False 'A11
    MsgBox "W komórkach A10 i A11 widoczne są liczby: -1 i 0"
    'formatujemy komórki, aby zamiast liczb -1 i 0 zostały wyświetlone słowa PRAWDA / TRUE i FAŁSZ / FALSE
    oSht.getCellByPosition(0,9).NumberFormat = 99 'A10
    oSht.getCellByPosition(0,10).NumberFormat = 99 'A11
End Sub


Wartości logiczne wpisujemy tylko jako wartość (.Value). Standardowo wartości logicznej PRAWDA odpowiada liczba -1, a wartości logicznej FAŁSZ odpowiada liczba 0. Należy pamiętać, że każda liczba różna od zera zostanie zinterpretowana jako wartość logiczna PRAWDA, a tylko liczbie zero odpowiada wartość logiczna FAŁSZ.


Wpisywanie tekstu do komórki i podstawowe formatowanie

Wpisany do komórki tekst wymaga zazwyczaj ustawienia wielu rożnych opcji formatowania. W tym przykładzie podajemy tylko podstawowe, a w następnym podamy kolejne.

Sub Komorki6()
    Dim oCll as Object
    oCll = ThisComponent.getSheets().getByIndex(0).getCellByPosition(2, 2)
    oCll.String = "abcd"
    oCll.CellBackColor = RGB(255, 0, 0) 'kolor tła komórki
    
    'ustawienie tekstu w poziomie (STANDARD, LEFT, CENTER, RIGHT, BLOCK, REPEAT) (LINK1), (LINK2)
    oCll.HoriJustify = com.sun.star.table.CellHoriJustify.RIGHT
    
    'ustawienie tekstu w pionie (STANDARD, TOP, CENTER, BOTTOM) (LINK1), (LINK2)
    oCll.VertJustify = com.sun.star.table.CellVertJustify.BOTTOM
    
    'orientacja tekstu
    oCll.RotateAngle = 9000 '90 stopni obrotu w lewo
End Sub


Wpisywanie tekstu do komórki i dalsze formatowanie

Sub Komorki7()
    'wpisywanie tekstu do komórki i formatowanie komórki
    Dim oSht as Object, oRow as Object, oA15 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oRow = oSht.getRows().getByIndex(14) 'uchwyt do wiersza o indeksie 14
    oRow.Height = 1500 'ustawia wysokość wiersza równą 15 mm
    oA15 = oSht.getCellByPosition(0,14) 'uchwyt do komórki A15
    oA15.String = "Komórka A15"
    oA15.CellBackColor = RGB(204, 153, 102) 'kolor tła komórki
    oA15.CharHeight = 14 'rozmiar czcionki
    
    'grubość czcionki (DONTKNOW, THIN, ULTRALIGHT, LIGHT, SEMILIGHT, NORMAL, SEMIBOLD, BOLD, ULTRABOLD, BLACK) (LINK1), (LINK2)
    oA15.CharWeight = com.sun.star.awt.FontWeight.BOLD
    
    oA15.setPropertyValue( "IsTextWrapped", True ) 'zawija tekst automatycznie
    oA15.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER 'wyśrodkowanie tekstu w poziomie
    oA15.VertJustify = com.sun.star.table.CellVertJustify.CENTER 'wyśrodkowanie tekstu w pionie
End Sub


Formatowanie komórki liczbowej

Funkcja oblicza liczbę całkowitą, która określa, jak zostanie sformatowana komórka liczbowa. Najłatwiej kod formatu (parametr sNumFormat) uzyskać wykorzystując okno "Formatuj komórki". Wybieramy potrzebne ustawienia (liczba miejsc po przecinku, separator tysięcy itd.), kopiujemy otrzymany kod i wykorzystujemy go jako parametr sNumFormat. (LINK)

Function NumberFormatId( sNumFormat as String, sLanguage as String, sCountry as String ) as Long
    'Dla USA: sLanguage="en", sCountry="us"
    'Przykładowo: sNumFormat="# ###,00"
    'Sposób użycia: Cell.NumberFormat = NumberFormatId("# ###,00", "pl", "pl")
    Dim LocalSettings As New com.sun.star.lang.Locale
    
    LocalSettings.Language = sLanguage
    LocalSettings.Country = sCountry
    NumberFormatId = ThisComponent.NumberFormats.queryKey(sNumFormat, LocalSettings, True)
    If NumberFormatId = -1 Then
        NumberFormatId = ThisComponent.NumberFormats.addNew(sNumFormat, LocalSettings)
    End If
End Function
Sub Komorki8()
    Dim oA1 as Object
    oA1 = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1") 'uchwyt do komórki A1 przez nazwę
    oA1.Value = 12345.6789
    oA1.NumberFormat = NumberFormatId("# ###,00", "pl", "pl")
End Sub


Odczytywanie zawartości komórki i operacje na takich danych

Sub Komorki9()
    'odczytywanie wartości komórki i opercje na tych wartościach
    Dim oSht as Object
    Dim oA1 as Object, oA2 as Object, oA3 as Object
    Dim oC1 as Object, oC2 as Object
    Dim x as Double, y as Double
    Dim s as String
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oA1 = oSht.getCellByPosition(0, 0) 'uchwyty do komórek
    oA2 = oSht.getCellRangebyName("A2")
    oA3 = oSht.getCellByPosition(0, 2)
    oC1 = oSht.getCellRangebyName("C1")
    oC2 = oSht.getCellByPosition(2, 1)
    
    oA1.Value = 5 'zapisujemy liczbę w komórce A1
    oA2.String = "To jest formuła: " 'zapisujemy tekst w komórce A2
    oA3.Formula = "=2*A1" 'zapisujemy formułę w komórce A3
    
    x = oA1.Value 'odczytujemy liczbę zapisaną w komórce A1
    s = oA2.String 'odczytujemy tekst zapisany w komórce A2
    y = oA3.Value 'odczytujemy LICZBĘ wyliczoną z formuły zapisanej w komórce A3
    
    oC1.Value = x + y 'zapisujemy sumę liczb z komórek A1 i A3
    oC2.String = s & oA3.Formula 'zapisujemy połączone teksty: z komórki A2 i odczytaną formułę z komórki A3
End Sub


Jeszcze jeden przykład:

Sub Komorki10()
    'operacje na wartościach odczytanych z komórek
    Dim oSht as Object
    Dim oA1 as Object, oA2 as Object, oA3 as Object, oA4 as Object
    Dim oB1 as Object, oB2 as Object, oB3 as Object, oB4 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    ThisComponent.CurrentController.setActiveSheet(oSht) 'aktywacja pierwszego arkusza
    oSht.getColumns().insertByIndex(0, 3) 'dodajemy trzy kolumny na pozycję pierwszą
    oA1 = oSht.getCellRangebyName("A1") 'uchwyt do komórki A1 przez nazwę
    oA2 = oSht.getCellRangebyName("A2")
    oA3 = oSht.getCellRangebyName("A3")
    oA4 = oSht.getCellRangebyName("A4")
    oB1 = oSht.getCellRangebyName("B1")
    oB2 = oSht.getCellRangebyName("B2")
    oB3 = oSht.getCellRangebyName("B3")
    oB4 = oSht.getCellRangebyName("B4")
    
    'wpisywanie danych
    oA1.String = "abc"
    oA2.String = "123"
    oB1.Value = 5
    oB2.Value = 7
    
    'wpisywanie sumy odczytanych wartości do nowej komórki (LINK)
    oA3.String = oA1.String & oA2.String
    oB3.Value = oB1.Value + oB2.Value
    
    'operator + można stosować do tekstu, a operator & do liczb - zobacz rezultat (LINK)
    oA4.String = oA1.String + oA2.String
    oB4.Value = oB1.Value & oB2.Value
End Sub


Ważne własności komórki

Dysponując uchwytem do komórki, możemy uzyskać uchwyt do arkusza, z którego pochodzi komórka. Poza tym możemy poznać indeks tego arkusza oraz indeks kolumny i wiersza, w których komórka jest położona.

Sub Komorki11()
    Dim oSht as Object, oCll as Object
    oCll = ThisComponent.getSheets().getByIndex(0).getCellByPosition(1, 2)
    oSht = oCll.Spreadsheet 'uchwyt do arkusza komórki
    MsgBox oSht.getName()
    
    'indeks arkusza, kolumny i wiersza
    MsgBox oCll.getCellAddress().Sheet
    MsgBox oCll.getCellAddress().Column
    MsgBox oCll.getCellAddress().Row
End Sub


Kasowanie zawartości komórki

Nim wykonamy kasowanie zawartości komórki, 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 Komorki12()
    'usuwanie zawartości komórki
    Dim oSht as Object, oCll as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    oCll = oSht.getCellRangeByName("A1") 'tworzymy uchwyt do komórki
    oCll.clearContents(7) 'usuwa liczby, liczby sformatowane jako data i czas oraz tekst
End Sub


Kopiowanie komórek

Musimy przygotować uchwyty do każdej z komórek (kopiowanej i docelowej), a następnie wywołać metodę arkusza copyRange z odpowiednimi parametrami (LINK).

Sub Komorki13()
    'kopiowanie komórki Cll1 do komórki Cll2
    Dim oSht as Object, oCll1 as Object, oCll2 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    
    'tworzymy uchwyty do obydwu komórek:
    oCll1 = oSht.getCellRangeByName("A1")
    oCll2 = oSht.getCellRangeByName("B2")
    
    'kopiowanie wymaga wywołania metody arkusza copyRange z następującymi parametrami:
    oSht.copyRange( oCll2.getCellAddress(), oCll1.getRangeAddress() )
End Sub


Gdybyśmy z jakiegoś powodu nie mieli uchwytu do arkusza, z którego wzięliśmy komórki, to możemy zrobić tak:

Sub Komorki14()
    'kopiowanie komórki Cll1 do komórki Cll2
    Dim oCll1 as Object, oCll2 as Object
    oCll1 = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1")
    oCll2 = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("B2")
    oCll1.Spreadsheet.copyRange( oCll2.getCellAddress(), oCll1.getRangeAddress() )
End Sub


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

Sub Komorki15()
    'kopiowanie komórki Cll1 pierwszego arkusza do komórki Cll2 drugiego arkusza
    Dim oSht1 as Object, oSht2 as Object, oCll1 as Object, oCll2 as Object
    oSht1 = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    oSht2 = ThisComponent.getSheets().getByIndex(1) 'uchwyt do drugiego arkusza
    
    'tworzymy uchwyty do obydwu komórek:
    oCll1 = oSht1.getCellRangeByName("A1")
    oCll2 = oSht2.getCellRangeByName("B2")
    
    'tak samo, jak to uczyniliśmy wyżej, wywołujemy metodę copyRange (dowolnego arkusza):
    oSht1.copyRange( oCll2.getCellAddress(), oCll1.getRangeAddress() )
End Sub


Przesuwanie komórek

Przesuwanie 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 każdej z komórek (kopiowanej i docelowej), a następnie wywołać metodę arkusza moveRange z odpowiednimi parametrami.

Sub Komorki16()
    'przesuwanie komórki Cll1 w miejsce komórki Cll2
    Dim oSht as Object, oCll1 as Object, oCll2 as Object
    oSht = ThisComponent.getSheets().getByIndex(0) 'uchwyt do pierwszego arkusza
    
    'tworzymy uchwyty do obydwu komórek:
    oCll1 = oSht.getCellRangeByName("A1")
    oCll2 = oSht.getCellRangeByName("B2")
    
    'przesuwanie wymaga wywołania metody arkusza moveRange z następującymi parametrami:
    oSht.moveRange( oCll2.getCellAddress(), oCll1.getRangeAddress() )
End Sub


Usuwanie komórek

Metoda arkusza removeRange usuwa "fizycznie" wybraną komórkę (jeśli można tak powiedzieć o komórce, która 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 - komórka zostaje usunięta, komórki leżące poniżej zostaną przesunięte w górę
  • LEFT - komórka zostaje usunięta, komórki leżące na prawo zostaną przesunięte w lewo
  • ROWS - komórka zostaje usunięta, wiersze leżące poniżej zostaną przesunięte w górę
  • COLUMNS - komórka zostaje usunięta, kolumny leżące na prawo zostaną przesunięte w lewo


Opcja ROWS odpowiada usunięciu wiersza, w którym położona była komórka. Opcja COLUMNS odpowiada usunięciu kolumny, w której położona była komórka. (LINK1), (LINK2)

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



Polecane strony internetowe

BASIC Guide – Cells and Ranges

Spreadsheet common – Working with cells

Apache OpenOffice – service CellProperties





LibreOffice Calc – makra                   Strona główna