导入本地Excel到DataSet中

        /// <summary>
        /// 导入本地Excel到DataSet中
        /// </summary>
        /// <param name="strFileSourse">文件的路径和文件全名,含扩展名</param>
        /// <returns></returns>
        public DataSet ExcelToDataSet(string strFileSourse)
        {
            DataSet ds = new DataSet();
            //Excel数据源(兼容03/10)
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileSourse + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            //连接
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();

                //获取Excel中所有的sheet
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                //把Excel中所有的sheet数据读到一个Table中
                for (int i = 0; i < dtSheet.Rows.Count; i++)
                {
                    string strSheetName = dtSheet.Rows[i]["TABLE_NAME"].ToString();
                    OleDbDataAdapter OleDa = new OleDbDataAdapter("select * from [" + strSheetName + "]", conn);
                    OleDa.Fill(ds, "TableName");
                    conn.Close();
                }
            }
            catch (Exception)
            {
                //System.Windows.Forms.MessageBox.Show(e.ToString());
                throw;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return ds;
        }
posted @ 2017-10-20 09:58  TTonlyV5  阅读(144)  评论(0编辑  收藏  举报