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);
}
}