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