Abfrage Ergebnis mittels Makro in Calc Zellen schreiben

[sg1985]

Hallo,

ich habe in einem Makro eine Select Abfrage, welche mehrere Datensätzen mit mehreren Spalten als Ergebnis liefert.

zB: die Spalten sind (Projektname, Sollstunden, Iststunden):
Projekt1 4:00 7:00
Projekt2 5:00 10:00
Projekt3 17:00 30:00
usw.

Nun möchte ich diese Datensätze in ein Calc Sheet schreiben (in die einzelnen Zellen), wobei noch wichtig ist, dass ich beispielsweise erst in der
Zelle A8 zu schreiben beginne. Weiters sollen die mittels Select gewählten Spaltennamen der Tabelle auch im Calc Sheet stehen.

Kann mir bitte jemand sagen, wie das geht?

lg

[komma4]

was hast Du von den bisher erteilten Antworten nicht verstanden?

Hast Du Dir mal Andrews Makro Dokument angeschaut?

[sg1985]

im Forum habe ich nichts dazu gefunden und ich vermute das Macro, das ich brauchen würde verbirgt sich unter "Database Macros" auf deinem Link - wenn ich dieses Dokument im OO öffne und UNICODE (habs auch mit anderen Zeichensätzen probiert) als Zeichensatz wähle, erhalte ich aber lauter komische Zeichen und kann nichts entziffern.

Könntest du mir den benötigten Macro Code fürs Speichern des Ergebnisses in bestimmte Zellen vielleicht hier posten?

[komma4]

Andrew's Dokument findet sich sich (im *.odt-Format) genau hinter dem Text "OpenOffice.org Macro document": http://www.pitonyak.org/AndrewMacro.odt

Darin finden sich auch Beispiele, wie Zellen bearbeitet werden (auch hier im Forum habe ich schon mehrfach Beispiele gesehen)

... und, wie ich schon schrieb, für VIELE Zellen ist das Nutzen eines Datenarrays angesagt.
Ein Beispiel dafür habe ich in Deinem posting "Datenbankverbindung per Makro" viewtopic.php?f=8&t=18752 geliefert, ein anderes findest Du in meiner Extension OOoDIL Dokumenten Info Liste


Deshalb noch mal die Frage: was verstehst Du nicht?

[sg1985]

danke für deine Geduld,

Andrew's Dokument findet sich sich (im *.odt-Format) genau hinter dem Text "OpenOffice.org Macro document": http://www.pitonyak.org/AndrewMacro.odt

wenn ich auf diesen Link klicke oder das Dokument herunterlade und dann im OO öffnen will, erhalte ich aber eben diese kryptischen Zeichen... keine Ahnung woran das liegt.

Ich werde mir das was du mir im anderen Posting geschrieben hast, noch mal genau anschauen und sag dir dann was ich nicht versteh

[komma4]

Der Link zu Andrews Dokument funktioniert hier - ich kann das Dokument auch direkt aus Firefox in OOo öffnen.

Hinterlasse mir Deine eMail-Adresse per PM Schaltfläche in meinem Profil, und ich schicke Dir eine Kopie zu.

Viel Erfolg!

[sg1985]

So, nun hänge ich leider bei deinem Makro, das du mir hier viewtopic.php?f=8&t=18752 gepostet hast.

Es gibt mir zwar die Spaltennamen die im Select Befehl stehen aus, jedoch nicht den dazu gehörigen Inhalt des Ergebnisses.

Das Makro schaut momentan folgendermaßen aus:

Code: Alles auswählen

sub Abfrage

' Datenbankverbindung
' Kontext holen
oDatenbankKontext = CreateUnoService ( "com.sun.star.sdb.DatabaseContext" )
' Datenquelle auswählen
' Name der registrierten Datenquelle
oDatenquelle = oDatenbankKontext.getByName( "db_test3" )

'
oDatenquelle.setLoginTimeout( 10 )
' Verbindung zur DB herstellen
'
If Not oDatenquelle.IsPasswordRequired Then
   oVerbindung = oDatenquelle.getConnection("postgres","")
