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