excel到sql [英] excel to sql

查看：67 发布时间：2019/6/22 10:39:12 C#

本文介绍了excel到sql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

aspx.cs代码:

aspx.cs code:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
//using 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;

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

    }
    protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\trainee.soft\Desktop\example.xls;Extended Properties=Excel 8.0");
        try
        {
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
            oconn.Open();
            OleDbDataReader odb = ocmd.ExecuteReader();
            string traded_date = "";
            string ex_id = "";
            string comname_id = "";
            string closeprice = "";
            string unit_id = "";
            string spot_attribute = "";
            string estimate_type = "";
            string source_nm = "";
            string change = "";
            string status = "";
            while (odb.Read())
            {
                traded_date = valid(odb, 0);
                ex_id = valid(odb, 1);
                comname_id = valid(odb, 2);
                closeprice = valid(odb, 5);
                unit_id = valid(odb, 7);
                spot_attribute = valid(odb, 8);
                estimate_type = valid(odb, 9);
                source_nm = valid(odb, 10);
                change = valid(odb, 11);
                status = valid(odb, 12);
                insertdataintosql(traded_date, ex_id, comname_id, closeprice, unit_id, spot_attribute, estimate_type, source_nm, change, status);
                
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
     
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
    public void insertdataintosql(string traded_date, string ex_id, string comname_id, string closeprice, string unit_id, string spot_attribute,string estimate_type,string source_nm,string change,string status)
    {
        SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\sample.mdf;Integrated Security=True;User Instance=True");
        
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "insert into spot1(traded_date,ex_id,comname_id,closeprice,unit_id,spot_attribute,estimate_type,source_nm,change,status) values(@traded_date,@ex_id,@comname_id,@closeprice,@unit_id,@spot_attribute,@estimate_type,@source_nm,@change,@status)";
        cmd.Parameters.Add("@traded_date", SqlDbType.DateTime).Value = traded_date;
        cmd.Parameters.Add("@ex_id", SqlDbType.NVarChar).Value = ex_id;
        cmd.Parameters.Add("@comname_id", SqlDbType.NVarChar).Value = comname_id;
        cmd.Parameters.Add("@closeprice", SqlDbType.Int).Value = Convert.ToInt32(closeprice);
        cmd.Parameters.Add("@unit_id", SqlDbType.NVarChar).Value = unit_id;
        cmd.Parameters.Add("@spot_attribute", SqlDbType.NVarChar).Value = spot_attribute;
        cmd.Parameters.Add("@estimate_type", SqlDbType.NVarChar).Value = estimate_type;
        cmd.Parameters.Add("@source_nm", SqlDbType.NVarChar).Value = source_nm;
        cmd.Parameters.Add("@change", SqlDbType.Int).Value = Convert.ToInt32(change);
        cmd.Parameters.Add("@status", SqlDbType.Int).Value = Convert.ToInt32(status);
        cmd.CommandType = CommandType.Text;
        
        con.Open();
        cmd.ExecuteNonQuery();
       
        con.Close();
    }
    protected void viewdata_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\sample.mdf;Integrated Security=True;User Instance=True");
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("select * from spot1", con);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (DataException de)
        {
            lblmsg.Text = de.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Shown Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    protected void deletedata_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\sample.mdf;Integrated Security=True;User Instance=True");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "delete from spot1";
            cmd.CommandType = CommandType.Text;
            con.Open();
            cmd.ExecuteScalar();
            con.Close();
        }
        catch (DataException de1)
        {
            lblmsg.Text = de1.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Deleted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
    }
}

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="excel2sql.aspx.cs" Inherits="excel2sql" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table align="center" class="style1">
            <tr>
                <td align="center">
                    <asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">Insert Data</asp:LinkButton>
                </td>
                <td align="center">
                    <asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">View Data</asp:LinkButton>
                </td>
                <td align="center">
                    <asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">Delete Data</asp:LinkButton>
                </td>
            </tr>
            <tr>
                <td colspan="9" align="center">
                    <asp:GridView ID="GridView1" runat="server">
                    </asp:GridView>
                </td>
            </tr>
             <tr>
                <td colspan="9" align="center">
                    <asp:Label ID="lblmsg" runat="server" Width="500px"></asp:Label>
                 </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

通过此代码，我可以从excel插入到sql数据库.但只有一次，我只能打开excel文件.运行excel2sql.aspx页后，Excel工作表无法打开.如果正在尝试打开无法访问"消息.但我只想通过excel文件插入数据.所以每次我要打开Excel文件时.该怎么办.

by this code i can able to insert from excel to sql database. but only one time only i can able to open the excel file. after running the excel2sql.aspx page excel sheet is not opening. if am trying to open "cannot access" message is coming. but i want to insert data through excel file only. so every time i want to open excel file. what to do. help?

推荐答案

"，oconn)； oconn.Open(); OleDbDataReader odb = ocmd.ExecuteReader(); 字符串 traded_date = " ; 字符串 ex_id = " ; 字符串 comname_id = " ; 字符串 closeprice = " ; 字符串 unit_id = " ; 字符串 spot_attribute = " ; 字符串 estimate_type = " ; 字符串 source_nm = " ; 字符串 change = " ; 字符串 status = " ; while (odb.Read()) { traded_date =有效(odb， 0 )； ex_id =有效(odb， 1 )； comname_id = valid(odb， 2 ); closeprice =有效(odb， 5 )； unit_id = valid(odb， 7 ); spot_attribute = valid(odb， 8 ); 估计类型=有效(odb， 9 )； source_nm =有效(odb， 10 )；更改=有效(odb， 11 )；状态=有效(odb， 12 )； insertdataintosql(traded_date，ex_id，comname_id，closeprice，unit_id，spot_attribute，estimate_type，source_nm，更改，状态)； } oconn.Close(); } 捕获(DataException ee) { lblmsg.Text = ee.Message; lblmsg.ForeColor = System.Drawing.Color.Red; } 最终 { lblmsg.Text = " ; lblmsg.ForeColor = System.Drawing.Color.Green; } } 受保护的字符串有效(OleDbDataReader myreader， int stval)// 如果发现任何列为空，则将它们替换为零 { 对象 val = myreader [stval]; 如果(值！= DBNull.Value) 返回 val.ToString(); 其他返回 Convert.ToString( 0 ); } 公共无效 insertdataintosql(字符串 traded_date， string ex_id， string comname_id， string closeprice，字符串 unit_id，字符串 spot_attribute，字符串 estimate_type ，字符串 source_nm，字符串更改，字符串地位) { SqlConnection con = 新 SqlConnection(" ); SqlCommand cmd = 新 SqlCommand(); cmd.Connection = con; cmd.CommandText = " ; cmd.Parameters.Add(" ，SqlDbType.DateTime).Value = traded_date; cmd.Parameters.Add(" ，SqlDbType.NVarChar).Value = ex_id; cmd.Parameters.Add(" ，SqlDbType.NVarChar).Value = comname_id; cmd.Parameters.Add(" ，SqlDbType.Int).Value = Convert.ToInt32(收盘价); cmd.Parameters.Add(" ，SqlDbType.NVarChar).Value = unit_id; cmd.Parameters.Add(" ，SqlDbType.NVarChar).值= spot_attribute; cmd.Parameters.Add(" ，SqlDbType.NVarChar). cmd.Parameters.Add(" ，SqlDbType.NVarChar).值= source_nm; cmd.Parameters.Add(" ，SqlDbType.Int).Value = Convert.ToInt32(改变); cmd.Parameters.Add(" ，SqlDbType.Int).Value = Convert.ToInt32(地位); cmd.CommandType = CommandType.Text; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } 受保护的无效 viewdata_Click(对象发件人，EventArgs e) { SqlConnection con = 新 SqlConnection(" ); 尝试 { SqlDataAdapter sda = 新 SqlDataAdapter(" ，骗局)； DataSet ds = 新 DataSet(); sda.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); } 捕获(DataException de) { lblmsg.Text = de.Message; lblmsg.ForeColor = System.Drawing.Color.Red; } 最终 { lblmsg.Text = " ; lblmsg.ForeColor = System.Drawing.Color.Green; } } 受保护的无效 deletedata_Click(对象发件人，EventArgs e) { SqlConnection con = 新 SqlConnection(" ); 尝试 { SqlCommand cmd = 新 SqlCommand(); cmd.Connection = con; cmd.CommandText = " ; cmd.CommandType = CommandType.Text; con.Open(); cmd.ExecuteScalar(); con.Close(); } 捕获(DataException de1) { lblmsg.Text = de1.Message; lblmsg.ForeColor = System.Drawing.Color.Red; } 最终 { lblmsg.Text = " ; lblmsg.ForeColor = System.Drawing.Color.Red; } } }

