Excel数据导入Dataset

很多情况下要将Excel数据导入数据表，做了个简单实例供大家参考，源码

ExcelUtility 包含一个返回Dataset的方法GetWorksheet

 1public class ExcelUtility
 2    {
 3        public static DataSet GetWorksheet(string fileName, string sheetName)
 4        {
 5            DataSet dataSet=new DataSet();
 6            OleDbConnection oleDbConnection = null;
 7            try
 8            {
 9                oleDbConnection =new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;IMEX=1;\"");
10                OleDbCommand selectCommand = new OleDbCommand();
11                selectCommand.Connection = oleDbConnection;
12                selectCommand.CommandText = "select * from [" + sheetName +"]";
13                selectCommand.CommandType = CommandType.Text;
14                OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommand);
15                dataSet = new DataSet();
16                adapter.Fill(dataSet);
17            }
18            catch (Exception exception)
19            {
20                oleDbConnection.Close();
21            }
22            finally
23            {                
24                oleDbConnection.Close();
25            }
26            return dataSet;
27        }
28
29    }

将导入的数据绑定到GridView测试是否绑定成功

 1protected void btnImport_Click(object sender, EventArgs e)
 2        {
 3            string fileName= string.Empty;
 4            HttpPostedFile postedFile = fileExcel.PostedFile;
 5            fileName = postedFile.FileName;
 6            ExcelUtility excelUtility = new ExcelUtility();
 7            DataSet ds= ExcelUtility.GetWorksheet(fileName, "Sheet1$");
 8
 9            gridProduct.DataSource = ds;
10            gridProduct.DataBind();
11        }

特别要注意Sheet1$中的$,不加$会抛错：“数据库引擎找不到对象'Sheet1'“