第一步:转换导入的文件

　　private void btnSelectFile_Click(object sender, EventArgs e)   {       OpenFileDialog ofd = new OpenFileDialog();       ofd.Title = "Excel文件";       ofd.FileName = "";       ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);       ofd.Filter = "Excel文件(*.xls)|*.xls";       ofd.ValidateNames = true;       ofd.CheckFileExists = true;       ofd.CheckPathExists = true;       if (openFileDialog.ShowDialog() == DialogResult.OK)       {           txtFileName.Text = openFileDialog.FileName;       }    }

 第二步：执行导入

　　　 private void btnImport_Click(object sender, EventArgs e)      {          if (cboSaleStock.SelectedIndex == -1)          {              MessageBox.Show("没有选择销售仓库");              return;          }          if (cboShop.SelectedIndex == -1)          {              MessageBox.Show("没有选择销售门店");              return;          }          if (string.IsNullOrEmpty(txtFileName.Text) || txtFileName.Text.Length == 0)          {              MessageBox.Show("没有选择Excel文件！无法进行数据导入");              return;          }          DataTable dt = ExcelToDataTable(txtFileName.Text);          string sql = string.Empty;          for (int i = 0; i < dt.Rows.Count; i++)          {              sql += string.Format(@" declare @sno{4} int,@dgrID{4} varchar(20)                                      set @dgrID{4}=(select top 1 dgoodsresultid from tbdGoodsResult where shopID='{0}' and stockid='{1}' and goodsid='{2}')                                      if @dgrID{4} is null begin                                          set @sno{4}=(select top 1 sno from tbpKeyManager where tablename='tbdGoodsResult')                                          if @sno{4} is null begin                                              insert into tbpKeyManager(tablename,sno,trdate) values('tbdGoodsResult',1,CONVERT(varchar(12),getdate(),112 ))                                              set @sno{4}=1                                          end else begin                                               set @sno{4}=@sno{4}+1                                               update tbpKeyManager set sno=sno+1 where tablename='tbdGoodsResult'                                          end                                          insert into tbdGoodsResult(dgoodsresultid,shopid,stockid,goodsid,pthismonthqty)                                           values(cast(@sno{4} as varchar(11)),'{0}','{1}','{2}','{3}')                                      end else begin                                          update tbdGoodsResult set pthismonthqty={3} where dgoodsresultid=@dgrID{4}                                      end", cboShop.SelectedValue, cboSaleStock.SelectedValue, dt.Rows[i][2], dt.Rows[i][1], i);          }          string msg = "";          if (ws.ExecuteNonQueryWithTrans(sql, ref msg) > 0)          {              MessageBox.Show("导入数据成功");          }          if (msg.Length > 0)          {              MessageBox.Show("导入数据失败:\r\n" + msg);          }      }

第三步：Excel数据导入

private DataTable ExcelToDataTable(string filePath)      {          //根据路径打开一个Excel文件并将数据填充到DataSet中          string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入          OleDbConnection conn = new OleDbConnection(strConn);          conn.Open();          string strExcel = "";          OleDbDataAdapter myCommand = null;          DataSet ds = null;          strExcel = "select  * from   [sheet1$]";          myCommand = new OleDbDataAdapter(strExcel, strConn);          ds = new DataSet();          myCommand.Fill(ds, "table1");          DataTable dt = ds.Tables[0];          dt.Columns.Add("goodsid");          string joinSql = "select '' pluno";          foreach (DataRow dr in dt.Rows)          {              joinSql += string.Format(" union all select '{0}' pluno", dr[0]);          }          string sql = string.Format(@"select tbgoods.goodsid,tbgoods.pluno from tbgoods join ({0}) as t on t.pluno=tbgoods.pluno ", joinSql);          DataTable dtGoods = cf.GetDataSet(sql).Tables[0];          foreach (DataRow dr in dt.Rows)          {              foreach (DataRow dr1 in dtGoods.Rows)              {                  if (dr[0].ToString().ToUpper() == dr1["pluno"].ToString())                  {                      dr["goodsid"] = dr1["goodsid"].ToString();                  }              }          }          return ds.Tables[0];      }