1.本机环境office2016
2.使用访问代码:
using System.Data;
using System.Data.OleDb;
namespace study
{
/// <summary>
/// 访问Access数据库通用类
/// </summary>
public class OleDbHelper
{
//Excel 2003(引擎有区别)
//private static string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0";
//创建连接字符串(Access数据库版本不同,字符串连接有区别,现在2010,适合Excel2007以后的版本)
private static string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
/// <summary>
/// 将指定路径的excel导入到数据集中(读取excel必备的方法)
/// </summary>
/// <param name="sql"></param>
/// <param name="path"></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 (System.Exception ex)
{
throw new System.Exception("数据集查询失败:" + ex.Message);
}
}
}
}
3. 封装的访问方法:
using System;
using System.Collections.Generic;
using System.Data;
namespace study
{
/// <summary>
/// 从excel导入数据(需要根据不同的excel表写不同的方法)
/// </summary>
public class ImportDataFromExcel
{
/// <summary>
/// 将选择的Excel数据表查询后封装成对象集合
/// </summary>
/// <param name="path">Excel文件路径</param>
/// <returns></returns>
public List<Student> GetStudentsByExcel(string path)
{
List<Student> list = new List<Student>();
string sql = "select * from [Sheet1$]";
DataSet ds = OleDbHelper.GetDataSet(sql, path);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
list.Add(new Student()
{
Id = Convert.ToInt32(row["学号"]),
Name1 = row["姓名"].ToString(),
Gender = row["性别"].ToString(),
Address = row["住址"].ToString(),
BirthDay = row["出生日期"].ToString()
}) ;
}
return list;
}
}
}
4. 封装的Student类:
namespace study
{
public class Student
{
public int Id { get; set; }
public string Name1 { get; set; }
public string Gender { get; set; }
public string Address { get; set; }
public string BirthDay { get; set; }
}
}
5.使用方法:
private void button_Click(object sender, EventArgs e)
{
//打开excel文件
OpenFileDialog openFile = new OpenFileDialog();
DialogResult result = openFile.ShowDialog();
if (result == DialogResult.OK)
{
string path = openFile.FileName;//获取文件路径
this.stuList = new ImportDataFromExcel().GetStudentsByExcel(path);
this.dataGridView1.DataSource = stuList;
}
}
6.出错:
(1)未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序
解决办法:
第一步:修改程序资源池
第二步:(附加---如果你都改完了,但是问题并没有解决,请参考此配置)
第三步:安装驱动
第四步:重启电脑