예제 #1
0
        public RevenueActuals Update(RevenueActuals revenueActuals)
        {
            var newRevenueActuals = _context.RevenueActuals.Update(revenueActuals);

            if (newRevenueActuals != null && newRevenueActuals.State == EntityState.Added)
            {
                var affectedRows = _context.SaveChanges();

                if (affectedRows > 0)
                {
                    return(newRevenueActuals.Entity);
                }
            }

            return(null);
        }
예제 #2
0
        public IActionResult RevenueActuals()
        {
            IFormFile file        = Request.Form.Files[0];
            string    folderName  = "UploadExcel";
            string    webRootPath = _hostingEnvironment.WebRootPath;
            string    newPath     = Path.Combine(webRootPath, folderName);

            //StringBuilder sb = new StringBuilder();

            if (!Directory.Exists(newPath))
            {
                Directory.CreateDirectory(newPath);
            }

            if (file.Length > 0)
            {
                string sFileExtension = Path.GetExtension(file.FileName).ToLower();

                ISheet sheet;

                string fullPath = Path.Combine(newPath, file.FileName);

                using (var stream = new FileStream(fullPath, FileMode.Create))
                {
                    file.CopyTo(stream);
                    stream.Position = 0;

                    if (sFileExtension == ".xls")
                    {
                        HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
                        sheet = hssfwb.GetSheetAt(0);                   //get first sheet from workbook
                    }
                    else
                    {
                        XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
                        sheet = hssfwb.GetSheetAt(0);                   //get first sheet from workbook
                    }

                    IRow headerRow = sheet.GetRow(0); //Get Header Row

                    int cellCount = headerRow.LastCellNum;

                    //test only 3-4 row
                    for (int i = 1; i <= 685; i++)
                    {
                        var x = new RevenueActuals()
                        {
                        };
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        if (row.Cells.All(d => d.CellType == CellType.Blank))
                        {
                            continue;
                        }

                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                //columns += row.GetCell(j).ToString();
                                //if (j == 0)
                                //{
                                //    Name = row.GetCell(j).ToString();
                                //}
                                switch (j)
                                {
                                case 1:
                                    x.Quarter = row.GetCell(j).ToString();
                                    break;

                                case 5:
                                    x.BusinessUnitL5 = row.GetCell(j).ToString();
                                    break;

                                case 10:
                                    x.EndCustomerName = row.GetCell(j).ToString();
                                    break;

                                case 13:
                                    x.RevKSadj = Convert.ToDouble(row.GetCell(j).ToString());
                                    break;

                                case 14:
                                    x.CosKSadj = Convert.ToDouble(row.GetCell(j).ToString());
                                    break;

                                case 15:
                                    x.Units = Convert.ToInt32(row.GetCell(j).ToString());
                                    break;

                                default:
                                    break;
                                }
                            }
                        }
                        _revenueActualsRepository.Add(x);
                    }
                }
            }
            return(View("RevenueActuals", "Successfully uploaded revenue actuals"));
        }