poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: WEEKDAY Function in spreadsheet
Date Wed, 09 Sep 2009 15:46:35 GMT

After spending the whole day quite literally making hay - and baking in the
sun I may add - I have had the chance to apply the finishing touches to this
version of the class to evaluate Excel's WEEKDAY() function. It is much
changed from the first iteration - and all the better for it to my mind -
and here is the code;

import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
import java.util.Calendar;
import java.text.DateFormat;
import java.text.ParseException;

/**
 * An instance of this class may be used to evaluate Excel's WEEKDAY()
function.
 * The class follows the Singleton pattern meaning that only a single
instance
 * can exist. To make use of the class, it is necessary to do something like
 * the following;
 *
 * WeekdayFunction2 wf2 = WeekdayFunction2.getInstance();
 * int dayOfWeek = wf2.getWeekday("WEEKDAY(TODAY(),1)");
 *
 * Currently, the class is not able to offer the same range of processing as
 * could the WeekdayFunction class. The WeekdayFunction2 class can only
process
 * the following types of the WEEKDAY() function;
 *
 * WEEKDAY(TODAY())
 * WEEKDAY(TODAY(),1)
 * WEEKDAY(TODAY(),2)
 * WEEKDAY(TODAY(),3)
 * WEEKDAY(DATE(9999,99,99))
 * WEEKDAY(DATE(9999,99,99),1)
 * WEEKDAY(DATE(9999,99,99),2)
 * WEEKDAY(DATE(9999,99,99),3)
 * WEEKDAY(Date_String)
 * WEEKDAY(Date_String,1)
 * WEEKDAY(Date_String,2)
 * WEEKDAY(Date_String,3)
 *
 * This limitation will remain in place until a reliable RegEx can be found
for
 * identifying/validating Excel cell addresses.
 *
 * Also, note that there are three different RegExs defined for
 * validating\identifying date Strings. Whilst there is inevitably some
cross
 * over between them - for example the dates 12/12/2009 will be valid for
both
 * the DD/MM/YYYY and MM/DD/YYYY formats - this was felt necessary to allow
 * for possible internationalisation\localisation concerns. Currently,
exactly
 * the same processing occurs irrespective of the date format detected but
this
 * does not have to be the case and it can be modified if necessary to allow
 * for different locales.
 *
 * Finally, not that a considerable amount of testing/validating the various
 * parameter values is omitted. This decision was taken because the source
of
 * the WEEKDAY() functions is an Excel worksheet and the various parameter
 * values will have been checked and validated as they were entered into the
 * cells by the user.
 *
 * @author Mark Beardsley [msb at apache.org]
 * @version 1.10 9th September 2009
 */
public class WeekdayFunction2 {

    private static WeekdayFunction2 wf2 = null;

    private static final int DEFAULT_RETURN_VALUE = 1;
    private static final String DATE_FUNCTION_IDENTIFIER = "DATE(";
    private static final String TODAY_FUNCTION_IDENTIFIER = "TODAY(";
    private static final String DATE_STRING_DELIMITER = "\"";

    // Regular expressions for - respectively - TODAY() function with a
return
    // value, TODAY() function wihtou return value, DATE() function with
then
    // without return value, date String in MM/DD/YYYY format with and
without
    // a return value, date String in DD/MM/YYYY format with and without a
    // return value and a date String in YYYY/MM/DD format with and without
a
    // return value.
    //
    // Note that the different date RegEx's have been included to support
the
    // possibility of different locales. If this feature does prove useful,
    // then other changes wil have to be made to detect\allow the user to
pass
    // additional parameters that identify the locale. Likewise, new methods
    // will need to be included to handle parsing locale specific dates.
    private final String TODAY_FUNCTION_INT_RETURN =
            "^WEEKDAY\\(TODAY\\(\\)\\,([123]{1})\\)$";
    private final String TODAY_FUNCTION_NO_RETURN =
            "^WEEKDAY\\(TODAY\\(\\)\\)$";
    private final String DATE_FUNCTION_INT_RETURN =
           
"^WEEKDAY\\(DATE\\(((19|20)\\d\\d)\\,(1[012]|[1-9])\\,([1-9]|[12][0-9]|3[01])\\)\\,([123]{1})\\)$";
    private final String DATE_FUNCTION_NO_RETURN =
           
"^WEEKDAY\\(DATE\\(((19|20)\\d\\d)\\,(1[012]|[1-9])\\,([1-9]|[12][0-9]|3[01])\\)\\)$";
    private final String DATE_STRING_MMDDYYYY_INT_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)[/ -.]((19|20)\\d\\d)\\\"\\,([123]{1})\\)$";
    private final String DATE_STRING_MMDDYYYY_NO_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)[/ -.]((19|20)\\d\\d)\\\"\\)$";
    private final String DATE_STRING_DDMMYYYY_INT_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[12]\\d|30|31)[/
-.](0?[1-9]|[1][012])[/ -.]((19|20)\\d\\d)\\\"\\,([123]{1})\\)$";
    private final String DATE_STRING_DDMMYYYY_NO_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[12]\\d|30|31)[/
-.](0?[1-9]|[1][012])[/ -.]((19|20)\\d\\d)\\\"\\)$";
    private final String DATE_STRING_YYYYMMDD_INT_RETURN =
            "^WEEKDAY\\(\\\"((19|20)\\d\\d)[/ -.](0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)\\\"\\,([123]{1})\\)$";
    private final String DATE_STRING_YYYYMMDD_NO_RETURN =
            "^WEEKDAY\\(\\\"((19|20)\\d\\d)[/ -.](0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)\\\"\\)$";

    // The various Pattern class instances for the above regular
