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 Fri, 11 Sep 2009 11:24:27 GMT

>From my point of view, this is the final iteration of the class. Note that I
have been forced to modify the signature of the getWeekday() method as, once
it became necessary to work with cell addresses, the class needed access to
both the workbook and the sheet.

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

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;


/**
 * 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)", workbook,
sheetNumber);
 *
 * where the workbook parameter will receive a reference to the workbook
 * currently being processed and the sheetNumber parameter the index number
of
 * the sheet that contained the cell the WEEKDAY() function was recovered
from.
 *
 * Currently, the following types of the WEEKDAY() function can be
 * processed/evaluated;
 *
 * WEEKDAY(TODAY())
 * WEEKDAY(TODAY(),1)
 * WEEKDAY(TODAY(),2)
 * WEEKDAY(TODAY(),3)
 * WEEKDAY(TODAY(),Cell_Address)
 * 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(9999,99,99),Cell_Address)
 * WEEKDAY(Date_String)
 * WEEKDAY(Date_String,1)
 * WEEKDAY(Date_String,2)
 * WEEKDAY(Date_String,3)
 * WEEKDAY(Date_String,Cell_Address)
 * WEEKDAY(Cell_Address)
 * WEEKDAY(Cell_Address,1)
 * WEEKDAY(Cell_Address,2)
 * WEEKDAY(Cell_Address,3)
 * WEEKDAY(Cell_Address,Cell_Address)
 *
 * Note that there are three different RegExs defined for validating or
 * dentifying 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, note that a considerable amount of testing or 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
 *          1.20 10th September 2009. Added support for cell addresses. Now,
it
 *                                    is possible to specify a cell address
as
 *                                    the source of both the date and return
 *                                    value for the WEEKDAY() function.
 */
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 static final String TODAY_FUNCTION_INT_RETURN =
            "^WEEKDAY\\(TODAY\\(\\)\\,([123]{1})\\)$";
    private static final String TODAY_FUNCTION_NO_RETURN =
            "^WEEKDAY\\(TODAY\\(\\)\\)$";
    private static final String TODAY_FUNCTION_CELL_ADDR_RETURN =
           
"^WEEKDAY\\(TODAY\\(\\)\\,(\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";
    private static 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 static final String DATE_FUNCTION_NO_RETURN =
           
"^WEEKDAY\\(DATE\\(((19|20)\\d\\d)\\,(1[012]|[1-9])\\,([1-9]|[12][0-9]|3[01])\\)\\)$";
    private static final String DATE_FUNCTION_CELL_ADDR_RETURN =
           
"^WEEKDAY\\(DATE\\(((19|20)\\d\\d)\\,(1[012]|[1-9])\\,([1-9]|[12][0-9]|3[01])\\)\\,(\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";
    private static 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 static 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 static final String DATE_STRING_MMDDYYYY_CELL_ADDR_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)[/
-.]((19|20)\\d\\d)\\\"\\,(\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";
    private static 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 static final String DATE_STRING_DDMMYYYY_NO_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)[/ -.]((19|20)\\d\\d)\\\"\\)$";
    private static final String DATE_STRING_DDMMYYYY_CELL_ADDR_RETURN =
            "^WEEKDAY\\(\\\"(0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)[/
-.]((19|20)\\d\\d)\\\"\\,(\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";
    private static 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 static final String DATE_STRING_YYYYMMDD_NO_RETURN =
            "^WEEKDAY\\(\\\"((19|20)\\d\\d)[/ -.](0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)\\\"\\)$";
    private static final String DATE_STRING_YYYYMMDD_CELL_ADDR_RETURN =
            "^WEEKDAY\\(\\\"((19|20)\\d\\d)[/ -.](0?[1-9]|[1][012])[/
-.](0?[1-9]|[12]\\d|30|31)\\\"\\,(\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";
    private static final String CELL_ADDR_DATE_INT_RETURN = 
           
"^WEEKDAY\\((\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\,([123]{1})\\)$";
    private static final String CELL_ADDR_DATE_NO_RETURN =
           
"^WEEKDAY\\((\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";
    private static final String CELL_ADDR_DATE_CELL_ADDR_RETURN =
           
"^WEEKDAY\\((\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\,(\\w*?\\!)?\\$?([a-zA-Z]){1,3}\\$?([1-9]){1}(([0-9]){1,9})?\\)$";

    // The various Pattern class instances for the above regular
