public ActionResult UsersExcel()
{
UsersRetrieveReq req = null;
RequestParameter para = new RequestParameter();
para.Load(Request);
req = new UsersRetrieveReq();
JsonConvert.PopulateObject(para.Item("json"), req);
var memoryStream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
#region Sheet1
SheetData sheetData = new SheetData();
Row row;
#region header
row = new Row(); //header
row.Append(
new Cell()
{
CellValue = new CellValue("序號")
},
new Cell()
{
CellValue = new CellValue("帳號")
},
new Cell()
{
CellValue = new CellValue("姓名")
},
new Cell()
{
CellValue = new CellValue("密碼")
},
new Cell()
{
CellValue = new CellValue("電子郵件")
},
new Cell()
{
CellValue = new CellValue("狀態")
},
new Cell()
{
CellValue = new CellValue("備註")
},
new Cell()
{
CellValue = new CellValue("建檔時間")
},
new Cell()
{
CellValue = new CellValue("建檔人員")
},
new Cell()
{
CellValue = new CellValue("異動時間")
},
new Cell()
{
CellValue = new CellValue("異動人員")
}
);
sheetData.AppendChild(row);
#endregion
#region data
UsersRetrieveRes res = new KYL_CMS.Models.BusinessLogic.Users("SCC").ReportData(req);
foreach (USERS data in res.USERS) //data
{
row = new Row();
row.Append(
new Cell()
{
CellValue = new CellValue(data.SN.ToString())
},
new Cell()
{
CellValue = new CellValue(data.ID)
},
new Cell()
{
CellValue = new CellValue(data.NAME.ToString())
},
new Cell()
{
CellValue = new CellValue(data.PASSWORD.ToString())
},
new Cell()
{
CellValue = new CellValue(data.EMAIL == null ? "" : data.EMAIL)
},
new Cell()
{
CellValue = new CellValue(data.MODE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MEMO == null ? "" : data.MEMO)
},
new Cell()
{
CellValue = new CellValue(data.CDATE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.CUSER.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MDATE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MUSER.ToString())
}
);
sheetData.AppendChild(row);
}
#endregion
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet; //add a Worksheet to the WorksheetPart
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "工作表1"
});
#endregion
}
memoryStream.Seek(0, SeekOrigin.Begin);
return(File(memoryStream.ToArray(), "application/vnd.ms-excel", "TEST.xlsx"));
}