Utilisation d'Excel OleDb pour obtenir les noms des feuilles dans l'ordre des feuilles.

Question

J'utilise OleDb pour lire un classeur Excel comportant de nombreuses feuilles.

J'ai besoin de lire les noms des feuilles, mais dans l'ordre où ils sont définis dans la feuille de calcul ; donc, si j'ai un fichier qui ressemble à ceci ;

|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/

Alors je dois obtenir le dictionnaire

1="GERMANY", 
2="UK", 
3="IRELAND"

J'ai essayé d'utiliser OleDbConnection.GetOleDbSchemaTable() et cela me donne la liste des noms, mais en les classant par ordre alphabétique. Le tri alphabétique signifie que je ne sais pas à quel numéro de feuille correspond un nom particulier. Donc, j'obtiens ;

GERMANY, IRELAND, UK

qui a changé l'ordre des UK y IRELAND .

La raison pour laquelle j'ai besoin qu'elles soient triées est que je dois laisser l'utilisateur choisir une plage de données par nom ou par index ; il peut demander "toutes les données de l'ALLEMAGNE à l'IRLANDE" ou "les données de la feuille 1 à la feuille 3".

Toute idée serait grandement appréciée.

Si je pouvais utiliser les classes interop du bureau, ce serait simple. Malheureusement, ce n'est pas le cas car les classes d'interopérabilité ne fonctionnent pas de manière fiable dans les environnements non interactifs tels que les services Windows et les sites ASP.NET, et je dois donc utiliser OLEDB.

Answer 1

Ne pouvez-vous pas simplement parcourir en boucle les feuilles de 0 à Nombre de noms -1 ? De cette façon, vous devriez les obtenir dans le bon ordre.

Modifier

J'ai remarqué dans les commentaires que l'utilisation des classes Interop pour récupérer les noms des feuilles suscite de nombreuses inquiétudes. Voici donc un exemple utilisant OLEDB pour les récupérer :

/// <summary>
/// This method retrieves the excel sheet names from 
/// an excel workbook.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns>String[]</returns>
private String[] GetExcelSheetNames(string excelFile)
{
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        // Connection String. Change the excel file to the file you
        // will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
          "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if(dt == null)
        {
           return null;
        }

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        // Add the sheet name to the string array.
        foreach(DataRow row in dt.Rows)
        {
           excelSheets[i] = row["TABLE_NAME"].ToString();
           i++;
        }

        // Loop through all of the sheets if you want too...
        for(int j=0; j < excelSheets.Length; j++)
        {
            // Query each excel sheet.
        }

        return excelSheets;
   }
   catch(Exception ex)
   {
       return null;
   }
   finally
   {
      // Clean up.
      if(objConn != null)
      {
          objConn.Close();
          objConn.Dispose();
      }
      if(dt != null)
      {
          dt.Dispose();
      }
   }
}

Extrait de Article sur le CodeProject.

Answer 2

Puisque le code ci-dessus ne couvre pas les procédures d'extraction de la liste des noms de feuilles pour Excel 2007, le code suivant sera applicable aussi bien pour Excel (97-2003) que pour Excel 2007 :

public List<string> ListSheetInExcel(string filePath)
{
   OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
   String strExtendedProperties = String.Empty;
   sbConnection.DataSource = filePath;
   if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
   {
      sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
      strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
   }
   else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
   {
      sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
      strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
   }
   sbConnection.Add("Extended Properties",strExtendedProperties);
   List<string> listSheet = new List<string>();
   using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
   {
     conn.Open();
     DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);         
     foreach (DataRow drSheet in dtSheet.Rows)
     {
        if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
        {
             listSheet.Add(drSheet["TABLE_NAME"].ToString());
        } 
     }
  }
 return listSheet;
}

La fonction ci-dessus renvoie la liste des feuilles d'un fichier Excel particulier pour les deux types d'Excel (97, 2003, 2007).

Answer 3

Je n'ai pas trouvé cela dans la documentation MSDN actuelle, mais un modérateur dans les forums a dit que

