• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

ASP.NETmvc导出Excel文件

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

首先要下载 NPOI.dll 引用到项目中

 

 

第一步。

 

 

第二步控制台(业务逻辑层)

public ActionResult Export(string CustomerName="",int SumbitUser=0,string Level="",DateTime? StartDate=null, DateTime? EndDate =null,int Industry=0,string CustomerCode="",int PageIndex=0,int PageSize=0)

{

try

{

///下面是添加表的标题

DataTable dtSouce = new DataTable();

//客户基本信息

dtSouce.Columns.Add("客户名称");

dtSouce.Columns.Add("客户代码");

dtSouce.Columns.Add("客户等级");

dtSouce.Columns.Add("所属行业");

dtSouce.Columns.Add("上一年产值");

dtSouce.Columns.Add("主要产品类型");

dtSouce.Columns.Add("终端客户");

dtSouce.Columns.Add("上一年PCB采购额度");

dtSouce.Columns.Add("主要层数分布");

dtSouce.Columns.Add("建议采取措施");

//合作信息

dtSouce.Columns.Add("合作年份");

dtSouce.Columns.Add("采购额度");

dtSouce.Columns.Add("预计采购额度");

dtSouce.Columns.Add("我司报价情况");

dtSouce.Columns.Add("客户投诉情况");

dtSouce.Columns.Add("目前进展");

dtSouce.Columns.Add("合作风险");

dtSouce.Columns.Add("付款方式");

dtSouce.Columns.Add("回款期");

//竞争对手信息

dtSouce.Columns.Add("竞争对手名称");

dtSouce.Columns.Add("供货层数");

dtSouce.Columns.Add("供货类型");

dtSouce.Columns.Add("采购比例");

dtSouce.Columns.Add("价格信息");

dtSouce.Columns.Add("质量情况");

dtSouce.Columns.Add("服务(关系)情况");

dtSouce.Columns.Add("交货期情况");

var user = (AuthProvider.CurrentUserInfo)Thread.CurrentPrincipal;

var userModel = BusinessService.SystemManage.UserInfoService.GetSingleUserInfo(user.Identity.Name);//获取当前用户信息

var records = BusinessService.CustomerManage.LCustomerService.GetListPage(CustomerName, CustomerCode, Level, Industry, SumbitUser, 0, 0, StartDate, EndDate, PageIndex, PageSize,0, userModel.ID);//得到部分数据

var lPayType = BusinessService.SystemManage.PayTypeService.GetList("");//得到部分数据

if (records != null && records.Count > 0)//下面是表里面的数据赋值

{

foreach (var item in records)

{

var row = dtSouce.NewRow();

//客户基本信息

row[0] = item.CustomerName;

row[1] = item.CustomerCode;

row[2] = item.Level;

row[3] = "";

var recordModel = new Models.LCustomerRecordsViewModel();

var industry = BusinessService.SystemManage.IndustryInfoService.GetSingle(item.Industry);

if (industry != null)

{

row[3] = industry.Name;

}

row[4] = item.PreYearPValue.ToString();

row[5] = item.MainProductTypes;

row[6] = item.TerminalCustomers;

row[7] = item.PreYearPCBTradeCredit;

row[8] = item.MainLayersDistribution;

row[9] = item.SuggestUseMeasures;

//合作信息

var cooperatorInfo = BusinessService.CustomerManage.LCustomerService.GetLCooperation(item.ID);

if (cooperatorInfo!=null&&cooperatorInfo.Count>0)

{

row[10] = cooperatorInfo.First().CooperationYear;

row[11] = cooperatorInfo.First().TradeCredit.ToString();

row[12] = cooperatorInfo.First().ExpectMyTradeCredit.ToString();

row[13] = cooperatorInfo.First().MyQuotationInfo;

row[14] = cooperatorInfo.First().CustomerComplaintsInfo;

row[15] = cooperatorInfo.First().CurrentProgressInfo;

row[16] = cooperatorInfo.First().CooperationRisk;

if (cooperatorInfo.First().PayType>0)

{

var payType = lPayType.Where(m => m.ID.Equals(cooperatorInfo.First().PayType)).ToList();

if (payType!=null&&payType.Count>0)

{

row[17] = payType.First().Name;

}

}

row[18] = cooperatorInfo.First().PaybackPeriod;

}

//竞争对手信息

var competitorInfo = BusinessService.CustomerManage.LCustomerService.GetLCompetitors(item.ID);

if (competitorInfo!=null&&competitorInfo.Count>0)

{

row[19] = competitorInfo.First().Name;

row[20] = competitorInfo.First().GoodsLayers;

row[21] = competitorInfo.First().GoodsTypes;

row[22] = competitorInfo.First().ProcurementPercent;

row[23] = competitorInfo.First().PriceInfo;

row[24] = competitorInfo.First().QualityInfo;

row[25] = competitorInfo.First().ServiceInfo;

row[26] = competitorInfo.First().DeliveryDateInfo;

}

dtSouce.Rows.Add(row);

 

//var subitUser = BusinessService.SystemManage.UserInfoService.GetSingleUserInfo(item.SumbitUser);

//recordModel.SumbitUser = subitUser.ChinessName;

//if (item.FollowUser != null)

//{

// var followUser = BusinessService.SystemManage.UserInfoService.GetSingleUserInfo((int)item.FollowUser);

// recordModel.FollowUser = followUser.ChinessName;

//}

//else

//{

// recordModel.FollowUser = "暂未指定";

//}

}

}

var wookbook = Helper.ExcelHelper.DataTable2Excel(dtSouce,2, "", Server.MapPath("~/ExcelTemp/潜力客户信息导入模板.xlsx"));//地址 (引用帮助类)

//ms.Seek(0, SeekOrigin.Begin);

Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", "潜力客户信息列表" + DateTime.Now.ToString("yyyyMMddHHmmssfff")));//表名

//Response.BinaryWrite(byteData);

wookbook.SaveAs(Response.OutputStream);

//ms.Close();

//ms.Dispose();

 

return Content("");

}

catch (Exception ex)

{

throw ex;

}

}

 

