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;
}
public static string GetCellValue(Cell c, SharedStringTable stringTable, CellFormats formats, Field colmn)
{
string sval = null;
int styleIndex;
CellFormat cellFormat = null;
if (c == null) return null;
if (c.StyleIndex != null)
{
styleIndex = (int)c.StyleIndex.Value;
cellFormat = (CellFormat)formats.ElementAt(styleIndex);
}
if (c == null || c.CellValue == null)
return sval;
if (c.DataType != null && c.CellFormula == null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
sval = stringTable.ElementAt(int.Parse(c.InnerText)).InnerText;
break;
default:
sval = "not a string";
break;
}
}
else
sval = string.IsNullOrWhiteSpace(c.CellValue.InnerText) ? null : c.CellValue.InnerText;
if (colmn != null)
{
switch (colmn.DataFormat)
{
case DataFormatType.Date:
var pdt = c.CellValue.InnerText;
pdt = FixDate(pdt);
if (cellFormat != null && cellFormat.NumberFormatId != null && c.DataType == null)
{
var d = DateTime.FromOADate(double.Parse(pdt));
sval = d.ToString("MM/dd/yyyy");
}
else
{
DateTime tv;
if(sval != null && DateTime.TryParse(sval, out tv))
{
if (colmn.DataFormat == DataFormatType.Date)
sval = tv.ToShortDateString();
else
sval = tv.ToString();
}
else
sval = null;
}
break;
case DataFormatType.DateTime:
var dt = DateTime.FromOADate(double.Parse(c.CellValue.InnerText));
sval = dt.ToString();
break;
case DataFormatType.DateMixed:
double aoDate;
if (double.TryParse(c.CellValue.InnerText, out aoDate))
{
var aodt = DateTime.FromOADate(aoDate);
if ((DateTime.Now - aodt).Days < 36500)
sval = aodt.ToString("MM/dd/yyyy");
}
break;
}
}
if (sval != null && colmn != null && colmn.postProcRegex != null)
foreach(var d in colmn.postProcRegex)
sval = Regex.Replace(sval, d.Item1, d.Item2);
if (sval != null)
{
//if (sval.Length > 4000)
//{
// Log.New.Msg($"truncating to length 4000 cell: {c.CellReference.InnerText} contents: {sval}");
// sval = sval.Substring(0, 4000);
//}
sval = sval.Replace("\t", "");
sval.Trim();
}
return sval;
}