Else
   oInteractionHandler = createUnoService( "com.sun.star.sdb.InteractionHandler" )
   oVerbindung = oDatenquelle.connectWithCompletion( oInteractionHandler )
End If

' Verbindung nicht geklappt
If IsNull( oVerbindung ) Then
   msgbox "keine Verbindung zur DB (user/passwort falsch?)"
   exit sub
End If

Dim oForm as Object, oList as Object, oList1 as Object, oList2 as Object
Dim oListCtr as Object, oTextCtr as String, oTextCtr1 as String, oTextCtr2 as String, oPosCtr as String
  
oForm = thisComponent.sheets(0).Drawpage.Forms.getByName("Standard") 
oList = oForm.getByName("ListBox")
oListCtr = ThisComponent.CurrentController.GetControl(oList)
oTextCtr = oListCtr.SelectedItem
'oPosCtr = oListCtr.SelectedItempos

oList1 = oForm.getByName("ListBox1")
oListCtr = ThisComponent.CurrentController.GetControl(oList1)
oTextCtr1 = oListCtr.SelectedItem
oList2 = oForm.getByName("ListBox2")
oListCtr = ThisComponent.CurrentController.GetControl(oList2)
oTextCtr2 = oListCtr.SelectedItem

'Abfrage
Dim s as String, t as String
sSQL = "SELECT p_start, p_end from project where p_name ='"+oTextCtr1+"';"
	
oStatement = oVerbindung.createStatement
oResultSet = oStatement.executeQuery (sSQL)

' ***************************************************
oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.ActiveConnection = oVerbindung
oRowSet.setPropertyValue("Command", sSQL)
oRowSet.execute()

' wenn ungleich 0: Anzahl feststellen
If oRowSet.getPropertyValue("RowCount")  Then

' Drew Jensen Sat Aug 19, 2006 4:49 pm
' http://www.oooforum.org/forum/viewtopic.phtml?t=41470
   oRowSet.Next
    bkMark = oRowSet.getBookMark()
    oRowSet.Last
    oRowSet.moveToBookmark( bkMark )
Else
   Msgbox "Keine Zeilen erhalten  - beende das Makro"
   Exit Sub
End If

' Zeilen- und Spaltenanzahl
iAnzahlZeilen = oRowSet.getPropertyValue( "RowCount" ) 
iAnzahlSpalten = oRowSet.Columns.getCount()

' __________________________________________________________________
' neues Calc Dokument
' im Hintergrund erstellen
' Bereich zum Füllen holen
oDeskNeu = createUnoService( "com.sun.star.frame.Desktop" )

Dim OpenProperties(1) as new com.sun.star.beans.PropertyValue
OpenProperties(0).Name = "Hidden"
OpenProperties(0).Value = True

sNeuURL = "private:factory/scalc"
oNeuDok = oDeskNeu.loadComponentFromURL( sNeuURL, "_blank" , 0 , OpenProperties() )
oBlatt = oNeuDok.Sheets.getByIndex(0)

oBereich = oBlatt.getCellRangeByPosition( 0, 0, iAnzahlSpalten - 1 , iAnzahlZeilen )
' __________________________________________________________________

ReDim aDatenzeile( iAnzahlSpalten - 1 )

oDaten = oBereich.getDataArray()

' Spaltenbeschriftung ermitteln
for i = 0 to oRowSet.getColumns().getCount() - 1
aDatenZeile( i ) = oRowSet.getColumns().getByIndex( i ).Name
next i
' Spaltenüberschrift setzen
oDaten(0) = aDatenZeile()

ReDim aSpaltenTyp( iAnzahlSpalten - 1 ) as String
' Spaltentypen ermitteln
for i = 0 to oRowSet.getColumns.getCount() - 1
   aSpaltenTyp( i ) =  oRowSet.getColumns.getByIndex( i ).TypeName
next

