从本地读取Excel文件并转换成DataTable格式

1. 选取Excel文件

         OpenFileDialog open = new OpenFileDialog();

         open.InitialDirectory = "窗口打开位置";        //注意地址的斜杠为\\

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

2.读取并转换

if (open.ShowDialog() == DialogResult.OK)

{

String fName = open.FileName;

try

    {

     string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";

         using (OleDbConnection conn = new OleDbConnection(strconn))

         {

           conn.Open();

               DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

               string firstNmae = sheetsName.Rows[0][2].ToString();

               string sql = string.Format("select * from [{0}]", firstNmae);

               OleDbDataAdapter ada = new OleDbDataAdapter(sql, strconn);

               DataSet set = new DataSet();

               ada.Fill(set);

               DataGridView.DataSource = set.Tables[0];

          }

    }

    catch (Exception ex)

    {

          Console.WriteLine(ex);

    }

}