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

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

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

publicclassOleDbHelper{//创建连接字符串privatestaticstring 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>publicstaticDataSetGetDataSet(string sql,string path){OleDbConnection conn =newOleDbConnection(string.Format(connString, path));OleDbCommand cmd =newOleDbCommand(sql, conn);OleDbDataAdapter da =newOleDbDataAdapter(cmd);DataSet ds =newDataSet();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的工作表。

publicclassStudent{publicstring StudentName {get;set;}publicstring Gender {get;set;}publicDateTime Birthday {get;set;}publicstring Address {get;set;}}

存放数据的对象

publicList<Student>GetStudentFromExcel(string path){List<Student> stuList =newList<Student>();string sql ="select 姓名,性别,出生日期,家庭住址 from [Student$]";DataTable dt = OleDbHelper.GetDataSet(sql, path).Tables[0];foreach(DataRow row in dt.Rows){
                stuList.Add(newStudent(){
                    StudentName = row["姓名"].ToString(),
                    Gender = row["性别"].ToString(),
                    Birthday = Convert.ToDateTime(row["出生日期"]),
                    Address = row["家庭住址"].ToString()});}return stuList;}

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

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

4、效果展示

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

publicclassStudent{publicstring StudentName {get;set;}publicstring Gender {get;set;}publicDateTime Birthday {get;set;}publicstring Address {get;set;}publicstring StudentIdNo {get;set;}}

publicList<Student>GetStudentFromExcel(string path){List<Student> stuList =newList<Student>();string sql ="select * from [Student$]";DataTable dt = OleDbHelper.GetDataSet(sql, path).Tables[0];foreach(DataRow row in dt.Rows){
                stuList.Add(newStudent(){
                    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)

方法进行上面修改，显示效果如下图：

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

publicFrmMain(){InitializeComponent();this.dataGridView1.AutoGenerateColumns =false;//禁止自动生成列}

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