OLED连接读取Excel到DataSet中的方法

我们常用的ERP系统，往往要用到读取excel表格到程序中进行操作，以下是采用OLEDB连接的方法读取Excel到DS的方法：

		/// <summary>
		/// OLED连接读取Excel到DataSet中的方法
		/// </summary>
		/// <param name="Path">Excel路径</param>
		/// <returns></returns>
		public DataSet ReadExcelToDS(string Path)
        {
			//获取Excel后缀，选择不同的连接方式
			string connstr = null;
			if (File.Exists(Path))
			{
				FileInfo fileInfo = new FileInfo(Path);
				string extension = fileInfo.Extension;
				switch (extension)
				{
					case ".xls":
						connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
						break;
					case ".xlsx":
						connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
						break;
					default:
						connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
						break;
				}
			}
			//创建打开excel的Oledb连接
			try
            {
				OleDbConnection conn = null;
                conn = new OleDbConnection(connstr);
                conn.Open();
                //获取工作簿名称
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string tableName = schemaTable.Rows[0].ItemArray[2].ToString().Trim();
                //填充dataset
                string sql = "select * from " + tableName;
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, "table1");
				conn.Close();
                return ds;
            }
            catch (Exception e)
            {
				conn.Close();
                throw e;
			}
        }
		//调用方法：
		 DataSet ds = ReadExcelToDS('D://1.xls');