public PriceListModel Parse(UserInputMessage message)
{
using (var package = new ExcelPackage(new FileInfo(message.FileUrl)))
{
var result = new PriceListModel
{
Items = new List <PriceListItemModel>()
};
var sheet = package.Workbook.Worksheets.FirstOrDefault();
if (sheet == null)
{
throw new FileNotFoundException($"file {message.FileUrl} not found or does not have any sheets");
}
var rowCnt = sheet?.Dimension?.End?.Row ?? 0;
for (int rowNum = 1; rowNum < rowCnt + 1; rowNum++)
{
var row = new FileRow
{
Id = sheet.Cells[rowNum, message.TitleColumn].Value?.ToString(),
Price = sheet.Cells[rowNum, message.PriceColumn].Value?.ToString()
};
var rowModel = _parseInputRowService.ParsePriceFromRow(row);
if (rowModel != null)
{
result.Items.Add(rowModel);
}
}
return(result);
}
}
public IParser GetParser(UserInputMessage message)
{
if (message.FileUrl.ToLower().EndsWith(".xls"))
{
return(new XlsParser(_parseInputRowService));
}
return(new XlsxParser(_parseInputRowService));
}
public byte[] ComparePrices(UserInputMessage oldPriceSettings, UserInputMessage newPriceSettings)
{
var oldPrice = _parserFactory.GetParser(oldPriceSettings).Parse(oldPriceSettings);
var newPrice = _parserFactory.GetParser(newPriceSettings).Parse(newPriceSettings);
var compareResult = _comparePriceTableService.CompareTables(oldPrice, newPrice);
var resultFile = _createResultFileService.GenerateResultFile(compareResult);
return(resultFile);
}
private void CompareBtn_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(_oldPriceUrl))
{
MessageBox.Show("Select old price file!");
return;
}
if (string.IsNullOrEmpty(_newPriceUrl))
{
MessageBox.Show("Select new price file!");
return;
}
int op;
int ot;
int np;
int nt;
if (
!(int.TryParse(OldPriceColumn.Text, out op) && int.TryParse(OldTitleColumn.Text, out ot) &&
int.TryParse(NewPriceColumn.Text, out np) && int.TryParse(NewTitleColumn.Text, out nt)))
{
MessageBox.Show("column name must be number");
return;
}
var old = new UserInputMessage
{
FileUrl = _oldPriceUrl,
PriceColumn = op,
TitleColumn = ot,
};
var newFile = new UserInputMessage
{
FileUrl = _newPriceUrl,
PriceColumn = op,
TitleColumn = ot,
};
var compareResultFile = _compareService.ComparePrices(old, newFile);
if (CompareResultSaveDialog.ShowDialog() == DialogResult.OK)
{
using (var myStream = CompareResultSaveDialog.OpenFile())
{
myStream.Write(compareResultFile, 0, compareResultFile.Length);
myStream.Close();
}
}
}
public async Task ParseInputFile_NotExistingFile_TellCorrectType()
{
var message = new UserInputMessage
{
FileUrl = System.AppDomain.CurrentDomain.BaseDirectory + "\\badFile.xlsx",
PriceColumn = "A",
TitleColumn = "B"
};
var response = await _parser.Ask(message);
Assert.NotNull((response as Failure)?.Exception, response.ToString());
var result = await GetFakeMessage();
Assert.AreEqual(0, result.Count);
}
public async Task ParseInputFile_ExistingEmptyFile_TellCorrectType()
{
var message = new UserInputMessage
{
FileUrl = System.AppDomain.CurrentDomain.BaseDirectory + "\\..\\..\\TestData\\empty.xlsx",
PriceColumn = "A",
TitleColumn = "B"
};
var parserResponse = await _parser.Ask(message);
Assert.NotNull((parserResponse) as CompletedMessage, parserResponse.ToString());
var response = await GetFakeMessage();
Assert.AreEqual(1, response.Count);
var result = response[0] as List <PriceRow>;
Assert.NotNull(result);
Assert.AreEqual(0, result.Count);
}
public PriceListModel Parse(UserInputMessage message)
{
FileStream stream = File.Open(message.FileUrl, FileMode.Open, FileAccess.Read);
//Choose one of either 1 or 2
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
//Choose one of either 3, 4, or 5
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet table = excelReader.AsDataSet();
var rowCnt = table.Tables[0].Rows.Count;
var result = new PriceListModel
{
Items = new List <PriceListItemModel>()
};
for (int rowNum = 0; rowNum < rowCnt; rowNum++)
{
var row = new FileRow
{
Id = table.Tables[0].Rows[rowNum].ItemArray[message.TitleColumn - 1]?.ToString(),
Price = table.Tables[0].Rows[rowNum].ItemArray[message.PriceColumn - 1]?.ToString()
};
var rowModel = _parseInputRowService.ParsePriceFromRow(row);
if (rowModel != null)
{
result.Items.Add(rowModel);
}
}
return(result);
}
}
public async Task ParseInputFile_ExistingNonEmptyFile_TellCorrectList()
{
var message = new UserInputMessage
{
FileUrl = System.AppDomain.CurrentDomain.BaseDirectory + "\\..\\..\\TestData\\test.xlsx",
PriceColumn = "C",
TitleColumn = "A"
};
var parserResponse = await _parser.Ask(message);
Assert.NotNull((parserResponse) as CompletedMessage, parserResponse.ToString());
var response = await GetFakeMessage();
Assert.AreEqual(1, response.Count);
var result = response[0] as List <PriceRow>;
Assert.NotNull(result);
Assert.AreEqual(2, result.Count);
Assert.IsTrue(result.Any(x => string.Equals("id1", x.Id, StringComparison.InvariantCultureIgnoreCase)));
Assert.IsTrue(result.Any(x => string.Equals("id3", x.Id, StringComparison.InvariantCultureIgnoreCase)));
}