public byte[] GenerateDistributorUpdateExcel(IList <DistributorUpdate> distUpdates, DateTime startDate, DateTime endDate)
{
byte[] excelData = new byte[] { };
if (distUpdates.Count > 0)
{
using (var excelStream = new MemoryStream())
{
using (var inputStream = new MemoryStream())
{
// Copy data to memory to avoid locking file for a long time
using (var stream = new FileStream(_webHelper.DistributorUpdateExcelTemplate, FileMode.Open))
{
stream.CopyTo(inputStream);
}
// Get excel param
var excelParams = _excelParamManager.GetExcelParams(ExcelExportType.DISTRIBUTOR_UPDATES);
if (excelParams == null)
{
throw new ABOException("Excel params are not found. Please check ExcelParams configuration!");
}
int tableStartRow = Convert.ToInt32(excelParams["TableStartRow"]);
string cellExportTime = excelParams["CellExportTime"];
string cellRecordCount = excelParams["CellRecordCount"];
string cellSearchDate = excelParams["CellSearchDate"];
int colDistNumber = Convert.ToInt32(excelParams["ColDistNumber"]);
int colDistName = Convert.ToInt32(excelParams["ColDistName"]);
int colJoinDate = Convert.ToInt32(excelParams["ColJoinDate"]);
int colExpiryDate = Convert.ToInt32(excelParams["ColExpiryDate"]);
int colUpdatedType = Convert.ToInt32(excelParams["ColUpdatedType"]);
using (var xlPackage = new ExcelPackage(excelStream, inputStream))
{
int row = tableStartRow;
var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
// Export time
worksheet.Cells[cellExportTime].Value = DateTime.Now.ToString(_webHelper.DateTimeFormat);
// Record count
worksheet.Cells[cellRecordCount].Value = distUpdates.Count;
// Search date
worksheet.Cells[cellSearchDate].Value = string.Format("{0} - {1}", _webHelper.GetDateString(startDate), _webHelper.GetDateString(endDate));
// Insert new empty row
worksheet.InsertRow(row, distUpdates.Count - 1, row);
foreach (var distUpdate in distUpdates)
{
worksheet.Cells[row, colDistNumber].Value = distUpdate.DistNumber;
worksheet.Cells[row, colDistName].Value = distUpdate.Distributor.Name;
worksheet.Cells[row, colJoinDate].Value = _webHelper.GetDateString(distUpdate.Distributor.JoinDate);
worksheet.Cells[row, colExpiryDate].Value = _webHelper.GetDateString(distUpdate.Distributor.ExpiryDate);
worksheet.Cells[row, colUpdatedType].Value = distUpdate.UpdatedType;
row++;
}
xlPackage.Save();
}
excelData = excelStream.ToArray();
}
}
}
return(excelData);
}
private ProductImportData GetProductListFromSheet(ExcelWorksheet sheet)
{
var productList = new List <Product>();
var categoryDictionary = new Dictionary <string, ProductCategory>();
var excelParams = _excelParamManager.GetExcelParams(ExcelImportTypes.ProductList);
int colSku = Convert.ToInt32(excelParams["ColSku"]);
int colPv = Convert.ToInt32(excelParams["ColPV"]);
int colPrice = Convert.ToInt32(excelParams["ColPrice"]);
int colNetWeight = Convert.ToInt32(excelParams["ColNetWeight"]);
int colDescriptionVi = Convert.ToInt32(excelParams["ColDescriptionVi"]);
int colDescriptionEn = Convert.ToInt32(excelParams["ColDescriptionEn"]);
int colNote = Convert.ToInt32(excelParams["ColNote"]);
int colIsActive = Convert.ToInt32(excelParams["ColIsActive"]);
int startRow = Convert.ToInt32(excelParams["RowStart"]);
string currentCategory = string.Empty;
for (int rowIndex = startRow; ; rowIndex++)
{
var sku = GetCellValue <string>(sheet, rowIndex, colSku);
var pv = GetCellValue <double>(sheet, rowIndex, colPv);
var price = GetCellValue <decimal>(sheet, rowIndex, colPrice);
var netWeight = GetCellValue <string>(sheet, rowIndex, colNetWeight);
var desciptionVi = GetCellValue <string>(sheet, rowIndex, colDescriptionVi);
var descriptionEn = GetCellValue <string>(sheet, rowIndex, colDescriptionEn);
var note = GetCellValue <string>(sheet, rowIndex, colNote);
var isActive = (GetCellValue <string>(sheet, rowIndex, colIsActive) ?? string.Empty).Equals("yes", StringComparison.OrdinalIgnoreCase);
if (string.IsNullOrEmpty(sku)) // empty row, break
{
break;
}
if (string.IsNullOrEmpty(desciptionVi)) // category row
{
currentCategory = sku;
categoryDictionary[currentCategory] = new ProductCategory()
{
Name = currentCategory
};
}
else
{
productList.Add(new Product()
{
Sku = sku,
Name = desciptionVi,
NameEn = descriptionEn,
Description = note,
PV = pv,
Price = price,
NetWeight = netWeight,
ProductCategory = categoryDictionary[currentCategory],
IsActive = isActive,
CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now
});
}
}
return(new ProductImportData(productList, categoryDictionary.Select(x => x.Value).ToList()));
}
