ASP.NETMVC导⼊Excel⽂件（完整版）
View视图部分：
<form method="post" enctype="multipart/form-data" action="/Position/ImportExcel" class="form-group">
<input name="file" type="file" id="file" />
<button id="btn_import" type="submit" class="btn btn-info">
<span class="glyphicon glyphicon-pencil"></span>导⼊
</button>
</form>
控制器部分：
public ActionResult ImportExcel()
{
/
/获取上传的Excel⽂件
HttpPostedFileBase File = Request.Files["file"];
string message = "";
if (File.ContentLength > 0)
{
//GetExtension：返回指定路径的⽂件的扩展名
var Isxls = System.IO.Path.GetExtension(File.FileName).ToString().ToLower();
if (Isxls != ".xls" && Isxls != ".xlsx")
{
message = "<script>alert('请上传Excel⽂件'),window.location.href='/Position/Index'</script>";
}
var FileName = File.FileName;//获取⽂件夹名称
var path = Server.MapPath("~/FileExcel/" + FileName);
File.SaveAs(path);//将⽂件保存到服务器
PositionBLL bll = new PositionBLL();
var list = bll.FileUpLoad(path);
if (list.Count > 0)
{
int num = bll.LoadFile(list);
if (num > 0)
{
message = "<script>alert('数据导⼊成功'),window.location.href='/Position/Index'</script>";
}
}
else
{
message = "<script>alert('导⼊的数据不能为空'),window.location.href='/Position/Index'</script>";
}
}
else
{
message = "<script>alert('请选择上传的⽂件'),window.location.href='/Position/Index'</script>";
}
return Content(message);
}
Model部分：
public class PositionModel
{
string PositionName;
string Qualification;
string Remark;
public string PositionName1 { get => PositionName; set => PositionName = value; }
public string Qualification1 { get => Qualification; set => Qualification = value; }
public string Remark1 { get => Remark; set => Remark = value; }
}
PositionBLL部分：
public class PositionBLL
{
//private const string ConnString2003 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel
8.0';HDR='Yes'";
//链接打开excel的字符串
private const string ConnString2003 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0';"; public List<PositionModel> FileUpLoad(string filePath)
{
DataSet ds = new DataSet();
List<PositionModel> list = new List<PositionModel>();
string strSQL = string.Format(ConnString2003, filePath);
//OleDbConnection：表⽰与数据源的开放链接
OleDbConnection conn = new OleDbConnection(strSQL);
try
{
//判断连接的状态
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
DataTable tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第⼀个Sheet名称
var sheetName = tableName.Rows[0]["TABLE_NAME"].ToString().Trim();
string SQL = "select * from [" + sheetName + "]";
OleDbDataAdapter sa = new OleDbDataAdapter(SQL, conn);
sa.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
PositionModel model = new PositionModel();
model.PositionName1 = dr["职位名称"].ToString();
model.Qualification1 = dr["任职资格"].ToString();
model.Remark1 = dr["职位描述"].ToString();
list.Add(model);
}
}
catch (Exception ex)
{
Console.WriteLine("错误信息：PositionBLL+FileUpLoad⽅法" + ex);
}
return list;
}
//将数据循环遍历到数据库中
PositionDAL dal = new PositionDAL();
public int LoadFile(List<PositionModel> list)
{
var num = 0;
foreach (var item in list)
{
PositionModel model = new PositionModel();
model.PositionName1 = item.PositionName1;
model.Qualification1 = item.Qualification1;
model.Remark1 = item.Remark1;
num = dal.Add(model);
}
return num;position职位
}
PositionDAL部分：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace ExcleImport.Models
{
public class PositionDAL
{
DBhelper dBhelper = new DBhelper();
public int Add(PositionModel model) {
string sql = string.Format("insert into userInfo (position,grade,remark)values('{0}','{1}','{2}')", model.PositionName1, model.Qualification1, model.Remark1);
return dBhelper.ExceDml(sql);
}
}
}
DBhelper部分：
//创建链接数据库的字符串
string dbStr = "Data Source=.;Initial Catalog=sales;Integrated Security=True";
public int ExceDml(string sql)
{
try
{
int res = 0;
//连接数据库
using (SqlConnection conn = new SqlConnection(dbStr))
{
//打开连接数据库
conn.Open();
//执⾏sql
SqlCommand comm = new SqlCommand(sql, conn);
res = comm.ExecuteNonQuery();
}
return res;
}
catch (Exception ex)
{
Console.WriteLine("错误信息：DBhelper+ExceDml⽅法" + ex);
throw ex;
}
}
数据库表：
create table userInfo
(
id int identity(1,1)not null,
position varchar(50)not null,
grade varchar(50)not null,
remark varchar(100)not null
)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。