将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;
        }

转载于:https://www.cnblogs.com/nikytwo/archive/2008/05/28/1209102.html