Hi @enchev,
I am exporting a master-detail data grid as follows. But the problem is filter = _grid.View; does not bring the filtered data. As an example there is 1 order and 2 details depending on this order. I am filtering the detail and now it is 1 order and 1 detail for this order. But unfortunately, it still brings the all details. What could i be missing? (I didn't add master-detail data grid to avoid confusion.)
...
IQueryable<Order?> filter;
...
private async Task ExportExcel()
{
filter = _grid.View;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var stream = new MemoryStream();
using (var package = new ExcelPackage(stream))
{
var workSheet = package.Workbook.Worksheets.Add("Orders");
var recordIndex = 2;
workSheet.Row(1).Style.Font.Bold = true;
var headerCells = workSheet.Cells["A1:U1"];
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 = OfficeOpenXml.Style.ExcelFillStyle.Solid;
headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
headerCells = workSheet.Cells["F1:U1"];
// Set their background color to DarkBlue.
headerCells.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
headerCells.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
workSheet.Cells[1, 1].Value = "Order Id";
workSheet.Cells[1, 2].Value = "Order Date";
workSheet.Cells[1, 3].Value = "Status";
workSheet.Cells[1, 4].Value = "Customer";
workSheet.Cells[1, 5].Value = "DoneBy";
workSheet.Cells[1, 6].Value = "ID";
workSheet.Cells[1, 7].Value = "Product Code";
workSheet.Cells[1, 8].Value = "Product";
workSheet.Cells[1, 9].Value = "Vendor";
workSheet.Cells[1, 10].Value = "Quantity";
workSheet.Cells[1, 11].Value = "Cost Ratio";
workSheet.Cells[1, 12].Value = "Currency";
workSheet.Cells[1, 13].Value = "Unit Cost";
workSheet.Cells[1, 14].Value = "Buy Unit Price";
workSheet.Cells[1, 15].Value = "Total Buy Price";
workSheet.Cells[1, 16].Value = "Sell Unit Price";
workSheet.Cells[1, 17].Value = "Total Sell Price";
workSheet.Cells[1, 18].Value = "Status";
workSheet.Cells[1, 19].Value = "Shipping Number";
workSheet.Cells[1, 20].Value = "Tracking Number";
workSheet.Cells[1, 21].Value = "Description";
foreach (var order in filter)
{
workSheet.Cells[recordIndex, 1].Value = order.Id;
workSheet.Cells[recordIndex, 1].Style.Font.Bold = true;
workSheet.Cells[recordIndex, 2].Value = order.OrderDateTime.ToShortDateString();
workSheet.Cells[recordIndex, 3].Value = order.Status;
workSheet.Cells[recordIndex, 4].Value = order.Customer.Name;
workSheet.Cells[recordIndex, 5].Value = order.DoneBy;
var quantity = 0;
foreach (var orderDetails in order.OrderDetails)
{
workSheet.Cells[recordIndex, 6].Value = orderDetails.Id;
workSheet.Cells[recordIndex, 6].Style.Font.Bold = true;
workSheet.Cells[recordIndex, 7].Value = orderDetails.ProductCode;
workSheet.Cells[recordIndex, 8].Value = orderDetails.ProductName;
workSheet.Cells[recordIndex, 9].Value = orderDetails.Vendor.Name;
workSheet.Cells[recordIndex, 10].Value = orderDetails.Quantity;
workSheet.Cells[recordIndex, 11].Value = orderDetails.CostRatio;
workSheet.Cells[recordIndex, 12].Value = orderDetails.Currency;
workSheet.Cells[recordIndex, 13].Value = (orderDetails.BuyUnitPrice * (orderDetails.CostRatio/100)) + orderDetails.BuyUnitPrice;
workSheet.Cells[recordIndex, 14].Value = orderDetails.BuyUnitPrice;
workSheet.Cells[recordIndex, 15].Value = orderDetails.BuyUnitPrice * orderDetails.Quantity;
workSheet.Cells[recordIndex, 16].Value = orderDetails.SellUnitPrice;
workSheet.Cells[recordIndex, 17].Value = orderDetails.Quantity * orderDetails.SellUnitPrice;
workSheet.Cells[recordIndex, 18].Value = orderDetails.Status;
workSheet.Cells[recordIndex, 19].Value = orderDetails.ShippingNumber;
workSheet.Cells[recordIndex, 20].Value = orderDetails.TrackingNumber;
workSheet.Cells[recordIndex, 21].Value = orderDetails.Description;
quantity += orderDetails.Quantity;
recordIndex++;
}
workSheet.Cells[recordIndex, 1].Value = "Total:";
workSheet.Cells["A" + recordIndex + ":U" + recordIndex].Style.Border.Top.Style = ExcelBorderStyle.Thin;
workSheet.Cells["A" + recordIndex + ":U" + recordIndex].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
workSheet.Cells["A" + recordIndex + ":U" + recordIndex].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
workSheet.Cells[recordIndex, 10].Value = quantity;
workSheet.Cells[recordIndex, 10].Style.Font.Bold = true;
workSheet.Cells[recordIndex, 10].Style.Font.Size = 11;
recordIndex++;
}
//Make all text fit the cells
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
await package.SaveAsync();
}
stream.Position = 0;
var excelName = $"OrderList-{DateTime.Now.ToString("ddMMyyyyHHmm")}.xlsx";
using var streamRef = new DotNetStreamReference(stream: stream);
await JS.InvokeVoidAsync("downloadFileFromStream", excelName, streamRef);
}