如何返回单个excel表格表 [英] How to return single excel sheet table

问题描述

我有一个名为myexcel.xsls文件的excel文件，该excel文件中有10个工作表，名为summary，detail，kpi，orders等。在excel工作表摘要中有5个表，其名称为Table1，Table2 ，表3，表4，表5.创建表格就像我想将Table1记录放入数据表中那样为此我编写了以下代码

i have a excel file named as myexcel.xsls file and there are 10 sheets in that excel file named as summary,detail,kpi,orders etc etc.In excel sheet summary there are 5 tables in it named as Table1,Table2,Table3,Table4,Table5.Tables are created like I want to get Table1 records into the Data Table so for that i've written a following code

public DataTable GetExcelFileAsDataTable(string FileName)
{
DataTable dt = new DataTable();
OleDbConnection objConn = null;
try
{
objConn = new OleDbConnection(GetConnectionString(FileName));
OleDbCommand objCmd = new OleDbCommand("Select * From [Summary$]", objConn);
SPSecurity.RunWithElevatedPrivileges(delegate()
{

objConn.Open();
});

OleDbDataReader dr = objCmd.ExecuteReader();
if (dr != null)
{
dt.Load(dr);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
objConn.Close();
}

return dt;


}

但是当我运行该代码时，它会返回摘要中的所有数据sheet表示该摘要表中的所有行。



任何人都可以告诉我如何才能获得Table1的单个表或数据集中的所有表表名为

But when i run that code it returns all the data from the summary sheet means all the rows from that summary sheet.

Can anyone tell me how i can only get single table which is Table1 or all the tables in the data set with each table with it's name

推荐答案

，objConn）;
SPSecurity.RunWithElevatedPrivileges（ delegate （）
{

objConn.Open（）;
}）;

OleDbDataReader dr = objCmd.ExecuteReader（）;
if （dr！= null ）
{
dt.Load（ dr）;
}
}
catch （exception ex）
{
throw ex;
}
finally
{
objConn.Close（） ;
}

return dt;


}

", objConn); SPSecurity.RunWithElevatedPrivileges(delegate() { objConn.Open(); }); OleDbDataReader dr = objCmd.ExecuteReader(); if (dr != null) { dt.Load(dr); } } catch (Exception ex) { throw ex; } finally { objConn.Close(); } return dt; }

但是，当我运行该代码时，它会从摘要表中返回所有数据，这意味着该摘要表中的所有行。



任何人都可以告诉我如何只能获得Table1的单个表或数据集中的所有表，每个表的名称为

But when i run that code it returns all the data from the summary sheet means all the rows from that summary sheet.

Can anyone tell me how i can only get single table which is Table1 or all the tables in the data set with each table with it's name

如果我理解正确，你需要为每个工作表单独查询。首先查询Table1然后Table2，依此类推。您还需要事先知道工作表名称，因为它们是在查询的FROM子句中定义的。



如果需要，可以获取工作表名称，以便用于示例 C＃ - 检索Excel工作簿表名称。 [ ^ ]

If I understood you correctly, you need to do a separate query for each worksheet. First query Table1 then Table2 and so on. Also you need to know the sheet names beforehand since they are defined in the FROM clause of the query.

If you need, to get the sheet names you can use for example C# - Retrieve Excel Workbook Sheet Names.[^]

应该是：

should be:

"Select * From [Table1

这篇关于如何返回单个excel表格表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！