导出Excel

导出Excel首先需要查询出你需要导出到Excel的数据，
function tabNoticeSearch() {
var RegionID = $("#RegionName").val();//对应from表单绑定的ID
var ManagerID = $("#ManagName").val();
var CustomerID = $("#CustomerTypeName").val();
var CustoStateID = $("#CustoStateName").val();
var BusinessTypeID = $("#BusinessTypeName").val();
var ArreaTypeID = $("#ArreaTypeName").val();
var CustoId = $("#CustoIdentifi1").val().trim();
//判断是否导出全部数据
if (RegionID == 0) {
layer.confirm(“是否导出全部数据？若不需要请筛选数据”, { icon: 3, title: ‘提示’ }, function (index) {
layer.close(index);
window.open(“ExportByID?RegionID=” + RegionID + “&ManagerID=” + ManagerID + “&CustomerTypeID=” + CustomerID +"&CustoStateID=" + CustoStateID + “&BusinessTypeID=” + BusinessTypeID + “&ArreaTypeID=” + ArreaTypeID);});// ExportByID=控制器的方法，后面跟着需要导出的数据加上上面from表单绑定ID的对应值
} else {
window.open(“ExportByID?RegionID=” + RegionID + “&ManagerID=” + ManagerID + “&CustomerTypeID=” + CustomerID +
“&CustoStateID=” + CustoStateID + “&BusinessTypeID=” + BusinessTypeID + “&ArreaTypeID=” + ArreaTypeID);}}
public ActionResult ExportExamineeByID(int RegionID, int ManagerID, int CustomerTypeID, int ServiStateID, int CustomerID) {
#region 查询需要导出的数据
var listBusinIncom = (from tbBusinIncom in myModel.BusinIncom
join tbCustomer in myModel.Customer on tbBusinIncom.CustomerID equals tbCustomer.CustomerID
join tbManager in myModel.Manager on tbBusinIncom.ManagerID equals tbManager.ManagerID
select new Export
{BusinIncomID = tbBusinIncom.BusinIncomID,CustoIdentifi = tbCustomer.CustoIdentifi,
CustomerName = tbCustomer.CustomerName,StatisMonth = tbBusinIncom.StatisMonth,
DebtCycle = tbBusinIncom.DebtCycle,Arrea = tbBusinIncom.Arrea,
RegionID = tbBusinIncom.RegionID,ManagerID = tbBusinIncom.ManagerID,
CustomerTypeID = tbBusinIncom.CustomerTypeID,ServiStateID = tbBusinIncom.ServiStateID,
CustomerID = tbBusinIncom.CustomerID,StatisMonthTime1 = tbBusinIncom.StatisMonth.ToString(),
DebtCycleTime1 = tbBusinIncom.DebtCycle.ToString()}).ToList();
if (RegionID > 0){listBusinIncom = listBusinIncom.Where(m => m.RegionID == RegionID).ToList();}if (ManagerID > 0){listBusinIncom = listBusinIncom.Where(m => m.ManagerID == ManagerID).ToList();}if (CustomerTypeID > 0){listBusinIncom = listBusinIncom.Where(m => m.CustomerTypeID == CustomerTypeID).ToList();}
if (ServiStateID > 0){listBusinIncom = listBusinIncom.Where(m => m.ServiStateID == ServiStateID).ToList();}if (CustomerID > 0){
listBusinIncom = listBusinIncom.Where(m => m.CustomerID == CustomerID).ToList();}
查出来后需要创建Excel表格
//创建Excel对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//创建Excel对象工作簿
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
//给sheet添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue(“客户标识”);
row1.CreateCell(1).SetCellValue(“客户名称”);
row1.CreateCell(2).SetCellValue(“统计月份”);
row1.CreateCell(3).SetCellValue(“债务周期”);
row1.CreateCell(4).SetCellValue(“金额”);
sheet.SetColumnWidth(2, 20 * 256);//
sheet.SetColumnWidth(3, 20 * 256);// //设置表格的宽度
for (int i = 0; i < listBusinIncom.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1) row.CreateCell(0).SetCellValue(listBusinIncom[i].CustoIdentifi);
row.CreateCell(1).SetCellValue(listBusinIncom[i].CustomerName);
row.CreateCell(2).SetCellValue(listBusinIncom[i].StatisMonthTime1.ToString());
row.CreateCell(3).SetCellValue(listBusinIncom[i].DebtCycleTime1.ToString());
row.CreateCell(4).SetCellValue(listBusinIncom[i].Arrea);
}//给每一行绑定数据
//输出的文件名称string fileName = “收入列单” + DateTime.Now.ToString(“yyyy-MM-dd-HH-mm-ss-ffff”) + “.xls”;
//把Excel转化为文件流，输出
MemoryStream BookStream = new MemoryStream();//定义文件流
book.Write(BookStream);//将工作薄写入文件流
BookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek（偏移量，游标位置）方法：获取文件流的长度
return File(BookStream, “application/vnd.ms-excel”, fileName); // 文件类型/文件名称/
选择是否导出全部数据