< Summary

Information
Class: Services.ParserServices.FielParsers.ExcelParser
Assembly: Services
File(s): /home/runner/work/Studio2Prod/Studio2Prod/AppRepository/ParserServices/FielParsers/ExcelParser.cs
Line coverage
0%
Covered lines: 0
Uncovered lines: 593
Coverable lines: 593
Total lines: 1027
Line coverage: 0%
Branch coverage
0%
Covered branches: 0
Total branches: 338
Branch coverage: 0%
Method coverage

Feature is only available for sponsors

Upgrade to PRO version

Metrics

MethodBranch coverage Crap Score Cyclomatic complexity Line coverage
.ctor(...)100%210%
UpdateTableToStandartColumns(...)0%210140%
NormalizeHeaderName(...)0%620%
ConvertCsvToExcel(...)0%4260%
SplitCsvLine(...)0%7280%
IsXlsFormat(...)0%7280%
GetCellValueFromNpoi(...)0%342180%
GetTableHeadersAsync()100%210%
GetTableHeadersFromXls(...)0%4260%
GetTableHeadersFromXlsx(...)0%620%
GetTableDataAsync()100%210%
GetTableDataFromXls(...)0%420200%
GetTableDataFromXlsx(...)0%272160%
GetTableDataAsyncForDropbox()100%210%
IsNumeric(...)100%210%
MatchExcelDataAsync()0%210140%
MatchExcelDataAsync()0%1056320%
CompareValues(...)0%812280%
GetTableDataFromXlsAsync()100%210%
GetDateFromWorksheet(...)0%210140%
ParseDateString(...)0%4260%
ProcessPriceCell(...)0%7280%
GetCellValue(...)0%1640400%

File(s)

/home/runner/work/Studio2Prod/Studio2Prod/AppRepository/ParserServices/FielParsers/ExcelParser.cs

