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 <[email protected]> > 07/09/2009 07:46 > Please respond to > "POI Users List" <[email protected]> > > > To > [email protected] > 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 <[email protected]> >> 04/09/2009 10:12 >> Please respond to >> "POI Users List" <[email protected]> >> >> >> To >> [email protected] >> 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: [email protected] >> For additional commands, e-mail: [email protected] >> >> >> >> >> >> --- >> >> 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: [email protected] > For additional commands, e-mail: [email protected] > > > > > > --- > > 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: [email protected] For additional commands, e-mail: [email protected]