expressions.
    private Pattern todayIntReturn = null;
    private Pattern todayNoReturn = null;
    private Pattern dateIntReturn = null;
    private Pattern dateNoReturn = null;
    private Pattern stringMDYIntReturn = null;
    private Pattern stringMDYNoReturn = null;
    private Pattern stringDMYIntReturn = null;
    private Pattern stringDMYNoReturn = null;
    private Pattern stringYMDIntReturn = null;
    private Pattern stringYMDNoReturn = null;

    // DateFormat and Calendar instances for manipulating dates.
    private Calendar calendar = null;
    private DateFormat shortDateFormat = null;

    /**
     * Construct a new instance of the WeekdayFunction2 class. The
constructor
     * is private as this class obeys the Singleton pattern, that is to say
that
     * there can only be once instance of the class.
     *
     * @throws java.util.regex.PatternSyntaxException Thrown if a problem is
     *                                                encountered trying to
     *                                                compile the RegEx
strings
     *                                                into Pattern objects.
     */
    private WeekdayFunction2() throws PatternSyntaxException {
        this.todayIntReturn =
Pattern.compile(this.TODAY_FUNCTION_INT_RETURN);
        this.todayNoReturn = Pattern.compile(this.TODAY_FUNCTION_NO_RETURN);
        this.dateIntReturn = Pattern.compile(this.DATE_FUNCTION_INT_RETURN);
        this.dateNoReturn = Pattern.compile(this.DATE_FUNCTION_NO_RETURN);
        this.stringMDYIntReturn =
Pattern.compile(this.DATE_STRING_MMDDYYYY_INT_RETURN);
        this.stringMDYNoReturn =
Pattern.compile(this.DATE_STRING_MMDDYYYY_NO_RETURN);
        this.stringDMYIntReturn =
Pattern.compile(this.DATE_STRING_DDMMYYYY_INT_RETURN);
        this.stringDMYNoReturn =
Pattern.compile(this.DATE_STRING_DDMMYYYY_NO_RETURN);
        this.stringYMDIntReturn =
Pattern.compile(this.DATE_STRING_YYYYMMDD_INT_RETURN);
        this.stringYMDNoReturn =
Pattern.compile(this.DATE_STRING_YYYYMMDD_NO_RETURN);
        this.calendar = Calendar.getInstance();
        this.shortDateFormat = DateFormat.getDateInstance(DateFormat.SHORT);
    }

    /**
     * Recover a reference to the single istance of the WeekdayFunction2
class.
     *
     * @return The reference to an instance of the WeekdayFunction2 class.
     * @throws java.util.regex.PatternSyntaxException Thrown if a problem
occurs
     *                                                creating any of the
RegEx
     *                                                Patterns that this
class
     *                                                uses to validate and
     *                                                process the WEEKDAY()
     *                                                functions.
     */
    public static WeekdayFunction2 getInstance() throws
PatternSyntaxException {
        if(WeekdayFunction2.wf2 == null) {
            WeekdayFunction2.wf2 = new WeekdayFunction2();
        }
        return(WeekdayFunction2.wf2);
    }

    /**
     * Calculates and returns an integer that describes the adjusted -
offset -
     * day of the week from a given date value.
     *
     * @param functionString An instance of the String calss that
encapsulates
     *                       the WEEKDAY() function recovered from a
worksheet
     *                       cell.
     * @return A primitive int that contains the adjusted - offset - value
     *         for the day of the week.
     * @throws java.lang.IllegalArgumentException Thrown if the WEEKDAY()
     *                                            function is found not to
have
     *                                            a valid format.
     * @throws java.text.ParseException A date String can be used as the
source
     *                                  for the WEEKDAY() functions date.
This
     *                                  String must be converted into a Date
     *                                  object and a ParseException will be
     *                                  thrown if a problem occurs during
the
     *                                  conversion process.
     */
    public int getWeekday(String functionString) throws
IllegalArgumentException,
            ParseException {
        int excelDayOfWeek = 0;
        int index = 0;
        int[] dateArgs = null;
        String tempString = null;
        functionString = functionString.toUpperCase().trim();
        // The date is provided by the TODAY() function and an integer value
for
        // the return value has been supplied.
        if(this.todayIntReturn.matcher(functionString).matches()) {
            // Strip out the return_value parameter, convert into an integer
            // and call the getDOWFromToday() method.
            index = functionString.lastIndexOf(",");
            functionString = functionString.substring(++index);
            index = functionString.indexOf(")");
            functionString = functionString.substring(0, index);
            excelDayOfWeek =
this.getDOWFromToday(Integer.parseInt(functionString));
        }
        // The date is provided by the TODAY() function and an integer value
for
        // the return value has not been supplied.
        else if(this.todayNoReturn.matcher(functionString).matches()) {
            // In the absence of a return_value, somply pass the deafult
value
            // of one to the getDOWFromToday() method.
            excelDayOfWeek =
this.getDOWFromToday(WeekdayFunction2.DEFAULT_RETURN_VALUE);
        }
        // The date is provided by the DATE() function and an integer value
for
        // the return value has been supplied.
        else if(this.dateIntReturn.matcher(functionString).matches()) {
            index = functionString.indexOf("(");
            index++;
            functionString = functionString.substring(index).trim();
            index = functionString.lastIndexOf(",");
            dateArgs = this.getArgumentsFromDateFunction(
                    functionString.substring(0, index).trim());
            functionString = functionString.substring(index).trim();
            index = functionString.indexOf(",");
            index++;
            functionString = functionString.substring(index).trim();
            index = functionString.indexOf(")");
            functionString = functionString.substring(0, index).trim();
            excelDayOfWeek = this.getDOWFromDateFunction(
                    dateArgs, Integer.parseInt(functionString));
        }
        // The date is provided by the DATE() function and an integer value
for
        // the return value has not been supplied.
        else if(this.dateNoReturn.matcher(functionString).matches()) {
            index = functionString.indexOf("(");
            index++;
            functionString = functionString.substring(index).trim();
            index = functionString.lastIndexOf(")");
            functionString = functionString.substring(0, index);
            excelDayOfWeek = this.getDOWFromDateFunction(
                    this.getArgumentsFromDateFunction(functionString),
                    WeekdayFunction2.DEFAULT_RETURN_VALUE);
        }
        // The date is provided by a date String in the format MM/DD/YYYY
and
        // an integer value for the return value has been supplied.
        else if(this.stringMDYIntReturn.matcher(functionString).matches()) {
            index = functionString.lastIndexOf(",");
            index++;
            tempString = functionString.substring(index).trim();
            index = tempString.indexOf(")");
            tempString = tempString.substring(0, index).trim();
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    Integer.parseInt(tempString));
        }
        // The date is provided by a date String in the format MM/DD/YYYY
and
        // an integer value for the return value has not been supplied.
        else if(this.stringMDYNoReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    WeekdayFunction2.DEFAULT_RETURN_VALUE);
        }
        // The date is provided by a date String in the format DD/MM/YYYY
and
        // an integer value for the return value has been supplied.
        else if(this.stringDMYIntReturn.matcher(functionString).matches()) {
            index = functionString.lastIndexOf(",");
            index++;
            tempString = functionString.substring(index).trim();
            index = tempString.indexOf(")");
            tempString = tempString.substring(0, index).trim();
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    Integer.parseInt(tempString));
        }
        // The date is provided by a date String in the format DD/MM/YYYY
and
        // an integer value for the return value has not been supplied.
        else if(this.stringDMYNoReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    WeekdayFunction2.DEFAULT_RETURN_VALUE);
        }
        // The date is provided by a date String in the format YYYY/MM/DD
