private void CheckDateTimeNumFmts(List <XlsxNumFmt> list)
{
if (list.Count != 0)
{
foreach (XlsxNumFmt fmt in list)
{
if (!string.IsNullOrEmpty(fmt.FormatCode))
{
int num;
string str = fmt.FormatCode.ToLower();
while ((num = str.IndexOf('"')) > 0)
{
int index = str.IndexOf('"', num + 1);
if (index > 0)
{
str = str.Remove(num, (index - num) + 1);
}
}
FormatReader reader = new FormatReader {
FormatString = str
};
if (reader.IsDateFormatString())
{
this._defaultDateTimeStyles.Add(fmt.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())
{
_defaultDateTimeStyles.Add(numFmt.Id);
}
}
}
private bool IsDateFormat(ushort xFormat)
{
ushort format;
if (xFormat < ExtendedFormats.Count)
{
// If a cell XF record does not contain explicit attributes in a group (if the attribute group flag is not set),
// it repeats the attributes of its style XF record.
var rec = ExtendedFormats[xFormat];
switch (rec.Id)
{
case BIFFRECORDTYPE.XF_V2:
format = (ushort)(rec.ReadByte(2) & 0x3F);
break;
case BIFFRECORDTYPE.XF_V3:
format = rec.ReadByte(1);
break;
case BIFFRECORDTYPE.XF_V4:
format = rec.ReadByte(1);
break;
default:
format = rec.ReadUInt16(2);
break;
}
}
else
{
format = xFormat;
}
// From BIFF5 on, the built-in number formats will be omitted.
if (Workbook.BiffVersion >= 5)
{
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(false);
// 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(true);
case 0x31: // "@";
return(false); // NOTE: was value.ToString();
}
}
if (Formats.TryGetValue(format, out XlsBiffFormatString fmtString))
{
var fmt = fmtString.GetValue(Encoding);
var formatReader = new FormatReader {
FormatString = fmt
};
return(formatReader.IsDateFormatString());
}
return(false);
}
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(Helpers.ConvertFromOATime(value));
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(Helpers.ConvertFromOATime(value));
}
}
return(value);
}
}
private object tryConvertOADateTime(double value, ushort XFormat)
{
XlsBiffFormatString str;
ushort key = 0;
if ((XFormat < 0) || (XFormat >= this.m_globals.ExtendedFormats.Count))
{
key = XFormat;
}
else
{
XlsBiffRecord record = this.m_globals.ExtendedFormats[XFormat];
BIFFRECORDTYPE iD = record.ID;
if (iD == BIFFRECORDTYPE.XF_V2)
{
key = (ushort)(record.ReadByte(2) & 0x3f);
}
else if (iD == BIFFRECORDTYPE.XF_V3)
{
if ((record.ReadByte(3) & 4) == 0)
{
return(value);
}
key = record.ReadByte(1);
}
else if (iD == BIFFRECORDTYPE.XF_V4)
{
if ((record.ReadByte(5) & 4) == 0)
{
return(value);
}
key = record.ReadByte(1);
}
else
{
if ((record.ReadByte(this.m_globals.Sheets[this.m_globals.Sheets.Count - 1].IsV8 ? 9 : 7) & 4) == 0)
{
return(value);
}
key = record.ReadUInt16(2);
}
}
switch (key)
{
case 0:
case 1:
case 2:
case 3:
case 4:
case 5:
case 6:
case 7:
case 8:
case 9:
case 10:
case 11:
case 12:
case 13:
case 0x25:
case 0x26:
case 0x27:
case 40:
case 0x29:
case 0x2a:
case 0x2b:
case 0x2c:
case 0x30:
return(value);
case 14:
case 15:
case 0x10:
case 0x11:
case 0x12:
case 0x13:
case 20:
case 0x15:
case 0x16:
case 0x2d:
case 0x2e:
case 0x2f:
return(Helpers.ConvertFromOATime(value));
case 0x31:
return(value.ToString());
}
if (this.m_globals.Formats.TryGetValue(key, out str))
{
string str2 = str.Value;
FormatReader reader = new FormatReader {
FormatString = str2
};
if (reader.IsDateFormatString())
{
return(Helpers.ConvertFromOATime(value));
}
}
return(value);
}
