asp.net中EXCEL数据导入到数据库的方法

%@ Page  AutoEventWireup="true" CodeFile="IntoExcel.aspx.cs" Inherits="study_IntoExcel" %> 
 
!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> 
script language="javascript" type="text/javascript">!-- 
// !CDATA[ 
function check() { 
var k=//S+/.[xls]/; 
if(!k.test(document.getElementById("fileId").value)) 
{ 
    alert("只能上次xls格式的文件"); 
    return false; 
} 
return true; 
} 
// -->/script> 
/head> 
body> 
    form id="form1" runat="server"> 
    div> 
    p> 
        asp:FileUpload ID="fileId" runat="server" /> 
        asp:Button ID="Button1" runat="server" Text="上传" OnClientClick="return check()" onclick="Button1_Click" />/p> 
    /div> 
    /form> 
/body> 
/html>

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.UI; 
using System.Collections; 
using System.Configuration; 
using System.Data; 
using System.Web.Security; 
using System.Web.UI.HtmlControls; 
using System.Web.UI.WebControls.WebParts; 
using System.IO; 
using System.Data.OleDb; 
using System.Data.SqlClient; 
using System.Web.UI.WebControls; 
 
public partial class study_IntoExcel : System.Web.UI.Page 
{ 
    protected void Page_Load(object sender, EventArgs e) 
    { 
 
    } 
        /// summary> 
        /// 上传文件 
        /// /summary> 
        /// param name="sender">/param> 
        /// param name="e">/param> 
        protected void Button1_Click(object sender, EventArgs e) 
        { 
            string fileName = fileId.FileName; 
            string savePath = Server.MapPath("~/file/"); 
            FileOperatpr(fileName, savePath); 
            fileId.SaveAs(savePath + fileName); 
            DataOperator(fileName, savePath); 
        } 
        /// summary> 
        /// 数据操作 
        /// /summary> 
        /// param name="fileName">/param> 
        /// param name="savePath">/param> 
        private void DataOperator(string fileName, string savePath) 
        { 
            string myString = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =  " + savePath + fileName + ";Extended Properties=Excel 8.0"; 
            OleDbConnection oconn = new OleDbConnection(myString); 
            oconn.Open(); 
            DataSet ds = new DataSet(); 
            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", oconn); 
            oda.Fill(ds); 
            oconn.Close(); 
            DataSetOperator(ds,savePath+fileName); 
        } 
        /// summary> 
        /// 数据集操作 
        /// /summary> 
        /// param name="ds">/param> 
        private void DataSetOperator(DataSet ds,string filePath) 
        { 
            SqlConnection conn = new SqlConnection("Data Source=SONYSVR;Initial Catalog=IAR_Factory_811;User ID=sa;Password=P@ssword"); 
            conn.Open(); 
            SqlTransaction str = conn.BeginTransaction();//利用事务处理 防止中断 
            int k = 0; 
            if (ds.Tables[0].Rows.Count 1) 
            { 
                Response.Write("script>alert('没有数据！')/script>"); 
                return; 
            } 
            try 
            { 
                for (int i = 0; i ds.Tables[0].Rows.Count; i++) 
                { 
                    string strong>a href="https://www.jb51.net" title="sql" target="_blank">sql/a>/strong>Str = "insert into IntoExcel(Tname,Tage,Taddress)values"; 
                    sqlStr +="('"+ ds.Tables[0].Rows[i][0].ToString()+"',"; 
                    sqlStr += ds.Tables[0].Rows[i][1].ToString()+","; 
                    sqlStr +="'" +ds.Tables[0].Rows[i][2].ToString()+"')"; 
                    SqlCommand cmd = new SqlCommand(sqlStr, conn, str); 
                    cmd.Transaction = str; 
                    k += cmd.ExecuteNonQuery(); 
                } 
                str.Commit(); 
            } 
            catch (Exception ex) 
            { 
                Response.Write("发生异常,数据已回滚/n信息/n" + ex.Message); 
                str.Rollback(); 
            } 
            finally 
            { 
                Response.Write("上传成功" + k + "条"); 
                File.Delete(filePath); 
            } 
        } 
        /// summary> 
        /// 文件操作 
        /// /summary> 
        /// param name="fileName">/param> 
        /// param name="savePath">/param> 
        private void FileOperatpr(string fileName, string savePath) 
        { 
            if (!Directory.Exists(savePath)) 
            { 
                Directory.CreateDirectory(savePath); 
            } 
            if (File.Exists(savePath + fileName)) 
            { 
                File.Delete(savePath + fileName); 
            } 
        } 
} 
 
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";Extended Properties='Excel 8.0;HDR=YES
 Provider=Microsoft.Jet.OLEDB.4.0;;//连接驱动
Data Source=" + savePath + "; // 数据库地址
Extended Properties='Excel 8.0; // 连接的是Excel8.0
HDR=YES;// 有两个值:YES/ NO, 这2个值，说了你是否能直接读列名，NO，只可以读下标
IMEX=1;//解决数字与字符混合时,识别不正常的情况.

create table IntoExcel 
( 
    Tid int identity(1,1) primary key, 
    Tname varchar(50), 
    Tage int, 
    Taddress varchar(200), 
     
)