private MemoryStream GetReplyWithApply(int ID, int groupID)
{
MemoryStream sm;
ApplyExcelReplyDataModel model = new ApplyExcelReplyDataModel();
using (var module = new ApplyMaintainModule())
{
model.ActivityID = ID;
var result = module.GetApplyDetailsForExcel(model);
if (groupID > 0)
{
result.ApplyListData = result.ApplyListData.Where(o => o.GroupID == groupID).ToList();
}
model.GetExcelFormType = Models.Manage.ExcelForm.EmptyForm1;
sm = FileRepo.ObjectToExcel <ApplyExcelReplyDataModel>(model);
}
return(sm);
}
public ApplyExcelReplyDataModel GetApplyDetailsForExcel(ApplyExcelReplyDataModel details)
{
details.GroupList = GetActivityGroupListByID(details.ActivityID);
var activityData = DB.OLACT.Where(o => o.ID == details.ActivityID).FirstOrDefault();
if (activityData == null)
{
throw new Exception("無法取得該活動賽事,是否已被移除？");
}
details.ActivityID = activityData.ID;
details.ActivityName = activityData.ACTITLE;
var applyLstData =
DB.APPLY
.Where(w => w.MAP_ACT_ID == details.ActivityID)
.AsEnumerable()
.Select(s => new ApplyListDetailsData()
{
ID = s.ID,
ApplyDate = s.BUD_DT.ConvertDateTimeTo10CodeString(),
ApplyNumber = s.APPLY_IDEN_NUM,
ApplySuccessStatus = s.APPLY_SUCCESS,
ApplyTeamName = s.TEAM_NM,
ApplyTeamMemberCount = DB.APPLY_MEMBER.Where(o => o.MAP_APPLY_ID == s.ID && o.REF_ACT_ID == details.ActivityID).Count(),
GroupID = s.MAP_ACT_GUP_ID,
ContactPhone = s.CONTACT_PHONE,
ContactEmail = s.EMAIL,
Remark = s.REMRK.ReplaceEmpty(),
Coach = s.TEAM_COACH,
Contact = s.CONTACT,
MemberInfo = GetMemberInfoByApplyID(details.ActivityID, s.ID)
})
.ToList();
details.ApplyListData = applyLstData;
foreach (var d in details.ApplyListData)
{
PublicMethodRepository.HtmlDecode(d);
}
return(details);
}
public MemoryStream ObjectToExcel <TModel>(TModel obj) where TModel : ExcelReplyBase
{
Type type = obj.GetType();
MemoryStream fs = new MemoryStream();
var typeProcessorMap = new Dictionary <Type, Delegate>
{
{ typeof(ApplyExcelReplyDataModel), new Action <ExcelReplyBase>(m => {
/* Excel Bind*/
ApplyExcelReplyDataModel model = (m as ApplyExcelReplyDataModel);
try
{
string folderPath = "~/Content/ExcelTemp/";
string TemplateFile = string.Empty;
int rangeDefualtLength = 0;
switch (model.GetExcelFormType)
{
case ExcelForm.EmptyForm1:
TemplateFile = HttpContext.Current.Server.MapPath(folderPath + "Apply.xlsx");
rangeDefualtLength = 17;
break;
default:
throw new Exception("can't get method with excel base file.");
}
XLWorkbook exl = new XLWorkbook(TemplateFile);
IXLWorksheet wsht = exl.Worksheet(1);
wsht.RangeUsed().SetAutoFilter();
//var defualtRowHeight = wsht.Row(2).Height;
var defualtRowHeight = 80;
var sexCell = wsht.Cell("G2").Value;
int satartRow = 3;
/*
* 報名編號 隊名 參賽組別 教練 聯絡人 聯絡電話 E-mail 其他 隊長 隊員
*/
string docTitle = "比賽報名明細表";
wsht.Cell(1, 1).Value = string.Concat(model.ActivityName, docTitle);
wsht.Cell(2, 2).Value = model.ActivityName;
if (model.GetExcelFormType == ExcelForm.EmptyForm1)
{
foreach (var data in model.ApplyListData)
{
wsht.Cell(++satartRow, 1).Value = data.ApplyNumber;
wsht.Cell(satartRow, 2).Value = data.ApplyTeamName;
wsht.Cell(satartRow, 3).Value = model.GroupList.Where(o => o.Key == data.GroupID).First().Value;
wsht.Cell(satartRow, 4).Value = data.Coach;
wsht.Cell(satartRow, 5).Value = data.Contact;
wsht.Cell(satartRow, 6).Value = data.ContactPhone;
wsht.Cell(satartRow, 7).Value = data.ContactEmail;
wsht.Cell(satartRow, 8).Value = data.Remark;
int mbIndex = 10;
foreach (var mb in data.MemberInfo)
{
string memberInfo = string.Format("{0}\r\n{1}\r\n{2}", mb.MemberName, mb.MemberBirthday, mb.MemberIdentityID);
if (mb.MemberType == "Leader")
{
wsht.Cell(satartRow, 9).Value = memberInfo;
}
else
{
wsht.Cell(satartRow, mbIndex).Value = memberInfo;
mbIndex++;
}
}
wsht.Row(satartRow).Height = defualtRowHeight;
}
}
wsht.RangeUsed().SetAutoFilter(); //Add autofilter
wsht.Columns().AdjustToContents();
//wsht.FirstRowUsed().Style.Fill.BackgroundColor = XLColor.PowderBlue;
//wsht.FirstRowUsed().Style.Font.Bold = true;
satartRow = wsht.RowsUsed().Count();
IXLRange rangeFirst = wsht.Range(1, 1, 1, rangeDefualtLength);
rangeFirst.Style.Fill.BackgroundColor = XLColor.PowderBlue;
rangeFirst.Style.Font.Bold = true;
rangeFirst.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
IXLRange range = wsht.Range(4, 1, satartRow, rangeDefualtLength);
range.Style.Font.FontSize = 12;
//水平置中
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
//垂直置中
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;;
range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
range.Style.Alignment.WrapText = true;
range.Style.Font.SetFontName("細明體");
exl.SaveAs(fs);
fs.Position = 0;
fs.Close();
fs.Dispose();
wsht.Dispose();
exl.Dispose();
}
catch (Exception ex)
{
model.IsSuccess = false;
model.Message = ex.Message;
}
model.IsSuccess = true;
}) },
};
typeProcessorMap[type].DynamicInvoke(obj);
if (!obj.IsSuccess)
{
throw new Exception(obj.Message);
}
return(fs);
}
