public ActionResult DownloadStudentSubmittedProposalsSpreadsheet()
{
int courseId = 1;
uint i = 2;
string cellReference = "A2";
string fileName = "StudentProposalsSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx";
List <string> headerRow = new List <string>();
MemoryStream memoryStream = new MemoryStream();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
List <DocumentFormat.OpenXml.Spreadsheet.Font> fonts = new List <DocumentFormat.OpenXml.Spreadsheet.Font>
{
new DocumentFormat.OpenXml.Spreadsheet.Font(new Bold()),
new DocumentFormat.OpenXml.Spreadsheet.Font()
};
List <ForegroundColor> foregroundColors = new List <ForegroundColor>();
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFD633")
});
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFFFF")
});
List <BackgroundColor> backgroundColors = new List <BackgroundColor>();
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
List <DocumentFormat.OpenXml.Spreadsheet.Alignment> alignments = new List <DocumentFormat.OpenXml.Spreadsheet.Alignment>();
alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment());
alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment {
WrapText = true, Vertical = VerticalAlignmentValues.Top
});
SpreadsheetDocument spreadsheetDocument;
Stylesheet stylesheet;
stylesheet = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments);
spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream);
ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet);
List <List <string> > spreadsheetData = _projectctx.GetSelfInitiatedProjectsSpreadsheetData(courseId);
headerRow.AddRange(new string[] { "S/N", "Project Background", "Project Title", "Project Brief", "Technology Specification" });
sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1));
foreach (List <string> l in spreadsheetData)
{
sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2));
++i;
cellReference = "A" + i;
}
ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData);
memoryStream.Seek(0, SeekOrigin.Begin);
Response.Headers.Add("Content-Disposition", "attachement; filename=" + fileName);
Response.Cookies.Append("completedDownloadToken", "downloaded", new CookieOptions()
{
Expires = DateTime.Now.AddSeconds(8)
});
return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
public ActionResult DownloadProjectSelectionSpreadsheet()
{
int courseId = 1;
uint i = 2;
string cellReference = "A2";
string fileName = "ProjectSelectionSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx";
List <string> headerRow = new List <string>();
IEnumerable <Project> allAvailableProjects = new List <Project>();
MemoryStream memoryStream = new MemoryStream();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
List <Font> fonts = new List <Font>();
fonts.Add(new Font(new Bold()));
fonts.Add(new Font());
List <ForegroundColor> foregroundColors = new List <ForegroundColor>();
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFD633")
});
foregroundColors.Add(new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFFFF")
});
List <BackgroundColor> backgroundColors = new List <BackgroundColor>();
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
backgroundColors.Add(new BackgroundColor {
Indexed = 64
});
List <Alignment> alignments = new List <Alignment>();
alignments.Add(new Alignment());
alignments.Add(new Alignment());
SpreadsheetDocument spreadsheetDocument;
Stylesheet stylesheet;
stylesheet = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments);
spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream);
ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet);
List <List <string> > spreadsheetData = groupRepository.GetGroupProjectSelectionSpreadsheetData(courseId);
allAvailableProjects = projectRepository.GetAvailableProjects(courseId);
headerRow.AddRange(new string[] { "Group#", "Student ID", "Student Name", "Mobile", "Personal Email" });
foreach (Project p in allAvailableProjects)
{
headerRow.Add(p.project_title);
}
sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1));
foreach (List <string> l in spreadsheetData)
{
sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2));
++i;
cellReference = "A" + i;
}
ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData);
memoryStream.Seek(0, SeekOrigin.Begin);
Response.Headers.Add("Content-Disposition", "attachment; filename=" + fileName);
CookieOptions options = new CookieOptions();
options.Expires = DateTime.Now.AddMilliseconds(8000);
Response.Cookies.Append("completedDownloadToken", "downloaded", options);
return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}