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에서와 같이 시트 순서를 유지하지 못하는 것이 두렵습니다.
이것은 적절한 해결 방법이있을만큼 충분히 일반적인 요구 사항 인 것 같습니다.
답변
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를 시도하지 않았습니다.
감사,
에센