.net实现将Excel中的数据导入数据库

　　在近期的项目中需要实现将Excel中的数据导入到数据库的功能，相信各位博友也会经常有这样的需求，现把我的实现方式与大家分享一下，如果哪位博友有更好的实现方式，欢迎补充！
 　　实现思路：先上传Excel文件，然后用OleDB连接Excel文件，将Excel数据导入Dataset，再将Dataset数据导入数据库，最后再将上传的Excel文件删除。
 　　前台代码：　　

<table class="Text" cellSpacing="1" cellPadding="0" width="100%" bgColor="#1d82d0" border="0">
  <tr bgColor="#ffffff">
    <td vAlign="top">
      <table class="Text" cellSpacing="0" cellPadding="0" width="100%" border="0">
        <tr>
          <td width="15">&nbsp;</td>
          <td vAlign="top" width="100%">
            <table class="Text" cellSpacing="1" cellPadding="0" width="100%" border="0">
              <tr height="30">
                <td style="WIDTH: 120px" width="120"><FONT face="宋体">请选择要导入的文件</FONT></td>
                <td style="WIDTH: 350px" align="left" width="350"><INPUT id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server"><FONT color="red"></FONT></td>
                <td class="hint"><FONT face="宋体"><asp:button id="BtnImport" Text="导 入" CssClass="button" Runat="server"></asp:button></FONT></td>
              </tr>
            </table>
          </td>
        </tr>
      </table>
      <asp:label id="LblMessage" runat="server" Font-Bold="True" ForeColor="Red"></asp:label>
    </td>
  </tr>
</table>

 

 　　后台代码：
 　　

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;

namespace GZPI.Service.AgenciesChannel
{
    /// <summary>
    /// ImportXlsToData 的摘要说明。
    /// </summary>
    public class ImportXlsToDataBase : GZPI.Service.AgenciesChannel.AgenciesBasePage
    {
        protected System.Web.UI.HtmlControls.HtmlInputFile FileExcel;
        protected System.Web.UI.WebControls.Button BtnImport;
        protected System.Web.UI.WebControls.Label LblMessage;
        protected ZsoftDataAccess.DataAccess _da = new ZsoftDataAccess.DataAccess();
        private void Page_Load(object sender, System.EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
        }

        #region Web 窗体设计器生成的代码
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /// <summary>
        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
        /// 此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {    
            this.BtnImport.Click += new System.EventHandler(this.BtnImport_Click);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion


        //// <summary>
        /// 从Excel提取数据--》Dataset
        /// </summary>
        /// <param name="filename">Excel文件路径名</param>
        private void ImportXlsToData(string fileName)
        {
            try
            {
                if (fileName == string.Empty) 
                {                     
                    throw new ArgumentNullException("Excel文件上传失败！");
                }
                
                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();
    
                AddDatasetToSQL(ds,14);
            }
            catch(Exception ex)
            {
                throw ex;        
            }
        }
        
        /// <summary>
        /// 上传Excel文件
        /// </summary>
        /// <param name="inputfile">上传的控件名</param>
        /// <returns></returns>
        private string UpLoadXls(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("导入的Excel文件大小为0，请检查是否正确！");
                    }
                    //得到扩展名
                    fileExtend = inputfile.Value.Substring(inputfile.Value.LastIndexOf(".")+1);
                    if(fileExtend.ToLower() != "xls")
                    {                        
                        throw new Exception("你选择的文件格式不正确，只能导入EXCEL文件！");
                    }
                    //路径
                    uploadfilepath = Server.MapPath("~/Service/GraduateChannel/GraduateApply/ImgUpLoads");
                    //新文件名
                    modifyfilename = System.Guid.NewGuid().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;
        }
        