and
        // an integer value for the return value has been supplied.
        else if(this.stringYMDIntReturn.matcher(functionString).matches()) {
            index = functionString.lastIndexOf(",");
            index++;
            tempString = functionString.substring(index).trim();
            index = tempString.indexOf(")");
            tempString = tempString.substring(0, index).trim();
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    Integer.parseInt(tempString));
        }
        // The date is provided by a date String in the format YYYY/MM/DD
and
        // an integer value for the return value has not been supplied.
        else if(this.stringYMDNoReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    WeekdayFunction2.DEFAULT_RETURN_VALUE);
        }
        else {
            throw new IllegalArgumentException("The function " + 
                    functionString +
                    " was not recognised.");
        }
        return(excelDayOfWeek);
    }

    /**
     * The TODAY() function can be used to supply the date for the WEEKDAY()
     * function. This method uses the current system time as the basis for
     * calculating the day of the week.
     *
     * @param returnValue A primitive int that contains a value which
defines
     *                    how the day of the week should be adjusted or
offset.
     * @return A primitive int that contains the adjusted - offset - value
     *         for the day of the week.
     */
    private int getDOWFromToday(int returnValue) {
        this.calendar.setTimeInMillis(System.currentTimeMillis());
       
return(this.getExcelDayOfWeek(this.calendar.get(Calendar.DAY_OF_WEEK),
returnValue));
    }

    /**
     * The DATE() function can be used to supply the date for the WEEKDAY()
     * function. Three parameters are passed to the DATE() function and they
     * are, in order, the year, month and day. This method uses those values
     * to initialise a Calendar object, interrogates that for the day of the
     * week and then applies any offset to this value to recover what the
     * WEEKDAY() function would regard as being the day of the week for the
date.
     *
     * @param dateArgs An array of type int that contains the three integer
     *                 values for the year, month and day for a specific
date.
     * @param returnValue A primitive int that contains a value which
defines
     *                    how the day of the week should be adjusted or
offset.
     * @return A primitive int that contains the adjusted - offset - value
     *         for the day of the week.
     */
    private int getDOWFromDateFunction(int[] dateArgs, int returnValue) {
        this.calendar.set(Calendar.YEAR, dateArgs[0]);
        // Note that months are zero based - subtract one for the cirrect
value
        this.calendar.set(Calendar.MONTH, (dateArgs[1] - 1));
        this.calendar.set(Calendar.DAY_OF_MONTH, dateArgs[2]);
       
return(this.getExcelDayOfWeek(this.calendar.get(Calendar.DAY_OF_WEEK),
returnValue));
    }

    /**
     * It is possible to pass a date string to the WEEKDAY() function. This
     * method recovers the day of the week from that string and applies the
     * offset specified by the return_value parameter.
     *
     * @param dateString An instance of the String class that encapsulates a
     *                   valid date.
     * @param returnValue A primitive int whose value determines the offset
for
     *                    the day of the week. Offset in this case means
which
     *                    day is seen as the start of the week and what the
     *                    number of that day should be.
     * @return A primitive int whose value indicates what day of the wek the
     *         date defines.
     * @throws java.text.ParseException Thrown if it is not possible to
convert
     *                                  - parse - the date String into a
Date
     *                                  object.
     */
    private int getDOWFromDateString(String dateString,
            int returnValue) throws ParseException {

        // Set the time ofr the Calendar by converting the date String into
        // a Date object
        this.calendar.setTime(this.shortDateFormat.parse(dateString));
        // Convert Java's day of the week into Excel's day of the week
applying
        // any offset specified by the return_value.
       
return(this.getExcelDayOfWeek(this.calendar.get(Calendar.DAY_OF_WEEK),
returnValue));
    }

    /**
     * The day of the week value returned by the WEEKDAY() method can be
     * corrected or adjusted by specifying one of three values for the
     * return_value parameter. In order to mimic that behaviour, this method
     * tests the day of the week recovered from the date passed to the
WEEKDAY()
     * function's serial_number parameter and adjusts the value of the day
of
     * the week in line with the integer value that was passed to the
function's
     * return_value parameter.
     *
     * @param dayOfWeek A primitive int that contains a value which
indicates
     *                  the day of the week recovered for a specific date
from
     *                  Java's own Calendar classes.
     * @param returnValue A primitive int that indicates how the day of the
week
     *                    value should be adjusted.
     * @return A primitive int that contains the value for the day of the
week
     *         adjusted in line with the original specification in the
WEEKDAY()
     *         function.
     */
    private int getExcelDayOfWeek(int dayOfWeek, int returnValue) {

        int excelDayOfWeek = 0;
        switch(dayOfWeek) {
            case Calendar.MONDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 2;
                        break;
                    case 2:
                        excelDayOfWeek = 1;
                        break;
                    case 3:
                        excelDayOfWeek = 0;
                        break;
                }
                break;
            case Calendar.TUESDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 3;
                        break;
                    case 2:
                        excelDayOfWeek = 2;
                        break;
                    case 3:
                        excelDayOfWeek = 1;
                        break;
                }
                break;
            case Calendar.WEDNESDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 4;
                        break;
                    case 2:
                        excelDayOfWeek = 3;
                        break;
                    case 3:
                        excelDayOfWeek = 2;
                        break;
                }
                break;
            case Calendar.THURSDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 5;
                        break;
                    case 2:
                        excelDayOfWeek = 4;
                        break;
                    case 3:
                        excelDayOfWeek = 3;
                        break;
                }
                break;
            case Calendar.FRIDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 6;
                        break;
                    case 2:
                        excelDayOfWeek = 5;
                        break;
                    case 3:
                        excelDayOfWeek = 4;
                        break;
                }
                break;
            case Calendar.SATURDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 7;
                        break;
                    case 2:
                        excelDayOfWeek = 6;
                        break;
                    case 3:
                        excelDayOfWeek = 5;
                        break;
                }
                break;
            case Calendar.SUNDAY:
                switch(returnValue) {
                    case 1:
                        excelDayOfWeek = 1;
                        break;
                    case 2:
                        excelDayOfWeek = 7;
                        break;
                    case 3:
                        excelDayOfWeek = 6;
                        break;
                }
                break;
        }
        return(excelDayOfWeek);
    }

    /**
     * When the date value for the WEEKDAY() function is supplied by the
Excel
     * DATE() function, that latter function will be passed three values,
     * integers that indicate the year, month and day. Each value is
recovered
     * from that comma spearated list, converted into an integer and then
     * written into an arry which is returned once the method completes.
     *
     * @param dateFunction An instance of the String class encapsulating the
     *                     DATE() function along with it's comma separated
list
     *                     of parameter values.
     * @return An arry if type int whose elements contain - in order - the
year,
     *         month and day for the specified date.
     * @throws NumberFormatException Thrown if it is not possible to convert
     *                               any of the parameter values into an
int.
     */
    private int[] getArgumentsFromDateFunction(String dateFunction)
            throws NumberFormatException {
        int[] args = new int[3];


        int index = 0;
        // Extract each part of the date from the complete string.
        // Firstly, the Year
        index =
dateFunction.indexOf(WeekdayFunction2.DATE_FUNCTION_IDENTIFIER);
        index += WeekdayFunction2.DATE_FUNCTION_IDENTIFIER.length();
        dateFunction = dateFunction.substring(index).trim();
        index = dateFunction.indexOf(",");
        args[0] = Integer.parseInt(dateFunction.substring(0, index));
        // Next the month
        index++;
        dateFunction = dateFunction.substring(index).trim();
        index = dateFunction.indexOf(",");
        args[1] = Integer.parseInt(dateFunction.substring(0, index));
        // Finally the day
        index++;
        dateFunction = dateFunction.substring(index).trim();
        index = dateFunction.indexOf(")");
        args[2] = Integer.parseInt(dateFunction.substring(0, index));
        return(args);
    }

    /**
     * When the date value for the WEEKDAY() function is supplied by a
String,
     * Excel returns that value enclosed by quotation marks. This method
simply
     * strips those off of the source String and returns the result.
     *
     * @param dateString An instance of the String class encapsulating a
date
     *                   value enclosed by quoation marks; for example
     *                   "12/12/2009"
     * @return An instance of the String class encapsulating the same date
value
     *         that was passed to the dateString parameter with the leading
and
     *         trailing quotation marlks removed.
     */
    private String getDateStringFromFunction(String dateString) {
        int index =
dateString.indexOf(WeekdayFunction2.DATE_STRING_DELIMITER);
        index++;
        dateString = dateString.substring(index).trim();
        index = dateString.indexOf(WeekdayFunction2.DATE_STRING_DELIMITER);
        return(dateString.substring(0, index).trim());
    }
}