", oconn); oconn.Open(); OleDbDataReader odb = ocmd.ExecuteReader(); string traded_date = ""; string ex_id = ""; string comname_id = ""; string closeprice = ""; string unit_id = ""; string spot_attribute = ""; string estimate_type = ""; string source_nm = ""; string change = ""; string status = ""; while (odb.Read()) { traded_date = valid(odb, 0); ex_id = valid(odb, 1); comname_id = valid(odb, 2); closeprice = valid(odb, 5); unit_id = valid(odb, 7); spot_attribute = valid(odb, 8); estimate_type = valid(odb, 9); source_nm = valid(odb, 10); change = valid(odb, 11); status = valid(odb, 12); insertdataintosql(traded_date, ex_id, comname_id, closeprice, unit_id, spot_attribute, estimate_type, source_nm, change, status); } oconn.Close(); } catch (DataException ee) { lblmsg.Text = ee.Message; lblmsg.ForeColor = System.Drawing.Color.Red; } finally { lblmsg.Text = "Data Inserted Sucessfully"; lblmsg.ForeColor = System.Drawing.Color.Green; } } protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero { object val = myreader[stval]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } public void insertdataintosql(string traded_date, string ex_id, string comname_id, string closeprice, string unit_id, string spot_attribute,string estimate_type,string source_nm,string change,string status) { SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\sample.mdf;Integrated Security=True;User Instance=True"); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "insert into spot1(traded_date,ex_id,comname_id,closeprice,unit_id,spot_attribute,estimate_type,source_nm,change,status) values(@traded_date,@ex_id,@comname_id,@closeprice,@unit_id,@spot_attribute,@estimate_type,@source_nm,@change,@status)"; cmd.Parameters.Add("@traded_date", SqlDbType.DateTime).Value = traded_date; cmd.Parameters.Add("@ex_id", SqlDbType.NVarChar).Value = ex_id; cmd.Parameters.Add("@comname_id", SqlDbType.NVarChar).Value = comname_id; cmd.Parameters.Add("@closeprice", SqlDbType.Int).Value = Convert.ToInt32(closeprice); cmd.Parameters.Add("@unit_id", SqlDbType.NVarChar).Value = unit_id; cmd.Parameters.Add("@spot_attribute", SqlDbType.NVarChar).Value = spot_attribute; cmd.Parameters.Add("@estimate_type", SqlDbType.NVarChar).Value = estimate_type; cmd.Parameters.Add("@source_nm", SqlDbType.NVarChar).Value = source_nm; cmd.Parameters.Add("@change", SqlDbType.Int).Value = Convert.ToInt32(change); cmd.Parameters.Add("@status", SqlDbType.Int).Value = Convert.ToInt32(status); cmd.CommandType = CommandType.Text; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } protected void viewdata_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\sample.mdf;Integrated Security=True;User Instance=True"); try { SqlDataAdapter sda = new SqlDataAdapter("select * from spot1", con); DataSet ds = new DataSet(); sda.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); } catch (DataException de) { lblmsg.Text = de.Message; lblmsg.ForeColor = System.Drawing.Color.Red; } finally { lblmsg.Text = "Data Shown Sucessfully"; lblmsg.ForeColor = System.Drawing.Color.Green; } } protected void deletedata_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\sample.mdf;Integrated Security=True;User Instance=True"); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "delete from spot1"; cmd.CommandType = CommandType.Text; con.Open(); cmd.ExecuteScalar(); con.Close(); } catch (DataException de1) { lblmsg.Text = de1.Message; lblmsg.ForeColor = System.Drawing.Color.Red; } finally { lblmsg.Text = "Data Deleted Sucessfully"; lblmsg.ForeColor = System.Drawing.Color.Red; } } }

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="excel2sql.aspx.cs" Inherits="excel2sql" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table align="center" class="style1">
            <tr>
                <td align="center">
                    <asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">Insert Data</asp:LinkButton>
                </td>
                <td align="center">
                    <asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">View Data</asp:LinkButton>
                </td>
                <td align="center">
                    <asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">Delete Data</asp:LinkButton>
                </td>
            </tr>
            <tr>
                <td colspan="9" align="center">
                    <asp:GridView ID="GridView1" runat="server">
                    </asp:GridView>
                </td>
            </tr>
             <tr>
                <td colspan="9" align="center">
                    <asp:Label ID="lblmsg" runat="server" Width="500px"></asp:Label>
                 </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

通过此代码，我可以从excel插入到sql数据库.但只有一次，我只能打开excel文件.运行excel2sql.aspx页后，Excel工作表无法打开.如果正在尝试打开无法访问"消息.但我只想通过excel文件插入数据.所以每次我要打开Excel文件时.该怎么办.帮助吗?

这篇关于excel到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

excel到sql [英] excel to sql

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

excel到sql [英] excel to sql

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