c# 导入EXCEL到数据库中

来源：互联网发布：麻瓜编程 python 视频编辑：程序博客网时间：2024/06/09 17:26

1.类似于DBHelper

public class ImportDAL
    {
        //成员字段
        OleDbConnection m_con = null;
        public OleDbConnection Connection
        {
            get { return m_con; }
        }
        OleDbCommand cmd = null;
        OleDbDataAdapter dad = null;

        //单例模式
        private   ImportDAL(string filepath)
        {
            //连接每个方法都要用。先实例化
            string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filepath + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
            m_con = new OleDbConnection(SourceConstr);
        }

private static ImportDAL selfObj;

        public static ImportDAL GetInstance(string filepath)
        {
            if (selfObj == null)
            {
                selfObj = new ImportDAL(filepath);
            }
            selfObj.Connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filepath + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
            return selfObj;
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool UpdateOpear(string sql)
        {
            bool flag = false;
            cmd = new OleDbCommand(sql, m_con);
            m_con.Open();
            flag = cmd.ExecuteNonQuery() > 0;
            m_con.Close();
            return flag;

}

        /// <summary>
        /// 取首行首列数据
        /// </summary>
        /// <param name="sql">select语句</param>
        /// <returns>object</returns>
        public object GetScalar(string sql)
        {
            object value = null;
            cmd = new OleDbCommand(sql, m_con);
            m_con.Open();
            value = cmd.ExecuteScalar();
            m_con.Close();
            return value;
        }

        /// <summary>
        /// 得到数据表
        /// </summary>
        /// <param name="sql">select语句</param>
        /// <returns>DataTable</returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();
            dad = new OleDbDataAdapter(sql, m_con);
            dad.Fill(dt);
            return dt;
        }
        public DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                dad = new OleDbDataAdapter(sql, m_con);
                dad.Fill(ds, "[sheet1$]");
            }
            finally
            {
                m_con.Close();
            }
            return ds;
        }

把EXCEL文件上传到服务器并返回文件路径

        private String Typename(FileUpload fileloads)
        {
            string fullfilename = fileloads.PostedFile.FileName;
            string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1);
            string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1);
            string murl = "";
            if (type == "xls" || type == "xlsx")
            {
                fileloads.PostedFile.SaveAs(Server.MapPath("excel") + "\\" + filename);
                murl = (Server.MapPath("excel") + "\\" + filename).ToString();
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert(' 导入文件格式不对！')", true);
            }
            return murl;
        }

string fileurl = Typename(this.flUpload);//调用typename方法取得excel文件路径
DataSet ds =ImportDAL.GetInstance(fileurl).GetDataSet("SELECT * FROM [Sheet1$]");

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                ViewManangeModel vmm = new ViewManangeModel();
                               vmm.A = ds.Tables[0].Rows[i][1].ToString().Trim ();
                                vmm.B = ds.Tables[0].Rows[i][2].ToString().Trim ();
                                vmm.H= ds.Tables[0].Rows[i][3].ToString().Trim();
                                vmm.D = ds.Tables[0].Rows[i][4].ToString();

DAL.CC.ADD(vmm);//添加到数据库中

}