简单excel导入数据库

来源：互联网发布：淘宝上有特色的礼物店编辑：程序博客网时间：2024/05/12 11:58

一个简单的excel导入导出的例子

1 新建个网站：

2 页面html如下：

<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Label ID="Label1" runat="server"></asp:Label>
        <br />
        <asp:Button ID="btnIn" runat="server" onclick="btnIn_Click" Text="上传并导入" />

    </div>
    </form>
    <p>
        点击此处下载模版<a href="学生.xls">学生.xls</a></p>
</body>
</html>

3.页面后台代码如下：

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.UI.MobileControls;
using System.Collections;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    }
    protected void btnIn_Click(object sender, EventArgs e)
    {
        #region//判断
        string filename;
        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        {
            Response.Write("<script>alert('请您选择Excel文件!')</script>");
            return;
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            Response.Write("<script>alert('只可以选择Excel文件!')</script>");
            return;//当选择的不是Excel文件时,返回
        }
        filename = FileUpload1.FileName;              //获取Execle文件名
        ViewState["filename"] = filename;
        string path = this.FileUpload1.PostedFile.FileName; //获取文件的绝对路径注意：如果获取到的只是文件名的话原因是ie浏览器安全限制，解决方法 “工具-Internet选项-安全-自定义级别-启用‘将文件上载到服务器时包含本地路径’ 即可，或者把安全级别调小也可
        if (path == filename)
        {
            Response.Write("<script>alert('出错啦！原因:获取不到文件的绝对路径，导致附加不成功。解决办法：打开IE浏览器，依次单击“工具-Internet选项-安全-自定义级别-启‘将文件上载到服务器时包含本地路径’” 即可,或者依次单击“工具-Internet选项-安全-自定义级别-‘默认级别’或者更低”')</script>");
            return;
        }
        Label1.Text = "当前操作EXCEL文件为：" + filename;
        Label1.Visible = true;

        string savePath = Server.MapPath("ExcelModle/");//设置保存文件夹
        if (!System.IO.Directory.Exists(savePath))//如果不存在则创建文件夹
        {
            System.IO.Directory.CreateDirectory(savePath);
        }
        FileUpload1.SaveAs(savePath + filename);//上传并保存excel文件
        #endregion
        #region 读取excel
        ExcelHelper bllExcel = new ExcelHelper();
        DataSet ds = bllExcel.CheckExecleDs(savePath + filename, filename); //调用自定义方法读取excel 返回 DataSet
        if (ds != null && ds.Tables[0].Rows.Count > 0)
        {

            string[] list=new string[2] ;
            for (int i = 0; i <ds.Tables[0].Columns.Count; i++)
            {
                string columnName = ds.Tables[0].Columns[i].ColumnName;//获取列名
                if (columnName == "姓名")
                {
                    list[0] = columnName;//保存列名
                }
                if (columnName == "联系电话")
                {
                    list[1] = columnName;//保存列名
                }

            }
            foreach (DataRow row in ds.Tables[0].Rows)
            {   //循环取出行数据并添加到数据库
                //for(int i=0;i<=list.Count;i++){
                string name = row[list[0].ToString()].ToString();
                string tell = row[list[1].ToString()].ToString();
                string sql = "insert into users (user_name,user_tell) values('"+name+"','"+tell+"')";
                DB mydb = new DB();
                if (!mydb.openDB())
                {
                    Response.Write("error!");
                    return;
                }
                mydb.execute(sql);
                mydb.closeDB();
                //}
            }

}
#endregion

}
}

3.新建excel读取类类ExcelHelper.cs 代码如下（）

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using System.Data.OleDb;

    public class ExcelHelper
    {

        #region 读取excel
        /// <summary>查询EXCEL电子表格添加到DATASET
        ///
        /// </summary>
        /// <param name="filenameurl">服务器路径</param>
        /// <param name="table">表名</param>
        ///
        /// <returns></returns>
        public DataSet CheckExecleDs(string filenameurl, string table)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            odda.Fill(ds, table);
            conn.Close();
            return ds;
        }
#endregion

}//end class

4,新建数据库访问类 DB.cs

代码如下：

using System;
using System.Data;
using System.Data.OleDb;

/// <summary>
/// DB 的摘要说明
/// </summary>
public class DB
{
    public Boolean status;
    public string errmsg;
    private OleDbConnection conn;
    public string optionresult = "";
    public DB()
    {
        status = false;
        errmsg = "";
    }
    public Boolean openDB()
    {
        //string dbhost = "db.88138.cc";
        //string dbhost = "cdn760.fastcache.biz";
        //string username = "h192316";
        //string password = "odaynj";
        string database = System.Web.HttpContext.Current.Server.MapPath("~/") + "//AGdata//AG.mdb";//注意数据库要放在新建文件夹AGdata中
        //string connstr = "Server=" + dbhost + ";database=" + database + ";uid=" + username + ";pwd=" + password + ";";
        string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + database + ";";
        //string dbhost = "db.pc95500.com";
        //string username = "h191241";
        //string password = "jasyjasy";
        //string database = "dbh191241";
        //string connstr = "Server=" + dbhost + ";database=" + database + ";uid=" + username + ";pwd=" + password + ";";
        conn = new OleDbConnection(connstr);
        try
        {
            conn.Open();
            status = true;
        }
        catch (Exception ee)
        {
            status = false;
            errmsg = ee.Message;
        }
        return status;
    }
    public void getchild(string typeid, int depth, DB mydb)
    {
        string depstr = "";
        for (int i = 0; i < depth; i++)
        {
            depstr += "├";
        }
        string sql = "select TypeID,TypeName from webtype where typeid=" + typeid;
        DataTable dt = mydb.getDataSet(sql);
        foreach (DataRow dr in dt.Rows)
        {
            optionresult += "<option value='"+dr["TypeID"].ToString()+"'>"+depstr+dr["TypeName"].ToString()+"</option>";
            //ListItem li = new ListItem();
            //li.Text = depstr + dr["TypeName"].ToString();
            //li.Value = dr["TypeID"].ToString();
            //WebType.Items.Add(li);
        }
        sql = "select typeid from webtype where parentid=" + typeid;
        dt = mydb.getDataSet(sql);
        foreach (DataRow dr in dt.Rows)
        {
            getchild(dr["typeid"].ToString(), depth + 1, mydb);
        }
    }
    /// <summary>
    /// 返回DataTable
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public DataTable getDataSet(string sql)
    {
        try
        {
            OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception ee)
        {
            errmsg = ee.Message;
            return null;
        }
    }
    /// <summary>
    /// 返回datatable【0】
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <param name="currentpage"> 页数</param>
    /// <param name="pagesize">每页数据量</param>
    /// <returns></returns>
    public DataTable getFenYeDataSet(string sql, int currentpage, int pagesize)
    {
        try
        {
            OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, (currentpage - 1) * pagesize, pagesize, "table1");
            return ds.Tables[0];
        }
        catch (Exception ee)
        {
            errmsg = ee.Message;
            return null;
        }
    }
    /// <summary>
    /// 是否存在
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public int execute(string sql)
    {
        try
        {
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            return cmd.ExecuteNonQuery();
        }
        catch (Exception ee)
        {
            errmsg = ee.Message;
            return -1;
        }
    }
    public void closeDB()
    {
        conn.Close();
        status = false;
    }
}

5 数据库结构这里是acess数据库

user_id 自增

user_name 文本

user_tell 文本

相关例子在此可以下载http://download.csdn.net/source/3228865 发布的时候忘了设置成0分资源了···不知道咋滴改··现在要1分抱歉抱歉