Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }