C# Winform 选择Excel文件并读取内容

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ReadExcel
{
    public partial class FrmReadExcel : Form
    {
        public FrmReadExcel()
        {
            InitializeComponent();
        }

        #region UI控制
        private void picBack_MouseLeave(object sender, EventArgs e)
        {
            picBack.BackColor = Color.FromArgb(25, 118, 210);
        }

        private void picBack_MouseMove(object sender, MouseEventArgs e)
        {
            picBack.BackColor = Color.FromArgb(237, 156, 40);
        }

        private void btnSelect_MouseLeave(object sender, EventArgs e)
        {
            btnSelect.BackColor = Color.FromArgb(25, 118, 210);
        }

        private void btnSelect_MouseMove(object sender, MouseEventArgs e)
        {
            btnSelect.BackColor = Color.FromArgb(237, 156, 40);
        }

        private void btnRead_MouseLeave(object sender, EventArgs e)
        {
            btnRead.BackColor = Color.FromArgb(25, 118, 210);

        }

        private void btnRead_MouseMove(object sender, MouseEventArgs e)
        {
            btnRead.BackColor = Color.FromArgb(237, 156, 40);
        }

        #endregion

        #region 关闭程序
        private void picBack_Click(object sender, EventArgs e)
        {
            System.Environment.Exit(0);
        }
        #endregion

        #region 选择Excel文件
        private void btnSelect_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "*.xls(Excel文件)|*.xls|*.xlsx(Excel文件)|*.xlsx";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = openFileDialog1.FileName;
                GetExcelTableNames(textBox1.Text);    
                dataGridView1.DataSource = null;
            }
        }

        public DataTable GetExcelTableNames(string strExcelPath)
        {
            try
            {
                DataTable dtExcel = new DataTable();         
                //数据表
                DataSet ds = new DataSet();
                //获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(strExcelPath);
                string strFileName = System.IO.Path.GetFileName(strExcelPath);
                //Excel的连接
                OleDbConnection objConn = null;
                switch (strExtension)
                {
                    case ".xls":
                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                        break;
                    case ".xlsx":
                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        break;
                    default:
                        objConn = null;
                        break;
                }
                if (objConn == null)
                {
                    return null;
                }
                objConn.Open();
                //获取Excel中所有Sheet表的信息
                System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);   
                //string lstSheetNames;
                List<string> lstSheetNames = new List<string>();
                comboBox1.Items.Clear();

                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string strSheetName = (string)schemaTable.Rows[i]["TABLE_NAME"];
                    if (strSheetName.Contains("$") && !strSheetName.Replace("'", "").EndsWith("$"))
                    {
                        //过滤无效SheetName完毕....
                        continue;
                    }
                    if (lstSheetNames != null && !lstSheetNames.Contains(strSheetName))     
                        strSheetName = strSheetName.Replace("$", "").Replace("'", "");
                    comboBox1.Items.Add(strSheetName);
                    lstSheetNames.Add(strSheetName);
                }
                objConn.Close();           
                comboBox1.SelectedIndex = 0;       
                return dtExcel;
            }
            catch (Exception ex)
            {   
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
                return null;
            }
        }
        #endregion

        #region 读取数据
        private void btnRead_Click(object sender, EventArgs e)
        {
            string excelFilePath = textBox1.Text;     
            string excelSheetName = comboBox1.Text;
            excelSheetName =  excelSheetName.Replace("$", "");
            DataTable tb = GetExcelTableByOleDB(excelFilePath, excelSheetName);
            dataGridView1.ClearSelection();
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.AllowUserToDeleteRows = false;
            dataGridView1.ReadOnly = true;
            dataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing;
            dataGridView1.ColumnHeadersHeight = 30;
            dataGridView1.RowTemplate.Height = 35;
            dataGridView1.DataSource = tb;
        }
        public static DataTable GetExcelTableByOleDB(string strExcelPath, string tableName)
        {
            try
            {
                DataTable dtExcel = new DataTable(); 
                //数据表
                DataSet ds = new DataSet();
                //获取文件扩展名
                string strExtension = System.IO.Path.GetExtension(strExcelPath);
                string strFileName = System.IO.Path.GetFileName(strExcelPath);
                //Excel的连接
                OleDbConnection objConn = null;
                switch (strExtension)
                {
                    case ".xls":
                        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
                        break;
                    case ".xlsx":
                        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 
                        break;
                    default:
                        objConn = null;
                        break;
                }
                if (objConn == null)
                {
                    return null;
                }
                objConn.Open();
                //获取Excel中所有Sheet表的信息
                System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet表名
                string tableName1 = schemaTable.Rows[0][2].ToString().Trim();


                //string lstSheetNames;
                List<string> lstSheetNames = new List<string>();

                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    string strSheetName = (string)schemaTable.Rows[i]["TABLE_NAME"];
                    if (strSheetName.Contains("$") && !strSheetName.Replace("'", "").EndsWith("$"))
                    {
                        //过滤无效SheetName完毕....
                        continue;
                    }
                    if (lstSheetNames != null && !lstSheetNames.Contains(strSheetName))
                        //MessageBox.Show(strSheetName);
                        lstSheetNames.Add(strSheetName);
                }
                // MessageBox.Show(lstSheetNames[0]);

                string strSql = "select * from [" + tableName + "$]";
                // string strSql = "select 分院名称,城区,地址 from [" + tableName + "$]";
                //获取Excel指定Sheet表中的信息
                OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
                OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
                myData.Fill(ds, tableName);//填充数据
                objConn.Close();
                //dtExcel即为excel文件中指定表中存储的信息
                dtExcel = ds.Tables[tableName];
                return dtExcel;
            }
            catch (Exception ex)
            {  
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
                return null;
            }
        }
        #endregion


   
    }
}

备注:如果程序提示未在本地计算机上注册“microsoft.ACE.oledb.12.0”!!!

解决办法：

1、下载对应组件安装：https://www.microsoft.com/zh-CN/download/details.aspx?id=13255

注意：要选择AccessDatabaseEngine.exe，而不是AccessDatabaseEngine64.exe，否则还会提示错误！！！