第三步(帮助类)

/// <summary>

/// 将Excel内容转换为DataTable

/// </summary>

/// <param name="fs">文件流</param>

/// <param name="isFirstRowColumn">标题列</param>

/// <param name="sheetName">工作表名称,若为空则取第一个工作表</param>

/// <returns></returns>

public static DataTable GetDataFromExcel(Stream fs, int titleIndex, string sheetName)

{

DataTable data = new DataTable();

try

{

ISheet sheet = null;

IWorkbook workbook = null;

int startRow = 0;

workbook = new XSSFWorkbook(fs);

if (workbook == null)

{

workbook = new HSSFWorkbook(fs);

}

if (!string.IsNullOrEmpty(sheetName))

{

sheet = workbook.GetSheet(sheetName);

if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet

{

sheet = workbook.GetSheetAt(0);

}

}

else

{

sheet = workbook.GetSheetAt(0);

}

if (sheet != null)

{

IRow firstRow = sheet.GetRow(titleIndex);

int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

 

for (int i = firstRow.FirstCellNum; i < cellCount; ++i)

{

ICell cell = firstRow.GetCell(i);

if (cell != null)

{

string cellValue = cell.StringCellValue;

if (cellValue != null)

{

DataColumn column = new DataColumn(cellValue);

data.Columns.Add(column);

}

}

}

startRow = titleIndex + 1;

 

//最后一列的标号

int rowCount = sheet.LastRowNum;

for (int i = startRow; i <= rowCount; ++i)

{

IRow row = sheet.GetRow(i);

if (row == null) continue; //没有数据的行默认是null       

 

DataRow dataRow = data.NewRow();

for (int j = row.FirstCellNum; j < cellCount; ++j)

{

if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null

dataRow[j] = row.GetCell(j).ToString();

}

data.Rows.Add(dataRow);

}

}

}

catch (Exception ex)

{

ExceptionHelper.ThrowReferensNullException("Excel文件转换错误,请确认填写的数据格式是否跟模板一致。");

}

finally

{

fs.Close();

fs.Dispose();

}

return data;

}

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap