public FileStreamResult Get(UnivGraduationModel Model) { var memoryStream = new MemoryStream(); using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { //Excel var workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); //Excel 分頁(Sheet) var sheets = workbookPart.Workbook.AppendChild(new Sheets()); sheets.Append(new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "UnivGraduation", }); // var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); //Get資料 sheetData = GetUnivGraduation(sheetData, Model.Year, Model.Country); } //Return memoryStream.Seek(0, SeekOrigin.Begin); return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); }
public List <UnivGraduationModel> ShowGrad(string yearStart, string yearEnd, string countryNo, string countryDeptNo) { // admin check if (getUserStatusNo() != "1") { return(null); } string condition = ""; if (countryDeptNo != "*") { condition = string.Format("where countryDept.CountryDeptNo = {0}", SqlVal2(countryDeptNo)); } else { condition = string.Format("where CountryNo = {0} ", SqlVal2(countryNo)); } if (yearStart != "*") { condition += string.Format("and GraduationYear >= {0} and GraduationYear <= {1}", SqlVal2(yearStart), SqlVal2(yearEnd)); } List <UnivGraduationModel> graduationData = new List <UnivGraduationModel>(); var sqlStr = string.Format( "SELECT [GraduationYear], [DeptName], [GraduationNumber], countryDept.[CountryDeptNo]" + "FROM[dbo].[Graduation] as grad " + "inner join[dbo].[CountryDepartment] as countryDept " + "on grad.CountryDeptNo = countryDept.CountryDeptNo " + "inner join[dbo].[Department] as dept " + "on countryDept.DeptNo = dept.DeptNo " + "{0} order by GraduationYear", condition); var data = _DB_GetData(sqlStr); foreach (DataRow row in data.Rows) { UnivGraduationModel model = new UnivGraduationModel(); model.Year = row.ItemArray.GetValue(0).ToString(); model.DeptName = row.ItemArray.GetValue(1).ToString(); model.GraduationNumber = row.ItemArray.GetValue(2).ToString(); model.CountryDeptNo = row.ItemArray.GetValue(3).ToString(); graduationData.Add(model); } return(graduationData); }