Lire un fichier Excel avec OLEDB (sans Excel)
Bonjour à tous,
Je suis tout nouveau sur le forum, je voulais vous faire partager une méthode que j'ai trouvée sur le Net pour lire les données d'un classeur Excel sans ouvrir Excel.
Dans le cas présent, je m'en sers pour générer un fichier texte par onglet.
Le principal avantage de cette méthode est sa rapidité par rapport à l'utilisation d'un objet COM (méthode la plus répandue j'ai l'impression) : le temps de traitement pour le même fichier est passé dans mon cas de 40sec à 1sec :).
Le classeur Excel que j'ai utilisé est simple (je posterai plus de tests avec plus de données) : 3 onglets, avec chacun entre 5 et 8 colonnes et une dizaine de lignes environ. Toutes les colonnes ont une en-tête (qu'on veut récupérer), et aucune cellule n'est vide dans les tables.
J'utilise le format .xslm, mais le .xslx fonctionne aussi avec ce script. Pour les formats plus anciens, il faut modifier la version du Provider OLEDB aux lignes 7 et 9
Je m'excuse par avance si j'enfreins une quelconque règle du forum en postant ceci ici et de cette façon. Je n'ai que 2 semaines de code Powershell derrière moi donc n'hésitez pas également à me signaler toute erreur de syntaxe :)
Voici le script :
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
|
#déclaration du dossier courant et du nom du classeur Excel
$workingDir = "C:\Users\Exemple\Desktop\powershell\"
$strFileName = $workingDir + "classeurExcel.xlsm"
#connect to data source
#connexion à la source de données
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 12.0"
$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$objConn.open()
#stockage des en-têtes dans un tableau
#store sheet names in an array
$tables = $objConn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::Tables,$null);
$sheets = @()
foreach($t in $tables)
{
$tableName = $t.TABLE_NAME
$sheets = ,$tableName.substring(0, $tableName.length - 1) + $sheets
}
#création du dossier de sortie s'il n'existe pas
#create output directory if not exists
$dataDir = $workingDir + "data\"
if(!(Test-Path $dataDir))
{
$mkdir = New-Item $dataDir -type Dir
}
#et suppression de tous les fichiers *.txt à l'intérieur
#and remove all *.txt files in it
Get-ChildItem $dataDir -Filter *.txt | Remove-Item
#itération sur le tableau des feuilles
#iterate on sheets array
$sheetIndex = 1
foreach($s in $sheets)
{
#retrieve data from Excel sheet
#récupération des données de la feuille
$strSheetName = $s + "`$"
$strQuery = "SELECT * FROM [$strSheetName]"
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$DataReader = $sqlCommand.ExecuteReader()
#modèle de nom de fichier : $workDir\data\sheetIndex-sheetName.txt
#output filename will be like $workDir\data\sheetIndex-sheetName.txt
$textFile = $dataDir + $sheetIndex + "-" + $s + ".txt"
#itération sur les lignes de la table
#iterate on table rows
$header = $true
while($DataReader.read())
{
$count = $DataReader.FieldCount
#on écrit les en-têtes si c'est la première ligne
#output field names as column headers if first row
if($header)
{
$fields = @()
for ($i = 0; $i -lt $count; $i++)
{
$fields += $DataReader.GetName($i)
}
$fields -join "," > $textFile
$header = $false
}
#itération sur les cellules de la ligne
#iterate on row cells
$cells = @()
for($i = 0; $i -lt $count; $i++)
{
$cells += $DataReader[$i]
}
#on écrit les cellules dans une nouvelle ligne du fichier : val1,val2,val3,val4,...
#write val1,val2,val3,val4,... in a new row of output file
$cells -join "," >> $textFile
}
$dataReader.close()
$sheetIndex++
}
$objConn.close()
Write-Output("Text export done / Export effectué") |
