public String Format(double num)
{
double doubleValue = num;
// Format may be p or p;n or p;n;z (okay we never get a z).
// Fall back to p when n or z is not specified.
String[] formatBits = formatstr.Split(";".ToCharArray());
int f = doubleValue > 0.0 ? 0 : doubleValue < 0.0 ? 1 : 2;
String str = (f < formatBits.Length) ? formatBits[f] : formatBits[0];
double wholePart = Math.Floor(Math.Abs(doubleValue));
double decPart = Math.Abs(doubleValue) - wholePart;
if (wholePart + decPart == 0)
{
return "0";
}
if (doubleValue < 0.0)
{
wholePart *= -1.0;
}
//str = regex.Replace(str, ReplaceWS);
// Split the format string into decimal and fraction parts
String[] parts = str.Replace(" *", " ").Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
String[] fractParts;
if (parts.Length == 2)
{
fractParts = parts[1].Split("/".ToCharArray());
}
else
{
fractParts = str.Split("/".ToCharArray());
}
// Excel supports both #/# and ?/?, but Java only the former
for (int i = 0; i < fractParts.Length; i++)
{
fractParts[i] = fractParts[i].Replace('?', '#');
}
if (fractParts.Length == 2)
{
int fractPart1Length = Math.Min(CountHashes(fractParts[1]), 4); // Any more than 3 and we go around the loops for ever
double minVal = 1.0;
double currDenom = Math.Pow(10, fractPart1Length) - 1d;
double currNeum = 0;
for (int i = (int)(Math.Pow(10, fractPart1Length) - 1d); i > 0; i--)
{
for (int i2 = (int)(Math.Pow(10, fractPart1Length) - 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);
}
}
}
DecimalFormat neumFormatter = new DecimalFormat(fractParts[0]);
DecimalFormat denomFormatter = new DecimalFormat(fractParts[1]);
CultureInfo cul = CultureInfo.CurrentCulture;
if (parts.Length == 2)
{
DecimalFormat wholeFormatter = new DecimalFormat(parts[0]);
String result = wholeFormatter.Format(wholePart, cul) + " " + neumFormatter.Format(currNeum, cul) + "/" + denomFormatter.Format(currDenom, cul);
return result;
}
else
{
String result = neumFormatter.Format(currNeum + (currDenom * wholePart), cul) + "/" + denomFormatter.Format(currDenom, cul);
return result;
}
}
else
{
throw new ArgumentException("Fraction must have 2 parts, found " + fractParts.Length + " for fraction format " + this.formatstr);
}
}
public void TestSetDefaultNumberFormat()
{
IRow row = wb.GetSheetAt(0).GetRow(3);
List<ICell> cells = row.Cells;
FormatBase defaultFormat = new DecimalFormat("Balance $#,#00.00 USD;Balance -$#,#00.00 USD");
formatter.SetDefaultNumberFormat(defaultFormat);
Random rand = new Random((int)DateTime.Now.ToFileTime());
log("\n==== DEFAULT NUMBER FORMAT ====");
foreach(ICell cell in cells)
{
cell.SetCellValue(cell.NumericCellValue * rand.Next() / 1000000 - 1000);
log(formatter.FormatCellValue(cell));
Assert.IsTrue(formatter.FormatCellValue(cell).StartsWith("Balance "));
Assert.IsTrue(formatter.FormatCellValue(cell).EndsWith(" USD"));
}
}
/// <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);
}
}
}
public String Format(double num)
{
double doubleValue = num;
// Format may be p or p;n or p;n;z (okay we never get a z).
// Fall back to p when n or z is not specified.
String[] formatBits = formatstr.Split(";".ToCharArray());
int f = doubleValue > 0.0 ? 0 : doubleValue < 0.0 ? 1 : 2;
String str = (f < formatBits.Length) ? formatBits[f] : formatBits[0];
double wholePart = Math.Floor(Math.Abs(doubleValue));
double decPart = Math.Abs(doubleValue) - wholePart;
if (wholePart + decPart == 0)
{
return("0");
}
if (doubleValue < 0.0)
{
wholePart *= -1.0;
}
//str = regex.Replace(str, ReplaceWS);
// Split the format string into decimal and fraction parts
String[] parts = str.Replace(" *", " ").Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
String[] fractParts;
if (parts.Length == 2)
{
fractParts = parts[1].Split("/".ToCharArray());
}
else
{
fractParts = str.Split("/".ToCharArray());
}
// Excel supports both #/# and ?/?, but Java only the former
for (int i = 0; i < fractParts.Length; i++)
{
fractParts[i] = fractParts[i].Replace('?', '#');
}
if (fractParts.Length == 2)
{
int fractPart1Length = Math.Min(CountHashes(fractParts[1]), 4); // Any more than 3 and we go around the loops for ever
double minVal = 1.0;
double currDenom = Math.Pow(10, fractPart1Length) - 1d;
double currNeum = 0;
for (int i = (int)(Math.Pow(10, fractPart1Length) - 1d); i > 0; i--)
{
for (int i2 = (int)(Math.Pow(10, fractPart1Length) - 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);
}
}
}
DecimalFormat neumFormatter = new DecimalFormat(fractParts[0]);
DecimalFormat denomFormatter = new DecimalFormat(fractParts[1]);
CultureInfo cul = CultureInfo.CurrentCulture;
if (parts.Length == 2)
{
DecimalFormat wholeFormatter = new DecimalFormat(parts[0]);
String result = wholeFormatter.Format(wholePart, cul) + " " + neumFormatter.Format(currNeum, cul) + "/" + denomFormatter.Format(currDenom, cul);
return(result);
}
else
{
String result = neumFormatter.Format(currNeum + (currDenom * wholePart), cul) + "/" + denomFormatter.Format(currDenom, cul);
return(result);
}
}
else
{
throw new ArgumentException("Fraction must have 2 parts, found " + fractParts.Length + " for fraction format " + this.formatstr);
}
}
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;
}
}
}
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, "[y|m|M|d|s|h]+").Success)
{
//may be datetime string
ValueEval result = TryParseDateTime(s0, s1);
if (result != ErrorEval.VALUE_INVALID)
return result;
}
//The regular expression needs ^ and $.
if (Regex.Match(s1, @"^[\d,\#,\.,\$,\,]+$").Success)
{
//TODO: simulate DecimalFormat class in java.
FormatBase formatter = new DecimalFormat(s1);
return new StringEval(formatter.Format(s0));
}
else if (s1.IndexOf("/", StringComparison.Ordinal) == s1.LastIndexOf("/", StringComparison.Ordinal) && s1.IndexOf("/", StringComparison.Ordinal) >= 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
{
return TryParseDateTime(s0, s1);
}
}
