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 Mon, 07 Sep 2009 06:46:32 GMT

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


Mime
View raw message