Makro eksportujące komórki do pliku.

[kris86k]

Witam.
Potrzebuję wyeksportować do pliku zakres komórek np: T2:V100 ale tylko wiersze w których A1:A100 są niepuste lub większe od 0 (nie ma to znaczenia).
Znalazłem skrypt jak poniżej. Czy da się go zmodyfikować do moich potrzeb aby filtrował wynik eksportu?

Kod: Zaznacz cały

Sub CreateCSVFile
   Dim sURLFolder as String
   Dim sFileName As String
   Dim i As Integer
   Dim n As Integer
   Dim oSheet
   Dim oCell
   Dim oRange
   Dim CellContentArray
   
   REM Get the active sheet object
   oSheet = ThisComponent.getCurrentController.ActiveSheet
   
   REM Get the cell contents 
   oRange = oSheet.getCellRangeByPosition(19,1,21,99)
   CellContentArray = oRange.getDataArray()
      
   REM Get the filename from cell M1 (column 12, row 0) of the active sheet
   oCell = oSheet.getCellByPosition(12,0)
   
   REM Get the folder name this file is saved in and append the filename
   sURLFolder=replace(ThisComponent.url,ThisComponent.title,"")
   sFileName = sURLFolder & oCell.getString() & "eksport.csv"
   
   REM Create a new file (if this filename exists, it will be deleted and replaced)
   n = FreeFile()
   Open sFileName For Output As #n
   
   REM Write the contents of the data array to the file
   REM This assumes all rows are six cells wide
   For i = 0 To UBound(CellContentArray)
      Print #n, CellContentArray(i)(0), CellContentArray(i)(1), CellContentArray(i)(2)
   Next
   
   REM Close the file
   Close #n

End Sub

[Jan_J]

Tablica

Kod: Zaznacz cały

oRange = oSheet.getCellRangeByPosition(19,1,21,99)
CellContentArray = oRange.getDataArray()

obejmuje obszar T2:V100.
Można zrobić sobie drugą podobną, z obszarem A2:A100.

Pętla

Kod: Zaznacz cały

For i = 0 To UBound(CellContentArray)
      Print #n, CellContentArray(i)(0), CellContentArray(i)(1), CellContentArray(i)(2)
Next

wyprowadza do otwartego pliku #n i-ty wiersz z obszaru. Wystarczy, by robiła to warunkowo, coś w stylu

Kod: Zaznacz cały

For i = 0 To UBound(CellContentArray)
   If CellContentArray(i)(0) > 0 Then
      Print #n, CellContentArray(i)(0), CellContentArray(i)(1), CellContentArray(i)(2)
   Endif
Next

przy czym warunek trzeba starannie dobrać.
Ja napisałem CellContentArray(i)(0) > 0 -- to się odnosi do komórki z kolumny T. Chcąc sprawdzać kolumnę A, albo ustaw dla niej podobną tablicę (patrz pierwszy mój komentarz), albo wpisz warunek oSheet.getCellByPosition(0,i).getValue() > 0, który jawnie odsyła do i-tego wiersza w kolumnie A.
Taki warunek będzie dawał wartość True, jeżeli w komórce pojawi się liczba dodatnia; False, jeśli liczba będzie niedodatnia albo komórka będzie pusta; wygeneruje błąd jeżeli komórka zawiera tekst lub formułę z wynikiem tekstowym.

[kris86k]

Niestety nie ogarniam tego. W komórce od A2:A100 będzie albo liczba dodatnia albo pusta.

Kod: Zaznacz cały

  For i = 0 To UBound(CellContentArray)
   IF oSheet.getCellByPosition(0,i),getValue() > 0 Then
      Print #n, CellContentArray(i)(0), CellContentArray(i)(1), CellContentArray(i)(2)
     Endif   
   Next

Wyrzucą błąd:

Błąd składni BASIC. Oczekiwano: Then.

[Jan_J]

Pisałem od ręki bez testowania. Banał: zamiast
oSheet.getCellByPosition(0,i),getValue()
ma być
oSheet.getCellByPosition(0,i).getValue()
Z kropką zamiast przecinka. W odpowiedzi poprawiłem.

[Jermor]

Mam wrażenie drogi Janie, że instrukcja basica powinna wyglądać tak:

Kod: Zaznacz cały

