从Excel汇入数据

 

  private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable datatable = this.DataBlockSet.DataSource.Tables["CUSTOMER_ITEMS"];
                if (datatable != null)
                {
                    DataTable dest = datatable.Clone();
                    DtsProviderManager manager = new DtsProviderManager(TransferMode.Import);
                    string appPath = Application.StartupPath;
                    string columnMappingFile = Path.Combine(appPath, @"..\Templates\dts\import_customer_item.xml");

                    manager.SetColumnMappingFile(columnMappingFile);
                    ExcelDtsProvider excelProvider = new ExcelDtsProvider();
                    manager.AddDtsProvider(excelProvider);

                    dest.BeginLoadData();
                    DTSWizard.RunWizard(this, dest, manager);
                    dest.EndLoadData();

                    //delete the exists data
                    DataView view = datatable.DefaultView;
                    string key="CUSTOMER_ITEM_NUMBER";
                    view.Sort = key;
                    datatable.BeginLoadData();
                    foreach (DataRow row in dest.Rows)
                    {
                        //int rowIndex=view.Find(row[key]);
                        //if ( rowIndex>= 0)
                        //{
                        //    view.Delete(rowIndex);                          
                        //}
                        DataRow findRow = datatable.Rows.Find(row[key]);
                        if (findRow != null)
                        {
                            findRow.ItemArray = row.ItemArray;
                        }
                    }
                    datatable.EndLoadData();
                    datatable.Merge(dest, true);

                }
            }
            catch (Exception ex)
            {
                base.ShowError(ex);
            }
        }