public ActionResult Download(string configType) { ConfigType config = string.IsNullOrEmpty(configType) ? ConfigType.KpiAchievement : (ConfigType)Enum.Parse(typeof(ConfigType), configType); var model = new ConfigurationViewModel() { /*switch (config)*/ /*{*/ /* case ConfigType.KpiAchievement:*/ /* var request = new GetKpiAchievementsConfigurationRequest();*/ /* var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);*/ /* model = achievement.MapTo<ConfigurationViewModel>();*/ /* break;*/ /* case ConfigType.KpiTarget:*/ /* var targetRequest = new GetKpiTargetsConfigurationRequest();*/ /* var target = _kpiTargetService.GetKpiTargetsConfiguration(targetRequest);*/ /* model = target.MapTo<ConfigurationViewModel>();*/ /* break;*/ /* case ConfigType.Economic:*/ /* //var request = new GetKpiAchievementsConfigurationRequest();*/ /* //var achievement = _kpiAchievementService.GetKpiAchievementsConfiguration(request);*/ /* //model = achievement.MapTo<ConfigurationViewModel>();*/ /* break;*/ /*}*/PeriodeType = "Yearly", Year = DateTime.Now.Year, Month = DateTime.Now.Month, ConfigType = config.ToString(), Years = _dropdownService.GetYears().MapTo<SelectListItem>(), Months = _dropdownService.GetMonths().MapTo<SelectListItem>(), PeriodeTypes = _dropdownService.GetPeriodeTypes().MapTo<SelectListItem>() }; return PartialView("_Download", model); //return base.ErrorPage(response.Message); }
public ActionResult Download(string configType) { ConfigType config = string.IsNullOrEmpty(configType) ? ConfigType.KpiAchievement : (ConfigType)Enum.Parse(typeof(ConfigType), configType); var viewModel = new ConfigurationViewModel() { PeriodeType = "Yearly", Year = DateTime.Now.Year, Month = DateTime.Now.Month, ConfigType = config.ToString(), Years = _dropdownService.GetYears().MapTo<SelectListItem>(), Months = _dropdownService.GetMonths().MapTo<SelectListItem>(), PeriodeTypes = _dropdownService.GetPeriodeTypes().MapTo<SelectListItem>() }; return PartialView("_Download", viewModel); }
public ActionResult Download(int scenarioId) { var list = new List<SelectListItem>(); list.Add(new SelectListItem { Text = "Yearly", Value = "Yearly" }); list.Add(new SelectListItem { Text = "Monthly", Value = "Monthly" }); var model = new ConfigurationViewModel() { PeriodeType = "Yearly", Year = DateTime.Now.Year, Month = DateTime.Now.Month, Years = _dropdownService.GetYears().MapTo<SelectListItem>(), Months = _dropdownService.GetMonths().MapTo<SelectListItem>(), PeriodeTypes = list }; ViewBag.ScenarioId = scenarioId; return PartialView("_Download", model); }
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; }
public ActionResult Upload(string configType) { var viewModel = new ConfigurationViewModel(); viewModel.ConfigType = configType; return PartialView("_Upload", viewModel); }
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; }