winfrom选择Excel文件并转成DataTable

winfrom选择Excel文件并转成DataTable

直接上代码：

        //导入
        private void btnImport_Click(object sender, EventArgs e)
        {
            string fileName = string.Empty;
            fileName = GetFileName();
            if (fileName == string.Empty) return;
            DataTable tbTF = InputFromExcel(fileName, "Sheet1");
        
        }
        //选择Excel文件
        private string GetFileName()
        {
            string fileName = string.Empty;
            {
                OpenFileDialog dlgOpen = new OpenFileDialog();
                dlgOpen.Filter = "表格文件 (*.xls)|*.xls";  
                if (dlgOpen.ShowDialog(this) == DialogResult.Cancel)
                {
                    return string.Empty;
                }
                fileName = dlgOpen.FileName;
                dlgOpen = null;
            }
            return fileName;
        }
        //读取Excel数据
        public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
        {
            if (!File.Exists(ExcelFilePath))
            {
                throw new Exception("Excel文件不存在！");
            }

            //如果数据表名不存在，则数据表名为Excel文件的第一个数据表
            ArrayList TableList = new ArrayList();
            TableList = GetExcelTables(ExcelFilePath);

            if (TableName.IndexOf(TableName) < 0)
            {
                TableName = TableList[0].ToString().Trim();
            }

            DataTable table = new DataTable();
            OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 12.0");
            OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

            try
            {
                if (dbcon.State == ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                adapter.Fill(table);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (dbcon.State == ConnectionState.Open)
                {
                    dbcon.Close();
                }
            }
            return table;
        }
        //读取Excle文件
        public static ArrayList GetExcelTables(string ExcelFileName)
        {
            DataTable dt = new DataTable();
            ArrayList TablesList = new ArrayList();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Ace.OleDb.12.0;Extended Properties=Excel 12.0;Data Source=" + ExcelFileName))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }

                    //获取数据表个数
                    int tablecount = dt.Rows.Count;
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }

就这些，复制粘贴直接用