/// <summary>
/// Formats a number or date cell, be that a real number, or the
/// answer to a formula
/// </summary>
/// <param name="cell">The cell.</param>
/// <param name="value">The value.</param>
/// <returns></returns>
private String FormatNumberDateCell(CellValueRecordInterface cell, double value)
{
// Get the built in format, if there is one
int formatIndex = ft.GetFormatIndex(cell);
String formatString = ft.GetFormatString(cell);
if (formatString == null)
{
return value.ToString(CultureInfo.InvariantCulture);
}
else
{
// Is it a date?
if (NPOI.SS.UserModel.DateUtil.IsADateFormat(formatIndex, formatString) &&
NPOI.SS.UserModel.DateUtil.IsValidExcelDate(value))
{
// Java wants M not m for month
formatString = formatString.Replace('m', 'M');
// Change \- into -, if it's there
formatString = formatString.Replace("\\\\-", "-");
// Format as a date
DateTime d = NPOI.SS.UserModel.DateUtil.GetJavaDate(value, false);
SimpleDateFormat df = new SimpleDateFormat(formatString);
return df.Format(d);
}
else
{
if (formatString == "General")
{
// Some sort of wierd default
return value.ToString(CultureInfo.InvariantCulture);
}
// Format as a number
DecimalFormat df = new DecimalFormat(formatString);
return df.Format(value);
}
}
}
/**
* Convert a Nullable<DateTime> into a String.
*
* @param d
* The Date to convert.
* @return The formated date or null.
* @see java.util.SimpleDateFormat
*/
private String GetDateValue(Nullable<DateTime> d) {
if (d == null || d.Equals(""))
return "";
else {
SimpleDateFormat df = new SimpleDateFormat(
"yyyy-MM-dd'T'HH:mm:ss'Z'");
return df.Format(d.Value, CultureInfo.CurrentCulture);
}
}
/// <summary>
/// Returns a string representation of the cell
/// </summary>
/// <returns>Formula cells return the formula string, rather than the formula result.
/// Dates are displayed in dd-MMM-yyyy format
/// Errors are displayed as #ERR<errIdx>
/// </returns>
public override String ToString()
{
switch (CellType)
{
case CellType.Blank:
return "";
case CellType.Boolean:
return BooleanCellValue ? "TRUE" : "FALSE";
case CellType.Error:
return ErrorEval.GetText(ErrorCellValue);
case CellType.Formula:
return CellFormula;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(this))
{
FormatBase sdf = new SimpleDateFormat("dd-MMM-yyyy");
return sdf.Format(DateCellValue, CultureInfo.CurrentCulture);
}
return NumericCellValue + "";
case CellType.String:
return RichStringCellValue.ToString();
default:
return "Unknown Cell Type: " + CellType;
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
{
double s0;
String s1;
try
{
s0 = TextFunction.EvaluateDoubleArg(arg0, srcRowIndex, srcColumnIndex);
s1 = TextFunction.EvaluateStringArg(arg1, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return e.GetErrorEval();
}
if (Regex.Match(s1, @"[\d,\#,\.,\$,\,]+").Success)
{
FormatBase formatter = new DecimalFormat(s1);
return new StringEval(formatter.Format(s0));
}
else if (s1.IndexOf("/") == s1.LastIndexOf("/") && s1.IndexOf("/") >= 0 && !s1.Contains("-"))
{
double wholePart = Math.Floor(s0);
double decPart = s0 - wholePart;
if (wholePart * decPart == 0)
{
return new StringEval("0");
}
String[] parts = s1.Split(' ');
String[] fractParts;
if (parts.Length == 2)
{
fractParts = parts[1].Split('/');
}
else
{
fractParts = s1.Split('/');
}
if (fractParts.Length == 2)
{
double minVal = 1.0;
double currDenom = Math.Pow(10, fractParts[1].Length) - 1d;
double currNeum = 0;
for (int i = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i > 0; i--)
{
for (int i2 = (int)(Math.Pow(10, fractParts[1].Length) - 1d); i2 > 0; i2--)
{
if (minVal >= Math.Abs((double)i2 / (double)i - decPart))
{
currDenom = i;
currNeum = i2;
minVal = Math.Abs((double)i2 / (double)i - decPart);
}
}
}
FormatBase neumFormatter = new DecimalFormat(fractParts[0]);
FormatBase denomFormatter = new DecimalFormat(fractParts[1]);
if (parts.Length == 2)
{
FormatBase wholeFormatter = new DecimalFormat(parts[0]);
String result = wholeFormatter.Format(wholePart) + " " + neumFormatter.Format(currNeum) + "/" + denomFormatter.Format(currDenom);
return new StringEval(result);
}
else
{
String result = neumFormatter.Format(currNeum + (currDenom * wholePart)) + "/" + denomFormatter.Format(currDenom);
return new StringEval(result);
}
}
else
{
return ErrorEval.VALUE_INVALID;
}
}
else
{
try
{
FormatBase dateFormatter = new SimpleDateFormat(s1);
DateTime dt = new DateTime(1899, 11, 30, 0, 0, 0);
dt.AddDays((int)Math.Floor(s0));
double dayFraction = s0 - Math.Floor(s0);
dt.AddMilliseconds((int)Math.Round(dayFraction * 24 * 60 * 60 * 1000));
return new StringEval(dateFormatter.Format(dt));
}
catch (Exception)
{
return ErrorEval.VALUE_INVALID;
}
}
}
private ValueEval TryParseDateTime(double s0, string s1)
{
try
{
FormatBase dateFormatter = new SimpleDateFormat(s1);
//first month of java Gregorian Calendar month field is 0
DateTime dt = new DateTime(1899, 12, 30, 0, 0, 0);
dt = dt.AddDays((int)Math.Floor(s0));
double dayFraction = s0 - Math.Floor(s0);
dt = dt.AddMilliseconds((int)Math.Round(dayFraction * 24 * 60 * 60 * 1000));
return new StringEval(dateFormatter.Format(dt));
}
catch (Exception)
{
return ErrorEval.VALUE_INVALID;
}
}
/**
* Returns a string representation of the cell
* <p>
* Formula cells return the formula string, rather than the formula result.
* Dates are displayed in dd-MMM-yyyy format
* Errors are displayed as #ERR<errIdx>
* </p>
*/
public override String ToString()
{
switch (CellType)
{
case CellType.BLANK:
return "";
case CellType.BOOLEAN:
return BooleanCellValue ? "TRUE" : "FALSE";
case CellType.ERROR:
return ErrorEval.GetText(ErrorCellValue);
case CellType.FORMULA:
return CellFormula;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(this))
{
FormatBase sdf = new SimpleDateFormat("dd-MMM-yyyy");
return sdf.Format(DateCellValue);
}
return NumericCellValue + "";
case CellType.STRING:
return RichStringCellValue.ToString();
default:
return "Unknown Cell Type: " + CellType;
}
}
