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