        /// <summary>
        /// 将Dataset的数据导入数据库
        /// </summary>
        /// <param name="pds">数据集</param>
        /// <param name="Cols">数据集列数</param>
        /// <returns></returns>
        private bool AddDatasetToSQL(DataSet pds,int Cols)
        {
            int ic,ir;
            ic = pds.Tables[0].Columns.Count;
            if (pds.Tables[0].Columns.Count < Cols)
            {
                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 = 1;i < pds.Tables[0].Rows.Count;i++)
                {
                    Add(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(),pds.Tables[0].Rows[i][7].ToString(),
                        pds.Tables[0].Rows[i][8].ToString(),pds.Tables[0].Rows[i][9].ToString(),
                        pds.Tables[0].Rows[i][10].ToString(),pds.Tables[0].Rows[i][11].ToString(),
                        pds.Tables[0].Rows[i][12].ToString(),pds.Tables[0].Rows[i][13].ToString());
                }
            }
            else
            {                
                throw new Exception("导入数据为空！");    
            }
            return true;
        }

        /// <summary>
        /// 插入数据到数据库
        /// </summary>
        public void Add(string B0105,string SequenceNumber,string A0101,string OldGuid,string RecordType,string BirthDay,string A0177,string MobliePhone,string TelePhone,string ContractBeginDate,string ContractEndDate,string ContractPayCharge,string TransactDate)
        {    
            string sql="select * from PersonRecord where A0177='"+A0177+"'";
            DataTable dt=_da.ExecuteDataTable(sql);
            if(dt.Rows.Count==0)
            {
                //insert into PersonRecord
                StringBuilder strSql=new StringBuilder();
                strSql.Append("insert into PersonRecord(");
                strSql.Append("ID,B0105,SequenceNumber,A0101,OldGuid,RecordType,BirthDay,A0177,MobliePhone,TelePhone,ContractBeginDate,ContractEndDate,ContractPayCharge,TransactDate");
                strSql.Append(")");
                strSql.Append(" values (");
                strSql.Append("'"+System.Guid.NewGuid().ToString()+"',");
                strSql.Append("'"+B0105+"',");
                strSql.Append("'"+SequenceNumber+"',");
                strSql.Append("'"+A0101+"',");
                strSql.Append("'"+OldGuid+"',");
                strSql.Append("'"+RecordType+"',");
                strSql.Append("'"+BirthDay+"',");
                strSql.Append("'"+A0177+"',");
                strSql.Append("'"+MobliePhone+"',");
                strSql.Append("'"+TelePhone+"',");
                strSql.Append("'"+ContractBeginDate+"',");
                strSql.Append("'"+ContractEndDate+"',");
                strSql.Append("'"+ContractPayCharge+"',");
                strSql.Append("'"+TransactDate+"'");
                strSql.Append(")");

                //insert into PersonnelAgencyInfo
                string GUID=System.Guid.NewGuid().ToString();
                strSql.Append("    insert into PersonnelAgencyInfo(");
                strSql.Append("PersonnelAgencyID, A0101, A0177, PersonnelAgencyState, PersonnelAgencyStateCode, Checker, CheckTime");
                strSql.Append(")");
                strSql.Append(" values (");
                strSql.Append("'"+GUID+"',");
                strSql.Append("'"+A0101+"',");
                strSql.Append("'"+A0177+"',");
                strSql.Append("'通过',");
                strSql.Append("'1',");
                strSql.Append("'"+GZPI.Service.Common.AuthManager.CurrentUserNameCN.ToString()+"',");
                strSql.Append("'"+DateTime.Now.ToString()+"'");
                strSql.Append(")");    

                //insert into PersonnelAgencyRecord
                strSql.Append("    insert into PersonnelAgencyRecord(");
                strSql.Append("PersonnelAgencyRecordID, PersonnelAgencyID, PersonnelAgencyState, Checker, CheckTime");
                strSql.Append(")");
                strSql.Append(" values (");
                strSql.Append("'"+System.Guid.NewGuid().ToString()+"',");
                strSql.Append("'"+GUID+"',");
                strSql.Append("'通过',");
                strSql.Append("'系统导入数据',");
                strSql.Append("'"+DateTime.Now.ToString()+"'");
                strSql.Append(")");
                _da.ExecuteNonQuery(strSql.ToString());        
            }
        }

        private void BtnImport_Click(object sender, System.EventArgs e)
        {
            string filename = string.Empty;
            try
            {
                filename = UpLoadXls(FileExcel);//上传XLS文件
                ImportXlsToData(filename);//将XLS文件的数据导入数据库                
                if (filename != string.Empty && System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);//删除上传的XLS文件
                }
                LblMessage.Text="数据导入成功！";
            }
            catch(Exception ex)
            {
                LblMessage.Text=ex.Message;                                    
            }
        }
    }
}