#LineLine coverage
 1using OfficeOpenXml;
 2using OzonDomains.Models.MatchedRowSys;
 3using Services.ParserServices.FielParsers.Models;
 4using Services.ReleasServices.ReleaseManager;
 5using System.Globalization;
 6using System.Text;
 7using System.Text.RegularExpressions;
 8using NPOI.SS.UserModel;
 9using NPOI.HSSF.UserModel;
 10
 11namespace Services.ParserServices.FielParsers
 12{
 13    public class ExcelParser
 14    {
 15        private readonly ReleaseManager _releaseManager;
 16        private CultureInfo _culture;
 17
 018        public ExcelParser(ReleaseManager releaseManager)
 19        {
 020            _releaseManager = releaseManager;
 021            _culture = _releaseManager.GetCultureInfo();
 022            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
 023        }
 24
 25        public List<Dictionary<string, string>> UpdateTableToStandartColumns(List<Dictionary<string, string>> table,
 26            Dictionary<string, string> columnMappings)
 27        {
 028            var updatedTableData = new List<Dictionary<string, string>>();
 29
 30            // Определяем служебные столбцы, которые нужно сохранить как есть
 031            var preservedColumns = new HashSet<string> { "Целая часть", "Дробная часть" };
 32
 33            // Создаем нормализованный словарь для быстрого поиска
 34            // Нормализуем значения из columnMappings для сравнения
 035            var normalizedMappings = new Dictionary<string, (string OriginalKey, string OriginalValue)>();
 036            foreach (var mapping in columnMappings)
 37            {
 038                if (!string.IsNullOrEmpty(mapping.Value))
 39                {
 040                    var normalizedValue = NormalizeHeaderName(mapping.Value);
 41                    // Если уже есть такой нормализованный ключ, используем первый найденный
 042                    if (!normalizedMappings.ContainsKey(normalizedValue))
 43                    {
 044                        normalizedMappings[normalizedValue] = (mapping.Key, mapping.Value);
 45                    }
 46                }
 47            }
 48
 049            foreach (var row in table)
 50            {
 051                var updatedRow = new Dictionary<string, string>();
 52
 053                foreach (var column in row)
 54                {
 55                    // Если столбец нужно сохранить как есть
 056                    if (preservedColumns.Contains(column.Key))
 57                    {
 058                        updatedRow.Add(column.Key, column.Value);
 059                        continue;
 60                    }
 61
 62                    // Нормализуем имя столбца для сравнения
 063                    var normalizedColumnKey = NormalizeHeaderName(column.Key);
 64
 65                    // Ищем сопоставление по нормализованному значению
 066                    if (normalizedMappings.TryGetValue(normalizedColumnKey, out var mapping))
 67                    {
 068                        updatedRow.Add(mapping.OriginalKey, column.Value);
 69                    }
 70                }
 71
 072                updatedTableData.Add(updatedRow);
 73            }
 74
 075            return updatedTableData;
 76        }
 77
 78        /// <summary>
 79        /// Нормализует имя заголовка для сравнения: убирает лишние пробелы, нормализует переносы строк
 80        /// </summary>
 81        private string NormalizeHeaderName(string headerName)
 82        {
 083            if (string.IsNullOrEmpty(headerName))
 084                return string.Empty;
 85
 86            // Заменяем различные варианты переносов строк на пробелы
 087            var normalized = headerName
 088                .Replace("\r\n", " ") // Windows line break
 089                .Replace("\n", " ") // Unix line break
 090                .Replace("\r", " ") // Old Mac line break
 091                .Replace("\t", " ") // Tabs
 092                .Replace("\u00A0", " ") // Non-breaking space
 093                .Replace("\u2009", " ") // Thin space
 094                .Replace("\u200A", " ") // Hair space
 095                .Replace("\u202F", " ") // Narrow no-break space
 096                .Replace("\u205F", " "); // Medium mathematical space
 97
 98            // Убираем множественные пробелы и обрезаем
 099            normalized = Regex.Replace(normalized, @"\s+", " ").Trim();
 100
 0101            return normalized;
 102        }
 103
 104        public MemoryStream ConvertCsvToExcel(Stream csvStream, char delimiter)
 105        {
 0106            csvStream.Position = 0;
 107
 0108            var excelStream = new MemoryStream();
 109
 0110            using (var package = new ExcelPackage())
 111            {
 0112                var worksheet = package.Workbook.Worksheets.Add("Sheet1");
 113
 0114                using (var reader = new StreamReader(csvStream, Encoding.UTF8))
 115                {
 0116                    int row = 1;
 0117                    while (!reader.EndOfStream)
 118                    {
 0119                        string? line = reader.ReadLine();
 0120                        if (line == null)
 0121                            break;
 0122                        string[] cells = SplitCsvLine(line, delimiter);
 123
 0124                        for (int col = 0; col < cells.Length; col++)
 125                        {
 0126                            worksheet.Cells[row, col + 1].Value = cells[col];
 127                        }
 128
 0129                        row++;
 130                    }
 0131                }
 132
 0133                package.SaveAs(excelStream);
 0134            }
 135
 0136            excelStream.Position = 0;
 0137            return excelStream;
 138        }
 139
 140        private string[] SplitCsvLine(string line, char delimiter)
 141        {
 0142            List<string> cells = new List<string>();
 143
 0144            bool inQuotes = false;
 0145            StringBuilder currentCell = new StringBuilder();
 146
 0147            for (int i = 0; i < line.Length; i++)
 148            {
 0149                char c = line[i];
 150
 0151                if (c == '"')
 152                {
 0153                    inQuotes = !inQuotes;
 154                }
 0155                else if (c == delimiter && !inQuotes)
 156                {
 0157                    cells.Add(currentCell.ToString().Trim('"'));
 0158                    currentCell.Clear();
 159                }
 160                else
 161                {
 0162                    currentCell.Append(c);
 163                }
 164            }
 165
 0166            cells.Add(currentCell.ToString().Trim('"'));
 0167            return cells.ToArray();
 168        }
 169
 170
 171        /// <summary>
 172        /// Определяет формат Excel файла по потоку
 173        /// </summary>
 174        private bool IsXlsFormat(Stream stream)
 175        {
 0176            if (!stream.CanSeek)
 0177                return false;
 178
 0179            var position = stream.Position;
 180            try
 181            {
 0182                stream.Seek(0, SeekOrigin.Begin);
 0183                var header = new byte[8];
 0184                stream.Read(header, 0, 8);
 0185                stream.Seek(position, SeekOrigin.Begin);
 186
 187                // .xls файлы начинаются с определенной сигнатурой
 188                // OLE2 файлы начинаются с 0xD0CF11E0A1B11AE1
 0189                return header[0] == 0xD0 && header[1] == 0xCF && header[2] == 0x11 && header[3] == 0xE0;
 190            }
 0191            catch
 192            {
 0193                stream.Seek(position, SeekOrigin.Begin);
 0194                return false;
 195            }
 0196        }
 197
 198        /// <summary>
 199        /// Получает значение ячейки из NPOI
 200        /// </summary>
 201        private string GetCellValueFromNpoi(ICell cell)
 202        {
 0203            if (cell == null)
 0204                return string.Empty;
 205
 0206            switch (cell.CellType)
 207            {
 208                case CellType.Numeric:
 0209                    if (DateUtil.IsCellDateFormatted(cell))
 210                    {
 0211                        return (cell.DateCellValue ?? DateTime.MinValue)
 0212                            .ToString("dd.MM.yyyy", CultureInfo.InvariantCulture);
 213                    }
 214
 0215                    return cell.NumericCellValue.ToString(_culture);
 216                case CellType.String:
 0217                    return cell.StringCellValue;
 218                case CellType.Boolean:
 0219                    return cell.BooleanCellValue.ToString();
 220                case CellType.Formula:
 221                    try
 222                    {
 0223                        if (cell.CachedFormulaResultType == CellType.String)
 0224                            return cell.StringCellValue;
 0225                        else if (cell.CachedFormulaResultType == CellType.Numeric)
 0226                            return cell.NumericCellValue.ToString(_culture);
 0227                        else if (cell.CachedFormulaResultType == CellType.Boolean)
 0228                            return cell.BooleanCellValue.ToString();
 229                        else
 0230                            return cell.StringCellValue;
 231                    }
 0232                    catch
 233                    {
 0234                        return cell.NumericCellValue.ToString(_culture);
 235                    }
 236                default:
 0237                    return string.Empty;
 238            }
 0239        }
 240
 241        public async Task<List<string>> GetTableHeadersAsync(Stream excelStream, int startRow = 1, int startColumn = 1)
 242        {
 0243            return await Task.Run(() =>
 0244            {
 0245                // Создаем копию потока для чтения, чтобы избежать проблем с позицией
 0246                using (var streamCopy = new MemoryStream())
 0247                {
 0248                    if (excelStream.CanSeek)
 0249                    {
 0250                        excelStream.Seek(0, SeekOrigin.Begin);
 0251                    }
 0252                    excelStream.CopyTo(streamCopy);
 0253                    streamCopy.Position = 0;
 0254
 0255                    // Проверяем формат файла
 0256                    if (IsXlsFormat(streamCopy))
 0257                    {
 0258                        return GetTableHeadersFromXls(streamCopy, startRow, startColumn);
 0259                    }
 0260                    else
 0261                    {
 0262                        return GetTableHeadersFromXlsx(streamCopy, startRow, startColumn);
 0263                    }
 0264                }
 0265            });
 0266        }
 267
 268        private List<string> GetTableHeadersFromXls(Stream excelStream, int startRow, int startColumn)
 269        {
 0270            var headers = new List<string>();
 271
 0272            if (excelStream.CanSeek)
 273            {
 0274                excelStream.Seek(0, SeekOrigin.Begin);
 275            }
 276
 0277            using (var workbook = new HSSFWorkbook(excelStream, true))
 278            {
 0279                var worksheet = workbook.GetSheetAt(0);
 0280                var headerRow = worksheet.GetRow(startRow - 1); // NPOI использует 0-based индексы
 281
 0282                if (headerRow != null)
 283                {
 0284                    for (int col = startColumn - 1; col < headerRow.LastCellNum; col++)
 285                    {
 0286                        var cell = headerRow.GetCell(col);
 0287                        headers.Add(GetCellValueFromNpoi(cell));
 288                    }
 289                }
 0290            }
 291
 0292            return headers;
 293        }
 294
 295        private List<string> GetTableHeadersFromXlsx(Stream excelStream, int startRow, int startColumn)
 296        {
 0297            var headers = new List<string>();
 298
 0299            using (var package = new ExcelPackage(excelStream))
 300            {
 0301                var worksheet = package.Workbook.Worksheets[0];
 302
 0303                foreach (var cell in worksheet.Cells[startRow, startColumn, startRow,
 0304                             worksheet.Dimension.End.Column])
 305                {
 0306                    headers.Add(cell.Text);
 307                }
 308            }
 309
 0310            return headers;
 311        }
 312
 313        public async Task<List<Dictionary<string, string>>> GetTableDataAsync(Stream excelStream, int startRow = 1,
 314            int startColumn = 1)
 315        {
 0316            return await Task.Run(() =>
 0317            {
 0318                // Создаем копию потока для чтения, чтобы избежать проблем с позицией
 0319                using (var streamCopy = new MemoryStream())
 0320                {
 0321                    if (excelStream.CanSeek)
 0322                    {
 0323                        excelStream.Seek(0, SeekOrigin.Begin);
 0324                    }
 0325                    excelStream.CopyTo(streamCopy);
 0326                    streamCopy.Position = 0;
 0327
 0328                    // Проверяем формат файла
 0329                    if (IsXlsFormat(streamCopy))
 0330                    {
 0331                        return GetTableDataFromXls(streamCopy, startRow, startColumn);
 0332                    }
 0333                    else
 0334                    {
 0335                        return GetTableDataFromXlsx(streamCopy, startRow, startColumn);
 0336                    }
 0337                }
 0338            });
 0339        }
 340
 341        private List<Dictionary<string, string>> GetTableDataFromXls(Stream excelStream, int startRow, int startColumn)
 342        {
 0343            var tableData = new List<Dictionary<string, string>>();
 344
 0345            if (excelStream.CanSeek)
 346            {
 0347                excelStream.Seek(0, SeekOrigin.Begin);
 348            }
 349
 0350            using (var workbook = new HSSFWorkbook(excelStream, true))
 351            {
 0352                var worksheet = workbook.GetSheetAt(0);
 0353                var headers = new List<string>();
 354
 355                // Получаем заголовки
 0356                var headerRow = worksheet.GetRow(startRow - 1);
 0357                if (headerRow != null)
 358                {
 0359                    for (int col = startColumn - 1; col < headerRow.LastCellNum; col++)
 360                    {
 0361                        var cell = headerRow.GetCell(col);
 0362                        headers.Add(GetCellValueFromNpoi(cell));
 363                    }
 364                }
 365
 366                // Получаем данные
 0367                for (int rowIndex = startRow; rowIndex <= worksheet.LastRowNum; rowIndex++)
 368                {
 0369                    var row = worksheet.GetRow(rowIndex);
 0370                    if (row == null) continue;
 371
 0372                    var rowData = new Dictionary<string, string>();
 0373                    bool rowIsEmpty = true;
 374
 0375                    for (int col = startColumn - 1; col < row.LastCellNum; col++)
 376                    {
 0377                        var cell = row.GetCell(col);
 0378                        string cellText = GetCellValueFromNpoi(cell);
 379
 0380                        if (!string.IsNullOrWhiteSpace(cellText))
 381                        {
 0382                            rowIsEmpty = false;
 383                        }
 384
 0385                        int headerIndex = col - (startColumn - 1);
 0386                        if (headerIndex < headers.Count && !string.IsNullOrWhiteSpace(headers[headerIndex]))
 387                        {
 0388                            rowData[headers[headerIndex]] = cellText;
 389                        }
 390                    }
 391
 0392                    if (!rowIsEmpty)
 393                    {
 0394                        tableData.Add(rowData);
 395                    }
 396                }
 0397            }
 398
 0399            return tableData;
 400        }
 401
 402        private List<Dictionary<string, string>> GetTableDataFromXlsx(Stream excelStream, int startRow, int startColumn)
 403        {
 0404            var tableData = new List<Dictionary<string, string>>();
 405
 0406            using (var package = new ExcelPackage(excelStream))
 407            {
 0408                var worksheet = package.Workbook.Worksheets[0];
 0409                var headers = new List<string>();
 410
 0411                foreach (var cell in worksheet.Cells[startRow, startColumn, startRow,
 0412                             worksheet.Dimension.End.Column])
 413                {
 0414                    headers.Add(cell.Text);
 415                }
 416
 0417                for (int row = startRow + 1; row <= worksheet.Dimension.End.Row; row++)
 418                {
 0419                    var rowData = new Dictionary<string, string>();
 0420                    bool rowIsEmpty = true;
 421
 0422                    for (int col = startColumn; col <= worksheet.Dimension.End.Column; col++)
 423                    {
 0424                        var cell = worksheet.Cells[row, col];
 425                        string cellText;
 426
 0427                        if (cell.Value is DateTime dateValue)
 428                        {
 0429                            cellText = dateValue.ToString("dd.MM.yyyy", CultureInfo.InvariantCulture);
 430                        }
 431                        else
 432                        {
 0433                            cellText = cell.Text;
 434                        }
 435
 0436                        if (!string.IsNullOrWhiteSpace(cellText))
 437                        {
 0438                            rowIsEmpty = false;
 439                        }
 440
 0441                        if (col - startColumn < headers.Count &&
 0442                            !string.IsNullOrWhiteSpace(headers[col - startColumn]))
 443                        {
 0444                            rowData[headers[col - startColumn]] = cellText;
 445                        }
 446                    }
 447
 0448                    if (!rowIsEmpty)
 449                    {
 0450                        tableData.Add(rowData);
 451                    }
 452                }
 0453            }
 454
 0455            return tableData;
 456        }
 457
 458        public async Task<List<Dictionary<string, string>>> GetTableDataAsyncForDropbox(Stream excelStream,
 459            int startRow = 1, int startColumn = 1)
 460        {
 0461            return await Task.Run(() =>
 0462            {
 0463                var tableData = new List<Dictionary<string, string>>();
 0464
 0465                using (var package = new ExcelPackage(excelStream))
 0466                {
 0467                    var worksheet = package.Workbook.Worksheets[0];
 0468                    var headers = new List<string>();
 0469
 0470                    // Собираем заголовки
 0471                    foreach (var cell in worksheet.Cells[startRow, startColumn, startRow,
 0472                                 worksheet.Dimension.End.Column])
 0473                    {
 0474                        headers.Add(cell.Text);
 0475                    }
 0476
 0477                    // Добавляем новые заголовки для частей цены
 0478                    var extendedHeaders = new List<string>(headers);
 0479                    extendedHeaders.Add("Целая часть");
 0480                    extendedHeaders.Add("Дробная часть");
 0481
 0482                    for (int row = startRow + 1; row <= worksheet.Dimension.End.Row; row++)
 0483                    {
 0484                        var rowData = new Dictionary<string, string>();
 0485                        bool rowIsEmpty = true;
 0486
 0487                        for (int col = startColumn; col <= worksheet.Dimension.End.Column; col++)
 0488                        {
 0489                            var cell = worksheet.Cells[row, col];
 0490                            string cellText;
 0491                            string[] formats = { "dd/MM/yyyy", "MM/dd/yyyy", "yyyy-MM-dd" };
 0492                            CultureInfo provider = CultureInfo.InvariantCulture;
 0493
 0494                            string? rawText = cell.Text?.Trim();
 0495                            if (rawText != null &&
 0496                                (col - startColumn) < headers.Count &&
 0497                                string.Equals(headers[col - startColumn]?.Trim(), "Цена",
 0498                                    StringComparison.OrdinalIgnoreCase))
 0499                            {
 0500                                string normalizedText = rawText.Replace(" ", "").Replace("'", "");
 0501                                string[] parts;
 0502
 0503                                if (normalizedText.Contains(','))
 0504                                {
 0505                                    parts = normalizedText.Split(',');
 0506                                }
 0507                                else if (normalizedText.Contains('.'))
 0508                                {
 0509                                    parts = normalizedText.Split('.');
 0510                                }
 0511                                else
 0512                                {
 0513                                    parts = new string[] { normalizedText };
 0514                                }
 0515
 0516                                if (parts.Length == 2)
 0517                                {
 0518                                    rowData["Целая часть"] = parts[0];
 0519                                    rowData["Дробная часть"] = parts[1];
 0520                                    cellText = normalizedText;
 0521                                    Console.WriteLine($"Разделено: целая='{parts[0]}', дробная='{parts[1]}'");
 0522                                }
 0523                                else if (parts.Length == 1)
 0524                                {
 0525                                    rowData["Целая часть"] = parts[0];
 0526                                    rowData["Дробная часть"] = "0"; // или string.Empty
 0527                                    cellText = normalizedText;
 0528                                    Console.WriteLine($"Только целая часть: '{parts[0]}'");
 0529                                }
 0530                                else
 0531                                {
 0532                                    cellText = rawText;
 0533                                    rowData["Целая часть"] = string.Empty;
 0534                                    rowData["Дробная часть"] = string.Empty;
 0535                                    Console.WriteLine($"Некорректный формат цены: '{rawText}'");
 0536                                }
 0537                            }
 0538                            else if (rawText != null && DateTime.TryParseExact(rawText, formats, provider, DateTimeStyle
 0539                                         out DateTime dateValue))
 0540                            {
 0541                                cellText = dateValue.ToString("dd.MM.yyyy", new CultureInfo("ru-RU"));
 0542                            }
 0543                            else
 0544                            {
 0545                                cellText = rawText ?? string.Empty;
 0546                            }
 0547
 0548                            if (!string.IsNullOrWhiteSpace(cellText))
 0549                            {
 0550                                rowIsEmpty = false;
 0551                            }
 0552
 0553                            if (col - startColumn < headers.Count &&
 0554                                !string.IsNullOrWhiteSpace(headers[col - startColumn]))
 0555                            {
 0556                                rowData[headers[col - startColumn]!] = cellText;
 0557                            }
 0558                        }
 0559
 0560                        if (!rowIsEmpty)
 0561                        {
 0562                            tableData.Add(rowData);
 0563                        }
 0564                    }
 0565                }
 0566
 0567                return tableData;
 0568            });
 0569        }
 570
 571        public static bool IsNumeric(object Expression)
 572        {
 573            double retNum;
 574
 0575            bool isNum = Double.TryParse(Convert.ToString(Expression), System.Globalization.NumberStyles.Any,
 0576                System.Globalization.NumberFormatInfo.InvariantInfo, out retNum);
 0577            return isNum;
 578        }
 579
 580        public async Task<List<MatchedRowModel>> MatchExcelDataAsync(Stream file1Stream,
 581            Stream file2Stream,
 582            List<MatchingColumn> matchingColumns,
 583            int startRow1,
 584            int startColumn1,
 585            int startRow2,
 586            int startColumn2)
 587        {
 0588            var matchedRows = new List<MatchedRowModel>();
 589
 0590            var headersFile1 = await GetTableHeadersAsync(file1Stream, startRow1, startColumn1);
 0591            file1Stream.Position = 0;
 0592            var dataFile1 = await GetTableDataAsync(file1Stream, startRow1, startColumn1);
 593
 0594            var headersFile2 = await GetTableHeadersAsync(file2Stream, startRow2, startColumn2);
 0595            file2Stream.Position = 0;
 0596            var dataFile2 = await GetTableDataAsync(file2Stream, startRow2, startColumn2);
 597
 0598            headersFile1 = headersFile1.Distinct().ToList();
 0599            headersFile2 = headersFile2.Distinct().ToList();
 600
 0601            var unmatchedRowsFromFile2 = new List<Dictionary<string, string>>();
 602
 0603            foreach (var rowFile1 in dataFile1)
 604            {
 0605                var matchedRow = new MatchedRowModel
 0606                {
 0607                    File1Data = rowFile1,
 0608                    File2Data = null
 0609                };
 610
 0611                var rowsToRemove = new List<Dictionary<string, string>>();
 0612                foreach (var rowFile2 in dataFile2)
 613                {
 0614                    bool isMatch = matchingColumns.All(match =>
 0615                    {
 0616                        var match1 = match.Item1?.ToLower().Trim();
 0617                        var match2 = match.Item2?.ToLower().Trim();
 0618                        if (string.IsNullOrEmpty(match1) || string.IsNullOrEmpty(match2) ||
 0619                            !rowFile1.Any(e => e.Key.ToLower().Trim() == match1) ||
 0620                            !rowFile2.Any(e => e.Key.ToLower().Trim() == match2))
 0621                        {
 0622                            return false;
 0623                        }
 0624
 0625                        var rowFile1List = rowFile1.ToList();
 0626                        var rowFile2List = rowFile2.ToList();
 0627
 0628                        var index1 = rowFile1List.FindIndex(e => e.Key.ToLower().Trim() == match1);
 0629                        var index2 = rowFile2List.FindIndex(e => e.Key.ToLower().Trim() == match2);
 0630
 0631                        if (index1 != -1 && index2 != -1)
 0632                        {
 0633                            var value1 = rowFile1List[index1].Value;
 0634                            var value2 = rowFile2List[index2].Value;
 0635                            return CompareValues(value1, value2);
 0636                        }
 0637
 0638                        return false;
 0639                    });
 640
 0641                    if (isMatch)
 642                    {
 0643                        if (matchedRow.File2Data == null)
 644                        {
 0645                            matchedRow.File2Data = new List<Dictionary<string, string>>();
 646                        }
 647
 0648                        matchedRow.File2Data.Add(rowFile2);
 0649                        rowsToRemove.Add(rowFile2);
 650                    }
 651                }
 652
 0653                matchedRows.Add(matchedRow);
 654
 0655                foreach (var row in rowsToRemove)
 656                {
 0657                    dataFile2.Remove(row);
 658                }
 659            }
 660
 0661            foreach (var rowFile2 in dataFile2)
 662            {
 0663                unmatchedRowsFromFile2.Add(rowFile2);
 664            }
 665
 0666            foreach (var unmatchedRow in unmatchedRowsFromFile2)
 667            {
 0668                matchedRows.Add(new MatchedRowModel
 0669                {
 0670                    File1Data = null,
 0671                    File2Data = new List<Dictionary<string, string>> { unmatchedRow }
 0672                });
 673            }
 674
 0675            return matchedRows;
 0676        }
 677
 678        public async Task<List<MatchedRowModel>> MatchExcelDataAsync(
 679            List<MatchedRowModel> matchedRows,
 680            Stream file1Stream,
 681            Stream file2Stream,
 682            List<MatchingColumn> matchingColumns,
 683            int startRow1,
 684            int startColumn1,
 685            int startRow2,
 686            int startColumn2)
 687        {
 0688            if (file1Stream == null || file2Stream == null || matchingColumns == null || matchedRows == null)
 689            {
 0690                return matchedRows ?? new List<MatchedRowModel>();
 691            }
 692
 0693            var headersFile1 = await GetTableHeadersAsync(file1Stream, startRow1, startColumn1);
 0694            file1Stream.Position = 0;
 0695            var dataFile1 = await GetTableDataAsync(file1Stream, startRow1, startColumn1);
 696
 0697            var headersFile2 = await GetTableHeadersAsync(file2Stream, startRow2, startColumn2);
 0698            file2Stream.Position = 0;
 0699            var dataFile2 = await GetTableDataAsync(file2Stream, startRow2, startColumn2);
 700
 0701            if (headersFile1 == null || headersFile2 == null || dataFile1 == null || dataFile2 == null)
 702            {
 0703                return matchedRows;
 704            }
 705
 0706            headersFile1 = headersFile1.Distinct().ToList();
 0707            headersFile2 = headersFile2.Distinct().ToList();
 708
 0709            var existingRows = new HashSet<string>(
 0710                matchedRows.SelectMany(m => m.File2Data ?? new List<Dictionary<string, string>>())
 0711                    .Select(row => string.Join("|", row?.Values?.ToList() ?? new List<string>())));
 712
 713
 0714            foreach (var rowFile1 in dataFile1)
 715            {
 0716                if (rowFile1 == null) continue;
 717
 0718                var matchedRow = new MatchedRowModel
 0719                {
 0720                    File1Data = rowFile1,
 0721                    File2Data = new List<Dictionary<string, string>>()
 0722                };
 723
 0724                foreach (var rowFile2 in dataFile2)
 725                {
 0726                    if (rowFile2 == null) continue;
 727
 0728                    bool isMatch = matchingColumns.All(match =>
 0729                    {
 0730                        if (string.IsNullOrEmpty(match.Item1) || string.IsNullOrEmpty(match.Item2) ||
 0731                            !rowFile1.ContainsKey(match.Item1) || !rowFile2.ContainsKey(match.Item2))
 0732                        {
 0733                            return false;
 0734                        }
 0735
 0736                        var value1 = rowFile1[match.Item1];
 0737                        var value2 = rowFile2[match.Item2];
 0738
 0739                        return CompareValues(value1, value2);
 0740                    });
 741
 0742                    if (isMatch)
 743                    {
 0744                        var rowKey = string.Join("|", rowFile2.Values);
 0745                        if (!existingRows.Contains(rowKey))
 746                        {
 0747                            matchedRow.File2Data.Add(rowFile2);
 0748                            existingRows.Add(rowKey);
 749                        }
 750                    }
 751                }
 752
 0753                if (matchedRow.File2Data.Any())
 754                {
 0755                    matchedRows.Add(matchedRow);
 756                }
 757            }
 758
 0759            return matchedRows;
 0760        }
 761
 762
 763        private bool CompareValues(object value1, object value2)
 764        {
 0765            var culture = _culture;
 766
 0767            var formats = new[] { "d.M.yyyy", "yyyy-MM-dd HH:mm:ss" };
 768
 0769            if (DateTime.TryParseExact(value1?.ToString(), formats, culture, DateTimeStyles.None, out DateTime date1) &&
 0770                DateTime.TryParseExact(value2?.ToString(), formats, culture, DateTimeStyles.None, out DateTime date2))
 771            {
 0772                return date1.Date == date2.Date;
 773            }
 774
 0775            if (decimal.TryParse(value1?.ToString(), NumberStyles.Any, culture, out decimal decimal1) &&
 0776                decimal.TryParse(value2?.ToString(), NumberStyles.Any, culture, out decimal decimal2))
 777            {
 0778                return Math.Floor(decimal1) == Math.Floor(decimal2);
 779            }
 780
 0781            if (int.TryParse(value1?.ToString(), NumberStyles.Any, culture, out int int1) &&
 0782                int.TryParse(value2?.ToString(), NumberStyles.Any, culture, out int int2))
 783            {
 0784                return int1 == int2;
 785            }
 786
 0787            return string.Equals(value1?.ToString(), value2?.ToString(), StringComparison.OrdinalIgnoreCase);
 788        }
 789
 790        public async Task<(List<Dictionary<string, string>> TableData, DateTime? DocumentDate)>
 791            GetTableDataFromXlsAsync(
 792                Stream excelStream, int startRow = 1, int startColumn = 1, int dateRow = 5, int dateColumn = 7)
 793        {
 0794            return await Task.Run(() =>
 0795            {
 0796                var tableData = new List<Dictionary<string, string>>();
 0797                DateTime? documentDate = null;
 0798
 0799                // Сбрасываем позицию потока
 0800                if (excelStream.CanSeek)
 0801                {
 0802                    excelStream.Seek(0, SeekOrigin.Begin);
 0803                }
 0804
 0805                using (var workbook = new HSSFWorkbook(excelStream, true)) // true = leaveOpen
 0806                {
 0807                    var worksheet = workbook.GetSheetAt(0); // Первый лист
 0808
 0809                    // Сначала получаем дату документа
 0810                    documentDate = GetDateFromWorksheet(worksheet, dateRow, dateColumn);
 0811
 0812                    // Затем обрабатываем строки с данными
 0813                    for (int rowIndex = startRow - 1; rowIndex <= worksheet.LastRowNum; rowIndex++)
 0814                    {
 0815                        var row = worksheet.GetRow(rowIndex);
 0816                        if (row == null) continue;
 0817
 0818                        var rowData = new Dictionary<string, string>();
 0819                        bool rowIsEmpty = true;
 0820
 0821                        // Обрабатываем ячейки в строке
 0822                        for (int col = startColumn - 1; col < row.LastCellNum; col++)
 0823                        {
 0824                            var cell = row.GetCell(col);
 0825                            string cellText = GetCellValue(cell);
 0826
 0827                            // Используем числовой индекс как ключ (начинаем с 1 для удобства)
 0828                            string columnKey = (col - (startColumn - 1) + 1).ToString();
 0829
 0830                            // Обработка цен (предполагаем, что цена в столбце с индексом 7, считая с 1)
 0831                            if (columnKey == "7") // price(RUR) столбец
 0832                            {
 0833                                ProcessPriceCell(cellText, rowData);
 0834                            }
 0835
 0836                            if (!string.IsNullOrWhiteSpace(cellText))
 0837                            {
 0838                                rowIsEmpty = false;
 0839                            }
 0840
 0841                            // Всегда добавляем данные, даже если пустые
 0842                            rowData[columnKey] = cellText;
 0843                        }
 0844
 0845                        // Добавляем дополнительные поля для частей цены
 0846                        if (!rowData.ContainsKey("Целая часть"))
 0847                            rowData["Целая часть"] = string.Empty;
 0848                        if (!rowData.ContainsKey("Дробная часть"))
 0849                            rowData["Дробная часть"] = string.Empty;
 0850
 0851                        if (!rowIsEmpty)
 0852                        {
 0853                            tableData.Add(rowData);
 0854                        }
 0855                    }
 0856                }
 0857
 0858                return (tableData, documentDate);
 0859            });
 0860        }
 861
 862        // Вспомогательный метод для получения даты из worksheet
 863        private DateTime? GetDateFromWorksheet(ISheet worksheet, int rowNumber, int columnNumber)
 864        {
 865            try
 866            {
 0867                var row = worksheet.GetRow(rowNumber - 1); // -1 потому что 0-based
 0868                if (row == null) return null;
 869
 0870                var cell = row.GetCell(columnNumber - 1); // -1 потому что 0-based
 0871                if (cell == null) return null;
 872
 873                // Обрабатываем разные типы ячеек
 0874                switch (cell.CellType)
 875                {
 876                    case CellType.Numeric:
 0877                        if (DateUtil.IsCellDateFormatted(cell))
 878                        {
 0879                            return cell.DateCellValue;
 880                        }
 881                        else
 882                        {
 0883                            string stringValue = cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
 0884                            return ParseDateString(stringValue);
 885                        }
 886
 887                    case CellType.String:
 0888                        return ParseDateString(cell.StringCellValue);
 889
 890                    case CellType.Formula:
 891                        try
 892                        {
 0893                            if (cell.CachedFormulaResultType == CellType.Numeric &&
 0894                                DateUtil.IsCellDateFormatted(cell))
 895                            {
 0896                                return cell.DateCellValue;
 897                            }
 898                            else
 899                            {
 0900                                return ParseDateString(cell.ToString());
 901                            }
 902                        }
 0903                        catch
 904                        {
 0905                            return ParseDateString(cell.ToString());
 906                        }
 907
 908                    default:
 0909                        return ParseDateString(cell.ToString());
 910                }
 911            }
 0912            catch (Exception ex)
 913            {
 0914                Console.WriteLine($"Ошибка при чтении даты: {ex.Message}");
 0915                return null;
 916            }
 0917        }
 918
 919        // Вспомогательный метод для парсинга даты из строки
 920        private DateTime? ParseDateString(string? dateString)
 921        {
 0922            if (string.IsNullOrWhiteSpace(dateString))
 0923                return null;
 924
 0925            dateString = dateString.Trim().Replace("\"", "");
 926
 0927            string[] formats =
 0928            {
 0929                "dd.MM.yyyy", "d.M.yyyy", "dd/MM/yyyy", "d/M/yyyy",
 0930                "MM/dd/yyyy", "M/d/yyyy", "yyyy-MM-dd", "yyyy-M-d",
 0931                "dd.MM.yy", "d.M.yy", "dd/MM/yy", "d/M/yy"
 0932            };
 933
 0934            if (DateTime.TryParseExact(dateString, formats, CultureInfo.InvariantCulture, DateTimeStyles.None,
 0935                    out DateTime result))
 936            {
 0937                return result;
 938            }
 939
 0940            if (DateTime.TryParse(dateString, CultureInfo.InvariantCulture, DateTimeStyles.None, out result))
 941            {
 0942                return result;
 943            }
 944
 0945            Console.WriteLine($"Не удалось распарсить дату: '{dateString}'");
 0946            return null;
 947        }
 948
 949        // Вспомогательный метод для обработки цены
 950        private void ProcessPriceCell(string cellText, Dictionary<string, string> rowData)
 951        {
 0952            string normalizedText = cellText.Replace(" ", "").Replace("'", "");
 953            string[] parts;
 954
 0955            if (normalizedText.Contains(','))
 956            {
 0957                parts = normalizedText.Split(',');
 958            }
 0959            else if (normalizedText.Contains('.'))
 960            {
 0961                parts = normalizedText.Split('.');
 962            }
 963            else
 964            {
 0965                parts = new string[] { normalizedText };
 966            }
 967
 0968            if (parts.Length == 2)
 969            {
 0970                rowData["Целая часть"] = parts[0];
 0971                rowData["Дробная часть"] = parts[1];
 0972                Console.WriteLine($"Разделено: целая='{parts[0]}', дробная='{parts[1]}'");
 973            }
 0974            else if (parts.Length == 1)
 975            {
 0976                rowData["Целая часть"] = parts[0];
 0977                rowData["Дробная часть"] = "0";
 0978                Console.WriteLine($"Только целая часть: '{parts[0]}'");
 979            }
 980            else
 981            {
 0982                rowData["Целая часть"] = string.Empty;
 0983                rowData["Дробная часть"] = string.Empty;
 0984                Console.WriteLine($"Некорректный формат цены: '{cellText}'");
 985            }
 0986        }
 987
 988        // Вспомогательный метод для получения значения ячейки
 989        private string GetCellValue(ICell cell)
 990        {
 0991            if (cell == null) return string.Empty;
 992
 0993            switch (cell.CellType)
 994            {
 995                case CellType.String:
 0996                    return cell.StringCellValue?.Trim() ?? string.Empty;
 997                case CellType.Numeric:
 0998                    if (DateUtil.IsCellDateFormatted(cell))
 999                    {
 01000                        return cell.DateCellValue?.ToString() ?? string.Empty;
 1001                    }
 1002
 01003                    return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
 1004                case CellType.Boolean:
 01005                    return cell.BooleanCellValue.ToString();
 1006                case CellType.Formula:
 1007                    try
 1008                    {
 01009                        if (cell.CachedFormulaResultType == CellType.String)
 01010                            return cell.StringCellValue?.Trim() ?? string.Empty;
 01011                        else if (cell.CachedFormulaResultType == CellType.Numeric)
 01012                            return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
 01013                        else if (cell.CachedFormulaResultType == CellType.Boolean)
 01014                            return cell.BooleanCellValue.ToString();
 1015                        else
 01016                            return cell.ToString()?.Trim() ?? string.Empty;
 1017                    }
 01018                    catch
 1019                    {
 01020                        return cell.ToString()?.Trim() ?? string.Empty;
 1021                    }
 1022                default:
 01023                    return cell.ToString()?.Trim() ?? string.Empty;
 1024            }
 01025        }
 1026    }
 1027}
