Exemple #1
0
        /**
         * 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);
        }
Exemple #2
0
        /**
         * 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);
        }