C# 从EXCEL导入数据到数据库，动态绑定列名（第一次作业）

功能：从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 }
posted @ 2022-02-08 09:59 一贴灵阅读(654) 评论(1) 收藏举报
刷新页面返回顶部
健康一贴灵

医药行业、IT、ERP

C# 从EXCEL导入数据到数据库，动态绑定列名（第一次作业）

公告

健康一贴灵

医药行业、IT、ERP

C# 从EXCEL导入数据到数据库，动态绑定列名（ 第一次作业）

公告

C# 从EXCEL导入数据到数据库，动态绑定列名（第一次作业）
