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));
}