expressions.
    private Pattern todayIntReturn = null;
    private Pattern todayNoReturn = null;
    private Pattern todayCellAddrReturn = null;
    private Pattern dateIntReturn = null;
    private Pattern dateNoReturn = null;
    private Pattern dateCellAddrReturn = null;
    private Pattern stringMDYIntReturn = null;
    private Pattern stringMDYNoReturn = null;
    private Pattern stringMDYCellAddrReturn = null;
    private Pattern stringDMYIntReturn = null;
    private Pattern stringDMYNoReturn = null;
    private Pattern stringDMYCellAddrReturn = null;
    private Pattern stringYMDIntReturn = null;
    private Pattern stringYMDNoReturn = null;
    private Pattern stringYMDCellAddrReturn = null;
    private Pattern cellAddrDateNoReturn = null;
    private Pattern cellAddrDateIntReturn = null;
    private Pattern cellAddrDateCellAddrReturn = 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(
                WeekdayFunction2.TODAY_FUNCTION_INT_RETURN);
        this.todayNoReturn = Pattern.compile(
                WeekdayFunction2.TODAY_FUNCTION_NO_RETURN);
        this.todayCellAddrReturn = Pattern.compile(
                WeekdayFunction2.TODAY_FUNCTION_CELL_ADDR_RETURN);
        this.dateIntReturn = Pattern.compile(
                WeekdayFunction2.DATE_FUNCTION_INT_RETURN);
        this.dateNoReturn = Pattern.compile(
                WeekdayFunction2.DATE_FUNCTION_NO_RETURN);
        this.dateCellAddrReturn = Pattern.compile(
                WeekdayFunction2.DATE_FUNCTION_CELL_ADDR_RETURN);
        this.stringMDYIntReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_MMDDYYYY_INT_RETURN);
        this.stringMDYNoReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_MMDDYYYY_NO_RETURN);
        this.stringMDYCellAddrReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_MMDDYYYY_CELL_ADDR_RETURN);
        this.stringDMYIntReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_DDMMYYYY_INT_RETURN);
        this.stringDMYNoReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_DDMMYYYY_NO_RETURN);
        this.stringDMYCellAddrReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_DDMMYYYY_CELL_ADDR_RETURN);
        this.stringYMDIntReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_YYYYMMDD_INT_RETURN);
        this.stringYMDNoReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_YYYYMMDD_NO_RETURN);
        this.stringYMDCellAddrReturn = Pattern.compile(
                WeekdayFunction2.DATE_STRING_YYYYMMDD_CELL_ADDR_RETURN);
        this.cellAddrDateIntReturn = Pattern.compile(
                WeekdayFunction2.CELL_ADDR_DATE_INT_RETURN);
        this.cellAddrDateNoReturn = Pattern.compile(
                WeekdayFunction2.CELL_ADDR_DATE_NO_RETURN);
        this.cellAddrDateCellAddrReturn = Pattern.compile(
                WeekdayFunction2.CELL_ADDR_DATE_CELL_ADDR_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.
     * @param workbook An instance of the HSSFWorkbook or XSSFWorkbook
classes
     *                 that encapsulates a reference to an Excel workbook.
     * @param sheetNumber A primitive int whose value identifies theindex
number
     *                    of a sheet in an Excel workbook.
     * @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,
            Workbook workbook, int sheetNumber) 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 TODAY() function but the return value
        // is held in a worksheet cell.
        else if(this.todayCellAddrReturn.matcher(functionString).matches())
{
            excelDayOfWeek = this.getDOWFromToday(
                    this.getReturnValueCellValue(functionString, workbook,
sheetNumber));
        }
        // 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 value is provided by the DATE() function and the value
for
        // the return value must be recovered from a workbook cell.
        else if(this.dateCellAddrReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateFunction(
                    this.getArgumentsFromDateFunction(functionString),
                    this.getReturnValueCellValue(functionString, workbook,
sheetNumber));
        }
        // 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);
        }
        else
if(this.stringMDYCellAddrReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    this.getReturnValueCellValue(functionString, workbook,
sheetNumber));
        }
        // 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);
        }
        else
if(this.stringDMYCellAddrReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    this.getReturnValueCellValue(functionString, workbook,
sheetNumber));
        }
        // 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
if(this.stringYMDCellAddrReturn.matcher(functionString).matches()) {
            excelDayOfWeek = this.getDOWFromDateString(
                    this.getDateStringFromFunction(functionString),
                    this.getReturnValueCellValue(functionString, workbook,
sheetNumber));
        }
        else
if(this.cellAddrDateIntReturn.matcher(functionString).matches()) {
            index = functionString.lastIndexOf(",");
            index++;
            tempString = functionString.substring(index).trim();
            index = tempString.indexOf(")");
            tempString = tempString.substring(0, index).trim();
            excelDayOfWeek = getDOWFromCellAddress(
                    this.getDateFromCellAddress(functionString, workbook,
sheetNumber),
                    Integer.parseInt(tempString));
        }
        else if(this.cellAddrDateNoReturn.matcher(functionString).matches())
{
            excelDayOfWeek = getDOWFromCellAddress(
                    this.getDateFromCellAddress(functionString, workbook,
sheetNumber),
                    WeekdayFunction2.DEFAULT_RETURN_VALUE);
        }
        else
