Korzystanie z Excel OleDb, aby uzyskać nazwy arkuszy W KOLEJNOŚCI ARKUSZY

Używam OleDb do czytania ze skoroszytu programu Excel z wieloma arkuszami.

Muszę przeczytać nazwy arkuszy, ale potrzebuję ich w kolejności, w jakiej są zdefiniowane w arkuszu kalkulacyjnym; więc jeśli mam plik, który wygląda tak;

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

Następnie muszę zdobyć słownik

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

Próbowałem użyć OleDbConnection.GetOleDbSchemaTable()i to daje mi listę nazwisk, ale posortuje je alfabetycznie. Sortowanie alfa oznacza, że ​​nie wiem, któremu numerowi arkusza odpowiada dana nazwa. Więc dostaję;

GERMANY, IRELAND, UK

który zmienił kolejność UKi IRELAND.

Powodem, dla którego potrzebuję sortowania, jest to, że muszę pozwolić użytkownikowi wybrać zakres danych według nazwy lub indeksu; mogą poprosić o „wszystkie dane z NIEMIEC do IRLANDII” lub „dane z arkusza 1 do arkusza 3”.

Wszelkie pomysły będą mile widziane.

gdybym mógł użyć klas międzyoperacyjnych pakietu office, byłoby to proste. Niestety nie mogę, ponieważ klasy międzyoperacyjne nie działają niezawodnie w nieinteraktywnych środowiskach, takich jak usługi Windows i witryny ASP.NET, więc musiałem użyć OLEDB.

Nie mogę znaleźć tego w rzeczywistej dokumentacji MSDN, ale powiedział moderator na forach

Obawiam się, że OLEDB nie zachowuje kolejności arkuszy tak jak w Excelu

Nazwy arkuszy programu Excel w kolejności arkuszy

Wydaje się, że byłby to dość powszechny wymóg, aby istniało przyzwoite obejście.

Czy nie możesz po prostu zapętlić arkuszy od 0 do Liczba nazwisk -1? w ten sposób powinieneś uzyskać je we właściwej kolejności.

Edytować

Zauważyłem w komentarzach, że istnieje wiele obaw związanych z używaniem klas Interop do pobierania nazw arkuszy. Dlatego oto przykład użycia OLEDB do ich odzyskania:

/// <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();
      }
   }
}

Wyodrębniono z artykułu w CodeProject.

Ponieważ powyższy kod nie obejmuje procedur wyodrębniania listy nazw arkuszy dla Excel 2007, poniższy kod będzie miał zastosowanie zarówno do Excela (97-2003), jak i 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;
}

Powyższa funkcja zwraca listę arkuszy w konkretnym pliku Excel dla obu typów Excela (97,2003,2007).

To jest krótkie, szybkie, bezpieczne i użyteczne ...

