using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace ExcelThingy
{
    internal class Item
    {
        public string Code { get; set; }
        public double? Price { get; set; }
    }

    class Program
    {
        // путь к файлу
        const string FilePath = "testing2.xlsx";

        // колонка с кодами вещей, которые мы будем сравнивать
        const string FirstCodeColumn = "A";
        // колонка с ценами вещей, которые мы будем сравнивать
        const string FirstPriceColumn = "B";
        // колонка с кодами вещей, с которыми мы будем сравнивать
        const string SecondCodeColumn = "D";
        // колонка с ценами вещей, с которыми мы будем сравнивать
        const string SecondPriceColumn = "E";

        static void Main(string[] args)
        {

            // создаем пару словарей, в первом будут храниться коды и цены из первых колонок
            // во втором- цены и коды из второй пары колонок
            var originalItems = new List<Item>();
            var otherItems = new List<Item>();

            // открываем xlsx файлик на чтение
            using (var spreadsheetDocument =
                SpreadsheetDocument.Open(FilePath, true))
            {
                var workbookPart = spreadsheetDocument.WorkbookPart;
                var worksheetPart = workbookPart.WorksheetParts.First();
                var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                var stringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                var stringTable = stringTablePart.SharedStringTable;

                foreach (var row in sheetData.Elements<Row>())
                {
                    // пропускаем первую строку, если надо
                    if (row.RowIndex == 1) continue;

                    // собираем все интересующие нас ячейки
                    var cells = row.Elements<Cell>().ToList();
                    var firstPriceCell = cells.FirstOrDefault(c => c.CellReference.Value.Equals(FirstPriceColumn + row.RowIndex));
                    var firstCodeCell = cells.FirstOrDefault(c => c.CellReference.Value.Equals(FirstCodeColumn + row.RowIndex));
                    var secondPriceCell = cells.FirstOrDefault(c => c.CellReference.Value.Equals(SecondPriceColumn + row.RowIndex));
                    var secondCodeCell = cells.FirstOrDefault(c => c.CellReference.Value.Equals(SecondCodeColumn + row.RowIndex));

                    // вытягиваем информацию из этих ячеек и наполняем наши словари
                    var firstPrice = ReadCellValue(firstPriceCell, stringTable);
                    var firstCode = ReadCellValue(firstCodeCell, stringTable);
                    var secondPrice = ReadCellValue(secondPriceCell, stringTable);
                    var secondCode = ReadCellValue(secondCodeCell, stringTable);

                    if (!string.IsNullOrWhiteSpace(firstCode))
                        originalItems.Add(new Item
                        {
                            Code = firstCode,
                            Price = double.Parse(firstPrice, NumberStyles.Float)
                        });

                    if (!string.IsNullOrWhiteSpace(secondCode))
                        otherItems.Add(new Item
                        {
                            Code = secondCode,
                            Price = double.Parse(secondPrice, NumberStyles.Float)
                        });
                }

                // находим все позиции из первой пары колонок с меньшей ценой
                var cheaperItems = new List<Item>();

                // группируем первую пару столбцов по ключу
                var groups = originalItems.GroupBy(i => i.Code);

                foreach (var g in groups)
                {
                    // находим элемент, с которым необходимо сравнить элементы из группы
                    var compareTo = otherItems.FirstOrDefault(i => i.Code == g.Key);
                    if(compareTo == null)
                        continue;

                    // сравниваем и добавляем те, что дешевле, в коллекцию-результат
                    cheaperItems.AddRange(g.Where(i => i.Price < compareTo.Price));
                }


                // создаем новый лист в документе
                var newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());

                var sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                var relationshipId =
                    spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart);

                var sheetId = 1u;
                if (sheets.Elements<Sheet>().Any())
                {
                    sheetId =
                        sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                var sheetName = "CheaperItems" + sheetId;

                var sheet = new Sheet
                {
                    Id = relationshipId, SheetId = sheetId, Name = sheetName
                };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();

                // проходимся по словарю вещей на запись и пишем по две ячейки в каждую строку
                var rowIndex = 1u;
                foreach(var item in cheaperItems)
                {
                    var codeCell = InsertCellInWorksheet(FirstCodeColumn, rowIndex, newWorksheetPart);
                    codeCell.CellValue = new CellValue(item.Code);
                    codeCell.DataType = CellValues.String;
                    var priceCell = InsertCellInWorksheet(FirstPriceColumn, rowIndex, newWorksheetPart);
                    priceCell.CellValue = new CellValue(item.Price + "");
                    priceCell.DataType = CellValues.Number;
                    rowIndex++;
                }
                workbookPart.Workbook.Save();


            } // после этой скобки поток закроется и файл станет доступным для других процессов
        }

        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Count(r => r.RowIndex == rowIndex) != 0)
            {
                row = sheetData.Elements<Row>().First(r => r.RowIndex == rowIndex);
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.
            if (row.Elements<Cell>().Any(c => c.CellReference.Value == columnName + rowIndex))
            {
                return row.Elements<Cell>().First(c => c.CellReference.Value == cellReference);
            }
            // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            var refCell = row.Elements<Cell>().FirstOrDefault(cell => string.Compare(cell.CellReference.Value, cellReference, true) > 0);

            var newCell = new Cell() { CellReference = cellReference };
            row.InsertBefore(newCell, refCell);

            worksheet.Save();
            return newCell;
        }

        private static string ReadCellValue(Cell cell, SharedStringTable stringTable)
        {
            if (cell == null || cell.CellValue == null)
                return null;
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
            {
                var ssid = int.Parse(cell.CellValue.Text);
                return stringTable.ChildElements[ssid].InnerText;
            }

            if ((cell.DataType != null) && (cell.DataType == CellValues.Number))
            {
                // формат дробных чисел между экселем и .NET может не совпадать, поэтому возможно будет необходимо заменить дробную запятую на дробную точку
                return cell.CellValue.Text.Replace(',', '.');
            }

            return cell.CellValue.Text;
        }
    }
}