public ActionResult Configuration(ConfigurationParamViewModel paramViewModel) { int roleGroupId = paramViewModel.Id; PeriodeType pType = string.IsNullOrEmpty(paramViewModel.PeriodeType) ? PeriodeType.Yearly : (PeriodeType)Enum.Parse(typeof(PeriodeType), paramViewModel.PeriodeType); var request = new GetKpiTargetsConfigurationRequest(); request.PeriodeType = pType.ToString(); request.RoleGroupId = roleGroupId; request.Year = paramViewModel.Year; request.Month = paramViewModel.Month; var response = _kpiTargetService.GetKpiTargetsConfiguration(request); if (response.IsSuccess) { var viewModel = response.MapTo <ConfigurationKpiTargetsViewModel>(); viewModel.Year = request.Year; viewModel.Month = request.Month; viewModel.Years = _dropdownService.GetYears().MapTo <SelectListItem>(); viewModel.Months = _dropdownService.GetMonths().MapTo <SelectListItem>(); viewModel.PeriodeType = pType.ToString(); //viewModel.FileName = this._ExportToExcel(viewModel); return(View(viewModel)); } return(base.ErrorPage(response.Message)); }
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); }