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”提供程序”的解决方案