asp.net中EXCEL数据导入到数据库程序代码

作者：袖梨 2022-06-25

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

下面是源码：IntoExcel.aspx：

代码如下

复制代码

<%@ Page AutoEventWireup="true" CodeFile="IntoExcel.aspx.cs" Inherits="study_IntoExcel" %>

无标题页

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)
    {

    }


        ///

/// 上传文件
///

        ///
        ///
        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);
        }
        ///

/// 数据操作
///

        ///
        ///
        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);
        }
        ///

/// 数据集操作
///

        ///
        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("");
                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);
            }
        }
        ///

/// 文件操作
///

        ///
        ///
        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), )