if(this.cellAddrDateCellAddrReturn.matcher(functionString).matches()) {
            excelDayOfWeek = getDOWFromCellAddress(
                    this.getDateFromCellAddress(functionString, workbook,
sheetNumber),
                    this.getReturnValueCellValue(functionString, workbook,
sheetNumber));
        }
        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));
    }

    /**
     * Calculates Exce;'s day of the week nummber for WEEKDAY() functions
where
     * the date - the serial number - value has been supplied by the
contents of
     * another cell on a worksheet within the same workbook.
     *
     * @param date An instance of the java.util.Date class that encapsulates
     *             the contenst of the spreadsheet cell.
     * @param returnValue A second actual parameter can be passed to the
WEEKDAY()
     *                    function to indicate what the first day of the
week
     *                    ought to be and what the number of the first day
of
     *                    the week should be. Called the return_value, it
will
     *                    be passed to this formal parameter.
     * @return A primitive int containing Excel's day of the week number.
     */
    private int getDOWFromCellAddress(Date date, int returnValue) {
        this.calendar.setTime(date);
        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());
    }

    /**
     * Cell addresses can be supplied to provide either or both actual
parameter
     * values for the WEEKDAY() function. This method recovers the contents
of
     * a cell, validates the value and then returns it.
     *
     * @param functionString The function String recovered from a worksheet
     *                       cell.
     * @param workbook The workbook that contained the cell the WEEKDAY()
     *                 function String was read from. Note that whilst it is
     *                 possible for the serial_numbers value to be read from
a
     *                 cell on another sheet within the same workbook, it is
not
     *                 possible to access cells on sheets within other,
     *                 different, workbooks.
     * @param sheetNumber The number of the sheet that contained the cell
from
     *                    which the WEEKDAY() function was read.
     * @return A primitive int that contains the value recovered from the
cell.
     * @throws IllegalArgumentException Thrown if the cell is of the wrong
type
     *                                  to hold a number or if the number
     *                                  recovered from the cell is out or
range.
     */
    private int getReturnValueCellValue(String functionString, Workbook
workbook,
            int sheetNumber) throws IllegalArgumentException {
        CellReference cellRef = null;
        Sheet sheet = null;
        Cell cell = null;
        String cellAddress = null;
        int returnValue = 0;

        // Recover the cell address from the functionString and create a
        // CellReference object from that.
        cellAddress = this.getReturnValueCellAddress(functionString);
        cellRef = new CellReference(cellAddress);

        // Try to recover the name of the sheet from the cell reference
object.
        // If a null value is returned, the cell will be on the sheet whose
        // index is contained within the sheetNumber parameter.
        if(cellRef.getSheetName() == null) {
            sheet = workbook.getSheetAt(sheetNumber);
        }
        // If the call to get the sheet name does return a String, use this
        // to recover a reference to the named sheet from the workbook.
        else {
            sheet = workbook.getSheet(cellRef.getSheetName());
        }

        // Use the cell reference object again to recover the specific cell
        // from the sheet and check it's type. It must be numeric.
        cell = sheet.getRow(cellRef.getRow()).getCell(cellRef.getCol());
        if(cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            throw new IllegalArgumentException("The cell address " +
                    cellAddress +
                    " must point to a numeric cell.");
        }
        
        // Get the value from the cell and check it falls within range. The
range
        // of valid values for the return_value parameter is 1, 2 and 3.
        returnValue = (int)cell.getNumericCellValue();
        if(returnValue < 0 || returnValue > 3) {
            throw new IllegalArgumentException("The return value must be an
" +
                    "integer bewteen 1 and 3 inclusive.");
        }
        return(returnValue);
    }

    /**
     * Cell addresses can be supplied to provide either or both actual
parameter
     * values for the WEEKDAY() function. This method recovers the cell
address
     * for the return_value parameter from the WEEKDAY() function String.
     *
     * @param functionString The function String recovered from a worksheet
     *                       cell.
     * @return An instance of the String class encapsulating the address of
     *         a cell on the worksheet in Excel's format, for example A1.
     */
    private String getReturnValueCellAddress(String functionString) {
        int from = 0;
        int to = 0;
        from = functionString.lastIndexOf(",");
        from++;
        to = functionString.indexOf(")", from);
        return(functionString.substring(from, to).trim());
    }

    /**
     * Cell addresses can be supplied to provide either or both actual
parameter
     * value for the WEEKDAY() function. This method recoveres the first of
those
     * cell addresses and reads the date value - the actual parameter value
for
     * the serial_number parameter of the WEEKDAY() function - from a cell
on a
     * worksheet.
     *
     * @param functionString The function String recovered from a worksheet
     *                       cell.
     * @param workbook The workbook that contained the cell the WEEKDAY()
     *                 function String was read from. Note that whilst it is
     *                 possible for the serial_numbers value to be read from
a
     *                 cell on another sheet within the same workbook, it is
not
     *                 possible to access cells on sheets within other,
     *                 different, workbooks.
     * @param sheetNumber The number of the sheet that contained the cell
from
     *                    which the WEEKDAY() function was read.
     * @return An instance of the java.util.Date class that encapsulates the
     *         date value recovered from the worksheet cell.
     * @throws java.lang.IllegalArgumentException Thrown if the type of the
     *                                            cell is incorrect or if it
is
     *                                            not formatted to hold a
date.
     */
    private Date getDateFromCellAddress(String functionString,
            Workbook workbook, int sheetNumber) throws
IllegalArgumentException {
        CellReference cellRef = null;
        Sheet sheet = null;
        Cell cell = null;
        Date date = null;
        String sheetName = null;
        int index = 0;

        // Find the location of the opening bracket of the WEEKDAY()
function
        // and discard all of those characters that precede it.
        index = functionString.indexOf("(");
        index++;
        functionString = functionString.substring(index).trim();

        // The character that marks the end of the cell address can be
either
        //a comma or a closing bracket - depending upon whether a
return_value
        // was passed to the WEEKDAY() function. So, search for the comma
and
        // if it is not found, search for the bracket.
        index = functionString.indexOf(",");
        if(index < 0) {
            index = functionString.indexOf(")");
        }

        // And recover the address of the cell that holds the date value -
the
        // serial_number - for the WEEKDAY() function.
        functionString = functionString.substring(0, index).trim();

        // Use the cell address to instantiate the CellReference object then
        // attempt to recover the name of the sheet from this object. If a
null
        // value is returned, use the value in the sheetNumber parameter to
        // recover a sheet from the workbook, else recover the named sheet.
        cellRef = new CellReference(functionString);
        if((sheetName = cellRef.getSheetName()) == null) {
            sheet = workbook.getSheetAt(sheetNumber);
        }
        else {
            sheet = workbook.getSheet(sheetName);
        }

        // Recover a cell from the sheet and check it's type. It MUST be a
        // numeric cella and, further, it must be date formatted
        cell = sheet.getRow(cellRef.getRow()).getCell(cellRef.getCol());
        if(cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            throw new IllegalArgumentException("The cell " +
                    cellRef.formatAsString() +
                    " is the wrong type to hold a date value.");
        }
        // Ensure that the cell has been formatted to hold a date - the
first
        // check above would allow past ANY numeric cells.
        if(!DateUtil.isCellDateFormatted(cell)) {
            throw new IllegalArgumentException("The cell " +
                    cellRef.formatAsString() +
                    " does not contain a date value.");
        }
        
        // Get and return the cell's Date value.
        return(cell.getDateCellValue());
    }
}

