方法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));
}