界面
点击导入后datagridview中加载的Excel数据
最后点击保存就可以同步到数据库中了
具体步骤
1、首先设置需要同步数据库表的id为自增，导入的Excel数据删除id的列
2、代码
点击导入按钮在里面编写代码
//打开文件，获取到文件存储路径，并保存在textBox中
openFileDialog1.ShowDialog();
textBox1.Text = openFileDialog1.FileName;
//导入文件，读取文件的路径，并将其导入到DataGridView中
string fileName = "";
fileName = textBox1.Text;
if (textBox1.Text != "")
{
try
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + " ;Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = " SELECT * FROM [sheet1$] ";
//sheet1为对应表名，如果不是初始默认的，记得更改
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
//importExcel为DataGridView的Name
dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("请选择Excel文件");
}
3、单击保存按钮编辑代码
//循环datagridview，根据索引，分别对应Excel中的列字段
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
string aa = this.dataGridView1.Rows[i].Cells[0].Value.ToString().Trim();
string bb = this.dataGridView1.Rows[i].Cells[1].Value.ToString().Trim();
string cc = this.dataGridView1.Rows[i].Cells[2].Value.ToString().Trim();
string dd = this.dataGridView1.Rows[i].Cells[3].Value.ToString().Trim();
string ee = this.dataGridView1.Rows[i].Cells[4].Value.ToString().Trim();
string ff = this.dataGridView1.Rows[i].Cells[5].Value.ToString().Trim();
string gg = this.dataGridView1.Rows[i].Cells[6].Value.ToString().Trim();
string hh = this.dataGridView1.Rows[i].Cells[7].Value.ToString().Trim();
string ii = this.dataGridView1.Rows[i].Cells[8].Value.ToString().Trim();
string jj = this.dataGridView1.Rows[i].Cells[9].Value.ToString().Trim();
string kk = this.dataGridView1.Rows[i].Cells[10].Value.ToString().Trim();
string ll = this.dataGridView1.Rows[i].Cells[11].Value.ToString().Trim();
string mm = this.dataGridView1.Rows[i].Cells[12].Value.ToString().Trim();
decimal nn = Convert.ToDecimal(dataGridView1.Rows[i].Cells[13].Value);
decimal oo = Convert.ToDecimal(dataGridView1.Rows[i].Cells[14].Value);
decimal pp = Convert.ToDecimal(dataGridView1.Rows[i].Cells[15].Value);
decimal qq = Convert.ToDecimal(dataGridView1.Rows[i].Cells[16].Value);
decimal rr = Convert.ToDecimal(dataGridView1.Rows[i].Cells[17].Value);
string ss = this.dataGridView1.Rows[i].Cells[18].Value.ToString().Trim();
decimal tt = Convert.ToDecimal(dataGridView1.Rows[i].Cells[19].Value);
decimal uu = Convert.ToDecimal(dataGridView1.Rows[i].Cells[20].Value);
string vv = this.dataGridView1.Rows[i].Cells[21].Value.ToString().Trim();
string ww = this.dataGridView1.Rows[i].Cells[22].Value.ToString().Trim();
//datagridview和excel中的字段对应后进行insert到数据库中
SqlConnection con = new SqlConnection(SQL);
con.Open();
string myInsert = "INSERT INTO [dbo].[ProductIn]([orderid],[contractid],[date],[staffin],[staffout],[company],[project],[product],[norms],[kfcz],[sjcz],[ydh],[dw],[yssl],[sssl],[zsms],[fhsl],[scdj],[shck],[cbdj],[cbje],[state],[examine]) VALUES('" + aa + "', '" + bb + "', '" + cc + "', '" + dd + "', '" + ee + "', '" + ff + "', '" + gg + "', '" + hh + "', '" + ii + "', '" + jj + "', '" + kk + "', '" + ll + "', '" + mm + "', '" + nn + "', '" + oo + "', '" + pp + "', '" + qq + "', '" + rr + "', '" + ss + "', '" + tt + "', '" + uu + "', '" + vv + "', '" + ww + "')";
SqlCommand myCom = new SqlCommand(myInsert, con);
int cot = myCom.ExecuteNonQuery();
if (cot == 0)
{
MessageBox.Show("失败");
}
con.Close();
}
导入功能就写完了，在导入中要注意的就是Excel中的列要和datagridview中对应就好了