Excel OleDb를 사용하여 시트 이름을 시트 순서로 가져오기
OleDb를 사용하여 시트가 많은 엑셀 워크북을 읽고 있습니다.
시트 이름을 읽어야 하지만 스프레드시트에 정의된 순서대로 읽어야 합니다. 따라서 이렇게 생긴 파일이 있으면;
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
|_____|_____|____|____|____|____|____|____|____|
\__GERMANY__/\__UK__/\__IRELAND__/
그럼 나는 사전을 사야 합니다.
1="GERMANY",
2="UK",
3="IRELAND"
사용해 보았습니다.OleDbConnection.GetOleDbSchemaTable()이름의 목록을 알 수 있지만 알파벳 순으로 정렬합니다.알파 정렬은 특정 이름이 어떤 시트 번호에 해당하는지 모른다는 것을 의미합니다.그래서 이해합니다.
GERMANY, IRELAND, UK
바꾼것를의 .UK그리고.IRELAND.
제가 그것을 정렬해야 하는 이유는 사용자가 이름이나 색인으로 데이터 범위를 선택할 수 있도록 해야 하기 때문입니다. 그들은 '독일에서 아일랜드까지의 모든 데이터' 또는 '시트 1에서 시트 3까지의 데이터'를 요청할 수 있습니다.
어떤 아이디어라도 주시면 감사하겠습니다.
제가 사무실 인터럽트 수업을 이용할 수 있다면, 이것은 간단할 것입니다.유감스럽게도 윈도우 서비스나 ASP.NET 사이트와 같은 비인터랙티브 환경에서는 interop 클래스가 안정적으로 작동하지 않기 때문에 OLEDB를 사용해야 했습니다.
0부터 -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();
}
}
}
코드 프로젝트에 대한 기사에서 발췌했습니다.
상기 코드는 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;
}
위 함수는 두 Excel 유형(97,2003,2007) 모두에 대해 특정 Excel 파일의 시트 목록을 반환합니다.
실제 MSDN 문서에서는 찾을 수 없지만 포럼의 진행자가 말했습니다.
유감스럽게도 OLEDB는 엑셀에서처럼 시트 오더를 보존하지 않습니다.
이것은 괜찮은 해결책이 있을 것이라는 충분히 일반적인 요구사항인 것 같습니다.
짧고 빠르고 안전하고 사용할 수 있는...
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 등)은 올바른 순서대로 나열되어 있습니다.이 태그들을 읽기만 하면 됩니다.
안부 전해요
@kraepy(https://stackoverflow.com/a/19930386/2617732) 의 답변에 제공된 정보를 이용하여 아래와 같은 기능을 만들었습니다.이를 위해서는 .net 프레임워크 v4.5를 사용해야 하며 시스템에 대한 참조가 필요합니다.IO.압축.이 작업은 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_독일, 2_영국, 3_아일랜드로 명명하는 @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
업데이트: @SidHol과 BIFF에 대한 코멘트를 읽은 후 아이디어가 번쩍였습니다.코드를 통해 다음 단계를 수행할 수 있습니다.시트 이름을 같은 순서로 가져오기 위해 정말로 그렇게 하고 싶은지 모르겠습니다.코드를 통해 이 작업을 수행하기 위해 도움이 필요하면 알려주십시오.
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는 안 해봤어요.
감사해요.
에센
이것은 저에게 효과가 있었습니다.여기서 도난:엑셀 워크북의 첫 페이지 이름은 어떻게 얻습니까?
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;
이거 먹어봐요.여기 시트 이름을 순서대로 가져오는 코드가 있습니다.
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;
}
MSDN에 따르면 Excel 내부 스프레드시트의 경우 Excel 파일이 실제 데이터베이스가 아니기 때문에 작동하지 않을 수 있습니다.따라서 워크북에서 시트 이름을 시각화 순서대로 가져올 수 없습니다.
interop을 사용하여 시각적 모양에 따라 시트 이름을 가져오는 코드:
Microsoft Excel 12.0 개체 라이브러리에 참조를 추가합니다.
다음 코드는 정렬된 이름이 아니라 워크북에 저장된 실제 순서대로 시트 이름을 제공합니다.
샘플 코드:
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);
}
저는 app.xml의 순서가 시트의 순서로 보장된다는 문서를 보지 못했습니다.아마 그럴 것입니다만, OOXML 사양에 따르면 그렇지 않습니다.
반면 workbook.xml 파일에는 sheetId 속성이 포함되어 있으며, 이 속성은 1부터 시트 수까지 순서를 결정합니다.이것은 OOXML 사양에 따른 것입니다.workbook.xml은 시트의 시퀀스가 보관되는 위치로 설명됩니다.
따라서 XLSX에서 추출한 후 workbook.xml을 읽는 것이 좋습니다.app.xml이 아닙니다.docProps/app.xml 대신 xl/workbook.xml을 사용하여 다음과 같이 요소를 확인합니다.
`
<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
'programing' 카테고리의 다른 글
| string.IsNullOrEmpty(string) 대 string.IsNull 또는화이트스페이스(문자열) (0) | 2023.05.20 |
|---|---|
| WPF가 GC를 호출하는 것을 피할 수 있는 모든 방법.반사 외에 수집(2)? (0) | 2023.05.20 |
| 이클립스: 동일한 파일을 두 편집기에서 열었습니까? (0) | 2023.05.20 |
| m2e는 maven-dependency-dependency-dependencies("복사본 종속성", "복사본 종속성" 포함)를 지원하지 않습니다. (0) | 2023.05.20 |
| 요소가 'display:none'이거나 블록인 경우 jQuery를 사용하여 확인합니다. (0) | 2023.05.20 |