/// <summary>
/// Excel导入Access
/// </summary>
public void Excel2Access(string[] filenames)
{
try
{
for (int j = 0; j < filenames.Length; j++)
{
OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder();
connectStringBuilder.DataSource = filenames[j];
connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0";
connectStringBuilder.Add("Extended Properties", "Excel 8.0");
using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString))
{
DataSet ds = new DataSet();
string sql = string.Format("select * from [{0}$]", sheetName);
OleDbCommand cmdLiming = new OleDbCommand(sql, cn);
cn.Open();
using (OleDbDataReader dr = cmdLiming.ExecuteReader())
{
ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName });
DataTable dt = ds.Tables[sheetName];
if (dt.Rows.Count > 0)
{
for (int i = 4; i < dt.Rows.Count; i++)
{
//写入数据库数据
if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString())))
{
string MySql = "insert into TB_BlacakList (User_ChineseName,User_EnglishName,Department_ChineseName,Department_EnglishName,Job,Sex,Birthdy,Identification_Type,Identification_Number,Employer,TELEPHONE) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "','" + dt.Rows[i][6].ToString() + "','" + dt.Rows[i][7].ToString() + "','" + dt.Rows[i][8].ToString() + "','" + dt.Rows[i][9].ToString() + "','" + dt.Rows[i][10].ToString() + "','" + dt.Rows[i][11].ToString() + "')";
AccessHelper.SQLExecute(MySql);
}
}
MessageBox.Show("数据导入成功！");
string getblacklistsql = "select ID ,User_ChineseName as 中文,User_EnglishName as 英文,Department_ChineseName as 单位名称中文,Department_EnglishName as 单位名称英文,Job as 职务,Sex as 性别,Birthdy as 出生日期,Identification_Type as 身份证件类型,Identification_Number as 身份证件号码,Employer as 工作单位,TELEPHONE as 联系方式 from TB_BlacakList ";
DriverListBLL driverListBll = new DriverListBLL();
DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);
dgvBlackList.DataSource = dtDriverList;
dgvBlackList.Rows[0].Selected = false;
}
else
{
MessageBox.Show("请检查你的Excel中是否存在数据");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
