public List <RemarketingData> ReadRmkDataFromSourceFile(ConverterConfiguration config)
{
var fileData = new List <RemarketingData>();
// The incoming file is a BIFF 5.0 version of the excel file (Excel 2010)
// so we have to use ExcelReader to read the contents.
// running ExcelDataReading on .NET Core requires we specify the encoding provider
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
// the easiest way to access the data is to read the entire workbook into a data set
// and then parse the dataset data
DataSet ds;
using (var stream = File.Open(config.SourceFile, FileMode.Open, FileAccess.Read))
{
using var reader = ExcelReaderFactory.CreateReader(stream);
ds = reader.AsDataSet();
}
// each worksheet in the excel file is a data table in the data set
var sheet = ds.Tables[0];
int rowIndex = -1;
foreach (DataRow row in sheet.Rows)
{
// track the row number so we can skip the first N header rows
rowIndex++;
if (rowIndex < config.NumberOfHeaderRows)
{
continue;
}
var items = row.ItemArray;
// convert the data table data row to a strongly typed row of data.
// ReSharper disable once UseObjectOrCollectionInitializer
var data = new RemarketingData();
data.AccountNumber = items[0].ToString();
// if we encounter a missing account number, we'll use that to signify the end of the data rows.
if (string.IsNullOrEmpty(data.AccountNumber))
{
break;
}
data.LoanNumber = items[1].ToString();
data.LastName = items[2].ToString();
data.FirstName = items[3].ToString();
data.Balance = Convert.ToDecimal(items[4]);
data.Year = items[5].ToString();
data.Make = items[6].ToString();
data.Model = items[7].ToString();
data.Vin = items[8].ToString();
data.Mileage = Convert.ToInt32(items[9]);
data.RepoAgentName = items[10].ToString();
data.RepoAgentsLookup = items[11].ToString();
data.LocationOfUnit = items[12].ToString();
data.DateOfRepo = Convert.ToDateTime(items[13]);
data.DateOfClear = Convert.ToDateTime(items[14]);
fileData.Add(data);
}
_logger.Information($"Read {fileData.Count} row(s) from: {config.SourceFile}");
return(fileData);
}
public List <RemarketingData> ReadRmkDataFromSourceFile(ConverterConfiguration config)
{
var fileData = new List <RemarketingData>();
// The incoming file is a BIFF 5.0 version of the excel file (Excel 2010)
// so we have to use ExcelReader to read the contents.
// running ExcelDataReading on .NET Core requires we specify the encoding provider
// the easiest way to access the data is to read the entire workbook into a data set
// and then parse the dataset data
DataSet ds;
using (var stream = File.Open(config.SourceFile, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
ds = reader.AsDataSet();
}
}
var colMap = config.ClientConfiguration.SourceColumnMap;
// each worksheet in the excel file is a data table in the data set
var sheet = ds.Tables[0];
int rowIndex = -1;
foreach (DataRow row in sheet.Rows)
{
// track the row number so we can skip the first N header rows
rowIndex++;
if (rowIndex < config.ClientConfiguration.NumberOfHeaderRows)
{
continue;
}
var items = row.ItemArray;
// convert the data table data row to a strongly typed row of data.
// ReSharper disable once UseObjectOrCollectionInitializer
var data = new RemarketingData();
data.AccountNumber = Get <string>(items, config.ClientConfiguration.SourceColumnMap.AccountNumber, rowIndex, nameof(data.AccountNumber));
// if we encounter a missing account number, we'll use that to signify the end of the data rows.
if (string.IsNullOrEmpty(data.AccountNumber))
{
_logger.Debug($"encountered empty Account Number value at row {rowIndex} col {colMap.AccountNumber}. Assuming end of file.");
break;
}
data.LoanNumber = Get <string>(items, colMap.LoanNumber, rowIndex, nameof(data.LoanNumber));
data.LastName = Get <string>(items, colMap.LastName, rowIndex, nameof(data.LastName));
data.FirstName = Get <string>(items, colMap.FirstName, rowIndex, nameof(data.FirstName));
data.Balance = Get <decimal>(items, colMap.LoanBalance, rowIndex, nameof(data.Balance));
data.Year = Get <string>(items, colMap.Year, rowIndex, nameof(data.Year));
data.Make = Get <string>(items, colMap.Make, rowIndex, nameof(data.Make));
data.Model = Get <string>(items, colMap.Model, rowIndex, nameof(data.Model));
data.Vin = Get <string>(items, colMap.Vin, rowIndex, nameof(data.Vin));
data.Mileage = Get <int>(items, colMap.Mileage, rowIndex, nameof(data.Mileage));
data.RepoAgentName = Get <string>(items, colMap.RepoAgentName, rowIndex, nameof(data.RepoAgentName));
data.RepoAgentsLookup = Get <string>(items, colMap.RepoAgentsLookup, rowIndex, nameof(data.RepoAgentsLookup));
data.LocationOfUnit = Get <string>(items, colMap.LocationOfUnit, rowIndex, nameof(data.LocationOfUnit));
data.DateOfRepo = Get <DateTime>(items, colMap.DateOfRepo, rowIndex, nameof(data.DateOfRepo));
data.DateOfClear = Get <DateTime>(items, colMap.DateOfClear, rowIndex, nameof(data.DateOfClear));
fileData.Add(data);
}
_logger.Information($"Read {fileData.Count} row(s) from: {config.SourceFile}");
return(fileData);
}