Hi,
In my Blazor Server application, I am generating an excel from a grid as follows. I couldn't manage how to add a drop-down for Payment Status with EPPlus. This drop-down has "Pending Payment", and "Paid" options. Since this is generated from the grid, the selected value for the payment status should be selected on the grid as well.
Is there a way to do it?
private async Task Export()
{
filter = _grid.View;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var stream = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
var workSheet = package.Workbook.Worksheets.Add("Report");
workSheet.Protection.IsProtected = true;
var recordIndex = 2;
workSheet.Row(1).Style.Font.Bold = true;
var headerCells = workSheet.Cells["A1:Z1"];
headerCells.Style.Font.Bold = true;
headerCells.Style.Font.Size = 13;
headerCells.Style.Border.BorderAround(ExcelBorderStyle.Thin);
headerCells.Style.Border.Top.Style = ExcelBorderStyle.Thin;
headerCells.Style.Border.Left.Style = ExcelBorderStyle.Thin;
headerCells.Style.Border.Right.Style = ExcelBorderStyle.Thin;
headerCells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
headerCells.Style.Font.Color.SetColor(System.Drawing.Color.Black);
headerCells = workSheet.Cells["A1:E1"];
// Set their background color to DarkBlue.
headerCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
headerCells = workSheet.Cells["F1:Z1"];
// Set their background color to DarkBlue.
headerCells.Style.Fill.PatternType = ExcelFillStyle.Solid;
headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
workSheet.Cells[1, 1].Value = "Order Id";
workSheet.Cells[1, 2].Value = "Customer";
workSheet.Cells[1, 3].Value = "Order Date";
workSheet.Cells[1, 4].Value = "Order Status";
workSheet.Cells[1, 5].Value = "DoneBy";
workSheet.Cells[1, 6].Value = "Product ID";
workSheet.Cells[1, 7].Value = "Product Code";
//workSheet.Cells[1, 8].Value = "Product";
workSheet.Cells[1, 8].Value = "Vendor";
workSheet.Cells[1, 9].Value = "Warehouse";
workSheet.Cells[1, 10].Value = "Quantity";
workSheet.Cells[1, 11].Value = "Buy Unit Price";
workSheet.Cells[1, 12].Value = "Cost Ratio";
workSheet.Cells[1, 13].Value = "Unit Cost";
workSheet.Cells[1, 14].Value = "Total Buy Price";
workSheet.Cells[1, 15].Value = "Sell Unit Price";
workSheet.Cells[1, 16].Value = "Total Sell Price";
workSheet.Cells[1, 17].Value = "Total Unit Cost";
workSheet.Cells[1, 18].Value = "Order Detail Status";
workSheet.Cells[1, 19].Value = "Tracking Number";
workSheet.Cells[1, 20].Value = "Payment Status";
workSheet.Cells[1, 21].Value = "Currency";
workSheet.Cells[1, 22].Value = "Customer Stock Code";
workSheet.Cells[1, 23].Value = "Customer Order Number";
workSheet.Cells[1, 24].Value = "Completion Date";
workSheet.Cells[1, 25].Value = "Customer Id";
workSheet.Cells[1, 26].Value = "Vendor Id";
// Set up the list of payment statuses for the drop-down
string[] paymentStatuses = { "Paid", "Pending Payment" };
string paymentStatusList = string.Join(",", paymentStatuses);
foreach (var order in filter)
{
workSheet.Cells[recordIndex, 1].Value = order.OrderId;
workSheet.Cells[recordIndex, 1].Style.Font.Bold = true;
workSheet.Cells[recordIndex, 2].Value = order.CustomerName;
workSheet.Cells[recordIndex, 3].Value = order.OrderDateTime.ToShortDateString();
workSheet.Cells[recordIndex, 4].Value = order.Status;
workSheet.Cells[recordIndex, 5].Value = order.DoneBy;
workSheet.Cells[recordIndex, 6].Value = order.OrderDetailId;
workSheet.Cells[recordIndex, 6].Style.Font.Bold = true;
workSheet.Cells[recordIndex, 7].Value = order.ProductCode;
workSheet.Cells[recordIndex, 7].Style.Locked = false;
workSheet.Cells[recordIndex, 8].Value = order.VendorName;
workSheet.Cells[recordIndex, 8].Style.Locked = false;
workSheet.Cells[recordIndex, 9].Value = order.Warehouse;
workSheet.Cells[recordIndex, 9].Style.Locked = false;
workSheet.Cells[recordIndex, 10].Value = order.Quantity;
workSheet.Cells[recordIndex, 10].Style.Locked = false;
workSheet.Cells[recordIndex, 11].Value = order.BuyUnitPrice;
workSheet.Cells[recordIndex, 11].Style.Locked = false;
workSheet.Cells[recordIndex, 12].Value = order.CostRatio;
workSheet.Cells[recordIndex, 12].Style.Locked = false;
workSheet.Cells[recordIndex, 13].Value = order.UnitCost;
workSheet.Cells[recordIndex, 13].Style.Locked = false;
workSheet.Cells[recordIndex, 14].Value = order.TotalBuyPrice;
workSheet.Cells[recordIndex, 14].Style.Locked = false;
workSheet.Cells[recordIndex, 15].Value = order.SellUnitPrice;
workSheet.Cells[recordIndex, 15].Style.Locked = false;
workSheet.Cells[recordIndex, 16].Value = order.TotalSellPrice;
workSheet.Cells[recordIndex, 16].Style.Locked = false;
workSheet.Cells[recordIndex, 17].Value = order.TotalUnitCost;
workSheet.Cells[recordIndex, 17].Style.Locked = false;
workSheet.Cells[recordIndex, 18].Value = order.OrderDetailStatus;
workSheet.Cells[recordIndex, 18].Style.Locked = false;
workSheet.Cells[recordIndex, 19].Value = order.TrackingNumber;
workSheet.Cells[recordIndex, 19].Style.Locked = false;
workSheet.Cells[recordIndex, 20].Value = order.PaymentStatus;
workSheet.Cells[recordIndex, 20].Style.Locked = false;
workSheet.Cells[recordIndex, 21].Value = order.Currency;
workSheet.Cells[recordIndex, 22].Value = order.CustomerStockCode;
workSheet.Cells[recordIndex, 22].Style.Locked = false;
workSheet.Cells[recordIndex, 23].Value = order.CustomerOrderNumber;
workSheet.Cells[recordIndex, 23].Style.Locked = false;
workSheet.Cells[recordIndex, 24].Value = order.CompletionDateTime.ToString();
workSheet.Cells[recordIndex, 25].Value = order.CustomerId;
workSheet.Cells[recordIndex, 26].Value = order.VendorId;
recordIndex++;
}
//Make all text fit the cells
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
await package.SaveAsync();
}
stream.Position = 0;
var excelName = $"ReportList-{DateTime.Now.ToString("ddMMyyyyHHmm")}.xlsx";
using var streamRef = new DotNetStreamReference(stream);
await JS.InvokeVoidAsync("downloadFileFromStream", excelName, streamRef);
}