private bool MatchCombinedField(Field fld, FieldCellMap fm)
{
var titles = fld.titles.Select(t => t.ToLower()).Where(t =>
{
bool hasTitle = fm.Title.StartsWith(t);
return hasTitle;
});
if (titles.Count() > 0)
{
fm.field = fld;
var title = titles.FirstOrDefault();
if (fm.Value != null && fm.Value.Length >= titles.FirstOrDefault().Length)
fm.Value = fm.Value != null ? fm.Value.Substring(title.Length).Trim() : null;
if (fld.DataFormat == DataFormatType.Date || fld.DataFormat == DataFormatType.DateTime || fld.DataFormat == DataFormatType.DateMixed)
{
var val = fm.Value.Replace("(", "").Replace(")", "").Replace(":", "");
if (val.Contains("Through"))
val = val.Substring(0, val.IndexOf("Through", StringComparison.OrdinalIgnoreCase));
DateTime outVal;
if (DateTime.TryParse(val, out outVal))
{
if (fld.DataFormat == DataFormatType.Date || fld.DataFormat == DataFormatType.DateMixed)
fm.Value = outVal.ToString("MM/dd/yyyy");
else
fm.Value = outVal.ToString();
}
else
fm.Value = fld.DataFormat == DataFormatType.DateMixed ? fm.Value : null;
}
if (string.IsNullOrWhiteSpace(fm.Value))
fm.Value = null;
return true;
}
return false;
}
private bool MatchSeparatedField(IEnumerable<Cell> tcs, Field fld, FieldCellMap fm, SharedStringTablePart stringTable, CellFormats formats)
{
if (fm.Title != null && fld.titles.Select(t => t.ToLower()).Contains(fm.Title))
{
fm.field = fld;
if (fld.DataFormat == DataFormatType.Date || fld.DataFormat == DataFormatType.DateTime || fld.DataFormat == DataFormatType.DateMixed)
{
var cell = tcs.Where(c => c.CellReference == fm.cellLoc.ValueRef).FirstOrDefault();
fm.Value = Spreadsheet.GetCellValue(cell, stringTable.SharedStringTable, formats, fld);
}
return true;
}
return false;
}
private bool MatchField(IEnumerable<Cell> tcs, FieldCellMap fm, SheetLayout sheetLayout, SharedStringTablePart stringTable, CellFormats formats, List<FieldCellMap> agMaps)
{
bool foundFld = false;
foreach (var fld in sheetLayout.wsLayout.fields.Where(f => f.fldType == FieldType.cell))
{
if (foundFld)
break;
try
{
switch (fm.cellLoc.dataLayout)
{
case CellDataLayout.combined:
foundFld = MatchCombinedField(fld, fm);
break;
case CellDataLayout.separate:
foundFld = MatchSeparatedField(tcs, fld, fm, stringTable, formats);
break;
case CellDataLayout.aggregate:
// Cell contains an aggregate of fields. Based on
{
var aggCell = tcs.Where(c => c.CellReference == fm.cellLoc.TitleRef).FirstOrDefault();
var val = Spreadsheet.GetCellValue(aggCell, stringTable.SharedStringTable, formats, null);
if (val != null && fm.cellLoc.aggregateCellCnt > 0 && fm.cellLoc.aggregateCellSeparator != null && fm.cellLoc.aggregateCellSeparator.Count() > 0)
{
var cells = Regex.Split(val.Trim(), fm.cellLoc.aggregateCellSeparator).ToList();
if (cells.Count() == fm.cellLoc.aggregateCellCnt)
{
var nfms = new List<FieldCellMap>();
foreach(var agv in fm.cellLoc.cellMaps)
{
FieldCellMap nfm = null;
switch (agv.dataLayout)
{
case CellDataLayout.combined:
nfm = new FieldCellMap {
Title = cells[agv.aggregateIdx].ToLower(),
Value = cells[agv.aggregateIdx],
versMap = fm.versMap,
cellLoc = new CellLocation
{
dataLayout = CellDataLayout.combined,
TitleRef = fm.cellLoc.TitleRef,
ValueRef = fm.cellLoc.ValueRef
}
};
MatchField(tcs, nfm, sheetLayout, stringTable, formats, agMaps);
if (nfm.field != null)
nfms.Add(nfm);
break;
case CellDataLayout.lookup:
var nfld1 = sheetLayout.wsLayout.fields.Where(f => f.fldType == FieldType.cell);
var nfld2 = nfld1.Where(f => f.titles != null);
var nfld = nfld2.FirstOrDefault(f => f.titles.Select(t => t.ToLower()).Contains(agv.lookupString.ToLower()));
if (nfld != null)
{
nfm = new FieldCellMap
{
Title = agv.lookupString,
Value = cells[agv.aggregateIdx],
field = nfld,
versMap = fm.versMap,
cellLoc = new CellLocation
{
dataLayout = CellDataLayout.separate,
TitleRef = fm.cellLoc.TitleRef,
ValueRef = fm.cellLoc.ValueRef
}
};
nfms.Add(nfm);
}
break;
}
}
if(nfms.Count == fm.cellLoc.cellMaps.Count())
{
agMaps.AddRange(nfms);
foundFld = true;
break;
}
}
}
}
break;
}
}
catch (Exception ex)
{
Log.New.Msg(ex);
}
}
return foundFld;
}