public ActionResult ExcelExport(int userid)
{
var model = _dao.DataExport(userid);
var currentYear = DateTime.Now.Year;
var currentWeek = DateTime.Now.GetIso8601WeekOfYear();
var currentMonth = DateTime.Now.Month;
var currentQuarter = DateTime.Now.GetQuarter();
var now = DateTime.Now;
var end = now.GetEndOfQuarter();
var tt = end.Subtract(now).Days;
//var targetValue = "";
DataTable Dt = new DataTable();
Dt.Columns.Add("KPILevel Code", typeof(string));
Dt.Columns.Add("KPI Name", typeof(string));
Dt.Columns.Add("Actual Value", typeof(string));
Dt.Columns.Add("Target Value", typeof(object));
Dt.Columns.Add("Period Value", typeof(string));
Dt.Columns.Add("Year", typeof(int));
Dt.Columns.Add("OC", typeof(string));
Dt.Columns.Add("Update Time", typeof(object));
Dt.Columns.Add("Start Date", typeof(string));
Dt.Columns.Add("End Date", typeof(string));
foreach (var item in model)
{
var oc = new LevelDAO().GetNode(item.KPILevelCode);
// Logic export tuần
if (item.StateW == true)
{
for (int i = 1; i <= currentWeek; i++)
{
var startDayOfWeek = CodeUtility.ToGetMondayOfWeek(currentYear, i).ToString("MM/dd/yyyy");
var endDayOfWeek = CodeUtility.ToGetSaturdayOfWeek(currentYear, i).ToString("MM/dd/yyyy");
var updateTimeW = item.UploadTimeW.ConvertNumberDayOfWeekToString() + ", Week " + i;
var target = _dao.GetTargetData(item.KPILevelCode, "W", i);
var value = _dao.GetValueData(item.KPILevelCode, "W", i);
Dt.Rows.Add(item.KPILevelCode + "W", item.KPIName, value, target, i, currentYear, oc, updateTimeW, startDayOfWeek, endDayOfWeek);
}
}
///Logic export tháng
if (item.StateM == true)
{
var updateTimeM = item.UploadTimeM.ToStringDateTime("MM/dd/yyyy");
for (int i = 1; i <= currentMonth; i++)
{
var startDayOfMonth = CodeUtility.ToGetStartDateOfMonth(currentYear, i).ToString("MM/dd/yyyy");
var endDayOfMonth = CodeUtility.ToGetEndDateOfMonth(currentYear, i).ToString("MM/dd/yyyy");
var value = _dao.GetValueData(item.KPILevelCode, "M", i);
var target = _dao.GetTargetData(item.KPILevelCode, "M", i);
Dt.Rows.Add(item.KPILevelCode + "M", item.KPIName, value, target, i, currentYear, oc, updateTimeM, startDayOfMonth, endDayOfMonth);
}
}
///Logic export quý
if (item.StateQ == true)
{
var updateTimeQ = item.UploadTimeQ.ToStringDateTime("MM/dd/yyyy");
for (int i = 1; i <= currentQuarter; i++)
{
var seq = CodeUtility.ToGetStartAndEndDateOfQuarter(currentYear, i);
var value = _dao.GetValueData(item.KPILevelCode, "Q", i);
var target = _dao.GetTargetData(item.KPILevelCode, "Q", i);
Dt.Rows.Add(item.KPILevelCode + "Q", item.KPIName, value, target, i, currentYear, oc, updateTimeQ, seq.start.ToString("MM/dd/yyyy"), seq.end.ToString("MM/dd/yyyy"));
}
}
///Logic export năm
if (item.StateY == true)
{
var updateTimeY = item.UploadTimeY.ToStringDateTime("MM/dd/yyyy");
var sey = CodeUtility.ToGetStartAndEndDateOfYear(currentYear);
for (int i = currentYear - 9; i <= currentYear; i++)
{
var value = _dao.GetValueData(item.KPILevelCode, "Y", currentYear);
var target = _dao.GetTargetData(item.KPILevelCode, "Y", i);
Dt.Rows.Add(item.KPILevelCode + "Y", item.KPIName, value, target, i, currentYear, oc, updateTimeY, sey.start.ToString("MM/dd/yyyy"), sey.end.ToString("MM/dd/yyyy"));
}
}
}
var memoryStream = new MemoryStream();
using (var excelPackage = new ExcelPackage(memoryStream))
{
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells["A1"].LoadFromDataTable(Dt, true, TableStyles.None);
worksheet.Cells["A1:AN1"].Style.Font.Bold = true;
worksheet.DefaultRowHeight = 18;
worksheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
worksheet.Column(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Column(7).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.DefaultColWidth = 20;
worksheet.Column(2).AutoFit();
Session["DownloadExcel_FileManager"] = excelPackage.GetAsByteArray();
return(Json("", JsonRequestBehavior.AllowGet));
}
}
public ActionResult ExcelExport(int userid)
{
//string token = Request.Headers["Authorization"].ToSafetyString();
//var userid = Extensions.GetDecodeTokenByProperty(token, "nameid").ToInt();
var model = _dataService.DataExport(userid);
var currentYear = DateTime.Now.Year;
var currentWeek = DateTime.Now.GetIso8601WeekOfYear();
var currentMonth = DateTime.Now.Month;
var currentHalf = DateTime.Now.GetQuarter();
var currentQuarter = DateTime.Now.GetQuarter();
//var now = DateTime.Now;
//var end = now.GetEndOfQuarter();
//var tt = end.Subtract(now).Days;
//var targetValue = "";
DataTable Dt = new DataTable();
Dt.Columns.Add("KPILevel Code", typeof(string));
Dt.Columns.Add("KPI Name", typeof(string));
Dt.Columns.Add("Actual Value", typeof(string));
Dt.Columns.Add("Target Value", typeof(object));
Dt.Columns.Add("Period Value", typeof(string));
Dt.Columns.Add("Year", typeof(int));
Dt.Columns.Add("OC", typeof(string));
Dt.Columns.Add("Update Time", typeof(object));
Dt.Columns.Add("Start Date", typeof(string));
Dt.Columns.Add("End Date", typeof(string));
foreach (var item in model)
{
var oc = _levelService.GetNode(item.KPILevelCode);
// Logic export tuần
if (item.StateW == true)
{
for (int i = 1; i <= currentWeek; i++)
{
var startDayOfWeek = CodeUtility.ToGetMondayOfWeek(currentYear, i).ToString("MM/dd/yyyy");
var endDayOfWeek = CodeUtility.ToGetSaturdayOfWeek(currentYear, i).ToString("MM/dd/yyyy");
var updateTimeW = item.UploadTimeW.ConvertNumberDayOfWeekToString() + ", Week " + i;
var target = _dataService.GetTargetData(item.KPILevelCode, "W", i);
var value = _dataService.GetValueData(item.KPILevelCode, "W", i);
Dt.Rows.Add(item.KPILevelCode + "W", item.KPIName, value, target, i, currentYear, oc, updateTimeW, startDayOfWeek, endDayOfWeek);
}
}
///Logic export tháng
if (item.StateM == true)
{
var updateTimeM = item.UploadTimeM.ToStringDateTime("MM/dd/yyyy");
for (int i = 1; i <= currentMonth; i++)
{
var startDayOfMonth = CodeUtility.ToGetStartDateOfMonth(currentYear, i).ToString("MM/dd/yyyy");
var endDayOfMonth = CodeUtility.ToGetEndDateOfMonth(currentYear, i).ToString("MM/dd/yyyy");
var value = _dataService.GetValueData(item.KPILevelCode, "M", i);
var target = _dataService.GetTargetData(item.KPILevelCode, "M", i);
Dt.Rows.Add(item.KPILevelCode + "M", item.KPIName, value, target, i, currentYear, oc, updateTimeM, startDayOfMonth, endDayOfMonth);
}
}
///Logic export nửa năm
if (item.StateH == true)
{
var updateTimeH = item.UploadTimeH.ToStringDateTime("MM/dd/yyyy");
for (int i = 1; i <= currentHalf; i++)
{
var seq = CodeUtility.ToGetStartAndEndDateOfHalfYear(currentYear, i);
var value = _dataService.GetValueData(item.KPILevelCode, "H", i);
var target = _dataService.GetTargetData(item.KPILevelCode, "H", i);
Dt.Rows.Add(item.KPILevelCode + "H", item.KPIName, value, target, i, currentYear, oc, updateTimeH, seq.Item1.ToString("MM/dd/yyyy"), seq.Item2.ToString("MM/dd/yyyy"));
}
}
///Logic export quý
if (item.StateQ == true)
{
var updateTimeQ = item.UploadTimeQ.ToStringDateTime("MM/dd/yyyy");
for (int i = 1; i <= currentQuarter; i++)
{
var seq = CodeUtility.ToGetStartAndEndDateOfQuarter(currentYear, i);
var value = _dataService.GetValueData(item.KPILevelCode, "Q", i);
var target = _dataService.GetTargetData(item.KPILevelCode, "Q", i);
Dt.Rows.Add(item.KPILevelCode + "Q", item.KPIName, value, target, i, currentYear, oc, updateTimeQ, seq.Item1.ToString("MM/dd/yyyy"), seq.Item2.ToString("MM/dd/yyyy"));
}
}
///Logic export năm
if (item.StateY == true)
{
var updateTimeY = item.UploadTimeY.ToStringDateTime("MM/dd/yyyy");
var sey = CodeUtility.ToGetStartAndEndDateOfYear(currentYear);
for (int i = currentYear - 9; i <= currentYear; i++)
{
var value = _dataService.GetValueData(item.KPILevelCode, "Y", currentYear);
var target = _dataService.GetTargetData(item.KPILevelCode, "Y", i);
Dt.Rows.Add(item.KPILevelCode + "Y", item.KPIName, value, target, i, currentYear, oc, updateTimeY, sey.Item1.ToString("MM/dd/yyyy"), sey.Item2.ToString("MM/dd/yyyy"));
}
}
}
var memoryStream = new MemoryStream();
using var excelPackage = new ExcelPackage(memoryStream);
var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
worksheet.Cells["A1"].LoadFromDataTable(Dt, true, TableStyles.None);
worksheet.Cells["A1:AN1"].Style.Font.Bold = true;
worksheet.DefaultRowHeight = 18;
worksheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
worksheet.Column(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Column(7).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.DefaultColWidth = 20;
worksheet.Column(2).AutoFit();
if (excelPackage == null)
{
return(NotFound());
}
byte[] data = excelPackage.GetAsByteArray() as byte[];
return(File(data, "application/octet-stream", "DataUpload.xlsx"));
}