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 Tue, 08 Sep 2009 14:23:52 GMT

Thanks for you message Vijay and I would appreciate it if you would let me
know how the class works for you. Must admit that I am already working on an
'improved' version that uses regular expressions to identify which sort of
WEEKDAY() function is being parsed and that promises to be very, very much
neater. I will post the code as soon as I have made sufficient progress.

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-tp25279402p25347250.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