public static void Export(string filename, MarketItems item) { MarketItems items = new MarketItems(); foreach (var a in item) { items.Add(a); if (a.ChildrenItems.Count != 0) { foreach (var b in a.ChildrenItems) { items.Add(b); } } } using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(filename))) { var Worksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here var totalRows = Worksheet.Dimension.End.Row; var totalColumns = Worksheet.Dimension.End.Column; Dictionary <string, List <string> > Colls = new Dictionary <string, List <string> >(); for (int col = 1; col <= totalColumns; col++) { if (Worksheet.Cells[1, col].Value.ToString() == "*Model") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Model; } } if (Worksheet.Cells[1, col].Value.ToString() == "*Name") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Name; } } if (Worksheet.Cells[1, col].Value.ToString() == "Description") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Description; } } if (Worksheet.Cells[1, col].Value.ToString() == "Meta title") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Meta_title; } } if (Worksheet.Cells[1, col].Value.ToString() == "SEO url") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].SEO_url; } } if (Worksheet.Cells[1, col].Value.ToString() == "Quantity") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Quantity; } } if (Worksheet.Cells[1, col].Value.ToString() == "Out stock status") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Out_stock_status; } } if (Worksheet.Cells[1, col].Value.ToString() == "Option") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Option; } } if (Worksheet.Cells[1, col].Value.ToString() == "Option value") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Option_value; } } if (Worksheet.Cells[1, col].Value.ToString() == "Price") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Price; } } if (Worksheet.Cells[1, col].Value.ToString() == "Manufacturer") { for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = items[i].Manufacturer; } } if (Worksheet.Cells[1, col].Value.ToString() == "Main image") {// "catalog/tovar/" + a.Model+".jpg" for (int i = 0; i < items.Count; i++) { Worksheet.Cells[i + 2, col, i + 2, col].Value = "catalog/tovar/" + items[i].Model + ".jpg"; } } xlPackage.Save(); } } }
public static MarketItems Import(string filename) { using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(filename))) { var Worksheet = xlPackage.Workbook.Worksheets.First(); //select sheet here var totalRows = Worksheet.Dimension.End.Row; var totalColumns = Worksheet.Dimension.End.Column; // var sb = new StringBuilder(); //this is your your data Dictionary <string, List <string> > Colls = new Dictionary <string, List <string> >(); for (int col = 1; col <= totalColumns; col++) { var Column = Worksheet.Cells[1, col, totalRows, col]. Select(x => x.Value == null ? " " : x.Value.ToString()).ToList(); Colls.Add(Column.First(), Column); } MarketItems result = new MarketItems(); for (int i = 1; i < Colls["*Model"].Count - 1; i++) { try { if ((Colls["*Model"][i] == Colls["*Model"][i - 1]) && (result.Last() != null)) { result.Last().ChildrenItems.Add(new MarketItem { Model = Convert.ToInt32(Colls["*Model"][i]), Name = Colls["*Name"][i], Description = result.Last().Description, Meta_title = result.Last().Meta_title, SEO_url = result.Last().SEO_url, Quantity = Colls["Quantity"][i], Out_stock_status = result.Last().Out_stock_status, Option = result.Last().Option, Option_value = Colls["Option value"][i], Price = Colls["Price"][i], Manufacturer = result.Last().Manufacturer, Main_image = result.Last().Main_image }); } else { result.Add(new MarketItem { Model = Convert.ToInt32(Colls["*Model"][i]), Name = Colls["*Name"][i], Description = Colls["Description"][i], Meta_title = Colls["Meta title"][i], SEO_url = Colls["SEO url"][i], Quantity = Colls["Quantity"][i], Out_stock_status = Colls["Out stock status"][i], Option = Colls["Option"][i], Option_value = Colls["Option value"][i], Price = Colls["Price"][i], Manufacturer = Colls["Manufacturer"][i], Main_image = Colls["Main image"][i] }); } } catch { result.Add(new MarketItem()); } } return(result); } }