Exportation DB vers Excel

Version imprimable

12/06/2007, 19h41
thierry007

Exportation DB vers Excel

qq aurait un bout de code qui exporte des données d'une DB access vers Excel , sa m'arangerait dans mon petit module 8O

Salut

J'ai fait ça dernièrement avec des tables SQL en passant par un fichier csv. Je fait ma requète remontant les données à exporter et j'écris le résultat dans un fichier texte au format csv. Après plus qu'à ouvrir le csv avec Excel

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
Public Class ExportData
 
        Private _pathExport As String
        Private _TableID As String
        Private _debut As Date
        Private _fin As Date
        Private _NbTable As Integer
        Private _form As Form1
 
        Public Sub New(ByVal TableID As String, ByVal NbTable As Integer, ByVal Mois As Integer, ByVal Annee As Integer, ByVal pathExport As String, ByVal myForm As Form1)
            _form = myForm
            My.Computer.FileSystem.CreateDirectory(TableID)
 
            _TableID = TableID
            _NbTable = NbTable
            _debut = Date.Parse("01/" & Mois.ToString("00") & "/" & Annee.ToString)
            _fin = _debut.AddMonths(1)
            _pathExport = pathExport
 
        End Sub
 
        Public Sub Execute()
 
            _form.Invoke(_form.MajForm, _TableID, "En cours...")
            Dim myConn As New SqlConnection(My.Settings.ConnectionString)
            myConn.Open()
 
            Dim i As Integer
 
            For i = 1 To _NbTable
 
                Dim myCommande As New SqlCommand("Select * From " & _TableID & i & " Where timestamp >= '" & _debut.ToString & "' AND timestamp < '" & _fin.ToString & "'", myConn)
                'Dim myCommande As New SqlCommand("Select TOP 45000 * From " & _TableID & i, myConn)
                Dim myReader As SqlDataReader
                myReader = myCommande.ExecuteReader
 
                Dim j As Integer = 0
                Dim strToWrite As String = ""
 
                For j = 0 To myReader.FieldCount - 1
                    If j <> myReader.FieldCount - 1 Then
                        strToWrite = strToWrite & myReader.GetName(j).ToString & ","
                    Else
                        strToWrite = strToWrite & myReader.GetName(j).ToString & vbCrLf
                    End If
                Next
 
                Dim sw As New StreamWriter(_pathExport & "\" & _TableID & "\" & _TableID & i & ".csv", False, System.Text.Encoding.Default)
                sw.Write(strToWrite)
 
                Dim count As Integer = 0
                Dim result As Integer
                Dim values(248) As Object
 
                While myReader.Read
                    result = myReader.GetValues(values)
                    strToWrite = ConvertToString(values, result)
                    strToWrite = strToWrite & vbCrLf
                    sw.Write(strToWrite)
                End While
 
                sw.Close()
                myReader.Close()
                myCommande = Nothing
                myReader = Nothing
 
            Next
 
            myConn.Close()
            _form.Invoke(_form.MajForm, _TableID, "Terminé")
        End Sub
 
        Private Function ConvertToString(ByVal objets As Object(), ByVal nbValues As Integer) As String
            Dim strCopy(nbValues - 1) As String
            Dim i As Integer = 0
            For i = 0 To nbValues - 1
                If IsDBNull(objets(i)) Then
                    strCopy.SetValue("NULL", i)
                Else
                    strCopy.SetValue(objets(i).ToString(), i)
                End If
            Next
            Return String.Join(",", strCopy)
        End Function
    End Class

Si ça peut t'aider

c'est ce que je fait pour l'instant , il y a surement moyen d'ouvrir l'application excel et de faire un link vers les cellules
je le fesait en VB6 voir le code ci-dessous

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
Dim i As Integer
Dim Couche As String
Dim Rep As Long
 
 If Dir("C:\Program files\Microsoft Office\Office11\Excel.exe") = "" Then
  MsgBox "Impossible de localiser le programme Excel", vbCritical
  Exit Function
 End If
 
 If Text2.LinkMode = 0 Then
  Rep = Shell("C:\Program files\Microsoft Office\Office10\Excel", 4)
  Text2.LinkTopic = "Excel|Classeur1"
  Text2.LinkItem = "L1C1"
  Text2.LinkMode = 1
 End If
 
 
 Data1.RecordSource = "SELECT * FROM ARTICLES ORDER BY NumeroArticle"
 Data1.Refresh
 
 Data1.Recordset.MoveFirst
 Do While Not Data1.Recordset.EOF
  i = i + 1
 
  Text2.LinkItem = "L" & i & "C1"
  Text2.Text = Data1.Recordset.NumeroArticle
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C2"
  Text2.Text = Data1.Recordset.CodeFournisseur
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C3"
  Text2.Text = Data1.Recordset.CodeFabricant
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C4"
  Text2.Text = Data1.Recordset.Denomination
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C5"
  Text2.Text = Data1.Recordset.CompteAchat
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C6"
  Text2.Text = Data1.Recordset.CompteVente
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C7"
  Text2.Text = Data1.Recordset.PrixAchat
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C8"
  Text2.Text = Data1.Recordset.PrixVente
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C9"
  Text2.Text = "4"
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C10"
  Text2.Text = "o"
  Text2.LinkPoke
  Text2.LinkItem = "L" & i & "C11"
  Text2.Text = Data1.Recordset.BarreCode
  Text2.LinkPoke
  Data1.Recordset.MoveNext
  On Error Resume Next
 Loop
 
 MsgBox "Exportation vers Excel terminé. . .", vbInformation

14/06/2007, 13h48
Angath

J'ai trouver ça dans la FAQ VB.NET :

http://faqvbnet.developpez.com/?page...xcel_transfert

Ca ressemble à ce que tu veut faire ;)