Share via

How to add drop down to an excel in EPPLUS

Cenk 991 Reputation points
2023-02-27T08:49:32.52+00:00

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);
    }
Blazor
Blazor
A free and open-source web framework that enables developers to create web apps using C# and HTML being developed by Microsoft.
1,500 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,695 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,650 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,720 questions
0 comments
Accepted answer
  1. Pritesh Gajjar 75 Reputation points
    2023-02-27T09:34:23.3866667+00:00
    1. Create a list of items for the drop-down:
    var paymentStatusList = new List<string> { "Pending Payment", "Paid" };
    1. Define the range where the drop-down will be placed:
    var paymentStatusCell = worksheet.Cells["D2"];
var paymentStatusRange = worksheet.Cells["D2:D" + (rowCount + 1)];
    1. Add data validation to the range:
    var validation = paymentStatusRange.DataValidation.AddListDataValidation(); validation.Formula.ExcelFormula = "\"" + string.Join(",", paymentStatusList) + "\"";
    1. Set the default value for the drop-down:
    paymentStatusCell.Value = "Pending Payment";
    1. 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.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest