/// <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());
}
}
private void Form1_Load(object sender, EventArgs e)
{
//dgvBlackList.AutoGenerateColumns = false;
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 ";
try
{
DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);
dgvBlackList.DataSource = dtDriverList;
}
catch (Exception ex)
{
// LogHelper.WriteLog(typeof(Form1), "初始化黑名单错误 :" + ex.Message);
MessageBox.Show("初始化黑名单错误 :");
}
dgvBlackList.Rows[0].Selected = false;
//LogHelper.WriteLog(typeof(Form1), "初始化黑名单错误 :" + ex.Message);
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
string sql = "update TB_BlacakList set User_ChineseName='" + txtUser_ChineseName.Text + "',User_EnglishName='" + txtUser_EnglishName.Text + "',Department_EnglishName='" + txtDepartment_EnglishName.Text + "',Department_ChineseName='" + txtDepartment_ChineseName.Text + "',Job='" + txt_Job.Text + "',Birthdy='" + txtBirthdy.Text + "' ,Identification_Type='" + txtIdentification_Type.Text + "',Identification_Number='" + txtIdentification_Number.Text + "',Telephone='" + txtTELEPHONE.Text + "',Employer='" + txtEmployer.Text + "' where ID=" + driver.Id + "";
if (driverList.ExecuteSql(sql))
{
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 ";
DataTable dtDriverList = driverListBll.FindAll_infos(getblacklistsql);
DataGridView dgv = (DataGridView)(f1.Controls["tabContro1"].Controls["tabPage1"].Controls["dgvBlackList"]);
dgv.DataSource = dtDriverList;
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
