功能:从EXCEL导入数据至数据库中,动态判断列名,并将EXCEL列名与系统 的列名进行绑定,下次直接可以使用。
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Data.OleDb; 6 using System.Data.SqlClient; 7 using System.Drawing; 8 using System.Linq; 9 using System.Text; 10 using System.Threading.Tasks; 11 using System.Windows.Forms; 12 using drp.Common; 13 using drp.DB; 14 namespace drp 15 { 16 public partial class FrmLxdr1 : Form 17 { 18 private string supplierNumber; 19 int rowCounts=0, columnCounts=0; 20 string excelLm ; 21 string drpLm ; 22 int oldRowCount = 0; 23 public FrmLxdr1() 24 { 25 InitializeComponent(); 26 } 27 28 private void btnTc_Click(object sender, EventArgs e) 29 { 30 this.Close(); 31 } 32 33 private void label1_Click(object sender, EventArgs e) 34 { 35 36 } 37 38 private void btnDrlx_Click(object sender, EventArgs e) 39 { 40 supplierNumber = txtGysbh.Text.Trim(); 41 //打开文件对话框 42 OpenFileDialog ofd = new OpenFileDialog(); 43 if (ofd.ShowDialog() == DialogResult.OK) 44 { 45 string excelpath = ofd.FileName; 46 dataGridView1.DataSource = Excel.ReadFromExcel(excelpath); 47 48 //取得总行数和总列数 49 rowCounts = dataGridView1.Rows.Count; 50 columnCounts = dataGridView1.Columns.Count; 51 //显示总行数和列数 52 lblColumns.Text = lblColumns.Text + columnCounts; 53 lblRowCounts.Text = lblRowCounts.Text + rowCounts; 54 55 //读取DATAGRIDVIEW1 列名 56 for (int i = 0; i < dataGridView1.ColumnCount; i++) 57 { 58 string lm = dataGridView1.Columns[i].HeaderText; 59 dataGridView2.Rows.Add(); 60 dataGridView2.Rows[i].Cells[1].Value = lm; 61 if (lm.IndexOf("名称") > 0 || lm.IndexOf("单位") > 0) 62 { 63 dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;//自适应列宽 64 } 65 66 } 67 } 68 69 } 70 71 private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e) 72 { 73 74 } 75 //根据列名获得列的索引流水号 76 private int GetDataGridColumnIndex(DataGridView dataGridView,string columnName) 77 { 78 79 for (int i =0; i < dataGridView.Columns.Count; i++) 80 { 81 if (dataGridView.Columns[i].Name == columnName) 82 { 83 return i; 84 } 85 } 86 return 0; 87 } 88 89 /// <summary> 90 /// 列名匹配 91 /// </summary> 92 private void SetColumnName() 93 { 94 //根据gysbh 供应商编号,取已保存的EXCEL列名与系统标准列名对照表 95 string sql = " select lmppzd_excellm,lmppzd_drplm from lmppzd where lmppzd_gysbh =@gysbh"; 96 SqlParameter p = new SqlParameter("@gysbh", supplierNumber); 97 DataTable dt = new DataTable(); 98 dt = DBHelper.ExecuteQuery(sql, p); 99 oldRowCount = dt.Rows.Count; 100 if (oldRowCount > 0) 101 { 102 excelLm = dt.Rows[0][0].ToString(); 103 drpLm = dt.Rows[0][1].ToString(); 104 105 excelLm = excelLm.Replace(",,", ","); 106 drpLm = drpLm.Replace(",,", ","); 107 108 string[] excelLmArray = excelLm.Split(','); 109 string[] drpLmArray = drpLm.Split(','); 110 string excelField = ""; 111 string flag = ""; 112 113 for (int i = 0; i < dataGridView2.RowCount; i++) 114 { 115 excelField = dataGridView2.Rows[i].Cells[1].Value.ToString(); 116 //如果数组中存在当前的列名,则选中标志改为1,并将系统列名赋值 117 if (Array.IndexOf(excelLmArray, excelField) >= 0) 118 { 119 dataGridView2.Rows[i].Cells[0].Value = true; 120 dataGridView2.Rows[i].Cells[2].Value = drpLmArray[Array.IndexOf(excelLmArray, excelField)]; 121 } 122 123 124 } 125 } 126 else 127 { 128 MessageBox.Show("没有找到列名匹配记录,请手动选择!"); 129 } 130 //读取EXCEL列名并填充datagridview2 131 132 133 } 134 135 private void textBox1_TextChanged(object sender, EventArgs e) 136 { 137 138 } 139 140 private void btnXyb_Click(object sender, EventArgs e) 141 { 142 //保存列名匹配结果 143 if (SaveColumnName() == 1) 144 { 145 //导入数据 146 ImportData(); 147 } 148 149 } 150 151 private void btnLmpp_Click(object sender, EventArgs e) 152 { 153 154 //列名设置 155 SetColumnName(); 156 } 157 158 //保存列名对应关系 159 private int SaveColumnName() 160 { 161 162 //根据表格1的列数量定义数组 163 string[] excelLmArray = new string[dataGridView1.ColumnCount]; 164 string[] drpLmArray = new string[dataGridView1.ColumnCount] ; 165 int j = 0; 166 bool flag = false; 167 for (int i = 0; i < dataGridView2.RowCount; i++) 168 { 169 //如果选中标志改为true,保存结果 170 flag = bool.Parse(dataGridView2.Rows[i].Cells[0].EditedFormattedValue.ToString()); 171 if (flag) 172 { 173 excelLm = dataGridView2.Rows[i].Cells[1].Value.ToString().Trim(); 174 drpLm = dataGridView2.Rows[i].Cells[2].Value.ToString().Trim(); 175 excelLmArray[j] = excelLm; 176 drpLmArray[j] = drpLm; 177 j++; 178 } 179 } 180 //将数组转换为字符串,以,号间隔 181 excelLm = string.Join(",", excelLmArray); 182 drpLm = string.Join(",", drpLmArray); 183 //如果字符长度不是0,且最右边是逗号,循环删除末尾N个逗号, 184 while (excelLm.Length > 0 && excelLm.Substring(excelLm.Length - 1) == "," ) //加入为空判断 185 { 186 excelLm = excelLm.Substring(0, excelLm.Length - 1); 187 } 188 189 while (drpLm.Length > 0 && drpLm.Substring(drpLm.Length - 1) == "," ) 190 { 191 drpLm = drpLm.Substring(0, drpLm.Length - 1); 192 } 193 194 if (drpLm == "" || excelLm =="") 195 { 196 MessageBox.Show("列名对应关系为空,请重新选择"); 197 return 0; 198 } 199 string sql = ""; 200 if ( oldRowCount ==0) 201 { 202 sql = String.Format(" Insert into lmppzd (lmppzd_excellm,lmppzd_drplm,lmppzd_gysbh ) values('{0}','{1}', '{2}' )", excelLm, drpLm, supplierNumber); 203 } 204 else 205 { 206 sql = String.Format(" update lmppzd set lmppzd_excellm ='{0}',lmppzd_drplm='{1}' where lmppzd_gysbh = '{2}' ", excelLm, drpLm, supplierNumber); 207 } 208 209 210 DBHelper.ExecuteNonQuery(sql); 211 return 1; 212 213 214 } 215 //双击打开客户选择窗口; 216 private void txtGysbh_DoubleClick(object sender, EventArgs e) 217 { 218 txtGysbh.Text = PubFunction.SelectClient(); 219 } 220 221 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e) 222 { 223 //自动编号,与数据无关 224 Rectangle rectangle = new Rectangle(e.RowBounds.Location.X, 225 e.RowBounds.Location.Y, 226 dataGridView1.RowHeadersWidth - 4, 227 e.RowBounds.Height); 228 TextRenderer.DrawText(e.Graphics, 229 (e.RowIndex + 1).ToString(), 230 dataGridView1.RowHeadersDefaultCellStyle.Font, 231 rectangle, 232 dataGridView1.RowHeadersDefaultCellStyle.ForeColor, 233 TextFormatFlags.VerticalCenter | TextFormatFlags.Right); 234 } 235 236 //将表格1的流向数据导入数据库中 237 private void ImportData() 238 { 239 string sql; 240 if (drpLm == "") 241 { 242 MessageBox.Show("列名对应关系为空,请重新选择"); 243 return; 244 } 245 drpLm = drpLm.Replace("日期", "YJLX_LXRQ"); 246 drpLm = drpLm.Replace("客户名称", "YJLX_KHMC"); 247 drpLm = drpLm.Replace("物料名称", "YJLX_WLMC"); 248 drpLm = drpLm.Replace("规格型号", "YJLX_GGXH"); 249 drpLm = drpLm.Replace("计量单位", "YJLX_JLDW"); 250 drpLm = drpLm.Replace("数量", "YJLX_SL"); 251 drpLm = drpLm.Replace("流向单价", "YJLX_LXDJ"); 252 drpLm = drpLm.Replace("批号", "YJLX_PCH"); 253 drpLm = drpLm.Replace("包装", "YJLX_BZ"); 254 drpLm = drpLm.Replace("商品编号", "YJLX_SPBH"); 255 string excelColumnName,excelColumnValue; 256 string[] excelLmArray = excelLm.Split(','); 257 //遍历所有行 258 259 for (int j = 0; j < dataGridView1.RowCount; j++) 260 { 261 string tempTableName = "yjlx"; 262 sql = "Insert into " + tempTableName + " ("; 263 sql += "YJLX_LRRQ,YJLX_LRRY,YJLX_LXQJ,YJLX_GYSBH,"; 264 sql += drpLm; 265 sql += ") "; 266 sql += "VALUES ('"; 267 sql += DateTime.Now.ToString(); 268 sql += "','','"; 269 sql += txtLxqj.Text; 270 sql += "','"; 271 sql += supplierNumber; 272 273 274 //判断EXCEL列名,是否需要导入 275 foreach (string s in excelLmArray) 276 { 277 for (int i = 0; i < dataGridView1.Columns.Count; i++) 278 { 279 excelColumnName = dataGridView1.Columns[i].HeaderText; 280 if (s== excelColumnName) 281 { 282 if (dataGridView1.Rows[j].Cells[i].Value != null) 283 { 284 excelColumnValue = dataGridView1.Rows[j].Cells[i].Value.ToString(); 285 excelColumnName.Replace("'", ""); //替换'单引号,以免SQL 出错 286 } 287 else 288 { 289 break; 290 } 291 292 293 sql += "','"; 294 sql += excelColumnValue; 295 break; 296 } 297 } 298 299 } 300 sql += " ')"; 301 302 DBHelper.ExecuteNonQuery(sql); 303 Console.WriteLine(sql); 304 } 305 MessageBox.Show("当前页面导入完成!", "提示"); 306 this.Close(); 307 } 308 } 309 310 }