Używanie programu Excel OleDb do pobierania nazw arkuszy w kolejności arkuszy
Używam OleDb do czytania ze skoroszytu Excela 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__/
W takim razie muszę zdobyć słownik
1="GERMANY",
2="UK",
3="IRELAND"
Próbowałem użyć OleDbConnection.GetOleDbSchemaTable()
, i to daje mi listę nazwisk, ale sortuje je alfabetycznie. Sortowanie Alfa oznacza, że nie wiem, do którego numeru arkusza odpowiada dana nazwa. Więc Ja get;
GERMANY, IRELAND, UK
Który zmienił kolejność UK
i IRELAND
.
Powodem, dla którego muszę je posortować, jest to, że muszę pozwolić użytkownikowi wybrać zakres danych według nazwy lub indeksu; może on 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ł skorzystać z klasy Office interop, byłoby to proste. Niestety nie mogę, ponieważ zajęcia interop nie działają niezawodnie w środowiska nieinteraktywne, takie jak usługi windows i ASP.NET strony, więc musiałem użyć OLEDB.11 answers
Nie można znaleźć tego w rzeczywistej dokumentacji MSDN, ale moderator na forum powiedział
Obawiam się, że OLEDB nie zachowuje kolejności arkuszy tak jak w Excelu
Nazwy arkuszy Excel w kolejności arkuszy
Wydaje się, że byłoby to na tyle powszechne, że byłoby godne obejście.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-26 14:45:22
Czy nie można po prostu przeszukiwać arkuszy od 0 do liczby imion -1? w ten sposób powinieneś je ustawić we właściwej kolejności.
Edit
Zauważyłem w komentarzach, że istnieje wiele obaw dotyczących używania klas Interop do pobierania nazw arkuszy. Oto przykład użycia OLEDB do ich pobrania:
/// <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ębniony z Artykuł na CodeProject.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-10-02 09:50:03
Ponieważ powyższy kod nie obejmuje procedur wyodrębniania listy nazwy arkusza dla programu Excel 2007, następujący kod będzie miał zastosowanie zarówno do programu Excel(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ę arkusza w konkretnym pliku excel dla obu typów excel (97,2003,2007).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-29 14:09:45
Inny sposób:
Plik xls(x) jest tylko zbiorem*.pliki XML przechowywane w *.Pojemnik 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 = worksheets). Nazwy tabel (Tabelle3 itd.) są w odpowiedniej kolejności. Wystarczy przeczytać te Tagi;)
Pozdrawiam
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-12 13:23:30
Stworzyłem poniższą funkcję korzystając z informacji podanych w odpowiedzi od @kraeppy ( https://stackoverflow.com/a/19930386/2617732 ). wymaga to użycia.NET framework v4. 5 i wymaga odniesienia do kompresji System. IO. działa to tylko dla plików xlsx, a nie dla 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);
}
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:03:05
Jest krótki, szybki, bezpieczny i użyteczny...
public static List<string> ToExcelsSheetList(string exceladdress)
{
List<string> sheets = new List<string>();
using (OleDbConnection connection = new OleDbConnection((exceladdress.TrimEnd().ToLower().EndsWith("x")) ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + exceladdress + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"
: "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + exceladdress + "';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;
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-03 15:44:01
Podoba mi się pomysł @deathApril, aby nazwać arkusze jako 1_Germany, 2_UK, 3_ireland. Mam też twój problem, aby zrobić tę zmianę nazwy dla setek arkuszy. Jeśli nie masz problemu ze zmianą nazwy arkusza, możesz użyć tego makra, aby zrobić to za Ciebie. Zmiana nazw wszystkich arkuszy zajmie mniej niż sekundy. niestety ODBC, OLEDB zwraca nazwę arkusza order by asc. Nie ma zastępstwa. Musisz albo użyć COM, albo zmienić nazwę, aby być w spokój.
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: Po przeczytaniu komentarza @ SidHoland dotyczącego BIFF-a pojawił się pomysł. Następujące 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
UPDATE: Inne rozwiązanie - NPOI może być tu 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-22 14:43:54
To mi pomogło. Jak uzyskać nazwę pierwszej strony skoroszytu 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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-03 16:02:42
Zgodnie z MSDN, w przypadku arkuszy kalkulacyjnych wewnątrz Excela może to nie działać, ponieważ pliki Excela nie są prawdziwymi bazami danych. Tak więc nie będzie można uzyskać nazwy arkuszy w kolejności ich wizualizacji w skoroszycie.
Kod, aby uzyskać nazwę arkuszy zgodnie z ich wyglądem wizualnym za pomocą interop:
Dodaj odniesienie do biblioteki obiektów Microsoft Excel 12.0.
Następujący kod da nazwę arkuszy w rzeczywistej kolejności przechowywanej w skoroszycie, a nie posortowaną nazwę.
Próbka 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);
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-25 11:53:09
Nie widzę żadnej dokumentacji, która mówi o kolejności w aplikacji.XML jest gwarantowana kolejność arkuszy. Prawdopodobnie tak, ale nie zgodnie ze specyfikacją OOXML.
Skoroszyt.z kolei plik xml zawiera atrybut sheetId, który określa sekwencję-od 1 do liczby arkuszy. Jest to zgodne ze specyfikacją OOXML. skoroszyt.xml jest opisany jako miejsce, w którym przechowywana jest sekwencja arkuszy.
Więc czytanie skoroszytu.xml po nim jest wyodrębniony z XLSX byłby moją rekomendacją. NIE app.xml. Zamiast docProps / app.xml, użyj XL / skoroszytu.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>
`
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-08-02 15:00:24
Spróbuj tego. Oto kod, aby uporządkować nazwy 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;
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-06 07:51:39