public static List<string> ToExcelsSheetList(string excelFilePath)
{
    List<string> sheets = new List<string>();
    using (OleDbConnection connection = 
            new OleDbConnection((excelFilePath.TrimEnd().ToLower().EndsWith("x")) 
            ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFilePath + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"
            : "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFilePath + "';Extended Properties=Excel 8.0;"))
    {
        connection.Open();
        DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        foreach (DataRow drSheet in dt.Rows)
            if (drSheet["TABLE_NAME"].ToString().Contains("$"))
            {
                string s = drSheet["TABLE_NAME"].ToString();
                sheets.Add(s.StartsWith("'")?s.Substring(1, s.Length - 3): s.Substring(0, s.Length - 1));
            }
        connection.Close();
    }
    return sheets;
}

Inny sposób:

plik xls (x) to po prostu zbiór plików * .xml przechowywanych w kontenerze * .zip. rozpakuj plik „app.xml” w folderze 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>

Plik jest plikiem niemieckim (Arbeitsblätter = workheets). Nazwy tabel (Tabelle3 itp.) Są we właściwej kolejności. Wystarczy przeczytać te tagi;)

pozdrowienia

Poniższą funkcję stworzyłem korzystając z informacji podanych w odpowiedzi z @kraeppy ( https://stackoverflow.com/a/19930386/2617732 ). Wymaga to użycia platformy .net Framework w wersji 4.5 i odwołania do System.IO.Compression. Działa to tylko w przypadku plików xlsx, a nie starszych plików xls.

    using System.IO.Compression;
    using System.Xml;
    using System.Xml.Linq;

    static IEnumerable<string> GetWorksheetNamesOrdered(string fileName)
    {
        //open the excel file
        using (FileStream data = new FileStream(fileName, FileMode.Open))
        {
            //unzip
            ZipArchive archive = new ZipArchive(data);

            //select the correct file from the archive
            ZipArchiveEntry appxmlFile = archive.Entries.SingleOrDefault(e => e.FullName == "docProps/app.xml");

            //read the xml
            XDocument xdoc = XDocument.Load(appxmlFile.Open());

            //find the titles element
            XElement titlesElement = xdoc.Descendants().Where(e => e.Name.LocalName == "TitlesOfParts").Single();

            //extract the worksheet names
            return titlesElement
                .Elements().Where(e => e.Name.LocalName == "vector").Single()
                .Elements().Where(e => e.Name.LocalName == "lpstr")
                .Select(e => e.Value);
        }
    }

Podoba mi się pomysł @deathApril, aby nazywać arkusze jako 1_Germany, 2_UK, 3_IRELAND. Mam również twój problem, aby zmienić nazwę dla setek arkuszy. Jeśli nie masz problemu ze zmianą nazwy arkusza, możesz użyć tego makra, aby zrobić to za siebie. Zmiana nazw wszystkich arkuszy zajmie mniej niż sekundy. niestety ODBC, OLEDB zwraca kolejność nazw arkuszy rosnąco. Nie ma na to zamiennika. Musisz użyć COM lub zmienić nazwę swojej nazwy, aby była w kolejności.

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

AKTUALIZACJA: Po przeczytaniu komentarza @SidHoland dotyczącego BIFF pojawił się pomysł. Poniższe kroki można wykonać za pomocą kodu. Nie wiem, czy naprawdę chcesz to zrobić, aby uzyskać nazwy arkuszy w tej samej kolejności. Daj mi znać, jeśli potrzebujesz pomocy, aby to zrobić za pomocą kodu.

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

AKTUALIZACJA: Inne rozwiązanie - NPOI może być tutaj pomocne 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();

To rozwiązanie działa dla xls. Nie próbowałem xlsx.

Dzięki,

Esen

To zadziałało dla mnie. Skradzione stąd: Jak uzyskać nazwę pierwszej strony skoroszytu programu Excel?

object opt = System.Reflection.Missing.Value;
Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(WorkBookToOpen,
                                         opt, opt, opt, opt, opt, opt, opt,
                                         opt, opt, opt, opt, opt, opt, opt);
Excel.Worksheet worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
string firstSheetName = worksheet.Name;

Spróbuj tego. Oto kod umożliwiający uporządkowanie nazw arkuszy.

private Dictionary<int, string> GetExcelSheetNames(string fileName)
{
    Excel.Application _excel = null;
    Excel.Workbook _workBook = null;
    Dictionary<int, string> excelSheets = new Dictionary<int, string>();
    try
    {
        object missing = Type.Missing;
        object readOnly = true;
        Excel.XlFileFormat.xlWorkbookNormal
        _excel = new Excel.ApplicationClass();
        _excel.Visible = false;
        _workBook = _excel.Workbooks.Open(fileName, 0, readOnly, 5, missing,
            missing, true, Excel.XlPlatform.xlWindows, "\\t", false, false, 0, true, true, missing);
        if (_workBook != null)
        {
            int index = 0;
            foreach (Excel.Worksheet sheet in _workBook.Sheets)
            {
                // Can get sheet names in order they are in workbook
                excelSheets.Add(++index, sheet.Name);
            }
        }
    }
    catch (Exception e)
    {
        return null;
    }
    finally
    {
        if (_excel != null)
        {

            if (_workBook != null)
                _workBook.Close(false, Type.Missing, Type.Missing);
            _excel.Application.Quit();
        }
        _excel = null;
        _workBook = null;
    }
    return excelSheets;
}

Zgodnie z MSDN, w przypadku arkuszy kalkulacyjnych w programie Excel może nie działać, ponieważ pliki programu Excel nie są prawdziwymi bazami danych. Dlatego nie będziesz w stanie uzyskać nazwy arkuszy w kolejności ich wizualizacji w skoroszycie.

Kod, aby uzyskać nazwę arkusza zgodnie z ich wyglądem wizualnym za pomocą współdziałania:

Dodaj odniesienie do biblioteki obiektów programu Microsoft Excel 12.0.

Poniższy kod poda nazwę arkuszy w rzeczywistej kolejności przechowywanej w skoroszycie, a nie posortowaną nazwę.

Przykładowy kod:

using Microsoft.Office.Interop.Excel;

string filename = "C:\\romil.xlsx";

object missing = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook wb =excel.Workbooks.Open(filename,  missing,  missing,  missing,  missing,missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing,  missing);

ArrayList sheetname = new ArrayList();

foreach (Microsoft.Office.Interop.Excel.Worksheet  sheet in wb.Sheets)
{
    sheetname.Add(sheet.Name);
}

Nie widzę żadnej dokumentacji, która mówi, że kolejność w app.xml jest gwarantowana jako kolejność arkuszy. PRAWDOPODOBNIE jest, ale nie zgodnie ze specyfikacją OOXML.

Z drugiej strony plik workbook.xml zawiera atrybut sheetId, który określa kolejność - od 1 do liczby arkuszy. Jest to zgodne ze specyfikacją OOXML. workbook.xml jest opisywane jako miejsce, w którym przechowywana jest kolejność arkuszy.

Dlatego zalecam przeczytanie pliku workbook.xml po wyodrębnieniu go z pliku XLSX. NIE app.xml. Zamiast docProps / app.xml użyj xl / workbook.xml i spójrz na element, jak pokazano tutaj -

`

<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9303" /> 
  <workbookPr defaultThemeVersion="124226" /> 
- <bookViews>
  <workbookView xWindow="120" yWindow="135" windowWidth="19035" windowHeight="8445" /> 
  </bookViews>
- <sheets>
  <sheet name="By song" sheetId="1" r:id="rId1" /> 
  <sheet name="By actors" sheetId="2" r:id="rId2" /> 
  <sheet name="By pit" sheetId="3" r:id="rId3" /> 
  </sheets>
- <definedNames>
  <definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">'By song'!$A$1:$O$59</definedName> 
  </definedNames>
  <calcPr calcId="145621" /> 
  </workbook>

`