转---SQL与EXCEL交互(导出/导入)

SQL与EXCEL交互(导出/导入)

EXCE->SQL
//方案一： 通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到SQL Server

openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
     FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
     string filePath = fileInfo.FullName;
     string connExcel = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
    
     try
     {
         OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
         oleDbConnection.Open();
        
         //获取excel表
         DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

         //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素
         string tableName = dataTable.Rows[0][2].ToString().Trim();
         tableName = "[" + tableName.Replace("'","") + "]";

         //利用SQL语句从Excel文件里获取数据
         //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName;
         string query = "SELECT 曰期,开课城市,讲师,课程名称,持续时间 FROM " + tableName;
         dataSet = new DataSet();

         //OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection);
         //OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
         OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query,connExcel);
        
         oleAdapter.Fill(dataSet,"gch_Class_Info");

         //dataGrid1.DataSource = dataSet;
         //dataGrid1.DataMember = tableName;
         dataGrid1.SetDataBinding(dataSet,"gch_Class_Info");

         //从excel文件获得数据后，插入记录到SQL Server的数据表
         DataTable dataTable1 = new DataTable();
        
         SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate,
classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info",sqlConnection1);
        
         SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
        
         sqlDA1.Fill(dataTable1);

         foreach(DataRow dataRow in dataSet.Tables["gch_Class_Info"].Rows)
         {
             DataRow dataRow1 = dataTable1.NewRow();
            
             dataRow1["classDate"] = dataRow["曰期"];
             dataRow1["classPlace"] = dataRow["开课城市"];
             dataRow1["classTeacher"] = dataRow["讲师"];
             dataRow1["classTitle"] = dataRow["课程名称"];
             dataRow1["durativeDate"] = dataRow["持续时间"];

             dataTable1.Rows.Add(dataRow1);
         }

         Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
         sqlDA1.Update(dataTable1);
        
         oleDbConnection.Close();

     }
     catch(Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }
}





//方案二： 直接通过SQL语句执行SQL Server的功能函数将Excel文件转换到SQL Server数据库

OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";

SqlConnection sqlConnection1 = null;

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
     string filePath = openFileDialog.FileName;

     sqlConnection1 = new SqlConnection();
     sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library";

     //import excel into SQL Server 2000
     /*string importSQL = "SELECT * into live41 FROM OpenDataSource" +
         "('Microsoft.Jet.OLEDB.4.0','Data Source=" + "\"" + "E:\\022n.xls" + "\"" +
         "; User ID=assword=; Extended properties=Excel 5.0')...[Sheet1$]";*/

     //export SQL Server 2000 into excel
     string exportSQL = @"EXEC master..xp_cmdshell
'bcp Library.dbo.live41 out " + filePath + "-c -q -S" + "\"" + "\"" +
         " -U" + "\"" + "\"" + " -P" + "\"" + "\"" + "\'";
    
     try
     {
         sqlConnection1.Open();
        
         //SqlCommand sqlCommand1 = new SqlCommand();
         //sqlCommand1.Connection = sqlConnection1;
         //sqlCommand1.CommandText = importSQL;
         //sqlCommand1.ExecuteNonQuery();
         //MessageBox.Show("import finish!");
        
         SqlCommand sqlCommand2 = new SqlCommand();
         sqlCommand2.Connection = sqlConnection1;
         sqlCommand2.CommandText = exportSQL;
         sqlCommand2.ExecuteNonQuery();
         MessageBox.Show("export finish!");
     }
     catch(Exception ex)
     {
         MessageBox.Show(ex.ToString());
     }
}

if(sqlConnection1!=null)
{
     sqlConnection1.Close();
     sqlConnection1 = null;
}






//方案三： 通过到入Excel的VBA dll，通过VBA接口获取Excel数据到DataSet

OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel files(*.xls)|*.xls";

ExcelIO excelio = new ExcelIO();

if(openFile.ShowDialog()==DialogResult.OK)
{
     if(excelio!=null)
         excelio.Close();

     excelio = new ExcelIO(openFile.FileName);
     object[,] range = excelio.GetRange();
     excelio.Close();

    
     DataSet ds = new DataSet("xlsRange");

     int x = range.GetLength(0);
     int y = range.GetLength(1);

     DataTable dt = new DataTable("xlsTable");
     DataRow dr;
     DataColumn dc;
    
     ds.Tables.Add(dt);

     for(int c=1; c<=y; c++)
     {
         dc = new DataColumn();
         dt.Columns.Add(dc);
     }
    
     object[] temp = new object[y];
    
     for(int i=1; i<=x; i++)
     {
         dr = dt.NewRow();

         for(int j=1; j<=y; j++)
         {
             temp[j-1] = range[i,j];
         }
        
         dr.ItemArray = temp;
         ds.Tables[0].Rows.Add(dr);
     }

     dataGrid1.SetDataBinding(ds,"xlsTable");
    
     if(excelio!=null)
         excelio.Close();
}

SQL->EXCEL
         public class OPExcel
         {
                 public OPExcel(){}
                
                 public bool WritDataSetToExcel(DataSet ds,string ExcelPath,ProgressBar pb)
                 {
                         if(ds==null)return false;
                         int row=ds.Tables [0].Rows .Count ;
                         int col=ds.Tables [0].Columns .Count ;
                         if(row <1 || col<1)return false;
                         pb.Minimum =0;pb.Maximum =row;pb.Value =0;
                         Excel.ApplicationClass excel=new Excel.ApplicationClass ();
                         try
                         {        
                                 excel.Workbooks.Add (Type.Missing );
                                 Excel.Worksheet sheet=(Excel.Worksheet)excel.ActiveSheet;
                                 for(int i=0;i<col;i++)
                                         sheet.Cells [1,i+1]=ds.Tables [0].Columns.ToString ();
                                 for(int i=0;i<row;i++)
                                 {
                                         Application.DoEvents ();
                                         for(int j=0;j<col;j++)
                                         {
                                                 sheet.Cells [i+2,j+1]="'"+ds.Tables [0].Rows [j].ToString ();
                                         }
                                         pb.Value ++;
                                 }
                                 excel.Save (ExcelPath);
                                 excel.Quit ();
                         }
                         catch(System.Exception ex){MessageBox.Show (ex.Message );excel.Quit ();return false;}
                         GC.Collect ();
                         return true;
                 }
         }