You need to adopt a slightly different approach to use the thing as well,
something like the following;

WeekdayFunction2 wf2 = WeekdayFunction2.getInstance();
......
......
case Cell.CELL_TYPE_FORMULA:
    String formulaStr = cell.getCellFormula();
    if(formulaStr.contains("WEEKDAY")) {
        try {
            System.out.println(wf2.getWeekday(formulaStr));
        }
        catch(Exception ex) {
            System.out.println("Caught: " + ex.getClass().getName());
            System.out.println("Message: " + ex.getMessage());
            System.out.println("Stacktrace follows................");
            ex.printStackTrace(System.out);
        }
    }
    break;

WeekdayFunction2 follows the Singleton pattern so there can be only s single
instance of the class. If you are working in a multi threaded environment,
it would be wise to consider adding the synchronized modifier to the methods
even though, constructor aside, there is no method that modifies the objects
state.

Hope this is better and I will post an updated version once I have nailed
down the RegEx for Excel's cell addresses. In truth the cell address is easy
- the A1 - XFD1048576 part with or without $ modifiers - the problems come
when I add the ability to include sheet names - the 'Sheet1!' part causes
trouble. Also, in that version the RegEx's will all be constants, I cannot
think why I did not declare them with the static modifier from the start!

Finally, as before, this is very much beta code so test throughly before use
please.

