如何把excel文档中的内容读取到dataset中呢?我查询了一些资料,并亲自试用,以下代码均通过vs2010编译:
以下是实现该功能的一个函数,直接调用即可:
函数的参数说明:filepath是excel所在的全路径,tablename是EXCEL文档里的一个表名,一般为sheet1
private DataSet xsldata(string filepath, string tablename)
{
string strCon = "";
string filemode = filepath.Substring(filepath.Length - 4, 4);
if (filemode == ".xls")
{
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties='Excel 8.0;IMEX=1'"; //excel2003
}
else if (filemode == "xlsx")
{
strCon = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + filepath + ";" + "Extended Properties='Excel 12.0;IMEX=1'"; //excel2007
}
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT * FROM [" + tablename + "$]";
Conn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
DataGridView dataGridView = new DataGridView();
myCommand.Fill(ds, "[" + tablename + "$]");
dataGridView.DataSource = ds.Tables[0];
Conn.Close();
return ds;
}