C#上位机采用数据库操作方式对Excel或WPS表格进行读取操作

C#采用数据库操作方式对Excel或WPS表格进行读取操作

1、创建连接字符串并编写一个进行数据库操作的方法

    public class OleDbHelper
    {
        //创建连接字符串
        private static string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};" +
            "Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";

        /// <summary>
        /// 返回一个DataSet结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="path">excel文件路径</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, string path)
        {
            OleDbConnection conn = new OleDbConnection(string.Format(connString, path));
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);

            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

2、编写SQL语句进行数据的查询并返回对象列表

我这里是从Excel读取指定列的数据string sql = "select 姓名,性别,出生日期,家庭住址 from [Student$]";
如果需要读取全部的数据string sql = "select * from [Student$]";
**[Student$]**这个表示这个Excel工作簿的名称为Student的工作表。

    public class Student
    {
        public string StudentName { get; set; }
        public string Gender { get; set; }
        public DateTime Birthday { get; set; }
        public string Address { get; set; }
    }

存放数据的对象

        public List<Student> GetStudentFromExcel(string path)
        {
            List<Student> stuList = new List<Student>();
            string sql = "select 姓名,性别,出生日期,家庭住址 from [Student$]";

            DataTable dt = OleDbHelper.GetDataSet(sql, path).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                stuList.Add(new Student()
                {
                    StudentName = row["姓名"].ToString(),
                    Gender = row["性别"].ToString(),
                    Birthday = Convert.ToDateTime(row["出生日期"]),
                    Address = row["家庭住址"].ToString()
                });
            }

            return stuList;
        }

3、将对象列表显示在界面上

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            DialogResult result = openFileDialog.ShowDialog();
            string path = openFileDialog.FileName;
            this.dataGridView1.DataSource = objExcelRW.GetStudentFromExcel(path);
        }

4、效果展示

5、提示：如果需要DataGridView的列数少于需要展示的对象的属性数量，DataGridView会自动创建新的列。

    public class Student
    {
        public string StudentName { get; set; }
        public string Gender { get; set; }
        public DateTime Birthday { get; set; }
        public string Address { get; set; }
        public string StudentIdNo { get; set; }
    }

        public List<Student> GetStudentFromExcel(string path)
        {
            List<Student> stuList = new List<Student>();
            string sql = "select * from [Student$]";

            DataTable dt = OleDbHelper.GetDataSet(sql, path).Tables[0];

            foreach (DataRow row in dt.Rows)
            {
                stuList.Add(new Student()
                {
                    StudentName = row["姓名"].ToString(),
                    Gender = row["性别"].ToString(),
                    Birthday = Convert.ToDateTime(row["出生日期"]),
                    Address = row["家庭住址"].ToString(),
                    StudentIdNo = row["身份证号"].ToString()
                });
            }
            return stuList;
        }

将Student类和public List<Student> GetStudentFromExcel(string path)方法进行上面修改，显示效果如下图：

如果此时还是想只显示自己所设定的列，可以直接在界面构造方法里面添加

        public FrmMain()
        {
            InitializeComponent();

            this.dataGridView1.AutoGenerateColumns = false;//禁止自动生成列
        }

如果出现报错可以查看“未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序”的解决方案