private void btnOk_Click(object sender, EventArgs e) { int t = 0,i = 0; String alert=""; if (cbStart.Text == "" || cbEnd.Text == "" || cbWorker.Text == "") { MessageBox.Show("请先选择导入方式"); return; } String sql = "select "; sql += cbStart.Text + ","; sql += cbEnd.Text + ","; sql += cbWorker.Text; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); String sqliteSql; object oj; while (odr.Read()) { i++; sqliteSql = "select worker from tb where stNo='" + odr[0].ToString()+"' "; sqliteSql += " and edNo='" + odr[1].ToString() + "'"; oj = db.dbScalar(sqliteSql); if (odr[0].ToString() == "728563173001") { i = i; } if(oj!=null) { if(oj.ToString()=="闸北凯旋门") { sqliteSql="delete from tb where stNo='"+odr[0].ToString()+"' "; sqliteSql+=" and edNo='"+odr[1].ToString()+"'"; db.dbNonQuery(sqliteSql); }; } sqliteSql = "select count(*) from tb where stNo='" + odr[0].ToString()+"' "; sqliteSql += " and edNo='" + odr[1].ToString() + "'"; if (db.dbScalar(sqliteSql).ToString() == "0") { sqliteSql = "insert into tb(stNo,edNo,worker) values('"; sqliteSql += odr[0].ToString() + "','"; sqliteSql += odr[1].ToString() + "','"; sqliteSql += odr[2].ToString() + "')"; if (odr[0].ToString().Length == 12) { t++; db.dbNonQuery(sqliteSql); } } else { alert += "第" + i.ToString() + "行存在数据重复\n"; } } MessageBox.Show(alert); MessageBox.Show("导入成功"); }
private void button1_Click(object sender, EventArgs e) { dv.Rows.Clear(); dbOperator db = new dbOperator(); String sqliteSql,s; Object result; for (int i = 0; i < richTextBox1.Lines.Count(); i++) { s = richTextBox1.Lines.ElementAt(i).ToString(); dv.Rows.Add(); dv.Rows[dv.Rows.Count - 1].Cells[0].Value = s; if (s.Length == 12 && s.Substring(0, 1) != "3") { if (db.dbScalar("select count(*) from fafang where 起始单号<='" + s + "' and 结束单号>='" + s + "'").ToString() == "0") { dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "非本公司面单"; } else { sqliteSql = "select 姓名 from fafang,worker where 起始单号<='" + s + "' and 结束单号>='" + s + "' and 使用人=worker.编号 and 姓名<>'闸北凯旋门'"; result = db.dbScalar(sqliteSql); if (result != null) { dv.Rows[dv.Rows.Count - 1].Cells[1].Value = result.ToString(); } else { dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "闸北凯旋门"; } } } else if (s.Length == 12) { dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "电子面单"; } else { dv.Rows[dv.Rows.Count - 1].Cells[1].Value = "运单编号不符合规则"; } } }
private void btnStart_Click(object sender, EventArgs e) { if (cbImportType.Text == "发放记录导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; for (int j = 0; j < dvFormat.RowCount-1; j++) { sql += dvFormat.Rows[j].Cells[1].Value + ","; } sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); Object oj; while (odr.Read()) { if (odr[0].ToString().Length == 12) { mysql = "select 使用人 from fafang where 起始单号='" + odr[0].ToString() + "' "; mysql += " and 结束单号='" + odr[1].ToString() + "'"; oj = db.dbScalar(mysql); if (oj != null) { if (oj.ToString() == "21015.001") { mysql = "delete from fafang where 起始单号='" + odr[0].ToString() + "' "; mysql += " and 结束单号='" + odr[1].ToString() + "'"; db.dbNonQuery(mysql); }; } mysql = "insert into fafang("; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += dvFormat.Rows[j].Cells[0].Value + ","; } mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value; mysql += ") values('"; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += odr[j].ToString() + "','"; } mysql += odr[dvFormat.RowCount - 1].ToString() + "')"; db.dbNonQuery(mysql); } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } if (cbImportType.Text == "收件数据导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; for (int j = 0; j < dvFormat.RowCount - 1; j++) { sql += dvFormat.Rows[j].Cells[1].Value + ","; } sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; { dbOperator db = new dbOperator(); OleDbDataReader odr = ex.excelReader(sql); while (odr.Read()) { if (odr[0].ToString().Length == 12) { db.dbNonQuery("delete from shoujian where 运单编号='" + odr[0].ToString() + "'"); mysql = "insert into shoujian("; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += dvFormat.Rows[j].Cells[0].Value + ","; } mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value; mysql += ") values('"; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += odr[j].ToString() + "','"; } mysql += odr[dvFormat.RowCount - 1].ToString() + "')"; db.dbNonQuery(mysql); } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); } MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } if (cbImportType.Text == "派件数据导入") { double tot = ex.getRowCount(cbSheet.Text); double i = 0; String mysql; String sql = "select "; for (int j = 0; j < dvFormat.RowCount - 1; j++) { sql += dvFormat.Rows[j].Cells[1].Value + ","; } sql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[1].Value; sql += " from [" + cbSheet.Text + "]"; OleDbDataReader odr = ex.excelReader(sql); dbOperator db = new dbOperator(); while (odr.Read()) { mysql = "insert into paijian("; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += dvFormat.Rows[j].Cells[0].Value + ","; } mysql += dvFormat.Rows[dvFormat.RowCount - 1].Cells[0].Value; mysql += ") values('"; for (int j = 0; j < dvFormat.RowCount - 1; j++) { mysql += odr[j].ToString() + "','"; } mysql += odr[dvFormat.RowCount - 1].ToString() + "')"; //MessageBox.Show(mysql); if (db.dbNonQuery(mysql) <= 0) { return; } i += 1; pbImport.Value = (int)(i * 100 / tot); } odr.Close(); MessageBox.Show(tot.ToString() + "条运单数据数据导入成功"); } if (cbImportType.Text == "") { MessageBox.Show("请选择导入方式"); } MessageBox.Show("当前有" + mydb.dbScalar("select count(*) from shoujian").ToString() + "条数据"); }