excel导入数据的

.aspx 文件

<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" CssClass="form form-control " Style="width: 180px;" />
<div>
</div>
<asp:Button ID="Button1" CssClass="btn btn-info" runat="server" OnClick="UploadExeclFile" Text="保存" />
<button class="btn btn-default" onclick="BtnReturn()">返回</button>
</div>
</form>

 

 

 

 

 

 

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using HSRFQ.Common;

namespace HSRFQ
{
public partial class sMaterialDetailsImport : System.Web.UI.Page
{
private DBHelper db = new DBHelper();
public string sMaterialTypeGuid="";
public string sMaterialTypeCode = "";
protected void Page_Load(object sender, EventArgs e)
{
//将物料类别的guid传递过来
sMaterialTypeGuid = Request.QueryString["sMaterialTypeGuid"];
sMaterialTypeCode = Request.QueryString["sMaterialTypeCode"];
}

protected void UploadExeclFile(object sender, EventArgs e)
{
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" || IsXls == ".xlsx"))
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
//新建文件夹 ExcelTemp
if (!Directory.Exists(Server.MapPath("~/ExcelTemp/")))
{
Directory.CreateDirectory(Server.MapPath("~/ExcelTemp/"));
}

// string filename = FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
//文件重命名
string FileName = System.Guid.NewGuid().ToString();

string savePath = Server.MapPath("~/ExcelTemp/" + FileName + IsXls);//Server.MapPath 获得虚拟服务器相对路径

FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataSet ds = null;
try
{
ds = ExcelSqlConnection(savePath, FileName); //调用自定义方法
}
catch (Exception ex)
{
Response.Write(savePath);
Response.Write(ex.Message);
Response.End();
}
DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
int rowsnum = ds.Tables[0].Rows.Count;
string sUserNo = "";
HttpCookie cookie = Request.Cookies["sUser"];
if (cookie == null)
{
Response.Write("<script>alert('系统异常,请登录重新操作!');</script>");
}
else
{
sUserNo = cookie["sUserNo"];
}

if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
else
{
string sBatchStr = "";
for (int i = 0; i < dr.Length; i++)
{
string sMaterialType = dr[i]["物料类别"].ToString();
string smaterialNo = dr[i]["物料编号"].ToString();
if (smaterialNo == "")
{
continue;
}
string smaterialName = dr[i]["物料名称"].ToString();
string sMaterialStatus = dr[i]["物料状态"].ToString();
string sMaterialShuXing = dr[i]["物料属性"].ToString();
string sSpecification = dr[i]["物料规格"].ToString();
string sUnit = dr[i]["单位"].ToString();
string sMaterialTypeExCode = dr[i]["物料类别U9代码"].ToString();

//物料信息拼接的字符串
sBatchStr += sMaterialType.Trim()+"@@"+
smaterialNo.Trim() + "@@" +
smaterialName.Trim() + "@@" +
sMaterialStatus.Trim() + "@@" +
sMaterialShuXing.Trim() + "@@" +
sSpecification + "@@" +
sUnit.Trim() + "@@" +
sMaterialTypeExCode.Trim() + "$$";
}
string ssql = string.Format("spsMaterialDetailsExcelImport '{0}','{1}','{2}'", sBatchStr, sUserNo,sMaterialTypeGuid);
DataTable dt = null;
try
{
dt = db.ExecuteDataTable(ssql, null);
}
catch (Exception ex)
{
Response.Write(ssql);
Response.End();
}
string iStatus = dt.Rows[0]["iStatus"].ToString();
string sMessage = dt.Rows[0]["sMessage"].ToString();
if (iStatus == "1")
{
Response.Write("<script>alert('Excle表导入成功!');location.href='/MaterialInfoShow/MaterialDetail?uguid="+sMaterialTypeGuid+ "&sMaterialTypeCode="+sMaterialTypeCode+"';</script>");
}
else
{
Response.Write("<script>alert('" + sMessage + "');location.href='/MaterialInfoShow/MaterialDetail?uguid=" + sMaterialTypeGuid + "&sMaterialTypeCode=" + sMaterialTypeCode + "';</script>");
}
}
}

#region 连接Excel 读取Excel数据 并返回DataSet数据集合

/// <summary>
/// 连接Excel 读取Excel数据 并返回DataSet数据集合
/// </summary>
/// <param name="filepath">Excel服务器路径</param>
/// <param name="tableName">Excel表名称</param>
/// <returns></returns>
public System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
{
string sExt = System.IO.Path.GetExtension(filepath);
string strCon = "";
if (sExt == ".xlsx")//Excel2007
{
strCon =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + filepath + ";" +
"Extended Properties='Excel 12.0;HDR=YES'";
}
else if (sExt == ".xls") //Excel2003
{
//strCon =
// "Provider=Microsoft.Jet.OLEDB.4.0;" +
// "Data Source=" + filepath + ";" +
// "Extended Properties='Excel 8.0;;HDR=YES;IMEX=1'";

strCon =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + filepath + ";" +
"Extended Properties='Excel 12.0;HDR=YES'";
}
else
{
throw new Exception("未知的文件类型");
}
// strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection ExcelConn = new OleDbConnection(strCon);
try
{
//从execl中获取所需要的字段信息 插入到数据库中的
string strCom = string.Format("SELECT * FROM [Sheet1$]");
ExcelConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "[" + tableName + "$]");
ExcelConn.Close();
return ds;
}
catch (Exception ex)
{
Response.Write(strCon);
Response.Write("<br/>");
Response.Write(ex.Message);
Response.End();
ExcelConn.Close();
return null;
}
}

#endregion 连接Excel 读取Excel数据 并返回DataSet数据集合
}
}