如何获取名单只有使用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 $记录是实际的工作表。其他人都只是垃圾，我不需要。当您在元数据看他们，他们看起来就像普通的表，甚至表的类型。
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
和我也看了看周围很多计算器，如从下面这是有帮助的链接线，但并不能解决这一问题。
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.
有人问之前，我还想说，我真的没有在什么功能在电子表格中使用的控制，这样我就可以' ŧ只是告诉他们不要打开过滤或不使用打印表格。
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!
推荐答案
现在的问题是旧的，但对于那些谁发现，现在，跳绳可以做到吉姆发现..
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);
}
这是想都还是那些以结束$ 或那些与 $ 结束。
That is the wanted are or those that end with $ or those that end with $'.
这篇关于如何获取名单只有使用OLEDB Excel中的Excel工作表的名称;过滤非工作表中的元数据显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
