LPLocalhostMES/LocalhostMES/Helpers/KeyPartExcelImporter.cs

using LocalhostMES.Enums;
using LocalhostMES.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Xml.Linq;

namespace LocalhostMES.Helpers
{
    public static class KeyPartExcelImporter
    {
        private sealed class RowData
        {
            public string HandlePosition { get; set; }
            public string MaterialCode { get; set; }
            public string WireHarness { get; set; }
            public string Actuator { get; set; }
            public string CoverPlate { get; set; }
        }

        public static List<LocalhostPartInfo> BuildPartInfosFromExcel(string excelPath)
        {
            if (string.IsNullOrWhiteSpace(excelPath))
            {
                throw new ArgumentException("Excel路径不能为空", nameof(excelPath));
            }
            if (!File.Exists(excelPath))
            {
                throw new FileNotFoundException("Excel文件不存在", excelPath);
            }

            var rows = ReadRows(excelPath);
            var parts = new List<LocalhostPartInfo>();
            foreach (var row in rows)
            {
                if (string.IsNullOrWhiteSpace(row.MaterialCode))
                {
                    continue;
                }

                var isLeft = (row.HandlePosition ?? string.Empty).Contains("左");
                var side = isLeft ? "L" : "R";

                parts.Add(CreatePart(
                    StationType.OP10,
                    row.MaterialCode,
                    row.MaterialCode,
                    "把手零件号",
                    string.Empty,
                    string.Empty,
                    1m));

                parts.Add(CreatePart(
                    StationType.OP20L,
                    row.WireHarness,
                    row.MaterialCode,
                    "线束",
                    "HANDLE-ZP01-L-02",
                    "HANDLE-ZP01-L-02-015",
                    1m));

                parts.Add(CreatePart(
                    StationType.OP20R,
                    row.WireHarness,
                    row.MaterialCode,
                    "线束",
                    "HANDLE-ZP01-R-02",
                    "HANDLE-ZP01-R-02-01",
                    1m));

                parts.Add(CreatePart(
                    isLeft ? StationType.OP30L : StationType.OP30R,
                    row.MaterialCode,
                    row.MaterialCode,
                    string.Empty,
                    $"HANDLE-ZP01-{side}-03",
                    $"HANDLE-ZP01-{side}-03-01",
                    1m));

                parts.Add(CreatePart(
                    isLeft ? StationType.OP40L : StationType.OP40R,
                    row.MaterialCode,
                    row.MaterialCode,
                    string.Empty,
                    $"HANDLE-ZP01-{side}-04",
                    $"HANDLE-ZP01-{side}-04-01",
                    1m));

                parts.Add(CreatePart(
                    isLeft ? StationType.OP50L : StationType.OP50R,
                    row.MaterialCode,
                    row.MaterialCode,
                    string.Empty,
                    $"HANDLE-ZP01-{side}-05",
                    $"HANDLE-ZP01-{side}-05-01",
                    1m));

                parts.Add(CreatePart(
                    isLeft ? StationType.OP60L : StationType.OP60R,
                    row.CoverPlate,
                    row.MaterialCode,
                    "盖板",
                    $"HANDLE-ZP01-{side}-06",
                    $"HANDLE-ZP01-{side}-06-01",
                    1m));

                parts.Add(CreatePart(
                    isLeft ? StationType.OP70L : StationType.OP70R,
                    row.Actuator,
                    row.MaterialCode,
                    "执行器",
                    $"HANDLE-ZP01-{side}-07",
                    $"HANDLE-ZP01-{side}-07-01",
                    1m));
            }

            return parts
                .Where(p => p != null)
                .GroupBy(p => new { p.StationType, p.PartNum })
                .Select(g => g.Last())
                .ToList();
        }

        private static LocalhostPartInfo CreatePart(
            StationType stationType,
            string partNum,
            string materialCode,
            string partInfoName,
            string stationCode,
            string positionCode,
            decimal partQty)
        {
            var pn = (partNum ?? string.Empty).Trim();
            var material = (materialCode ?? string.Empty).Trim();
            if (string.IsNullOrWhiteSpace(pn))
            {
                return null;
            }

            return new LocalhostPartInfo
            {
                StationType = stationType,
                PartNum = pn,
                MaterialCode = material,
                PartInfoName = (partInfoName ?? string.Empty).Trim(),
                StationCode = (stationCode ?? string.Empty).Trim(),
                PositionCode = (positionCode ?? string.Empty).Trim(),
                PartQty = partQty
            };
        }

