从excel导入数据到sql

来源：互联网发布：ctp 行情数据编辑：程序博客网时间：2024/05/20 23:30

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

<%@ 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>");        }    }  }