Yours

Mark B



VK123 wrote:
> 
> Mark,
> 
>   My answer is 'Yes'. My excel formula is some thing like =
> WEEKDAY(TODAY()). 
>  It will be great if you send me some code samples.
> 
> Regards,
> 
> Vijayakumar Gowdaman
> 
> Group Technology & Operations (GTO)
> Global Markets
> Deutsche Bank
> off: 02075456250
> Mob:07789773998
> 
> 
> 
> MSB <markbrdsly@tiscali.co.uk> 
> 04/09/2009 10:12
> Please respond to
> "POI Users List" <user@poi.apache.org>
> 
> 
> To
> user@poi.apache.org
> cc
> 
> Subject
> Re: WEEKDAY Function in spreadsheet
> 
> 
> 
> 
> 
> 
> 
> Forgive me for asking stupid questions but I want to make sure I 
> understand
> exactly what you are asking. Are you using POI to read an existing Excel
> workbook, finding that one of the cells contains the WEEKDAY() formula and
> are looking to find a way - using POI - to evaluate that formula?
> 
> If the answer to this question is 'yes' then I am sorry to say that the
> WEEKDAY formula has not yet been implented in POI but you do have - 
> possibly
> - options. It is possible to get at the formula itself and you could then
> write code to parse it yourself. This morning, I ran a quick check and the
> cell containing the WEEKDAY formula will be recognised as being type 
> formula
> by POI; it should then be possible for you to recover a String that 
> contains
> the formula the user entered into the cell. Using Java's string handling
> functions, it is easy to check to see whether this String contains the 
> word
> 'WEEKDAY' and it should then be possible to write code to determine the
> result of evaluating the formula. If you do not feel confident enough to 
> do
> this yourself, can I ask you to post examples of the WEEKDAY formula on 
> your
> worksheet and I will take a look at writing some code to help you out.
> 
> Yours
> 
> Mark B
> 
> 
> VK123 wrote:
>> 
>> Is there any way to evaluate the formula in a cell which contains 
> WEEKDAY 
>>  function in a spreadsheet.
>> 
>> Regards,
>> 
>> Vijayakumar Gowdaman
>> 
>> 
>> 
>> ---
>> 
>> This e-mail may contain confidential and/or privileged information. If 
> you
>> are not the intended recipient (or have received this e-mail in error)
>> please notify the sender immediately and delete this e-mail. Any
>> unauthorized copying, disclosure or distribution of the material in this
>> e-mail is strictly forbidden.
>> 
>> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
>> additional EU corporate and regulatory disclosures.
>> 
> 
> -- 
> View this message in context: 
> http://www.nabble.com/WEEKDAY-Function-in-spreadsheet-tp25279402p25290878.html
> 
> Sent from the POI - User mailing list archive at Nabble.com.
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 
> 
> 
> 
> 
> ---
> 
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received this e-mail in error)
> please notify the sender immediately and delete this e-mail. Any
> unauthorized copying, disclosure or distribution of the material in this
> e-mail is strictly forbidden.
> 
> Please refer to http://www.db.com/en/content/eu_disclosures.htm for
> additional EU corporate and regulatory disclosures.
> 

-- 
View this message in context: http://www.nabble.com/WEEKDAY-Function-in-spreadsheet-tp25279402p25367790.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message