Excel输出4个sheet，横向排布 解决启动延迟问题

xcd

2020-07-15 bbac60058524198c13c4a22943719f853ef8e2bc

Excel输出4个sheet，横向排布
解决启动延迟问题

 src/Bro.Common.Device/DeviceBase/HDevEngineTool.cs

@@ -250,7 +250,7 @@

            for (int i = 0; i < tuple.Length; i++)
            {
                list.Add(tuple[i]);
                list.Add(tuple[i].D);
            }

            return list;

 src/Bro.M071.Model/Model/MeasurementUnitResult.cs

@@ -48,6 +48,10 @@
        public string ProductionCode { get; set; }
        [NotMapped]
        public string ProductionBarcode { get; set; }
        [NotMapped]
        public string Keys { get; set; }
        [NotMapped]
        public string Positions { get; set; }
    }

    public class MeasurementUnitResultRequest : BaseRequest

 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,19 +608,22 @@

            await Task.Run(() =>
            {
                lock (excelExportLock)
                {
                if (!Directory.Exists(Config.LogPath))
                {
                    Directory.CreateDirectory(Config.LogPath);
                }
                var fileName = Path.Combine(Config.LogPath, $"LDSData_{DateTime.Now.ToString("yyyyMMdd")}.xlsx");
                bool isExisted = File.Exists(fileName);
                    //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 (!package.Workbook.Worksheets.Any(s => s.Name == "RawData"))
                    {
                        package.Workbook.Worksheets.Add("RawData");
                        rawDataSheet = package.Workbook.Worksheets["RawData"];
@@ -616,47 +631,309 @@
                        {
                            var cell = rawDataSheet.Cells[i + 3, 1];
                            cell.Value = Config.KeyNameCollection[i];
                            cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(31, 73, 125));
                                SetTitleCell(cell, false);
                        }

                        //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");
                    }

                    rawDataSheet = package.Workbook.Worksheets["RawData"];

                        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 + 1 + 4];
                            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}的RawData未能获取行信息，未导出");
                                    return;
                                }

                        int zIndex = int.Parse(k.MeasurementItem.Substring(1));

                        var cell = rawDataSheet.Cells[keyIndex + 1 + 2, rowDataStartCol + zIndex];
                        cell.Value = k.ItemValue;
                                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}的Slant未能获取行信息，未导出");
                                    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}的Alignment未能获取行信息，未导出");
                                        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}的RowAlignment未能获取行信息，未导出");
                                        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 =>
                                              {

 src/Bro.M071.Process/M071Process_MotionCard.cs

@@ -30,10 +30,10 @@
                if (machineState == value)
                    return;

                //_machineStatePre = machineState;

                machineState = value;

                Task.Run(() =>
                {
                switch (machineState)
                {
                    case MachineState.Ready:
@@ -46,7 +46,6 @@

                        Task.Run(() =>
                        {

                            while (MachineState == MachineState.Ready)
                            {
                                lock (machineStateLock)
@@ -113,7 +112,6 @@
                            {
                                lock (machineStateLock)
                                {

                                    SwitchLightYellow(true);
                                    Thread.Sleep(1000);
                                    SwitchLightYellow(false);
@@ -125,17 +123,9 @@
                    default:
                        break;
                }
                });

                OnMachineStateChanged?.Invoke(machineState);

                //if (_machineStatePre == MachineState.Running && machineState == MachineState.Pause)
                //{
                //    Pause();
                //}
                //else if (_machineStatePre == MachineState.Pause && (machineState == MachineState.Running || machineState == MachineState.Ready))
                //{
                //    Resume();
                //}
            }
        }