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); }
private BaseResponse _ReadExcelFile(string filename, int scenarioId, string configType) { var response = new BaseResponse(); string periodType = string.Empty; PeriodeType pType = PeriodeType.Yearly; int tahun = DateTime.Now.Year, bulan = DateTime.Now.Month; List <OperationDataConfigurationViewModel.Item> list_data = new List <OperationDataConfigurationViewModel.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; List <int> list_Kpi = new List <int>(); 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) { int Kpis_Id = int.Parse(worksheet.Cells[i, j].Value.ToString()); list_Kpi.Add(Kpis_Id); } } } } var operationsId = _operationConfigService.GetOperationIn(new GetOperationsInRequest { KpiIds = list_Kpi }); //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) { var operationId = 0; var operation = operationsId.KeyOperations.FirstOrDefault(x => x.KpiId == Kpi_Id); if (operation != null) { operationId = operation.Id; } 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 OperationDataConfigurationViewModel.Item() { Value = nilai, KpiId = Kpi_Id, Periode = periodData, PeriodeType = pType, ScenarioId = scenarioId, OperationId = operationId }; 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); }