' Datenzeilen besetzen
for zz = _
   1  to _
   UBound( oDaten() ) 

   aDatenZeile() = oDaten( zz )
   ' zz: ZählerZeilen

   ' Spalten-/Zellen-Zähler
   for zc = _
      0 to _
      iAnzahlSpalten - 1
      
'
      Select Case aSpaltenTyp( zc )
      	 Case "NUMBER", "INTEGER"
                aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
         Case "VARCHAR2" , "VARCHAR"
         Case "NUMBER"
            aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
         Case "VARCHAR2"
            aDatenZeile( zc ) = oRowSet.getString( zc + 1 )
         CASE "DATE"            
            oDatum = _
               oRowSet.getTimestamp( zc + 1 )
            sDatum = _
               oDatum.Year & _
               "-" & _
               Format( oDatum.Month, "00" ) & _
               "-" & _
               Format( oDatum.Day, "00" ) & _
               " " & _
               Format( oDatum.Hours, "00" ) & _
               ":" & _
               Format( oDatum.Minutes, "00" ) & _
               ":" & _
               Format( oDatum.Seconds, "00" )
'            print "a"
'            aDatenZeile( zc ) = oRowSet.getDate( zc + 1 )
            aDatenZeile( zc ) = oRowSet.getString( zc + 1 )         
      End Select

         next ' Zellen

'   xray aDatenZeile()

   '
   oRowSet.next()
next ' Zeile

' Daten setzen
a = oResultSet.getString(1)
b = oResultSet.getString(2)
oDaten = (a,"b")
oBereich.setDataArray( oDaten() )
' __________________________________________________________________

' Dokument anzeigen
oNeuDok.getCurrentController().getFrame().getContainerWindow().Visible =  TRUE

End Sub

Weiters schaffe ich es nicht, dass es mir kein neues Dokument öffnet, sondern das Ergebnis der Abfrage ins gleiche Formular schreibt (Name "Standard")
wo auch die Listbox ist und ein Eintrag selektiert wird.

Bitte um Hilfe

[komma4]

Die Ergebniszeilen Deiner Abfrage werden auf den Datentyp untersucht. Da kann --von verwendeter Datenbank-engine und je nach Tabellendefinition-- unterschiedlich und abweichend zum vorliegenden Code andere Datentypen zurückgeliefert werden.

Untersuche mit Xray die Datentypen zum Zeitpunkt des Schreibens (dass die Abfrage prinzipiell mal funktioniert zeigt das Setzen der Spaltenüberschriften):

Code: Alles auswählen

' Test der Rückgabewerte mit Xray
      xray aSpaltenTyp( zc )
      Select Case aSpaltenTyp( zc )

Zum Setzen einer Zelle kannst Du bspw. folgenden Code nutzen:

Code: Alles auswählen

oDokument = StarDesktop.ActiveFrame.Controller.Model
oBlatt = oDokument.CurrentController.getActiveSheet()
oZelle = oBlatt.getCellByPosition( 0, 0 )  ' a1
oZelle.setString( "mein Zell-Textinhalt" )
oZelle2 = oBlatt.getCellByPosition( 1, 3 )  ' c2
oZelle2.setValue( 39654 )  ' Datum heute, 2008-07-25

Viele Codebeispiele bei Andrew! Wenn Dein Download nicht klappt: schicke mir per PM eine eMail-Adresse von Dir, dann bekommst Du eine Kopie.


Hilft das weiter?

[sg1985]

ich habe dir schon eine PN geschickt heute in der Früh

[tunix_xl]

@sg1984

Ich habe den Code von Komma4 ausprobiert. Wenn du als Datenbank mysql verwendest, probiere mal Kleinschreibung an dieser Stelle:

Code: Alles auswählen

 Select Case aSpaltenTyp( zc )
             Case "int", "smallint"
                aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
             Case  "varchar"
                aDatenZeile( zc ) = oRowSet.getString( zc + 1 )
             CASE "date"            
         

Die Cases "int", "smallint" und "date" sowie "varchar" sollten so funktionieren.

[sg1985]

