Ejemplo n.º 1
0
        public FileResult DownloadTemplate(DownloadTemplateViewModel vModel)
        {
            ConfigType config = string.IsNullOrEmpty(vModel.ConfigType) ? ConfigType.KpiTarget
                                    : (ConfigType)Enum.Parse(typeof(ConfigType), vModel.ConfigType);

            #region Get Data
            PeriodeType pType = string.IsNullOrEmpty(vModel.PeriodeType) ? PeriodeType.Yearly
                            : (PeriodeType)Enum.Parse(typeof(PeriodeType), vModel.PeriodeType);

            var viewModel = new ConfigurationViewModel();
            switch (config)
            {
                case ConfigType.KpiTarget:
                    {
                        var request = new GetKpiTargetsConfigurationRequest()
                        {
                            PeriodeType = vModel.PeriodeType,
                            Year = vModel.Year,
                            Month = vModel.Month,
                            RoleGroupId = vModel.RoleGroupId
                        };
                        var target = _kpiTargetService.GetKpiTargetsConfiguration(request);
                        viewModel = target.MapTo<ConfigurationViewModel>();
                        break;
                    }

                case ConfigType.KpiAchievement:
                    {
                        var request = new GetKpiAchievementsConfigurationRequest()
                        {
                            PeriodeType = vModel.PeriodeType,
                            Year = vModel.Year,
                            Month = vModel.Month,
                            RoleGroupId = vModel.RoleGroupId
                        };
                        var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);
                        viewModel = achievement.MapTo<ConfigurationViewModel>();
                        break;
                    }

                case ConfigType.OperationData:
                    {
                        var request = vModel.MapTo<GetOperationDataConfigurationRequest>();
                        request.PeriodeType = pType;
                        request.IsPartial = false;
                        var operationData = _operationDataService.GetOperationDataConfiguration(request);
                        viewModel = operationData.MapTo<ConfigurationViewModel>();
                        //return new FileContentResult(null, "application/octet-stream") { FileDownloadName = "as" };
                        break;
                    }

                default:
                    break;
            }
            #endregion

            /*
             * Find and Create Directory
             */
            var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, vModel.ConfigType));
            if (!Directory.Exists(resultPath))
            {
                Directory.CreateDirectory(resultPath);
            }

            #region parsing data to excel
            string dateFormat = string.Empty;
            string workSheetName = new StringBuilder(vModel.PeriodeType).ToString();
            switch (vModel.PeriodeType)
            {
                case "Yearly":
                    dateFormat = "yyyy";
                    break;
                case "Monthly":
                    dateFormat = "mmm-yy";
                    workSheetName = string.Format("{0}_{1}", workSheetName, vModel.Year);
                    break;
                default:
                    dateFormat = "dd-mmm-yy";
                    workSheetName = string.Format("{0}_{1}-{2}", workSheetName, vModel.Year, vModel.Month.ToString().PadLeft(2, '0'));
                    break;
            }

            string fileName = string.Format(@"{0}_{1}_{2}.xlsx", vModel.ConfigType, vModel.PeriodeType, DateTime.Now.ToString("yyyymmddMMss"));

            IWorkbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];

            worksheet.Name = workSheetName;
            workbook.Worksheets.ActiveWorksheet = worksheet;
            RowCollection rows = workbook.Worksheets[0].Rows;
            ColumnCollection columns = workbook.Worksheets[0].Columns;

            Row headerRow = rows[0];
            headerRow.FillColor = Color.DarkGray;
            headerRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            headerRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            Column kpiIdColumn = columns[0];
            Column kpiNameColumn = columns[1];
            kpiIdColumn.Visible = false;

            headerRow.Worksheet.Cells[headerRow.Index, kpiIdColumn.Index].Value = "KPI ID";
            headerRow.Worksheet.Cells[headerRow.Index, kpiNameColumn.Index].Value = "KPI Name";
            int i = 1; //i for row
            #region inserting from models
            foreach (var kpi in viewModel.Kpis)
            {
                worksheet.Cells[i, kpiIdColumn.Index].Value = kpi.Id;
                worksheet.Cells[i, kpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
                int j = 2; // for column
                var items = new List<ConfigurationViewModel.Item>();
                switch (vModel.ConfigType)
                {
                    case "KpiTarget":
                        {
                            foreach (var target in kpi.KpiTargets)
                            {
                                var item = new ConfigurationViewModel.Item
                                {
                                    Id = target.Id,
                                    KpiId = kpi.Id,
                                    Periode = target.Periode,
                                    Remark = target.Remark,
                                    Value = target.Value.HasValue ? target.Value.ToString() : string.Empty,
                                    PeriodeType = pType
                                };
                                items.Add(item);
                            }
                            break;
                        }

                    case "KpiAchievement":
                        {
                            foreach (var achievement in kpi.KpiAchievements)
                            {
                                var item = new ConfigurationViewModel.Item()
                                {
                                    Id = achievement.Id,
                                    KpiId = kpi.Id,
                                    Periode = achievement.Periode,
                                    Remark = achievement.Remark,
                                    Value = achievement.Value.HasValue ? achievement.Value.ToString() : string.Empty,
                                    PeriodeType = pType
                                };
                                items.Add(item);
                            }
                            break;
                        }

                    case "OperationData":
                        {
                            //items = kpi.OperationData.MapTo<ConfigurationViewModel.Item>();
                            foreach (var operationData in kpi.OperationData)
                            {
                                var item = new ConfigurationViewModel.Item()
                                {
                                    Id = operationData.Id,
                                    KpiId = kpi.Id,
                                    Periode = operationData.Periode,
                                    Remark = operationData.Remark,
                                    Value = operationData.Value.HasValue ? operationData.Value.ToString() : string.Empty,
                                    PeriodeType = pType
                                };
                                items.Add(item);
                            }
                            break;
                        }
                }

                foreach (var item in items)
                {
                    worksheet.Cells[headerRow.Index, j].Value = item.Periode;
                    worksheet.Cells[headerRow.Index, j].NumberFormat = dateFormat;
                    worksheet.Cells[headerRow.Index, j].AutoFitColumns();

                    worksheet.Cells[i, j].Value = item.RealValue;
                    worksheet.Cells[i, j].NumberFormat = "#,0.#0";
                    worksheet.Columns[j].AutoFitColumns();
                    j++;
                }

                Column totalValueColumn = worksheet.Columns[j];
                if (i == headerRow.Index + 1)
                {
                    worksheet.Cells[headerRow.Index, totalValueColumn.Index].Value = "Average";
                    worksheet.Cells[headerRow.Index, totalValueColumn.Index + 1].Value = "SUM";
                    Range r1 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
                }
                else
                {
                    // add formula
                    Range r2 = worksheet.Range.FromLTRB(kpiNameColumn.Index + 1, i, j - 1, i);
                    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
                    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
                }
                i++;
            }
            #endregion
            kpiNameColumn.AutoFitColumns();
            worksheet.FreezePanes(headerRow.Index, kpiNameColumn.Index);

            string resultFilePath = string.Format("{0},{1}", resultPath, fileName);
            //System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
            //System.Web.HttpContext.Current.Response.Clear();
            //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));

            using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.SaveDocument(stream, DevExpress.Spreadsheet.DocumentFormat.Xlsx);
                stream.Close();
            }
            //System.Web.HttpContext.Current.Response.End();
            //workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
            //workbook.Dispose();
            #endregion

            string namafile = Path.GetFileName(resultFilePath);
            byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
            var response = new FileContentResult(fileBytes, "application/octet-stream") { FileDownloadName = fileName };
            return response;
        }
