C#将SQL数据库中数据导入Excel中，并将Excel中反导入SQL数据库中

using  System;
using  System.Collections.Generic;
using  System.ComponentModel;
using  System.Data;
using  System.Drawing;
using  System.Linq;
using  System.Text;
using  System.Windows.Forms;
using  GemBox.ExcelLite;
using  System.Data.SqlClient;
using  System.Data.OleDb;
using  System.Collections;
using  System.IO;

namespace  Encryption
{
     public   partial   class  ExcelDemo : Form
    {
         private   static   string  _filePath  =   string .Empty;
                
         public  ExcelDemo()
        {
            InitializeComponent();
            BindUser();
        }
         ///   <summary>
         ///  绑定数据
         ///   </summary>
         private   void  BindUser()
        {
             string  sql  =   " select * from Users " ;
            DataTable dt  =  DbHelperSQL.QueryTb(sql);
            dataGridView1.DataSource  =  dt;
        }
         ///   <summary>
         ///  将Users表中的数据导入Excel中
         ///   </summary>
         private   void  btnExcelin_Click( object  sender, EventArgs e)
        {
            ExcelFile excelFile  =   new  ExcelFile();
            ExcelWorksheet sheet  =  excelFile.Worksheets.Add( " Users " );

             int  columns  =  dataGridView1.Columns.Count;
             int  rows  =  dataGridView1.Rows.Count;

             for  ( int  j  =   0 ; j  <  columns; j ++ )
            {
                sheet.Cells[ 0 , j].Value  =  dataGridView1.Columns[j].HeaderText; 
            }

             for  ( int  i  =   1 ; i  <  rows; i ++ )
            {
                 for  ( int  j  =   0 ; j  <  columns; j ++ )
                {
                    sheet.Cells[i, j].Value  =  dataGridView1[j, i  -   1 ].Value.ToString().Trim();
                }
            }

            excelFile.SaveXls( " ./Users.xls " );

            MessageBox.Show( " 生成成功 " );
        }
         ///   <summary>
         ///  选择要向SQL数据库中导入数据的Excel文件
         ///   </summary>
         private   void  btnChoose_Click( object  sender, EventArgs e)
        {
             using  (OpenFileDialog dialog  =   new  OpenFileDialog())
            {
                dialog.Multiselect  =   true ;
                 if  (dialog.ShowDialog()  ==  DialogResult.OK)
                {
                     try
                    {
                        txtPath.Text  =  dialog.FileName;
                    }
                     catch  { }
                }
            }
        }
         ///   <summary>
         ///  将Excel中的数据导入到SQL数据库中
         ///   </summary>
         private   void  btnExcelout_Click( object  sender, EventArgs e)
        {
            DataSet ds = ImportFromExcel(txtPath.Text.Trim());
            DataTable dt  =  ds.Tables[ 0 ];

             try
            {
                 string  strInsertComm;
                 for  ( int  i  =   0 ; i  <  dt.Rows.Count; i ++ )
                {
                    strInsertComm  =   "" ;
                    strInsertComm  =   " Insert INTO NewUsers(LoginNum,UserName,Password,Email,UserHead,Sex,BloodType,BirthPlace,UserLevel,Education,Occupational,Contact,[Profile]) " ;
                    strInsertComm  +=   "  values( " ;
                     for  ( int  j  =   1 ; j  <  dt.Columns.Count; j ++ )
                    {
                         if  (j  >   1 )
                        {
                            strInsertComm  +=   " ,' "   +  dt.Rows[i][j].ToString().Trim()  +   " ' " ;
                        }
                         else
                        {
                            strInsertComm  +=   " ' "   +  dt.Rows[i][j].ToString().Trim()  +   " ' " ;
                        }
                    }
                    strInsertComm  +=   " ) " ;
                    DbHelperSQL.ExecuteSql(strInsertComm);
                }
            }
             catch  (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        
         #region  Excel导入SQL数据库
         ///   <summary>
         ///  获取Excel数据表列表
         ///   </summary>
         ///   <returns></returns>
         public   static  ArrayList GetExcelTables( string  FilePath)
        {
             // 将Excel架构存入数据里
            System.Data.DataTable dt  =   new  System.Data.DataTable();
            ArrayList TablesList  =   new  ArrayList();
            
             if  (File.Exists(FilePath))
            {
                 using  (OleDbConnection conn  =   new  OleDbConnection( " Provider=Microsoft.Jet. "   +
                      " OLEDB.4.0;Extended Properties=\ " Excel  8.0 \ " ;Data Source= "   +  FilePath))
                {
                     try
                    {
                        conn.Open();
                        dt  =  conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,  new   object [] {  null ,  null ,  null ,  " TABLE "  });
                    }
                     catch  (Exception exp)
                    {
                        MessageBox.Show(exp.Message);
                    }

                     // 获取数据表个数
                     int  tablecount  =  dt.Rows.Count;
                     for  ( int  i  =   0 ; i  <  tablecount; i  =  i  +   2 )
                    {
                         string  tablename  =  dt.Rows[i][ 2 ].ToString().Trim().TrimEnd( ' $ ' );
                         if  (TablesList.IndexOf(tablename)  <   0 )
                        {
                            TablesList.Add(tablename);
                        }

                    }
                }
            }
             return  TablesList;
        }

         ///   <summary>
         ///  导入Excel数据表至DataTable（第一行作为表头）
         ///   </summary>
         ///   <returns></returns>
         public   static  System.Data.DataSet FillDataSet( string  FilePath)
        {
             if  ( ! File.Exists(FilePath))
            {
                 throw   new  Exception( " Excel文件不存在！ " );
            }

            ArrayList TableList  =   new  ArrayList();
            TableList  =  GetExcelTables(FilePath);
             if  (TableList.Count  <=   0 )
            {
                 return   null ;
            }

            System.Data.DataTable table;
            System.Data.DataSet ds  =   new  DataSet();
            OleDbConnection dbcon  =   new  OleDbConnection( @" Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "   +  FilePath  +   " ;Extended Properties=Excel 8.0 " );
             try
            {
                 if  (dbcon.State  ==  ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                 for  ( int  i  =   0 ; i  <  TableList.Count; i ++ )
                {
                     string  dtname  =  TableList[i].ToString();
                     try
                    {
                        OleDbCommand cmd  =   new  OleDbCommand( " select * from [ "   +  dtname  +   " $] " , dbcon);
                        OleDbDataAdapter adapter  =   new  OleDbDataAdapter(cmd);
                        table  =   new  DataTable(dtname);
                        adapter.Fill(table);
                        ds.Tables.Add(table);
                    }
                     catch  (Exception exp)
                    {
                        MessageBox.Show(exp.Message);
                    }
                }
            }
             finally
            {
                 if  (dbcon.State  ==  ConnectionState.Open)
                {
                    dbcon.Close();
                }
            }
             return  ds;
        }

         ///   <summary>
         ///  Excel导入数据库
         ///   </summary>
         ///   <returns></returns>
         public   static  DataSet ImportFromExcel( string  FilePath)
        {
             return  FillDataSet(FilePath);
        }

         #endregion  

    }
}