Test it throughly before use of course and I hope this helps.

Yours

Mark B



VK123 wrote:
> 
> Thanks for your code Mark. I'll give a go and let you know how it went. 
> 
> Regards,
> 
> Vijayakumar Gowdaman
> 
> 
> 
> 
> MSB <markbrdsly@tiscali.co.uk> 
> 07/09/2009 07:46
> Please respond to
> "POI Users List" <user@poi.apache.org>
> 
> 
> To
> user@poi.apache.org
> cc
> 
> Subject
> Re: WEEKDAY Function in spreadsheet
> 
> 
> 
> 
> 
> 
> 
> Well, I have to report partial success so far and a few interesting 
> problems
> still to solve.
> 
> The first 'problem' is that the code cannot currently accept a cell 
> address
> as the source of the date value, as the serial_number argument; this 
> should
> however be quite easy to correct. It does mean however, that in the code I
> am posting to you now, it is not possible to use the address of a cell to
> provide the source for the date. The second problem I ran into concerns 
> tha
> Calendar class; put simply it did not function as I expected it to.
> 
> Initially, I was changing the date by calling the set method of the 
> Calendar
> class, passing an integer value to indicate which value I wanted to set
> (year, month or day) and passing the values recovered from the WEEKDAY()
> functions serial_number parameter. This however failed to yield the 
> expected
> values when I interrogated the Calendar object for the day of the week. As 
> a
> result, I had to resort to using a DateFormat object to create dates from 
> a
> String and pass this to the setTime() method of the Calendar class. If I 
> did
> this, the day of the week returned after interrogating the Calendar 
> instance
> was as expected. It seemed more important to get the code to you and to 
> then
> pursue the answer to this problem and this is what I have chosen to do.
> 
> Below, I have attached the code for the WeekdayFunction class;
> 
> import java.util.Calendar;
> import java.util.GregorianCalendar;
> import java.text.DateFormat;
> import java.text.ParseException;
> import javax.management.InvalidAttributeValueException;
> 
> import org.apache.poi.ss.usermodel.Workbook;
> import org.apache.poi.ss.usermodel.Sheet;
> import org.apache.poi.ss.usermodel.Cell;
> import org.apache.poi.ss.util.CellReference;
> 
> /**
>  * Supports limited processing of the Excel WEEKDAY() function.
>  *
>  * @author Mark Beardsley [msb at apache.org]
>  * @version 1.00 5th September 2009
>  */
> public class WeekdayFunction {
> 
>     private static Calendar calendar = null;
>     private static DateFormat shortDateFormatter = null;
> 
>     private static final int TODAY_FUNCTION = 0;
>     private static final int DATE_FUNCTION = 1;
>     private static final int DATE_STRING = 2;
>     private static final int CELL_ADDRESS = 3;
>     private static final String WEEKDAY_FUNCTION_ID = "WEEKDAY(";
>     private static final String DATE_FUNCTION_ID = "DATE(";
> 
>     static {
>         shortDateFormatter = DateFormat.getDateInstance(DateFormat.SHORT);
>         calendar = new GregorianCalendar();
>     }
> 
>     /**
>      * The main purpose of this method is to identify which flavour of the
>      * WEEKDAY() function has been recovered from the worksheet cell  to
>      * strip out the arguments that were passed to the WEEKDAY() function
>      * and then to determine the corrected day of the week from the 
> provided
>      * date value.
>      *
>      * Excel's WEEKDAY() function returns an integer that indicates the 
> day
> of
>      * the week for a given date. This method is the main entry point for
> the
>      * WeekdayFunction class that provides a limited replca of that
>      * functionality.
>      *
>      * The function's syntax is often expressed as;
>      *
>      * WEEKDAY(serial_number, return_value)
>      *
>      * The serial_number parameter is a date value expressed as a number 
> or
> as
>      * a date in quotation marks. So, for the purposes of this initial
> attempt
>      * at processing the function, the range of arguments wil be limited 
> to
>      * the following;
>      *
>      * - The TODAY() function.
>      * - The DATE() function.
>      * - The address of a spreadsheet cell that itself contains a date.
>      * - A date contained within quotation marks.
>      *
>      * The return_value parameter is optional but controls when the week
>      * starts and the exact value returned. One of the following values 
> may
> be
>      * passed to this parameter;
>      *
>      *   Value           Explanation
>      *     1             Returns a number from 1 (Sunday) to 7 (Saturday). 
> This is the
>      *          default also if the return_value parameter is omitted.
>      *     2             Returns a number from 1 (Monday) to 7 (Sunday).
>      *     3             Returns a number from 0 (Monday) to 6 (Sunday).
>      *
>      * Again, for the purposes of this initial attempt at processing the
>      * function, the range of acceptable values will be limited to the
>      * following;
>      *
>      * - Nothing - the parameter can be omitted.
>      * - The address of a spreadsheet cell that itself contains an integer
>      *   value between 1 and 3.
>      * - An integer value between 1 and 3.
>      *
>      * From this, it is easy to identify the 'set' of 'flavours' the first
>      * attempt at processing will be able to handle;
>      *
>      * WEEKDAY(TODAY())
>      * WEEKDAY(TODAY(), Cell Address)
>      * WEEKDAY(TODAY(), Integer Value)
>      * WEEKDAY(DATE(9999,99,99))
>      * WEEKDAY(DATE(9999,99,99), Cell Address)
>      * WEEKDAY(DATE(9999,99,99), Integer Value)
>      * WEEKDAY(Cell Address)
>      * WEEKDAY(Cell Address, Cell Address)
>      * WEEKDAY(Cell Address, Integer Value)
>      * WEEKDAY(Date String)
>      * WEEKDAY(Date String, Cell Address)
>      * WEEKDAY(Date String, Integer Value)
>      *
>      * @param workbook An instance of the Workbook class that will
> encapsulate a
>      *                 referemnce to the Excel workbok that held the cell
> which
>      *                 contained the WEEKDAY() formula.
>      * @param sheet An instance of the Sheet class that will encapsulate a
>      *              referemce to an Excel worksheet that held the cell 
> which
>      *              contained the WEEKDAY() formula
>      * @param functionString An instance of the String class that will
>      *                       encapsulate the WEEKDAY() formula recovered
> from
>      *                       a cell on an Excel worksheet.
>      * @return A primitive int whose value indicates that day of the week
> for a
>      *         given date.
>      * @throws java.text.ParseException Dates must be recovered from the
> String
>      *                                  encapsulated by the functionString
>      *                                  parameter. Converting a String 
> into
> a
>      *                                  Date may fail giving rise to a
>      *                                  ParseException.
>      * @throws java.lang.IllegalArgumentException Thrown if the value 
> passed
>      *                                            to the return_value
> parameter
>      *                                            of the WEEKDAY() method
> was
>      *                                            non-numeric.
>      * @throws InvalidAttributeValueException Thrown if the value passed 
> to
> the
>      *                                        return_value parameter of 
> the
>      *                                        WEEKDAY() method was 
> inavlie,
> i.e.
>      *                                        outside of the range 1 - 3.
>      */
>     public static int getWeekday(Workbook workbook, Sheet sheet,
>             String functionString) throws ParseException,
>                                           IllegalArgumentException,
>                                           InvalidAttributeValueException {
>         String[] functionParams = null;
>         int index = 0;
>         int serialSource = 0;
> 
>         // Firstly, extract just that part of the WEEKDAY function string
>         // that identifies the parameters list.
>         functionString = functionString.substring(
>                 WeekdayFunction.WEEKDAY_FUNCTION_ID.length());
>         functionString = functionString.substring(
>                 0, (functionString.length() - 1));
> 
>         // The DATE function presents special problems as it's parameter
> list
>         // is comma separated. Thus, it must be dealt with separately 
> here.
>         if(functionString.contains(WeekdayFunction.DATE_FUNCTION_ID)) {
> 
>             // Set the serialSource variable to reflect the fact that the
> DATE()
>             // function was used to set the date's value then unpack the
>             // WEEKDAY function's parameters.
>             serialSource = WeekdayFunction.DATE_FUNCTION;
>             functionParams =
> WeekdayFunction.getWeekdayDateFunctionParameters(
>                     functionString);
>         }
>         else {
> 
>             // We are not dealing with a function String that contains the
>             // DATE function so the same method can be used to recover the
>             // parameters for all other WEEKDAY function.
>             functionParams = WeekdayFunction.getWeekdayFunctionParameters(
>                     functionString);
> 
>             // Identify the source of the WEEKDAY functions date value, is
>             // it provided by the contents of a cell, by the TODAY 
> function
>             // or by a String containing a date.
>             serialSource = WeekdayFunction.getSourceID(functionParams);
>         }
>  
>         return(WeekdayFunction.getDayAsInteger(workbook, sheet,
> functionParams[0],
>                 functionParams[1], serialSource));
>     }
> 
>     /**
>      * The first step in recovering the daye of the week from a date, this
>      * method calls two others. First, the value of the WEEKDAY() 
> function's
>      * return_value paremeter is recovered; this will be one of the
> following
>      * three integer values: 1, 2, or 3.
>      *
>      * Next the actual number of day of the week is recovered from the 
> Date
>      * that was passed to the WEEKDAY() function's serial_number parameter
> with
>      * any necessary corrections applied as specified by the value 
> contained
>      * within that function's return_value parameter.
>      *
>      * @param workbook An instabce of the Workbook class that will
> encapsulate a
>      *                 referemnce to the Excel workbok that held the cell
> which
>      *                 contained the WEEKDAY() formula.
>      * @param sheet An instance of the Sheet class that will encapsulate a
>      *              referemce to an Excel worksheet that held the cell 
> which
>      *              contained the WEEKDAY() formula
>      * @param serialNumber An instance of the String class that 
> encapsulates
>      *                     the value passed to the WEEKDAY() function's
>      *                     serial_number parameter. Currently, this is
> limited
>      *                     to the Excel functions TODAY() and DATE(), to a
> date
>      *                     String or to the address of a spreadsheet cell
> that
>      *                     itself contains a date.
>      * @param returnValue An instance of the String class that 
> encapsulates
> the
>      *                    value passed to the WEEKDAY() function's
> return_value
>      *                    parameter.
>      * @param serialSource A primitive int whose vaue indicates the
> 'flavour'
>      *                     of the WEEKDAY() function that is currently 
> being
>      *                     processed. The following four constants are
> provided
>      *                     = WeekdayFunction.CELL_ADDRESS
>      *                     = WeekdayFunction.DATE_FUNCTION
>      *                     = WeekdayFunction.DATE_STRING
>      *                     = WeekdayFunction.TODAY_FUNCTION
>      * @return A primitive int whose value indicates the day of the week 
> for
> a
>      *         given date.
>      * @throws java.text.ParseException Dates must be recovered from the
> String
>      *                                  encapsulated by the functionString
>      *                                  parameter. Converting a String 
> into
> a
>      *                                  Date may fail giving rise to a
>      *                                  ParseException.
>      * @throws java.lang.IllegalArgumentException Thrown if the value 
> passed
>      *                                            to the return_value
> parameter
>      *                                            of the WEEKDAY() method
> was
>      *                                            non-numeric.
>      * @throws InvalidAttributeValueException Thrown if the value passed 
> to
> the
>      *                                        return_value parameter of 
> the
>      *                                        WEEKDAY() method was 
> inavlie,
> i.e.
>      *                                        outside of the range 1 - 3.
>      */
>     private static int getDayAsInteger(Workbook workbook, Sheet sheet,
>             String serialNumber, String returnValue, int serialSource)
>                                          throws ParseException,
>                                                 IllegalArgumentException,
>  
> InvalidAttributeValueException {
> 
>         // Convert the String passed to the WEEKDAY() function's
> return_value
>         // parameter into an int.
>         int intReturnValue = WeekdayFunction.getReturnValue(
>                 workbook, sheet, returnValue);
>  
>         // Parse the date indicated by/encapsulated within the WEEKDAY()
>         // function serial_number parameter and return an integer that
>         // indicates the number of the day of the week.
>         return(WeekdayFunction.getDayNumber(workbook,
>                 sheet, serialNumber, intReturnValue, serialSource));
>     }
> 
>     /**
>      * The value passed to the WEEKDAY() function's return_value parameter
>      * describes a correction that should be applied when calculating the
>      * number of the day of the week for a given date. As recovered from 
> an
>      * Excel spreadsheet cell, the value passed to this parameter could
> firstly
>      * be omitted, be an integer value between 1 and 3 encapsulated within
> an
>      * instance of the String class, or be in the form of a cell address.
> This
>      * method, performs the work required to recover and convert this 
> value
>      * into a primitive integer value.
>      *
>      * @param workbook An instance of the Workbook class that will
> encapsulate a
>      *                 referemnce to the Excel workbok that held the cell
> which
>      *                 contained the WEEKDAY() formula.
>      * @param sheet An instance of the Sheet class that will encapsulate a
>      *              referemce to an Excel worksheet that held the cell 
> which
>      *              contained the WEEKDAY() formula
>      * @param returnValue An instance of the String class that 
> encapsulates
>      *                    the value recovered from the WEEKDAY() 
> function's
>      *                    serial_number parameter
>      * @return A primitive int containing the value originally passed to 
> the
>      *         the return_value parameter of the WEEKDAY() function.
>      * @throws java.lang.IllegalArgumentException Thrown if the value 
> passed
>      *                                            to the return_value
> parameter
>      *                                            of the WEEKDAY() method
> was
>      *                                            non-numeric.
>      * @throws InvalidAttributeValueException Thrown if the value passed 
> to
> the
>      *                                        return_value parameter of 
> the
>      *                                        WEEKDAY() method was 
> inavlid,
> i.e.
>      *                                        outside of the range 1 - 3.
>      */
>     private static int getReturnValue(Workbook workbook,
>             Sheet sheet, String returnValue) throws
> IllegalArgumentException,
>  
> InvalidAttributeValueException {
>         CellReference cellRef = null;
>         Cell cell = null;
>         int intReturnValue = 0;
> 
>         // Try to convert the contents of the strReturnValue parameter 
> into
> an
>         // integer value. If this step fails then the parameter MUST have
>         // held the address of a cell.
>         try {
>             intReturnValue = Integer.parseInt(returnValue);
>         }
> 
>         // Retrieve the cell from the workbook and ensure it contains a
> number.
>         catch(NumberFormatException nfe) {
>             cellRef = new CellReference(returnValue);
>             if(cellRef.getSheetName() != null) {
>                 sheet = workbook.getSheet(cellRef.getSheetName());
>             }
>             cell = 
> sheet.getRow(cellRef.getRow()).getCell(cellRef.getCol());
>             if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
>                 intReturnValue = (int)cell.getNumericCellValue();
>             }
>             else {
>                 throw new IllegalArgumentException(
>                         "This cell " +
>                         returnValue +
>                         " did not contain a numeric value.");
>             }
>         }
>  
>         // Ensure the value is valid, i.e. between 1 and 3 inclusive.
>         if(intReturnValue < 1 || intReturnValue > 3) {
>             throw new InvalidAttributeValueException("The return value can 
> "
> +
>                     "only be an integer between 1 and 3.");
>         }
> 
>         // Return the converted value.
>         return(intReturnValue);
>     }
> 
>     /**
>      * Recover the day of the week number from a date value.
>      *
>      * @param workbook An instabce of the Workbook class that will
> encapsulate a
>      *                 referemnce to the Excel workbok that held the cell
> which
>      *                 contained the WEEKDAY() formula.
>      * @param sheet An instance of the Sheet class that will encapsulate a
>      *              referemce to an Excel worksheet that held the cell 
> which
>      *              contained the WEEKDAY() formula
>      * @param serialNumber An instance of the String class that 
> encapsulates
>      *                     the value passed to the WEEKDAY() function's
>      *                     serial_number parameter. Currently, this is
> limited
>      *                     to the Excel functions TODAY() and DATE(), to a
> date
>      *                     String or to the address of a spreadsheet cell
> that
>      *                     itself contains a date.
>      * @param returnValue An instance of the String class that 
> encapsulates
>      *                    the value recovered from the WEEKDAY() 
> function's
>      *                    serial_number parameter
>      * @param serialSource A primitive int whose vaue indicates the
> 'flavour'
>      *                     of the WEEKDAY() function that is currently 
> being
>      *                     processed. The following four constants are
> provided
>      *                     = WeekdayFunction.CELL_ADDRESS
>      *                     = WeekdayFunction.DATE_FUNCTION
>      *                     = WeekdayFunction.DATE_STRING
>      *                     = WeekdayFunction.TODAY_FUNCTION
>      * @return A primitive int that will contain a correctly adjusted 
> value
>      *         indicating the number of the day of the week.
>      * @throws java.text.ParseException Thrown if an error occurs 
> converting
>      *                                  the String representation of a 
> date
> into
>      *                                  and actual Date object.
>      */
>     private static int getDayNumber(Workbook workbook, Sheet sheet,
>            String serialNumber, int returnValue, int serialSource)
>                                                throws ParseException {
>         CellReference cellRef = null;
>         Cell cell = null;
>         String[] dateParts = null;
>         int dayOfWeek = 0;
> 
>         // Test which 'flavour' of the WEEKDAY() function is being parsed.
>         switch(serialSource) {
> 
>             // If the source of the date is a cell in the workbook, then
> recover
>             // the contents of that cell, use them to create a date object
> and
>             // interrogate that for the day of the week.
>             case WeekdayFunction.CELL_ADDRESS:
>  
>                 // TO DO. Problems parsing the date again!!
> 
>                 break;
>             // As we are dealing with a DATE() function, the serialNumber
>             // parameter will already contain the methods arguments as a
> comma
>             // separated list. Convert these into the String 
> representation
> of
>             // a date, use that to set the time of the Calendar object and
>             // interrogate it for the day of the week.
>             case WeekdayFunction.DATE_FUNCTION:
>                 dateParts = serialNumber.split(",");
>                 serialNumber = dateParts[2] + "/" + dateParts[1] + "/" +
> dateParts[0];
>                 WeekdayFunction.calendar.setTime(
>  
> WeekdayFunction.shortDateFormatter.parse(serialNumber));
>                 dayOfWeek =
> WeekdayFunction.calendar.get(Calendar.DAY_OF_WEEK);
>                 break;
>             // Simply parse the date String to set the time of the 
> Calendar
>             // object and interrogate that for the day of the week.
>             case WeekdayFunction.DATE_STRING:
>                 WeekdayFunction.calendar.setTime(
>  
> WeekdayFunction.shortDateFormatter.parse(serialNumber));
>                 dayOfWeek =
> WeekdayFunction.calendar.get(Calendar.DAY_OF_WEEK);
>                 break;
>             // Set the time of the Calendar object to the current system
> time
>             // and interrogate it for the day of the week.
>             case WeekdayFunction.TODAY_FUNCTION:
>                 WeekdayFunction.calendar.setTime(shortDateFormatter.parse(
>                         WeekdayFunction.shortDateFormatter.format(
>                         new java.util.Date())));
>                 dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK);
>                 break;
>         }
> 
>         // Return the adjusted integer value for the day of the week.
>         return(WeekdayFunction.getExcelDayOfWeek(dayOfWeek, 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 static 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);
>     }
> 
>     /**
>      * Recover the parameters from the WEEKDAY() function if that 
> function's
>      * serial_number argument was provided by a DATE() function.
>      *
>      * @param functionString An instance of the String class encapsulating
> the
>      *                       arguments passed to the WEEKDAY() function.
>      * @return An array of type String whose elements encapsulate the two
>      *         arguments passed to the WEEKDAY() function. Note that if 
> the
>      *         return_value argument had been omited, it will be defaulted
> to
>      *         one.
>      */
>     private static String[] getWeekdayDateFunctionParameters(String
> functionString) {
> 
>         int index = 0;
>         String[] params = new String[2];
> 
>         // Firstly, extract the parameters for the DATE function.
>         functionString = functionString.substring(
>                 functionString.indexOf("(")).trim();
>         index = functionString.indexOf(")");
>         params[0] = functionString.substring(
>                 1, index).trim();
> 
>         // Then strip them away from the original function String and 
> check
>         // the length of what remains. If it's length is zero, no
>         // return_value was specified so default this to one.
>         functionString = functionString.substring(++index).trim();
>         if(functionString.length() == 0) {
>             params[1] = "1";
>         }
>         else {
>             // Else, if a return_value was specified, then strip this out
>             // of the original function String.
>             index = functionString.indexOf(",");
>             params[1] = functionString.substring(++index).trim();
>         }
>         return(params);
>     }
> 
>     /**
>      * Recover the parameters from the WEEKDAY() function if that 
> function's
>      * serial_number argument was provided by other than the DATE()
> function.
>      *
>      * @param functionString An instance of the String class encapsulating
> the
>      *                       arguments passed to the WEEKDAY() function.
>      * @return An array of type String whose elements encapsulate the two
>      *         arguments passed to the WEEKDAY() function. Note that if 
> the
>      *         return_value argument had been omited, it will be defaulted
> to
>      *         one.
>      */
>     private static String[] getWeekdayFunctionParameters(String
> functionString) {
>  
>         int index = 0;
>         String[] functionParams = new String[2];
> 
>         // If the comma is not found, then set a default value for the
>         // return_value parameter
>         if((index = functionString.lastIndexOf(",")) < 0) {
>             functionParams[0] = functionString;
>             functionParams[1] = "1";
>         }
> 
>         // If the comma separator is found, strip out the two paramneter
>         // values from the original function String.
>         else {
>             functionParams[0] = functionString.substring(0, index);
>             functionParams[1] = functionString.substring(++index);
>         }
>         return(functionParams);
>     }
> 
>     /**
>      * Determine the 'flavour' of WEEKDAY() function we are dealing with.
> This
>      * means determining whether the source for the date value is the
> TODAY()
>      * function, the DATE() function, a String or another cell.
>      *
>      * Note, this method only checks for three of the four options as the
> DATE()
>      * function option is detected in an earlier method.
>      *
>      * @param functionParams An instance of the String class that
> encapsulates
>      *                       the arguments passed to the WEEKDAY() 
> function.
>      * @return
>      */
>     private static int getSourceID(String[] functionParams) {
> 
>         int serialSource = 0;
> 
>         // Was the TODAY() function used to specify the date value?
>         if(functionParams[0].contains("TODAY()")) {
>             serialSource = WeekdayFunction.TODAY_FUNCTION;
>         }
> 
>         // The serial_number parameter may have been a date String. If 
> this
>         // is the case then the leading and trailing quotes must be 
> removed.
>         // Also, set the serialSource variable to reflect the fact that a
>         // date String was used to declare the date for the WEEKDAY
> function.
>         else if(functionParams[0].startsWith("\"")) {
>             functionParams[0] = functionParams[0].substring(1);
>             functionParams[0] = functionParams[0].substring(
>                     0, (functionParams[0].length() - 1));
>             serialSource = WeekdayFunction.DATE_STRING;
>         }
> 
>         // A cell address is the only other option.
>         else {
>             serialSource = WeekdayFunction.CELL_ADDRESS;
>         }
> 
>         return(serialSource);
>     }
> }
> 
> You can use the class a little like this;
> 
>     public static void displayCellContents(String filename) {
>         File file = null;
>         FileInputStream fis = null;
>         Workbook workbook = null;
>         Sheet sheet = null;
>         Row row = null;
>         Cell cell = null;
>         int numSheets = 0;
>         Iterator<Row> rowIter = null;
>         Iterator<Cell> cellIter = null;
>         try {
>             // Open the workbook.
>             file = new File(filename);
>             fis = new FileInputStream(file);
>             workbook = WorkbookFactory.create(fis);
> 
>             // Get the number of sheets in the workbook and enter a for 
> loop
>             // to iterate through them one at a time.
>             numSheets = workbook.getNumberOfSheets();
>             for(int i = 0; i < numSheets; i++) {
> 
>                 // Get the sheet and recover from that an iterator that
>                 // allows us to step through all of the rows the sheet
> contains.
>                 sheet = workbook.getSheetAt(i);
> 
>                 rowIter = sheet.rowIterator();
>                 while(rowIter.hasNext()) {
> 
>                     // Get a row and recover from it an Iterator that 
> allows
>                     // us to access each of the cells on the row.
>                     row = rowIter.next();
>                     cellIter = row.cellIterator();
>                     while(cellIter.hasNext()) {
> 
>                         // Get a cell
>                         cell = cellIter.next();
> 
>                         switch(cell.getCellType()) {
>                             case Cell.CELL_TYPE_BLANK:
>                                 System.out.println("is blank.");
>                                 break;
>                             case Cell.CELL_TYPE_BOOLEAN:
>                                 System.out.println("contains a boolean
> value.);
>                                 break;
>                             case Cell.CELL_TYPE_ERROR:
>                                 System.out.println("contains an error
> code.);
>                                 break;
>                             case Cell.CELL_TYPE_FORMULA:
>                                 System.out.println("contains a formula.);
>                                 String formulaStr = cell.getCellFormula();
>                                 if(formulaStr.contains("WEEKDAY")) {
>                                     try {
>  
> System.out.println(WeekdayFunction.getWeekday(
>                                                 workbook, sheet,
> formulaStr));
>                                     }
>                                     catch(Exception ex) {
>                                         // Handle exception.
>                                     }
>                                 }
>                                 break;
>                             case Cell.CELL_TYPE_NUMERIC:
> 
>                                 if(DateUtil.isCellDateFormatted(cell)) {
>                                     System.out.println("contains a 
> date.");
>                                 }
>                                 else {
>                                     System.out.println("contains a
> number.");
>                                 }
>                                 break;
>                             case Cell.CELL_TYPE_STRING:
>                                 System.out.println("contains a String.");
>                                 break;
>                         }
>                     }
>                 }
>             }
>         }
>         catch(FileNotFoundException fnfEx) {
>             System.out.println("Caught: " + fnfEx.getClass().getName());
>             System.out.println("Message: " + fnfEx.getMessage());
>             System.out.println("Stacktrace follows..............");
>             fnfEx.printStackTrace(System.out);
>         }
>         catch(IOException ioEx) {
>             System.out.println("Caught: " + ioEx.getClass().getName());
>             System.out.println("Message: " + ioEx.getMessage());
>             System.out.println("Stacktrace follows..............");
>             ioEx.printStackTrace(System.out);
>         }
>         catch(InvalidFormatException invFEx) {
>             System.out.println("Caught: " + invFEx.getClass().getName());
>             System.out.println("Message: " + invFEx.getMessage());
>             System.out.println("Stacktrace follows..............");
>             invFEx.printStackTrace(System.out);
>         }
>         finally {
>             if(fis != null) {
>                 try {
>                     fis.close();
>                     fis = null;
>                 }
>                 catch(IOException ioEx) {
>                     // I G N O R E
>                 }
>             }
>         }
> 
> Try not to be too critical of the code, it is still very much a work in
> progress, needing to be refactored and tidied in other ways. Remember 
> please
> that this is still test code and even though I believe it does yield the
> correct results, you MUST test it throughly yourself.
> 
> If I make any more progress, I will post again.
> 
> 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-tp25279402p25325709.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-tp25279402p25399231.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