asp.net mvc4 easyui datagrid 增删改查分页导出先上传后导入 NPOI批量导入导出EXCEL

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

效果图

数据库代码

create database CardManage
use CardManage
create table CardManage
(
ID int identity(1,1) primary key,
userDep nvarchar(10)not null,
userName nvarchar(5)not null,
userTel varchar(11)not null,
carNum nvarchar(20)not null,
fixedCarport nvarchar(20)null,
)
insert CardManage values('研发部','小明','','洒B13580','博士二二二二')
insert CardManage values('研发部','小仝','','洒B13580','博士二二二二')
insert CardManage values('酱油部','小华','','洒B13580','博士二二二二')truncate table CardManage
select*from CardManage--第二个表不清楚字段和名字我看文档那里有猜的
-- 确定再加上去
create table projectManage
(
ID int identity primary key not null,
projectName nvarchar(20)not null,
manager_1 nvarchar(10) not null,
manager_2 nvarchar(10) not null,
monitor nvarchar(10) not null,
isFire varchar(2) null ,
startTime datetime not null,
endTime datetime not null,
manager_1tel nvarchar(11) not null,
manager_1company nvarchar(20) not null,
manager_2tel nvarchar(11) not null,
manager_2company nvarchar(20) not null,
monitortel nvarchar(11) not null,
monitorcompany nvarchar(20) not null
)
select *from projectManage
insert projectManage values('装机','小明','大明','工程监理','是','1990-9-9','2000-9-9','','','','','','')create proc a
as
truncate table CardManage
go

车辆管理视图代码

工程监控视图代码

