IEnumerable <CodeImport> ParseCSV(string file)
        {
            IList <CodeImport> listDTO = new List <CodeImport>();

            using (var parser = new Microsoft.VisualBasic.FileIO.TextFieldParser(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file)))
            {
                parser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited;
                parser.SetDelimiters(",");
                parser.TrimWhiteSpace = true;

                //get the headers
                string[] headers = parser.ReadFields();

                //read the code import values
                while (parser.EndOfData == false)
                {
                    CodeImport code = new CodeImport();

                    string[] line       = parser.ReadFields();
                    int      lineNumber = 1;
                    for (int i = 0; i < line.Length; i++)
                    {
                        if (string.IsNullOrWhiteSpace(headers[i]))
                        {
                            continue;
                        }

                        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) ? TextSearchMethodType.ExactMatch : TextSearchMethodType.StartsWith;
                            break;
                        }
                    }

                    if (code.IsEmpty() == false)
                    {
                        listDTO.Add(code);
                    }
                    lineNumber++;
                }
            }

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

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file), 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))
                                {
                                    try
                                    {
                                        if (column.IsNullOrEmpty() || string.Equals("EMPTY", column, StringComparison.OrdinalIgnoreCase))
                                        {
                                            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) ? TextSearchMethodType.ExactMatch : TextSearchMethodType.StartsWith;
                                            break;
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        throw;
                                    }
                                }
                            }

                            if (addedCode.IsEmpty() == false)
                            {
                                codes.Add(addedCode);
                            }
                        }
                    }
                }
            }
            return(codes.OrderBy(c => c.CriteriaIndex).ThenBy(c => c.CodeType).ThenBy(c => c.SearchMethodType));
        }