Exemple #1
0
        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"));
        }