将Excel内容导入数据库(ASP.NET/C#)

思路：先上传XLS文件，然后用OleDB连接Excel文件，将Excel数据导入Dataset，再将Dataset数据导入数据库。
以下是主调入过程

string  filename  =   string .Empty;
             try
             {
                filename = UpdateXls(this.FileExcel);//上传XLS文件
                ImportXlsToData(filename);//将XLS文件内容导入
                //删除文件
                if (filename != string.Empty && File.Exists(filename))
                {
                    File.Delete(filename);//删除上传的文件
                }
            }
             catch (Exception ex)
             {
                this.WriteErrorXML(ex);//出错处理
                this.lblMessage.Text = ex.Message;//错误显示
            }
             finally
             {
                //重新加载页面
                LoadData();
            }

以下是各个主要的函数。
其中ImportXlsToData是使用OleDb链接Excel文件并将其数据导入Dataset中；
UpdateXls是上传Xls文件（其实应该写成UpLoadXls......-_-!）;
AddDatasetToSQL是将Dataset导入数据库，其中Save函数是插入一行记录（这里没有给出该函数）。

         /**/ /// <summary>
        /// 从Excel提取数据--》Dataset
        /// </summary>
        /// <param name="filename">Excel文件路径名</param>
         private   void  ImportXlsToData( string  fileName)
         {
            try
            {
                if (fileName == string.Empty) 
                { 
                    throw new ArgumentNullException("上传文件失败！");
                }
                //
                string oleDBConnString = String.Empty; 
                oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;"; 
                oleDBConnString += "Data Source="; 
                oleDBConnString += fileName; 
                oleDBConnString += ";Extended Properties=Excel 8.0;"; 
                //
                OleDbConnection oleDBConn = null; 
                OleDbDataAdapter oleAdMaster = null; 
                DataTable m_tableName=new DataTable();
                DataSet ds=new DataSet(); 

                oleDBConn = new OleDbConnection(oleDBConnString); 
                oleDBConn.Open(); 
                m_tableName=oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null); 

                if (m_tableName != null && m_tableName.Rows.Count > 0) 
                { 

                    m_tableName.TableName =m_tableName.Rows[0]["TABLE_NAME"].ToString(); 

                } 
                string sqlMaster; 
                sqlMaster=" SELECT *  FROM ["+m_tableName.TableName+"]"; 
                oleAdMaster=new OleDbDataAdapter(sqlMaster,oleDBConn); 
                oleAdMaster.Fill(ds,"m_tableName"); 
                oleAdMaster.Dispose();
                oleDBConn.Close();
                oleDBConn.Dispose();

                //测试是否提取数据
                //this.Datagrid1.DataSource = ds.Tables["m_tableName"];
                //this.Datagrid1.DataBind();
                //将Dataset中数据导入SQL
                AddDatasetToSQL(ds);

            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

         // 上传Excel文件
         private   string  UpdateXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile)
         {
            string orifilename = string.Empty;
            string uploadfilepath = string.Empty;
            string modifyfilename = string.Empty;
            string fileExtend = "" ;//文件扩展名
            int fileSize = 0;//文件大小
            try
            {
                if(inputfile.Value != string.Empty)
                {
                    //得到文件的大小
                    fileSize = inputfile.PostedFile.ContentLength;
                    if(fileSize == 0 )
                    {
                        throw new Exception("找不到该文件！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
                    if(fileExtend.ToLower() != "xls")
                    {
                        throw new Exception("请确认您所导入的文件是否EXCEL文件！！");
                    }
                    //路径
                    uploadfilepath = System.Web.HttpContext.Current.Server.MapPath(".")+path;
                    //新文件名
                    modifyfilename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() 
                        + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() 
                        + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() 
                        + DateTime.Now.Millisecond.ToString();
                    modifyfilename += "."+inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
                    if (!dir.Exists)
                    {
                        dir.Create();
                    }
                    orifilename = uploadfilepath+modifyfilename;
                    //如果存在,删除文件
                    if(File.Exists(orifilename))
                    {
                        File.Delete(orifilename);
                    }
                    // 上传文件
                    inputfile.PostedFile.SaveAs(orifilename);
                }
                else
                {
                    throw new Exception("没有选择Excel文件!");
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
            return orifilename;
        }

         // 将Dataset的内容导入SQL
         private   bool  AddDatasetToSQL(DataSet pds)
         {
            int ic,ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < 7)
            {
                throw new Exception("导入Excel格式错误！Excel只有" + ic.ToString() + "列");
            }
            ir = pds.Tables[0].Rows.Count;
            if (pds != null && pds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0;i < pds.Tables[0].Rows.Count;i++)
                {
                    Save(pds.Tables[0].Rows[i][0].ToString(),pds.Tables[0].Rows[i][1].ToString(),
                        pds.Tables[0].Rows[i][2].ToString(),pds.Tables[0].Rows[i][3].ToString(),
                        pds.Tables[0].Rows[i][4].ToString(),pds.Tables[0].Rows[i][5].ToString(),
                        pds.Tables[0].Rows[i][6].ToString());
                }
            }
            else
            {
                throw new Exception("导入数据为空！");
            }
            return true;
        }

 

 

 

另外一段

/// <summary>
  /// 读取Excel文档
  /// </summary>
  /// <param name="Path">文件名称</param>
  /// <returns>返回一个数据集</returns>
  public DataSet ExcelToDS(string Path)
  {
   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
   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");  
   return ds;
  }

/// <summary>
  /// 写入Excel文档
  /// </summary>
  /// <param name="Path">文件名称</param>
  public bool SaveFP2toExcel(string Path)
  {
   try
   {
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
    OleDbConnection conn = new OleDbConnection(strConn);
    conn.Open(); 
    System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
    cmd.Connection =conn;
    //cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
    //cmd.ExecuteNonQuery ();
    for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
    {
     if(fp2.Sheets [0].Cells[i,0].Text!="")
     {
      cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
       fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
       "','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
      cmd.ExecuteNonQuery ();
     }
    }
    conn.Close ();
    return true;
   }
   catch(System.Data.OleDb.OleDbException ex)
   {
    System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
   }
   return false;
  }