IF oSheet.getCellByPosition(0,i+1).getString <> "" Then

Trzeba przesunąć indeks o 1, bo indeks tablicy nie jest tożsamy z numerem wiersza.
Zastosowanie getString pozwala wyodrębnić tylko komórki rzeczywiście puste.
No i wystąpiła niezamierzona "literówka" przecinek zamiast kropki.
Albo tak jak proponowałeś:

Kod: Zaznacz cały

 oRange2=osheet.getCellRangeByPosition(0,1,0,99)   
   CellConditionArray=oRange2.getDataArray()

i dalej

Kod: Zaznacz cały

	If CellConditionArray(i)(0)<>"" Then 

[kris86k]

Zadziałało połączenie obu wersji Bardzo dziękuję.

Mam teraz jeszcze jeden mały problem. Chcę wygenerować dwa różne pliki z różnymi zakresami. Zrobiłem jak poniżej ale generuje tylko plik z pierwszego zakresu. Co jest nie tak?

Kod: Zaznacz cały

Sub CreateCSVFile
   Dim sURLFolder as String
   Dim sFileName As String
   Dim i As Integer
   Dim n As Integer
   Dim oSheet
   Dim oCell
   Dim oRange
   Dim CellContentArray
   
   REM Get the active sheet object
   oSheet = ThisComponent.getCurrentController.ActiveSheet
   
   REM Get the cell contents of U2 (column 20, row 1) to U3 (column 20, row 2)
   oRange = oSheet.getCellRangeByPosition(19,1,21,99)
   CellContentArray = oRange.getDataArray()
      
   REM Get the filename from cell M1 (column 12, row 0) of the active sheet
   oCell = oSheet.getCellByPosition(1002,0)
   
   REM Get the folder name this file is saved in and append the filename
   sURLFolder="D:\Dropbox\EU csv\AK CEN!\"
   sFileName = sURLFolder & oCell.getString() & "sqlcenyaukcje.csv"
   
   REM Create a new file (if this filename exists, it will be deleted and replaced)
   n = FreeFile()
   Open sFileName For Output As #n
   
   REM Write the contents of the data array to the file
   REM This assumes all rows are six cells wide
   For i = 0 To UBound(CellContentArray)
   IF oSheet.getCellByPosition(0,i+1).getValue() > 0 Then
      Print #n, CellContentArray(i)(0), CellContentArray(i)(1), CellContentArray(i)(2)
     Endif   
   Next
   
   REM Close the file
   Close #n

End Sub

Sub CreateCSVFail1
   Dim sURLFolder as String
   Dim sFileName As String
   Dim i As Integer
   Dim n As Integer
   Dim oSheet
   Dim oCell
   Dim oRange
   Dim CellContentArray
   
   REM Get the active sheet object
   oSheet = ThisComponent.getCurrentController.ActiveSheet
   
   REM Get the cell contents of U2 (column 20, row 1) to U3 (column 20, row 2)
   oRange = oSheet.getCellRangeByPosition(21,1,21,99)
   CellContentArray = oRange.getDataArray()
      
   REM Get the filename from cell M1 (column 12, row 0) of the active sheet
   oCell = oSheet.getCellByPosition(1002,0)
   
   REM Get the folder name this file is saved in and append the filename
   sURLFolder="D:\Dropbox\EU csv\AK CEN!\"
   sFileName = sURLFolder & oCell.getString() & "sqlcenyaukcje1.csv"
   
   REM Create a new file (if this filename exists, it will be deleted and replaced)
   n = FreeFile()
   Open sFileName For Output As #n
   
   REM Write the contents of the data array to the file
   REM This assumes all rows are six cells wide
   For i = 0 To UBound(CellContentArray)
   IF oSheet.getCellByPosition(0,i+1).getValue() > 0 Then
      Print #n, CellContentArray(i)(0)
     Endif   
   Next
   
   REM Close the file
   Close #n

End Sub

[Jan_J]

Ta instrukcja

Kod: Zaznacz cały

oRange = oSheet.getCellRangeByPosition(19,1,21,99)

deklaruje eksportowany zakres. Wydaje się że w obu wersjach jest taka sama.
Poza tym nie wiem czy uruchamiasz jedno makro czy oba.

W jakim kierunku iść?
napisać JEDNO makro i wywołać je wielokrotnie dla różnych zakresów i różnych nazw plików.

