在项目中经常涉及到基础数据的导入的问题,很多时候就需要从Excel中获取数据,下面提供一个从Excel文件提取数据到DataSet中方法:

/// <summary>
        /// 根据excel的文件的路径提取其中表的数据
        /// </summary>
        /// <param name="Path">Excel文件的路径</param>
        /// <returns></returns>
        public static DataSet GetDataFromExcel(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn;
            DataSet ds = new DataSet();
            try
            {
                conn = new OleDbConnection(strConn);
                conn.Open();
                //返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                string[] strTables = new string[dtSheetName.Rows.Count];
                int k;
                string temp;
                string[] ids = new string[dtSheetName.Rows.Count];
                for (k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    temp = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    strTables[k] = temp.Trim('$');
                    strTableNames[k] = temp;
                }

                OleDbDataAdapter myCommand = null;
                for (k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    DataTable dt = new DataTable();
                    //从指定的表明查询数据,可先把所有表明列出来供用户选择
                    string strExcel = "select * from [" + strTableNames[k] + "]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(dt);

                    dt.TableName = strTables[k];
                    ds.Tables.Add(dt);

                }
                conn.Close();
            }
            catch (Exception ex)
            {
                //写错误日志...
                string strOuput = string.Format("获取文件中数据失败,错误信息:{0},异常{1}\n",ex.Message,ex.InnerException);
                //将信息写入到日志输出文件
                DllComm.TP_WriteAppLogFileEx(DllComm.g_AppLogFileName, strOuput);

            }
            return ds;
        }