public List <ProductInfo> Import(Stream inputStream, long owner, long subdomain) { var templateWorkbook = new HSSFWorkbook(inputStream, true); var sheet = templateWorkbook.GetSheet("Products"); int count = 0; var productsList = new List <ProductInfo>(); using (var repository = new TradelrRepository()) { while (true) { var row = sheet.GetRow(count++); if (row == null) { break; } var sku = GetCellValueAsString(row.GetCell(0, MissingCellPolicy.RETURN_NULL_AND_BLANK)); if (sku.StartsWith(";")) { continue; } if (string.IsNullOrEmpty(sku)) { break; } var title = GetCellValueAsString(row.GetCell(1, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var description = GetCellValueAsString(row.GetCell(2, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var maincategory = GetCellValueAsString(row.GetCell(3, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var subcategory = GetCellValueAsString(row.GetCell(4, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var stockunit = GetCellValueAsString(row.GetCell(5, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var costprice = GetCellValueAsDecimal(row.GetCell(6, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var sellingprice = GetCellValueAsDecimal(row.GetCell(7, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var instock = GetCellValueAsInt(row.GetCell(8, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var photos = GetCellValueAsString(row.GetCell(9, MissingCellPolicy.RETURN_NULL_AND_BLANK)); var product = new product { subdomainid = subdomain, details = description, title = title, costPrice = costprice, sellingPrice = sellingprice }; if (!string.IsNullOrEmpty(stockunit)) { var masterunit = repository.AddMasterStockUnit(stockunit); var su = new stockUnit { unitID = masterunit.id, subdomainid = subdomain }; product.stockUnitId = repository.AddStockUnit(su); } product.otherNotes = ""; var inventoryloc = repository.GetInventoryLocation(GeneralConstants.INVENTORY_LOCATION_DEFAULT, subdomain); // create inventoryLocItem var inventoryLocItem = new inventoryLocationItem { locationid = inventoryloc.id, lastUpdate = DateTime.UtcNow }; var invWorker = new InventoryWorker(inventoryLocItem, subdomain, true, false); invWorker.SetValues("product created", instock, null, null, null); MASTERproductCategory mastercat; long?catid = null; if (!string.IsNullOrEmpty(maincategory)) { mastercat = repository.AddMasterProductCategory(maincategory); var cat = new productCategory() { masterID = mastercat.id, subdomain = subdomain }; catid = repository.AddProductCategory(cat, subdomain); } // add sub category if (!string.IsNullOrEmpty(subcategory) && !string.IsNullOrEmpty(maincategory)) { mastercat = repository.AddMasterProductCategory(subcategory); var subcat = new productCategory() { masterID = mastercat.id, subdomain = subdomain, parentID = catid }; catid = repository.AddProductCategory(subcat, subdomain); } product.category = catid; product.created = DateTime.UtcNow; product.updated = product.created; if (!productsList.Where(x => x.p.product_variants.Count(y => y.sku == sku) != 0).Any()) { var pi = new ProductInfo() { p = product }; if (!string.IsNullOrEmpty(photos)) { var photourls = photos.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries); foreach (var photourl in photourls) { pi.AddPhotoUrl(photourl); } } var variant = new product_variant { sku = sku }; variant.inventoryLocationItems.Add(inventoryLocItem); pi.p.product_variants.Add(variant); productsList.Add(pi); } } } return(productsList); }