Ejemplo n.º 2
0
        private BaseResponse _ReadExcelFile(string configType, string filename)
        {
            var response = new BaseResponse();
            string periodType = string.Empty;
            PeriodeType pType = PeriodeType.Yearly;
            int tahun = DateTime.Now.Year, bulan = DateTime.Now.Month;
            List<ConfigurationViewModel.Item> list_data = new List<ConfigurationViewModel.Item>();
            if (filename != Path.GetFullPath(filename))
            {
                filename = Server.MapPath(filename);
            }
            /*
             * cek file exist and return immediatelly if not exist
             */
            if (!System.IO.File.Exists(filename))
            {
                response.IsSuccess = false;
                response.Message = "File Not Found";
                return response;
            }
            Workbook workbook = new Workbook();
            using (FileStream stream = new FileStream(filename, FileMode.Open))
            {
                workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
                #region foreach
                foreach (var worksheet in workbook.Worksheets)
                {
                    string[] name = worksheet.Name.Split('_');
                    if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
                    {
                        periodType = name[0];
                        pType = string.IsNullOrEmpty(periodType)
                            ? PeriodeType.Yearly
                            : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodType);
                        string period = name[name.Count() - 1];
                        string[] periodes = null;
                        //validate and switch value by periodType
                        if (periodType != period && !string.IsNullOrEmpty(period))
                        {
                            switch (periodType)
                            {
                                case "Daily":
                                    periodes = period.Split('-');
                                    tahun = int.Parse(periodes[0]);
                                    bulan = int.Parse(periodes[periodes.Count() - 1]);
                                    break;
                                case "Monthly":
                                    tahun = int.Parse(period);
                                    break;
                                case "Yearly":
                                default:
                                    break;
                            }
                        }

                        workbook.Worksheets.ActiveWorksheet = worksheet;
                        //get row

                        Range range = worksheet.GetUsedRange();
                        int rows = range.RowCount;
                        int column = range.ColumnCount - 2;
                        int Kpi_Id = 0;
                        DateTime periodData = new DateTime();
                        double? nilai = null;

                        //get rows
                        for (int i = 1; i < rows; i++)
                        {
                            for (int j = 0; j < column; j++)
                            {
                                bool fromExistedToNull = false;
                                if (j == 0)
                                {
                                    if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
                                    {
                                        Kpi_Id = int.Parse(worksheet.Cells[i, j].Value.ToString());
                                    }
                                }
                                else if (j > 1)
                                {
                                    if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
                                    {
                                        periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());
                                        //}
                                        if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
                                        {
                                            nilai = double.Parse(worksheet.Cells[i, j].Value.ToString());
                                        }
                                        else if (worksheet.Cells[i, j].Value.Type == CellValueType.Text)
                                        {
                                            fromExistedToNull = true;
                                            nilai = null;
                                        }
                                        else
                                        {
                                            nilai = null;
                                        }

                                        if (nilai != null || fromExistedToNull)
                                        {
                                            // try to cacth and update
                                            var data = new ConfigurationViewModel.Item() { Value = nilai, KpiId = Kpi_Id, Periode = periodData, PeriodeType = pType };
                                            list_data.Add(data);
                                            //switch (configType)
                                            //{
                                            //    case "KpiTarget":
                                            //        response = this._UpdateKpiTarget(data);
                                            //        break;
                                            //    case "KpiAchievement":
                                            //        response = this._UpdateKpiAchievement(data);
                                            //        break;
                                            //    case "Economic":
                                            //        response = this._UpdateEconomic(data);
                                            //        break;
                                            //    default:
                                            //        response.IsSuccess = false;
                                            //        response.Message = "No Table Selected";
                                            //        break;
                                            //}
                                        }
                                    }
                                }
                            }
                        }
                    }
                    else
                    {
                        response.IsSuccess = false;
                        response.Message = "File Not Valid";
                        break;
                    }
                    switch (configType)
                    {
                        case "KpiTarget":
                            response = this._UpdateKpiTarget(list_data);
                            break;
                        case "KpiAchievement":
                            response = this._UpdateKpiAchievement(list_data, pType.ToString(), tahun, bulan);
                            break;
                        case "Economic":
                            response = this._UpdateEconomic(list_data);
                            break;
                        default:
                            response.IsSuccess = false;
                            response.Message = "No Table Selected";
                            break;
                    }
                }
                #endregion
            }

            //here to read excel fileController
            return response;
        }
