RhSolutions-AddIn/RhSolutions.AddIn/Services/GuessReader.cs

234 lines
6.7 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

using System.IO;
namespace RhSolutions.Services;
public class GuessReader : IReader
{
private readonly Application _application;
private ProgressBar _progressBar;
public GuessReader(Application application)
{
_application = application;
}
public Dictionary<Product, double> ReadProducts(Range range)
{
_progressBar = new("Ищу колонку с артикулами...", range.Columns.Count);
int? productColumnIndex = null;
for (int column = 1; column < range.Columns.Count + 1; column++)
{
_progressBar.Update();
if (IsProductColumn(range.Columns[column]))
{
productColumnIndex = column;
break;
}
}
if (productColumnIndex == null)
{
throw new ArgumentException("Колонка с артикулом не определена");
}
int? amountColumnIndex = null;
int currentIndex = productColumnIndex.Value + 1;
_progressBar = new("Ищу колонку с количеством...", range.Columns.Count);
while (currentIndex > 0)
{
_progressBar.Update();
if (currentIndex > range.Columns.Count + 1)
{
currentIndex = productColumnIndex.Value - 1;
continue;
}
if (currentIndex > productColumnIndex)
{
if (IsAmountColumn(range.Columns[currentIndex]))
{
amountColumnIndex = currentIndex;
break;
}
else currentIndex++;
}
else
{
if (IsAmountColumn(range.Columns[currentIndex]))
{
amountColumnIndex = currentIndex;
break;
}
else currentIndex--;
}
}
if (amountColumnIndex == null)
{
throw new ArgumentException("Колонка с количеством не определена");
}
else
{
return GetDictionaryFromColumns(range.Columns[productColumnIndex],
range.Columns[amountColumnIndex]);
}
}
private bool IsProductColumn(Range column)
{
int successCounter = 0;
var cells = column.Value2;
if (cells == null)
{
return false;
}
for (int row = 1; row < column.Rows.Count + 1; row++)
{
object currentCell = column.Rows.Count == 1 ? cells : cells[row, 1];
if (currentCell == null)
{
continue;
}
if (ProductSku.TryParse(currentCell.ToString(), out _))
{
successCounter++;
}
if (successCounter > 2)
{
return true;
}
}
return successCounter > 0;
}
private bool IsAmountColumn(Range column)
{
int successCounter = 0;
var cells = column.Value2;
double maxValue = 30000;
if (cells == null)
{
return false;
}
if (column.Rows.Count == 1)
{
double? value = cells as double?;
return value != null
&& value != 0
&& value < maxValue;
}
else
{
for (int row = 1; row < column.Rows.Count + 1; row++)
{
object currentCell = cells[row, 1];
double? value = currentCell as double?;
if (value == null
|| value == 0
|| value > maxValue)
{
continue;
}
if (++successCounter > 3)
{
return true;
}
}
}
return successCounter > 1;
}
private Dictionary<Product, double> GetDictionaryFromColumns(Range productColumn, Range amountColumn)
{
Dictionary<Product, double> result = new();
var firstRowIndex = 1;
var lastRowIndex = amountColumn.Worksheet
.Cells[amountColumn.Worksheet.Rows.Count, amountColumn.Column]
.End[XlDirection.xlUp].Row;
_progressBar = new("Заполняю словарь значений...", lastRowIndex);
Worksheet worksheet = amountColumn.Worksheet;
for (int row = firstRowIndex; row < lastRowIndex + 1; row++)
{
_progressBar.Update();
object currentAmountCell = worksheet.Cells[row, amountColumn.Column].Value2;
object currentProductCell = worksheet.Cells[row, productColumn.Column].Value2;
double? amountValue = currentAmountCell as double?;
if (amountValue == null || amountValue == 0 || currentProductCell == null)
{
continue;
}
if (ProductSku.TryParse(currentProductCell.ToString(), out IEnumerable<ProductSku> skus))
{
Product p = new(skus.First())
{
Name = "Распознанный артикул"
};
if (result.ContainsKey(p))
{
result[p] += amountValue.Value;
}
else
{
result.Add(p, amountValue.Value);
}
}
}
return result;
}
public IEnumerable<(string, Dictionary<Product, double>)> ReadProducts(IEnumerable<Worksheet> worksheets)
{
List<(string, Dictionary<Product, double>)> result = new();
foreach (Worksheet worksheet in worksheets)
{
string wbName = Path.GetFileNameWithoutExtension(
worksheet.Parent.Name);
Range range = worksheet.UsedRange;
var productsDict = ReadProducts(range);
result.Add((wbName, productsDict));
}
return result;
}
public IEnumerable<(string, Dictionary<Product, double>)> ReadProducts(string[] files)
{
_progressBar = new("Открываю исходные файлы...", files.Length);
List<Worksheet> worksheets = new();
_application.ScreenUpdating = false;
foreach (string file in files)
{
Workbook wb = _application.Workbooks.Open(file);
worksheets.Add(wb.ActiveSheet);
_progressBar.Update();
}
_application.ScreenUpdating = true;
var result = ReadProducts(worksheets);
foreach (var ws in worksheets)
{
ws.Parent.Close();
}
return result;
}
public void Dispose()
{
_progressBar?.Dispose();
}
}