Question

c#读取excel文件每个表的表名，只有3个表为何得到7个表名

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace 测试
{
class Program
{
static void Main(string[] args)
{
string tableName = null;
string excelFileName = @"E:\VS\CS\线的延长\2009-2010流速线计算.xls";
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
"OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
int a = dt.Rows.Count;
for (int i = 0; i < a; i++) //循环取得所有表名
{
tableName = dt.Rows[i][2].ToString().Replace("$","");
Console.WriteLine(tableName + i + a);
Console.ReadKey();
//sheetName[i] = tableName;
//sheetnamelist.Add(tableName );
}
}
}

}
}
}
代码全在上面了，在试验用的excel文件里只有三个表，可是得到的结果却是7个表名，不知道应该怎么解决？求大神帮帮忙啊~~~

Answer 1

这个不是获取表名，用C#正确读取表名的方法如下：
一、方式一：
public string GetExcelFirstTableName(string excelFileName)
{
string tableName = null;
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0\";Data Source=" + excelFileName))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
tableName += dt.Rows[i][2].ToString().Trim() + ";";
}
}
}
return tableName;
}
2、方式二：
#region 获取Excel内的sheet表名
public static string[] GetSheetsName(string pExcelAddress)
{
try
{
string[] vSheetsName;
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0\";Data Source=" + pExcelAddress))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
vSheetsName = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vSheetsName[i] = dt.Rows[i][2].ToString().Replace("$","");
}
return vSheetsName;
}
}
catch (Exception vErr)
{
return null;
}
}

3、过滤掉垃圾sheet文件名称

/// <summary>
/// 获取指定Excel内Sheet集合
/// </summary>
/// <param name="pFilePath"></param>
/// <param name="pOutInfo"></param>
/// <returns></returns>
public static string[] GetExcelSheetNames(string pFilePath, out string pOutInfo)
{
string vOutInfo = string.Empty;
List<string> vList = new List<string>();
try
{
string strConn = string.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=2'", pFilePath);
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
string vName = string.Empty;
List<string> pOUTPres = new List<string>();
#region 填充 pOUTPres 数组,对 OUTPres 特殊字符处理
for (int i = 0; i < sheetNames.Rows.Count; i++)
{
if (sheetNames.Rows[i][2].ToString().Trim().Contains("OUTPres") && i > 0)
{
if (sheetNames.Rows[i][2].ToString().Trim().Contains(sheetNames.Rows[i - 1][2].ToString().Trim() + "OUTPres"))
{
continue;
}
}
pOUTPres.Add(sheetNames.Rows[i][2].ToString().Trim());
}
#endregion
string[] vSheets = pOUTPres.ToArray();
#region 对特殊字符进行规范处理
string pSheetName = string.Empty;
for (int i = 0; i < vSheets.Length; i++)
{
string pStart = vSheets[i].Substring(0, 1);
string pEnd = vSheets[i].Substring(vSheets[i].Length - 1, 1);
if (pStart == "'" && pEnd == "'")
{
vSheets[i] = vSheets[i].Substring(1, vSheets[i].Length - 2);
}
Char[] pChar = vSheets[i].ToCharArray();
pSheetName = string.Empty;
for (int j = 0; j < pChar.Length; j++)
{
if (pChar[j].ToString() == "'" && pChar[j + 1].ToString() == "'")
{
pSheetName += pChar[j].ToString();
j++;
}
else
{
pSheetName += pChar[j].ToString();
}
}
vSheets[i] = pSheetName;
}
#endregion
#region 当最后字符为$时移除
for (int i = 0; i < vSheets.Length; i++)
{
pSheetName = vSheets[i];
if (pSheetName.Substring(pSheetName.Length - 1, 1) == "$")
{
vSheets[i] = pSheetName.Substring(0, pSheetName.Length - 1);
}
}
#endregion
#region 移除重复的Sheet名(因为特殊原因，通过这个方法获取的Sheet会有重名)
for (int i = 0; i < vSheets.Length; i++)
{
if (vList.IndexOf(vSheets[i].ToLower()) == -1)
{
vList.Add(vSheets[i]);
}
}
#endregion
#region 等sheet名称包含有 FilterDatabase 或 Sheet1$Print_Titles 这样字符时，要去掉。[注：这是由于特殊Excel的原因造成的]
List<string> ptList = new List<string>();
for (int j = 0; j < vList.Count; j++)
{
ptList.Add(vList[j]);
}
for (int i = 0; i < ptList.Count; i++)
{
if (ptList[i].ToString().Contains("FilterDatabase") || ptList[i].ToString().Contains("Print_Titles")
|| ptList[i].ToString().Contains("_xlnm#Database") || ptList[i].ToString().Contains("Print_Area")
|| ptList[i].ToString().Contains("_xlnm.Database") || ptList[i].ToString().Contains("ExternalData")
|| ptList[i].ToString().Contains("DRUG_IMP_STOCK") || ptList[i].ToString().Contains("Sheet1$zy")
|| ptList[i].ToString().Contains("Sheet1$xy") || ptList[i].ToString().Contains("data_xy_zcy")
|| ptList[i].ToString().Contains("Results"))
{
vList.Remove(ptList[i].ToString());
}
}
#endregion
#region 通过读取数据，验证sheet表是否确实存在
if (vList.Count > 1)
{
List<string> pCheckList = new List<string>();
for (int j = 0; j < vList.Count; j++)
{
pCheckList.Add(vList[j]);
}
conn.Open();
OleDbCommand pComm = new OleDbCommand();
pComm.Connection = conn;
for (int i = 0; i < pCheckList.Count; i++)
{
try
{
pComm.CommandText = string.Format(" select count(*) from [{0}$] where 1=0 ", pCheckList[i]);
pComm.ExecuteNonQuery();
}
catch (Exception ppErr)
{
if (ppErr.Message.Contains("Microsoft Access 数据库引擎找不到对象"))
{
vList.Remove(pCheckList[i].ToString());
}
}
}
conn.Close();
}
#endregion
}
catch (Exception vErr)
{
vOutInfo = vErr.Message;
}
pOutInfo = vOutInfo;
return vList.ToArray();
}

Answer 2

你这个明明是行数，怎么是表名啊
dt.Rows.Count; //row不是行数吗

表名应该是sheets.count

追问

编译器里面没有sheets啊，而且行数也不对啊，我的表里有32行，结果一直是7，很是不解啊

追答

DataTable 就是表格啊，我找了一下我原来写过的相关代码参考下吧，功能可能不一样

Microsoft.Office.Interop.Excel.Application excel=null;
Microsoft.Office.Interop.Excel.Workbook wb=null;
excel = new Microsoft.Office.Interop.Excel.Application();

wb = excel.Application.Workbooks.Add(true); //这里wb就是workbook，里面与wb.sheets.count应该就是sheet的数量
string filename = ti.path +"\\" +id.ToString() + ".xlsx";

wb.SaveAs(filename);
excel.Visible = true;
excel.Cells[1, 1] = "设备ID";