首先：我的Excel表格中是这个样子的
DWBMDM DWBMQC
1050100140001 采油四厂/厂机关
10501001400010002 采油四厂/厂机关/机关
采油四厂/厂机关/机关 我的这里面需要按照/把里面的内容分出来，然后再插入到库中。
下面开始上代码：
前台HTML
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="AnyOffice.general.DRBM.index" %>
<!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>
</head>
<body>
<form id="form1" runat="server">
<table class="small" cellSpacing="0" cellPadding="3" width="100%" border="0">
<tr>
<td class="Big"><IMG src="/images/menu/address.gif" align="absMiddle"><span class="big3"> <asp:Literal id="litNavName" runat="server" Text="设备导入"></asp:Literal></span>
</td>
</tr>
</table>
<br>
<TABLE class="small" cellSpacing="1" cellPadding="2" width="50%" align="center" bgColor="#000000"
border="0">
<TR>
<TD class="TableData" noWrap>EXCEL文件：</TD>
<TD class="TableData" noWrap>
<asp:FileUpload ID="FileUpload1" runat="server"
style="margin-left: 0px" /><SPAN style="COLOR: #ff0000">
<asp:Literal ID="litMessage" runat="server"></asp:Literal>
</SPAN></TD>
</TR>
<tr>
<TD class="TableControl" noWrap align="center" colSpan="3">
<asp:Button id="btnDR" runat="server" CssClass="BigButton" Text="导入"
onclick="btnDR_Click"></asp:Button>
<asp:Button id="btnFH" runat="server" CssClass="BigButton" Text="返回"
onclick="btnFH_Click"></asp:Button>
</TD>
</tr>
</TABLE>
</form>
</body>
</html>
后台：
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
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.Xml.Linq;
using Microsoft.ApplicationBlocks.Data;
using AnyOffice.Components;
using System.Data.OleDb;
namespace AnyOffice.general.DRBM
{
public partial class index : System.Web.UI.Page
{
private OracleConnection conn;
protected void Page_Load(object sender, EventArgs e)
{
conn = new OracleConnection(ConfigManager.ConnectionString);
}
#region======获得文件导入的行，并存放在ds中======
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";//HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);//[Sheet1$]这个不明白就百度吧。
odda.Fill(ds, table);
return ds;
}
#endregion
protected void btnDR_Click(object sender, EventArgs e)
{
bool b = true;// 标识 判断导入的数据是否有错误
#region ======文件导入======
ArrayList list = new ArrayList();
string[] str1 = new string[19];
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
//SqlConnection cn = new SqlConnection();
//cn.Open();
conn.Open();
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~\\general\\SBGL\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = null;
try
{
ds = ExecleDs(savePath, filename); //调用自定义方法
}
catch (Exception e10)
{
this.litMessage.Text = "上传模板有错误，请仔细检查或从新下载！";
return;
}
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
//if (File.Exists(savePath))
//{
// File.Delete(savePath);
//}
if (rowsnum == 1)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
int CF = 0;
for (int i = 1; i < dr.Length; i++)
{
string[] str = new string[2];
try
{
string dwbm = dr[i][0].ToString().Trim().Replace("'", "");//单位编号
string dwmc = dr[i][1].ToString().Trim().Replace("'", "");//单位名称
str[0] = dwbm;
str[1] = dwmc;
list.Add(str);//把取出来的单位编号和单位名称放到list中
}
catch (Exception ei)
{
this.litMessage.Text = "上传模板有错误，请仔细检查或从新下载！";
b = false;
return;
}
}
if (b)
{
string dwbm = null;//单位编码
string dwmc = null;//单位名称
for (int i = 0; i < list.Count; i++)
{
str1 = (string[])list[i];//取出来之前存放在LIST中的单位编码和单位名称-----------------
dwbm = str1[0];//单位编码
dwmc = str1[1];//单位名称
string[] dwmcs = dwmc.Split('/');//定义这个数组dwmcs，把dwmc这一列按照‘/’分开-------------------------------------
string SUPORG ="";
string ORGNAME = "";
string ORGTYPE = "";
string FULLORGNAME="";
#region ===========获取用户所需要的字段，并插入到数据库中=====
if (dwmcs.Length == 2)
{
SUPORG = dwmcs[dwmcs.Length - 2];
ORGTYPE = "大队级";
FULLORGNAME="采油四厂"+"→"+dwmcs[0]+"→"+dwmcs[1];
}
if (dwmcs.Length == 3)
{
SUPORG = dwmcs[dwmcs.Length - 3] + "/" + dwmcs[dwmcs.Length - 2];
ORGTYPE = "小队级";
FULLORGNAME = "采油四厂" + "→" + dwmcs[0] + "→" + dwmcs[1] + "→" + dwmcs[2];
}
string insertstr = "insert into SYS_ORG(SUPORG,ORGNAME,ORGTYPE,FULLORGNAME,ORGCODE,ORDERID,ISON) values('" + SUPORG + "','" + dwmc + "','" + ORGTYPE + "','" + FULLORGNAME + "','" + dwbm + "','1','0')";
#endregion
OracleCommand cmd = new OracleCommand(insertstr,conn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex) //捕捉异常
{
Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
}
}
//Response.Write("<script>alert('导入完成! 有" + CF + "行记录重复未能导入');location='index.aspx'</script>");
}
}
#endregion
}
protected void btnFH_Click(object sender, EventArgs e)
{
}
}
}