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