Hello,
What is Export to Excel Button for in Manage Products Panel.
if it is to update inventory then how do we use it, is there an excel template available to use.
Regards
Export to Excel
- 39
- 205
- 3.09.2021
- India
> Orders list export with extra dynamic component form fields list
> Two List Export Order with static and Form Field with dynamic
> after order list generated all column with static fetch all dynamic form field data and export into a excel
>Order static columns created
>form Fields dynamic columns created and header text fetching from form field table caption(Text Prompt)
--------------------------------------------------------------
public virtual byte[] ExportOrdersDetailsToXlsx(IList<OrderExport> orders)
{
//lambda expressions for choosing correct order address
//Address orderAddress(OrderExport o) => _addressService.GetAddressById((o.PickupInStore ? o.PickupAddressId : o.ShippingAddressId) ?? 0);
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
ws.Cells[1, 1].Value = "Customer Email";
ws.Cells[1, 2].Value = "Customer";
ws.Cells[1, 3].Value = "Class";
ws.Cells[1, 4].Value = "Date";
ws.Cells[1, 5].Value = "Customer's Memo";
ws.Cells[1, 6].Value = "Item - Line Level";
ws.Cells[1, 7].Value = "Item Display Name - Line Level";
ws.Cells[1, 8].Value = "Color - Line Level";
ws.Cells[1, 9].Value = "Size - Line Level";
ws.Cells[1, 10].Value = "Discription - Line Level";
ws.Cells[1, 11].Value = "Quantity - Line Level";
ws.Cells[1, 12].Value = "Price - Line Level";
ws.Cells[1, 13].Value = "Shipping Method";
ws.Cells[1, 14].Value = "Shipping Country";
ws.Cells[1, 15].Value = "Address";
ws.Cells[1, 16].Value = "News letter Subscription";
ws.Cells[1, 17].Value = "Sms Subscription";
ws.Cells[1, 18].Value = "Mail Subscription";
//form fields cloumns generate with caption
var formFields = _formFieldService.GetFormFieldAttributeMappingsByStoreId(_storeContext.CurrentStore.Id);
var formFieldColCount = 19;
foreach (var formField in formFields)
{
if (formFieldColCount > formFields.Count + 19)
break;
if (formField.AttributeControlTypeId != (int)FormFieldAttributeControlType.FileUpload)
ws.Cells[1, formFieldColCount].Value = _formFieldService.GetFormFieldAttributeById(formField.Id).TextPrompt;
formFieldColCount++;
}
var orderRowStart = 2;
foreach (var order in orders)
{
ws.Cells[string.Format("A{0}", orderRowStart)].Value = _customerService.GetCustomerById(order.CustomerId)?.Email;
ws.Cells[string.Format("B{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceByStoreId(order.StoreId).NetsuitCustomer;
ws.Cells[string.Format("C{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceByStoreId(order.StoreId).NetsuitClass;
ws.Cells[string.Format("D{0}", orderRowStart)].Value = order.CreatedOnUtc.ToString("dd-MM-yyyy");
ws.Cells[string.Format("E{0}", orderRowStart)].Value = order.Id;
ws.Cells[string.Format("F{0}", orderRowStart)].Value = _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Sku + " : " + _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Sku + " - " + _productAttributeParser.ParseProductAttributeValues(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).AttributesXml, _productAttributeService.GetProductAttributeExport(order.ProductId, ProductAttributeDefaults.Size)?.Id ?? int.MaxValue).FirstOrDefault()?.Name ?? string.Empty;
ws.Cells[string.Format("G{0}", orderRowStart)].Value = _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Name;
ws.Cells[string.Format("H{0}", orderRowStart)].Value = _productAttributeParser.ParseProductAttributeValues(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).AttributesXml, _productAttributeService.GetProductAttributeExport(order.ProductId, ProductAttributeDefaults.Color)?.Id ?? int.MaxValue).FirstOrDefault()?.Name ?? string.Empty;
ws.Cells[string.Format("I{0}", orderRowStart)].Value = _productAttributeParser.ParseProductAttributeValues(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).AttributesXml, _productAttributeService.GetProductAttributeExport(order.ProductId, ProductAttributeDefaults.Size)?.Id ?? int.MaxValue).FirstOrDefault()?.Name ?? string.Empty; ;
ws.Cells[string.Format("J{0}", orderRowStart)].Value = _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Name;
ws.Cells[string.Format("K{0}", orderRowStart)].Value = _orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Quantity;
ws.Cells[string.Format("L{0}", orderRowStart)].Value = order.OrderTotal;
ws.Cells[string.Format("M{0}", orderRowStart)].Value = order.ShippingMethod;
ws.Cells[string.Format("N{0}", orderRowStart)].Value = _countryService.GetCountryByAddress(_addressService.GetAddressById((order.PickupInStore ? order.PickupAddressId : order.ShippingAddressId) ?? 0))?.Name ?? string.Empty;
ws.Cells[string.Format("O{0}", orderRowStart)].Value = string.Empty;
ws.Cells[string.Format("P{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceValue(_orderService.GetOrderByGuid(order.OrderGuid).OrderGuid, order.CustomerId, order.StoreId)?.ShowEmail ?? false;
ws.Cells[string.Format("Q{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceValue(_orderService.GetOrderByGuid(order.OrderGuid).OrderGuid, order.CustomerId, order.StoreId)?.ShowSMS ?? false;
ws.Cells[string.Format("R{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceValue(_orderService.GetOrderByGuid(order.OrderGuid).OrderGuid, order.CustomerId, order.StoreId)?.ShowMailOffers ?? false;
orderRowStart++;
}
//form fields row generate with data
int orderCount = 2;
int formFieldRowStart = 19;
foreach (var order in orders)
{
var formFieldsData = _formFieldService.GetFormFieldAttributeMappingsByStoreId(order.StoreId);
foreach (var formField in formFieldsData)
{
var formFieldAttributeValueMapping = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueMappingData(order.CustomerId, formField.Id, order.StoreId).FirstOrDefault()?.FormFieldAttributeValue;
if (formField.AttributeControlTypeId == (int)FormFieldAttributeControlType.DropdownList)
ws.Cells[orderCount, formFieldRowStart].Value = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueById(Convert.ToInt32(formFieldAttributeValueMapping))?.Name ?? string.Empty;
else if (formField.AttributeControlTypeId != (int)FormFieldAttributeControlType.FileUpload)
ws.Cells[orderCount, formFieldRowStart].Value = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueMappingData(order.CustomerId, formField.Id, order.StoreId).FirstOrDefault()?.FormFieldAttributeValue;
formFieldRowStart++;
}
orderCount++;
formFieldRowStart = 19;
}
ws.Cells["A:AZ"].AutoFitColumns();
return pck.GetAsByteArray();
--------------------------------------------------------------------
Controller
------------------
public virtual IActionResult ExportOrderBacthExcelAll(OrderSearchModel model)
{
if (!_permissionService.Authorize(StandardPermissionProvider.ManageOrders))
return AccessDeniedView();
//load orders
var orders = _orderService.GetBatchExportOrders(model.BatchId, model.StoreId);
//ensure that we at least one order selected
if (!orders.Any())
{
_notificationService.ErrorNotification(_localizationService.GetResource("Admin.Orders.NoOrders"));
return RedirectToAction("OrderBatching", new { storeId = model.StoreId });
}
try
{
var bytes = _exportManager.ExportOrdersDetailsToXlsx(orders);
return File(bytes, MimeTypes.TextXlsx, "Batch.xlsx");
}
catch (Exception exc)
{
_notificationService.ErrorNotification(exc);
return RedirectToAction("OrderBatching", new { storeId = model.StoreId });
}
}
- 39
- 205
- 3.09.2021
- India
--export orders with extra attribute fields
--add new feature of checkbox component check to print checkboxes data
--Coumn header bold and italic
--add new feature of checkbox component check to print checkboxes data
--Coumn header bold and italic
public virtual byte[] ExportOrdersDetailsToXlsx(IList<OrderExport> orders)
{
//lambda expressions for choosing correct order address
//Address orderAddress(OrderExport o) => _addressService.GetAddressById((o.PickupInStore ? o.PickupAddressId : o.ShippingAddressId) ?? 0);
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Report");
//style header
ws.Cells["A1:AZ1"].Style.Font.Bold = true;
ws.Cells["A1:AZ1"].Style.Font.Italic = true;
ws.Cells[1, 1].Value = "Customer Email";
ws.Cells[1, 2].Value = "Customer";
ws.Cells[1, 3].Value = "Class";
ws.Cells[1, 4].Value = "Date";
ws.Cells[1, 5].Value = "Customer's Memo";
ws.Cells[1, 6].Value = "Item - Line Level";
ws.Cells[1, 7].Value = "Item Display Name - Line Level";
ws.Cells[1, 8].Value = "Color - Line Level";
ws.Cells[1, 9].Value = "Size - Line Level";
ws.Cells[1, 10].Value = "Discription - Line Level";
ws.Cells[1, 11].Value = "Quantity - Line Level";
ws.Cells[1, 12].Value = "Price - Line Level";
ws.Cells[1, 13].Value = "Shipping Method";
ws.Cells[1, 14].Value = "Shipping Country";
ws.Cells[1, 15].Value = "Address";
ws.Cells[1, 16].Value = "News letter Subscription";
ws.Cells[1, 17].Value = "Sms Subscription";
ws.Cells[1, 18].Value = "Mail Subscription";
ws.Cells[1, 19].Value = "Order Status";
//form fields cloumns generate with caption
var formFields = _formFieldService.GetFormFieldAttributeMappingsByStoreId(orders.Select(x => x.StoreId).FirstOrDefault());
var formFieldColCount = 20;
foreach (var formField in formFields)
{
if (formFieldColCount > formFields.Count + 20)
break;
if (formField.AttributeControlTypeId != (int)FormFieldAttributeControlType.FileUpload)
ws.Cells[1, formFieldColCount].Value = _formFieldService.GetFormFieldAttributeById(formField.Id).TextPrompt;
formFieldColCount++;
}
var orderRowStart = 2;
foreach (var order in orders)
{
ws.Cells[string.Format("A{0}", orderRowStart)].Value = _customerService.GetCustomerById(order.CustomerId)?.Email;
ws.Cells[string.Format("B{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceByStoreId(order.StoreId).NetsuitCustomer;
ws.Cells[string.Format("C{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceByStoreId(order.StoreId).NetsuitClass;
ws.Cells[string.Format("D{0}", orderRowStart)].Value = order.CreatedOnUtc.ToString("dd-MM-yyyy");
ws.Cells[string.Format("E{0}", orderRowStart)].Value = order.Id;
ws.Cells[string.Format("F{0}", orderRowStart)].Value = _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Sku + " : " + _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Sku + " - " + _productAttributeParser.ParseProductAttributeValues(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).AttributesXml, _productAttributeService.GetProductAttributeExport(order.ProductId, ProductAttributeDefaults.Size)?.Id ?? int.MaxValue).FirstOrDefault()?.Name ?? string.Empty;
ws.Cells[string.Format("G{0}", orderRowStart)].Value = _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Name;
ws.Cells[string.Format("H{0}", orderRowStart)].Value = _productAttributeParser.ParseProductAttributeValues(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).AttributesXml, _productAttributeService.GetProductAttributeExport(order.ProductId, ProductAttributeDefaults.Color)?.Id ?? int.MaxValue).FirstOrDefault()?.Name ?? string.Empty;
ws.Cells[string.Format("I{0}", orderRowStart)].Value = _productAttributeParser.ParseProductAttributeValues(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).AttributesXml, _productAttributeService.GetProductAttributeExport(order.ProductId, ProductAttributeDefaults.Size)?.Id ?? int.MaxValue).FirstOrDefault()?.Name ?? string.Empty; ;
ws.Cells[string.Format("J{0}", orderRowStart)].Value = _orderService.GetProductByOrderItemId(_orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Id).Name;
ws.Cells[string.Format("K{0}", orderRowStart)].Value = _orderService.GetOrderItemByOrderId(order.Id, order.ProductId).Quantity;
ws.Cells[string.Format("L{0}", orderRowStart)].Value = order.OrderTotal;
ws.Cells[string.Format("M{0}", orderRowStart)].Value = order.ShippingMethod;
ws.Cells[string.Format("N{0}", orderRowStart)].Value = _countryService.GetCountryByAddress(_addressService.GetAddressById((order.PickupInStore ? order.PickupAddressId : order.ShippingAddressId) ?? 0))?.Name ?? string.Empty;
ws.Cells[string.Format("O{0}", orderRowStart)].Value = _addressService.GetAddressById((order.PickupInStore ? order.PickupAddressId : order.ShippingAddressId) ?? 0)?.Address1;
ws.Cells[string.Format("P{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceValue(_orderService.GetOrderByGuid(order.OrderGuid).OrderGuid, order.CustomerId, order.StoreId)?.ShowEmail ?? false;
ws.Cells[string.Format("Q{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceValue(_orderService.GetOrderByGuid(order.OrderGuid).OrderGuid, order.CustomerId, order.StoreId)?.ShowSMS ?? false;
ws.Cells[string.Format("R{0}", orderRowStart)].Value = _subscriptionPreferenceService.GetSubscriptionPreferenceValue(_orderService.GetOrderByGuid(order.OrderGuid).OrderGuid, order.CustomerId, order.StoreId)?.ShowMailOffers ?? false;
ws.Cells[string.Format("S{0}", orderRowStart)].Value = (OrderStatusenum)order.OrderStatusId;
orderRowStart++;
}
//form fields row generate with data
int orderCount = 2;
int formFieldRowStart = 20;
foreach (var order in orders)
{
var formFieldsData = _formFieldService.GetFormFieldAttributeMappingsByStoreId(order.StoreId);
foreach (var formField in formFieldsData)
{
//get and sets record from dropdown and radiolist
var formFieldAttributeValueMapping = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueMappingData(order.CustomerId, formField.Id, order.StoreId).FirstOrDefault()?.FormFieldAttributeValue;
if (formField.AttributeControlTypeId == (int)FormFieldAttributeControlType.DropdownList ||
formField.AttributeControlTypeId == (int)FormFieldAttributeControlType.RadioList)
ws.Cells[orderCount, formFieldRowStart].Value = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueById(Convert.ToInt32(formFieldAttributeValueMapping))?.Name ?? string.Empty;
//get and sets record from checkboxes
else if (formField.AttributeControlTypeId == (int)FormFieldAttributeControlType.Checkboxes)
{
if (formFieldAttributeValueMapping != null)
{
var ids = formFieldAttributeValueMapping.Split(',');
var selectedIds = Array.ConvertAll(ids, s => int.Parse(s));
var formFieldAttributes = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueByIds(selectedIds).Select(x => x.Name);
ws.Cells[orderCount, formFieldRowStart].Value = string.Join(", ", formFieldAttributes);
}
else
ws.Cells[orderCount, formFieldRowStart].Value = string.Empty;
}
//get and sets record from Textboxes and MultilineTextbox
else if (formField.AttributeControlTypeId == (int)FormFieldAttributeControlType.TextBox ||
formField.AttributeControlTypeId == (int)FormFieldAttributeControlType.MultilineTextbox)
ws.Cells[orderCount, formFieldRowStart].Value = _formFieldAttributeValueMappingService.GetFormFieldAttributeValueMappingData(order.CustomerId, formField.Id, order.StoreId).FirstOrDefault()?.FormFieldAttributeValue;
formFieldRowStart++;
}
orderCount++;
formFieldRowStart = 20;
}
ws.Cells["A:AZ"].AutoFitColumns();
return pck.GetAsByteArray();
}