【原创】上传EXCEL并把内容批量写入指定数据库

最新推荐文章于 2024-10-23 11:59:30 发布

原创最新推荐文章于 2024-10-23 11:59:30 发布 · 1.6k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#excel #dataset #string #button #textbox #table

该博客介绍了如何创建一个前端界面，通过按钮触发事件，读取Excel文件内容，并按特定格式批量写入到Access数据库中。用户需要注意Excel表格的布局需与数据库字段顺序和格式匹配，且自动编号字段不应包含在Excel中。

注明：必须按照数据库字段的“顺序”，“字段格式 ”来写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>

</HEAD>

<TABLE id="Table1" style="Z-INDEX: 101; LEFT: 0px; POSITION: absolute; TOP: 0px" height="100%"

cellSpacing="1" cellPadding="1" width="100%" border="0">

<TR>

<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>

</TR>

<TR>

</TR>

<TR>

</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 窗体设计器生成的代码

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

}

具体路径自行左修改就OK了。