protected void btnExport_Click(object sender, EventArgs e)
{
lblError.Text = "";
object data = GeneralFunction.GetReportDataCache();
if (data != null)
{
List <Jury> flist = (List <Jury>)data;
XLWorkbook workbook = new XLWorkbook();
MemoryStream memoryStream = new MemoryStream();
int x = 1;
int y = 1;
string sheetName = "Jury Listing";
workbook.Worksheets.Add(sheetName);
x = 1;
#region Basic Entry Headers
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("No."); x++;
// basic details
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Type"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("JudgeId"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Email"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Salutation"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Firstname"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Lastname"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Title"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Company"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Type of Company"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Network"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Holding Company"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Photo"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Bio Attachment"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Jury Update By Judge"); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue("Jury Update By Admin"); x++;
#endregion
y++;
foreach (Jury jury in flist)
{
x = 1;
#region Basic Jury DataRows
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue((y - 1).ToString()); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.Type); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.SerialNo); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.Email); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.Salutation); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.FirstName); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.LastName); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.Designation); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.Company); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.CompanyType); x++;
string network = jury.Network;
if (jury.NetworkOthers != "")
{
network += " - " + jury.NetworkOthers;
}
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(network); x++;
string holdingcompany = jury.HoldingCompany;
if (jury.HoldingCompanyOthers != "")
{
holdingcompany += " - " + jury.HoldingCompanyOthers;
}
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(holdingcompany); x++;
string photo = ConfigurationManager.AppSettings["storagePhysicalPath"] + "JuryPhoto\\" + jury.Id.ToString() + ".jpg";
if (File.Exists(photo))
{
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).Value = jury.Id.ToString();
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).Hyperlink = new XLHyperlink(ConfigurationManager.AppSettings["storagePhysicalPath"].ToString() + "JuryPhoto\\" + jury.Id.ToString() + ".jpg"); x++;
}
else
{
x++;
}
string profile = ConfigurationManager.AppSettings["storagePhysicalPath"] + "JuryProfile\\" + jury.SerialNo.ToString() + ".doc";
string profile1 = ConfigurationManager.AppSettings["storagePhysicalPath"] + "JuryProfile\\" + jury.SerialNo.ToString() + ".docx";
if (File.Exists(profile))
{
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).Value = jury.SerialNo.ToString();
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).Hyperlink = new XLHyperlink(ConfigurationManager.AppSettings["storagePhysicalPath"].ToString() + "JuryProfile\\" + jury.SerialNo.ToString() + ".doc"); x++;
}
else if (File.Exists(profile1))
{
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).Value = jury.SerialNo.ToString();
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).Hyperlink = new XLHyperlink(ConfigurationManager.AppSettings["storagePhysicalPath"].ToString() + "JuryProfile\\" + jury.SerialNo.ToString() + ".docx"); x++;
}
else
{
x++;
}
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.DateJuryModified.Equals(DateTime.MaxValue) ? string.Empty : jury.DateJuryModified.ToString("dd/MM/yy")); x++;
workbook.Worksheets.Worksheet(sheetName).Cell(y, x).SetValue(jury.DateModified.Equals(DateTime.MaxValue) ? string.Empty : jury.DateModified.ToString("dd/MM/yy")); x++;
#endregion
y++;
}
GeneralFunction.StyleReport(workbook).SaveAs(memoryStream);
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=Effie_Jury_Master.xlsx");
memoryStream.WriteTo(Response.OutputStream);
Response.End();
}
}
public dynamic GetList(string _search, long nd, int rows, int?page, string sidx, string sord, int id, string filters = "")
{
// Construct where statement
string strWhere = GeneralFunction.ConstructWhere(filters);
string filter = null;
GeneralFunction.ConstructWhereInLinq(strWhere, out filter);
if (filter == "")
{
filter = "true";
}
// Get Data
var q = _employeeWorkingTimeService.GetQueryable().Where(x => x.WorkingTimeId == id).Include("WorkingTime").Include("Employee").Include("TitleInfo").Include("Division");
var query = (from model in q
select new
{
model.Id,
model.WorkingTimeId,
model.EmployeeId,
EmployeeNIK = model.Employee.NIK,
EmployeeName = model.Employee.Name,
Title = model.Employee.TitleInfo.Name,
Division = model.Employee.Division.Name,
//model.IsShiftable,
//model.IsEnabled,
model.StartDate,
model.EndDate,
model.CreatedAt,
model.UpdatedAt,
}).Where(filter).OrderBy(sidx + " " + sord); //.ToList();
var list = query.AsEnumerable();
var pageIndex = Convert.ToInt32(page) - 1;
var pageSize = rows;
var totalRecords = query.Count();
var totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
// default last page
if (totalPages > 0)
{
if (!page.HasValue)
{
pageIndex = totalPages - 1;
page = totalPages;
}
}
list = list.Skip(pageIndex * pageSize).Take(pageSize);
return(Json(new
{
total = totalPages,
page = page,
records = totalRecords,
rows = (
from model in list
select new
{
id = model.Id,
cell = new object[] {
model.Id,
model.WorkingTimeId,
model.EmployeeId,
model.EmployeeNIK,
model.EmployeeName,
model.Title,
model.Division,
model.StartDate,
model.EndDate,
model.CreatedAt,
model.UpdatedAt,
}
}).ToArray()
}, JsonRequestBehavior.AllowGet));
}
