我搜索互联网,这和能不能真正找到喜欢它的一个问题。每个人都在寻找一种方式来导入某个表中的Excel文件,但我想要的是把文件中的所有表导入到数据表
的在数据集
不知道工作表名称。
I've searched internet for this and couldn't really find a question like it. Everyone was looking for a way to import an individual sheet in the excel file but what I want is to import all the sheets in the file to DataTable
's in DataSet
without knowing the sheet names.
我没有做很多事情与Excel之前。这是一个样本和部分工作code,我在互联网上找到,它只是分析给定表名称:
I've not done much things with Excel before. This a sample and partially working code I've found on the internet and it only parses the given sheet name:
public static DataSet Parse(string fileName, string workSheetName)
{
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
string query = string.Format("SELECT * FROM [{0}$]", workSheetName);
DataSet data = new DataSet();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(data);
}
return data;
}
在code以上,如你所见,workSheetName应该在这样的查询可以通过知道去哪里找的到进口。就我而言,我希望它遍历所有的表,无论他们被命名为喜欢和它们导入到单个数据表
是一个数据集。
In the code above, as you see, workSheetName should be passed in so the query can know where to look at to import. In my case, I want it to traverse all the sheets no matter what they are named like and import them to individual DataTable
's of a DataSet
.
因此,在本质上,最后一件事将是一个数据集
,其中每个数据表
持有的行中的每个片导入的文件。
So in essence, the final thing will be a DataSet
in which each DataTable
holds rows for each sheet in the imported file.
推荐答案
这是一个code我想出了和它的作品完美,但我看到别人已经添加了一个答案:
This is a code I came up with and it works perfect but I saw someone else already added an answer:
static DataSet Parse(string fileName)
{
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
DataSet data = new DataSet();
foreach(var sheetName in GetExcelSheetNames(connectionString))
{
using (OleDbConnection con = new OleDbConnection(connectionString))
{
var dataTable = new DataTable();
string query = string.Format("SELECT * FROM [{0}]", sheetName);
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(dataTable);
data.Tables.Add(dataTable);
}
}
return data;
}
static string[] GetExcelSheetNames(string connectionString)
{
OleDbConnection con = null;
DataTable dt = null;
con= new OleDbConnection(connectionString);
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheetNames = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheetNames[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheetNames;
}
相关推荐
最新文章