/**
* Gets the java equivalent number format for the formatString
*
* @return The java equivalent of the number format for this object
*/
public CSharpJExcel.Interop.NumberFormat getNumberFormat()
{
if (format != null && format is CSharpJExcel.Interop.NumberFormat)
{
return((CSharpJExcel.Interop.NumberFormat)format);
}
try
{
string fs = formatString;
// Replace the Excel formatting characters with java equivalents
fs = replace(fs, "E+", "E");
fs = replace(fs, "_)", string.Empty);
fs = replace(fs, "_", string.Empty);
fs = replace(fs, "[Red]", string.Empty);
fs = replace(fs, "\\", string.Empty);
format = new DecimalFormat(fs);
}
catch (Exception e)
{
// Something went wrong with the date format - fail silently
// and return a default value
format = new DecimalFormat("#.###");
}
return((CSharpJExcel.Interop.NumberFormat)format);
}
/**
* Gets the java equivalent date format for the formatString
*
* @return The java equivalent of the date format for this object
*/
public CSharpJExcel.Interop.DateFormat getDateFormat()
{
if (format != null && format is CSharpJExcel.Interop.DateFormat)
{
return((CSharpJExcel.Interop.DateFormat)format);
}
string fmt = formatString;
// Replace the AM/PM indicator with an a
StringBuilder sb = null;
int pos = fmt.IndexOf("AM/PM");
while (pos != -1)
{
sb = new StringBuilder(fmt.Substring(0, pos));
sb.Append('a');
sb.Append(fmt.Substring(pos + 5));
fmt = sb.ToString();
pos = fmt.IndexOf("AM/PM");
}
// Replace ss.0 with ss.SSS (necessary to always specify milliseconds
// because of NT)
pos = fmt.IndexOf("ss.0");
while (pos != -1)
{
sb = new StringBuilder(fmt.Substring(0, pos));
sb.Append("ss.SSS");
// Keep going until we run out of zeros
pos += 4;
while (pos < fmt.Length && fmt[pos] == '0')
{
pos++;
}
sb.Append(fmt.Substring(pos));
fmt = sb.ToString();
pos = fmt.IndexOf("ss.0");
}
// Filter out the backslashes
sb = new StringBuilder();
for (int i = 0; i < fmt.Length; i++)
{
if (fmt[i] != '\\')
{
sb.Append(fmt[i]);
}
}
fmt = sb.ToString();
// If the date format starts with anything inside square brackets then
// filter tham out
if (fmt[0] == '[')
{
int end = fmt.IndexOf(']');
if (end != -1)
{
fmt = fmt.Substring(end + 1);
}
}
// Get rid of some spurious characters that can creep in
fmt = replace(fmt, ";@", string.Empty);
// We need to convert the month indicator m, to upper case when we
// are dealing with dates
char[] formatBytes = fmt.ToCharArray();
for (int i = 0; i < formatBytes.Length; i++)
{
if (formatBytes[i] == 'm')
{
// Firstly, see if the preceding character is also an m. If so,
// copy that
if (i > 0 && (formatBytes[i - 1] == 'm' || formatBytes[i - 1] == 'M'))
{
formatBytes[i] = formatBytes[i - 1];
}
else
{
// There is no easy way out. We have to deduce whether this an
// minute or a month? See which is closest out of the
// letters H d s or y
// First, h
int minuteDist = System.Int32.MaxValue;
for (int j = i - 1; j > 0; j--)
{
if (formatBytes[j] == 'h')
{
minuteDist = i - j;
break;
}
}
for (int j = i + 1; j < formatBytes.Length; j++)
{
if (formatBytes[j] == 'h')
{
minuteDist = System.Math.Min(minuteDist, j - i);
break;
}
}
for (int j = i - 1; j > 0; j--)
{
if (formatBytes[j] == 'H')
{
minuteDist = i - j;
break;
}
}
for (int j = i + 1; j < formatBytes.Length; j++)
{
if (formatBytes[j] == 'H')
{
minuteDist = System.Math.Min(minuteDist, j - i);
break;
}
}
// Now repeat for s
for (int j = i - 1; j > 0; j--)
{
if (formatBytes[j] == 's')
{
minuteDist = System.Math.Min(minuteDist, i - j);
break;
}
}
for (int j = i + 1; j < formatBytes.Length; j++)
{
if (formatBytes[j] == 's')
{
minuteDist = System.Math.Min(minuteDist, j - i);
break;
}
}
// We now have the distance of the closest character which could
// indicate the the m refers to a minute
// Repeat for d and y
int monthDist = System.Int32.MaxValue;
for (int j = i - 1; j > 0; j--)
{
if (formatBytes[j] == 'd')
{
monthDist = i - j;
break;
}
}
for (int j = i + 1; j < formatBytes.Length; j++)
{
if (formatBytes[j] == 'd')
{
monthDist = System.Math.Min(monthDist, j - i);
break;
}
}
// Now repeat for y
for (int j = i - 1; j > 0; j--)
{
if (formatBytes[j] == 'y')
{
monthDist = System.Math.Min(monthDist, i - j);
break;
}
}
for (int j = i + 1; j < formatBytes.Length; j++)
{
if (formatBytes[j] == 'y')
{
monthDist = System.Math.Min(monthDist, j - i);
break;
}
}
if (monthDist < minuteDist)
{
// The month indicator is closer, so convert to a capital M
formatBytes[i] = Char.ToUpper(formatBytes[i]);
}
else if ((monthDist == minuteDist) &&
(monthDist != System.Int32.MaxValue))
{
// They are equidistant. As a tie-breaker, take the formatting
// character which precedes the m
char ind = formatBytes[i - monthDist];
if (ind == 'y' || ind == 'd')
{
// The preceding item indicates a month measure, so convert
formatBytes[i] = Char.ToUpper(formatBytes[i]);
}
}
}
}
}
try
{
this.format = new SimpleDateFormat(new string(formatBytes));
}
catch (Exception e)
{
// There was a spurious character - fail silently
this.format = new SimpleDateFormat("dd MM yyyy hh:mm:ss");
}
return((CSharpJExcel.Interop.DateFormat) this.format);
}