Ich hab das mit der Kleinschreibung probiert, bringt leider auch nichts.
Ich suche nun schon ewig, finde den Fehler aber einfach nicht

Die Spaltenüberschriften der SQL Abfrage werden zwar auf das calc Sheet geschrieben, aber keine dazugehörigen Datensätze.

Mein Makro schaut momentan folgendermaßen aus:

Code: Alles auswählen

sub Abfrage

' Datenbankverbindung
' Kontext holen
oDatenbankKontext = CreateUnoService ( "com.sun.star.sdb.DatabaseContext" )
' Datenquelle auswählen
' Name der registrierten Datenquelle
oDatenquelle = oDatenbankKontext.getByName( "db_test3" )

'
oDatenquelle.setLoginTimeout( 10 )
' Verbindung zur DB herstellen
'
If Not oDatenquelle.IsPasswordRequired Then
   oVerbindung = oDatenquelle.getConnection("","")
Else
   oInteractionHandler = createUnoService( "com.sun.star.sdb.InteractionHandler" )
   oVerbindung = oDatenquelle.connectWithCompletion( oInteractionHandler )
End If

' Verbindung nicht geklappt
If IsNull( oVerbindung ) Then
   msgbox "keine Verbindung zur DB (user/passwort falsch?)"
   exit sub
End If

Dim oForm as Object, oList as Object, oList1 as Object, oList2 as Object
Dim oListCtr as Object, oTextCtr as String, oTextCtr1 as String, oTextCtr2 as String, oPosCtr as String
  
oForm = thisComponent.sheets(0).Drawpage.Forms.getByName("Standard") 
oList = oForm.getByName("ListBox")
oListCtr = ThisComponent.CurrentController.GetControl(oList)
oTextCtr = oListCtr.SelectedItem
'oPosCtr = oListCtr.SelectedItempos

oList1 = oForm.getByName("ListBox1")
oListCtr = ThisComponent.CurrentController.GetControl(oList1)
oTextCtr1 = oListCtr.SelectedItem
oList2 = oForm.getByName("ListBox2")
oListCtr = ThisComponent.CurrentController.GetControl(oList2)
oTextCtr2 = oListCtr.SelectedItem

'Abfrage
Dim s as String, t as String
sSQL = "SELECT p_start, p_end from project where p_name ='"+oTextCtr1+"';"
sSQL1 = "SELECT t_name AS Taskname, t_target_hours AS Sollstunden, SUM(EXTRACT(epoch FROM ts_duration))/3600 AS Iststunden FROM project, task, timeslot, staff, allocations WHERE (s_id = a_s_id AND a_t_id = t_id AND a_id = ts_a_id AND t_p_id = p_id AND p_name='"+oTextCtr1+"' AND SUBSTR(TO_CHAR(ts_start, 'YYYY-MM-DD HH:MM'),1,4)='"+oTextCtr2+"' ) GROUP BY t_name, Sollstunden ORDER BY Taskname ASC;"
sSQL2 = "SELECT DISTINCT s_lname || ' ' || s_fname as Name from staff, project, allocations, task, timeslot WHERE (s_id = a_s_id AND a_t_id = t_id AND t_p_id = p_id and p_name='"+oTextCtr1+"') 	"
		
oStatement = oVerbindung.createStatement
oStatement1 = oVerbindung.createStatement
oStatement2 = oVerbindung.createStatement
oResultSet = oStatement.executeQuery (sSQL)
oResultSet1 = oStatement1.executeQuery (sSQL1)
oResultSet2 = oStatement2.executeQuery(sSQL2)

if oResultSet.Next() then
	p_start = oResultSet.getString(1) 'p_start
	p_end = oResultSet.getString(2)
end if

While oResultSet1.Next()
   t_name = oResultSet1.getString(1) 't_name
   soll_h = oResultSet1.getString(2) 'SollStunden
   ist_h = oResultSet1.getString(3) 'Iststunden
   'datensaetze = oResultSet1.getString(4) 'Anzahl der Datensaetze
Wend

