从excel导入数据到sql

最新推荐文章于 2018-03-27 16:49:00 发布

xttxqjfg

最新推荐文章于 2018-03-27 16:49:00 发布

阅读量574

点赞数

分类专栏： C# web网页开发

本文链接：https://blog.csdn.net/xttxqjfg/article/details/8549585

版权

web网页开发同时被 2 个专栏收录

26 篇文章 0 订阅

订阅专栏

12 篇文章 0 订阅

订阅专栏

这几天一直在做这个东西，在网上也找了很多，大部分不是这个函数封装了就是那个方法不公开，很多都没法用，我就想不通了，既然都放出了那么多的代码干嘛还要藏起来一部分？？废话不说了，下面是我的代码，也是借鉴了别人的一些代码修改后完成的，不能说很好，只能说实现了这个功能，需要的可以贴过去看看，根据自己的需要修改。。

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="exceltosql.aspx.cs" Inherits="exceltosql" %>

<!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 id="Head1" runat="server">  
    <title></title>  
</head>  
<body>  
    <form id="form1" runat="server">  
    <div>  
        <asp:FileUpload ID="FileUpload1" runat="server" />  
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />  
    </div>  
    </form>  
</body>  
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration; 

public partial class exceltosql : System.Web.UI.Page
{
    public static SqlConnection con;
    public static SqlCommand com;
    public static string sqlstr = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;   //在web配置文件中需要设置数据库连接字符串

    protected void Page_Load(object sender, EventArgs e)
    {

    }


    protected void Button1_Click(object sender, EventArgs e)
    {
        //获取文件路径  
        string filePath = this.FileUpload1.PostedFile.FileName;
        if (filePath != "")
        {
            if (filePath.Contains("xls"))//判断文件是否存在  
            {
                InputExcel(filePath);
            }
            else
            {
                Response.Write("请检查您选择的文件是否为Excel文件！谢谢！");
            }
        }

        else
        {
            Response.Write("请先选择导入文件后，再执行导入！谢谢！");
        }
    }

    private void InputExcel(string pPath)
    {
        string conn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + pPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
        OleDbConnection oleCon = new OleDbConnection(conn);
        oleCon.Open();
        string Sql = "select * from [Sheet1$]";
        OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, oleCon);
        DataSet ds = new DataSet();
        mycommand.Fill(ds, "[Sheet1$]");
        oleCon.Close();
        int count = ds.Tables["[Sheet1$]"].Rows.Count;

        con = new SqlConnection(sqlstr);
        con.Open();
        try
        {
            for (int i = 0; i < count; i++)
            {
                string tRealName, tSex, tInClass, tQuestion, tAnswer;
                tRealName = ds.Tables["[Sheet1$]"].Rows[i]["学号"].ToString().Trim();
                tSex = ds.Tables["[Sheet1$]"].Rows[i]["姓名"].ToString().Trim();
                tInClass = ds.Tables["[Sheet1$]"].Rows[i]["联系方式"].ToString().Trim();
                tQuestion = ds.Tables["[Sheet1$]"].Rows[i]["V网短号"].ToString().Trim();
                tAnswer = ds.Tables["[Sheet1$]"].Rows[i]["银行卡号"].ToString().Trim();
                string excelsql = "insert into test(num, name, tel,Vnet,bankcard) values ('" + tRealName + "','" + tSex + "','" + tInClass + "','" + tQuestion + "','" + tAnswer + "')";
                com = new SqlCommand(excelsql, con);
                try
                {
                    com.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    Response.Write("<script language=javascript>window.alert('数据导入失败！');</script>");
                    break;
                }
            }
            Response.Write("<script language=javascript>window.alert('数据导入成功！');</script>");
            con.Close();
        }
        catch (Exception)
        {
            Response.Write("<script language=javascript>window.alert('数据导入失败！');</script>");
        }
    }  
}