public static Lot GetLotData(string orderFileName)
{
Lot lot = new Lot();
// Open file
excel = new ExcelService(orderFileName);
// Check for sheets count
if (excel.GetSheetsCount(false) < 3)
{
excel.AddSheet("");
}
// Parse file and get data
excel.SetSheetByIndex(1);
// Check for space between hat and broker
if (string.IsNullOrEmpty(excel.GetCell(4, "B")))
{
excel.InsertRow(4);
}
lot.Number = "1";
lot.Name = excel.GetCell(9, "C");
var quantityCell = excel.GetCell(13, "C");
try {
if (quantityCell != null)
{
lot.Quantity = string.IsNullOrEmpty(quantityCell) ? 0 : quantityCell.ToLower().Contains("прилож") ? 0 : Convert.ToDecimal(quantityCell);
}
else
{
lot.Quantity = 0;
}
} catch { lot.Quantity = 0; }
Regex regex = new Regex(@"[\d]*[,]*[\d]*");
lot.Price = lot.Quantity == 0 ? 0 : Convert.ToDecimal(regex.Match(excel.GetCell(12, "C").Replace(" ", "")).Value);
lot.Sum = lot.Quantity == 0 ? Convert.ToDecimal(regex.Match(excel.GetCell(15, "C").Replace(" ", "")).Value) : lot.Quantity * lot.Price;
try {
lot.Step = Convert.ToDecimal(regex.Match(excel.GetCell(16, "C")).Value);
} catch { lot.Step = 0; }
lot.PaymentTerm = excel.GetCell(17, "C");
lot.DeliveryPlace = excel.GetCell(18, "C");
lot.DeliveryTime = excel.GetCell(19, "C");
lot.LocalContent = Convert.ToDecimal(regex.Match(excel.GetCell(21, "C")).Value);
lot.Warranty = Convert.ToDecimal(regex.Match(excel.GetCell(24, "C")).Value);
// Parse tech spec part for lot ex
excel.SetSheetByIndex(2);
lot.LotsExtended = new System.Collections.ObjectModel.ObservableCollection <LotsExtended>();
int startRow = excel.FindRow("Наименование");
if (startRow != 0)
{
int iRow = startRow + 1;
int iCount = 1;
while (!string.IsNullOrEmpty(excel.GetCell(iRow, "A")) || !string.IsNullOrEmpty(excel.GetCell(iRow, "B")))
{
lot.LotsExtended.Add(new LotsExtended()
{
serialnumber = iCount,
name = excel.GetCell(iRow, "B"),
unit = excel.GetCell(iRow, "C"),
quantity = Convert.ToDecimal(excel.GetCell(iRow, "D")),
price = Convert.ToDecimal(excel.GetCell(iRow, "E")),
sum = Convert.ToDecimal(excel.GetCell(iRow, "F")),
country = string.IsNullOrEmpty(excel.GetCell(iRow, "G")) ? "" : excel.GetCell(iRow, "G"),
techspec = excel.GetCell(iRow, "H"),
terms = excel.GetCell(iRow, "I"),
paymentterm = excel.GetCell(iRow, "J"),
dks = Convert.ToInt32(Math.Round(Convert.ToDecimal(regex.Match(excel.GetCell(iRow, "K")).Value), 0)),
contractnumber = string.IsNullOrEmpty(excel.GetCell(iRow, "L")) ? "" : excel.GetCell(iRow, "L")
});
iCount++;
iRow++;
}
lot.Dks = lot.LotsExtended[0].dks;
}
// close excel
excel.CloseWorkbook(false);
excel.CloseExcel();
// Return collected data
return(lot);
}