While oResultSet2.Next()
   names = oResultSet2.getString(1) 's_lname + s_fname
   'msgBox names
Wend

oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.ActiveConnection = oVerbindung
oRowSet.setPropertyValue("Command", sSQL1)
oRowSet.execute()

' wenn ungleich 0: Anzahl feststellen
If oRowSet.getPropertyValue("RowCount")  Then
    oRowSet.Next
    bkMark = oRowSet.getBookMark()
    oRowSet.Last
    oRowSet.moveToBookmark( bkMark )
Else
    Msgbox "Keine Zeilen erhalten  - Abfrage wurde beendet"
    Exit Sub
End If

' Zeilen- und Spaltenanzahl
iAnzahlZeilen = oRowSet.getPropertyValue("RowCount") 
iAnzahlSpalten = oRowSet.Columns.getCount()

' neues Calc Dokument
' im Hintergrund erstellen
' Bereich zum Füllen holen

oDeskNeu = createUnoService("com.sun.star.frame.Desktop")

Dim OpenProperties(1) as new com.sun.star.beans.PropertyValue
OpenProperties(0).Name = "Hidden"
OpenProperties(0).Value = True

sNeuURL = "private:factory/scalc"
'oNeuDok = oDeskNeu.loadComponentFromURL( sNeuURL, "_blank" , 0 , OpenProperties()) 
oBlatt = ThisComponent.Sheets.getByIndex(0)
oBereich = oBlatt.getCellRangeByName("G1:I1")'Position( 0, 0, iAnzahlSpalten - 1 , iAnzahlZeilen )

oZelle_pstart = oBlatt.getCellRangeByName("C8")
oZelle_pend = oBlatt.getCellRangeByName("C9")
oZelle_pstart.String = p_start
'oZelle_pstart.clearContents(23) ' Inhalt von Zelle loeschen
oZelle_pend.String = p_end
'oZelle_names = oBlatt.getCellRangeByName("C12")
'oZelle_names.String = names

ReDim aDatenzeile( iAnzahlSpalten - 1 )

oDaten = oBereich.getDataArray()

' Spaltenbeschriftung ermitteln
for i = 0 to oRowSet.getColumns().getCount() - 1
aDatenZeile( i ) = oRowSet.getColumns().getByIndex( i ).Name
next i
' Spaltenüberschrift setzen
oDaten(0) = aDatenZeile()

ReDim aSpaltenTyp( iAnzahlSpalten - 1 ) as String
' Spaltentypen ermitteln
for i = 0 to oRowSet.getColumns.getCount() - 1
   aSpaltenTyp( i ) =  oRowSet.getColumns.getByIndex( i ).TypeName
next

' Datenzeilen besetzen
for zz = 1 to UBound( oDaten() ) 

   aDatenZeile() = oDaten( zz )
   ' zz: ZählerZeilen

   ' Spalten-/Zellen-Zähler
   for zc = 0 to iAnzahlSpalten - 1
      
      Select Case aSpaltenTyp( zc )
      	 Case "number", "integer"
                aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
         Case "varchar2" , "varchar"
         Case "number"
            aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
         Case "varchar2"
            aDatenZeile( zc ) = oRowSet.getString( zc + 1 )
         CASE "date"            
            oDatum = oRowSet.getTimestamp( zc + 1 )
            sDatum = oDatum.Year & _
               "-" & _
               Format( oDatum.Month, "00" ) & _
               "-" & _
               Format( oDatum.Day, "00" ) & _
               " " & _
               Format( oDatum.Hours, "00" ) & _
               ":" & _
               Format( oDatum.Minutes, "00" ) & _
               ":" & _
               Format( oDatum.Seconds, "00" )
'            print "a"
'            aDatenZeile( zc ) = oRowSet.getDate( zc + 1 )
            aDatenZeile( zc ) = oRowSet.getString( zc + 1 )         
      End Select
      
   next ' Zellen

'   xray aDatenZeile()

   '
   oRowSet.next()
next ' Zeile

' Daten setzen
oBereich.setDataArray(oDaten())

