IEnumerable <CodeImport> ParseCSV(string filename)
        {
            List <CodeImport> codes = new List <CodeImport>();

            string path = Path.Combine(ResourcesFolder, filename);

            using (var parser = new Microsoft.VisualBasic.FileIO.TextFieldParser(path))
            {
                parser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
                parser.SetDelimiters(",");
                parser.TrimWhiteSpace = true;

                //get the headers
                string[] headers    = parser.ReadFields();
                int      lineNumber = 1;
                while (parser.EndOfData == false)
                {
                    CodeImport code = new CodeImport();

                    string[] line = parser.ReadFields();

                    for (int i = 0; i < line.Length; i++)
                    {
                        switch (headers[i].ToUpperInvariant())
                        {
                        case "CRITERIAINDEX":
                            try
                            {
                                code.CriteriaIndex = Convert.ToInt32(line[i]);
                            }catch
                            {
                                throw new FormatException($"Unable to convert Criteria Index to integer on line { lineNumber }. Value: \"{ line[i] }\"");
                            }
                            break;

                        case "CODETYPE":
                            code.CodeType = line[i];
                            break;

                        case "CODE":
                            code.Code = line[i];
                            break;

                        case "EXACTMATCH":
                            code.SearchMethodType = string.Equals("1", line[i], StringComparison.OrdinalIgnoreCase) ? DTO.Enums.TextSearchMethodType.ExactMatch : DTO.Enums.TextSearchMethodType.StartsWith;
                            break;
                        }
                    }

                    if (code.IsEmpty() == false)
                    {
                        codes.Add(code);
                    }

                    lineNumber++;
                }
            }

            return(codes.OrderBy(c => c.CriteriaIndex).ThenBy(c => c.CodeType).ThenBy(c => c.SearchMethodType));
        }
        IEnumerable <CodeImport> ParseExcel(string filename)
        {
            List <CodeImport> codes = new List <CodeImport>();

            string path = Path.Combine(ResourcesFolder, filename);

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
            {
                //Read the first Sheet from Excel file.
                Sheet sheet = spreadsheetDocument.WorkbookPart.Workbook.Sheets.GetFirstChild <Sheet>();

                //Get the Worksheet instance.
                Worksheet worksheet = (spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;

                //Fetch all the rows present in the Worksheet.
                IEnumerable <Row>           rows    = worksheet.GetFirstChild <SheetData>().Descendants <Row>();
                Dictionary <string, string> headers = null;
                foreach (var row in rows)
                {
                    if (row.RowIndex.Value == 1)
                    {
                        headers = new Dictionary <string, string>();
                        foreach (Cell cell in row.Descendants <Cell>())
                        {
                            if (cell.CellReference.HasValue == false)
                            {
                                continue;
                            }

                            string columnReference = System.Text.RegularExpressions.Regex.Replace(cell.CellReference.Value.ToUpper(), @"[\d]", string.Empty);
                            if (string.IsNullOrEmpty(columnReference))
                            {
                                continue;
                            }

                            if (cell.CellValue.IsEmpty() || cell.CellValue.IsNull())
                            {
                                headers.Add(columnReference, "EMPTY");
                            }
                            else
                            {
                                string value = GetValue(spreadsheetDocument, cell);
                                headers.Add(columnReference, (value ?? string.Empty).ToUpperInvariant());
                            }
                        }
                    }
                    else
                    {
                        if (!row.Descendants <Cell>().All(x => x.CellValue.IsNull() || x.CellValue.IsEmpty()))
                        {
                            CodeImport addedCode = new CodeImport();

                            foreach (var cell in row.Descendants <Cell>())
                            {
                                if (cell.CellReference.HasValue == false)
                                {
                                    continue;
                                }

                                string columnReference = System.Text.RegularExpressions.Regex.Replace(cell.CellReference.Value.ToUpper(), @"[\d]", string.Empty);
                                if (string.IsNullOrEmpty(columnReference))
                                {
                                    continue;
                                }

                                if (headers.TryGetValue(columnReference, out string column))
                                {
                                    if (column.IsNullOrEmpty())
                                    {
                                        continue;
                                    }

                                    string value = GetValue(spreadsheetDocument, cell);
                                    switch (column.ToUpperInvariant())
                                    {
                                    case "CRITERIAINDEX":
                                        try
                                        {
                                            addedCode.CriteriaIndex = Convert.ToInt32(value);
                                        }
                                        catch
                                        {
                                            throw new FormatException($"Unable to convert Criteria Index to integer at cell reference: { cell.CellReference.Value }. Value: \"{ value }\"");
                                        }
                                        break;

                                    case "CODETYPE":
                                        addedCode.CodeType = value;
                                        break;

                                    case "CODE":
                                        addedCode.Code = value;
                                        break;

                                    case "EXACTMATCH":
                                        addedCode.SearchMethodType = string.Equals("1", value, StringComparison.OrdinalIgnoreCase) ? DTO.Enums.TextSearchMethodType.ExactMatch : DTO.Enums.TextSearchMethodType.StartsWith;
                                        break;
                                    }
                                }
                            }

                            if (addedCode.IsEmpty() == false)
                            {
                                codes.Add(addedCode);
                            }
                        }
                    }
                }
            }

            return(codes.OrderBy(c => c.CriteriaIndex).ThenBy(c => c.CodeType).ThenBy(c => c.SearchMethodType));
        }