首先要下载 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;

}