public void TableExecutor() { long?value = 0; int bulkSize = 1; Dictionary <string, string> dic = new Dictionary <string, string>(); foreach (var product in allProducts) { try { if (product != null && !dic.ContainsKey(product.ItemId)) { Wholesaler_Fragrancex fran = new Wholesaler_Fragrancex(); fran.id = bulkSize + 1; fran.Sku = Convert.ToInt32(product.ItemId); fran.BrandName = product.BrandName; fran.Description = product.Description; fran.Gender = product.Gender; fran.Instock = product.Instock; fran.LargeImageUrl = product.LargeImageUrl; fran.MetricSize = product.MetricSize; fran.ParentCode = product.ParentCode; fran.ProductName = product.ProductName; fran.RetailPriceUSD = product.RetailPriceUSD; fran.Size = product.Size; fran.SmallImageURL = product.SmallImageUrl; fran.Type = product.Type; fran.WholePriceAUD = product.WholesalePriceAUD; fran.WholePriceCAD = product.WholesalePriceCAD; fran.WholePriceEUR = product.WholesalePriceEUR; fran.WholePriceGBP = product.WholesalePriceGBP; fran.WholePriceUSD = product.WholesalePriceUSD; if (upc.TryGetValue(Convert.ToInt32(product.ItemId), out value)) { fran.Upc = value; } dic.Add(fran.Sku.ToString(), fran.Sku.ToString()); fragrancex.Add(fran); } } catch (Exception e) { throw; } } //upload(uploadFragrancex, bulkSize, "dbo.Fragrancex"); }
public virtual void TableExecutor() { List <UPC> list = new List <UPC>(); FileInfo file = new FileInfo(path); int exception = 0; try { using (ExcelPackage package = new ExcelPackage(file)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int rowCount = worksheet.Dimension.Rows; long? value = 0; int itemID = 0, description = 0, price = 0, brandName = 0, image = 0, type = 0; for (int row = 1; row <= rowCount; row++) { // Map titles if (row == 1) { for (int column = 1; column <= worksheet.Dimension.Columns; column++) { if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("sku")) { itemID = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("html")) { description = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("variant price")) { price = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("vendor")) { brandName = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("image src")) { image = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("type")) { type = column; } } } else { exception++; if (!franDic.ContainsKey(Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString()))) { Wholesaler_Fragrancex fran = new Wholesaler_Fragrancex(); fran.id = exception; fran.Sku = Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString()); fran.BrandName = worksheet.Cells[row, brandName].Value?.ToString(); fran.Description = worksheet.Cells[row, description].Value?.ToString(); fran.Gender = null; fran.Instock = true; fran.LargeImageUrl = worksheet.Cells[row, image].Value?.ToString(); fran.MetricSize = null; fran.ParentCode = null; fran.ProductName = null; fran.RetailPriceUSD = 0.0; fran.Size = null; fran.SmallImageURL = null; fran.Type = worksheet.Cells[row, type].Value?.ToString(); fran.WholePriceAUD = 0.0; fran.WholePriceCAD = 0.0; fran.WholePriceEUR = 0.0; fran.WholePriceGBP = 0.0; fran.WholePriceUSD = Convert.ToDouble(worksheet.Cells[row, price].Value?.ToString()); if (upc.TryGetValue(Convert.ToInt32(Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString())), out value)) { fran.Upc = value; } fragancexList.Add(fran); franDic.Add(Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString()), Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString())); } } } } } catch (Exception ex) { throw (ex); } }
public void saveExcel() { FileInfo file = new FileInfo(path); try { using (ExcelPackage package = new ExcelPackage(file)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; // delete everything from the spreadsheet worksheet.DeleteRow(2, worksheet.Dimension.Rows); string itemID; string title = ""; int row = 2; int execption = 0; setTitleDic(); try { foreach (var item in shopifyProfile .OrderBy(x => x.Value.handle) .OrderBy(x => x.Value.option1Value)) { if (shopifyUser.ContainsKey(item.Value.sku + "_" + profile.ProfileUser)) { execption++; itemID = item.Value.sku; Wholesaler_Fragrancex fra = new Wholesaler_Fragrancex(); fragrancex.TryGetValue(Convert.ToInt32(itemID), out fra); string description = string.Empty; double price = 0.0; if (fra != null) { description = fra.Description; price = fra.WholePriceUSD; } // Set Handle worksheet.Cells[row, 1].Value = item.Value.handle; // Logic for the title title = BuildTitle(dicTitle, item.Value.handle, item.Value.collection); worksheet.Cells[row, 2].Value = title; //Logic for the HTML Body worksheet.Cells[row, 3].Value = BuildHTML(title, row, profile.html, item.Value.image , description); // Set Vendor worksheet.Cells[row, 4].Value = item.Value.vendor; // Set Type worksheet.Cells[row, 5].Value = item.Value.type; // Set Publish worksheet.Cells[row, 6].Value = "TRUE"; // Option1 Name worksheet.Cells[row, 7].Value = "Size"; // Option1 Value worksheet.Cells[row, 8].Value = item.Value.option1Value; // Set SKU worksheet.Cells[row, 13].Value = item.Value.sku; // Set Variant worksheet.Cells[row, 14].Value = 400; // Set Variant Inventory Tracker worksheet.Cells[row, 15].Value = "shopify"; // Set Variant Inventory Policy worksheet.Cells[row, 17].Value = "deny"; // Set Variant Fulfillment Service worksheet.Cells[row, 18].Value = "manual"; // Set Variant Compare At Price //if (item.Value.comparePrice != 0) //{ // worksheet.Cells[row, 20].Value = item.Value.comparePrice; //} // Set Variant Requires Shipping worksheet.Cells[row, 21].Value = "TRUE"; // Set Variant Taxable worksheet.Cells[row, 22].Value = "FALSE"; // UPC creator UPC upcs = new UPC(); upc.TryGetValue(Convert.ToInt32(itemID), out upcs); if (upcs != null) { worksheet.Cells[row, 23].Value = upcs.Upc; } // Set Image worksheet.Cells[row, 24].Value = fixPictureHTML(item.Value.image); // Set Tags worksheet.Cells[row, 26].Value = item.Value.tags; // Set Collection worksheet.Cells[row, 27].Value = item.Value.collection; // Prices if (price != 0.0) { // Set Price worksheet.Cells[row, 19].Value = getSellingPrice(price); // Set Variant Inventory Qty worksheet.Cells[row, 16].Value = profile.items; } else { // Set Price worksheet.Cells[row, 19].Value = 100.0; // Set Variant Inventory Qty worksheet.Cells[row, 16].Value = 0; } worksheet.Cells[row, 28].Value = price; row++; } } } catch (Exception e) { throw e; } package.Save(); } } catch (Exception ex) { throw ex; } }
public void ExcelGenerator() { FileInfo file = new FileInfo(path); long? skuID; int execption = 0; try { using (ExcelPackage package = new ExcelPackage(file)) { StringBuilder sb = new StringBuilder(); ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int rowCount = worksheet.Dimension.Rows; int ColCount = worksheet.Dimension.Columns; int sku = 0; int price = 0; int quantity = 0; int asinCol = 0; for (int row = 1; row <= rowCount; row++) { execption++; if (row == 1) { for (int column = 1; column <= ColCount; column++) { if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("sku")) { sku = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("price")) { price = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("quantity") || worksheet.Cells[row, column].Value.ToString().ToLower().Contains("qty")) { quantity = column; } else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("asin")) { asinCol = column; } } worksheet.Cells[row, 1].Value = "sku"; worksheet.Cells[row, 2].Value = "price"; worksheet.Cells[row, 3].Value = "minimum-seller-allowed-price"; worksheet.Cells[row, 4].Value = "maximum-seller-allowed-price"; worksheet.Cells[row, 5].Value = "quantity"; worksheet.Cells[row, 6].Value = "handling-time"; worksheet.Cells[row, 7].Value = "fulfillment-channel"; worksheet.Cells[row, 8].Value = "Suggested Price"; worksheet.Cells[row, 9].Value = "Weight Price"; } else { if (!string.IsNullOrEmpty(worksheet.Cells[row, 1].Value?.ToString())) { // if the first row is a perfume/Cologne string rowSku = worksheet.Cells[row, 1].Value.ToString(); int digitSku = DigitGetter(rowSku); double rowPrice = Convert.ToDouble(worksheet.Cells[row, price].Value); string asin = Convert.ToString(worksheet.Cells[row, asinCol].Value); if (isBlackListed(asin)) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Pink); worksheet.Cells[row, 5].Value = 0; worksheet.Cells[row, 8].Value = "ASIN Black Listed"; } else if (isFragrancex(digitSku)) { // In-stock if (fragrancexList.ContainsKey(digitSku)) { skuID = DigitGetter(rowSku); Wholesaler_Fragrancex f = new Wholesaler_Fragrancex(); fragrancexList.TryGetValue(Convert.ToInt32(skuID), out f); fragrancex = f; double sellingPrice = getSellingPrice(fragrancex.WholePriceUSD); // Price lower if (isPriceLower(rowPrice, sellingPrice) && sellingPrice != 0) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Red); worksheet.Cells[row, 8].Value = sellingPrice; } // The price is too high else if (isPriceTooHigh(rowPrice, sellingPrice) && sellingPrice != 0) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 3; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue); worksheet.Cells[row, 8].Value = sellingPrice; } else if (sellingPrice != 0) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 3; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Green); worksheet.Cells[row, 8].Value = sellingPrice; } } // Out of stock else { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 0; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow); } } else if (isAzImporter(rowSku)) { double sellingPrice = getSellingPrice(); // In-stock if (azImporter.Quantity > 0) { // Weight is not register if (!isWeightRegister(AzImporterPriceWeight)) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Orange); worksheet.Cells[row, 8].Value = "Weight Not Register"; } else { // Price too low if (isPriceLower(rowPrice, sellingPrice) && sellingPrice != 0) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Red); worksheet.Cells[row, 8].Value = sellingPrice; worksheet.Cells[row, 9].Value = azImporter.Weight; } // Price is too high else if (isPriceTooHigh(rowPrice, sellingPrice) && sellingPrice != 0) { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 3; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue); worksheet.Cells[row, 8].Value = sellingPrice; worksheet.Cells[row, 9].Value = azImporter.Weight; } else { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 3; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Green); worksheet.Cells[row, 8].Value = sellingPrice; worksheet.Cells[row, 9].Value = azImporter.Weight; } } } // Out of stock else { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 0; worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow); worksheet.Cells[row, 8].Value = sellingPrice; worksheet.Cells[row, 9].Value = azImporter.Weight; } azImporterSku = ""; AzImporterPriceWeight = 0.0; AzImporterWeight = 0.0; } //else if(isPerfumeWorldWide(rowSku)) //{ // // In-stock // if (perfumeWorldWideList.ContainsKey(rowSku)) // { // PerfumeWorldWide p = new PerfumeWorldWide(); // perfumeWorldWideList.TryGetValue(rowSku, out p); // perfumeWorldWide = p; // double sellingPrice = getSellingPrice(perfumeWorldWide.Cost); // // Price lower // if (isPriceLower(rowPrice, sellingPrice) && sellingPrice != 0) // { // worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; // worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value; // worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Red); // worksheet.Cells[row, 8].Value = sellingPrice; // } // // The price is too high // else if (isPriceTooHigh(rowPrice, sellingPrice) && sellingPrice != 0) // { // worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; // worksheet.Cells[row, 5].Value = 3; // worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue); // worksheet.Cells[row, 8].Value = sellingPrice; // } // else if (sellingPrice != 0) // { // worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; // worksheet.Cells[row, 5].Value = 3; // worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Green); // worksheet.Cells[row, 8].Value = sellingPrice; // } // } // // Out-stock // else // { // worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; // worksheet.Cells[row, 5].Value = 0; // worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid; // worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow); // } //} else { worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value; worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value; worksheet.Cells[row, 5].Value = 0; } worksheet.Cells[row, 3].Value = ""; worksheet.Cells[row, 4].Value = ""; } } } int start = 2; worksheet.Cells[execption + start, 1].Value = "Legend"; start++; worksheet.Cells[execption + start, 1].Value = "Out of stock"; worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Yellow); start++; worksheet.Cells[execption + start, 1].Value = "Weight not Register"; worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Orange); start++; worksheet.Cells[execption + start, 1].Value = "Price is too High"; worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue); start++; worksheet.Cells[execption + start, 1].Value = "Price is too Low"; worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Red); start++;; worksheet.Cells[execption + start, 1].Value = "Price is Correct"; worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Green); package.Save(); } } catch (Exception ex) { throw (ex); } }