从excel读取数据到datatable

 /// <summary>
        /// 
        /// 从excel读取数据到datatable
        /// using System.Windows.Forms;
        /// using System.Data.OleDb;
        /// using System.Threading;
        /// </summary>
        /// <param name="hasTitle">excel量是否有头</param>
        /// <returns></returns> 

public static  DataTable GetDataFromExcelByConn(bool hasTitle = true)         {

             OpenFileDialog openFile = new OpenFileDialog();            

　　　   openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";            

　　　   openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

              openFile.Multiselect = false;            

　　　　Thread th = new Thread(new ThreadStart(delegate ()             {

                openFile.ShowDialog();

            }));            

　　　　th.TrySetApartmentState(ApartmentState.STA);  

              th.Start();

              th.Join();            

            var filePath = openFile.FileName;

            string fileType = System.IO.Path.GetExtension(filePath);

            if (string.IsNullOrEmpty(fileType)) return null;

            using (DataSet ds = new DataSet())             {

            string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" + "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +  "data source={3};",  (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);

                string strCom = " SELECT * FROM [Sheet1$]";

                using (OleDbConnection myConn = new OleDbConnection(strCon))

                using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))                 {

                    myConn.Open();

                    myCommand.Fill(ds);

                }

                if (ds == null || ds.Tables.Count <= 0) return null;

                return ds.Tables[0];

            }

        }

转载于:https://www.cnblogs.com/cgfblogs/p/9436186.html