コード例 #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;
        }
コード例 #3
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;
        }
コード例 #4
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);
                                                    var delete = _kpiAchievementService.DeleteKpiAchievement(new DeleteKpiAchievementRequest {
                                                        kpiId          = kpiId,
                                                        periode        = periodData,
                                                        periodeType    = pType,
                                                        ControllerName = "KPI Achievement",
                                                        ActionName     = "Read Excel File",
                                                        UserId         = UserProfile().UserId
                                                    });
                                                    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;
                                            request.UpdateFrom = "KPIAchievementForm";
                                            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);
        }