你的位置:首页 > ASP.net教程

[ASP.net教程]MVC下载Excel


方法1:

public ActionResult DownExcel()

{

var stream = list.Select(p => new
{
p.UserName,
p.Mobile,
Status = CommonUtilities.GetEnumDescription<UserStatus>(p.Status ?? 0)
}).ToExcel("sheet1",
new ColumnMap("UserName", "员工姓名"),
new ColumnMap("Mobile", "手机号码"),
new ColumnMap("Status", "账户状态"));

return File(stream, "application/vnd.ms-excel", string.Format("员工信息_{0:yyyyMMdd}.xls", DateTime.Now));

}

方法2:

public ActionResult DownLoadExcel()

{

var list=new List();//list,根据情况取数据

if (list!= null && list.Count > 0)
{
     //下载数据-导Excel
      CreateExcel(list, (HttpContextBase)HttpContext);

}
return null;

}

public void CreateExcel(List<CompanyUserInfoViewModel> list, HttpContextBase context)
{


IWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
ISheet sheet = workbook.CreateSheet("Sheet1");//创建工作表

#region CellStyle
ICellStyle CellStyle = workbook.CreateCellStyle();
CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
CellStyle.VerticalAlignment = VerticalAlignment.Center;
#endregion

#region TitleStyle
IFont fontStyle = workbook.CreateFont();
fontStyle.Color = NPOI.HSSF.Util.HSSFColor.White.Index;

ICellStyle TitleStyle = workbook.CreateCellStyle();
TitleStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
TitleStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
TitleStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
TitleStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
TitleStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
TitleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
TitleStyle.SetFont(fontStyle);
#endregion

#region 生成标题行
//Title
string[] arrStr = { "编号",  "联系人", "手机","状态" };
int[] arrWidth = { 12, 30, 24, 20};

IRow row = sheet.CreateRow(0); //在工作表中标题行
for (int i = 0; i < arrStr.Length; i++)
{
     sheet.SetColumnWidth(i, arrWidth[i] * 256); //列宽

     ICell cell = row.CreateCell(i);
     cell.SetCellValue(arrStr[i]);
     cell.CellStyle = TitleStyle;
}
#endregion

 

int currentRow = 0;
//生成数据行

foreach (var item in list)
{
       CreateRow(sheet, item, ref currentRow, CellStyle);

}

#region 输出文件

string sFileName="文件名称";
MemoryStream sw = new MemoryStream();
workbook.Write(sw);
sw.Seek(0, SeekOrigin.Begin);
byte[] bf = sw.GetBuffer();
sw.Close();

context.Response.Clear();
context.Response.Buffer = true;
context.Response.Charset = "GB2312";
#region 设定文件名
if (context.Request.UserAgent.ToLower().IndexOf("msie") > -1)
{
sFileName = HttpUtility.UrlPathEncode(sFileName);
}
if (context.Request.UserAgent.ToLower().IndexOf("firefox") > -1)
{
context.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + sFileName + "\"");
}
else
{
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + sFileName);
}
#endregion
context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
context.Response.ContentType = "application/ms-excel";
context.Response.BinaryWrite(bf);
#endregion

}

 protected void CreateRow(ISheet _sheet, ViewModel info, ref int _currentRow, ICellStyle _CellStyle)

{

IRow newRow = _sheet.CreateRow(++_currentRow);
newRow.CreateCell(0).SetCellValue(info.ID);
newRow.CreateCell(1).SetCellValue(info.UserName);
newRow.CreateCell(2).SetCellValue(info.Mobile);
newRow.CreateCell(3).SetCellValue(CommonUtilities.GetCompanyStatus(info.Status));

}