private void CheckDateTimeNumFmts(List <XlsxNumFmt> list)
{
if (list.Count == 0)
{
return;
}
foreach (XlsxNumFmt numFmt in list)
{
if (string.IsNullOrEmpty(numFmt.FormatCode))
{
continue;
}
string fc = numFmt.FormatCode.ToLower();
int pos;
while ((pos = fc.IndexOf('"')) > 0)
{
int endPos = fc.IndexOf('"', pos + 1);
if (endPos > 0)
{
fc = fc.Remove(pos, endPos - pos + 1);
}
}
//it should only detect it as a date if it contains
//dd mm mmm yy yyyy
//h hh ss
//AM PM
//and only if these appear as "words" so either contained in [ ]
//or delimted in someway
//updated to not detect as date if format contains a #
var formatReader = new FormatReader()
{
FormatString = fc
};
if (formatReader.IsDateFormatString())
{
m_defaultDateTimeStyles.Add(numFmt.Id);
}
}
}
private void CheckDateTimeNumFmts(List<XlsxNumFmt> list) {
if (list.Count == 0)
return;
foreach (XlsxNumFmt numFmt in list) {
if (string.IsNullOrEmpty(numFmt.FormatCode))
continue;
string fc = numFmt.FormatCode.ToLower();
int pos;
while ((pos = fc.IndexOf('"')) > 0) {
int endPos = fc.IndexOf('"', pos + 1);
if (endPos > 0)
fc = fc.Remove(pos, endPos - pos + 1);
}
//it should only detect it as a date if it contains
//dd mm mmm yy yyyy
//h hh ss
//AM PM
//and only if these appear as "words" so either contained in [ ]
//or delimted in someway
//updated to not detect as date if format contains a #
var formatReader = new FormatReader {
FormatString = fc
};
if (formatReader.IsDateFormatString()) {
m_defaultDateTimeStyles.Add(numFmt.Id);
}
}
}
private object tryConvertOADateTime(double value, ushort XFormat)
{
ushort format = 0;
if (XFormat >= 0 && XFormat < m_globals.ExtendedFormats.Count)
{
var rec = m_globals.ExtendedFormats[XFormat];
switch (rec.ID)
{
case BIFFRECORDTYPE.XF_V2:
format = (ushort) (rec.ReadByte(2) & 0x3F);
break;
case BIFFRECORDTYPE.XF_V3:
if ((rec.ReadByte(3) & 4) == 0)
return value;
format = rec.ReadByte(1);
break;
case BIFFRECORDTYPE.XF_V4:
if ((rec.ReadByte(5) & 4) == 0)
return value;
format = rec.ReadByte(1);
break;
default:
if ((rec.ReadByte(m_globals.Sheets[m_globals.Sheets.Count-1].IsV8 ? 9 : 7) & 4) == 0)
return value;
format = rec.ReadUInt16(2);
break;
}
}
else
{
format = XFormat;
}
switch (format)
{
// numeric built in formats
case 0: //"General";
case 1: //"0";
case 2: //"0.00";
case 3: //"#,##0";
case 4: //"#,##0.00";
case 5: //"\"$\"#,##0_);(\"$\"#,##0)";
case 6: //"\"$\"#,##0_);[Red](\"$\"#,##0)";
case 7: //"\"$\"#,##0.00_);(\"$\"#,##0.00)";
case 8: //"\"$\"#,##0.00_);[Red](\"$\"#,##0.00)";
case 9: //"0%";
case 10: //"0.00%";
case 11: //"0.00E+00";
case 12: //"# ?/?";
case 13: //"# ??/??";
case 0x30:// "##0.0E+0";
case 0x25:// "_(#,##0_);(#,##0)";
case 0x26:// "_(#,##0_);[Red](#,##0)";
case 0x27:// "_(#,##0.00_);(#,##0.00)";
case 40:// "_(#,##0.00_);[Red](#,##0.00)";
case 0x29:// "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)";
case 0x2a:// "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)";
case 0x2b:// "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)";
case 0x2c:// "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)";
return value;
// date formats
case 14: //this.GetDefaultDateFormat();
case 15: //"D-MM-YY";
case 0x10: // "D-MMM";
case 0x11: // "MMM-YY";
case 0x12: // "h:mm AM/PM";
case 0x13: // "h:mm:ss AM/PM";
case 20: // "h:mm";
case 0x15: // "h:mm:ss";
case 0x16: // string.Format("{0} {1}", this.GetDefaultDateFormat(), this.GetDefaultTimeFormat());
case 0x2d: // "mm:ss";
case 0x2e: // "[h]:mm:ss";
case 0x2f: // "mm:ss.0";
return value.ConvertFromOATime();
case 0x31:// "@";
return value.ToString();
default:
XlsBiffFormatString fmtString;
if (m_globals.Formats.TryGetValue(format, out fmtString) )
{
var fmt = fmtString.Value;
var formatReader = new FormatReader() {FormatString = fmt};
if (formatReader.IsDateFormatString())
return value.ConvertFromOATime();
}
return value;
}
}
private object tryConvertOADateTime(double value, ushort XFormat)
{
ushort format = 0;
if (XFormat >= 0 && XFormat < m_globals.ExtendedFormats.Count)
{
var rec = m_globals.ExtendedFormats[XFormat];
switch (rec.ID)
{
case BIFFRECORDTYPE.XF_V2:
format = (ushort)(rec.ReadByte(2) & 0x3F);
break;
case BIFFRECORDTYPE.XF_V3:
if ((rec.ReadByte(3) & 4) == 0)
{
return(value);
}
format = rec.ReadByte(1);
break;
case BIFFRECORDTYPE.XF_V4:
if ((rec.ReadByte(5) & 4) == 0)
{
return(value);
}
format = rec.ReadByte(1);
break;
default:
if ((rec.ReadByte(m_globals.Sheets[m_globals.Sheets.Count - 1].IsV8 ? 9 : 7) & 4) == 0)
{
return(value);
}
format = rec.ReadUInt16(2);
break;
}
}
else
{
format = XFormat;
}
switch (format)
{
// numeric built in formats
case 0: //"General";
case 1: //"0";
case 2: //"0.00";
case 3: //"#,##0";
case 4: //"#,##0.00";
case 5: //"\"$\"#,##0_);(\"$\"#,##0)";
case 6: //"\"$\"#,##0_);[Red](\"$\"#,##0)";
case 7: //"\"$\"#,##0.00_);(\"$\"#,##0.00)";
case 8: //"\"$\"#,##0.00_);[Red](\"$\"#,##0.00)";
case 9: //"0%";
case 10: //"0.00%";
case 11: //"0.00E+00";
case 12: //"# ?/?";
case 13: //"# ??/??";
case 0x30: // "##0.0E+0";
case 0x25: // "_(#,##0_);(#,##0)";
case 0x26: // "_(#,##0_);[Red](#,##0)";
case 0x27: // "_(#,##0.00_);(#,##0.00)";
case 40: // "_(#,##0.00_);[Red](#,##0.00)";
case 0x29: // "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)";
case 0x2a: // "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(\"$\"* \"-\"_);_(@_)";
case 0x2b: // "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(\"$\"* \"-\"??_);_(@_)";
case 0x2c: // "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)";
return(value);
// date formats
case 14: //this.GetDefaultDateFormat();
case 15: //"D-MM-YY";
case 0x10: // "D-MMM";
case 0x11: // "MMM-YY";
case 0x12: // "h:mm AM/PM";
case 0x13: // "h:mm:ss AM/PM";
case 20: // "h:mm";
case 0x15: // "h:mm:ss";
case 0x16: // string.Format("{0} {1}", this.GetDefaultDateFormat(), this.GetDefaultTimeFormat());
case 0x2d: // "mm:ss";
case 0x2e: // "[h]:mm:ss";
case 0x2f: // "mm:ss.0";
return(value.ConvertFromOATime());
case 0x31: // "@";
return(value.ToString());
default:
XlsBiffFormatString fmtString;
if (m_globals.Formats.TryGetValue(format, out fmtString))
{
var fmt = fmtString.Value;
var formatReader = new FormatReader()
{
FormatString = fmt
};
if (formatReader.IsDateFormatString())
{
return(value.ConvertFromOATime());
}
}
return(value);
}
}
