从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];
}
}