Excel OleDb를 사용하여 시트 순서로 시트 이름 가져 오기

Excel OleDb를 사용하여 시트 순서로 시트 이름 가져 오기

---

OleDb를 사용하여 시트가 많은 Excel 통합 문서에서 읽습니다.

시트 이름을 읽어야하지만 스프레드 시트에 정의 된 순서대로 필요합니다. 그래서 다음과 같은 파일이 있다면;

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

그런 다음 사전을 가져와야합니다

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

나는을 사용해 보았고 OleDbConnection.GetOleDbSchemaTable()이름 목록을 제공하지만 알파벳순으로 정렬합니다. 알파 정렬은 특정 이름이 어떤 시트 번호에 해당하는지 모른다는 것을 의미합니다. 그래서 나는 얻는다;

GERMANY, IRELAND, UK

UK및 의 순서를 변경했습니다 IRELAND.

정렬해야하는 이유는 사용자가 이름이나 색인으로 데이터 범위를 선택할 수 있도록해야하기 때문입니다. 그들은 '독일에서 아일랜드까지의 모든 데이터'또는 '시트 1에서 시트 3까지의 데이터'를 요청할 수 있습니다.

어떤 아이디어라도 대단히 감사하겠습니다.

사무실 interop 클래스를 사용할 수 있다면 간단합니다. 안타깝게도 Windows 서비스 및 ASP.NET 사이트와 같은 비대화 형 환경에서는 interop 클래스가 안정적으로 작동하지 않아서 OLEDB를 사용해야했습니다.

---

실제 MSDN 문서에서는 찾을 수 없지만 포럼의 중재자가 말했습니다.

OLEDB가 Excel에서와 같이 시트 순서를 유지하지 못하는 것이 두렵습니다.

시트 순서의 Excel 시트 이름

이것은 적절한 해결 방법이있을만큼 충분히 일반적인 요구 사항 인 것 같습니다.

---

0에서 Count of names -1까지 시트를 반복 할 수 없습니까? 그런 식으로 올바른 순서로 가져와야합니다.

편집하다

주석을 통해 Interop 클래스를 사용하여 시트 이름을 검색하는 데 많은 관심이 있음을 알았습니다. 따라서 다음은 OLEDB를 사용하여 검색하는 예입니다.

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

CodeProject의 기사 에서 발췌 .

---

위의 코드는 Excel 2007 용 시트 명 목록 추출 절차를 다루지 않으므로 다음 코드는 Excel (97-2003)과 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;
}

위 함수는 엑셀 유형 (97,2003,2007) 모두에 대해 특정 엑셀 파일의 시트 목록을 반환합니다.

---

또 다른 방법:

xls (x) 파일은 * .zip 컨테이너에 저장된 * .xml 파일 모음입니다. docProps 폴더에 "app.xml"파일의 압축을 풉니 다.

<?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>

파일은 독일어 파일입니다 (Arbeitsblätter = 워크 시트). 테이블 이름 (Tabelle3 등)이 올바른 순서로되어 있습니다. 이 태그를 읽으면됩니다.)

문안 인사

---

@kraeppy ( https://stackoverflow.com/a/19930386/2617732 ) 의 답변에 제공된 정보를 사용하여 아래 함수를 만들었습니다 . 이를 위해서는 .net 프레임 워크 v4.5를 사용해야하며 System.IO.Compression에 대한 참조가 필요합니다. 이것은 xlsx 파일에서만 작동하며 이전 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);
        }
    }

---

이것은 짧고 빠르며 안전하며 사용 가능합니다 ...

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;
}

---

시트 이름을 1_Germany, 2_UK, 3_IRELAND로 지정하는 @deathApril 아이디어가 마음에 듭니다. 또한 수백 장의 시트에 대해이 이름 바꾸기를 수행하는 데 문제가 있습니다. 시트 이름을 바꾸는 데 문제가없는 경우이 매크로를 사용하여 수행 할 수 있습니다. 모든 시트 이름의 이름을 바꾸는 데 몇 초도 걸리지 않습니다. 불행히도 ODBC, OLEDB는 asc로 시트 이름 순서를 반환합니다. 그것을 대체 할 수 없습니다. COM을 사용하거나 순서대로 이름을 바꿔야합니다.

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

UPDATE: After reading @SidHoland comment regarding BIFF an idea flashed. The following steps can be done through code. Don't know if you really want to do that to get the sheet names in the same order. Let me know if you need help to do this through 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

UPDATE: Another solution - NPOI might be helpful here 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();

This solution works for xls. I didn't try xlsx.

Thanks,

Esen

---

This worked for me. Stolen from here: How do you get the name of the first page of an excel workbook?

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;

---

Try this. Here is the code to get the sheet names in order.

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;
}

---

As per MSDN, In a case of spreadsheets inside of Excel it might not work because Excel files are not real databases. So you will be not able to get the sheets name in order of their visualization in workbook.

Code to get sheets name as per their visual appearance using interop:

Add reference to Microsoft Excel 12.0 Object Library.

Following code will give the sheets name in the actual order stored in workbook, not the sorted name.

Sample Code:

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

---

I don't see any documentation that says the order in app.xml is guaranteed to be the order of the sheets. It PROBABLY is, but not according to the OOXML specification.

The workbook.xml file, on the other hand, includes the sheetId attribute, which does determine the sequence - from 1 to the number of sheets. This is according to the OOXML specification. workbook.xml is described as the place where the sequence of the sheets is kept.

So reading workbook.xml after it is extracted form the XLSX would be my recommendation. NOT app.xml. Instead of docProps/app.xml, use xl/workbook.xml and look at the element, as shown here -

`

<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>

`

참고URL : https://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order