private IList <TransactionItem> ExtractTransactions(string fullPathFileName, IEnumerable <TransactionCriteria> criterias)
{
var dataList = new List <ExcelRow>();
var mappedItems = new List <TransactionItem>();
var qualifiedItems = new List <TransactionItem>();
using (var spreadsheetDocument = SpreadsheetDocument.Open(fullPathFileName, false))
{
var workbook = spreadsheetDocument.WorkbookPart;
var worksheet = workbook.WorksheetParts.First();
var sheetData = worksheet.Worksheet.Elements <SheetData>().First();
if (sheetData != null)
{
var rootElement = new XmlRootAttribute();
rootElement.ElementName = "row";
rootElement.IsNullable = true;
rootElement.Namespace = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
var test = sheetData.OuterXml;
foreach (var row in sheetData.Elements <Row>())
{
var test1 = row.InnerXml;
var test2 = row.OuterXml;
var reader = new StringReader(row.OuterXml);
var xmlSetting = new XmlReaderSettings
{
CloseInput = true,
ConformanceLevel = ConformanceLevel.Fragment,
IgnoreWhitespace = true
};
var xmlReader = XmlReader.Create(reader, xmlSetting);
var serializer = new XmlSerializer(typeof(ExcelRow), rootElement);
var rowContent = serializer.Deserialize(xmlReader);
dataList.Add((ExcelRow)rowContent);
}
mappedItems = dataList.Select(x => ExcelRowToTransactionItemMapper.Map(x, workbook)).ToList();
//_rentalTransactions.AddRange(mappedItems.Where(x => x.Description.Contains("RAVINDER KAUR")));
//_rentalTransactions.AddRange(mappedItems.Where(x => x.Description.Contains("PAYMENT TO R.A.C.I.")));
}
}
foreach (var criteria in criterias)
{
var items = mappedItems.Where(x => x.Description.Contains(criteria.Keyword)).ToList();
foreach (var i in items)
{
i.IsExpenditure = criteria.IsExpenditure;
i.IsRental = criteria.IsRental;
}
qualifiedItems.AddRange(items);
}
return(qualifiedItems);
}
private void OpenExcelWorkbook()
{
var fileName = @"C:\Temp\ANZ_3Month.xlsx";
var dataList = new List <ExcelRow>();
using (var spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
var workbook = spreadsheetDocument.WorkbookPart;
var worksheet = workbook.WorksheetParts.First();
var sheetData = worksheet.Worksheet.Elements <SheetData>().First();
if (sheetData != null)
{
var rootElement = new XmlRootAttribute();
rootElement.ElementName = "row";
rootElement.IsNullable = true;
rootElement.Namespace = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
var test = sheetData.OuterXml;
foreach (var row in sheetData.Elements <Row>())
{
var test1 = row.InnerXml;
var test2 = row.OuterXml;
var reader = new StringReader(row.OuterXml);
var xmlSetting = new XmlReaderSettings
{
CloseInput = true,
ConformanceLevel = ConformanceLevel.Fragment,
IgnoreWhitespace = true
};
var xmlReader = XmlReader.Create(reader, xmlSetting);
var serializer = new XmlSerializer(typeof(ExcelRow), rootElement);
var rowContent = serializer.Deserialize(xmlReader);
dataList.Add((ExcelRow)rowContent);
}
var mappedItems = dataList.Select(x => ExcelRowToTransactionItemMapper.Map(x, workbook)).ToList();
_rentalTransactions.AddRange(mappedItems.Where(x => x.Description.Contains("RAVINDER KAUR")));
_rentalTransactions.AddRange(mappedItems.Where(x => x.Description.Contains("PAYMENT TO R.A.C.I.")));
}
}
}