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

秒速五厘米 2022-09-18 11:43 248阅读 0赞

在项目中经常要用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"></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()" οnclick="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数据

![Image 1][]

[Image 1]: