MVC download Excel, mvcexcel
Method 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", "employee name "),
New ColumnMap ("Mobile", "Mobile phone number "),
New ColumnMap ("Status", "account Status "));
Return File (stream, "application/vnd. ms-excel", string. Format ("employee info _ {0: yyyyMMdd}.xls", DateTime. Now ));
}
Method 2:
Public ActionResult DownLoadExcel ()
{
Var list = new List (); // list, fetch data as needed
If (list! = Null & list. Count> 0)
{
// Download data-import Excel
CreateExcel (list, (HttpContextBase) HttpContext );
}
Return null;
}
Public void CreateExcel (List <CompanyUserInfoViewModel> list, HttpContextBase context)
{
IWorkbook workbook = new HSSFWorkbook (); // create a Workbook object
ISheet sheet = workbook. CreateSheet ("Sheet1"); // create a worksheet
# 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 = verticalignment. 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 generate the title line
// Title
String [] arrStr = {"no.", "Contact", "Mobile Phone", "status "};
Int [] arrWidth = {12, 30, 24, 20 };
IRow row = sheet. CreateRow (0); // The title row in the worksheet.
For (int I = 0; I <arrStr. Length; I ++)
{
Sheet. SetColumnWidth (I, arrWidth [I] * 256); // The column width.
ICell cell = row. CreateCell (I );
Cell. SetCellValue (arrStr [I]);
Cell. CellStyle = TitleStyle;
}
# Endregion
Int currentRow = 0;
// Generate data rows
Foreach (var item in list)
{
CreateRow (sheet, item, ref currentRow, CellStyle );
}
# Region output file
String sFileName = "file name ";
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 set the file name
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));
}