From bbac60058524198c13c4a22943719f853ef8e2bc Mon Sep 17 00:00:00 2001
From: xcd <834800634@qq.com>
Date: 星期三, 15 七月 2020 19:34:15 +0800
Subject: [PATCH] Excel输出4个sheet，横向排布 解决启动延迟问题
---
src/Bro.M071.Process/M071Process.cs | 396 ++++++++++++++++++++++++++++++++++++++++++++++++--------
1 files changed, 337 insertions(+), 59 deletions(-)
diff --git a/src/Bro.M071.Process/M071Process.cs b/src/Bro.M071.Process/M071Process.cs
index c9ff290..cf6b12d 100644
--- a/src/Bro.M071.Process/M071Process.cs
+++ b/src/Bro.M071.Process/M071Process.cs
@@ -10,6 +10,7 @@
using HalconDotNet;
using Newtonsoft.Json;
using OfficeOpenXml;
+using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
@@ -375,7 +376,7 @@
}
else
{
- m.Spec.ActualValue = _halconToolDict[toolKey].GetResultTuple("OUTPUT_Result").D;
+ m.Spec.ActualValue = double.Parse(_halconToolDict[toolKey].GetResultTuple("OUTPUT_Result").D.ToString("f2"));
LogAsync(DateTime.Now, $"{m.GetDisplayText()}鏁版嵁{m.Spec.ActualValue}锛岀粨鏋渰(m.Spec.MeasureResult == null ? "TBD" : (m.Spec.MeasureResult == true ? "OK" : "NG"))}", "");
}
}
@@ -422,7 +423,7 @@
}
//Excel鎶ヨ〃杈撳嚭 锛堝崟涓骇鍝佺殑excel瀵煎嚭锛�
- ExportProductionExcel(measurementUnitResultAndKeyUnitDataSet);
+ //ExportProductionExcel(measurementUnitResultAndKeyUnitDataSet);
ExportProductionInColumns(measurementUnitResultAndKeyUnitDataSet);
////鏁版嵁搴撲繚瀛�
@@ -504,8 +505,19 @@
measurementUnitResult.ProductionMeasurementRecordsId = productionMeasurementRecords.ID;
measurementUnitResult.ProductionBarcode = productionMeasurementRecords.ProductionBarcode;
measurementUnitResult.MeasurementName = measurementUnit.GetDisplayText();
+
+ if (measurementUnit.MeasureType == "Alignment" || measurementUnit.MeasureType == "Slant")
+ {
+ measurementUnitResult.Keys = string.Join("-", measurementUnit.KeyUnitCollection.Select(u => u.Key));
+ measurementUnitResult.Positions = string.Join("-", measurementUnit.KeyUnitCollection.Select(u => u.KeyResultId));
+ }
+ else if (measurementUnit.MeasureType == "RowAlignment")
+ {
+ measurementUnitResult.Keys = measurementUnit.KeyUnitCollection[0].Key + "~" + measurementUnit.KeyUnitCollection[measurementUnit.KeyUnitCollection.Count() - 1].Key;
+ measurementUnitResult.Keys = measurementUnit.KeyUnitCollection[0].KeyResultId + "~" + measurementUnit.KeyUnitCollection[measurementUnit.KeyUnitCollection.Count() - 1].KeyResultId;
+ }
measurementUnitResult.MeasurementType = measurementUnit.MeasureType;
- measurementUnitResult.MeasurementValue = measurementUnit.Spec.ActualValue.ToString();
+ measurementUnitResult.MeasurementValue = measurementUnit.Spec.ActualValue == null ? "NA" : measurementUnit.Spec.ActualValue.Value.ToString("f2");
measurementUnitResult.MeasurementResult = measurementUnit.Spec.MeasureResult.Value ? "OK" : "NG";
measurementUnitResults.Add(measurementUnitResult);
@@ -521,7 +533,7 @@
keyUnitData.ProductionBarcode = productionMeasurementRecords.ProductionBarcode;
keyUnitData.Key = keyUnit.Key;
keyUnitData.MeasurementItem = keyValue.Key;
- keyUnitData.ItemValue = keyValue.Value.ToString();
+ keyUnitData.ItemValue = keyValue.Value == null ? "NA" : keyValue.Value.Value.ToString("f2");
keyUnitDatas.Add(keyUnitData);
MeasurementAndKeyDataRelation measurementAndKeyDataRelation = new MeasurementAndKeyDataRelation();
@@ -596,67 +608,332 @@
await Task.Run(() =>
{
- if (!Directory.Exists(Config.LogPath))
+ lock (excelExportLock)
{
- Directory.CreateDirectory(Config.LogPath);
- }
- var fileName = Path.Combine(Config.LogPath, $"LDSData_{DateTime.Now.ToString("yyyyMMdd")}.xlsx");
- bool isExisted = File.Exists(fileName);
- using (ExcelPackage package = new ExcelPackage(new FileInfo(fileName)))
- {
- ExcelWorksheet rawDataSheet = null;
- ExcelWorksheet slantSheet = null;
- ExcelWorksheet alignmentSheet = null;
- ExcelWorksheet rowAlignmentSheet = null;
- if (!isExisted)
+ if (!Directory.Exists(Config.LogPath))
{
- package.Workbook.Worksheets.Add("RawData");
- rawDataSheet = package.Workbook.Worksheets["RawData"];
- for (int i = 0; i < Config.KeyNameCollection.Count; i++)
- {
- var cell = rawDataSheet.Cells[i + 3, 1];
- cell.Value = Config.KeyNameCollection[i];
- cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(31, 73, 125));
- }
-
- //package.Workbook.Worksheets.Add("Slant");
- //slantSheet = package.Workbook.Worksheets["Slant"];
- //for(int i=0;i<exportData.MeasurementUnitResultList)
- //package.Workbook.Worksheets.Add("Alignment");
- //package.Workbook.Worksheets.Add("RowAlignment");
+ Directory.CreateDirectory(Config.LogPath);
}
-
- rawDataSheet = package.Workbook.Worksheets["RawData"];
-
- int rowDataStartCol = rawDataSheet.Dimension.Columns;
- var barcodeCell = rawDataSheet.Cells[1, rowDataStartCol + 1, 1, rowDataStartCol + 1 + 4];
- barcodeCell.Merge = true;
- barcodeCell.Value = exportData.ProductionMeasurementRecord.ProductionBarcode;
-
- var z1Cell = rawDataSheet.Cells[2, rowDataStartCol + 1];
- z1Cell.Value = "Z1";
-
- var z2Cell = rawDataSheet.Cells[2, rowDataStartCol + 2];
- z2Cell.Value = "Z2";
-
- var z3Cell = rawDataSheet.Cells[2, rowDataStartCol + 3];
- z3Cell.Value = "Z3";
-
- var z4Cell = rawDataSheet.Cells[2, rowDataStartCol + 4];
- z4Cell.Value = "Z4";
-
- exportData.KeyUnitDataList.ForEach(k =>
+ var fileName = Path.Combine(Config.LogPath, $"LDSData_{DateTime.Now.ToString("yyyyMMdd")}.xlsx");
+ //bool isExisted = File.Exists(fileName);
+ using (ExcelPackage package = new ExcelPackage(new FileInfo(fileName)))
{
- int keyIndex = Config.KeyNameCollection.IndexOf(k.Key);
- int zIndex = int.Parse(k.MeasurementItem.Substring(1));
+ ExcelWorksheet rawDataSheet = null;
+ ExcelWorksheet slantSheet = null;
+ ExcelWorksheet alignmentSheet = null;
+ ExcelWorksheet rowAlignmentSheet = null;
- var cell = rawDataSheet.Cells[keyIndex + 1 + 2, rowDataStartCol + zIndex];
- cell.Value = k.ItemValue;
- });
+ if (!package.Workbook.Worksheets.Any(s => s.Name == "RawData"))
+ {
+ package.Workbook.Worksheets.Add("RawData");
+ rawDataSheet = package.Workbook.Worksheets["RawData"];
+ for (int i = 0; i < Config.KeyNameCollection.Count; i++)
+ {
+ var cell = rawDataSheet.Cells[i + 3, 1];
+ cell.Value = Config.KeyNameCollection[i];
+ SetTitleCell(cell, false);
+ }
+ }
+ rawDataSheet = package.Workbook.Worksheets["RawData"];
- package.Save();
- };
+ if (!package.Workbook.Worksheets.Any(s => s.Name == "Slant"))
+ {
+ package.Workbook.Worksheets.Add("Slant");
+ slantSheet = package.Workbook.Worksheets["Slant"];
+
+ var keyCell = slantSheet.Cells[2, 1];
+ keyCell.Value = "Key";
+ SetTitleCell(keyCell);
+
+ for (int i = 0; i < Config.KeyNameCollection.Count; i++)
+ {
+ var cell = slantSheet.Cells[i + 3, 1];
+ cell.Value = Config.KeyNameCollection[i];
+ SetTitleCell(cell, false);
+ }
+ }
+ slantSheet = package.Workbook.Worksheets["Slant"];
+
+ if (!package.Workbook.Worksheets.Any(s => s.Name == "Alignment"))
+ {
+ package.Workbook.Worksheets.Add("Alignment");
+ alignmentSheet = package.Workbook.Worksheets["Alignment"];
+
+ var keysCell = alignmentSheet.Cells[2, 1];
+ keysCell.Value = "Keys";
+ SetTitleCell(keysCell);
+
+ var positionCell = alignmentSheet.Cells[2, 2];
+ positionCell.Value = "Positions";
+ SetTitleCell(positionCell);
+
+ var alignmentMeasures = exportData.MeasurementUnitResultList.Where(u => u.MeasurementType == "Alignment").ToList();
+ for (int i = 0; i < alignmentMeasures.Count(); i++)
+ {
+ var cellKeys = alignmentSheet.Cells[i + 3, 1];
+ cellKeys.Value = alignmentMeasures[i].Keys;
+ SetTitleCell(cellKeys, false);
+
+ var cellPosition = alignmentSheet.Cells[i + 3, 2];
+ cellPosition.Value = alignmentMeasures[i].Positions;
+ SetTitleCell(cellPosition, false);
+ }
+ }
+ alignmentSheet = package.Workbook.Worksheets["Alignment"];
+
+ if (!package.Workbook.Worksheets.Any(s => s.Name == "RowAlignment"))
+ {
+ package.Workbook.Worksheets.Add("RowAlignment");
+ rowAlignmentSheet = package.Workbook.Worksheets["RowAlignment"];
+
+ var keysCell = rowAlignmentSheet.Cells[2, 1];
+ keysCell.Value = "Keys";
+ SetTitleCell(keysCell);
+
+ var positionCell = rowAlignmentSheet.Cells[2, 2];
+ positionCell.Value = "Positions";
+ SetTitleCell(positionCell);
+
+ var rowAlignmentMeasures = exportData.MeasurementUnitResultList.Where(u => u.MeasurementType == "RowAlignment").ToList();
+ for (int i = 0; i < rowAlignmentMeasures.Count(); i++)
+ {
+ var cellKeys = rowAlignmentSheet.Cells[i + 3, 1];
+ cellKeys.Value = rowAlignmentMeasures[i].Keys;
+ SetTitleCell(cellKeys, false);
+
+ var cellPosition = rowAlignmentSheet.Cells[i + 3, 2];
+ cellPosition.Value = rowAlignmentMeasures[i].Positions;
+ SetTitleCell(cellPosition, false);
+ }
+ }
+ rowAlignmentSheet = package.Workbook.Worksheets["RowAlignment"];
+
+ #region RawData
+ {
+ //rawDataSheet = package.Workbook.Worksheets["RawData"];
+
+ int rowDataStartCol = rawDataSheet.Dimension.Columns;
+ var barcodeCell = rawDataSheet.Cells[1, rowDataStartCol + 1, 1, rowDataStartCol + 4];
+ barcodeCell.Merge = true;
+ barcodeCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
+ barcodeCell.Value = exportData.ProductionMeasurementRecord.ProductionBarcode;
+ SetTitleCell(barcodeCell);
+
+ var z1Cell = rawDataSheet.Cells[2, rowDataStartCol + 1];
+ z1Cell.Value = "Z1";
+ SetTitleCell(z1Cell);
+
+ var z2Cell = rawDataSheet.Cells[2, rowDataStartCol + 2];
+ z2Cell.Value = "Z2";
+ SetTitleCell(z2Cell);
+
+ var z3Cell = rawDataSheet.Cells[2, rowDataStartCol + 3];
+ z3Cell.Value = "Z3";
+ SetTitleCell(z3Cell);
+
+ var z4Cell = rawDataSheet.Cells[2, rowDataStartCol + 4];
+ z4Cell.Value = "Z4";
+ SetTitleCell(z4Cell);
+
+ exportData.KeyUnitDataList.ForEach(k =>
+ {
+ int keyIndex = Config.KeyNameCollection.IndexOf(k.Key);
+ if (keyIndex < 0)
+ {
+ LogAsync(DateTime.Now, "Excel瀵煎嚭閿欒", $"{k.Key}鐨凴awData鏈兘鑾峰彇琛屼俊鎭紝鏈鍑�");
+ return;
+ }
+
+ int zIndex = int.Parse(k.MeasurementItem.Substring(1));
+
+ var cell = rawDataSheet.Cells[keyIndex + 1 + 2, rowDataStartCol + zIndex];
+ cell.Value = string.IsNullOrWhiteSpace(k.ItemValue) ? "NA" : k.ItemValue;
+ });
+ }
+ #endregion
+
+ #region Slant
+ var slantMeasures = exportData.MeasurementUnitResultList.Where(u => u.MeasurementType == "Slant").ToList();
+ if (slantMeasures.Count > 0)
+ {
+ int slantStartCol = slantSheet.Dimension.Columns;
+
+ var barcodeCell = slantSheet.Cells[1, slantStartCol + 1, 1, slantStartCol + 2];
+ barcodeCell.Merge = true;
+ barcodeCell.Value = exportData.ProductionMeasurementRecord.ProductionBarcode;
+ SetTitleCell(barcodeCell);
+
+ var valueCell = slantSheet.Cells[2, slantStartCol + 1];
+ valueCell.Value = "Value";
+ SetTitleCell(valueCell);
+
+ var resultCell = slantSheet.Cells[2, slantStartCol + 2];
+ resultCell.Value = "Result";
+ SetTitleCell(resultCell);
+
+ slantMeasures.ForEach(m =>
+ {
+ int rowIndex = Config.KeyNameCollection.IndexOf(m.Keys);
+ if (rowIndex < 0)
+ {
+ LogAsync(DateTime.Now, "Excel瀵煎嚭閿欒", $"{m.Keys}鐨凷lant鏈兘鑾峰彇琛屼俊鎭紝鏈鍑�");
+ return;
+ }
+
+ var cellValue = slantSheet.Cells[rowIndex + 1 + 2, slantStartCol + 1];
+ var cellResult = slantSheet.Cells[rowIndex + 1 + 2, slantStartCol + 2];
+
+ cellValue.Value = m.MeasurementValue;
+ cellResult.Value = m.MeasurementResult;
+
+ if (m.MeasurementResult != "OK")
+ {
+ SetNGCell(cellValue);
+ SetNGCell(cellResult);
+ }
+ });
+ }
+ #endregion
+
+ #region Alignment
+ {
+ var alignmentMeasures = exportData.MeasurementUnitResultList.Where(u => u.MeasurementType == "Alignment").ToList();
+ if (alignmentMeasures.Count > 0)
+ {
+ List<string> keysList = new List<string>();
+ int allRowNums = alignmentSheet.Dimension.Rows;
+ int aligneStartCol = alignmentSheet.Dimension.Columns;
+
+ for (int i = 3; i <= allRowNums; i++)
+ {
+ string keys = alignmentSheet.Cells[i, 1].Value.ToString();
+ string position = alignmentSheet.Cells[i, 2].Value.ToString();
+
+ keysList.Add($"{keys}_{position}");
+ }
+
+ var barcodeCell = alignmentSheet.Cells[1, aligneStartCol + 1, 1, aligneStartCol + 2];
+ barcodeCell.Merge = true;
+ barcodeCell.Value = exportData.ProductionMeasurementRecord.ProductionBarcode;
+ SetTitleCell(barcodeCell);
+
+ var valueCell = alignmentSheet.Cells[2, aligneStartCol + 1];
+ valueCell.Value = "Value";
+ SetTitleCell(valueCell);
+
+ var resultCell = alignmentSheet.Cells[2, aligneStartCol + 2];
+ resultCell.Value = "Result";
+ SetTitleCell(resultCell);
+
+ alignmentMeasures.ForEach(a =>
+ {
+ int rowIndex = keysList.IndexOf($"{a.Keys}_{a.Positions}");
+ if (rowIndex < 0)
+ {
+ LogAsync(DateTime.Now, "Excel瀵煎嚭閿欒", $"{a.Keys}_{a.Positions}鐨凙lignment鏈兘鑾峰彇琛屼俊鎭紝鏈鍑�");
+ return;
+ }
+
+ var cellValue = alignmentSheet.Cells[rowIndex + 1 + 2, aligneStartCol + 1];
+ var cellResult = alignmentSheet.Cells[rowIndex + 1 + 2, aligneStartCol + 2];
+
+ cellValue.Value = a.MeasurementValue;
+ cellResult.Value = a.MeasurementResult;
+
+ if (a.MeasurementResult != "OK")
+ {
+ SetNGCell(cellValue);
+ SetNGCell(cellResult);
+ }
+ });
+ }
+ }
+ #endregion
+
+ #region RowAlignment
+ {
+ var rowAlignmentMeasures = exportData.MeasurementUnitResultList.Where(u => u.MeasurementType == "RowAlignment").ToList();
+ if (rowAlignmentMeasures.Count > 0)
+ {
+ List<string> keysList = new List<string>();
+ int allRowNums = rowAlignmentSheet.Dimension.Rows;
+ int aligneStartCol = rowAlignmentSheet.Dimension.Columns;
+
+ for (int i = 3; i <= allRowNums; i++)
+ {
+ string keys = alignmentSheet.Cells[i, 1].Value.ToString();
+ string position = alignmentSheet.Cells[i, 2].Value.ToString();
+
+ keysList.Add($"{keys}_{position}");
+ }
+
+ var barcodeCell = rowAlignmentSheet.Cells[1, aligneStartCol + 1, 1, aligneStartCol + 2];
+ barcodeCell.Merge = true;
+ barcodeCell.Value = exportData.ProductionMeasurementRecord.ProductionBarcode;
+ SetTitleCell(barcodeCell);
+
+ var valueCell = rowAlignmentSheet.Cells[2, aligneStartCol + 1];
+ valueCell.Value = "Value";
+ SetTitleCell(valueCell);
+
+ var resultCell = rowAlignmentSheet.Cells[2, aligneStartCol + 2];
+ resultCell.Value = "Result";
+ SetTitleCell(resultCell);
+
+ rowAlignmentMeasures.ForEach(a =>
+ {
+ int rowIndex = keysList.IndexOf($"{a.Keys}_{a.Positions}");
+ if (rowIndex < 0)
+ {
+ LogAsync(DateTime.Now, "Excel瀵煎嚭閿欒", $"{a.Keys}_{a.Positions}鐨凴owAlignment鏈兘鑾峰彇琛屼俊鎭紝鏈鍑�");
+ return;
+ }
+
+ var cellValue = rowAlignmentSheet.Cells[rowIndex + 1 + 2, aligneStartCol + 1];
+ var cellResult = rowAlignmentSheet.Cells[rowIndex + 1 + 2, aligneStartCol + 2];
+
+ cellValue.Value = a.MeasurementValue;
+ cellResult.Value = a.MeasurementResult;
+
+ if (a.MeasurementResult != "OK")
+ {
+ SetNGCell(cellValue);
+ SetNGCell(cellResult);
+ }
+ });
+ }
+ }
+ #endregion
+
+ package.Save();
+ };
+ }
});
+ }
+
+ private void SetNGCell(ExcelRange cell)
+ {
+ cell.Style.Font.Color.SetColor(Color.White);
+ cell.Style.Font.Bold = true;
+ cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
+ cell.Style.Fill.BackgroundColor.SetColor(Color.Red);
+ }
+
+ private void SetTitleCell(ExcelRange cell, bool isCenterAlign = true)
+ {
+ cell.Style.Font.Color.SetColor(Color.White);
+ cell.Style.Font.Bold = true;
+ cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
+ cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(31, 73, 125));
+ cell.AutoFitColumns();
+ if (isCenterAlign)
+ {
+ cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
+ }
+ else
+ {
+ cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
+ }
}
#region 鍥惧儚淇濆瓨
@@ -842,13 +1119,14 @@
}
else
{
- var results = _halconToolDict[keyToolKey].GetResultTuple("OUTPUT_Results").HTupleToDouble();
+ var results = _halconToolDict[keyToolKey].GetResultTuple("OUTPUT_Results").DArr.ToList();
if (results.Count == 0 || results.Any(u => u < 0))
{
LogAsync(DateTime.Now, $"{k.AliasName}鍘熷鏁版嵁寮傚父", "");
}
else
{
+ //results = results.Select(u => double.Parse(u.ToString("f2"))).ToList();
LogAsync(DateTime.Now, $"{k.AliasName}鍘熷鏁版嵁", $"{string.Join(" ", results)}");
resultDict = k.KeyResultList.ToDictionary(u => u, u =>
{
--
Gitblit v1.8.0