Beispiel #1
0
        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);
                    }
                }
            }
Beispiel #4
0
        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);
            }
        }
Beispiel #5
0
        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;
                }
            }
        }
Beispiel #6
0
        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);
            }
        }