【原创】上传EXCEL并把内容批量写入指定数据库
来源:互联网 发布:mac管理员账户不见了 编辑:程序博客网 时间:2024/06/02 02:31
注明:必须按照数据库字段的“顺序”,“字段格式 ”来写EXCEL;自动编号不要写入EXCEL。
我这里用的是ACCESS
前台代码:
<%...@ Page language="c#" Codebehind="Excel.aspx.cs" AutoEventWireup="false" Inherits="TestAll.Excel" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>Excel</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<TABLE id="Table1" style="Z-INDEX: 101; LEFT: 0px; POSITION: absolute; TOP: 0px" height="100%"
cellSpacing="1" cellPadding="1" width="100%" border="0">
<TR>
<TD style="FONT-SIZE: 12px; COLOR: appworkspace" align="center" colSpan="3"><FONT face="宋体"> </FONT>
<TABLE id="Table3" style="FONT-SIZE: 12px" cellSpacing="1" cellPadding="1" width="300"
border="0">
<TR>
<TD style="FONT-WEIGHT: bolder; FONT-SIZE: 12pt" align="center" colSpan="2">EXCEL批量上传</TD>
</TR>
<TR>
<TD align="right">表格名称:</TD>
<TD><asp:textbox id="txtTableName" runat="server" Font-Size="12px"></asp:textbox></TD>
</TR>
<TR>
<TD align="right">位置:</TD>
<TD><INPUT id="file" style="FONT-SIZE: 12px; HEIGHT: 22px" type="file" runat="server"></TD>
</TR>
<TR>
<TD></TD>
<TD align="center"><asp:button id="Button1" runat="server" Text="上 传" BorderStyle="None"></asp:button></TD>
</TR>
<TR>
<TD style="COLOR: gray" align="right" colSpan="2">>>表名称为EXCEL表格中左下角的字段名</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</form>
</body>
</HTML>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>Excel</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<TABLE id="Table1" style="Z-INDEX: 101; LEFT: 0px; POSITION: absolute; TOP: 0px" height="100%"
cellSpacing="1" cellPadding="1" width="100%" border="0">
<TR>
<TD style="FONT-SIZE: 12px; COLOR: appworkspace" align="center" colSpan="3"><FONT face="宋体"> </FONT>
<TABLE id="Table3" style="FONT-SIZE: 12px" cellSpacing="1" cellPadding="1" width="300"
border="0">
<TR>
<TD style="FONT-WEIGHT: bolder; FONT-SIZE: 12pt" align="center" colSpan="2">EXCEL批量上传</TD>
</TR>
<TR>
<TD align="right">表格名称:</TD>
<TD><asp:textbox id="txtTableName" runat="server" Font-Size="12px"></asp:textbox></TD>
</TR>
<TR>
<TD align="right">位置:</TD>
<TD><INPUT id="file" style="FONT-SIZE: 12px; HEIGHT: 22px" type="file" runat="server"></TD>
</TR>
<TR>
<TD></TD>
<TD align="center"><asp:button id="Button1" runat="server" Text="上 传" BorderStyle="None"></asp:button></TD>
</TR>
<TR>
<TD style="COLOR: gray" align="right" colSpan="2">>>表名称为EXCEL表格中左下角的字段名</TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</form>
</body>
</HTML>
后台代码:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Security;
using System.Configuration;
namespace TestAll
...{
/**//// <summary>
/// Excel 的摘要说明。
/// </summary>
public class Excel : System.Web.UI.Page
...{
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.HtmlControls.HtmlInputFile File1;
protected System.Web.UI.WebControls.TextBox txt_tbname;
protected System.Web.UI.HtmlControls.HtmlInputFile file;
protected System.Web.UI.WebControls.TextBox txtTableName;
public string ST_ConnectionString;
private void Page_Load(object sender, System.EventArgs e)
...{
}
Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
...{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/**//// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
...{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
...{
if (!file.Value.ToLower().EndsWith("xls"))
...{
Response.Write("<script>alert('只能导入Excel类型文件');</script>");
return;
}
if (txtTableName.Text.Trim() == "")
...{
Response.Write("<script>alert('请填写Excel表单名');</script>");
return;
}
//获取上传文件的路径
string myFile=file.PostedFile.FileName;
string fileName=myFile.Substring(myFile.LastIndexOf("/")+1);
file.PostedFile.SaveAs(Server.MapPath("../xls")+"/"+fileName);
string tbname = txtTableName.Text.Trim(); //表名
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+Server.MapPath("../xls")+"/"+fileName+";"+"Extended Properties='Excel 8.0;hdr=yes;imex=1';";
DataSet myDataSet = new DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+tbname+"$]", strConn);
try
...{
myCommand.Fill(myDataSet);
}
catch(Exception ex)
...{
string aa = ex.Message;
Response.Write("<script>alert('表单名不正确');</script>");
return;
}
// //复制
ST_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(ConfigurationSettings.AppSettings["ConnectionString1"]);
OleDbConnection con=new OleDbConnection(ST_ConnectionString);
OleDbDataAdapter da=new OleDbDataAdapter("select * from SW_Product order by id desc",con);
DataSet ds1=new DataSet();
da.Fill(ds1);//原有
for(int i=0;i<myDataSet.Tables[0].Rows.Count;i++)
...{
DataRow dr=ds1.Tables[0].NewRow();
for (int j=0; j<ds1.Tables[0].Columns.Count;j++)
...{
dr[j]=myDataSet.Tables[0].Rows[i][j];
}
ds1.Tables[0].Rows.Add(dr);
}
try
...{
OleDbCommandBuilder cb=new OleDbCommandBuilder(da);
cb.QuotePrefix="[";
cb.QuoteSuffix="]";
da.Update(ds1);
Response.Write("<script>alert('添加成功!');</script>");
}
catch(Exception ex1)
...{
Response.Write("<script>alert('"+ex1.Message.ToString()+"');</script>");
}
}
}
}
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Security;
using System.Configuration;
namespace TestAll
...{
/**//// <summary>
/// Excel 的摘要说明。
/// </summary>
public class Excel : System.Web.UI.Page
...{
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.HtmlControls.HtmlInputFile File1;
protected System.Web.UI.WebControls.TextBox txt_tbname;
protected System.Web.UI.HtmlControls.HtmlInputFile file;
protected System.Web.UI.WebControls.TextBox txtTableName;
public string ST_ConnectionString;
private void Page_Load(object sender, System.EventArgs e)
...{
}
Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
...{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/**//// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
...{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
...{
if (!file.Value.ToLower().EndsWith("xls"))
...{
Response.Write("<script>alert('只能导入Excel类型文件');</script>");
return;
}
if (txtTableName.Text.Trim() == "")
...{
Response.Write("<script>alert('请填写Excel表单名');</script>");
return;
}
//获取上传文件的路径
string myFile=file.PostedFile.FileName;
string fileName=myFile.Substring(myFile.LastIndexOf("/")+1);
file.PostedFile.SaveAs(Server.MapPath("../xls")+"/"+fileName);
string tbname = txtTableName.Text.Trim(); //表名
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+Server.MapPath("../xls")+"/"+fileName+";"+"Extended Properties='Excel 8.0;hdr=yes;imex=1';";
DataSet myDataSet = new DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+tbname+"$]", strConn);
try
...{
myCommand.Fill(myDataSet);
}
catch(Exception ex)
...{
string aa = ex.Message;
Response.Write("<script>alert('表单名不正确');</script>");
return;
}
// //复制
ST_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(ConfigurationSettings.AppSettings["ConnectionString1"]);
OleDbConnection con=new OleDbConnection(ST_ConnectionString);
OleDbDataAdapter da=new OleDbDataAdapter("select * from SW_Product order by id desc",con);
DataSet ds1=new DataSet();
da.Fill(ds1);//原有
for(int i=0;i<myDataSet.Tables[0].Rows.Count;i++)
...{
DataRow dr=ds1.Tables[0].NewRow();
for (int j=0; j<ds1.Tables[0].Columns.Count;j++)
...{
dr[j]=myDataSet.Tables[0].Rows[i][j];
}
ds1.Tables[0].Rows.Add(dr);
}
try
...{
OleDbCommandBuilder cb=new OleDbCommandBuilder(da);
cb.QuotePrefix="[";
cb.QuoteSuffix="]";
da.Update(ds1);
Response.Write("<script>alert('添加成功!');</script>");
}
catch(Exception ex1)
...{
Response.Write("<script>alert('"+ex1.Message.ToString()+"');</script>");
}
}
}
}
- 【原创】上传EXCEL并把内容批量写入指定数据库
- php批量上传图片并把图片名放入数据库
- yii2从excel文件中读取表格内容,并批量写入数据库
- 将Excel文件内容写入到数据库
- springMVC 读取excel内容并写入数据库
- 文件信息批量导入数据库(Struts2实现文件上传,POI实现Excel文件读取并写入数据库) .
- 文件信息批量导入数据库(Struts2实现文件上传,POI实现Excel文件读取并写入数据库)
- php循环目录下的文件并把文件里面的内容循环写入数据库
- 如何将图片上传到远端文件夹并把路径写入数据库
- android excel写入内容
- 将Excel文件内容写入到数据库-温柔一刀
- 将Excel文件内容写入到数据库-温柔一刀
- java如何将Excel表格中内容写入oracle数据库
- PHP读取Excel文件的内容并写入Mysql数据库
- 批量写入数据库
- 上传excel到服务器端,并写入到mssql数据库
- springmvc之Excel文件上传并写入数据库
- 指定内容编码写入文件
- 把字符转换成un编码。。。
- ASP.NET 2.0运行时简要分析
- sql中使用""不等于号的误区
- Web Application Security——XSS
- 看看JS中操作表格
- 【原创】上传EXCEL并把内容批量写入指定数据库
- struts iterator 标签 之 indexId
- .NET学习笔记(三) ------系统类型和通用操作 (下)
- 从.Net类库代码来看Asp.net运行时
- 盘点07年五大就业领域 互联网领域成为就业大户
- 使表格中的文字自动换行
- connection string大全
- 控制图片飘动 script 代码
- VFP经典64问