用MVC控制器视图+from做导入导出
view代码(from表单来进行提交):
<div>
///这里需要主要控制器名字和方法名字
<input type="button" value="添加客户信息" onclick="Add()" />
<form action="/Emp/Excel" method="post" enctype="multipart/form-data">
选择文件:<input id="exceFile" name="excelFile" type="file" />
<input type="submit" value="导入客户信息..." id="Submit"/>
</form>
<form action="/Emp/DownLoad" method="post" enctype="multipart/form-data">
<input type="submit" value="导出客户信息..." />
</form>
</div>
<div>
<form action="/Emp/Index" method="get">
<table class="table">
<tr>
<td>客户编号</td>
<td>客户名称</td>
<td>电话</td>
<td>地区</td>
<td>职务</td>
<td>费用</td>
<td>公司名称</td>
<td>操作</td>
</tr>
@foreach (var item in ViewBag.a)
{
<tr>
<td>@item.EmpId</td>
<td>@item.Ename</td>
<td>@item.Ephone</td>
<td>@item.Eaddress</td>
<td>@item.Ezhiwu</td>
<td>@item.Emoney</td>
<td>@item.Egongsi</td>
<td><a href="#" onclick="dels(@item.EmpId)">删除</a></td>
<td><a href="#" onclick="update(@item.EmpId)">修改</a></td>
</tr>
}
</table>
</form>
///需要应用DBhepp
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog= rikao;Integrated Security=True");
// <summary>
/// 导入
/// </summary>
/// <returns></returns>
public ActionResult Excel()
{
for (int i = 0; i < Request.Files.Count; i++)
{
var exceFile = Request.Files[i];
if (exceFile != null && !string.IsNullOrEmpty(exceFile.FileName))
{
IWorkbook wk = null;
var subName = exceFile.FileName.Substring(exceFile.FileName.LastIndexOf('.'), exceFile.FileName.Length - exceFile.FileName.LastIndexOf('.'));
if (subName.Equals(".xls"))
{
wk = new HSSFWorkbook(exceFile.InputStream);
}
else if (subName.Equals(".xlsx"))
{
wk = new XSSFWorkbook(exceFile.InputStream);
}
else
{
//这里可以返回(主要作用是表格有两种格式如果都不是的话就弹出不匹配并且跳入主页面)
// return Content("<script>alert('格式不匹配');location.href='/Emp/Index'</script>");
return View("Index");
}
ISheet sheet = wk.GetSheetAt(0);
IRow row = sheet.GetRow(1);
for (int k = 0; k <= sheet.LastRowNum; k++)
{
row = sheet.GetRow(k);
//这里是添加到数据库的//这里括号内是字段
string strSql = "insert into daoru(name,pwd) values(";
//如果是全球唯一标识符主键的话就取消注释下面这个添加
//strSql += "id"+Guid.NewGuid();
if (row != null)
{
for (int j = 0; j < row.LastCellNum; j++)
{
if (j == 0)
{
continue;
}
string value = row.GetCell(j).ToString();
strSql += "'" + value + "',";
}
conn.Open();
strSql = strSql.Substring(0, strSql.Length - 1) + ")";
SqlCommand command = new SqlCommand(strSql,conn);
int a = command.ExecuteNonQuery();
conn.Close();
}
}
}
}
return View("Index");
}