コード例 #1
0
        private ReadExcelFileModel _ReadExcelFile(string filename)
        {
            var listPrev = new List<UpdateKpiAchievementsViewModel.KpiAchievementItem>();
            var response = new ReadExcelFileModel();
            var file = Server.MapPath(filename);
            Workbook workbook = new Workbook();
            using (FileStream stream = new FileStream(file, FileMode.Open))
            {
                workbook.LoadDocument(stream, DocumentFormat.OpenXml);
                foreach (var worksheet in workbook.Worksheets)
                {
                    string[] name = worksheet.Name.Split('_');
                    //if (name.Count() > 0 && name[0] != "Sheet1" && (name[0] == PeriodeType.Daily.ToString() || name[0] == PeriodeType.Hourly.ToString() || name[0] == PeriodeType.Monthly.ToString() || name[0] == PeriodeType.Weekly.ToString() || name[0] == PeriodeType.Yearly.ToString()))
                    if(name[0] == "Daily" || name[0] == "Monthly" || name[0]== "Yearly")
                    {

                        string periodType = name[0];
                        PeriodeType pType = string.IsNullOrEmpty(periodType)
                            ? PeriodeType.Yearly
                            : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodType);
                        string period = name[name.Count() - 1];
                        string[] periodes = null;
                        int tahun, bulan;
                        //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;
                            }
                        }

                        //coba baca value
                        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;
                        for (int i = 1; i < rows; i++)
                        {
                            //get rows
                            for (int j = 0; j < column; j++)
                            {
                                var prepareDataContainer = new UpdateKpiAchievementsViewModel.KpiAchievementItem();
                                //get rows header and period
                                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
                                    {
                                        nilai = null;
                                    }

                                    if (nilai != null)
                                    {
                                        prepareDataContainer.Value = nilai;
                                        prepareDataContainer.KpiId = Kpi_Id;
                                        prepareDataContainer.Periode = periodData;
                                        prepareDataContainer.PeriodeType = pType;
                                        var oldKpiAchievement = _kpiAchievementService.GetKpiAchievementByValue(new GetKpiAchievementRequestByValue { Kpi_Id = Kpi_Id, periode = periodData, PeriodeType = periodType });
                                        if (oldKpiAchievement.IsSuccess)
                                        {
                                            prepareDataContainer.Id = oldKpiAchievement.Id;
                                        }
                                        var request = prepareDataContainer.MapTo<UpdateKpiAchievementItemRequest>();
                                        _kpiAchievementService.UpdateKpiAchievementItem(request);
                                    }
                                    //listPrev.Add(prepareDataContainer);
                                }

                            }

                        }
                        //DataTable dataTable = worksheet.CreateDataTable(range, true);
                        //for (int col = 0; col < range.ColumnCount; col++)
                        //{
                        //    //CellValueType cellType = range[0, col].Value.Type;
                        //    for (int r = 1; r < range.RowCount; r++)
                        //    {
                        //        //if (cellType != range[r, col].Value.Type)
                        //        //{
                        //        //    dataTable.Columns[col].DataType = typeof(string);
                        //        //    break;
                        //        //}
                        //    }
                        //}

                        response.isSuccess = true;
                    }
                    else
                    {
                        response.isSuccess = false;
                        response.Message = "File Not Valid";
                        break;
                    }

                }
            }
            return response;
        }
