/// <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_Car (PlateNumber,Color,Brand,UseYear,MotorcyleType) 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() + "')";
AccessHelper.SQLExecute(MySql);
}
}
MessageBox.Show("数据导入成功！");
CarBLL carBLL = new CarBLL();
DataTable carList = carBLL.FindAll_infos(getblacklistsql);
dgvCarList.DataSource = carList;
}
else
{
MessageBox.Show("请检查你的Excel中是否存在数据");
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void CarForm_Load(object sender, EventArgs e)
{
DataTable carList = carBLL.FindAll_infos(getblacklistsql);
dgvCarList.DataSource = carList;
}