Ejemplo n.º 3
0
        private BaseResponse ReadExcelFile(ProcessFileViewModel viewModel)
        {
            var response = new BaseResponse();

            try
            {
                int year = DateTime.Now.Year;
                int month = DateTime.Now.Month;
                var listData = new List<ConfigurationViewModel.Item>();

                if (viewModel.Filename != Path.GetFullPath(viewModel.Filename))
                {
                    viewModel.Filename = Server.MapPath(viewModel.Filename);
                }

                if (!System.IO.File.Exists(viewModel.Filename))
                {
                    response.IsSuccess = false;
                    response.Message = "File Not Found";
                    return response;
                }

                Workbook workbook = new Workbook();
                using (FileStream stream = new FileStream(viewModel.Filename, FileMode.Open))
                {
                    workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
                    #region foreach
                    foreach (var worksheet in workbook.Worksheets)
                    {
                        string[] name = worksheet.Name.Split('_');
                        PeriodeType pType;
                        if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
                        {
                            string periodeType = name[0];
                            pType = string.IsNullOrEmpty(periodeType) ? PeriodeType.Yearly : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodeType);
                            string period = name[name.Count() - 1];
                            string[] periodes = null;

                            if (periodeType != period && !string.IsNullOrEmpty(period))
                            {
                                switch (periodeType)
                                {
                                    case "Yearly":
                                        break;
                                    case "Monthly":
                                        year = int.Parse(period);
                                        break;
                                    case "Daily":
                                        periodes = period.Split('-');
                                        year = int.Parse(periodes[0]);
                                        month = int.Parse(periodes[periodes.Count() - 1]);
                                        break;
                                }
                            }

                            workbook.Worksheets.ActiveWorksheet = worksheet;

                            Range range = worksheet.GetUsedRange();
                            int rows = range.RowCount;
                            int column = range.ColumnCount - 2;
                            int kpiId = 0;
                            var kpiListWithOperationConfigId = new List<int>();
                            var operationIds = new GetOperationsInResponse();

                            #region get kpi list with key operation config
                            if (viewModel.ConfigType.ToLowerInvariant().Equals("operationdata"))
                            {
                                for (int i = 1; i < rows; i++)
                                {
                                    for (int j = 0; j < column; j++)
                                    {
                                        if (j == 0)
                                        {
                                            if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
                                            {
                                                kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
                                                kpiListWithOperationConfigId.Add(kpiId);
                                            }
                                        }
                                    }
                                }
                                operationIds = _operationConfigService.GetOperationIn(new GetOperationsInRequest { KpiIds = kpiListWithOperationConfigId });
                            }
                            #endregion

                            for (int i = 1; i < rows; i++)
                            {
                                bool isAuthorizedKPI = false;
                                for (int j = 0; j < column; j++)
                                {
                                    if (j == 0)
                                    {
                                        if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
                                        {
                                            kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
                                            //this will validate authorized KPI based on Role
                                            isAuthorizedKPI = ValidateAuthorizeKPI(kpiId);
                                            if (!isAuthorizedKPI)
                                            {
                                                break;
                                            }
                                        }
                                    }
                                    else if (j > 1)
                                    {
                                        if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
                                        {
                                            DateTime periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());

                                            if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric ||
                                                 worksheet.Cells[i, j].Value.Type == CellValueType.Text)
                                            {
                                                string value = worksheet.Cells[i, j].Value.ToString();
                                                int operationId = 0;
                                                if (viewModel.ConfigType.ToLowerInvariant().Equals(ConfigType.OperationData.ToString().ToLowerInvariant()))
                                                {

                                                    var operation = operationIds.KeyOperations.FirstOrDefault(x => x.KpiId == kpiId);
                                                    if (operation != null)
                                                    {
                                                        operationId = operation.Id;
                                                    }
                                                }

                                                if (!string.IsNullOrEmpty(value))
                                                {
                                                    var data = new ConfigurationViewModel.Item
                                                    {
                                                        Value = value,
                                                        KpiId = kpiId,
                                                        Periode = periodData,
                                                        PeriodeType = pType,
                                                        OperationId = operationId,
                                                        ScenarioId = viewModel.ScenarioId
                                                    };

                                                    listData.Add(data);
                                                }
                                            }
                                        }
                                    }
                                }
                                if (!isAuthorizedKPI) continue;
                            }
                        }
                        else
                        {
                            response.IsSuccess = false;
                            response.Message = "File Not Valid";
                            break;
                        }
                        switch (viewModel.ConfigType)
                        {
                            case "KpiTarget":
                                response = UpdateKpiTarget(listData);
                                break;
                            case "KpiAchievement":
                                response = UpdateKpiAchievement(listData);
                                break;
                            case "OperationData":
                                response = UpdateOperationData(listData);
                                break;
                            default:
                                response.IsSuccess = false;
                                response.Message = string.Format(@"config type for {0} is not existed",
                                                                 viewModel.ConfigType);
                                break;
                        }
                    }
                    #endregion
                }
            }
            catch (Exception exception)
            {
                response.IsSuccess = false;
                response.Message = exception.Message;
            }

            return response;
        }
