public async Task <IActionResult> AddBom([FromForm] BomForCreationDto bomForCreationDto) { var formFile = bomForCreationDto.File; var listas = new List <BomList>(); if (formFile == null || formFile.Length <= 0) { return(BadRequest("formfile is empty")); } if (!Path.GetExtension(formFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase)) { return(BadRequest("Not Support file extension")); } try { using (var stream = new MemoryStream()) { await formFile.CopyToAsync(stream); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (var package = new ExcelPackage(stream)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; var rowCount = worksheet.Dimension.Rows; var colCount = worksheet.Dimension.Columns; var buhNrCol = 0; var qtyCol = 0; var mnfCol = 0; var name = worksheet.Cells[1, 1].Value.ToString().Trim(); var tikrinimas = await _repo.GetBomName(name); if (tikrinimas != null) { return(BadRequest("Toks bomas jau yra ikeltas")); } for (int col = 1; col <= colCount; col++) { var res = worksheet.Cells[2, col].Value.ToString().Trim(); if (res.Contains("Buh") || res.Contains("buh")) { buhNrCol = col; } if (res.Contains("QTY") || res.Contains("uantity")) { qtyCol = col; } if (res.Contains("Manufacturer Part Number")) { mnfCol = col; } } if (buhNrCol == 0) { return(BadRequest("Nerastas buhalterinio nr. stuleplis, patikslinkite langelio pavadinima i Buh.Nr. ")); } if (qtyCol == 0) { return(BadRequest("Nerastas kiekio stulpelis, patikslinkite langelio pavadinima i QTY")); } if (mnfCol == 0) { return(BadRequest("Nerastas gamintojo kodo stuleplis, patikslinkite i Manufacturer Part Number")); } var bomName = new BomName { Name = name, DateAdded = DateTime.Now, LastModified = DateTime.Now }; var result = await _repo.RegisterBomName(bomName); for (int row = 3; row <= rowCount; row++) { var buhNr = worksheet.Cells[row, buhNrCol].Value.ToString().Trim(); var manufPartNr = worksheet.Cells[row, mnfCol].Value.ToString().Trim(); var componentasId = 0; var res = await _search.GetComponentBuhNr(buhNr); if (res != null) { manufPartNr = res.Mnf; componentasId = res.Id; } listas.Add(new BomList { BuhNr = buhNr, Qty = int.Parse(worksheet.Cells[row, qtyCol].Value.ToString().Trim()), BomNameId = result.Id, ComponentasId = componentasId, ManufPartNr = manufPartNr }); } var reg = await _repo.RegisterBomList(listas); } } } catch (System.Exception) { throw; } return(Ok(listas)); }