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

最新推荐文章于 2022-01-17 23:04:00 发布

在奋斗的大道

最新推荐文章于 2022-01-17 23:04:00 发布

阅读量902

点赞数

分类专栏： python 爬虫

python 爬虫专栏收录该内容

2 篇文章 1 订阅

订阅专栏

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

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

下面是源码；

IntoExcel.aspx：

[xhtml] view plain copy print ?

<mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.csdn.net/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"><!--
// <!CDATA[
function check() {
var k=//S+/.[xls]/;
if(!k.test(document.getElementById("fileId").value))
{
alert("只能上次xls格式的文件");
return false;
}
return true;
}
// --></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>

<mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js" mce_src="http://hi.images.csdn.net/js/blog/tiny_mce/themes/advanced/langs/zh.js"></mce:script><mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/plugins/syntaxhl/langs/zh.js" mce_src="http://hi.images.csdn.net/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()" οnclick="Button1_Click" /></p>
    </div>
    </form>
</body>
</html>

IntoExcel.aspx.cs

[c-sharp] view plain copy print ?

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"><!--
alert('没有数据！')
// --></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);
}
}
}
}

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

数据库中的字段：

[javascript] view plain copy print ?

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

EXCEL数据

在奋斗的大道

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
asp.net EXCEL数据导入到数据库

在项目中经常有客户要求用EXCEL导入到数据库，提高效率。注意：EXCEL中的第一行不能导入。下面是源码；IntoExcel.aspx：[xhtml] view plaincopyprint?mce:script type="text/javascript" src="http://hi.images.csdn.net/js/blog/tiny_mce/
复制链接

扫一扫