/// <summary>
/// Write all of the given products to the given .xls file.
/// </summary>
/// <param name="fileName">Full path to the .xls file to write to.</param>
/// <param name="products">The products to be written.</param>
public void SaveProducts(string fileName, ref ProductsEntity products)
{
/*******************************************************
* CONSTANTS
* ------------------------------------------------------
* (none)
********************************************************/
ExcelDAO excelDAO;
// Write the products to the given file.
excelDAO = new ExcelDAO(fileName);
excelDAO.Write(ref products);
}
/// <summary>
/// Write the product data to Excel file.
/// </summary>
/// <param name="products">Products to be written.</param>
public void Write(ref ProductsEntity products)
{
/*******************************************************
* CONSTANTS
* ------------------------------------------------------
* (none)
********************************************************/
Excel.Application excelApp; // Excel application reference.
Excel.Workbook wb; // Excel workbook reference.
Excel.Worksheet sh; // Excel worksheet reference.
int row; // Row index.
int specCount; // Product specification count.
int crumbCount; // Crumb segment count.
Dictionary<string, int> specIndexes; // Specification indexes.
int columnCount; // Total number of columns to be written (minus unknown specification count).
int columnIndex; // Excel file column write index.
try
{
// Fire up Excel!
excelApp = new Excel.Application();
}
catch (Exception ex)
{
// Re-throw exception with details.
throw new Exception("Error creating excel: " + ex.Message);
}
// Instantiate / initialize.
wb = excelApp.Workbooks.Add();
sh = wb.Sheets.Add();
row = 1;
sh.Name = "Data";
specCount = products.SpecificationLabels.Count();
specIndexes = new Dictionary<string, int>(specCount);
crumbCount = products.MaxCrumbCount;
columnCount = Enum.GetNames(typeof(Columns)).Length;
columnIndex = columnCount + crumbCount;
try
{
// Iterate over each crumb segment.
for (int i = 0; i < crumbCount; i++)
{
/*******************************************************
* CONSTANTS
* ------------------------------------------------------
* (none)
********************************************************/
string value; // Pretty column labels for the product categories (aka crumb).
switch (i)
{
case 0:
value = "Product Category";
break;
case 1:
value = "Product Type";
break;
default:
value = "Product Sub Type";
break;
}
// Write the column header.
sh.Cells[row, i + 1].Value2 = value;
}
// Add the default columns.
sh.Cells[row, (int)Columns.CATEGORY + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.CATEGORY);
sh.Cells[row, (int)Columns.SKU + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.SKU);
sh.Cells[row, (int)Columns.SHORT_DESC + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.SHORT_DESC);
sh.Cells[row, (int)Columns.PRICE + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.PRICE);
sh.Cells[row, (int)Columns.DESC + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.DESC);
sh.Cells[row, (int)Columns.IMAGE_URL + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.IMAGE_URL);
sh.Cells[row, (int)Columns.MANU_IMAGE_URL + crumbCount].Value2 = EnumUtils<Columns>.GetDescription(Columns.MANU_IMAGE_URL);
// Iterate over all unique product specification labels and add as column header.
foreach (var item in products.SpecificationLabels)
{
sh.Cells[row, ++columnIndex].Value2 = item;
specIndexes.Add(item, columnIndex);
}
// Move to first data row.
row++;
// Iterate over all products and write to file.
foreach (var product in products.Items)
{
// Write all crumb segments.
for (int i = 0; i < product.Crumbs.Count; i++)
{
sh.Cells[row, i + 1].Value2 = product.Crumbs[i];
}
// Write standard data.
sh.Cells[row, (int)Columns.CATEGORY + crumbCount].Value2 = product.Category;
sh.Cells[row, (int)Columns.SKU + crumbCount].Value2 = product.SKU;
sh.Cells[row, (int)Columns.SHORT_DESC + crumbCount].Value2 = product.ShortDescription;
sh.Cells[row, (int)Columns.PRICE + crumbCount].Value2 = product.Price;
sh.Cells[row, (int)Columns.DESC + crumbCount].Value2 = product.Description;
sh.Cells[row, (int)Columns.IMAGE_URL + crumbCount].Value2 = product.ImageUrl;
sh.Cells[row, (int)Columns.MANU_IMAGE_URL + crumbCount].Value2 = product.ManufacturerImageUrl;
// Iterate over all of the product's specifications and write to the correct column.
foreach (var item in product.Specifications)
{
/*******************************************************
* CONSTANTS
* ------------------------------------------------------
* (none)
********************************************************/
int index; // Index of the cell.
// Get the index of the column that the specification should be written to.
index = specIndexes[item.Label];
// Write to the correct cell.
sh.Cells[row, index].Value2 = item.Value;
}
// Move to next data row.
row++;
}
}
catch (Exception ex)
{
// Re-throw with details.
throw new Exception("Excel write error: " + ex.Message);
}
finally
{
// Save the file and clean up.s
wb.SaveAs(_fileName);
wb.Close();
excelApp.Quit();
}
}
