我们先来分析分析要制作Excel导出功能的思路
从功能上
1.导出全部
导出全部没有条件
2.导出部分
导出部分是特殊的导出全部,就是加条件进行筛选
步骤一
我们做Excel功能肯定要用到NPOI这个包,我们直接用Nuget包管理进行把NPOI的东西加进去
步骤二
在controller中把导出excel方法写好,然后调用sevice层中对数据的筛选
步骤三
在html中,就是前端中进行调用
那好,我们来看看代码是怎么实现的
步骤一直接省略
步骤二的代码实现功能
导出全部
/// <summary>
/// 首页导出全部到excel的功能
/// </summary>
/// <returns></returns>
public FileResult ExportIndex()
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.DefaultRowHeight = 15 * 15;
sheet1.DefaultColumnWidth = 15;
IRow row1 = sheet1.CreateRow(1);
row1.CreateCell(1).SetCellValue("序号");
row1.GetCell(1).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(2).SetCellValue("物品编号");
row1.GetCell(2).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(3).SetCellValue("项目编号");
row1.GetCell(3).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(4).SetCellValue("物品名称");
row1.GetCell(4).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(5).SetCellValue("物品型号");
row1.GetCell(5).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(6).SetCellValue("物品单位");
row1.GetCell(6).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(7).SetCellValue("物品单价");
row1.GetCell(7).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(8).SetCellValue("物品来源");
row1.GetCell(8).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(9).SetCellValue("入库时间");
row1.GetCell(9).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(10).SetCellValue("出库时间");
row1.GetCell(10).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(11).SetCellValue("状态");
row1.GetCell(11).CellStyle = Getcellstyle(book, Stylexls.默认标题);
//获取数据列表
//var list = _iAssetManageService.GetAssetManageModel();
var list = _iAssetManageService.GetAssetManageModel("", "", "", "", "", 0, "");
var index = 1;
var i = 0;
MemoryStream ms = new MemoryStream();
foreach (var item in list)
{
++index;
IRow rowtemp = sheet1.CreateRow(index);
++i;
rowtemp.CreateCell(1).SetCellValue(i);
rowtemp.GetCell(1).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(2).SetCellValue(item.GoodsNumber);
rowtemp.GetCell(2).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(3).SetCellValue(item.ItemId);
rowtemp.GetCell(3).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(4).SetCellValue(item.GoodsName);
rowtemp.GetCell(4).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(5).SetCellValue(item.GoodsType);
rowtemp.GetCell(5).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(6).SetCellValue(item.GoodsUnit);
rowtemp.GetCell(6).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(7).SetCellValue(item.GoodsPrice);
rowtemp.GetCell(7).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(8).SetCellValue(item.SourseOfGoods);
rowtemp.GetCell(8).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(9).SetCellValue(item.Storagetime);
rowtemp.GetCell(9).CellStyle = Getcellstyle(book, Stylexls.时间);
rowtemp.CreateCell(10).SetCellValue(item.DeliveryTime);
rowtemp.GetCell(10).CellStyle = Getcellstyle(book, Stylexls.时间);
rowtemp.CreateCell(11).SetCellValue(item.State);
rowtemp.GetCell(11).CellStyle = Getcellstyle(book, Stylexls.数据);
// 写入到客户端
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
}
return File(ms, "application/vnd.ms-excel", "资产管理首页[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "].xls");
}
导出部分
/// <summary>
/// 首页导出筛选后部分到excel
/// </summary>
/// <returns></returns>
public FileResult ExportIndexpart(string search, string startdate, string enddate, string startdate1, string enddate1, int? radiotype, string users)
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
sheet1.DefaultRowHeight = 15 * 15;
sheet1.DefaultColumnWidth = 15;
IRow row1 = sheet1.CreateRow(1);
row1.CreateCell(1).SetCellValue("序号");
row1.GetCell(1).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(2).SetCellValue("物品编号");
row1.GetCell(2).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(3).SetCellValue("项目编号");
row1.GetCell(3).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(4).SetCellValue("物品名称");
row1.GetCell(4).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(5).SetCellValue("物品型号");
row1.GetCell(5).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(6).SetCellValue("物品单位");
row1.GetCell(6).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(7).SetCellValue("物品单价");
row1.GetCell(7).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(8).SetCellValue("物品来源");
row1.GetCell(8).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(9).SetCellValue("入库时间");
row1.GetCell(9).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(10).SetCellValue("出库时间");
row1.GetCell(10).CellStyle = Getcellstyle(book, Stylexls.默认标题);
row1.CreateCell(11).SetCellValue("状态");
row1.GetCell(11).CellStyle = Getcellstyle(book, Stylexls.默认标题);
//获取数据列表
//var list = _iAssetManageService.GetAssetManageModel();
var list = _iAssetManageService.GetAssetManageModel(search, startdate, enddate, startdate1, enddate1, radiotype, users);
var index = 1;
var i = 0;
MemoryStream ms = new MemoryStream();
foreach (var item in list)
{
++index;
IRow rowtemp = sheet1.CreateRow(index);
++i;
rowtemp.CreateCell(1).SetCellValue(i);
rowtemp.GetCell(1).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(2).SetCellValue(item.GoodsNumber);
rowtemp.GetCell(2).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(3).SetCellValue(item.ItemId);
rowtemp.GetCell(3).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(4).SetCellValue(item.GoodsName);
rowtemp.GetCell(4).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(5).SetCellValue(item.GoodsType);
rowtemp.GetCell(5).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(6).SetCellValue(item.GoodsUnit);
rowtemp.GetCell(6).CellStyle = Getcellstyle(book, Stylexls.数据);
rowtemp.CreateCell(7).SetCellValue(item.GoodsPrice);
rowtemp.GetCell(7).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(8).SetCellValue(item.SourseOfGoods);
rowtemp.GetCell(8).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(9).SetCellValue(item.Storagetime);
rowtemp.GetCell(9).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(10).SetCellValue(item.DeliveryTime);
rowtemp.GetCell(10).CellStyle = Getcellstyle(book, Stylexls.数字);
rowtemp.CreateCell(11).SetCellValue(item.State);
rowtemp.GetCell(11).CellStyle = Getcellstyle(book, Stylexls.数据);
// 写入到客户端
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
}
return File(ms, "application/vnd.ms-excel", "资产管理首页部分[" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "].xls");
}
前面,我说过导出部分是特殊的导出全部功能。
导出全部的数据:
var list = _iAssetManageService.GetAssetManageModel("", "", "", "", "", 0, "");
参数都是空或0
导出部分的数据
var list = _iAssetManageService.GetAssetManageModel(search, startdate, enddate, startdate1, enddate1, radiotype, users);
其中,service层中对数据进行筛选我们就不细说了 就是取数据把取得的数据进行改变而已
步骤三就是去调用咯
调用就是用对应的方法,把对应的参数传过去就行了
导出全部
Html代码
<button class="btn btn-success btn-sm btn-export" οnclick="exportexcelall();">导出全部到Excel</button>
对应的js
function exportexcelall() {
location.href = '@Url.Action("ExportIndex")';
}
导出部分
Html代码
<button class="btn btn-success btn-sm btn-export" οnclick="exportexcelpart();">导出筛选后到Excel</button>
对应的js
function exportexcelpart() {
var search = $('#txtSearch').val().trim();
var startdate = $('.start-date').val().trim();
var enddate = $('.end-date').val().trim();
var startdate1 = $('.start-date1').val().trim();
var enddate1 = $('.end-date1').val().trim();
var radiotype = $('input[name="inlineRadioOptions"]:checked').val();
var users = $('.users-id').val();
location.href = '@Url.Action("ExportIndexpart")' + '?search=' + search + '&startdate=' + startdate + '&enddate=' + enddate + '&startdate1=' + startdate1 + '&enddate1=' + enddate1 + '&radiotype=' + radiotype + '&users=' + users;
}