[Bug 63819] New: Spreadsheet DATEVALUE function missing

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 63819] New: Spreadsheet DATEVALUE function missing

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=63819

            Bug ID: 63819
           Summary: Spreadsheet DATEVALUE function missing
           Product: POI
           Version: unspecified
          Hardware: PC
                OS: Linux
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Please implement function DATEVALUE

https://support.office.com/en-us/article/DATEVALUE-function-DF8B07D4-7761-4A93-BC33-B7471BBFF252
https://help.libreoffice.org/Calc/DATEVALUE

It could by done like this: (adding sublass class in
org.apache.poi.ss.formula.functions.DateFunc)


public final class DateFunc extends Fixed3ArgFunction {

   ....

    public static final Function DATEVALUE = new Fixed1ArgFunction() {
            public ValueEval evaluate(int srcRowIndex, int srcColumnIndex,
ValueEval arg0) {
                try {
                    ValueEval ve = OperandResolver.getSingleValue(arg0,
srcRowIndex, srcColumnIndex);
                    String arg = OperandResolver.coerceValueToString(ve);

                    StringTokenizer t = new StringTokenizer(arg, "-/");
                    String A = t.nextToken();
                    String B = t.nextToken();
                    String C = t.hasMoreTokens() ? t.nextToken() : null;
                    if (B.length() > 3) {
                        throw new EvaluationException(ErrorEval.VALUE_INVALID);
                    }

                    int year;
                    int month;
                    int day;

                    int a = Integer.valueOf(A);
                    if (StringUtils.isNumeric(B)) {
                        int b = Integer.valueOf(B);
                        int c = Integer.valueOf(C);
                        month = b;
                        if (a >= 1900) { // =DATEVALUE("2011/02/23")
                            year = a;
                            day = c;
                        } else { // =DATEVALUE("8/22/2011")
                            year = c;
                            day = a;
                        }
                    } else {

                        String monthName = B.toLowerCase();
                        String months[] = new DateFormatSymbols().getMonths();
                        for (month = 0; month < 12; month++) {
                            if (months[month].startsWith(monthName)) {
                                break;
                            }
                        }
                        if (month >= 12) {
                            throw new
EvaluationException(ErrorEval.VALUE_INVALID);
                        }
                        month++;

                        if (C == null) {
                            // =DATEVALUE("22-MAY")
                            GregorianCalendar gc = new GregorianCalendar();
                            year = gc.get(Calendar.YEAR);
                            day = a;
                        } else {
                            // =DATEVALUE("22-MAY-2011")
                            year = Integer.valueOf(C);
                            day = a;
                        }
                    }

                    return new NumberEval(evaluate(year, month, day));
                } catch (EvaluationException e) {
                    return e.getErrorEval();
                }
            }
    };

   ...

}

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 63819] Spreadsheet DATEVALUE function missing

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=63819

Nick Burch <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Version|unspecified                 |4.0.x-dev
           Keywords|                            |PatchAvailable

--- Comment #1 from Nick Burch <[hidden email]> ---
Thanks for this!

Would you be able to work out a little test case for this? In code, in
pseudo-code, or as an extension to one of the existing date function unit
tests? That way, we can verify it stays working into the future too

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 63819] Spreadsheet DATEVALUE function missing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=63819

--- Comment #2 from Petr Michálek <[hidden email]> ---
Created attachment 36817
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=36817&action=edit
Diff with DATEVALUE function

Diff file with function DATEVALUE and little testcase is attached. Please look
at this.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 63819] Spreadsheet DATEVALUE function missing

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=63819

Sven <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]