Discussion :

[Dullbrain]

Bonjour,

je fais face à un petit problème. Je souhaite exporter les données contenues dans une table contenant 13 millions de lignes dans un fichier Excel.
Dans ce classeur, il existe 3 feuilles, et chaque ligne lue dans la table est insérée dans la feuille correspondante en fonction d'un champ.

Le tout marche parfaitement, je gère également la volumétrie maximale d'une feuille Excel, enregistrant le fichier courant et en ouvrant un autre si besoin pour continuer l'exportation.

Techniquement, j'utilise un datareader sur ma table et ensuite un select sur le champ qui m'intéresse pour savoir dans quelle feuille insérer la ligne du datareader. Read. L'exécution est très chronophage, ça tourne à environ 2000 lignes à la minutes, ce qui pour 13 millions attendues est extrêmement long, il faudrait que cela prenne une dizaine d'heures environ.

Auriez vous des conseils sur la méthode à utiliser ou les optimisations à apporter ?

Par ailleurs, je souhaiterais faire en sorte que quand une exportation est lancée, il soit impossible à l'utilisateur de l'ordinateur d'ouvrir un autre classeur excel , car ceci fait planter mon application. Le double clic sur un classeur quelconque le fait s'ouvrir en utilisant l'instance d'excel que mon application a lancée.

Merci d'avance et bonne journée.

[hunteshiva]

Bonjour,

ça tourne à environ 2000 lignes à la minutes, ce qui pour 13 millions attendues est extrêmement long, il faudrait que cela prenne une dizaine d'heures environ.

Tu m'étonne

je trouve ça extrêmement lent...
pourrais tu nous montrer le code qui fonctionne aujourd'hui?

au moins un aperçu de

j'utilise un datareader sur ma table et ensuite un select sur le champ

et ensuite comment tu enregistre dans le fichier Excel

[Dullbrain]

Merci pour la rapidité de la réponse !

