ASP.NET mvc导出Excel文件
首先要下载 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;
}
