using System.Data; using System.Globalization; using ClosedXML.Excel; using Microsoft.Extensions.Logging; using Seyounth.Hyosung.Data.Models; namespace Seyounth.Hyosung.Data.Services; public class ReportExportService( IYarnService yarnService, IVarietyService varietyService, ITrayService trayService, ILogger logger) : IReportExportService { const string TempPath = "./exportTemp.xlsx"; public async Task ExportAsync(string trayCode) { try { var tray = await trayService.GetByCode(trayCode); var variety = await varietyService.GetById(tray.VarietyId); var yarns = await yarnService.GetYarnsByTrayIdAsync(tray.Id); await Create(tray, variety, yarns); await trayService.ExportedAsync(tray.TrayCode); } catch (Exception e) { logger.LogError(e, $"export report [{trayCode}] error"); } } public async Task ExportSampleAsync(string trayCode) { try { var tray = await trayService.GetByCode(trayCode); var variety = await varietyService.GetById(tray.VarietyId); var yarns = await yarnService.GetYarnsByTrayIdAsync(tray.Id); await CreateSample(tray, variety, yarns); await trayService.ExportedAsync(tray.TrayCode); } catch (Exception e) { logger.LogError(e, $"export report [{trayCode}] error"); } } private async Task CreateSample(Tray tray, Variety variety, List yarns) { try { DataTable dt = new DataTable(); dt.Columns.Add("LOT"); dt.Columns.Add("DTEX_FILA"); dt.Columns.Add("总个数/垛"); dt.Columns.Add("生产时间"); dt.Columns.Add("机台"); dt.Columns.Add("班次"); for (int i = 1; i <= yarns.Count; i++) { if (i == 1) { dt.Rows.Add(yarns[i - 1].Lot, tray.DtexFila, yarns.Count, yarns[i - 1].ProduceTime, yarns[i - 1].Machine, yarns[i - 1].WorkShift); } else { dt.Rows.Add(yarns[i - 1].Lot, tray.DtexFila, " ", yarns[i - 1].ProduceTime, yarns[i - 1].Machine, yarns[i - 1].WorkShift); } } var path = await GetDirectoryAsync(); var fileName = $"{variety.Lot}_{tray.Barcode.Split(" ")[1]}_{DateTime.Now:yyyyMMdd}.xlsx"; var filePath = Path.Combine(path, fileName); using var workbook = new XLWorkbook(); var worksheet = workbook.Worksheets.Add(dt,"export"); // 设置数据行字体大小 var dataRows = worksheet.RowsUsed(); foreach (var row in dataRows) { row.Style.Font.FontSize = 14; } // 自动调整列宽 worksheet.Columns().AdjustToContents(); workbook.SaveAs(filePath); } catch (Exception e) { logger.LogError(e, "export today report error"); } } private async Task Create(Tray tray, Variety variety, List yarns) { using var workbook = new XLWorkbook(TempPath); var worksheet = workbook.Worksheet(1); // 假设使用第一个工作表 worksheet.Cell("D2").Value = tray.Barcode; // 填充 TrayCode 到第一列 worksheet.Cell("D3").Value = variety.Code; // 填充 VarietyName 到第二列 worksheet.Cell("D4").Value = tray.DenFila; worksheet.Cell("I3").Value = tray.FinishTime?.ToString("yyyy-MM-dd HH:mm:ss"); worksheet.Cell("I4").Value = tray.DtexFila; worksheet.Cell("L3").Value = tray.Grade; worksheet.Cell("M3").Value = variety.StackingLayers; worksheet.Cell("N3").Value = tray.Unit?.ToString(); worksheet.Cell("O3").Value = yarns.Count.ToString(); worksheet.Cell("P3").Value = tray.ControlNo?.ToString(); worksheet.Cell("Q3").Value = tray.GrossWeight.ToString(CultureInfo.InvariantCulture); worksheet.Cell("R3").Value = tray.NetWeight.ToString(CultureInfo.InvariantCulture); int row = 8; // 假设数据从第二行开始填充 for (int i = 1; i <= yarns.Count; i++) { worksheet.Cell(row, "B").Value = i; // 填充 YarnId 到第一列 worksheet.Cell(row, "C").Value = yarns[i - 1].Lot; // 填充 YarnName 到第二列 worksheet.Cell(row, "D").Value = variety.Code; // 填充 YarnName 到第二列 worksheet.Cell(row, "F").Value = yarns[i - 1].Machine; worksheet.Cell(row, "H").Value = yarns[i - 1].WorkShift; worksheet.Cell(row, "I").Value = yarns[i - 1].QrCode; worksheet.Cell(row, "P").Value = yarns[i - 1].StackTime?.ToString("yyyy-MM-dd HH:mm:ss"); row++; } for (int i = 8; i < row; i++) { worksheet.Row(i).Height = 31.5; } // 自动调整列宽 // worksheet.Columns().AdjustToContents(); // 获取数据区域 var dataRange = worksheet.Range(8, 2, row - 1, 18); // 设置外边框样式 dataRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; dataRange.Style.Border.InsideBorder = XLBorderStyleValues.Medium; dataRange.Style.Border.OutsideBorderColor = XLColor.Black; var path = await GetDirectoryAsync(tray.FinishTime); var fileName = $"{variety.Lot}-{tray.TrayCode}-{tray.FinishTime?.ToString("yyyyMMddHHmmss")}.xlsx"; var filePath = Path.Combine(path, fileName); // 保存工作簿到新文件 workbook.SaveAs(filePath); } public async Task ExportNoExportAsync() { var codes = await trayService.GetNoExportCodesAsync(); codes.ForEach(async void (code) => { try { await ExportSampleAsync(code); } catch (Exception e) { logger.LogError(e, $"export report [{code}] error"); } }); } public async Task ExportTodayTotalReportAsync() { try { DataTable dt = new DataTable(); dt.Columns.Add("序号"); dt.Columns.Add("LOT"); dt.Columns.Add("品类"); dt.Columns.Add("条码"); dt.Columns.Add("数量"); dt.Columns.Add("毛重"); dt.Columns.Add("净重"); dt.Columns.Add("控制号"); dt.Columns.Add("完成时间"); var trays = await trayService.GetTodayTrayAsync(); for (int i = 0; i < trays.Count; i++) { var variety = await varietyService.GetById(trays[i].VarietyId); var yarns = await yarnService.GetYarnsByTrayIdAsync(trays[i].Id); dt.Rows.Add(i + 1, variety.Lot, variety.Code, trays[i].Barcode, yarns.Count, trays[i].GrossWeight, trays[i].NetWeight, trays[i].ControlNo, trays[i].FinishTime?.ToString("yyyy-MM-dd HH:mm:ss")); } var path = await GetTotalDirectoryAsync(); var fileName = $"每日报表-{DateTime.Now:yyyyMMdd}.xlsx"; var filePath = Path.Combine(path, fileName); using var workbook = new XLWorkbook(); var worksheet = workbook.Worksheets.Add(dt); var headerRow = worksheet.FirstRowUsed(); headerRow.Style.Font.FontSize = 16; // 设置数据行字体大小 var dataRows = worksheet.RowsUsed().Skip(1); foreach (var row in dataRows) { row.Style.Font.FontSize = 14; } // 自动调整列宽 worksheet.Columns().AdjustToContents(); workbook.SaveAs(filePath); } catch (Exception e) { logger.LogError(e, "export today report error"); } } private async Task GetTotalDirectoryAsync(DateTime? date = null) { var root = "D:\\每日报表"; if (date is null) date = DateTime.Now; // 构建文件夹路径,格式为 年/月/日 string directoryPath = Path.Combine(root, date.Value.Year.ToString(), date.Value.Month.ToString()); // 检查文件夹是否存在 if (!Directory.Exists(directoryPath)) { // 如果不存在,则创建文件夹 await Task.Run(() => Directory.CreateDirectory(directoryPath)); } return directoryPath; } public async Task GetDirectoryAsync(DateTime? date = null) { var root = "D:\\码垛信息"; if (date is null) date = DateTime.Now; // 构建文件夹路径,格式为 年/月/日 string directoryPath = Path.Combine(root, date.Value.Year.ToString(), date.Value.Month.ToString(), date.Value.Day.ToString()); // 检查文件夹是否存在 if (!Directory.Exists(directoryPath)) { // 如果不存在,则创建文件夹 await Task.Run(() => Directory.CreateDirectory(directoryPath)); } return directoryPath; } }