Je crains qu'OLEDB ne conserve pas l'ordre des feuilles tel qu'il était dans Excel.

Noms de feuilles Excel dans l'ordre des feuilles

Il semble que ce soit une exigence suffisamment courante pour qu'il y ait une solution de contournement décente.

Answer 4

Une autre façon :

un fichier xls(x) est juste une collection de fichiers *.xml stockés dans un conteneur *.zip. Dézippez le fichier "app.xml" dans le dossier docProps.

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<TotalTime>0</TotalTime>
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
-<HeadingPairs>
  -<vt:vector baseType="variant" size="2">
    -<vt:variant>
      <vt:lpstr>Arbeitsblätter</vt:lpstr>
    </vt:variant>
    -<vt:variant>
      <vt:i4>4</vt:i4>
    </vt:variant>
  </vt:vector>
</HeadingPairs>
-<TitlesOfParts>
  -<vt:vector baseType="lpstr" size="4">
    <vt:lpstr>Tabelle3</vt:lpstr>
    <vt:lpstr>Tabelle4</vt:lpstr>
    <vt:lpstr>Tabelle1</vt:lpstr>
    <vt:lpstr>Tabelle2</vt:lpstr>
  </vt:vector>
</TitlesOfParts>
<Company/>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>14.0300</AppVersion>
</Properties>

Le fichier est un fichier allemand (Arbeitsblätter = feuilles de travail). Les noms des tableaux (Tabelle3 etc) sont dans l'ordre correct. Il vous suffit de lire ces balises ;)

salutations

Answer 5

J'aime l'idée de @deathApril de nommer les feuilles comme 1_Allemagne, 2_Royaume-Uni, 3_Irlande. Je comprends aussi votre problème pour renommer des centaines de feuilles. Si vous n'avez pas de problème pour renommer le nom des feuilles, vous pouvez utiliser cette macro pour le faire pour vous. Cela prendra moins de quelques secondes pour renommer tous les noms de feuilles. Malheureusement, ODBC et OLEDB renvoient les noms de feuilles par ordre croissant. Il n'y a pas de remplacement pour cela. Vous devez soit utiliser COM, soit renommer votre nom pour qu'il soit dans l'ordre.

Sub Macro1()
'
' Macro1 Macro
'

'
Dim i As Integer
For i = 1 To Sheets.Count
 Dim prefix As String
 prefix = i
 If Len(prefix) < 4 Then
  prefix = "000"
 ElseIf Len(prefix) < 3 Then
  prefix = "00"
 ElseIf Len(prefix) < 2 Then
  prefix = "0"
 End If
 Dim sheetName As String
 sheetName = Sheets(i).Name
 Dim names
 names = Split(sheetName, "-")
 If (UBound(names) > 0) And IsNumeric(names(0)) Then
  'do nothing
 Else
  Sheets(i).Name = prefix & i & "-" & Sheets(i).Name
 End If
Next

End Sub

MISE À JOUR : Après avoir lu le commentaire de @SidHoland concernant le BIFF, une idée a germé. Les étapes suivantes peuvent être effectuées par code. Je ne sais pas si vous voulez vraiment faire cela pour obtenir les noms des feuilles dans le même ordre. Faites-moi savoir si vous avez besoin d'aide pour faire cela par code.

1. Consider XLSX as a zip file. Rename *.xlsx into *.zip
2. Unzip
3. Go to unzipped folder root and open /docprops/app.xml
4. This xml contains the sheet name in the same order of what you see.
5. Parse the xml and get the sheet names

MISE À JOUR : Une autre solution - NPOI pourrait être utile ici. http://npoi.codeplex.com/

 FileStream file = new FileStream(@"yourexcelfilename", FileMode.Open, FileAccess.Read);

      HSSFWorkbook  hssfworkbook = new HSSFWorkbook(file);
        for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
        {
            Console.WriteLine(hssfworkbook.GetSheetName(i));
        }
        file.Close();

Cette solution fonctionne pour xls. Je n'ai pas essayé pour xlsx.

Merci,

Esen