如何使用OLEDB在Excel中获取只有excel工作表名称的列表;过滤掉在元数据中显示的非工作表 [英] How to get list of ONLY excel worksheet names in Excel using OLEDB; filter out non-worksheets that show up in metadata
问题描述
使用OLEDB从Excel电子表格获取工作表名称时出现问题。问题是当我使用GetOleDbSchemaTable时，生成的DataTable不仅仅是实际的工作表名称;它有额外的行表，我只能假设在Excel内部使用。
I have an issue getting worksheet names from an Excel spreadsheet using OLEDB. The problem is that when I use GetOleDbSchemaTable, the resulting DataTable has more than just the actual worksheet names; it has extra rows for "Tables" that I can only assume are used internally by Excel.
所以例如，如果我有一个名为myWorksheet的工作表，下面的代码可能会包含一个包含myWorksheet $，myWorksheet $ PrintTable和myWorksheet $ _的列表。只有第一个myWorksheet $记录是实际的工作表。其他的只是我不需要的垃圾。当您在元数据中查看它们时，它们看起来就像普通表，即使是TABLE的类型。
So for example, if I have a worksheet named myWorksheet, the code below might end up with a list that contains myWorksheet$, myWorksheet$PrintTable and myWorksheet$_. Only the first myWorksheet$ record is for the actual worksheet. The others are just garbage that I don't need. When you look at them in metadata they look just like regular tables, even with the type of TABLE.
现在我只需手动过滤掉任何带有$ _或$打印的名称，但谁知道什么其他Excel功能可能会使这些额外的记录以不同的格式。
For now I just manually filtered out anything with "$_" or "$Print" in the name, but who knows what other Excel feature might make these extra records turn up in a different format.
有没有人知道最好的方式来获得只有实际的工作表名称，而不是这些不是工作表的内部表？元数据有什么区别吗？
Does anyone know the best way to get ONLY actual worksheet names, and not these internal tables that aren't worksheets? Is there something in metadata that would differentiate them?
private ArrayList getXlsWorksheetNames(OleDb.OleDbConnection conn)
{
ArrayList wsList = new ArrayList();
DataTable schemaTable;
try
{
conn.Open();
schemaTable = conn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, null);
foreach (DataRow row in schemaTable.Rows)
{
//form.appendToResultsTxt("Adding worksheet to list: " + Environment.NewLine +
// "Name = " + row.Field<string>("TABLE_NAME") + "," + Environment.NewLine +
// "Type = " + row.Field<string>("TABLE_TYPE") + "," + Environment.NewLine + Environment.NewLine);
wsList.Add(row.Field<string>("TABLE_NAME"));
}
conn.Close();
}
catch (Exception ex)
{
if (this.mode == Cps2TxtUtilModes.GUI_MODE)
{
this.form.appendToResultsTxt(ex.ToString());
}
throw;
}
return wsList;
}
我在这个链接中阅读了这篇文章，但似乎没有做任何不同于我，我没有看到任何过滤的额外的非工作表表，所以微软似乎没有提供正确的答案。
I read through the article at this link, but they don't seem to be doing anything differently than I am, and I don't see any filtering out of extra non-worksheet tables, so Microsoft doesn't seem to have provided the right answer.
http://support.microsoft.com/kb/318452
而且我也查看了大量的StackOverflow，就像下面链接的线程一样有帮助，但是并不能解决这个问题。
And I've also looked around alot of StackOverflow, like at the thread from the link below which was helpful, but doesn't solve this one problem.
使用Excel OleDb获取工作表名称IN SHEET ORDER
在任何人要求之前，我也想说，我并没有真正掌握在电子表格，所以我不能告诉他们不要打开过滤或不要使用打印表。
Before anyone asks, I'd also like to say that I don't really have control over what features are used in the spreadsheet, so I can't just tell them "Don't turn on filtering" or "Don't use print tables".
任何想法都非常感激。谢谢！
Any ideas are much appreciated. Thanks!
推荐答案
问题是旧的，但是对于那些现在找到的人来说，跳过可以按照Jim发现。
The question is old, but for those who found it now, the skipping can be done as Jim found...
// skip those that do not end correctly
foreach (DataRow row in schemTable.Rows)
{
string sheetName = row["TABLE_NAME"].ToString();
if (!sheetName.EndsWith("$") && !sheetName.EndsWith("$'"))
continue;
Console.WriteLine(sheetName);
}
这是想要的或以$ code结尾的那些$ 或以 $'结尾的那些。
That is the wanted are or those that end with $ or those that end with $'.
这篇关于如何使用OLEDB在Excel中获取只有excel工作表名称的列表;过滤掉在元数据中显示的非工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
