namespace Clock.UI.Controllers
{
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using Clock.BLL;
using Clock.Model;
using Newtonsoft.Json;
public class ClockController : Controller
{
UsersClockBLL clockBLL = new UsersClockBLL();
// GET: Clock
public ActionResult Index()
{
return View();
}
public ActionResult Execl()
{
return View();
}
[HttpPost]
public string GetExcel()
{
//获取所选文件
HttpPostedFileBase getFile = Request.Files["Excel"];
if (getFile != null)
{
//获得所选文件名
string fileName = Server.MapPath("~/Content/") + getFile.FileName;
if (!System.IO.File.Exists(fileName))
getFile.SaveAs(fileName);
//把Excel当做数据源连接
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;";
//打开Excel
OleDbConnection conn = new OleDbConnection(connStr);
conn.Open();
//查询Excel
string sql = "select * from [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(sql, conn);
//初始化适配器
OleDbDataAdapter adapter = new OleDbDataAdapter();
//获取查出来的Excel表
adapter.SelectCommand = cmd;
//初始化dataset并通过适配器赋值
DataSet ds = new DataSet();
adapter.Fill(ds);
//获得ds的第一个表并添加到dataGridView1显示
DataTable dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlStr = string.Format("insert into UsersClock(Department, Name, Num, ClockDate, position) values('{0}','{1}','{2}','{3}','{4}')", dt.Rows[i]["Department"], dt.Rows[i]["Name"], dt.Rows[i]["Num"], dt.Rows[i]["ClockDate"], dt.Rows[i]["position"]);
DBhelper.AddOrDeleteOrUpdate(sqlStr, CommandType.Text);
}
conn.Close();
return "导入成功！";
}
else
{
return "导入失败！";
}
}
/// <summary>
/// 显示打卡记录
/// </summary>
/// <returns></returns>
public ActionResult ShowUsersClock()
{
return View();
}
/// <summary>
/// 获得所有打卡记录
/// </summary>
/// <returns></returns>
[HttpGet]
public JsonResult GetUsersClocks()
{
List<UsersClock> clocksList = clockBLL.GetUsersClocks();
return Json(clocksList,JsonRequestBehavior.AllowGet);
}
}
}
@{
Layout = null;
}
<!DOCTYPE html>
<script src="~/Content/jquery-3.1.1.js"></script>
<script>
$(function () {
$.ajax({
url: "/Clock/GetUsersClocks",
type: "get",
success: function (data) {
$(data).each(function (index,data) {
$("#getData").append("<tr><td>" + data.ID + "</td><td>" + data.Department + "</td><td>" + data.Name + "</td><td>" + data.Num + "</td><td>" + data.ClockDate + "</td><td>" + data.position + "</td></tr>");
})
}
})
})
</script>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>ShowUsersClock</title>
</head>
<body>
<table id="getData">
<tr>
<td>ID</td>
<td>所属部门</td>
<td>姓名</td>
<td>打卡号</td>
<td>打卡日期</td>
<td>职位</td>
</tr>
</table>
<div>
</div>
</body>
</html>
@{
Layout = null;
}
<!DOCTYPE html>
<script src="~/Content/jquery-3.1.1.js"></script>
<script>
$(function () {
$("#btnToLead").click(function () {
$.ajax({
url: "/Clock/GetExcel",
contentType: false,
processData: false,
cache: false,
data: new FormData($("#formData")[0]),
type: 'POST',
success: function (data) {
alert(data);
}
})
})
})
</script>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Execl</title>
</head>
<body>
<div>
<form id="formData" method="post" enctype="multipart/form-data">
<input id="File1" type="file" accept=".xls,.xlsx" name="Excel" />
<input id="btnToLead" type="button" value="导入" />
</form>
</div>
</body>
</html>