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);
Create a list of items for the drop-down:
var paymentStatusList = new List<string> { "Pending Payment", "Paid" };
Define the range where the drop-down will be placed:
var paymentStatusCell = worksheet.Cells["D2"];
var paymentStatusRange = worksheet.Cells["D2:D" + (rowCount + 1)];
Add data validation to the range:
var validation = paymentStatusRange.DataValidation.AddListDataValidation(); validation.Formula.ExcelFormula = "\"" + string.Join(",", paymentStatusList) + "\"";
Set the default value for the drop-down:
paymentStatusCell.Value = "Pending Payment";
Set the selected value of the drop-down based on the data in your grid. For example, if you have a property called PaymentStatus on your data object, you can loop through the rows and set the selected value for each row:
for (int i = 0; i < rowCount; i++)
var row = worksheet.Row(i + 2);
var data = gridData[i];
// Set the value in the Payment Status column
row.Cells[4].Value = data.PaymentStatus;
// Set the selected value for the drop-down
var validation = row.Cells[4].DataValidation;
validation.ShowErrorMessage = true;
validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
validation.ErrorTitle = "Invalid Payment Status";
validation.Error = "Please select a valid payment status";
validation.Formula.ExcelFormula = "\"" + string.Join(",", paymentStatusList) + "\"";
validation.Value = data.PaymentStatus;
This code sets the selected value of the drop-down based on the PaymentStatus property of each row in your grid. You can modify this code to use a different property or logic for setting the selected value.
Note that you will need to add the using OfficeOpenXml.DataValidation; namespace to use the DataValidation class.
This code sets the selected value of the drop-down based on the PaymentStatus property of each row in your grid. You can modify this code to use a different property or logic for setting the selected value.
Note that you will need to add the using OfficeOpenXml.DataValidation; namespace to use the DataValidation class.
var trustedFileNameForFileStorage = Path.GetRandomFileName();
var path = Path.Combine(env.ContentRootPath,
env.EnvironmentName, "unsafe_uploads_report",
trustedFileNameForFileStorage);
await using FileStream fs = new(path, FileMode.Create);
await file.CopyToAsync(fs);
logger.LogInformation("{FileName} saved at {Path}",
trustedFileNameForDisplay, path);
//Get file
var newfile = new FileInfo(file.FileName);
var fileExtension = newfile.Extension;
//Check if file is an Excel File
if (fileExtension.Contains(".xls"))
using var ms = new MemoryStream();
await file.OpenReadStream().CopyToAsync(ms);
// If you use EPPlus in a noncommercial context
// according to the Polyform Noncommercial license:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage(ms);
var workSheet = package.Workbook.Worksheets["Report"];
var totalRows = workSheet.Dimension.Rows;
var reportList = new List<OrderDetail>();
for (var i = 2; i <= totalRows; i++)
var item = new OrderDetail
OrderId = Convert.ToInt32(workSheet.Cells[i, 1].Value),
Id = Convert.ToInt32(workSheet.Cells[i, 6].Value),
ProductCode = workSheet.Cells[i, 7].Value?.ToString(),
Warehouse = workSheet.Cells[i, 9].Value?.ToString(),
Quantity = Convert.ToInt32(workSheet.Cells[i, 10].Value),
BuyUnitPrice = Convert.ToDouble(workSheet.Cells[i, 11].Value),
CostRatio = Convert.ToDouble(workSheet.Cells[i, 12].Value),
UnitCost = Convert.ToDouble(workSheet.Cells[i, 13].Value),
TotalBuyPrice = Convert.ToDouble(workSheet.Cells[i, 13].Value),
SellUnitPrice = Convert.ToDouble(workSheet.Cells[i, 14].Value),
TotalSellPrice = Convert.ToDouble(workSheet.Cells[i, 15].Value),
TotalUnitCost = Convert.ToDouble(workSheet.Cells[i, 16].Value),
Status = workSheet.Cells[i, 18].Value.ToString(),
TrackingNumber = workSheet.Cells[i, 19].Value?.ToString(),
PaymentStatus = workSheet.Cells[i, 20].Value?.ToString(),
Currency = workSheet.Cells[i, 21].Value.ToString(),
CustomerStockCode = workSheet.Cells[i, 22]?.Value?.ToString(),
CustomerOrderNumber = workSheet.Cells[i, 23]?.Value?.ToString(),
VendorId = Convert.ToInt32(workSheet.Cells[i, 26].Value),
IsActive = 1
reportList.Add(item);
await _updateReportListUseCase.ExecuteAsync(reportList);
