[c#] 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) 모두에 대해 특정 엑셀 파일의 시트 목록을 반환합니다.


답변

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

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


답변

또 다른 방법:

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


답변

시트 이름을 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

업데이트 : BIFF에 관한 @SidHoland 코멘트를 읽은 후 아이디어가 번쩍였습니다. 다음 단계는 코드를 통해 수행 할 수 있습니다. 같은 순서로 시트 이름을 얻기 위해 정말로 그렇게 할 것인지 모르겠습니다. 코드를 통해이 작업을 수행하는 데 도움이 필요하면 알려주세요.

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

업데이트 : 또 다른 해결책-NPOI는 여기 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();

이 솔루션은 xls에서 작동합니다. xlsx를 시도하지 않았습니다.

감사,

에센