SecMPS/api_sqlsugar/VolPro.Core/Generic/GenericExcelImportHelper.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using OfficeOpenXml;
using VolPro.Core.Extensions;
using VolPro.Core.UserManager;
using VolPro.Core.Utilities;

namespace VolPro.Core.Generic
{
    internal static class GenericExcelImportHelper
    {
        /// <summary>
        /// 从 Excel 流读取数据，按 CellOptions 校验表头和字典，
        /// 返回 WebResponseContent，成功时 Data 为 List&lt;Dictionary&lt;string,object&gt;&gt;
        /// </summary>
        public static WebResponseContent ReadRowsByCellOptions(string tableName, Stream excelStream, int importStartRowIndex = 1,
            HashSet<string> ignoreFields=null)
        {
            var response = WebResponseContent.Instance;
            var resultRows = new List<Dictionary<string, object>>();
            if (excelStream == null || !excelStream.CanRead)
                return response.Error("未能读取上传的文件".Translator());

            var cellOptions = GetCellOptions(tableName);
            if (cellOptions == null || cellOptions.Count == 0)
                return response.Error($"未找到表【{tableName}】的导出配置".Translator());

            using var package = new ExcelPackage(excelStream);
            var sheet = package.Workbook.Worksheets.FirstOrDefault();
            if (sheet?.Dimension == null || sheet.Dimension.End.Row <= importStartRowIndex)
                return response.Error("导入文件中没有数据".Translator());
            if (ignoreFields!=null)
            {
                cellOptions = cellOptions.Where(x => !ignoreFields.Contains(x.ColumnName)).ToList();
            }
       
            if (!BindHeaderIndexes(sheet, cellOptions, importStartRowIndex, out string headerError))
                return response.Error(headerError);

            var dicNoKeys = cellOptions
              .Where(x => !string.IsNullOrEmpty(x.DropNo) && x.KeyValues != null && x.KeyValues.Keys.Count > 0)
              .Select(x => (x.ColumnName, x.SearchType, x.EditType))
              .Distinct()
              .ToList();

            var multiSelectColumns = dicNoKeys
                .Where(x => x.SearchType == "checkbox" || x.SearchType == "selectList" || x.SearchType == "treeSelect"
                         || x.EditType == "checkbox" || x.EditType == "selectList" || x.EditType == "treeSelect")
                .Select(x => x.ColumnName)
                .ToArray();

            // 日期字段类型（用于 Excel 数字日期转换）
            var dateFields = TableColumnContext.Data
                .Where(x => x.TableName == tableName && (x.ColumnType == "Date" || x.ColumnType == "DateTime"))
                .Select(s => s.ColumnName)
                .ToHashSet(StringComparer.OrdinalIgnoreCase);

            int rowStart = importStartRowIndex + 1;
            int rowEnd = sheet.Dimension.End.Row;
            int colStart = sheet.Dimension.Start.Column;
            int colEnd = sheet.Dimension.End.Column;

            for (int r = rowStart; r <= rowEnd; r++)
            {
                var rowDict = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                bool hasAnyValue = false;

                for (int c = colStart; c <= colEnd; c++)
                {
                    var opt = cellOptions.FirstOrDefault(x => x.Index == c);
                    if (opt == null) continue;

                    string raw = sheet.Cells[r, c].Value?.ToString();
                    raw = raw?.Trim();

                    if (string.IsNullOrEmpty(raw))
                    {
                        if (opt.Requierd)
                        {
                            string msg = "第[{$ts}]行,[{$ts}]验证未通过,不能为空"
                                .TranslatorFormat(r, opt.ColumnCNName);
                            return response.Error(msg);
                        }

                        rowDict[opt.ColumnName] = null;
                        continue;
                    }

                    hasAnyValue = true;

                    // 日期校验与转换（参考 EPPlusHelper.ReadToDataTable 中的日期处理）：
                    // 如果当前字段在 dateFields 中，且值为长度 5 的数字（Excel 序列号），则转换成 DateTime。
                    if (dateFields.Contains(opt.ColumnName)
                        && raw.Length == 5
                        && int.TryParse(raw, out int days))
                    {
                        var dt = new DateTime(1900, 1, 1).AddDays(days - 2);
                        rowDict[opt.ColumnName] = dt;
                        continue;
                    }

                    if (string.IsNullOrEmpty(opt.DropNo))
                    {
                        rowDict[opt.ColumnName] = raw;
                        continue;
                    }

                    if (opt.KeyValues == null)
                    {
                        return response.Error("[{$ts}]数据字典缺失".TranslatorFormat(opt.ColumnCNName));
                    }

                    string key = null;
                    if (multiSelectColumns.Contains(opt.ColumnName))
                    {
                        var cellValues = raw.Replace("，", ",")
                            .Split(",", StringSplitOptions.RemoveEmptyEntries);
                        var keys = opt.KeyValues
                            .Where(x => cellValues.Contains(x.Value))
                            .Select(s => s.Key)
                            .ToArray();
                        if (cellValues.Length == keys.Length)
                        {
                            key = string.Join(",", keys);
                        }
                    }
                    else
                    {
                        key = opt.KeyValues
                            .Where(x => x.Value == raw)
                            .Select(s => s.Key)
                            .FirstOrDefault();
                    }

                    if (key == null)
                    {
                        string values = string.Join(",",
                            opt.KeyValues
                                .Take(300)
                                .Select(s => s.Value.Translator()));
                        string msg = "第[{$ts}]行,[{$ts}]验证未通过,只能填写[{$ts}]"
                            .TranslatorFormat(r, opt.ColumnCNName, values);
                        return response.Error(msg);
                    }
                    rowDict[opt.ColumnName] = key;
                }

                if (!hasAnyValue) continue;
                resultRows.Add(rowDict);
            }

            return response.OK(null, resultRows);
        }

        private static List<CellOptions> GetCellOptions(string tableName)
        {
            var mi = typeof(EPPlusHelper).GetMethod(
                "GetExportColumnInfo",
                BindingFlags.NonPublic | BindingFlags.Static);
            if (mi == null) return null;

            return mi.Invoke(null, new object[] { tableName, false, false, null }) as List<CellOptions>;
        }

        private static bool BindHeaderIndexes(
            ExcelWorksheet sheet,
            List<CellOptions> cellOptions,
            int headerRow,
            out string error)
        {
            error = null;

            for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
            {
                string columnCNName = sheet.Cells[headerRow, j].Value?.ToString()?.Trim();
                if (string.IsNullOrEmpty(columnCNName)) continue;

                var options = cellOptions
                    .FirstOrDefault(x => x.ColumnCNName.Translator() == columnCNName);

                if (options == null)
                {
                    error = "[{$ts}]不是模板中的列".TranslatorReplace(columnCNName, true);
                    return false;
                }
                if (options.Index > 0)
                {
                    error = "[{$ts}]列名重复".TranslatorReplace(columnCNName, true);
                    return false;
                }
                options.Index = j;
            }

            if (cellOptions.Exists(x => x.Index == 0))
            {
                var errorOps = cellOptions
                    .Where(x => x.Index == 0)
                    .Select(s => s.ColumnCNName.Translator() + "," + s.ColumnName);
                error = $"{"导入文件列未找到字段".Translator()}:{string.Join("; ", errorOps)}";
                return false;
            }

            return true;
        }
    }
}