        private static List<RowData> ReadRows(string excelPath)
        {
            var rows = new List<RowData>();
            using (var stream = File.OpenRead(excelPath))
            using (var zip = new ZipArchive(stream, ZipArchiveMode.Read, false))
            {
                var sharedStrings = ReadSharedStrings(zip);
                var sheetEntry = zip.GetEntry("xl/worksheets/sheet1.xml")
                    ?? zip.Entries.FirstOrDefault(e => e.FullName.StartsWith("xl/worksheets/sheet", StringComparison.OrdinalIgnoreCase));
                if (sheetEntry == null)
                {
                    return rows;
                }

                XDocument sheetXml;
                using (var sheetStream = sheetEntry.Open())
                {
                    sheetXml = XDocument.Load(sheetStream);
                }

                XNamespace ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
                bool headerFound = false;
                foreach (var row in sheetXml.Descendants(ns + "row"))
                {
                    var cellMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
                    foreach (var c in row.Elements(ns + "c"))
                    {
                        var refName = c.Attribute("r")?.Value;
                        if (string.IsNullOrEmpty(refName))
                        {
                            continue;
                        }

                        var col = GetColumnLetters(refName);
                        cellMap[col] = ReadCellValue(c, sharedStrings, ns);
                    }

                    var bVal = GetCell(cellMap, "B");
                    var dVal = GetCell(cellMap, "D");
                    if (!headerFound)
                    {
                        if ((bVal ?? string.Empty).Contains("手柄位置") || (dVal ?? string.Empty).Contains("把手总成零件号"))
                        {
                            headerFound = true;
                        }
                        continue;
                    }

                    if (string.IsNullOrWhiteSpace(dVal))
                    {
                        continue;
                    }

                    rows.Add(new RowData
                    {
                        HandlePosition = bVal,
                        MaterialCode = dVal,
                        WireHarness = GetCell(cellMap, "F"),
                        Actuator = GetCell(cellMap, "G"),
                        CoverPlate = GetCell(cellMap, "H")
                    });
                }
            }

            return rows;
        }

        private static string GetCell(Dictionary<string, string> row, string column)
        {
            return row.TryGetValue(column, out var v) ? v : string.Empty;
        }

        private static List<string> ReadSharedStrings(ZipArchive zip)
        {
            var entry = zip.GetEntry("xl/sharedStrings.xml");
            if (entry == null)
            {
                return new List<string>();
            }

            XDocument doc;
            using (var s = entry.Open())
            {
                doc = XDocument.Load(s);
            }

            XNamespace ns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
            return doc.Descendants(ns + "si")
                .Select(si =>
                {
                    var t = si.Element(ns + "t");
                    if (t != null)
                    {
                        return t.Value;
                    }
                    return string.Concat(si.Descendants(ns + "t").Select(x => x.Value));
                })
                .ToList();
        }

        private static string ReadCellValue(XElement c, List<string> sharedStrings, XNamespace ns)
        {
            var type = c.Attribute("t")?.Value;
            if (string.Equals(type, "inlineStr", StringComparison.OrdinalIgnoreCase))
            {
                return c.Element(ns + "is")?.Element(ns + "t")?.Value ?? string.Empty;
            }

            var v = c.Element(ns + "v")?.Value ?? string.Empty;
            if (string.Equals(type, "s", StringComparison.OrdinalIgnoreCase) && int.TryParse(v, out var idx))
            {
                if (idx >= 0 && idx < sharedStrings.Count)
                {
                    return sharedStrings[idx];
                }
                return string.Empty;
            }

            return v;
        }

        private static string GetColumnLetters(string cellReference)
        {
            if (string.IsNullOrEmpty(cellReference))
            {
                return string.Empty;
            }
            var chars = cellReference.TakeWhile(ch => char.IsLetter(ch)).ToArray();
            return new string(chars);
        }
    }
}