Alors voilà le code incriminé (PS : j'ai testé avec et sans le reportprogress, ça ne modifie pas les performances)

la boucle de lecture :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 'variable indiquant combien de lignes ont été exportées vers Excel 
            Dim nombre_lignes_exportees As Integer = 0
            'variable du nom de sauvegarde pour le ou les fichiers Excel d'exportation
            Dim nom_sauvegarde As String = Nothing
            While myReader.Read
                'appel de la fonction d'écriture dans la feuille appropriée
                Call write_Excel_line(ClasseurExcel, myReader)
                'incrémentation du nombre de lignes traitées
                nombre_lignes_exportees = nombre_lignes_exportees + 1
                'affichage de l'avancement
                worker.ReportProgress(nombre_lignes_exportees)
 
               'si une des feuilles Excel a atteint la limite
                If isLimitReached(ClasseurExcel) = 1000000 Then
                    nom_sauvegarde = "C:\USERS\" & Environment.UserName & "\Desktop\Extraction_" & CDate(ClasseurExcel.Worksheets("Courbe de Charge").cells(2, 3).value).ToString("ddMM") & "_" & CDate(myReader("HORODATE").ToString).ToString("ddMM")
                    'On vérifie si un tel fichier xlsx existe
                    If File.Exists(nom_sauvegarde & ".xlsx") Then
                        'si oui, on déclare une variable pour trouver un indice à ajouter au nom
                        Dim number As Integer = 1
                        'tant qu'un fichier nom voulu + indice existe
                        While File.Exists(nom_sauvegarde & "_" & number & ".xlsx")
                            'on incrémente l'indice
                            number = number + 1
                        End While
                        'une fois en possession du bon indice, modifie le nom de sauvegarde
                        nom_sauvegarde = nom_sauvegarde & "_" & number & ".xlsx"
                    End If
                    'on sauvegarde le classeur
                    ClasseurExcel.SaveAs(nom_sauvegarde)
                    'on ferme le classeur
                    ClasseurExcel.Close()
                    'on configure un nouveau classeur d'exportation
                    ClasseurExcel = Excel_config()
                End If
 
            End While

Et voici les fonctions appelées dans la boucle :

- Fonction d'écriture d'une ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
'Fonction d'écriture d'une ligne dans un fichier Excel
    Private Sub write_Excel_line(ByVal monclasseur As Excel.Workbook, ByVal myreader As SqlDataReader)
        Try
            'variable de la feuille dans laquelle on va écrire
            Dim FeuilleCourante As Excel.Worksheet
            'selon le channelname
            Select Case myreader("CHANNEL_NAME")
                'si c'est celui des courbes de charges
                Case "CC_EAS"
                    'on active la feuille appropriée
                    FeuilleCourante = monclasseur.Worksheets("Courbe de Charge")
                    'variable de curseur d'insertion dans la feuille Excel
                    Dim curseur As Integer = 0
                    'on affecte la valeur du curseur au nombre de lignes déjà existantes dans la feuille
                    curseur = FeuilleCourante.UsedRange.Rows.Count
                    'on insère dans la feuille appropriée
                    With FeuilleCourante
                        'on ajoute chaque élément de la ligne dans la colonne adéquate dans Excel
                        .Cells(curseur + 1, 1) = "'" & myreader("NUMSERIE_C")
                        .Cells(curseur + 1, 2) = "a" 'Liste_Individus(getPDCIndice(myreader("PDC"), Liste_Individus)).PRM
                        .Cells(curseur + 1, 3) = myreader("HORODATE")
                        .Cells(curseur + 1, 4) = "'" & myreader("VALEURCC")
                    End With
                    'on quitte le select
                    Exit Select
                    's'il s'agit des indexs
                Case "IDX_A_EAS_DIST_1"
                    'on active la feuille appropriée
                    FeuilleCourante = monclasseur.Worksheets("Index")
                    'variable de curseur d'insertion dans la feuille Excel
                    Dim curseur As Integer = 0
                    'on affecte la valeur du curseur au nombre de lignes déjà existantes dans la feuille
                    curseur = FeuilleCourante.UsedRange.Rows.Count
                    'on insère dans la feuille appropriée
                    With FeuilleCourante
                        ''on ajoute chaque élément de la ligne dans la colonne adéquate dans Excel
                        .Cells(curseur + 1, 1) = "'" & myreader("NUMSERIE_C")
                        .Cells(curseur + 1, 2) = "a" 'Liste_Individus(getPDCIndice(myreader("PDC"), Liste_Individus)).PRM
                        .Cells(curseur + 1, 3) = myreader("HORODATE")
                        .Cells(curseur + 1, 4) = "'" & myreader("VALEURIDX")
                    End With
                    'on quitte le select
                    Exit Select
            End Select
        Catch ex As Exception
            MsgBox("Erreur d'écriture dans le fichier Excel : " & ex.Message, MsgBoxStyle.Exclamation, "Erreur")
        End Try
    End Sub

- Fonction de vérification de la volumétrie dans le classeur Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
'fonction indiquant si une feuille d'un classeur Excel a atteint le nombre de lignes limite
    Private Function isLimitReached(ByVal monClasseur As Excel.Workbook) As Integer
        Try
            Dim max As Integer = 0
            'pour chaque feuille du classeur passé en paramètre
            For Each feuille As Excel.Worksheet In monClasseur.Worksheets
                'si cette feuille a plus de ligne que le max
                If feuille.UsedRange.Rows.Count > max Then
                    'on actualise le max
                    max = feuille.UsedRange.Rows.Count
                End If
            Next
            'arrivé là, on dispose du maximum
            Return max
        Catch ex As Exception
            MsgBox("Erreur de vérification de volumétrie du classeur Excel")
            Return -1
        End Try
    End Function

[hunteshiva]

Cherche pas plus loin.

ça viens de ce que l'on appel l'automation Excel.
c'est à dire quand tu ouvre ton fichier avec ce code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Dim FeuilleCourante As Excel.Worksheet

en faite cette .dll est éxtrément lente.

si tu veux faire ton traitement rapidement (moins de 30 sec),
il faut que tu regarde du coté de "OLEDB"+"Excel"+"vb.net" sur google.
ça va te faire travailler avec des requetes SQL mais c'est e jour et la nuit

[Dullbrain]

Merci pour l'explication !

En fait le code que j'ai posté, c'est une solution de contournement, à terme, je ferai une requête sur une base oracle pour ensuite les traiter à la volée avec le datareader.

OLEDB sera-t-il utilisable dans ce cas, et pourrais-je conserver le formalisme de mon classeur excel avec ses feuilles distinctes ?

[hunteshiva]

Oui sans soucis,

en faite OLEDB est un moyen de se connecter au fichier Excel sans l'ouvrir.
tu le pilote comme une Base de données.

le problème de la feuille ne se pose pas, voici un exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
        Try
 
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim sql As String
 
            MyConnection = New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
            "'c:\testfile.xls';Extended Properties=Excel 8.0;")
 
            MyConnection.Open()
            myCommand.Connection = MyConnection
            sql = "Insert into [Sheet1$] (id,name) values('5','e')"
            myCommand.CommandText = sql
            myCommand.ExecuteNonQuery()
            MyConnection.Close()
 
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
 
        MsgBox("Row Added ")

Après si tu veux créer une feuille, la je sais pas trop mais ça dois se faire.