public ActionResult ExportExcelLocalHelp(int registerEngryId)
{
var registerEngry = db.RegisterEntries.Find(registerEngryId);
string filename = "Local Help" + "-" + registerEngry.Time.ToString("MM-dd-yyyy-HH-mm") + ".xlsx";
var physicalPath = Path.Combine(Server.MapPath("~/Download/"), filename);
FileInfo file = new FileInfo(physicalPath);
if (file.Exists)
{
file.Delete();
file = new FileInfo(physicalPath);
}
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
ExcelWorksheet xlWorkSheet = xlPackage.Workbook.Worksheets.Add("Local Help Registration");
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.NAME).Value = "Name";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.GENDER).Value = "Gender";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.MAJOR).Value = "Major";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.EMAIL).Value = "Email";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.PHONE).Value = "Phone";
int row = 2;
foreach (var item in registerEngry.NewStudents)
{
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.NAME).Value = item.CnName ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.GENDER).Value = SystemGender.ToStringGender(item.Gender);
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.MAJOR).Value = item.Major ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.EMAIL).Value = item.Email ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.PHONE).Value = item.Phone;
xlWorkSheet.Row(row).Style = (row % 2 == 0) ? "Good" : "Bad";
row++;
}
foreach (var item in registerEngry.GuestParticipants)
{
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.NAME).Value = item.Name ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.GENDER).Value = " ";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.MAJOR).Value = " ";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.EMAIL).Value = item.Email ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.PHONE).Value = item.Phone;
xlWorkSheet.Row(row).Style = (row % 2 == 0) ? "Good" : "Bad";
row++;
}
xlWorkSheet.Column((int)LocalHelpRegistration.NAME).Width = 15;
xlWorkSheet.Column((int)LocalHelpRegistration.GENDER).Width = 15;
xlWorkSheet.Column((int)LocalHelpRegistration.MAJOR).Width = 20;
xlWorkSheet.Column((int)LocalHelpRegistration.EMAIL).Width = 25;
xlWorkSheet.Column((int)LocalHelpRegistration.PHONE).Width = 25;
xlPackage.Save();
}
byte[] ByteFile = System.IO.File.ReadAllBytes(physicalPath);
MemoryStream m = new MemoryStream(ByteFile);
return(File(m, "application/vnd.ms-excel", filename));
}
public ActionResult _ExportVolunteerToExcel(List <Volunteer> studList, string filename)
{
var physicalPath = Path.Combine(Server.MapPath("~/Download/"), filename);
FileInfo file = new FileInfo(physicalPath);
if (file.Exists)
{
file.Delete();
file = new FileInfo(physicalPath);
}
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
ExcelWorksheet xlWorkSheet = xlPackage.Workbook.Worksheets.Add("Volunteer List");
xlWorkSheet.Cell(1, (int)ExcelFormat.NAME).Value = "Name";
xlWorkSheet.Cell(1, (int)ExcelFormat.GENDER).Value = "Gender";
xlWorkSheet.Cell(1, (int)ExcelFormat.EMAIL).Value = "Email";
xlWorkSheet.Cell(1, (int)ExcelFormat.PHONE).Value = "Phone";
xlWorkSheet.Cell(1, (int)ExcelFormat.ADDRESS).Value = "Adress";
xlWorkSheet.Cell(1, (int)ExcelFormat.INTRO).Value = "Brief Introduction";
xlWorkSheet.Cell(1, (int)ExcelFormat.COMMENT).Value = "Comments";
xlWorkSheet.Cell(1, (int)ExcelFormat.ORGANIZATION).Value = "Organization";
int row = 2;
foreach (var item in studList)
{
xlWorkSheet.Cell(row, (int)ExcelFormat.NAME).Value = item.Name;
xlWorkSheet.Cell(row, (int)ExcelFormat.GENDER).Value = SystemGender.ToStringGender(item.Gender);
xlWorkSheet.Cell(row, (int)ExcelFormat.EMAIL).Value = item.Email;
xlWorkSheet.Cell(row, (int)ExcelFormat.PHONE).Value = item.Phone ?? "Not Provided";
xlWorkSheet.Cell(row, (int)ExcelFormat.ADDRESS).Value = Server.HtmlEncode(item.Address ?? "Not Provided");
xlWorkSheet.Cell(row, (int)ExcelFormat.INTRO).Value = Server.HtmlEncode(item.BriefIntro ?? "Not Provided");
xlWorkSheet.Cell(row, (int)ExcelFormat.COMMENT).Value = Server.HtmlEncode(item.Note ?? "Not Provided");
xlWorkSheet.Cell(row, (int)ExcelFormat.ORGANIZATION).Value = item.Organization.Name;
row++;
}
xlWorkSheet.Column((int)ExcelFormat.NAME).Width = 15;
xlWorkSheet.Column((int)ExcelFormat.EMAIL).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.PHONE).Width = 20;
xlWorkSheet.Column((int)ExcelFormat.INTRO).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.ADDRESS).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.COMMENT).Width = 25;
xlWorkSheet.Column((int)ExcelFormat.ORGANIZATION).Width = 25;
xlPackage.Save();
}
byte[] ByteFile = System.IO.File.ReadAllBytes(physicalPath);
MemoryStream m = new MemoryStream(ByteFile);
return(File(m, "application/vnd.ms-excel", filename));
}
public ActionResult ExportExcelAppliedStudents(int id)
{
var department = db.FacssDepartments.Find(id);
string filename = department.Name + "报名名单" + ".xlsx";
var physicalPath = Path.Combine(Server.MapPath("~/Download/"), filename);
FileInfo file = new FileInfo(physicalPath);
if (file.Exists)
{
file.Delete();
file = new FileInfo(physicalPath);
}
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
ExcelWorksheet xlWorkSheet = xlPackage.Workbook.Worksheets.Add(department.Name);
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.NAME).Value = "Name";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.GENDER).Value = "Gender";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.MAJOR).Value = "Major";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.EMAIL).Value = "Email";
xlWorkSheet.Cell(1, (int)LocalHelpRegistration.PHONE).Value = "Phone";
int row = 2;
foreach (var item in department.AppliedNewStudents)
{
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.NAME).Value = item.CnName ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.GENDER).Value = SystemGender.ToStringGender(item.Gender);
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.MAJOR).Value = item.Major ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.EMAIL).Value = item.Email ?? "";
xlWorkSheet.Cell(row, (int)LocalHelpRegistration.PHONE).Value = item.Phone;
row++;
}
xlWorkSheet.Column((int)LocalHelpRegistration.NAME).Width = 15;
xlWorkSheet.Column((int)LocalHelpRegistration.GENDER).Width = 15;
xlWorkSheet.Column((int)LocalHelpRegistration.MAJOR).Width = 20;
xlWorkSheet.Column((int)LocalHelpRegistration.EMAIL).Width = 25;
xlWorkSheet.Column((int)LocalHelpRegistration.PHONE).Width = 25;
xlPackage.Save();
}
byte[] ByteFile = System.IO.File.ReadAllBytes(physicalPath);
MemoryStream m = new MemoryStream(ByteFile);
return(File(m, "application/vnd.ms-excel", filename));
}