484 lines
23 KiB
C#
484 lines
23 KiB
C#
using Microsoft.Win32;
|
||
using OfficeOpenXml;
|
||
using OfficeOpenXml.Drawing.Chart;
|
||
using OfficeOpenXml.Style;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Collections.ObjectModel;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Windows;
|
||
using System.Windows.Controls;
|
||
|
||
namespace PLCDataMonitor
|
||
{
|
||
public partial class ReportPage : UserControl
|
||
{
|
||
public ObservableCollection<ReportData> ReportList { get; set; }
|
||
|
||
// 从主窗口注入的共享队列
|
||
public System.Collections.Concurrent.ConcurrentQueue<Tuple<DateTime, double, double>> CurveDataQueue { get; set; }
|
||
|
||
public ReportPage()
|
||
{
|
||
InitializeComponent();
|
||
ReportList = new ObservableCollection<ReportData>();
|
||
ReportDataGrid.ItemsSource = ReportList;
|
||
}
|
||
|
||
public void AddReportRecord(ReportData record)
|
||
{
|
||
Dispatcher.Invoke(() =>
|
||
{
|
||
ReportList.Insert(0, record);
|
||
});
|
||
}
|
||
|
||
private void ExportExcelButton_Click(object sender, RoutedEventArgs e)
|
||
{
|
||
if (ReportDataGrid.ItemsSource is IEnumerable<ReportData> reportData &&
|
||
CurveDataQueue != null && CurveDataQueue.Count > 0)
|
||
{
|
||
SaveFileDialog saveFileDialog = new SaveFileDialog
|
||
{
|
||
Filter = "Excel 文件 (*.xlsx)|*.xlsx",
|
||
FileName = $"滑动摩擦磨损试验报告_{DateTime.Now:yyyyMMddHHmm}.xlsx"
|
||
};
|
||
|
||
if (saveFileDialog.ShowDialog() == true)
|
||
{
|
||
try
|
||
{
|
||
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
|
||
|
||
using (ExcelPackage excelPackage = new ExcelPackage())
|
||
{
|
||
// ====== 第一个Sheet：报表 ======
|
||
ExcelWorksheet reportSheet = excelPackage.Workbook.Worksheets.Add("滑动摩擦磨损试验报告");
|
||
|
||
// 设置页面
|
||
reportSheet.PrinterSettings.PaperSize = ePaperSize.A4;
|
||
reportSheet.PrinterSettings.Orientation = eOrientation.Portrait;
|
||
|
||
// 标题
|
||
reportSheet.Cells["A1:E1"].Merge = true;
|
||
reportSheet.Cells["A1"].Value = "滑动摩擦磨损试验报告";
|
||
reportSheet.Cells["A1"].Style.Font.Size = 14;
|
||
reportSheet.Cells["A1"].Style.Font.Bold = true;
|
||
reportSheet.Cells["A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
reportSheet.Cells["A1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
|
||
reportSheet.Row(1).Height = 30;
|
||
|
||
reportSheet.Cells["A2"].Value = "报告编号：";
|
||
reportSheet.Cells["D2:E2"].Merge = true;
|
||
reportSheet.Cells["D2"].Value = "试验日期：______年______月______日";
|
||
reportSheet.Row(2).Height = 25;
|
||
|
||
reportSheet.Row(3).Height = 10;
|
||
|
||
// 表头
|
||
reportSheet.Cells["A4"].Value = "序号";
|
||
reportSheet.Cells["B4"].Value = "项目名称";
|
||
reportSheet.Cells["C4"].Value = "工位1";
|
||
reportSheet.Cells["D4"].Value = "工位2";
|
||
reportSheet.Cells["E4"].Value = "备注/说明";
|
||
|
||
for (int col = 1; col <= 5; col++)
|
||
{
|
||
reportSheet.Cells[4, col].Style.Font.Bold = true;
|
||
reportSheet.Cells[4, col].Style.Font.Size = 11;
|
||
reportSheet.Cells[4, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
reportSheet.Cells[4, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
|
||
reportSheet.Cells[4, col].Style.Border.Top.Style = ExcelBorderStyle.Thin;
|
||
reportSheet.Cells[4, col].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
|
||
reportSheet.Cells[4, col].Style.Border.Left.Style = ExcelBorderStyle.Thin;
|
||
reportSheet.Cells[4, col].Style.Border.Right.Style = ExcelBorderStyle.Thin;
|
||
}
|
||
reportSheet.Row(4).Height = 25;
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
// ========== 处理曲线数据，过滤暂停段 ==========
|
||
var mainWindow = (MainWindow)Application.Current.MainWindow;
|
||
var pausePeriods = mainWindow.PausePeriods; // 假设 MainWindow 有公共属性
|
||
|
||
// 获取所有原始数据并按时间排序
|
||
var allRawData = CurveDataQueue.ToList().OrderBy(d => d.Item1).ToList();
|
||
if (allRawData.Count == 0)
|
||
{
|
||
MessageBox.Show("没有曲线数据可导出！", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
|
||
return;
|
||
}
|
||
|
||
// 过滤掉暂停期间的点
|
||
var filteredData = allRawData.Where(point =>
|
||
!pausePeriods.Any(p => point.Item1 >= p.start && point.Item1 <= p.end)
|
||
).ToList();
|
||
|
||
if (filteredData.Count == 0)
|
||
{
|
||
MessageBox.Show("有效数据（非暂停期间）为空，无法导出！", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
|
||
return;
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
|
||
// 【修复】重建连续时间轴（秒）- 自动扣除暂停时间
|
||
List<double> timeAxis = new List<double>();
|
||
DateTime effectiveStartTime = filteredData.First().Item1;
|
||
DateTime effectiveEndTime = filteredData.Last().Item1;
|
||
|
||
for (int i = 0; i < filteredData.Count; i++)
|
||
{
|
||
DateTime currentTime = filteredData[i].Item1;
|
||
|
||
// 1. 计算从开始到现在的总物理时间
|
||
double totalElapsed = (currentTime - effectiveStartTime).TotalSeconds;
|
||
|
||
// 2. 计算这段时间内包含的“暂停时长”总和
|
||
double totalPauseDuration = 0;
|
||
foreach (var p in pausePeriods)
|
||
{
|
||
// 只计算在当前点之前发生的暂停
|
||
if (p.end <= effectiveStartTime) continue;
|
||
if (p.start >= currentTime) break;
|
||
|
||
// 计算暂停段与 [开始时间，当前时间] 的重叠部分
|
||
long overlapStart = p.start > effectiveStartTime ? p.start.Ticks : effectiveStartTime.Ticks;
|
||
long overlapEnd = p.end < currentTime ? p.end.Ticks : currentTime.Ticks;
|
||
|
||
if (overlapEnd > overlapStart)
|
||
{
|
||
totalPauseDuration += (overlapEnd - overlapStart) / (double)TimeSpan.TicksPerSecond;
|
||
}
|
||
}
|
||
|
||
// 3. 有效时间 = 物理时间 - 暂停时间
|
||
double validTime = totalElapsed - totalPauseDuration;
|
||
timeAxis.Add(Math.Max(0, validTime));
|
||
}
|
||
|
||
double totalValidSeconds = timeAxis.Last(); // 更新有效总时长
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
// 计算最大摩擦力（基于过滤后的数据）
|
||
double maxFriction1 = filteredData.Max(x => Math.Abs(x.Item2));
|
||
double maxFriction2 = filteredData.Max(x => Math.Abs(x.Item3));
|
||
|
||
// ========== 填写报表（第一个Sheet） ==========
|
||
int currentRow = 5;
|
||
|
||
// 序号1：油管型号
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "1", "油管型号", OilPipeType1.Text, OilPipeType2.Text, "");
|
||
|
||
// 序号2：滑块型号
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "2", "样件型号", SliderType1.Text, SliderType2.Text, "");
|
||
|
||
// 序号3：试验开始时间（有效）
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "3", "试验开始时间", effectiveStartTime.ToString("yyyy-MM-dd HH:mm:ss"), "", "试验开始的时间点");
|
||
|
||
// 序号4：试验停止时间（有效）
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "4", "试验停止时间", effectiveEndTime.ToString("yyyy-MM-dd HH:mm:ss"), "", "试验结束的时间点");
|
||
|
||
// 序号5：样件试验前壁厚
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "5", "样件1试验前壁厚(mm)", PreTestThickness.Text, PreTestThickness3.Text, "样件试验前壁厚(mm)");
|
||
|
||
// 序号6：样件试验后壁厚
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "6", "样件1试验后壁厚(mm)", PostTestThickness.Text, PostTestThickness3.Text, "样件试验后壁厚(mm)");
|
||
|
||
// 序号7：油管磨损深度
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "7", "油管1磨损深度(mm)", PipeWearDepth.Text, PipeWearDepth3.Text, "油管磨损深度(mm)");
|
||
|
||
// 序号8：样件1试验前重量(g)
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "8", "样件1试验前重量(g)", PreTestThickness4.Text, PreTestThickness2.Text, "样件试验前重量(g)");
|
||
|
||
// 序号9：样件1试验后重量(g)
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "9", "样件1试验后重量(g)", PostTestThickness4.Text, PostTestThickness2.Text, "样件试验后重量(g)");
|
||
|
||
// 序号10：样件1磨损量(g)
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "10", "样件1磨损量(g)", PipeWearDepth4.Text, PipeWearDepth2.Text, "样件磨损量(g)");
|
||
|
||
// 报表数据（温度、距离、压力）来自 reportData
|
||
if (reportData.Count() > 0)
|
||
{
|
||
reportData = reportData.OrderBy(s => s.Time).ToList();
|
||
}
|
||
|
||
// 序号11：水箱温度
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "11", "水箱温度（℃）",
|
||
reportData.Last().t1?.ToString("F1"),
|
||
reportData.Last().t2?.ToString("F1"),
|
||
"实时监测温度");
|
||
|
||
// 序号12：测试实际距离
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "12", "测试实际距离（m）",
|
||
reportData.Last().D?.ToString(),
|
||
reportData.Last().D?.ToString(),
|
||
"累计滑动行程");
|
||
|
||
// 序号13：压力
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "13", "压力（N）",
|
||
reportData.Last().Pressure1.ToString(),
|
||
reportData.Last().Pressure2.ToString(),
|
||
"施加载荷");
|
||
|
||
// 序号14：最大摩擦力（基于过滤后的有效数据）
|
||
CreateFormattedRowEPPlus(reportSheet, currentRow++, "14", "最大摩擦力（N）",
|
||
maxFriction1.ToString("F1"),
|
||
maxFriction2.ToString("F1"),
|
||
"测试过程中最大值（剔除暂停段）");
|
||
|
||
// 空行
|
||
currentRow++;
|
||
|
||
// 备注标题
|
||
reportSheet.Cells[currentRow, 1].Value = "备注：";
|
||
reportSheet.Cells[currentRow, 1].Style.Font.Bold = true;
|
||
|
||
for (int i = 0; i < 4; i++)
|
||
{
|
||
int remarkRow = currentRow + 1 + i;
|
||
reportSheet.Cells[remarkRow, 1, remarkRow, 5].Merge = true;
|
||
reportSheet.Cells[remarkRow, 1].Style.WrapText = true;
|
||
reportSheet.Cells[remarkRow, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Top;
|
||
reportSheet.Row(remarkRow).Height = 40;
|
||
}
|
||
|
||
reportSheet.Cells[currentRow + 1, 1].Value = "1. 所有数据应在试验结束后立即记录，确保真实、准确。";
|
||
reportSheet.Cells[currentRow + 2, 1].Value = "2. 壁厚和磨损深度需使用精密测量仪器测量。";
|
||
reportSheet.Cells[currentRow + 3, 1].Value = "3. 若试验过程中出现异常（如异响、过热、泄漏等），请在备注中说明。";
|
||
reportSheet.Cells[currentRow + 4, 1].Value = "4. 磨损量需通过精密电子天平称重（试验前后）。";
|
||
|
||
currentRow += 5;
|
||
currentRow += 2;
|
||
|
||
reportSheet.Cells[currentRow, 1].Value = "测试人签字：______";
|
||
reportSheet.Cells[currentRow, 4].Value = "审核人签字：______";
|
||
reportSheet.Row(currentRow).Height = 30;
|
||
currentRow++;
|
||
|
||
reportSheet.Cells[currentRow, 1].Value = "报告日期：______年______月______日";
|
||
reportSheet.Row(currentRow).Height = 25;
|
||
|
||
reportSheet.Column(1).Width = 23;
|
||
reportSheet.Column(2).Width = 30;
|
||
reportSheet.Column(3).Width = 20;
|
||
reportSheet.Column(4).Width = 20;
|
||
reportSheet.Column(5).Width = 20;
|
||
|
||
// ====== 第二个Sheet：曲线数据和图表 ======
|
||
ExcelWorksheet curveSheet = excelPackage.Workbook.Worksheets.Add("实时摩擦力曲线数据");
|
||
|
||
curveSheet.Column(1).Width = 15;
|
||
curveSheet.Column(2).Width = 20;
|
||
curveSheet.Column(3).Width = 20;
|
||
|
||
// 表头
|
||
curveSheet.Cells["A1"].Value = "时间(秒)";
|
||
curveSheet.Cells["B1"].Value = "工位1摩擦力(N)";
|
||
curveSheet.Cells["C1"].Value = "工位2摩擦力(N)";
|
||
|
||
curveSheet.Cells["A1:C1"].Style.Font.Bold = true;
|
||
curveSheet.Cells["A1:C1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
curveSheet.Cells["A1:C1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
|
||
curveSheet.Cells["A1:C1"].Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
|
||
curveSheet.Row(1).Height = 25;
|
||
|
||
// 写入过滤后的数据和重建的时间轴
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
int dataRow = 2;
|
||
for (int i = 0; i < filteredData.Count; i++)
|
||
{
|
||
double seconds = timeAxis[i];
|
||
var data = filteredData[i];
|
||
|
||
curveSheet.Cells[dataRow, 1].Value = Math.Round(seconds, 3);
|
||
curveSheet.Cells[dataRow, 2].Value = Math.Round(data.Item2, 1);
|
||
curveSheet.Cells[dataRow, 3].Value = Math.Round(data.Item3, 1);
|
||
|
||
curveSheet.Cells[dataRow, 1].Style.Numberformat.Format = "0.000";
|
||
curveSheet.Cells[dataRow, 2].Style.Numberformat.Format = "0.00";
|
||
curveSheet.Cells[dataRow, 3].Style.Numberformat.Format = "0.00";
|
||
|
||
dataRow++;
|
||
}
|
||
int lastDataRow = dataRow - 1;
|
||
|
||
// 计算X轴范围（基于有效时长）
|
||
double xAxisMax = Math.Ceiling(totalValidSeconds) + 1;
|
||
if (totalValidSeconds > 8 && totalValidSeconds < 12)
|
||
xAxisMax = 12;
|
||
else if (xAxisMax < 10)
|
||
xAxisMax = 10;
|
||
|
||
// 创建散点图
|
||
var chart = curveSheet.Drawings.AddChart("摩擦力曲线", eChartType.XYScatterLines);
|
||
chart.SetPosition(0, 0, 3, 0);
|
||
chart.SetSize(800, 400);
|
||
|
||
chart.Title.Text = "实时摩擦力曲线";
|
||
chart.Title.Font.Size = 14;
|
||
chart.Title.Font.Bold = true;
|
||
|
||
var series1 = chart.Series.Add(
|
||
curveSheet.Cells[2, 2, lastDataRow, 2],
|
||
curveSheet.Cells[2, 1, lastDataRow, 1]
|
||
);
|
||
series1.Header = "工位1摩擦力";
|
||
|
||
var series2 = chart.Series.Add(
|
||
curveSheet.Cells[2, 3, lastDataRow, 3],
|
||
curveSheet.Cells[2, 1, lastDataRow, 1]
|
||
);
|
||
series2.Header = "工位2摩擦力";
|
||
|
||
chart.XAxis.MinValue = 0;
|
||
chart.XAxis.MaxValue = xAxisMax;
|
||
|
||
double majorUnit = CalculateMajorUnit(xAxisMax);
|
||
chart.XAxis.MajorUnit = majorUnit;
|
||
|
||
chart.XAxis.Title.Text = "时间 (秒)";
|
||
chart.XAxis.Title.Font.Size = 11;
|
||
chart.XAxis.Title.Font.Bold = true;
|
||
|
||
chart.YAxis.Title.Text = "摩擦力 (N)";
|
||
chart.YAxis.Title.Font.Size = 11;
|
||
chart.YAxis.Title.Font.Bold = true;
|
||
|
||
chart.Legend.Position = eLegendPosition.Bottom;
|
||
chart.XAxis.MajorGridlines.Fill.Color = System.Drawing.Color.LightGray;
|
||
chart.YAxis.MajorGridlines.Fill.Color = System.Drawing.Color.LightGray;
|
||
|
||
// 信息行
|
||
int infoRow = lastDataRow + 2;
|
||
curveSheet.Cells[infoRow, 1].Value = "图表信息:";
|
||
curveSheet.Cells[infoRow, 1].Style.Font.Bold = true;
|
||
|
||
curveSheet.Cells[infoRow + 1, 1].Value = "有效数据时间范围:";
|
||
curveSheet.Cells[infoRow + 1, 2].Value = $"0 - {totalValidSeconds:F3} 秒";
|
||
|
||
curveSheet.Cells[infoRow + 2, 1].Value = "X轴显示范围:";
|
||
curveSheet.Cells[infoRow + 2, 2].Value = $"0 - {xAxisMax:F1} 秒";
|
||
|
||
curveSheet.Cells[infoRow + 3, 1].Value = "X轴主要刻度:";
|
||
curveSheet.Cells[infoRow + 3, 2].Value = $"{majorUnit:F1} 秒";
|
||
|
||
curveSheet.Cells[infoRow + 4, 1].Value = "有效数据点数:";
|
||
curveSheet.Cells[infoRow + 4, 2].Value = filteredData.Count;
|
||
|
||
// 保存文件
|
||
FileInfo excelFile = new FileInfo(saveFileDialog.FileName);
|
||
excelPackage.SaveAs(excelFile);
|
||
|
||
MessageBox.Show("滑动摩擦磨损试验报告导出成功！", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
|
||
|
||
// 清理数据（可选）
|
||
ReportList.Clear();
|
||
if (CurveDataQueue != null)
|
||
{
|
||
while (CurveDataQueue.TryDequeue(out _)) { }
|
||
}
|
||
// 同时清空暂停时间段（如果主窗口提供了方法）
|
||
mainWindow.ClearPausePeriods(); // 需要在 MainWindow 中实现
|
||
ReportDataGrid.ItemsSource = ReportList;
|
||
OnExportCompleted?.Invoke();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show($"导出失败: {ex.Message}\n\n详细错误:\n{ex.StackTrace}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
MessageBox.Show("没有可导出的数据！", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
|
||
}
|
||
}
|
||
|
||
private double CalculateMajorUnit(double xAxisMax)
|
||
{
|
||
if (xAxisMax <= 5) return 1;
|
||
if (xAxisMax <= 10) return 2;
|
||
if (xAxisMax <= 20) return 5;
|
||
if (xAxisMax <= 50) return 10;
|
||
if (xAxisMax <= 100) return 20;
|
||
return Math.Ceiling(xAxisMax / 10);
|
||
}
|
||
|
||
private void CreateFormattedRowEPPlus(ExcelWorksheet worksheet, int row, string seq, string projectName,
|
||
string station1, string station2, string remark)
|
||
{
|
||
worksheet.Cells[row, 1].Value = seq;
|
||
worksheet.Cells[row, 2].Value = projectName;
|
||
worksheet.Cells[row, 3].Value = station1;
|
||
worksheet.Cells[row, 4].Value = station2;
|
||
worksheet.Cells[row, 5].Value = remark;
|
||
|
||
for (int col = 1; col <= 5; col++)
|
||
{
|
||
worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
|
||
worksheet.Cells[row, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
|
||
worksheet.Cells[row, col].Style.Border.Top.Style = ExcelBorderStyle.Thin;
|
||
worksheet.Cells[row, col].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
|
||
worksheet.Cells[row, col].Style.Border.Left.Style = ExcelBorderStyle.Thin;
|
||
worksheet.Cells[row, col].Style.Border.Right.Style = ExcelBorderStyle.Thin;
|
||
}
|
||
|
||
worksheet.Cells[row, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
|
||
worksheet.Cells[row, 5].Style.WrapText = true;
|
||
worksheet.Row(row).Height = 25;
|
||
}
|
||
|
||
public event Action OnExportCompleted;
|
||
}
|
||
} |