把Excel表中的数据导入到数据库

最新推荐文章于 2022-08-10 15:57:20 发布

花落留香

最新推荐文章于 2022-08-10 15:57:20 发布

阅读量1.1k

点赞数

分类专栏： HTTP JSP

JSP 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

HTTP

3 篇文章 0 订阅

订阅专栏

Excel中的数据如图：

数据库表中的结构：

前台页面：


<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="导入" οnclick="Button1_Click" />
    </div>
    </form>
</body>

后台代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    #region
    /// <summary>
    /// 把Excel中的数据存到DataTable
    /// </summary>
    /// <param name="uploadPath">Excel存放的路径</param>
    /// <returns></returns>
    private DataTable GetExcelTable(string uploadPath)
    {
        DataSet ds = new DataSet();
        string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1  F2  F3
        OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
        try
        {
            Conn.Open();
            string sql_str = "select * from [Sheet1$]";
            OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
            da.Fill(ds, "excel_data");
            Conn.Close();
        }
        catch
        {
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
            return null;
        }
        finally
        {
            Conn.Dispose();
        }
        if (ds == null)
        {
            return null;
        }
        if (ds.Tables.Count < 1)
        {
            return null;
        }
        return ds.Tables[0];
    }
    #endregion
    /// <summary>
    /// 把DataTable里德数据添加到数据库中的表里
    /// </summary>
    /// <param name="table1"></param>
    public void InsertTable(DataTable table1)
    {
        string strInsert = "insert into ceshi values（" + GenrateSql(table1) + ")";
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString);
        conn.Open();
        SqlCommand com = new SqlCommand(strInsert, conn);

        for (int i = 0; i < table1.Rows.Count; i++)
        {
          
            for(int j=0;j<table1.Columns.Count;j++)
            {
                SqlParameter p1 = new SqlParameter("@value"+j.ToString(), SqlDbType.VarChar);
                p1.Value=table1.Rows[i][j].ToString();
                com.Parameters.Add(p1);
            }
            com.ExecuteNonQuery();
            com.Parameters.Clear();///在同一事务中若要多次执行同一SQL语句，必须在下一次执行前清理前一次的参数。否则会报变量名 @XXX 已声明。变量名在批查询或存储过程内部必须唯一的错误
        }
        conn.Close();
    }
    /// <summary>
    /// SQL语句中的参数
    /// </summary>
    /// <param name="tbl"></param>
    /// <returns></returns>
    private string GenrateSql(DataTable tbl)
    {
         string temp="";
         for (int i = 0; i < tbl.Columns.Count; i++)
        {
           if(i==0)
               temp+="@value"+i.ToString();
             else
             {
             temp+=","+"@value"+i.ToString();
             }
        }
         return "("+temp+")";
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string path = @"C:UsersAdministratorDesktopstudent.xls";//Excel存放的位置
        GetExcelTable(path);
        InsertTable(GetExcelTable(path));
    }
}