Coś jak

Kod: Zaznacz cały

    Sub CreateCSVFile(oSheet, startRow, startCol, endRow, endCol, sFileName)
       Dim i As Integer
       Dim n As Integer
       Dim oRange
       Dim CellContentArray
       
       REM Get the cell contents
       oRange = oSheet.getCellRangeByPosition(startCol,startRow,endCol,endRow)
       CellContentArray = oRange.getDataArray()
       
       REM Create a new file (if this filename exists, it will be deleted and replaced)
       n = FreeFile()
       Open sFileName For Output As #n
       
       REM Write the contents of the data array to the file
       For i = 0 To UBound(CellContentArray)-1
         v = oSheet.getCellByPosition(0,i+startRow).getValue()
         if v > 0 Then
          Print #n, CellContentArray(i)(0), CellContentArray(i)(1), CellContentArray(i)(2)
         Endif   
       Next
       
       REM Close the file
       Close #n

    End Sub

    Sub CreateCSVFiles()
       Dim sURLFolder as String
       Dim sFileName As String
       Dim oSheet
       Dim oCell
       
       REM Get the active sheet object
       oSheet = ThisComponent.getCurrentController.ActiveSheet
'       CreateCSVFile(oSheet, 0,1,10,5, "kuku.txt")
        
       REM Get the filename from cell M1 (column 12, row 0) of the active sheet
       oCell = oSheet.getCellByPosition(1002,0)
       
       REM Get the folder name this file is saved in and append the filename
       sURLFolder="D:\Dropbox\EU csv\AK CEN!\"

       sFileName = sURLFolder & oCell.getString() & "sqlcenyaukcje1.csv"
       CreateCSVFile(oSheet, 1,19,99,21, sFileName)
       
       sFileName = sURLFolder & oCell.getString() & "sqlcenyaukcje2.csv"
       CreateCSVFile(oSheet, 1,19,99,21, sFileName) rem WPISZ INNY ZAKRES
    End Sub

Testowałem zapis tylko zakomentowaną linijką (tą "kuku.txt").

Poza tym, WAŻNE:
taki sposób otwarcia pliku nie usuwa poprzedniej zawartości tylko po niej pisze.
Więc jeśli plik wynikowy csv uprzednio istniał, to jego zawartość stanie się mieszanką, w której sekcja początkowa pochodzi z nowego zapisu, ale jeśli poprzedni był dłuższy, to będzie spod niego 'wystawał' na końcu.
Uwaga powyższa wynika z przeprowadzonych eksperymentów. Nie mam wiele do czynienia z Basicem, ale zachowanie takie było dla mnie nieoczekiwane.

[Jermor]

U mnie zapisywanie działa poprawnie. Nie pojawia się nadpisywanie, tylko powstaje nowy plik.
Aby dopisywać nowe dane do istniejącego pliku w instrukcji OPEN należy użyć parametru FOR APPEND zamiast FOR OUTPUT.
No i trzeba pamiętać o usunięciu poprzedniej wersji pliku przed uruchomieniem makra. Można to zapisać w samym makro wykorzystując instrukcję KILL (z wykorzystaniem funkcji FILEEXIST do sprawdzenia czy plik istnieje).

[Jan_J]

Ad nieoczekiwane zachowanie
Nie jest to APPEND, tylko jakby dostęp READ+WRITE: `open filename for output as #n` nie usuwa zawartości otwieranego pliku, ustawia głowicę na jego początku. Dane zapisują się w trybie overwrite.

Grzebałem w dokumentacji Basica dla Libre i nie znalazłem wyjaśnienia.

Sprawdzałem na Linux SuSE Tumbleweed + firmowy LibreOffice 5.x, na razie tylko tam.

No i trzeba pamiętać o usunięciu poprzedniej wersji pliku przed uruchomieniem makra.

No właśnie.

[Jermor]

Starałem się posprawdzać, tak na szybko działanie.
"Nieoczekiwane zachowanie" o jakim piszesz pojawia się, (przynajmniej u mnie, LibreOffice w wersji dla Windows) tylko gdy plik jest otwarty jako RANDOM.

[kris86k]