@{
Layout = null;
}<!DOCTYPE html><html>
<head>
<meta name="viewport" content="width=device-width" />
<link href="~/Scripts/eastui/themes/icon.css" rel="stylesheet" />
<link href="~/Scripts/eastui/themes/default/easyui.css" rel="stylesheet" />
<script src="~/Scripts/eastui/jquery.min.js"></script>
<script src="~/Scripts/eastui/jquery.easyui.min.js"></script>
<title>Index</title>
<script type="text/javascript">
//$(function () {
// $(':radio[name="sex1"]').eq(0).attr("checked", true)
//})
$(function () {
f();
d();
}) window.setInterval(showalert, 3000);
function showalert()
{
var rows = $('#a').datagrid('getData');
var a = rows.total;
//alert(a)
$.ajax({
type: "POST",
url: '/Project/ss/',
success: function (result) { if (a!= result)
{
//alert(result)
$('#a').datagrid("reload");
}
}
}) } function f() {
$('#a').datagrid({
url: '../Project/sa',//控制器/方法
pagination: true,
pageSize:10, //每页记录数
pageNumber: 1, //默认显示第几页
pageList: [10,15,20], //分页记录数数组
//singleSelect: true,
rownumbers: true,
//toolbar: "#toolbar",
idField: 'ID',
frozenColumns: [[
{ field: 'ck', checkbox: true },
//{ title: '全选', field: 'EmpID', width: 80, sortable: true }
]],
columns:
[[
{ field: 'projectName', title: '项目名称', width:120, align: 'center' },
{ field: 'isFire', title: '是否动火作业', width:80, align: 'center' },
{
field: 'startTime', title: '施工开始时间', width:100, align: 'center', formatter: function ChangeDateFormat(val) {
if (val != null) {
var date = new Date(parseInt(val.replace("/Date(", "").replace(")/", ""), 10));
//月份为0-11，所以+1，月份小于10时补个0
var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1;
var currentDate = date.getDate() < 10 ? "0" + date.getDate() : date.getDate();
return date.getFullYear() + "-" + month + "-" + currentDate;
} return "";
},
},
{
field: 'endTime', title: '施工结束时间', width:100, align: 'center', formatter: function ChangeDateFormat(val) {
if (val != null) {
var date = new Date(parseInt(val.replace("/Date(", "").replace(")/", ""), 10));
//月份为0-11，所以+1，月份小于10时补个0
var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1;
var currentDate = date.getDate() < 10 ? "0" + date.getDate() : date.getDate();
return date.getFullYear() + "-" + month + "-" + currentDate;
} return "";
},
},
{ field: 'manager_1', title: '项目主管（甲方）', width:100, align: 'center' },
{ field: 'manager_2', title: '项目主管（乙方）', width:100, align: 'center' },
{ field: 'monitor', title: '工程监理', width:100, align: 'center' },
//{
// field: 'operation', width: 100, title: '操作', align: 'center', formatter: function (value, row, index) {
// return "<a id='delProjectBtn' onclick='delProject(\"" + value + "\");' class='behindBut'>删除</a> <a id='editProjectBtn' onclick='editProject(\"" + value + "\");' class='behindBut'>编辑</a>";
// }
//}
]], toolbar: [
{
id: 'btnadd',
text: '添加',
iconCls: 'icon-add',
handler: function () { //添加车位信息方法
$('#addProjectWin').window('open');
}
}, {
text: '删除',
id: 'btn_admin_del',
iconCls: 'icon-cut',
handler: function () {
del();
}
}, '-', {
text: '修改',
id: 'btn_admin_edit',
iconCls: 'icon-save',
handler: function ()
{ var rows = $('#a').datagrid('getSelections');
var a = rows.length;
if (a == 0) {
alert("需要要选择一条数据")
}
if (a == 1) {
var row = $('#a').datagrid('getSelected');
$('#editProjectWin').window('open');
var name = row.projectName;
var is = row.isFire; if (is == "是") {
$('#r1').attr('checked', 'true')
}
if (is == '否') {
$('#r2').attr('checked', 'true')
}
var id = row.ID;
var m1 = row.manager_1;
var m1t = row.manager_1tel;
var m1c = row.manager_1company;
var m2 = row.manager_2;
var m2t = row.manager_2tel
var m2c = row.manager_2company;
var mo=row.monitor;
var mot=row.monitortel;
var moc = row.monitorcompany;
var datatime1 = row.startTime;
var datatime2 = row.endTime; var date1 = new Date(parseInt(datatime2.replace("/Date(", "").replace(")/", ""), 10)); //月份为0-11，所以+1，月份小于10时补个0
var month1 = date1.getMonth() + 1 < 10 ? "0" + (date1.getMonth() + 1) : date1.getMonth() + 1;
var currentDate1 = date1.getDate() < 10 ? "0" + date1.getDate() : date1.getDate();
var dt2 = month1 + "/" + currentDate1 + "/" + date1.getFullYear() var date = new Date(parseInt(datatime1.replace("/Date(", "").replace(")/", ""), 10)); //月份为0-11，所以+1，月份小于10时补个0
var month = date.getMonth() + 1 < 10 ? "0" + (date.getMonth() + 1) : date.getMonth() + 1;
var currentDate = date.getDate() < 10 ? "0" + date.getDate() : date.getDate();
var dt1 = month + "/" + currentDate + "/" + date.getFullYear() dt1.toString(); $("#mod_projectName").attr("value", name);// 填充内容
$("#id").attr("value", id) $('#dt1').datebox('setValue', dt1);
$('#dt2').datebox('setValue', dt2);
$("#m1").attr("value", m1)
$("#m1t").attr("value", m1t)
$("#m1c").attr("value", m1c)
$("#m2").attr("value", m2)
$("#m2t").attr("value", m2t)
$("#m2c").attr("value", m2c)
$("#mo").attr("value", mo)
$("#mot").attr("value", mot)
$("#moc").attr("value", moc)
} if(a>1){
alert("只能选择一条数据")
$('#a').datagrid('clearSelections');
}
}
},
{
text: 'excel导出',
id: 'excelout', handler: function () {
window.open('/Project/ExportExcel');
}
},
{
text: 'nopi导出excel',
id: 'nopi',
handler: function () {
window.open('/Project/nopi');
}
} ]
}); var p = $('#a').datagrid('getPager');
$(p).pagination({
beforePageText: '第',//页数文本框前显示的汉字
afterPageText: '页共 {pages} 页',
displayMsg: '当前显示 {from} - {to} 条记录共 {total} 条记录'
});
$("#addProjectWin").window({
title: '添加工程监管',
iconCls: 'icon-add',
width: 'auto',
height: 'auto',
top: 100,
modal: true,
shadow: false,
closed: true,
maximizable: false,
minimizable: false,
resizable: false,
draggable: true
});
$("#editProjectWin").window({
title: '编辑工程监管',
iconCls: 'icon-edit',
width: 'auto',
height: 'auto',
top: 100,
modal: true,
shadow: false,
closed: true,
maximizable: false,
minimizable: false,
resizable: false,
draggable: true
});
}; //利用表单导入EXCEL
function excel() {
$("#upexcel").form("submit", {
url: '/Project/upexcel/',
onSubmit: function () {
return $(this).form("validate"); //提交前验证
},
success: function (data) { $.messager.alert('Excel批量用户导入', data, 'info');
$('#a').datagrid('load', {})
$('#a').datagrid("reload");
}
}
)
}
//利用nopi表单提交后导入excel
function npoiexcel() {
$("#npoiload").form("submit", {
url: '/Project/npoiload/',
onSubmit: function () {
return $(this).form("validate"); //提交前验证
},
success: function (data) { $.messager.alert('Excel批量用户导入', data, 'info');
$('#a').datagrid('load', {})
$('#a').datagrid("reload");
}
}
)
}
//搜索
function so() {
var name = $('#s_name').val();
$('#a').datagrid('load', {
name:name
}); }
//批量删除
function del() {
var row = $('#a').datagrid('getSelected'); var rows = $('#a').datagrid('getSelections');
var ids = [];
for (var i = 0; i < rows.length; i++) {
ids.push(rows[i].ID)
}
var name = [];
for (var i = 0; i < rows.length; i++) {
name.push(rows[i].projectName)
}
names = name.toString();
//alert(ids);
id = ids.toString(); //alert(id);
if (!row) {
$.messager.alert('提示', '请选择要删除的员工');
return;
}
else { $.messager.confirm('提示', '是否删除项目名为“' + names + '”的项目?', function (r) {
if (!r) {
return;
}
$.ajax({
type: "POST",
url: "/Project/del/",
data: { id: id },
success: function (msg) {
if (msg = "1") {
$.messager.alert('提示', '删除成功！', 'info', function () {
$('#a').datagrid("reload");
$('#a').datagrid('clearSelections'); });
}
else {
alert("删除失败");
}
},
error: function () {
$.messager.alert('错误', '删除失败！');
}
});
});
} } //添加
function saveUser() {
$("#fm_add_admin").form("submit", {
url: '/Project/add/', //使用参数
onSubmit: function () {
return $(this).form("validate"); //提交前验证
},
success: function (result) {
if (result == "1") { $.messager.alert("提示", "保存成功")
$('#addProjectWin').window('close'); $('#a').datagrid('load', {}) }
else {
$.messager.alert("提示", "失败");
}
}
});
}
function d() {
$('#dd').datebox({
required: true
});
$('#dd2').datebox({
required: true
});
}
function dsosuo() {
var d1 = $('#dd').datebox('getValue');
var d2 = $('#dd2').datebox('getValue');
//alert(d2);
var startdate = new Date(d1.replace(/-/g, "/"));
var enddate = new Date(d2.replace(/-/g, "/"));
if (d1 > d2) {
alert("开始日期不能大于结束日期");
}
else {
$('#a').datagrid('load', {
d1: d1,
d2: d2, });
} //})
} //修改方法
function editUser() {
var row = $('#a').datagrid('getSelected');
var id = row.EmpID;
$('#fm_modify_admin').form('submit', {
url: '/Project/edit/',
onSubmit: function () {
return $(this).form('validate');
},
success: function (result) {
if (result == "1") {
$('#dlg_edit_admin').dialog('close');
$.messager.alert("提示", "修改成功")
$('#editProjectWin').window('close');
$("#a").datagrid("reload"); }
else {
$.messager.alert("提示", result);
}
}
});
} $.extend($.fn.validatebox.defaults.rules, {
mobile: {//value值为文本框中的值
validator: function (value) {
var reg = /^1[3|4|5|8|9]\d{9}$/;
return reg.test(value);
},
message: '输入手机号码格式不准确.'
},
CHS: {//只能输入汉字
validator: function (value) {
return /^[\u0391-\uFFE5]+$/.test(value);
},
message: '只能输入汉字'
},
phoneAndMobile: {//电话号码或手机号码
validator: function (value) {
return /^(($\d{2,3}$)|(\d{3}\-))?($0\d{2,3}$|0\d{2,3}-)?[1-9]\d{6,7}(\-\d{1,4})?$/i.test(value) || /^(13|15|18)\d{9}$/i.test(value);
},
message: '电话号码或手机号码格式不正确'
},
carNo: { //验证车牌
validator: function (value) {
return /^[\u4E00-\u9FA5][\da-zA-Z]{6}$/.test(value);
},
message: '车牌号码无效（例：粤B12350）'
} }) </script>
</head>
<body>
<div class="search">
<label>项目名称：<input type="text" id="s_name" /></label>
<a href="javascript:void(0)" id="searchBtn" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-search'" onclick="so()">查询</a>
从
<input id="dd" type="text" editable="false" missingMessage="日期必须填写">
到
<input id="dd2" type="text" editable="false" missingMessage="日期必须填写">
<input id="Button1" type="button" value="搜索" onclick="dsosuo()" />
<form id="upexcel" method="post" enctype="multipart/form-data">
<input id="upload" type="file" name="upload" style="width: 250px; background: White" />
<input id="Button3" type="button" value="上传文件后批量导入" onclick="excel()" />
</form>
<form id="npoiload" method="post" enctype="multipart/form-data">
<input id="nopiupload" type="file" name="npoiload" style="width: 250px; background: White" />
<input id="Button4" type="button" value="npoi批量导入" onclick="npoiexcel()" />
</form> </div> <table id="a"></table>
<div id="addProjectWin">
<form id="fm_add_admin" method="post" novalidate>
<table class="detailTbl" style="width: 100%;" border="1"> <tbody>
<tr>
<th>项目名称：</th>
<td>
<input id="add_projectName" name="projectName" class="easyui-validatebox" data-options="required:'true', missingMessage:'项目名不能为空'"/></td>
<th>是否动火作业：</th>
<td>
<label>
<input type="radio" name="isFire" value="是" checked="checked">是</label>
<label>
<input type="radio" name="isFire" value="否" />否</label>
</td>
</tr>
<tr>
<th>施工时间：</th>
<td colspan="3">
<input type="text" class="easyui-datebox" name="startTime" editable="false" data-options="required:'true',missingMessage:'开始时间不能为空'" >
@* <input class="easyui-datetimebox" type="text" name="startTime" data-options="width:154" />*@至
@* <input class="easyui-datetimebox" type="text" name="endTime " data-options="width:154" />*@
<input type="text" class="easyui-datebox" name="endTime" editable="false" data-options="required:'true',missingMessage:'截止时间不能为空'" >
</td>
</tr>
<tr>
<th>相关人员：</th>
<td colspan="3">
<table>
<thead>
<tr>
<th>身份</th>
<th>姓名</th>
<th>电话</th>
<th>公司</th>
</tr>
</thead>
<tbody>
<tr>
<td>项目主管(甲方)</td>
<td>
<input type="text" name="manager_1" class="easyui-validatebox" data-options="validType:'CHS' ,required:'true', missingMessage:'姓名不能为空'"/></td>
<td>
<input type="text" name="manager_1tel" class="easyui-validatebox" data-options="validType:'phoneAndMobile' ,required:'true', missingMessage:'联系电话不能为空'"/></td>
<td>
<input type="text" name="manager_1company" class="easyui-validatebox" data-options="required:'true', missingMessage:'公司不能为空'"/></td>
</tr>
<tr>
<td>项目主管（乙方）</td>
<td>
<input type="text" name="manager_2" class="easyui-validatebox" data-options="validType:'CHS' ,required:'true', missingMessage:'姓名不能为空'"/></td>
<td>
<input type="text" name="manager_2tel" class="easyui-validatebox" data-options="validType:'phoneAndMobile' ,required:'true', missingMessage:'联系电话不能为空'"/></td>
<td>
<input type="text" name="manager_2company" class="easyui-validatebox" data-options="required:'true', missingMessage:'公司不能为空'" /></td>
</tr>
<tr>
<td>工程监理</td>
<td>
<input type="text" name="monitor" class="easyui-validatebox" data-options="validType:'CHS' ,required:'true', missingMessage:'姓名不能为空'"/></td>
<td>
<input type="text" name="monitortel" class="easyui-validatebox" data-options="validType:'phoneAndMobile' ,required:'true', missingMessage:'联系电话不能为空'"/></td>
<td>
<input type="text" name="monitorcompany" class="easyui-validatebox" data-options="required:'true', missingMessage:'公司不能为空'"/></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody> </table>
</form>
<div id="dlg_add_admin_buttons" style="text-align: left">
<a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-ok'" onclick="saveUser()">添加</a>
<a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" onclick="javascript:$('#addProjectWin').window('close');">取消</a>
</div>
</div> <div id="editProjectWin">
<form id="fm_modify_admin" method="post" novalidate>
<table class="detailTbl" style="width: 100%;" border="1"> <tbody>
<tr> <th>
<input id="id" name="ID" style="display:none" />
项目名称：</th>
<td>
<input id="mod_projectName" name="projectName" class="easyui-validatebox" type="text" data-options="required:'true'"/></td>
<th>是否动火作业：</th>
<td>
<label>
<input type="radio" id="r1" name="isFire" value="是"/>是</label>
<label>
<input type="radio" id="r2" name="isFire" value="否" />否</label>
</td>
</tr>
<tr>
<th>施工时间：</th>
<td colspan="3">
<input id="dt1" type="text" editable="false" class="easyui-datebox" name="startTime" data-options="required:'true',missingMessage:'开始时间不能为空'">
@* <input class="easyui-datetimebox" type="text" name="startTime" data-options="width:154" />*@至
@* <input class="easyui-datetimebox" type="text" name="endTime " data-options="width:154" />*@
<input id="dt2" type="text" editable="false" class="easyui-datebox" name="endTime" data-options="required:'true',missingMessage:'截止时间不能为空'" >
</td>
</tr>
<tr>
<th>相关人员：</th>
<td colspan="3">
<table>
<thead>
<tr>
<th>身份</th>
<th>姓名</th>
<th>电话</th>
<th>公司</th>
</tr>
</thead>
<tbody>
<tr>
<td>项目主管(甲方)</td>
<td>
<input id="m1" type="text" name="manager_1" class="easyui-validatebox" data-options="validType:'CHS' ,required:'true', missingMessage:'姓名不能为空'"/></td>
<td>
<input id="m1t" type="text" name="manager_1tel" class="easyui-validatebox" data-options="validType:'phoneAndMobile' ,required:'true', missingMessage:'联系电话不能为空'"/></td>
<td>
<input id="m1c" type="text" name="manager_1company" class="easyui-validatebox" data-options="required:'true', missingMessage:'公司不能为空'"/></td>
</tr>
<tr>
<td>项目主管（乙方）</td>
<td>
<input id="m2" type="text" name="manager_2" class="easyui-validatebox" data-options="validType:'CHS' ,required:'true', missingMessage:'姓名不能为空'" /></td>
<td>
<input id="m2t" type="text" name="manager_2tel" class="easyui-validatebox" data-options="validType:'phoneAndMobile' ,required:'true', missingMessage:'联系电话不能为空'"/></td>
<td>
<input id="m2c" type="text" name="manager_2company" class="easyui-validatebox" data-options="required:'true', missingMessage:'公司不能为空'" /></td>
</tr>
<tr>
<td>工程监理</td>
<td>
<input id="mo" type="text" name="monitor" class="easyui-validatebox" data-options="validType:'CHS' ,required:'true', missingMessage:'姓名不能为空'"/></td>
<td>
<input id="mot" type="text" name="monitortel" class="easyui-validatebox" data-options="validType:'phoneAndMobile' ,required:'true', missingMessage:'联系电话不能为空'"/></td>
<td>
<input id="moc" type="text" name="monitorcompany" class="easyui-validatebox" data-options="required:'true', missingMessage:'公司不能为空'"/></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody> </table>
</form>
<div id="dlg_modify_admin_buttons" style="text-align: left">
<a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-ok'" onclick="editUser()">修改</a>
<a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'" onclick="javascript:$('#editProjectWin').window('close');">取消</a>
</div>
</div>
<div> </div>
</body>
</html>

