asp.net实现 EXCEL数据导入到数据库功能

来源：互联网发布：傲剑飞龙升级数据大全编辑：程序博客网时间：2024/05/01 17:04

在项目中经常要用EXCEL导入数据到数据库，提高工作效率。

注意：EXCEL中的第一行不能导入。

下面是源码：

IntoExcel.aspx：

<%@ Page Language="C#" 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>

IntoExcel.aspx.cs

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 sqlStr = "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;//解决数字与字符混合时,识别不正常的情况.
这个读入数据库的方式不是最佳的，应该用office组件

select * from [Sheet1$] //引用EXCLE文件中sheet1工作表的内容

OleDB控件用的是OleDb的驱动程序，可以访问各种数据库

数据库中的字段：

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

SQL控件用的是专用的驱动程序，能高效的访问SQL Server数据库

SQLConnection只能访问SQL Server，而OleDbConnection则可以访问所有数据库。
如果只是访问SQL Server的话，SQL比OleDb更快。

EXCEL数据