Nie jest pewnie piękne ale działa. Kod poniżej. Bardzo dziękuję za pomoc. Odpisuję dopiero dziś bo nie dostałem powiadomienia o odpowiedzi.
Mam ostatnie pytanie czy da się z poziomu makra lub w inny sposób wykonać plik *.cmd lub kod w nim zawarty?

Kod: Zaznacz cały

Sub EU
   Dim sURLFolder as String
   Dim sFileName As String
   Dim i As Integer
   Dim n As Integer
   Dim oSheet
   Dim oCell
   Dim oRange
   Dim CellContentArray
   
   REM Get the active sheet object
   oSheet = ThisComponent.getCurrentController.ActiveSheet
   
   REM Get the cell contents of U2 (column 20, row 1) to U3 (column 20, row 2)
   oRange = oSheet.getCellRangeByPosition(20,1,20,99)
   CellContentArray = oRange.getDataArray()
      
   REM Get the filename from cell M1 (column 12, row 0) of the active sheet
   oCell = oSheet.getCellByPosition(1002,0)
   
   REM Get the folder name this file is saved in and append the filename
   sURLFolder="D:\Dropbox\EU csv\AK CEN!\"
   sFileName = sURLFolder & oCell.getString() & "EU.SQL"
   
   REM Create a new file (if this filename exists, it will be deleted and replaced)
   n = FreeFile()
   Open sFileName For Output As #n
   
   REM Write the contents of the data array to the file
   REM This assumes all rows are six cells wide
   For i = 0 To UBound(CellContentArray)
   IF oSheet.getCellByPosition(0,i+1).getValue() > 0 Then
      Print #n, CellContentArray(i)(0)
     Endif   
   Next
   
   REM Close the file
   Close #n



   
   REM Get the active sheet object
   oSheet = ThisComponent.getCurrentController.ActiveSheet
   
   REM Get the cell contents of U2 (column 20, row 1) to U3 (column 20, row 2)
   oRange = oSheet.getCellRangeByPosition(19,1,19,99)
   CellContentArray = oRange.getDataArray()
      
   REM Get the filename from cell M1 (column 12, row 0) of the active sheet
   oCell = oSheet.getCellByPosition(1002,0)
   
   REM Get the folder name this file is saved in and append the filename
   sURLFolder="D:\Dropbox\EU csv\AK CEN!\"
   sFileName = sURLFolder & oCell.getString() & "MAG.SQL"
   
   REM Create a new file (if this filename exists, it will be deleted and replaced)
   n = FreeFile()
   Open sFileName For Output As #n
   
   REM Write the contents of the data array to the file
   REM This assumes all rows are six cells wide
   For i = 0 To UBound(CellContentArray)
   IF oSheet.getCellByPosition(0,i+1).getValue() > 0 Then
      Print #n, CellContentArray(i)(0)
     Endif   
   Next
   
   REM Close the file
   Close #n

End Sub

[Jan_J]

shell(polecenie)

Efektem ubocznym jest wykonanie polecenia ze wszystkimi jego skutkami.
Wynikiem jest kod zakończenia procesu, a w przypadku wykonania asynchronicznego kod procesu inicjującego to wykonanie. Zazwyczaj 0 znaczy że się udało.
Standardowe wyjście polecenia jest przekierowane do nul ew. /dev/null. Tak że nie licz na to, że zobaczysz wydruk terminalowy. Chyba że odpalisz cmd, a w nim plik.cmd.

[kris86k]

Dlaczego to nie działa? "Błąd, nie znaleziono pliku".
Próbowałem różnych przykładów z innych makr ale nic to nie daje . Albo nic się nie dzieje albo wyrzuca błąd.

Kod: Zaznacz cały

Sub Main

shell ("D:\Dropbox\EU csv\AK CEN!\AKTUALIZACJA.CMD")


End Sub

[Jan_J]

Ma działać.

Biorąc na rozum, parametr przekazany do shell jest kompletnym wierszem poleceń. Twój system dostaje do realizacji polecenie

Kod: Zaznacz cały

D:\Dropbox\EU csv\AK CEN!\AKTUALIZACJA.CMD

co sprowadza się do próby uruchomienia procesu z pliku

Kod: Zaznacz cały

D:\Dropbox\EU

i przekazania mu dwóch parametrów:

Kod: Zaznacz cały

csv\AK

oraz

Kod: Zaznacz cały

CEN!\AKTUALIZACJA.CMD

