Untitled
using ClosedXML.Excel; using FoxHub_TPT.Areas.TPT.Enums; using System.Data; public class ExcelUtility { public byte[] ExportToExcel(IEnumerable<BookingOrderVM> orders, IEnumerable<TruckType> truckTypes, string uom) { using (var workbook = new XLWorkbook()) { var worksheet = workbook.Worksheets.Add("Booking Portal"); var currentRow = 1; var dropdownSheet = workbook.Worksheets.Add("Dropdowns"); CreateDropdownLists(dropdownSheet, truckTypes); AddHeaders(worksheet, ref currentRow, true, uom); worksheet.Row(1).Style.Protection.SetLocked(true); foreach (var order in orders) { var orderStartRow = currentRow; AddDispatchRow(worksheet, ref currentRow, order, uom); worksheet.Row(orderStartRow).Style.Protection.SetLocked(true); AddHeaders(worksheet, ref currentRow, false, uom); worksheet.Row(currentRow - 1).Style.Protection.SetLocked(true); foreach (var booking in order.Bookings) { AddBookingRow(worksheet, ref currentRow, booking, uom, orderStartRow); } for (int i = 0; i < 3; i++) { AddBookingRow(worksheet, ref currentRow, new Booking(), uom, orderStartRow); } AddRemainingRow(worksheet, ref currentRow, orderStartRow, uom); worksheet.Row(currentRow - 1).Style.Protection.SetLocked(true); currentRow++; } AddDropdowns(worksheet, truckTypes); worksheet.Columns().AdjustToContents(); worksheet.Protect() .AllowElement(XLSheetProtectionElements.SelectLockedCells) .AllowElement(XLSheetProtectionElements.SelectUnlockedCells) .AllowElement(XLSheetProtectionElements.FormatCells) .AllowElement(XLSheetProtectionElements.InsertRows) .AllowElement(XLSheetProtectionElements.DeleteRows); using (var stream = new MemoryStream()) { workbook.SaveAs(stream); return stream.ToArray(); } } } private void AddRemainingRow(IXLWorksheet worksheet, ref int currentRow, int orderStartRow, string uom) { int column = 1; var remainingCell = worksheet.Cell(currentRow, column++); remainingCell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 218, 185); remainingCell.Style.Font.Bold = true; for (int i = column; i < 8; i++) { var cell = worksheet.Cell(currentRow, i); cell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 218, 185); cell.Style.Font.Bold = true; } worksheet.Cell(currentRow, column++).Value = "Remaining"; column = 8; foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>()) { var dispatchRowValue = worksheet.Cell(orderStartRow, column).Value.ToString(); var cell = worksheet.Cell(currentRow, column); cell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 218, 185); cell.Style.Font.Bold = true; if (dispatchRowValue != "") { var bookingsStartRow = orderStartRow + 2; var bookingsEndRow = currentRow - 1; var columnLetter = GetExcelColumnName(column); cell.FormulaA1 = $"={columnLetter}{orderStartRow}-SUM({columnLetter}{bookingsStartRow}:{columnLetter}{bookingsEndRow})"; } else { cell.Value = ""; } cell.Style.Protection.SetLocked(true); column++; } currentRow++; } private void CreateDropdownLists(IXLWorksheet dropdownSheet, IEnumerable<TruckType> truckTypes) { for (int i = 1; i <= 10; i++) { dropdownSheet.Cell(i, 1).Value = i; } dropdownSheet.Workbook.NamedRanges.Add("TripNumbers", dropdownSheet.Range(dropdownSheet.Cell(1, 1), dropdownSheet.Cell(10, 1))); int row = 1; for (int hour = 0; hour < 24; hour++) { for (int minute = 0; minute < 60; minute += 15) { var time = new DateTime(2000, 1, 1, hour, minute, 0); dropdownSheet.Cell(row, 2).Value = time; dropdownSheet.Cell(row, 2).Style.NumberFormat.Format = "HH:mm"; row++; } } dropdownSheet.Workbook.NamedRanges.Add("TimePicker", dropdownSheet.Range(dropdownSheet.Cell(1, 2), dropdownSheet.Cell(96, 2))); row = 1; foreach (var truckType in truckTypes) { dropdownSheet.Cell(row, 3).Value = truckType.Id; row++; } dropdownSheet.Workbook.NamedRanges.Add("TruckTypes", dropdownSheet.Range(dropdownSheet.Cell(1, 3), dropdownSheet.Cell(row - 1, 3))); } private void AddDropdowns(IXLWorksheet worksheet, IEnumerable<TruckType> truckTypes) { var usedRange = worksheet.RangeUsed(); var lastRow = usedRange.LastRow().RowNumber(); for (int row = 1; row <= lastRow; row++) { if (IsHeaderOrDispatchRow(worksheet.Row(row))) continue; var tripCell = worksheet.Cell(row, 3); var tripValidation = tripCell.SetDataValidation(); tripValidation.List("=TripNumbers"); var truckTypeCell = worksheet.Cell(row, 4); var truckTypeValidation = truckTypeCell.SetDataValidation(); truckTypeValidation.List("=TruckTypes"); var arrivalCell = worksheet.Cell(row, 5); var timeValidation = arrivalCell.SetDataValidation(); timeValidation.List("=TimePicker"); arrivalCell.Style.NumberFormat.Format = "HH:mm"; } } private bool IsHeaderOrDispatchRow(IXLRow row) { var firstCell = row.Cell(1); var backgroundColor = firstCell.Style.Fill.BackgroundColor; return backgroundColor == XLColor.Green || backgroundColor == XLColor.Gray; } private void AddHeaders(IXLWorksheet worksheet, ref int currentRow, bool isMainHeader, string uom) { var headers = isMainHeader ? GetMainHeaders(uom) : GetBookingHeaders(uom); for (int i = 0; i < headers.Length; i++) { var cell = worksheet.Cell(currentRow, i + 1); cell.Value = headers[i]; if (isMainHeader) { cell.Style.Fill.BackgroundColor = XLColor.Green; cell.Style.Font.FontColor = XLColor.White; } else { cell.Style.Fill.BackgroundColor = XLColor.LightGray; } cell.Style.Font.Bold = true; cell.Style.Alignment.WrapText = true; cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; } currentRow++; } private void AddDispatchRow(IXLWorksheet worksheet, ref int currentRow, BookingOrderVM order, string uom) { int column = 1; int lastColumn = 7 + Enum.GetValues(typeof(DispatchType)).Length; // Calculate last column for (int i = 1; i <= lastColumn; i++) { var cell = worksheet.Cell(currentRow, i); cell.Style.Fill.BackgroundColor = XLColor.Gray; cell.Style.Font.Bold = true; } worksheet.Cell(currentRow, column++).Value = order.OrderSummaryId; worksheet.Cell(currentRow, column++).Value = order.OrderSummary.Customer?.CustomerName; worksheet.Cell(currentRow, column++).Value = order.OrderSummary.Customer?.Region; worksheet.Cell(currentRow, column++).Value = $"-"; worksheet.Cell(currentRow, column++).Value = "-"; switch (uom.ToLower()) { case "tons": worksheet.Cell(currentRow, column++).Value = order.OrderSummary.TotalTons; worksheet.Cell(currentRow, column++).Value = 0; break; case "pallets": worksheet.Cell(currentRow, column++).Value = order.OrderSummary.TotalPallets; worksheet.Cell(currentRow, column++).Value = 0; break; case "volume": worksheet.Cell(currentRow, column++).Value = order.OrderSummary.TotalVolume; worksheet.Cell(currentRow, column++).Value = 0; break; } foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>()) { var details = order.OrderSummary?.DispatchDetails?.FirstOrDefault(d => d.LoadType == type); var cell = worksheet.Cell(currentRow, column++); if (details == null) { cell.Value = ""; } else { switch (uom.ToLower()) { case "tons": cell.Value = details.Tons; break; case "pallets": cell.Value = details.Pallets; break; case "volume": cell.Value = details.Volume; break; } } } currentRow++; } private void AddBookingRow(IXLWorksheet worksheet, ref int currentRow, Booking booking, string uom, int orderStartRow) { int column = 1; var idCell = worksheet.Cell(currentRow, column++); idCell.Value = booking.Id; idCell.Style.Protection.SetLocked(true); var truckCell = worksheet.Cell(currentRow, column++); truckCell.Value = booking.Truck; truckCell.Style.Protection.SetLocked(false); var tripCell = worksheet.Cell(currentRow, column++); tripCell.Value = booking.Trip; tripCell.Style.Protection.SetLocked(false); var truckTypeCell = worksheet.Cell(currentRow, column++); truckTypeCell.Value = booking.TruckTypeId; truckTypeCell.Style.Protection.SetLocked(false); var arrivalCell = worksheet.Cell(currentRow, column++); if (booking.ArrivalPlanned.HasValue) { arrivalCell.Value = booking.ArrivalPlanned.Value.ToString("HH:mm"); } arrivalCell.Style.Protection.SetLocked(false); var availableCell = worksheet.Cell(currentRow, column++); switch (uom.ToLower()) { case "tons": availableCell.Value = booking.TruckAvailableTons; break; case "pallets": availableCell.Value = booking.TruckAvailablePallets; break; case "volume": availableCell.Value = booking.TruckAvailableVolume; break; } availableCell.Style.Protection.SetLocked(false); var remainingCell = worksheet.Cell(currentRow, column++); var columnLetter = GetExcelColumnName(column - 2); var dispatchTypeColumns = GetDispatchTypeColumns(currentRow, uom); remainingCell.FormulaA1 = $"={columnLetter}{currentRow}-SUM({dispatchTypeColumns})"; remainingCell.Style.Protection.SetLocked(true); foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>()) { var dispatchCell = worksheet.Cell(currentRow, column); var dispatchRowValue = worksheet.Cell(orderStartRow, column).Value.ToString(); if (dispatchRowValue == "") { dispatchCell.Style.Protection.SetLocked(true); dispatchCell.Value = ""; } else { var details = booking.BookingOrders?.FirstOrDefault()?.DispatchDetails .FirstOrDefault(d => d.LoadType == type); switch (uom.ToLower()) { case "tons": dispatchCell.Value = details?.Tons ?? 0; break; case "pallets": dispatchCell.Value = details?.Pallets ?? 0; break; case "volume": dispatchCell.Value = details?.Volume ?? 0; break; } dispatchCell.Style.Protection.SetLocked(false); } column++; } currentRow++; } private string GetExcelColumnName(int columnNumber) { string columnName = ""; while (columnNumber > 0) { int remainder = (columnNumber - 1) % 26; char columnChar = (char)('A' + remainder); columnName = columnChar + columnName; columnNumber = (columnNumber - 1) / 26; } return columnName; } private string GetDispatchTypeColumns(int row, string uom) { var dispatchTypes = Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>(); var startColumn = 8; var columns = new List<string>(); foreach (var type in dispatchTypes) { columns.Add($"{GetExcelColumnName(startColumn++)}{row}"); } return string.Join(",", columns); } private string[] GetMainHeaders(string uom) { var headers = new List<string> { "ID", "Customer Name", "Region", "Window", "Expected Arrival" }; switch (uom.ToLower()) { case "tons": headers.AddRange(new[] { "Total Tons", "Fulfilled Tons" }); break; case "pallets": headers.AddRange(new[] { "Total Pallets", "Fulfilled Pallets" }); break; case "volume": headers.AddRange(new[] { "Total Volume", "Fulfilled Volume" }); break; } foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>()) { switch (uom.ToLower()) { case "tons": headers.Add($"{type} Tons"); break; case "pallets": headers.Add($"{type} Pallets"); break; case "volume": headers.Add($"{type} Volume"); break; } } return headers.ToArray(); } private string[] GetBookingHeaders(string uom) { var headers = new List<string> { "Booking ID", "Truck", "Trip", "Type", "Arrival Time" }; switch (uom.ToLower()) { case "tons": headers.AddRange(new[] { "Available Tons", "Remaining Tons" }); break; case "pallets": headers.AddRange(new[] { "Available Pallets", "Remaining Pallets" }); break; case "volume": headers.AddRange(new[] { "Available Volume", "Remaining Volume" }); break; } foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>()) { switch (uom.ToLower()) { case "tons": headers.Add($"{type} Tons"); break; case "pallets": headers.Add($"{type} Pallets"); break; case "volume": headers.Add($"{type} Volume"); break; } } return headers.ToArray(); } public IEnumerable<BookingOrderVM> ImportFromExcel(Stream excelStream) { using (var workbook = new XLWorkbook(excelStream)) { var worksheet = workbook.Worksheet(1); var bookingOrders = new List<BookingOrderVM>(); var currentRow = 2; while (!worksheet.Row(currentRow).IsEmpty()) { var order = ParseDispatchRow(worksheet, ref currentRow); currentRow += 2; while (!worksheet.Row(currentRow).IsEmpty() && !IsDispatchRow(worksheet.Row(currentRow))) { var booking = ParseBookingRow(worksheet, currentRow, order.OrderSummaryId); order.Bookings.Add(booking); currentRow++; } bookingOrders.Add(order); currentRow++; } return bookingOrders; } } private BookingOrderVM ParseDispatchRow(IXLWorksheet worksheet, ref int row) { throw new NotImplementedException(); } private Booking ParseBookingRow(IXLWorksheet worksheet, int row, int orderSummaryId) { throw new NotImplementedException(); } private bool IsDispatchRow(IXLRow row) { throw new NotImplementedException(); } } iam using closedxml , i need insert row and delete row functionality in bookingrow for each dispatchrow you can give a button below bookingrow onclicking that same bookingrow adds up if you want to do it with macro by changing library do it . dont change the existing c# one keep all the existing c# code i just need insertrow delete row to be in macro
Leave a Comment