本文實例講述了asp.net中EXCEL數據導入到數據庫的方法。分享給大家供大家參考。具體分析如下：
excel是辦公中非常常用的一個辦公表格了，但我們在開發中通常會需要直接把excel數據快速導入到數據庫中了，這里整理了一個asp.net中EXCEL數據導入到數據庫的例子供各位參考學習。
注意：EXCEL中的第一行不能導入。
下面是源碼：IntoExcel.aspx：
復制代碼代碼如下:
<%@ Page 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>
<!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 <strong><a href="//www.jfrwli.cn" /> 這個讀入數據庫的方式不是最佳的，應該用office組件
select * from [Sheet1$] //引用EXCLE文件中sheet1工作表的內容
OleDB控件用的是OleDb的驅動程序，可以訪問各種數據庫
數據庫中的字段：
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 <strong><a href="//www.jfrwli.cn" /> 這個讀入數據庫的方式不是最佳的，應該用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),
)
(
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更快。希望本文所述對大家的asp.net程序設計有所幫助。
