导入Excel表中的数据

第一步:转换导入的文件

　　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];
      }