本文讲述在asp.net环境下将Excel文件导入到SQL Server 数据中的三种方法，供编程人员参考，也做为一个备份了！
第一种方法通过到入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();
}

//第二种方法：将Excel文件数据库导入SQL Server的三种方案//方案一： 通过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 = "Provider=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());
  }
}