Generated by: ReportGenerator 5.5.11.0
10/25/2026 - 07:43:48
GitHub | reportgenerator.io

Methods/Properties

.ctor(Services.ReleasServices.ReleaseManager.ReleaseManager)
UpdateTableToStandartColumns(System.Collections.Generic.List`1<System.Collections.Generic.Dictionary`2<System.String,System.String>>,System.Collections.Generic.Dictionary`2<System.String,System.String>)
NormalizeHeaderName(System.String)
ConvertCsvToExcel(System.IO.Stream,System.Char)
SplitCsvLine(System.String,System.Char)
IsXlsFormat(System.IO.Stream)
GetCellValueFromNpoi(NPOI.SS.UserModel.ICell)
GetTableHeadersAsync()
GetTableHeadersFromXls(System.IO.Stream,System.Int32,System.Int32)
GetTableHeadersFromXlsx(System.IO.Stream,System.Int32,System.Int32)
GetTableDataAsync()
GetTableDataFromXls(System.IO.Stream,System.Int32,System.Int32)
GetTableDataFromXlsx(System.IO.Stream,System.Int32,System.Int32)
GetTableDataAsyncForDropbox()
IsNumeric(System.Object)
MatchExcelDataAsync()
MatchExcelDataAsync()
CompareValues(System.Object,System.Object)
GetTableDataFromXlsAsync()
GetDateFromWorksheet(NPOI.SS.UserModel.ISheet,System.Int32,System.Int32)
ParseDateString(System.String)
ProcessPriceCell(System.String,System.Collections.Generic.Dictionary`2<System.String,System.String>)
GetCellValue(NPOI.SS.UserModel.ICell)