mvc5+ef+ adminlte教程16-1：两张表进行汇总、对比分析（以欠费清单管理为例
我感觉有时用得很多的是两张表的比对。
比如表1，XX,XXX,XXX几个人的数据清单。
表2，是另一个时间段的清单，
对比两张表的金额变化减少有多少，
对比两张表数量减少有多少。
就是包含 和不包含 。
两个表都需要手动导入。
一、在Model下新建两个模型。Arrearsone和Arrearstwo，
两个的内容和字段全部一样。
[Display(Name = "ID")]
public string Id { get; set; }
[Display(Name = "序号")]
public int SerialNumber{ get; set; }
[Display(Name = "用户名")]
public string UserName { get; set; }
[Display(Name = "号码")]
public string Number { get; set; }
[Display(Name = "账号ID")]
public string AccountId { get; set; }
[Display(Name = "联系电话")]
public string ContactNumber{ get; set; }
[Display(Name = "产品")]
public string Product { get; set; }
[Display(Name = "套餐")]
public string Package { get; set; }
[Display(Name = "金额")]
public double AmountMoney{ get; set; }
[Display(Name = "账龄")]
public int Aging { get; set; }
[Display(Name = "地址")]
public string Address { get; set; }
[Display(Name = "付费类型")]
public string PayType { get; set; }
[Display(Name = "责任部门")]
public string ResponsibleDePart { get; set; }
[Display(Name = "责任人")]
public string ResponsiblePeople { get; set; }
[Display(Name = "备注")]
public string Bei { get; set; }
然后在DAL下面的上下文中增加内容
public DbSet<Arrears2> Arrears2s { get; set; }
public DbSet<Arrears1> Arrears1s { get; set; }
记得要生成解决方案哟。然后first code.
(1)Enable-Migrations
(2)Enable-Migrations -ContextTypeName jsdhh2.DAL.OaDALContent
(3)update-database ：注意下面的表的名字不是这个NewArrearones,NewArrearTwos
2.为了方便，我直接生成了NewArrearones的控制器。 下面图中的MODEL也是错的，当时删除了这个重新建 的。
3.修改index()界面，增加两个导入按钮，分别用于导入NewArrearones和NewArreartwos
下面的代码没有帖，因为暂时只用上导入,分析是否超级用户，这个功能用得多，所以用户管理中，必须有一个角色是Super,有点累，但为了学习方便。
@if (Session["role"].ToString()=="Super") { @Html.ActionLink("导入Arrears1", "ImportArrears", "", new { @class = "btn btn-success" })
}
4.新建 ImportArrears方法和视图
//导入的界面操作
public ActionResult ImportArrears()
{
return View();
}
5.新建视图，主要有二个导入。
在这一步的时候，有高手建议ID用string类型，用GUID来生成，因我一直用INT,会导致主键 不停的增加。
我改了上面的id为string，所以不会。我搞了几个小时。唉。
<h2>请选择相应的导入</h2>
<div class="col-md-12">
<div class="nav-tabs-custom">
<ul class="nav nav-tabs">
<li class=""><a href="#activity" data-toggle="tab" aria-expanded="false">导入Arrears1</a></li>
<li class="active"><a href="#timeline" data-toggle="tab" aria-expanded="true">导入Arrears2</a></li>
<li class=""><a href="#settings" data-toggle="tab" aria-expanded="false">模板下载</a></li>
</ul>
<div class="tab-content">
<div class="tab-pane" id="activity">
@using (Html.BeginForm("LeadingOne", "NewArrearones", FormMethod.Post, new { enctype = "multipart/form-data", id = "form_Upload" }))
{
@Html.AntiForgeryToken() //防止跨站请求伪造(CSRF:Cross-site request forgery)攻击
<input id="input-41" name="input41[]" type="file" multiple class="file-loading">
<hr>
<button type="submit" class="btn btn-primary">上传1</button>
<button type="reset" class="btn btn-default">清除</button>
}
</div>
<!-- /.tab-pane -->
<div class="tab-pane active" id="timeline">
<!-- The timeline -->
@using (Html.BeginForm("LeadingTwo", "NewArrearones", FormMethod.Post, new { enctype = "multipart/form-data", id = "form_Upload" }))
{
@Html.AntiForgeryToken() //防止跨站请求伪造(CSRF:Cross-site request forgery)攻击
<input id="input-42" name="input41[]" type="file" multiple class="file-loading">
<hr>
<button type="submit" class="btn btn-primary">上传2</button>
<button type="reset" class="btn btn-default">清除</button>
}
</div>
<!-- /.tab-pane -->
<div class="tab-pane" id="settings">
<a href="~/Content/Excle/mp-arrearsone.xls">Arrears1表格下载</a>
</div>
<!-- /.tab-pane -->
</div>
<!-- /.tab-content -->
</div>
<!-- /.nav-tabs-custom -->
</div>
@section scripts{
<link href="~/Content/bootstrap-fileinput/css/fileinput.css" rel="stylesheet" />
<script src="~/Content/bootstrap-fileinput/js/fileinput.js"></script>
<script>
$(document).on('ready', function () {
$("#input-41").fileinput({
maxFileCount: 10,
maxFileSize: 1000,
allowedPreviewTypes: ['other'],
allowedFileExtensions: ['xlsx','xls']
});
});
</script>
<script>
$(document).on('ready', function () {
$("#input-42").fileinput({
maxFileCount: 10,
maxFileSize: 1000,
allowedPreviewTypes: ['other'],
allowedFileExtensions: ['xlsx', 'xls']
});
});
</script>
}
效果:滑动门效果。
6.导入的两个方法
测试全部正常。
//导入Arrears1的方法
public ActionResult LeadingOne()
{
//int saveCount = 0; //定义变量
//if (ModelState.IsValid && !string.IsNullOrEmpty(id))
//{}
var files = Request.Files;
if (files.Count > 0)
{
var file = files[0];
string strFileSavePath = Request.MapPath("~/Content/Excle"); //定义上传目标地址
string strFileExtention = Path.GetExtension(file.FileName);
if (!Directory.Exists(strFileSavePath))
{
Directory.CreateDirectory(strFileSavePath);
}
var dt = DateTime.Now;
string str = dt.ToString("yyyyMMddHHmmss");
var savePath = strFileSavePath + "/" + str + strFileExtention; //拼接保存文件路径
file.SaveAs(strFileSavePath + "/" + str + strFileExtention); //保存文件
//导入的代码
HSSFWorkbook hssfworkbook;
try
{
//读出表中数据
using (FileStream filesw = new FileStream(savePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(filesw);
}
//删除所有的数据，就是清空表原有数据
List<NewArrearone> xx = db.NewArrearones.ToList();
db.NewArrearones.RemoveRange(xx);
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
//DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
//for (int j = row.FirstCellNum; j < cellCount; j++)
//{
// if (row.GetCell(j) != null)
// dataRow[j] = GetCellValue(row.GetCell(j));
//}
NewArrearone arrears1 = new NewArrearone();
arrears1.Id = Guid.NewGuid().ToString();
arrears1.SerialNumber = Convert.ToInt32(row.GetCell(0).ToString());
arrears1.UserName = row.GetCell(1).ToString();
arrears1.Number = row.GetCell(2).ToString();
arrears1.AccountId = row.GetCell(3).ToString();
arrears1.ContactNumber = row.GetCell(4).ToString();
arrears1.Product = row.GetCell(5).ToString();
arrears1.Package = row.GetCell(6).ToString();
arrears1.AmountMoney = Convert.ToDouble(row.GetCell(7).ToString());
arrears1.TotalMoney = Convert.ToDouble(row.GetCell(8).ToString());
arrears1.Aging = Convert.ToInt32(row.GetCell(9).ToString());
arrears1.Address = row.GetCell(10).ToString();
arrears1.PayType = row.GetCell(11).ToString();
arrears1.ResponsibleDePart = row.GetCell(12).ToString();
arrears1.ResponsiblePeople = row.GetCell(13).ToString();
arrears1.Bei = row.GetCell(14).ToString();
db.NewArrearones.Add(arrears1);
}
}
db.SaveChanges();
//删除上传的文件
System.IO.File.Delete(savePath);
return RedirectToAction("Index", "NewArrearones");
}
return View();
}
// //导入Arrears2的方法
public ActionResult LeadingTwo()
{
//int saveCount = 0; //定义变量
//if (ModelState.IsValid && !string.IsNullOrEmpty(id))
//{}
var files = Request.Files;
if (files.Count > 0)
{
var file = files[0];
string strFileSavePath = Request.MapPath("~/Content/Excle"); //定义上传目标地址
string strFileExtention = Path.GetExtension(file.FileName);
if (!Directory.Exists(strFileSavePath))
{
Directory.CreateDirectory(strFileSavePath);
}
var dt = DateTime.Now;
string str = dt.ToString("yyyyMMddHHmmss");
var savePath = strFileSavePath + "/" + str + strFileExtention; //拼接保存文件路径
file.SaveAs(strFileSavePath + "/" + str + strFileExtention); //保存文件
//导入的代码
HSSFWorkbook hssfworkbook;
try
{
//读出表中数据
using (FileStream filesw = new FileStream(savePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(filesw);
}
//删除所有的数据，就是清空表原有数据
List<NewArreartwo> xx = db.NewArreartwos.ToList();
db.NewArreartwos.RemoveRange(xx);
}
catch (Exception e)
{
throw e;
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
//DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
//for (int j = row.FirstCellNum; j < cellCount; j++)
//{
// if (row.GetCell(j) != null)
// dataRow[j] = GetCellValue(row.GetCell(j));
//}
NewArreartwo arrears1 = new NewArreartwo();
arrears1.Id = Guid.NewGuid().ToString();
arrears1.SerialNumber = Convert.ToInt32(row.GetCell(0).ToString());
arrears1.UserName = row.GetCell(1).ToString();
arrears1.Number = row.GetCell(2).ToString();
arrears1.AccountId = row.GetCell(3).ToString();
arrears1.ContactNumber = row.GetCell(4).ToString();
arrears1.Product = row.GetCell(5).ToString();
arrears1.Package = row.GetCell(6).ToString();
arrears1.AmountMoney = Convert.ToDouble(row.GetCell(7).ToString());
arrears1.TotalMoney = Convert.ToDouble(row.GetCell(8).ToString());
arrears1.Aging = Convert.ToInt32(row.GetCell(9).ToString());
arrears1.Address = row.GetCell(10).ToString();
arrears1.PayType = row.GetCell(11).ToString();
arrears1.ResponsibleDePart = row.GetCell(12).ToString();
arrears1.ResponsiblePeople = row.GetCell(13).ToString();
arrears1.Bei = row.GetCell(14).ToString();
db.NewArreartwos.Add(arrears1);
}
}
db.SaveChanges();
//删除上传的文件
System.IO.File.Delete(savePath);
return RedirectToAction("Index", "NewArrearones");
}
return View();
}
7.我想在这个页面，显示汇总信息，要是点哪一个汇总的链接，他就跑到另一个社图。ArreasList
// GET: 欠费用户清单
public async Task<ActionResult> ArreasList()
{
return View(await db.NewArrearones.ToListAsync());
}
8.添加视图，暂不增加方法。
9.在index页面，增加两个按钮，已前期加了的，一个是导出所有的月初总数据，一个导出最新的欠费数
但最新的这个需要和总表比对，原因很简单，只有月初分配任务，后期只有表中数作比较。
@Html.ActionLink("导出月初数Excle", "ToExlceArrearone", "", new { @class = "btn btn-primary" }) @Html.ActionLink("导出最新数Excle", "ToExlceArreartwo", "", new { @class = "btn btn-warning" })
@if (Session["role"].ToString() == "Super")
{ @Html.ActionLink("导入Arrears1", "ImportArrears", "", new { @class = "btn btn-success" })
}
两个方法
//导出的方法Npoi
public FileResult ToExlceArrearone()
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据，这里直接获取所有的newarreasones表中数据
List<NewArrearone> listRainInfo = db.NewArrearones.ToList();
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//从第2列开始写，第一列是guid.ID
row1.CreateCell(1).SetCellValue("序号");
row1.CreateCell(2).SetCellValue("用户名");
row1.CreateCell(3).SetCellValue("号码");
row1.CreateCell(4).SetCellValue("账号ID");
row1.CreateCell(5).SetCellValue("联系电话");
row1.CreateCell(6).SetCellValue("产品");
row1.CreateCell(7).SetCellValue("套餐");
row1.CreateCell(8).SetCellValue("当月金额");
row1.CreateCell(9).SetCellValue("总金额");
row1.CreateCell(10).SetCellValue("账龄");
row1.CreateCell(11).SetCellValue("地址");
row1.CreateCell(12).SetCellValue("付费类型");
row1.CreateCell(13).SetCellValue("责任部门");
row1.CreateCell(14).SetCellValue("责任人");
row1.CreateCell(15).SetCellValue("备注哟");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
//也从第二列开始写
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].SerialNumber.ToString());
rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].UserName.ToString());
rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Number.ToString());
rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].AccountId.ToString());
rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].ContactNumber.ToString());
rowtemp.CreateCell(6).SetCellValue(listRainInfo[i].Product.ToString());
rowtemp.CreateCell(7).SetCellValue(listRainInfo[i].Package.ToString());
rowtemp.CreateCell(8).SetCellValue(listRainInfo[i].AmountMoney);
rowtemp.CreateCell(9).SetCellValue(listRainInfo[i].TotalMoney);
rowtemp.CreateCell(10).SetCellValue(listRainInfo[i].Aging);
rowtemp.CreateCell(11).SetCellValue(listRainInfo[i].Address.ToString());
rowtemp.CreateCell(12).SetCellValue(listRainInfo[i].PayType.ToString());
rowtemp.CreateCell(13).SetCellValue(listRainInfo[i].ResponsibleDePart.ToString());
rowtemp.CreateCell(14).SetCellValue(listRainInfo[i].ResponsiblePeople.ToString());
rowtemp.CreateCell(15).SetCellValue(listRainInfo[i].Bei.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "月初欠费总表.xls");
}
//导出的方法Npoi
public FileResult ToExlceArreartwo()
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据，因是最新欠费表，可能需要关联责任人与责任部门，主要原因是只有月初才分配清单。以后只是对比,所以字段全是表1。
var bijiao = (from m in db.NewArrearones
join mr in db.NewArreartwos
on m.Number equals mr.Number
select m).ToList();
List<NewArrearone> listRainInfo = bijiao;
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//从第2列开始写，第一列是guid.ID
row1.CreateCell(1).SetCellValue("序号");
row1.CreateCell(2).SetCellValue("用户名");
row1.CreateCell(3).SetCellValue("号码");
row1.CreateCell(4).SetCellValue("账号ID");
row1.CreateCell(5).SetCellValue("联系电话");
row1.CreateCell(6).SetCellValue("产品");
row1.CreateCell(7).SetCellValue("套餐");
row1.CreateCell(8).SetCellValue("当月金额");
row1.CreateCell(9).SetCellValue("总金额");
row1.CreateCell(10).SetCellValue("账龄");
row1.CreateCell(11).SetCellValue("地址");
row1.CreateCell(12).SetCellValue("付费类型");
row1.CreateCell(13).SetCellValue("责任部门");
row1.CreateCell(14).SetCellValue("责任人");
row1.CreateCell(15).SetCellValue("备注哟");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
//也从第二列开始写
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].SerialNumber.ToString());
rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].UserName.ToString());
rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Number.ToString());
rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].AccountId.ToString());
rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].ContactNumber.ToString());
rowtemp.CreateCell(6).SetCellValue(listRainInfo[i].Product.ToString());
rowtemp.CreateCell(7).SetCellValue(listRainInfo[i].Package.ToString());
rowtemp.CreateCell(8).SetCellValue(listRainInfo[i].AmountMoney);
rowtemp.CreateCell(9).SetCellValue(listRainInfo[i].TotalMoney);
rowtemp.CreateCell(10).SetCellValue(listRainInfo[i].Aging);
rowtemp.CreateCell(11).SetCellValue(listRainInfo[i].Address.ToString());
rowtemp.CreateCell(12).SetCellValue(listRainInfo[i].PayType.ToString());
rowtemp.CreateCell(13).SetCellValue(listRainInfo[i].ResponsibleDePart.ToString());
rowtemp.CreateCell(14).SetCellValue(listRainInfo[i].ResponsiblePeople.ToString());
rowtemp.CreateCell(15).SetCellValue(listRainInfo[i].Bei.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "最新欠费表.xls");
}
测试正常。
未完请看下一章。
