Excel数据导入到数据库中_aspx实现excel数据导入到数据库功能-优快云博客

本文链接：https://blog.youkuaiyun.com/yuwei_shiny/article/details/26878343

本文介绍了一个使用ASP.NET实现的Excel文件上传与数据导入系统的具体实现方法。该系统首先验证文件的有效性，然后将Excel文件保存至指定服务器路径，并读取其内容至数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

.aspx:

<%@ Page Language="C#" AutoEventWireup="true" Codebehind="Default.aspx.cs" Inherits="导入数据._Default" %>

<!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 runat="server">
    <title>无标题页</title>

    <script type="text/javascript">
        function check(){
            if( document.getElementById("fuUploadingFile").value == ""){
                alert("文件不能为空!");
                return false;
            }
            return true;
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:FileUpload ID="fuUploadingFile" runat="server" /><asp:Button ID="btnSubmit"
                runat="server" Text="上传" OnClientClick="return check()" OnClick="btnSubmit_Click" />
        </div>
    </form>
</body>
</html>

.CS

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace 导入数据
{
    public partial class _Default : System.Web.UI.Page
    {
        SqlConnection con = null;
        SqlCommand cmd = null;
        string constr = @"Data Source=.;Initial Catalog=MyDB;Integrated Security=True";

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 上传文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            if (fuUploadingFile.HasFile)
            {
                //有文件
                string fileName = fuUploadingFile.FileName;
                string serverPath = Server.MapPath("./UpExcelFile/");
                //检查文件路径
                CheckFileMapPath(fileName, serverPath);
                //上传至服务器
                fuUploadingFile.SaveAs(serverPath + fileName);
                //获取Excel内容
                GetExcel(fileName, serverPath);
            }
        }

        /// <summary>
        /// 检查目录
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="serverPath"></param>
        protected void CheckFileMapPath(string fileName, string serverPath)
        {
            if (!Directory.Exists(serverPath))
            {
                //不存在路径
                Directory.CreateDirectory(serverPath);
            }
            if (File.Exists(serverPath + "\\" + fileName))
            {
                //同路径下有同名文件
                File.Delete(serverPath + "\\" + fileName);
            }
        }

        /// <summary>
        /// 读取Excel文件内容
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="serverPath"></param>
        protected void GetExcel(string fileName, string serverPath)
        {
            string olbcn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =  " + serverPath + fileName + ";Extended Properties=Excel 8.0";
            OleDbConnection ocon = new OleDbConnection(olbcn);
            ocon.Open();
            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", ocon);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            ocon.Close();
            SaveSQL(ds);
        }

        /// <summary>
        /// Excel数据导入到数据库中
        /// </summary>
        /// <param name="ds"></param>
        protected void SaveSQL(DataSet ds)
        {
            int sum = 0;
            if (ds.Tables[0].Rows.Count <= 0)
            {
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('没有数据')</script>");
            }
            else
            {
                using (con = new SqlConnection(constr))
                {
                    con.Open();
                    cmd = con.CreateCommand();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        string sqlstr = "insert into T_MingPieces (FDepartment,Name,Photo,HandPhone,Email) 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() + "',";
                        sqlstr += "'" + ds.Tables[0].Rows[i][3].ToString() + "',";
                        sqlstr += "'" + ds.Tables[0].Rows[i][4].ToString() + "')";
                        cmd.CommandText = sqlstr;
                        sum += cmd.ExecuteNonQuery();
                    }
                }
                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('共导入" + sum + "条数据！')</script>");
            }

        }
    }
}