' Dokument anzeigen
'oNeuDok.getCurrentController().getFrame().getContainerWindow().Visible =  TRUE

End Sub
[/cod

Es betrifft das SQL Statement sSQL1, es werden eben die Spaltenüberschriften Taskname, Sollstunden, Iststunden ausgegeben, jedoch keine Datensätze.
An der SQL Abfrage kanns nicht liegen, die habe ich bereits anders getestet und sie gibt das richtige Ergebnis aus.

Hat jemand einen Tipp für mich was da falsch sein könnte?

[komma4]

sg1985,
Werden wirklich Datensätze gefunden?

Code: Alles auswählen

' Zeilen- und Spaltenanzahl
iAnzahlZeilen = oRowSet.getPropertyValue("RowCount") 
msgbox "Anzahl Treffer-Zeilen: " & iAnzahlZeilen 

und welche Spaltentypen werden erkannt?

Code: Alles auswählen

' Spaltentypen ermitteln
for i = 0 to oRowSet.getColumns.getCount() - 1
   aSpaltenTyp( i ) =  oRowSet.getColumns.getByIndex( i ).TypeName
   msgbox "bei Spalte mit Index " & i & " wurde der Typ erkannt: " & aSpaltenTyp( i )
next

[sg1985]

Hallo Winfried,

ja, es werden Datensätze gefunden (3 zum Beispiel)...

und es werden auch die Typen erkannt (in Spalte mit Index 0 --> varchar, in Spalte mit Index 1 --> int4, in Spalte mit Index 2 --> float8)

Es muss also irgendetwas mit der Ausgabe zu tun haben bzw. mit der Belegung von oBereich oder?
Oder vielleicht passt irgendetwas mit oDaten bzw. aDatenzeile nicht?!

[komma4]

...Dir fehlen die Auswertungen der Spaltentypen int4 und float8 in der
Select Case aSpaltenTyp( zc ) Anweisung.

Und beim Typ varchar fehlt der Übernahme-Code.... (.getString( zc + 1) )

Habe heute wenig Zeit zum Testen....: schau' Dir mit xray die möglichen Methoden beim oRowSet an (.getFloat()) ?

[sg1985]

ich hab die Spalte mit dem float8 Typ mal weg gelassen und das was in der select Anweisung noch gefehlt hat dazu geschrieben.

Schaut nun so aus:

Code: Alles auswählen

 Select Case aSpaltenTyp( zc )
      	 Case "number", "integer"
                aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
         Case "int4"
         		aDatenZeile ( zc ) = oRowSet.getInt ( zc + 1 )
         Case "varchar"
                aDatenZeile ( zc ) = oRowSet.getString ( zc + 1 )
         Case "number"
            aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
         Case "varchar2"
            aDatenZeile( zc ) = oRowSet.getString( zc + 1 )
         CASE "date"            
            oDatum = oRowSet.getTimestamp( zc + 1 )
            sDatum = oDatum.Year & _
               "-" & _
               Format( oDatum.Month, "00" ) & _
               "-" & _
               Format( oDatum.Day, "00" ) & _
               " " & _
               Format( oDatum.Hours, "00" ) & _
               ":" & _
               Format( oDatum.Minutes, "00" ) & _
               ":" & _
               Format( oDatum.Seconds, "00" )
'            print "a"
'            aDatenZeile( zc ) = oRowSet.getDate( zc + 1 )
            aDatenZeile( zc ) = oRowSet.getString( zc + 1 )         
      End Select

leider funktioniert das noch immer nicht. Mir kommt es komisch vor, dass man in den einzelnen Case Anweisungen das Ergebnis in die
Variable aDatenZeile speichert und später die Daten mit dem Befehl "oBereich.setDataArray(oDaten())" setzt.
In oDaten steht doch gar nicht das gewünschte drinnen oder? (außer in oDaten(0), hier stehen ja die Spaltenüberschriften)

oder holt man mit "oDaten = oBereich.getDataArray()" möglicherweise die falschen Daten und nicht die der Anweisung sSQL1?