Charger un fichier excel en mémoire

Commenter

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

S'inscrire maintenant

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.

Du même auteur (Ayoros12)