分页类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;namespace CarportManage.Models
{
public class datagrid
{ public int total { get; set; }
public object rows { get; set; } }
}

车辆管理控制器

using CarportManage.Models;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;namespace CarportManage.Controllers
{
public class CheController : Controller
{
//
// GET: /Che/
CardManageEntities3 db = new CardManageEntities3();
public ActionResult Index()
{
return View();
}
//添加
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult insert(FormCollection from)
{
int success;
try
{ CardManage t = new CardManage();
t.userName = from["add_name"];
t.userTel = from["add_tel"];
t.carNum = from["add_carNum"];
t.userDep = from["add_dep"];
t.fixedCarport = from["add_fixedCarport"];
db.CardManage.Add(t);
db.SaveChanges();
success = ; }
catch (Exception)
{
success = ; }
//new { success = true }
return Content(success.ToString());
} //查询加分页
public ActionResult select(string name, string carnum, string scar)
{
datagrid obj = new datagrid();
int pageSize = Convert.ToInt32(Request["rows"]);
int pageNum = Convert.ToInt32(Request["page"]);
if (name == null && carnum == null && scar == null)
{
var all = from a in db.CardManage select a;
obj.total = all.Count();
obj.rows = all.OrderBy(p => p.ID).Skip(pageSize * (pageNum - )).Take(pageSize);
return Json(obj, JsonRequestBehavior.AllowGet);
}
if (name != null && carnum != null && scar != null)
{
var al = (from c in db.CardManage where c.userName.Contains(name) && c.carNum.Contains(carnum) && c.fixedCarport.Contains(scar) select c).ToList();
obj.total = al.Count();
obj.rows = al.OrderBy(p => p.ID).Skip(pageSize * (pageNum - )).Take(pageSize);
return Json(al, JsonRequestBehavior.AllowGet);
}
else
{
var all = from a in db.CardManage select a;
obj.total = all.Count();
obj.rows = all.OrderBy(p => p.ID).Skip(pageSize * (pageNum - )).Take(pageSize); } return Json(obj);
}
//单条删除
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult del(string id)
{
CardManage tt = new CardManage() { ID = int.Parse(id) };
db.CardManage.Attach(tt);
db.CardManage.Remove(tt);
db.SaveChanges();
return Json(new { IsSuccess = true, Message = "删除成功" });
}
//批量删除
public ActionResult delete(string id)
{
int success;
try
{
string[] split = id.Split(new char[] { ',' });
for (int i = ; i < split.Count(); i++)
{
CardManage tt = new CardManage() { ID = int.Parse(split[i]) };
db.CardManage.Attach(tt);
db.CardManage.Remove(tt);
db.SaveChanges(); }
var all = from a in db.CardManage select a;
int total = all.Count();
if (total == )
{
////db.a();
}
success = ;
}
catch (Exception)
{ success = ;
} return Content(success.ToString());
}
public ActionResult upexcel(FormCollection from)
{
;
HttpPostedFileBase file = Request.Files["upload"]; string FileName;
string savePath = null;
if (file == null || file.ContentLength <= )
{
//xx不能为空
}
else
{
string filename = Path.GetFileName(file.FileName);
int fileSize = file.ContentLength;//上传文件大小
string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = * ;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (FileType.Contains(fileEx))
{
//格式不对
}
if (fileSize >= Maxsize)
{
//容量不对
}
string path = AppDomain.CurrentDomain.BaseDirectory + "uploads\\excel";//获取基目录
savePath = Path.Combine(path, FileName); //放进去
file.SaveAs(savePath);//保存.
}
string result = string.Empty;
try
{
CardManage cm = new CardManage(); string strConn;
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + "; " + "Extended Properties=Excel 8.0;";
strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", savePath);
//strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", file);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [$A1:R65536]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
// ...用foreach把tab中数据添加到数据库省略了如果是多表插入,可以调用存储过程.呵呵
int rowsnum = tab.Rows.Count;
if (rowsnum == )
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
// ...用foreach把tab中数据添加到数据库省略了如果是多表插入,可以调用存储过程.呵呵
DataRow[] dr = tab.Select();
for (int i = ; i < dr.Length; i++)
{ cm.userName = dr[i]["姓名"].ToString();
cm.userTel = dr[i]["联系电话"].ToString();
cm.userDep = dr[i]["车牌号码"].ToString();
cm.carNum = dr[i]["部门"].ToString();
cm.fixedCarport = dr[i]["固定车位"].ToString(); // excel列名【名称不能变,否则就会出错】
db.CardManage.Add(cm);
db.SaveChanges();
result = "导入成功！";
} }
catch (Exception)
{ result = "导入失败，请检查EXCEL格式是否错误！";
} JsonResult json = new JsonResult();
json.Data = result;
return json;
}
//excel nopi导出
public ActionResult npoiout()
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet st = workbook.CreateSheet("车输管理");
IRow row = st.CreateRow();
row.CreateCell(, CellType.String).SetCellValue("姓名");
row.CreateCell(, CellType.String).SetCellValue("联系电话");
row.CreateCell(, CellType.String).SetCellValue("车牌号码");
row.CreateCell(, CellType.String).SetCellValue("部门");
row.CreateCell(, CellType.String).SetCellValue("固定车位"); int i = ;
foreach (var item in db.CardManage)
{
IRow row1 = st.CreateRow(i);
row1.CreateCell(, CellType.String).SetCellValue(item.userName);
row1.CreateCell(, CellType.String).SetCellValue(item.userTel);
row1.CreateCell(, CellType.String).SetCellValue(item.carNum);
row1.CreateCell(, CellType.String).SetCellValue(item.userDep);
row1.CreateCell(, CellType.String).SetCellValue(item.fixedCarport); i++;
} workbook.Write(ms);
ms.Flush();
ms.Position = ;
return File(ms, "application/vnd.ms-excel", "车辆管理表" + System.DateTime.Now + ".xls");
} //excel导出
public FileResult ExportExcel()
{
CardManage cg = new CardManage();
var a = new StringBuilder();
var b = new List<string> { "姓名", "联系电话", "车牌号码", "部门", "固定车位" };
a.Append("<table border='1' cellspacing='0' cellpadding='0'>");
a.Append("<tr>");
foreach (var item in b)
{
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
}
a.Append("</tr>");
int count = db.CardManage.Count();
List<CardManage> c = new List<CardManage>
{
};
var d = db.CardManage; foreach (var item in d)
{
a.Append("<tr>");
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.userName);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.userTel);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.userDep);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.carNum);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.fixedCarport); a.Append("</tr>");
} a.Append("</table>");
byte[] fileContents = Encoding.Default.GetBytes(a.ToString());
return File(fileContents, "application/ms-excel", "CardManage测试数据.xls"); }
//前端获取路径导入，些方法高端浏览器不能使用，只能在IE上使用
public ActionResult UpLoad(string file)
{
string result = string.Empty;
try
{
CardManage cm = new CardManage(); string strConn;
//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + "; " + "Extended Properties=Excel 8.0;";
strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", file);
//strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", file);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [$A1:R65536]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
// ...用foreach把tab中数据添加到数据库省略了如果是多表插入,可以调用存储过程.呵呵
int rowsnum = tab.Rows.Count;
if (rowsnum == )
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
}
// ...用foreach把tab中数据添加到数据库省略了如果是多表插入,可以调用存储过程.呵呵
DataRow[] dr = tab.Select();
for (int i = ; i < dr.Length; i++)
{ cm.userName = dr[i]["姓名"].ToString();
cm.userTel = dr[i]["联系电话"].ToString();
cm.userDep = dr[i]["车牌号码"].ToString();
cm.carNum = dr[i]["部门"].ToString();
cm.fixedCarport = dr[i]["固定车位"].ToString(); // excel列名【名称不能变,否则就会出错】
db.CardManage.Add(cm);
db.SaveChanges();
result = "导入成功！";
} }
catch (Exception)
{ result = "导入失败，请检查EXCEL格式是否错误！";
} JsonResult json = new JsonResult();
json.Data = result;
return json;
} public ActionResult npoiload()
{
string a = "";
try
{
HttpPostedFileBase file = Request.Files["npoiload"];
Stream fs = file.InputStream;
IWorkbook wk = null;
try
{
wk = new XSSFWorkbook(fs);
}
catch (Exception ex)
{
wk = new HSSFWorkbook(fs);
} ISheet st = wk.GetSheetAt();
int rowCount = st.LastRowNum; for (int i = ; i < rowCount; i++)
{ CardManage cm = new CardManage();
cm.userName = st.GetRow(i).GetCell().ToString();
cm.userTel = st.GetRow(i).GetCell().ToString();
cm.carNum = st.GetRow(i).GetCell().ToString();
cm.userDep = st.GetRow(i).GetCell().ToString();
cm.fixedCarport = st.GetRow(i).GetCell().ToString();
db.CardManage.Add(cm);
db.SaveChanges();
a = "导入成功"; }
}
catch (Exception)
{ a = "导入失败，请检查EXCEL格式";
}
return Content(a);
} }
}

