Charger un fichier excel en mémoire

Soyez le premier à donner votre avis sur cette source.

Snippet vu 10 472 fois - Téléchargée 32 fois

Contenu du snippet

Après avoir longtemps cherché sur le site un code me permettant de rapidement lire et traiter des données contenues dans un fichier Excel, je me suis aperçu qu'il existait des dizaines de possibilité (Flexgrid, API, Objet, Office Web components etc.)
Celle que je trouvais le plus pratique consistait à lire directement les cellules après avoir ouvert le fichier.
Or la moindre opération prenait énormément de temps.
Par exemple, pour trier les champs uniques d'une colonne d'un fichier xls de 9Mo, je mettais 28s avec la fonction Cell(x,y) et 20s avec la fonction Range(coord).
C'était beaucoup trop long.
Et puis je suis tombé sur la méthode Resize de Range => 2s seulement.
Le code ci-dessous vous montre brièvement comment elle s'emploie.

Source / Exemple :


Option Explicit

Public appExcel As excel.Application 'Application Excel
Public wbExcel As excel.Workbook 'Classeur Excel
Public wsExcel As excel.Worksheet 'Feuille Excel
Option Base 1
Public colHeader As Variant 'contient la colonne courante
Public Open_Excel_Session As Boolean 'ca c'est pour savoir s'il faut killer Excel en sortant

'*************************************************************************
'                                                           OPEN EXCEL FILE
'
' Ouvre un fichier excel
' Sheet est l'index de la feuille. Visible détermine si on cache où nom Excel. 
' (pratique pour débuguer)
' Renvoie l'index de la dernière ligne
'************************************************************************
Public Function Open_Excel_File(FilePath As String, FileName As String, Optional Sheet As Byte = 1, Optional Visible As Boolean = False) As Long
FilePath = Trim$(FilePath)
FileName = Trim$(FileName)
If Dir(FilePath + "\" + FileName) = FileName Then
    Open_Excel_Session = True
    Set appExcel = CreateObject("Excel.Application")
    'Ouverture d'un fichier Excel
    Set wbExcel = appExcel.Workbooks.Open(FilePath + "\" + FileName)
    'wsExcel correspond à la première feuille du fichier
    Set wsExcel = wbExcel.Worksheets(Sheet)
    appExcel.Visible = Visible
    Fill_Col_Array
    Open_Excel_File = Last_Row(wsExcel)
Else
    MsgBox FilePath + FileName + vbCr + "has not been found", vbCritical, "File not Found"
    Open_Excel_File = -1
End If
End Function

'*************************************************************************
'                                                                 FILL COL ARRAY
'
' Fonction bien pratique pour ne pas s'embêter avec la conversion des chiffres en lettres
' *************************************************************************
Public Sub Fill_Col_Array()
colHeader = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", _
"AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", _
"BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", _
"CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ")
End Sub

'*************************************************************************
'                                                         LAST ROW
' Renvoie l'index de la dernière ligne du fichier excel
' *************************************************************************
Public Function Last_Row(sh As Worksheet) As Long
On Error Resume Next
Last_Row = sh.Cells.Find(What:="*", After:=sh.Range("A1"), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row
On Error GoTo 0
End Function

'voici la fonction qui permet de mettre le contenu du fichier en mémoire
'd'après mes tests, c'est plus rapide qu'avec le Web Office Component
'vous pouviez bien entendu l'adapter pour ne lire qu'une ligne ou une colonne
'*************************************************************************
'                                                      GET ALL DATA
' Copie en mémoire (table variant) l'ensemble des données d'une feuille excel)
' Maxcol est la dernière colonne, maxrow la dernière ligne, la copie débutant en case A:1
' *************************************************************************
Public Function Get_All_Data(maxcol As Byte, maxrow As Long) As Variant
Dim table As Variant
Dim dat As String
dat = "A1:" & colHeader(maxcol) & Trim$(Str$(maxrow))
table = wsExcel.Range(dat).Resize(maxrow, maxcol)
Get_All_Data = table
Set table = Nothing
End Function

'pour l'appeller c'est simple (après avoir ouvert le fichier excel bien sûr):
Dim montableau as variant
Dim Derniere_ligne as long 'pas la peine de prendre du double avec Excel
Derniere_ligne = open_excel_file ("c:\","test.xls")
montableau = get_all_data

Conclusion :


Voilà, j'espère que c'est assez clair.
Cela ne m'étonnerait pas qu'il y ait encore mieux, mais c'est ce que j'ai trouvé de plus simple. Et puis je préfère travailler avec des tableaux qu'avec des controles.
Comme vous le voyez, ce n'est pas la découverte du siècle, mais je vous garantie que j'ai passé plusieurs jours à tout essayer et chercher sur le web.
Vous dire, que c'est documenté... ;)

A voir également

Ajouter un commentaire

Commentaires

azerty58220
Messages postés
2
Date d'inscription
mercredi 27 septembre 2006
Statut
Membre
Dernière intervention
12 février 2007
-
un peu en retard... mais ca peut tjrs servir:

j'utilise simplement simplement le code suivant pour obtenir le numéro de la dernière ligne du fichier:
dim lastRow as integer
lastRow=sheets(xxx).cells(x,y).end(xldown).row

la fonction end(xldown) permet de selectionner la dernier cellule mais par contre s'arrete a la premiere cellule vide trouvée
cs_salazar
Messages postés
241
Date d'inscription
mercredi 1 octobre 2003
Statut
Membre
Dernière intervention
19 février 2006
1 -
Si je comprends bien, pour lire une valeur dans Excel, c'est accéleré en utilisant Resize :

Désolé mais j'ai essayé sur mon code, et au contraire ca plante ! Tu devrais mettre ton code en zip
gpouliot
Messages postés
4
Date d'inscription
vendredi 26 avril 2002
Statut
Membre
Dernière intervention
3 mai 2007
-
Je te remercie vraiment beaucoup et pour le code et pour la vitesse de la réponse.

Tiguidou
gpouliot
Messages postés
4
Date d'inscription
vendredi 26 avril 2002
Statut
Membre
Dernière intervention
3 mai 2007
-
J'aimerais savoir ce que fais la fontion Last_Row(wsExcel)
Pourrais-tu la définir? Il ne me manque que cela pour que ça fontionne. J'aurais aimer que tu définisses toutes les variables pour ceux qui emploient Option Explicit.

Merci à l'avance.
aurelskull
Messages postés
18
Date d'inscription
dimanche 13 avril 2003
Statut
Membre
Dernière intervention
27 mai 2005
-
j'ai pas trop saisi l'ensemble mais pourrais-tu mettre un exemple ? merci d'avance

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.