public DataSet importExcelToDataSet(string filepath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1;/";";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tableName = sheetNames.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBoxEx.Show("该Excel文件的工作表正在使用中,请关闭后重试!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + tableName + "]", conn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet);
}
catch (Exception ex)
{
MessageBoxEx.Show("该Excel文件的工作表导入错误," + ex.Message);
}
finally
{
conn.Close();
}
return myDataSet;
}