Jasne jest, że nie tędy idzie Twoja myśl. Winne są spacje w ścieżkach, zło wcielone.

Zabezpieczenia przeciw spacjom w komendach są dwa: albo (a) ochrona polecenia przez cudzysłowy, albo (b) ochrona spacji przez znak sterujący.

Poprawnymi poleceniami dla Windows byłyby:
wg (a) "D:\Dropbox\EU csv\AK CEN!\AKTUALIZACJA.CMD"
wg (b) D:\Dropbox\EU^ csv\AK^ CEN!\AKTUALIZACJA.CMD

Cudzysłowy które umieściłeś w kodzie są ogranicznikami stałej tekstowej w Basicu. Nie wchodzą w skład napisu, więc nie spełniają żadnej roli w samym poleceniu. Wewnątrz Basic-owego stringa znak " koduje się za pomocą "" -- coś w rodzaju symbolu sterującego. Więc zaklęcie w Basicu

Kod: Zaznacz cały

shell("""D:\Dropbox\EU csv\AK CEN!\AKTUALIZACJA.CMD""")

winno spełnić Twoje oczekiwania. Możliwość z symbole ^ też jest do pomyślenia, z tym że użytkownik Windowsa może skonfigurować inny symbol jako sterujący w wierszu poleceń. Więc będzie to rozwiązanie teoretycznie bardziej zawodne.

Nb., kiedy uruchamiam komendę shell spod Basica w linuksie, cytowanie przez """komenda ze spacjami""" działa, natomiast wypunktowanie spacji symbolem sterującym (w tym przypadku jest to \ a nie ^) nie działa. Wiem nawet dlaczego, ale nie umiem powiedzieć na pamięć jak to będzie w cmd.

[kris86k]

Dziękuję za dogłębne wyjaśnienie tematu.

Nie wyrzuca błędu ale też nic się nie dzieje po uruchomieniu marka.

Kod: Zaznacz cały

Sub Main

shell("""D:\Dropbox\EU csv\AK CEN!\AKTUALIZACJA.CMD""")

End Sub

[Jan_J]

Więc OK, bo co się ma dziać? pisałem, że okienko się nie odpali, wszystko dzieje się po cichu.
Wpisz w makrze

Kod: Zaznacz cały

w = shell(komenda)

i zbadaj wartość w. Jeśli 0, tzn. się udało.
Sprawdź skutki wykonania: jakiś plik w systemie, wpis w bazie czy co tam.

[kris86k]

No właśnie ma się uruchomić plik w cmd który wykonuje zadnia na bazie danych i na końcu utworzyć plik. Po ręcznym uruchomieniu wszystko działa. Po uruchomieniu makra plik nie jest wykonywany. Link skopiowany, nie przepisywany. Zresztą po uruchomieniu ręcznym tego linku działa więc błędu w pisowni nie ma.

[Jan_J]

Spróbowałem zrobić to samo pod Windows 10. Skrypt uruchamia się jeśli w jego ścieżce nie ma spacji. W przypadku spacji, pod Windows, podane przeze mnie sposoby nie są skuteczne. W linuksie były OK; podobnie są one OK w wierszu poleceń Windows, cmd.exe.

Andrew Pitonyak (http://www.pitonyak.org/OOME_3_0.pdf) w rozdziale 9.2.4 dyskutuje ten problem i podaje te same sposoby co ja, plus jeszcze jeden dodatkowy z kodowaniem URL (sekwencje szesnastkowe, np. %20 zamiast spacji). W układzie LibreOffice + Windows 10 żadna nie działa.

Nie mam czasu szukać rozwiązania, zwłaszcza że te, które znam, _powinny_ działać. Dużo prościej jest zadbać o dyscyplinę (unikać spacji i liter niełacińskich w nazwach plików i katalogów), niż zastanawiać się jak ominąć ograniczenia i niekonsekwencje oprogramowania.

Aha: w Windows odpalony przez makro proces cmd dostaje przydzielone okno na ekranie. Czyli domyślnie widać, że się uruchomił. Stan tego okna da się kontrolować drugim, opcjonalnym parametrem funkcji shell.

[kris86k]

Dzięki za wyjaśnienie. Utworzyłem plik CMD bezpośrednio na dysku D z linkiem do pliku i działa.