bird_tool/bird_tool/ExcelLoad.cs


using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using OfficeOpenXml;
using Newtonsoft.Json;

namespace digital
{
    public class ExcelDataManager
    {
        private readonly string _excelPath;
        private readonly string _progressPath;
        private readonly List<Dictionary<string, string>> _rows = new List<Dictionary<string, string>>();
        private readonly HashSet<string> _usedKeys = new HashSet<string>();
        private int _currentIndex = 0;
        private string[] _headers;

        // 主键列名（用于进度跟踪）
        private string _primaryKeyColumn = "账号";

        public ExcelDataManager(string excelPath, string progressPath = "progress.json", string primaryKeyColumn = "账号")
        {
            _excelPath = excelPath;
            _progressPath = progressPath;
            _primaryKeyColumn = primaryKeyColumn;
            LoadExcelData();
            LoadProgress();
        }

        private void LoadExcelData()
        {
            FileInfo file = new FileInfo(_excelPath);
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                // 读取标题行
                _headers = new string[colCount];
                for (int col = 1; col <= colCount; col++)
                {
                    _headers[col - 1] = worksheet.Cells[1, col].Text.Trim();
                }

                // 读取数据行
                for (int row = 2; row <= rowCount; row++)
                {
                    var rowData = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
                    for (int col = 1; col <= colCount; col++)
                    {
                        string header = _headers[col - 1];
                        string cellValue = worksheet.Cells[row, col].Text.Trim();
                        rowData[header] = cellValue;
                    }

                    // 只添加有主键的行
                    if (rowData.ContainsKey(_primaryKeyColumn))
                    {
                        _rows.Add(rowData);
                    }
                }
            }
        }

        // 顺序获取下一行数据
        public Dictionary<string, string> GetNextRow()
        {
            while (_currentIndex < _rows.Count)
            {
                var row = _rows[_currentIndex++];
                string key = GetPrimaryKey(row);

                if (!_usedKeys.Contains(key))
                {
                    _usedKeys.Add(key);
                    return new Dictionary<string, string>(row);
                }
            }
            return null;
        }

        // 根据主键值获取整行数据
        public Dictionary<string, string> GetRowByKey(string keyValue)
        {
            if (string.IsNullOrWhiteSpace(keyValue)) return null;

            var row = _rows.FirstOrDefault(r =>
                GetPrimaryKey(r).Equals(keyValue, StringComparison.OrdinalIgnoreCase) &&
                !_usedKeys.Contains(GetPrimaryKey(r)));

            if (row != null)
            {
                _usedKeys.Add(GetPrimaryKey(row));
                return new Dictionary<string, string>(row);
            }
            return null;
        }

        // 根据任意列查询获取整行数据
        public Dictionary<string, string> FindRowByColumnValue(string columnName, string columnValue)
        {
            if (string.IsNullOrWhiteSpace(columnName)) return null;
            if (string.IsNullOrWhiteSpace(columnValue)) return null;

            var row = _rows.FirstOrDefault(r =>
                r.ContainsKey(columnName) &&
                r[columnName].Equals(columnValue, StringComparison.OrdinalIgnoreCase) &&
                !_usedKeys.Contains(GetPrimaryKey(r)));

            if (row != null)
            {
                _usedKeys.Add(GetPrimaryKey(row));
                return new Dictionary<string, string>(row);
            }
            return null;
        }

        // 根据主键值获取指定列的值
        public string GetColumnValue(string keyValue, string columnName)
        {
            var row = GetRowByKey(keyValue);
            return row != null && row.TryGetValue(columnName, out string value) ? value : null;
        }

        // 根据任意列查询获取指定列的值
        public string GetColumnValueByQuery(string queryColumn, string queryValue, string resultColumn)
        {
            var row = FindRowByColumnValue(queryColumn, queryValue);
            return row != null && row.TryGetValue(resultColumn, out string value) ? value : null;
        }

        // 获取主键值
        private string GetPrimaryKey(Dictionary<string, string> row)
        {
            return row.TryGetValue(_primaryKeyColumn, out string value) ? value : "";
        }

        // 保存进度
        public void SaveProgress()
        {
            var progress = new ProgressData
            {
                CurrentIndex = _currentIndex,
                UsedKeys = _usedKeys.ToList()
            };

            File.WriteAllText(_progressPath, JsonConvert.SerializeObject(progress));
        }

        // 加载进度
        private void LoadProgress()
        {
            if (File.Exists(_progressPath))
            {
                try
                {
                    var json = File.ReadAllText(_progressPath);
                    var progress = JsonConvert.DeserializeObject<ProgressData>(json);

                    _currentIndex = progress.CurrentIndex;
                    _usedKeys.UnionWith(progress.UsedKeys);
                }
                catch
                {
                    ResetProgress();
                }
            }
        }

        // 重置进度
        public void ResetProgress()
        {
            _currentIndex = 0;
            _usedKeys.Clear();
            SaveProgress();
        }

        // 获取所有列名
        public IEnumerable<string> GetColumnNames()
        {
            return _headers;
        }
        // 查找所有匹配的行（不标记为已使用）
        public List<Dictionary<string, string>> FindAllRowsByColumnValue(string columnName, string columnValue)
        {
            return _rows.Where(r =>
                r.ContainsKey(columnName) &&
                r[columnName].Equals(columnValue, StringComparison.OrdinalIgnoreCase))
                .Select(r => new Dictionary<string, string>(r))
                .ToList();
        }

        // 多条件查询
        public Dictionary<string, string> FindRowByMultipleConditions(
            params (string column, string value)[] conditions)
        {
            foreach (var row in _rows)
            {
                bool match = true;
                string key = GetPrimaryKey(row);

                if (_usedKeys.Contains(key)) continue;

                foreach (var condition in conditions)
                {
                    if (!row.TryGetValue(condition.column, out string value) ||
                        !value.Equals(condition.value, StringComparison.OrdinalIgnoreCase))
                    {
                        match = false;
                        break;
                    }
                }

                if (match)
                {
                    _usedKeys.Add(key);
                    return new Dictionary<string, string>(row);
                }
            }
            return null;
        }

        // 获取当前进度状态
        public (int total, int used, int remaining) GetProgressStatus()
        {
            int total = _rows.Count;
            int used = _usedKeys.Count;
            return (total, used, total - used);
        }

        // 标记主键为已使用
        public void MarkKeyAsUsed(string keyValue)
        {
            if (!string.IsNullOrWhiteSpace(keyValue))
            {
                _usedKeys.Add(keyValue);
            }
        }

        private class ProgressData
        {
            public int CurrentIndex { get; set; }
            public List<string> UsedKeys { get; set; }
        }
    }
}