private JhInfo newInitInfo() { JhInfo jhInfo = new JhInfo(); jhInfo.KhdmGB = "5001120040046"; jhInfo.Khdm = "0100003"; jhInfo.Sl = 1.0; jhInfo.Cph = "渝A5Y965"; jhInfo.UserGuid = "643c91d0-003b-4c2b-aa68-3ef39bafceee"; jhInfo.Remark = ""; jhInfo.TotalChargeMoneyValue = 0.00; jhInfo.CountChargeMoneyValue = 0; jhInfo.SbyId = 11; jhInfo.IsEnabled = 1; jhInfo.Status = 1; jhInfo.Kh_mc = "观农贸批发市场"; jhInfo.SbyXm = "梅巧洪"; jhInfo.SbyCode = "001"; jhInfo.IsJz = 2; jhInfo.CountJybs = 0; jhInfo.JhXzCode = "01"; jhInfo.JhXzName = "代销"; jhInfo.KhJfFsCode = "01"; jhInfo.KhJfFsName = "本地菜"; jhInfo.SourceType = 1; jhInfo.SourceName = "手工录入"; jhInfo.JcChargeGuid = ""; return(jhInfo); }
private void 打开文件ToolStripMenuItem_Click(object sender, EventArgs e) { dataGridView1.DataSource = null; invaildProduct.Clear(); openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); //设置打开对话框的标题 openFileDialog1.Title = "请选择要打开的文件"; //设置打开对话框可以多选 openFileDialog1.Multiselect = true; openFileDialog1.FileName = ""; //设置对话框打开的文件类型 openFileDialog1.Filter = "Excel文件|*.xls"; //设置文件对话框当前选定的筛选器的索引 openFileDialog1.FilterIndex = 2; //设置对话框是否记忆之前打开的目录 openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog() == DialogResult.OK) { //获取用户选择的文件完整路径 string filePath = openFileDialog1.FileName; //获取对话框中所选文件的文件名和扩展名,文件名不包括路径 string fileName = openFileDialog1.SafeFileName; System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1, 0, 0, 0, 0)); long t = (DateTime.Now.Ticks - startTime.Ticks) / 10000; //除10000调整为13位 string batch_code = "S101" + t.ToString(); DataSet dataSet; try { dataSet = ExcelToDS(filePath); } catch (Exception f) { MessageBox.Show("打开Excel文件失败" + f.Message); return; } JhInfo order = newInitInfo(); List <JhInfoList> orderList = new List <JhInfoList>(); DataTable table = dataSet.Tables[0]; dataGridView1.DataSource = table; this.dataGridView1.Columns[0].FillWeight = 25; this.dataGridView1.Columns[1].FillWeight = 20; this.dataGridView1.Columns[2].FillWeight = 25; this.dataGridView1.Columns[3].FillWeight = 25; this.dataGridView1.Columns[0].HeaderText = "日期"; this.dataGridView1.Columns[1].HeaderText = "蔬菜名称"; this.dataGridView1.Columns[2].HeaderText = "重量(斤)"; this.dataGridView1.Columns[3].HeaderText = "金额"; String connsql = "server=" + ip + ";database=StmsCq;uid=" + username + ";pwd=" + userpassword;// 数据库连接字符串,database设置为自己的数据库名,以Windows身份验证 string year = DateTime.Now.ToString("yy"); string data = DateTime.Now.ToString(" HH:mm:ss.fff"); try { List <product> productList; using (var db = SugarDao.GetInstance()) { string sql = string.Format("SELECT id,spdmgb,Spmc,Spdm from product"); productList = db.SqlQuery <product>(sql); using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = connsql; conn.Open(); // 打开数据库连接 SqlDataAdapter sqlDa = new SqlDataAdapter("select top 1 JhCode,JhCodeSimple from JhInfo order by JhId desc", conn); DataTable dt = new DataTable(); sqlDa.Fill(dt); long JhCode = long.Parse(dt.Rows[0][0].ToString()) + 1; string JhCodeSimple = year + (long.Parse(dt.Rows[0][1].ToString().Substring(3, 3)) + 1).ToString() + "001"; order.JhCodeSimple = JhCodeSimple; order.JhCode = JhCode.ToString(); order.CountSplist = dataSet.Tables[0].Rows.Count; order.WriteDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"); order.EditDate = order.WriteDate; DataTable dts = GetTableSchema(); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); bulkCopy.DestinationTableName = "JhInfoList"; bulkCopy.BatchSize = dt.Rows.Count; sqlDa = new SqlDataAdapter("select top 1 JhId from JhInfo order by JhId desc", conn); DataTable dt1 = new DataTable(); sqlDa.Fill(dt1); long orderListStart = long.Parse(dt1.Rows[0][0].ToString()); foreach (DataRow dr in dataSet.Tables[0].Rows) { JhInfoList one = newInitInfoList(); DataRow drs = dts.NewRow(); foreach (var tmp in productList) { string product_name = ""; if (dr[1].ToString().Length < 3) { product_name = dr[1].ToString(); } else { product_name = dr[1].ToString().Substring(2); } if (tmp.Spmc == product_name) { one.SpdmGB = tmp.SpdmGB; one.Spdm = tmp.Spdm; one.Spmc = tmp.Spmc; break; } } if (one.SpdmGB == "") { string info = dr[1].ToString() + " 没有该商品代码请添加,或者修改Excel源数据"; try { invaildProduct.Add(info, info); } catch (Exception) { } continue; MessageBox.Show(info); return; one.SpdmGB = "000"; one.Spdm = "0000"; one.Spmc = dr[1].ToString(); } one.JhId = orderListStart + 1;// + dataSet.Tables[0].Rows.IndexOf(dr); one.IndexOfOrder = dataSet.Tables[0].Rows.IndexOf(dr) + 1; one.Spzl = dr[2].ToString(); one.Spsl = "1.00"; one.JhRq = dr[0].ToString().Substring(0, 10) + data; order.Jhrq = one.JhRq; one.JhCode = JhCode.ToString(); one.JhCodeSimple = order.JhCodeSimple; order.Zl += System.Convert.ToDouble(one.Spzl); orderList.Add(one); drs[0] = one.JhId; drs[1] = one.SpdmGB; drs[2] = one.Spdm; drs[3] = one.CddmGB; drs[4] = one.Cddm; drs[5] = one.IndexOfOrder; drs[6] = one.Spzl; drs[7] = one.Spsl; drs[8] = one.Status; drs[9] = one.JhUnitprice; drs[10] = one.Spmc; drs[11] = one.Cdmc; drs[12] = one.JhRq; drs[13] = one.JhCode; drs[14] = one.JhCodeSimple; drs[15] = one.Khdm; drs[16] = one.KhdmGB; drs[17] = one.Khmc; drs[18] = one.Scz; drs[19] = one.Scjd; drs[20] = one.Lxdh; drs[21] = one.Jczh; drs[22] = one.Cdzh; drs[23] = one.Yjscdm; drs[24] = one.Yjscmc; drs[25] = one.YjscdmGB; drs[26] = one.Ejscdm; drs[27] = one.EjscdmGB; drs[28] = one.Ejscmc; drs[29] = one.IsJz; drs[30] = one.IsEnabled; drs[31] = one.Cph; drs[32] = one.Sppz; drs[33] = one.Yjzsm; drs[34] = one.JhlxCode; drs[35] = one.Jhlxmc; drs[36] = one.JhXzCode; drs[37] = one.JhXzName; drs[38] = one.KhJfFsCode; drs[39] = one.KhJfFsName; drs[40] = one.SourceType; drs[41] = one.SourceName; dts.Rows.Add(drs); } if (invaildProduct.Count > 0) { string errstring = ""; foreach (var info in invaildProduct) { errstring += info.Value + "\n"; } MessageBox.Show(errstring); return; } bulkCopy.WriteToServer(dts); sql = "INSERT INTO JhInfo(Jhrq,JhCode,JhCodeSimple,KhdmGB,Khdm,Sl,Zl,Cph,UserGuid,Remark,WriteDate,EditDate,TotalChargeMoneyValue,CountChargeMoneyValue,CountSplist,SbyId,IsEnabled,Status,Kh_mc,SbyXm,SbyCode,IsJz,CountJybs,JhXzCode,JhXzName,KhJfFsCode,KhJfFsName,SourceType,SourceName,JcChargeGuid) VALUES(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30)"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@1", order.Jhrq); cmd.Parameters.AddWithValue("@2", order.JhCode); cmd.Parameters.AddWithValue("@3", order.JhCodeSimple); cmd.Parameters.AddWithValue("@4", order.KhdmGB); cmd.Parameters.AddWithValue("@5", order.Khdm); cmd.Parameters.AddWithValue("@6", order.Sl); cmd.Parameters.AddWithValue("@7", order.Zl); cmd.Parameters.AddWithValue("@8", order.Cph); cmd.Parameters.AddWithValue("@9", order.UserGuid); cmd.Parameters.AddWithValue("@10", order.Remark); cmd.Parameters.AddWithValue("@11", order.WriteDate); cmd.Parameters.AddWithValue("@12", order.EditDate); cmd.Parameters.AddWithValue("@13", 0.00); cmd.Parameters.AddWithValue("@14", 0); cmd.Parameters.AddWithValue("@15", order.CountSplist); cmd.Parameters.AddWithValue("@16", order.SbyId); cmd.Parameters.AddWithValue("@17", order.IsEnabled); cmd.Parameters.AddWithValue("@18", order.Status); cmd.Parameters.AddWithValue("@19", order.Kh_mc); cmd.Parameters.AddWithValue("@20", order.SbyXm); cmd.Parameters.AddWithValue("@21", order.SbyCode); cmd.Parameters.AddWithValue("@22", order.IsJz); cmd.Parameters.AddWithValue("@23", order.CountJybs); cmd.Parameters.AddWithValue("@24", order.JhXzCode); cmd.Parameters.AddWithValue("@25", order.JhXzName); cmd.Parameters.AddWithValue("@26", order.KhJfFsCode); cmd.Parameters.AddWithValue("@27", order.KhJfFsName); cmd.Parameters.AddWithValue("@28", order.SourceType); cmd.Parameters.AddWithValue("@29", order.SourceName); cmd.Parameters.AddWithValue("@30", order.JcChargeGuid); int count = cmd.ExecuteNonQuery(); //return; } } } } catch (Exception ex) { MessageBox.Show("错误信息:" + ex.Message, "出现错误"); return; } MessageBox.Show("添加完成"); } }