asp.net EXCEL数据导入到数据库

最新推荐文章于 2025-05-22 15:43:40 发布

vip__888

最新推荐文章于 2025-05-22 15:43:40 发布

阅读量8.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： asp.net 文章标签： excel asp.net dataset string button server

本文链接：https://blog.youkuaiyun.com/vip__888/article/details/4913288

asp.net 专栏收录该内容

37 篇文章

订阅专栏

本文介绍了一个使用C#实现的从Excel文件导入数据到SQL Server数据库的方法。该方法通过OLE DB连接Excel，并利用SQL命令将数据插入指定数据库表中，同时加入了文件格式检查及事务处理来确保数据的一致性和安全性。

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

在项目中经常有客户要求用EXCEL导入到数据库，提高效率。

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

下面是源码；

IntoExcel.aspx：

<mce:script type="text/javascript" src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.youkuaiyun.com/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js"></mce:script>><%@ Page Language="C#" AutoEventWireup="true" CodeBehind="IntoExcel.aspx.cs" Inherits="DEMO.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 runat="server"> <title>无标题页</title> <mce:script language="javascript" type="text/javascript"></mce: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; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; namespace DEMO { public partial class 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=.;Initial Catalog=TestExcel;Integrated Security=True"); conn.Open(); SqlTransaction str = conn.BeginTransaction();//利用事务处理防止中断 int k = 0; if (ds.Tables[0].Rows.Count < 1) { Response.Write("<mce:script type="text/javascript"></mce: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); } } } }

数据库中的字段：

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

EXCEL数据