Optimisation de l'exportation Excel

Version imprimable

01/06/2012, 09h44
Dullbrain

Optimisation de l'exportation Excel

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.
01/06/2012, 10h16
hunteshiva

Bonjour,

Citation:

Envoyé par Dullbrain

ç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 :mouarf:

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

au moins un aperçu de

Citation:

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

et ensuite comment tu enregistre dans le fichier Excel

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:

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:

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:

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

01/06/2012, 11h01
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:

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 ;)
01/06/2012, 11h08
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 ?

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:

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. ;)

Merci beaucoup !
Si j'arrive à faire marcher ça je pourrais résoudre deux problèmes à la fois !

Par contre, j'ai commencé à tester, mais j'ai un problème avec la connexion.
En effet, après avoir cherché sur internet, mon test plante :
Code:

1 2 3 Dim XLVerbindung As New Odbc.OdbcConnection XLVerbindung.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\cc06777s\Desktop\test.xls;Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""" XLVerbindung.Open()
Avec l'erreur :

Citation:

Error [IM002] [Microsoft] [Gestionnaire de Pilotes ODBC] Source de données introuvable et nom de pilote non spécifié

Sachant que Data Source, j'ai copié le chemin directement depuis l'explorateur Windows donc il est bon, et que pour les Extended Properties, j'ai aussi testé avec Excel 8.0 sans succès. Je tourne sur une machine 32 bits.

Une idée ? :)

01/06/2012, 12h05
chrismonoye

Bonjour,
peut être une piste.
01/06/2012, 12h08
hunteshiva
Citation:

Je tourne sur une machine 32 bits.

Oo Déjà :mouarf:

voici les manips a faire si j'étais à ta place:
1. Ajouter la dll qui va bien
  Projet -> Ajouter une référence ...
2. Importer la classe "System.Data"
  
  Code:
  
  Imports System.Data
voila 2 idées, je sais pas si ça va e solutionner ton problème.
01/06/2012, 14h03
sinople

Hello,

Je te donne, pour information, d'autre piste de solution pour ta problématique:

Valable pour les fichiers xlsx (Excel 2007 et suivant), Travaille directement sur le fichier en XML. Ceci te permet de garder un contrôle sur la mise en forme de ton fichier Excel. (Peut être pas la solution la plus simple, mais la maîtrise du concept offre un véritable plus à tes compétances...)

Exporter sous forme de fichier .csv (tableur text). Ces derniers sont compatibles avec la majorité des programmes utilisant des données tabulaires, dont Excel (bien entendu). En effet l'export dans un fichier Excel sans utilisation des fonctionnalités de ce dernier (mise en forme et cellules calculée) n'est pas optimum (perte de performance, pour ne pas utiliser le gain fonctionnel?)

En mode automation, vérifier que Application.screenupdating = False et que Application.Calculation = xlCalculationManual (à traduire en .NET). Ceci peut augmenter les performances de manière significative.

OleDb est quand même une solution intermédiaire très intéressante!
02/06/2012, 22h34
hunteshiva

Bonjour,

un personne a résolu le même probléme que toi:
http://www.developpez.net/forums/d10...ver-specified/

Sinon il y a :google:
ODBC excel vb.net Error [IM002]

;)
08/06/2012, 18h01
healey26

si je peux me permettre d'ajouter une réponse un peu différente dans les pistes à suivre, pourquoi ne pas utiliser les technos de cloud ? ça te permettrait de diminuer grandement le temps d'exécution peut être ? tu pourrais traiter aussi l'import de données via un autre langage comme du perl ou autre, ça dépend des possibilités qui te sont offertes.
Dans tous les cas oublies l'appel de l'automation, pratique dans certains cas mais beaucoup moins rapide qu'une appel OLE.
08/06/2012, 18h19
youtpout978

moi ce qui me choque c'est que tu fait ton écriture dans le read de ton datareader, une meilleur solution peut être serait de stocker tes données dans des listes et effectué ton écriture ensuite (donc une fois que tu as tout lu), tu peux même lire un nombre données de ligne et faire tes traitements pour pas saturer la mémoire et refaire une lecture ainsi de suite
08/06/2012, 19h16
meziantou

Citation:

Envoyé par youtpout978

moi ce qui me choque c'est que tu fait ton écriture dans le read de ton datareader

En quoi cela te dérange ?
08/06/2012, 20h26
youtpout978

pendant ce temps la connexion à la bdd ne reste pas ouverte ?
18/06/2012, 22h39
Skalp

Plus d'informations sur les différentes méthodes d'écriture dans un fichier Excel dans la FAQ : Comment écrire dans un fichier Excel ?