Ejemplo n.º 4
0
        public FileResult DownloadTemplate(string configType, string periodeType, int year, int month)
        {
            ConfigType config = string.IsNullOrEmpty(configType)
                                    ? ConfigType.KpiTarget
                                    : (ConfigType)Enum.Parse(typeof(ConfigType), configType);

            #region Get Data
            PeriodeType pType = string.IsNullOrEmpty(periodeType)
                            ? PeriodeType.Yearly
                            : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodeType);

            var viewModel = new ConfigurationViewModel();
            switch (config)
            {
                case ConfigType.KpiTarget:
                    //todo get KpiTarget Data
                    var targetRequest = new GetKpiTargetsConfigurationRequest() { PeriodeType = periodeType, Year = year, Month = month };
                    var target = _kpiTargetService.GetKpiTargetsConfiguration(targetRequest);
                    viewModel = target.MapTo<ConfigurationViewModel>();
                    break;
                case ConfigType.KpiAchievement:
                    //todo get KpiAchievement Data
                    var request = new GetKpiAchievementsConfigurationRequest() { PeriodeType = periodeType, Year = year, Month = month };
                    var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);
                    viewModel = achievement.MapTo<ConfigurationViewModel>();
                    break;
                case ConfigType.Economic:
                    break;
                default:
                    break;
            }
            #endregion

            /*
             * Find and Create Directory
             */
            var resultPath = Server.MapPath(string.Format("{0}{1}/", TemplateDirectory, configType));
            if (!System.IO.Directory.Exists(resultPath))
            {
                System.IO.Directory.CreateDirectory(resultPath);
            }

            #region parsing data to excel
            string dateFormat = string.Empty;
            string workSheetName = new StringBuilder(periodeType).ToString();
            switch (periodeType)
            {
                case "Yearly":
                    dateFormat = "yyyy";
                    break;
                case "Monthly":
                    dateFormat = "mmm-yy";
                    workSheetName = string.Format("{0}_{1}", workSheetName, year);
                    break;
                default:
                    dateFormat = "dd-mmm-yy";
                    workSheetName = string.Format("{0}_{1}-{2}", workSheetName, year, month.ToString().PadLeft(2, '0'));
                    break;
            }
            string guid = Guid.NewGuid().ToString();
            string fileName = new StringBuilder(guid).Append(".xlsx").ToString();

            //using (FileStream stream = new FileStream(fileName,FileMode.Create,FileAccess.ReadWrite)
            //{

            //}
            IWorkbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];

            worksheet.Name = workSheetName;
            workbook.Worksheets.ActiveWorksheet = worksheet;
            RowCollection rows = workbook.Worksheets[0].Rows;
            ColumnCollection columns = workbook.Worksheets[0].Columns;

            Row HeaderRow = rows[0];
            HeaderRow.FillColor = Color.DarkGray;
            HeaderRow.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            HeaderRow.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            Column KpiIdColumn = columns[0];
            Column KpiNameColumn = columns[1];
            KpiIdColumn.Visible = false;

            HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiIdColumn.Index].Value = "KPI ID";
            HeaderRow.Worksheet.Cells[HeaderRow.Index, KpiNameColumn.Index].Value = "KPI Name";
            int i = 1; //i for row
            foreach (var kpi in viewModel.Kpis)
            {
                worksheet.Cells[i, KpiIdColumn.Index].Value = kpi.Id;
                worksheet.Cells[i, KpiNameColumn.Index].Value = string.Format("{0} ({1})", kpi.Name, kpi.Measurement);
                int j = 2; // for column
                var items = new List<ConfigurationViewModel.Item>();
                switch (configType)
                {
                    case "KpiTarget":
                        foreach (var target in kpi.KpiTargets)
                        {
                            var item = new ConfigurationViewModel.Item();
                            item.Id = target.Id;
                            item.KpiId = kpi.Id;
                            item.Periode = target.Periode;
                            item.Remark = target.Remark;
                            item.Value = target.Value;
                            item.PeriodeType = pType;
                            items.Add(item);
                        }
                        break;
                    case"KpiAchievement":
                        foreach (var achieve in kpi.KpiAchievements)
                        {
                            var item = new ConfigurationViewModel.Item() { Id = achieve.Id, KpiId = achieve.Id, Periode = achieve.Periode, Remark = achieve.Remark, Value = achieve.Value, PeriodeType = pType };
                            items.Add(item);
                        }
                        break;
                    case "Economic":
                        items = kpi.Economics.MapTo<ConfigurationViewModel.Item>();
                        break;
                    default:
                        break;
                }
                foreach (var achievement in items)
                {
                    worksheet.Cells[HeaderRow.Index, j].Value = achievement.Periode;
                    worksheet.Cells[HeaderRow.Index, j].NumberFormat = dateFormat;
                    worksheet.Cells[HeaderRow.Index, j].AutoFitColumns();

                    worksheet.Cells[i, j].Value = achievement.Value;
                    worksheet.Cells[i, j].NumberFormat = "#,0.#0";
                    worksheet.Columns[j].AutoFitColumns();
                    j++;
                }
                //Column TotalValueColumn = worksheet.Columns[j];
                //if (i == HeaderRow.Index + 1)
                //{
                //    worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index].Value = "Average";
                //    worksheet.Cells[HeaderRow.Index, TotalValueColumn.Index + 1].Value = "SUM";
                //    Range r1 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
                //    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r1.GetReferenceA1());
                //    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r1.GetReferenceA1());
                //}
                //else
                //{
                //    // add formula
                //    Range r2 = worksheet.Range.FromLTRB(KpiNameColumn.Index + 1, i, j - 1, i);
                //    worksheet.Cells[i, j].Formula = string.Format("=AVERAGE({0})", r2.GetReferenceA1());
                //    worksheet.Cells[i, j + 1].Formula = string.Format("=SUM({0})", r2.GetReferenceA1());
                //}
                i++;
            }

            KpiNameColumn.AutoFitColumns();
            worksheet.FreezePanes(HeaderRow.Index, KpiNameColumn.Index);

            string resultFilePath = string.Format("{0},{1}",resultPath,fileName);// System.Web.HttpContext.Current.Request.MapPath(resultPath + fileName);
            //System.Web.HttpContext.Current.Response.Clear();
            //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //System.Web.HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}", fileName));

            using (FileStream stream = new FileStream(resultFilePath, FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.SaveDocument(stream, DocumentFormat.Xlsx);
                stream.Close();
            }
            //System.Web.HttpContext.Current.Response.End();
            //workbook.SaveDocument(resultFilePath, DocumentFormat.OpenXml);
            //workbook.Dispose();
            #endregion

            string namafile = Path.GetFileName(resultFilePath);
            byte[] fileBytes = System.IO.File.ReadAllBytes(resultFilePath);
            var response = new FileContentResult(fileBytes, "application/octet-stream") { FileDownloadName = namafile };
            return response;
        }