コード例 #2
0
        private ReadExcelFileModel _ReadExcelFile(string filename)
        {
            var response = new ReadExcelFileModel();
            try
            {
                int inserted = 0;
                int skipped = 0;
                int rejected = 0;
                var listPrev = new List<UpdateKpiAchievementsViewModel.KpiAchievementItem>();

                var file = Server.MapPath(filename);
                var userId = UserProfile().UserId;
                Workbook workbook = new Workbook();
                using (FileStream stream = new FileStream(file, FileMode.Open))
                {
                    workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
                    foreach (var worksheet in workbook.Worksheets)
                    {
                        string[] name = worksheet.Name.Split('_');
                        //if (name.Count() > 0 && name[0] != "Sheet1" && (name[0] == PeriodeType.Daily.ToString() || name[0] == PeriodeType.Hourly.ToString() || name[0] == PeriodeType.Monthly.ToString() || name[0] == PeriodeType.Weekly.ToString() || name[0] == PeriodeType.Yearly.ToString()))
                        if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
                        {

                            string periodType = name[0];
                            PeriodeType pType = string.IsNullOrEmpty(periodType)
                                                    ? PeriodeType.Yearly
                                                    : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodType);
                            string period = name[name.Count() - 1];
                            string[] periodes = null;
                            int tahun, bulan;
                            //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;
                                }
                            }

                            //coba baca value
                            workbook.Worksheets.ActiveWorksheet = worksheet;
                            //get row

                            Range range = worksheet.GetUsedRange();
                            int rows = range.RowCount;
                            int column = range.ColumnCount - 2;
                            int kpiId = 0;
                            DateTime periodData = new DateTime();
                            double? nilai = null;
                            for (int i = 1; i < rows; i++)
                            {
                                //get rows
                                for (int j = 0; j < column; j++)
                                {
                                    bool fromExistedToNull = false;
                                    var prepareDataContainer = new UpdateKpiAchievementsViewModel.KpiAchievementItem();
                                    //get rows header and period
                                    if (j == 0)
                                    {
                                        if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
                                        {
                                            kpiId = 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;
                                        }

                                        bool isValidKpi = false;
                                        if (!this.UserProfile().IsSuperAdmin)
                                        {
                                            skipped++;
                                            isValidKpi =
                                                _kpiService.IsValidKpi(new Services.Requests.Kpi.GetKpiByRole
                                                    {
                                                        RoleId = this.UserProfile().RoleId
                                                    });
                                        }
                                        else
                                        {
                                            isValidKpi = true;
                                        }

                                        if (isValidKpi && (nilai != null || fromExistedToNull))
                                        {
                                            prepareDataContainer.Value = nilai.ToString();
                                            prepareDataContainer.KpiId = kpiId;
                                            prepareDataContainer.Periode = periodData;
                                            prepareDataContainer.PeriodeType = pType;
                                            var oldKpiAchievement =
                                                _kpiAchievementService.GetKpiAchievementByValue(
                                                    new GetKpiAchievementRequestByValue
                                                        {
                                                            KpiId = kpiId,
                                                            Periode = periodData,
                                                            PeriodeType = periodType
                                                        });
                                            if (oldKpiAchievement.IsSuccess)
                                            {
                                                prepareDataContainer.Id = oldKpiAchievement.Id;
                                            }
                                            else
                                            {
                                                var fixedError = false;
                                                if (oldKpiAchievement.ExceptionType != null &&
                                                    oldKpiAchievement.ExceptionType ==
                                                    typeof(InvalidOperationException))
                                                {
                                                    var delete = _kpiAchievementService.DeleteKpiAchievement(kpiId, periodData, pType);
                                                    fixedError = delete.IsSuccess;
                                                }

                                                if (!fixedError)
                                                {
                                                    throw new Exception(string.Format(@"KPI with id {0} and periode {1} and periode type {2} can't be inserted", kpiId, periodData.ToShortDateString(), pType.ToString()));
                                                }
                                            }
                                            var request = prepareDataContainer.MapTo<UpdateKpiAchievementItemRequest>();
                                            request.UserId = userId;
                                            var insert = _kpiAchievementService.UpdateKpiAchievementItem(request);
                                            if (insert.IsSuccess)
                                            {
                                                inserted++;
                                            }
                                            else
                                            {
                                                rejected++;
                                            }
                                        }
                                    }
                                }
                            }

                            response.isSuccess = true;
                            response.Message = "Success :" + inserted + "\r\n";
                            response.Message += "Skipped :" + skipped + "\r\n";
                            response.Message += "Rejected :" + rejected + "\r\n";
                        }
                        else
                        {
                            response.isSuccess = false;
                            response.Message = "File Not Valid";
                            break;
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                response.Message = exception.Message;
                response.isSuccess = false;
            }

            return response;
        }