工程控件器

using CarportManage.Models;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;namespace CarportManage.Controllers
{
public class ProjectController : Controller
{
//
// GET: /Project/
CardManageEntities3 db = new CardManageEntities3(); public ActionResult Index()
{
return View();
}
public ActionResult sa(string name, string d1, string d2)
{ datagrid obj = new datagrid();
int pageSize = Convert.ToInt32(Request["rows"]);
int pageNum = Convert.ToInt32(Request["page"]);
if (name != null && name != "")
{
//var al = (from c in db.CardManage where c.userName.Contains(name)).ToList();
//var all = from a in db.projectManage.Where(a => a.projectName == name) select a;
var all = from a in db.projectManage where a.projectName.Contains(name) select a;
obj.total = all.Count();
obj.rows = all.OrderBy(p => p.projectName).Skip(pageSize * (pageNum - )).Take(pageSize); return Json(obj, JsonRequestBehavior.AllowGet);
}
if (d1 != null && d1 != "" && d2 != "" && d2 != null)
{
DateTime d = Convert.ToDateTime(d1);
DateTime dd = Convert.ToDateTime(d2); var all = (from t in db.projectManage where t.startTime >= d && t.endTime <= dd select t); obj.total = all.Count();
obj.rows = all.OrderBy(p => p.projectName).Skip(pageSize * (pageNum - )).Take(pageSize); return Json(obj, JsonRequestBehavior.AllowGet);
}
else
{
var all = from a in db.projectManage select a;
obj.total = all.Count();
obj.rows = all.OrderBy(p => p.ID).Skip(pageSize * (pageNum - )).Take(pageSize); } return Json(obj);
} public ActionResult ss()
{
var all = from a in db.projectManage select a;
int ab = all.Count();
return Content(ab.ToString());
}
//nopi excel导出
public ActionResult nopi()
{ MemoryStream ms = new MemoryStream(); IWorkbook wk = new HSSFWorkbook(); ISheet st = wk.CreateSheet("工程监控");
IRow rw = st.CreateRow();
rw.CreateCell().SetCellValue("项目名称");
rw.CreateCell().SetCellValue("是否动火作业");
rw.CreateCell().SetCellValue("施工开始时间");
rw.CreateCell().SetCellValue("施工结束时间");
rw.CreateCell().SetCellValue("甲方主管");
rw.CreateCell().SetCellValue("甲方主管电话");
rw.CreateCell().SetCellValue("甲方主管公司");
rw.CreateCell().SetCellValue("乙方主管");
rw.CreateCell().SetCellValue("乙方主管电话");
rw.CreateCell().SetCellValue("乙方主管公司");
rw.CreateCell().SetCellValue("工程监控者");
rw.CreateCell().SetCellValue("监控者电话");
rw.CreateCell().SetCellValue("监控者公司"); int a, b, c, d, e, f, g, h, i, j, k, l, m;
a = b = c = d = e = f = g = h = i = j = k = l = m = ; foreach (var item in db.projectManage)
{
st.CreateRow(a++).CreateCell().SetCellValue(item.projectName);
st.GetRow(b++).CreateCell().SetCellValue(item.isFire);
st.GetRow(c++).CreateCell().SetCellValue(item.startTime.ToShortDateString());
st.GetRow(d++).CreateCell().SetCellValue(item.endTime.ToShortDateString());
st.GetRow(e++).CreateCell().SetCellValue(item.manager_1);
st.GetRow(f++).CreateCell().SetCellValue(item.manager_1tel);
st.GetRow(g++).CreateCell().SetCellValue(item.manager_1company);
st.GetRow(h++).CreateCell().SetCellValue(item.manager_2);
st.GetRow(i++).CreateCell().SetCellValue(item.manager_2tel);
st.GetRow(j++).CreateCell().SetCellValue(item.manager_2company);
st.GetRow(k++).CreateCell().SetCellValue(item.monitor);
st.GetRow(l++).CreateCell().SetCellValue(item.monitortel);
st.GetRow(m++).CreateCell().SetCellValue(item.monitorcompany); } wk.Write(ms);
ms.Flush();
ms.Position = ; return File(ms, "application/vnd.ms-excel", "工程监控"+System.DateTime.Now+".xls");
} //excel导出
public FileResult ExportExcel()
{
projectManage cg = new projectManage();
var a = new StringBuilder();
var b = new List<string> { "项目名称", "是否动火作业", "施工开始时间", "施工结束时间", "甲方主管", "甲方主管电话", "甲方主管公司", "乙方主管", "乙方主管电话", "乙方主管公司", "工程监控者", "监控者电话", "监控者公司" };
a.Append("<table border='1' cellspacing='0' cellpadding='0'>");
a.Append("<tr>");
foreach (var item in b)
{
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
}
a.Append("</tr>");
int count = db.projectManage.Count();
List<projectManage> c = new List<projectManage>
{
};
var d = db.projectManage; foreach (var item in d)
{
a.Append("<tr>");
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.projectName);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.isFire);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.startTime);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.endTime);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.manager_1);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.manager_1tel);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.manager_1company);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.manager_2);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.manager_2tel);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.manager_2company);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.monitor);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.monitortel);
a.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item.monitorcompany);
a.Append("</tr>");
} a.Append("</table>");
byte[] fileContents = Encoding.Default.GetBytes(a.ToString());
return File(fileContents, "application/ms-excel", "projectManage测试数据.xls"); }
//excel上传后导入
public ActionResult upexcel()
{ HttpPostedFileBase file = Request.Files["upload"]; string FileName;
string savePath = null;
if (file == null || file.ContentLength <= )
{
//xx不能为空
}
else
{
string filename = Path.GetFileName(file.FileName);
int fileSize = file.ContentLength;//上传文件大小
string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = * ;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (FileType.Contains(fileEx))
{
//格式不对
}
if (fileSize >= Maxsize)
{
//容量不对
}
string path = AppDomain.CurrentDomain.BaseDirectory + "uploads\\excel";//获取基目录
savePath = Path.Combine(path, FileName); //放进去
file.SaveAs(savePath);//保存.
}
string result = string.Empty;
try
{
projectManage pm = new projectManage(); string strConn; strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", savePath);
//strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", file); OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [$A1:R65536]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
System.Data.DataTable tab = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); int rowsnum = tab.Rows.Count;
if (rowsnum == )
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
} DataRow[] dr = tab.Select();
for (int i = ; i < dr.Length; i++)
{ pm.projectName = dr[i]["项目名称"].ToString();
pm.isFire = dr[i]["是否动火作业"].ToString(); pm.startTime = Convert.ToDateTime(dr[i]["施工开始时间"]);
pm.endTime = Convert.ToDateTime(dr[i]["施工结束时间"]);
pm.manager_1 = dr[i]["甲方主管"].ToString();
pm.manager_1tel = dr[i]["甲方主管电话"].ToString();
pm.manager_1company = dr[i]["甲方主管公司"].ToString();
pm.manager_2 = dr[i]["乙方主管"].ToString();
pm.manager_2tel = dr[i]["乙方主管电话"].ToString();
pm.manager_2company = dr[i]["乙方主管公司"].ToString();
pm.monitor = dr[i]["工程监控者"].ToString();
pm.monitortel = dr[i]["监控者电话"].ToString();
pm.monitorcompany = dr[i]["监控者公司"].ToString(); // excel列名【名称不能变,否则就会出错】
db.projectManage.Add(pm);
db.SaveChanges();
result = "导入成功！";
}
}
catch (Exception)
{ result = "导入失败，请检查EXCEL格式是否错误！";
} JsonResult json = new JsonResult();
json.Data = result;
return json;
} public ActionResult npoiload()
{ string a = "";
try
{
HttpPostedFileBase file = Request.Files["npoiload"];
Stream fs = file.InputStream;
IWorkbook wk = null;
try
{
wk = new XSSFWorkbook(fs);
}
catch (Exception ex)
{
wk = new HSSFWorkbook(fs);
} ISheet st = wk.GetSheetAt();
int rowCount = st.LastRowNum; for (int i = ; i < rowCount; i++)
{ projectManage pm = new projectManage();
pm.projectName = st.GetRow(i).GetCell().ToString(); ;
pm.isFire = st.GetRow(i).GetCell().ToString();
pm.startTime = Convert.ToDateTime(st.GetRow(i).GetCell().ToString());
pm.endTime = Convert.ToDateTime(st.GetRow(i).GetCell().ToString());
pm.manager_1 = st.GetRow(i).GetCell().ToString();
pm.manager_1tel = st.GetRow(i).GetCell().ToString();
pm.manager_1company = st.GetRow(i).GetCell().ToString();
pm.manager_2 = st.GetRow(i).GetCell().ToString();
pm.manager_2tel = st.GetRow(i).GetCell().ToString();
pm.manager_2company = st.GetRow(i).GetCell().ToString();
pm.monitor = st.GetRow(i).GetCell().ToString();
pm.monitortel = st.GetRow(i).GetCell().ToString();
pm.monitorcompany = st.GetRow(i).GetCell().ToString();
db.projectManage.Add(pm);
db.SaveChanges();
a = "导入成功"; }
}
catch (Exception)
{ a = "导入失败，请检查EXCEL格式";
} return Content(a);
} //添加
//[AcceptVerbs(HttpVerbs.Post)]
public ActionResult add(FormCollection from)
{
//ViewData.Model = from["BookName"];
int success;
try
{
projectManage t = new projectManage();
//;tb_EmpInfoID()
t.projectName = from["projectName"];
t.manager_1 = from["manager_1"];
t.manager_2 = from["manager_2"];
t.monitor = from["monitor"];
t.isFire = from["isFire"];
t.startTime = Convert.ToDateTime(from["startTime"]);
t.endTime = Convert.ToDateTime(from["endTime"]);
t.manager_1tel = from["manager_1tel"];
t.manager_1company = from["manager_1company"];
t.manager_2tel = from["manager_2tel"];
t.manager_2company = from["manager_2company"];
t.monitortel = from["monitortel"];
t.monitorcompany = from["monitorcompany"];
db.projectManage.Add(t);
db.SaveChanges();
success = ; }
catch (Exception)
{
success = ; }
//new { success = true }
return Content(success.ToString());
}
//修改
public ActionResult edit(FormCollection from, projectManage tt)
{
// tb_Emp t = new tb_Emp() { EmpID = id };
// t.EmpName = from["EditEmpName"];
// t.EmpLoginPwd = from["EditEmpLoginPwd"];
int n;
DbEntityEntry<projectManage> entry = db.Entry<projectManage>(tt);
entry.State = System.Data.EntityState.Unchanged;
entry.Property(a => a.ID).IsModified = true;
entry.Property(a => a.projectName).IsModified = true;
entry.Property(a => a.manager_1).IsModified = true;
entry.Property(a => a.manager_2).IsModified = true;
entry.Property(a => a.monitor).IsModified = true;
entry.Property(a => a.isFire).IsModified = true;
entry.Property(a => a.startTime).IsModified = true;
entry.Property(a => a.endTime).IsModified = true;
entry.Property(a => a.manager_1tel).IsModified = true;
entry.Property(a => a.manager_1company).IsModified = true;
entry.Property(a => a.manager_2tel).IsModified = true;
entry.Property(a => a.manager_2company).IsModified = true;
entry.Property(a => a.monitorcompany).IsModified = true;
entry.Property(a => a.monitortel).IsModified = true; db.SaveChanges();
n = ;
return Content(n.ToString()); }
//批量删除
public ActionResult del(string id)
{
int success;
try
{
string[] split = id.Split(new char[] { ',' });
for (int i = ; i < split.Count(); i++)
{
projectManage tt = new projectManage() { ID = int.Parse(split[i]) };
db.projectManage.Attach(tt);
db.projectManage.Remove(tt);
db.SaveChanges(); }
success = ;
}
catch (Exception)
{ success = ;
} return Content(success.ToString());
} }
}