C# 怎么读取execl所有表名

[大正他爹]

现用：
GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
读取，如果有两个表：表1，表2
用此方法返回表1，表1$,表1_,表2，表2$,表2_

请问：怎么返回表1，表2

[flowfog]

string sFileName = @"c:\a.xls ";
object missing=Missing.Value;
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Open(sFileName,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
for(int i=0;i <excelApp.Workbooks[1].Worksheets.Count;i++)
{
Excel.Worksheet ws = (Excel.Worksheet)excelApp.Workbooks[1].Worksheets[i+1];
string sSheetName = ws.Name;
MessageBox.Show(sSheetName);
}

[flowfog]

需要引用EXCEL这个DLL

[大正他爹]

恩。用oledb没有办法解决吗？

[Jordan_Hill]

System.Data.DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow dr in dt.Rows)
{
string sheetName = dr["TABLE_NAME"].ToString(); //这里就是取得表的名字
}

[CraxyMouse]

 conn = new OleDbConnection(tempConString);
                conn.Open();
                lishSheets.Items.Clear();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    lishSheets.Items.Add(dt.Rows[i][2]);
                }
                conn.Close();
                if (dt.Rows.Count < 1)
                {
                    throw new Exception("无法获取Excel文件中Sheets!!");
                }

[大正他爹]

有好方法的尽快来讨论

[longhuadaxia]

将EXCEL中每一个sheet中的内容作为一个datatable，将所有的datatable存入一个dataset中，返回一个dataset就可以了。代码如下：


/从Excel中读取数据并放到DataSet中

public DataSet GetExcelSheets(string path)
{
SetExcelRegistry();

OleDbConnection objConn = null;
System.Data.DataTable dt = null;
DataSet ds = new DataSet();

try
{
string strQuery = "";
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path +
";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";

objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string str = dt.TableName;

if (dt == null)
{
//lg.LogError(" 文件中无数据或者读取不成功！！", logpath);
//lg.closeLog();
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString().Replace("'", "");
i++;
}

string strCom = string.Empty;
for (int j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
strQuery = excelSheets[j].ToString();
if (strQuery.EndsWith("$'") || strQuery.EndsWith("$"))
{
strCom = " SELECT * FROM [" + strQuery + "A:I]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, objConn);

myCommand.Fill(ds, strQuery);
}
}
objConn.Close();

}
catch(Exception ex)
{
throw ex;
}
return ds;
}

[longhuadaxia]

表名就是datatable的名字啦

[longhuadaxia]

string sheetname = ds.datatable[i].TableName;

[宇峰科技]

都有了

[wuyq11]

GetOleDbSchemaTable取到的表的信息还包括excel的命名区域；
因为excel是通过dts导出生成的，导出时同时生成了命名区域
schemaTableView = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null,null,null,"Table"});
if (schemaTableView!= null)
{
rsResult = schemaTableView.CreateDataReader();
if (rsResult != null)
{
alsheetName = new ArrayList();
if (alsheetName != null)
{
while (rsResult.Read())
{

int i=rsResult.GetString(2).IndexOf('$');
if (rsResult.GetString(2).Substring(i).Length == 1)
{
alsheetName.Add(rsResult.GetString(2)); // Table Name;
//alsheetName.Add(rsResult.GetString(2).Substring(0, i)); // Table Name;

}


}
}
rsResult.Close();
rsResult = null;
}
schemaTableView = null;
}

[大正他爹]

谢谢各位！