CY2_SPREADSHEET:Workbook.pcode

<* 
MIT License

Copyright (c) 2018-2023
CY2 IT Services - https://cy2.nl

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE. 
*>

/* CY2 MC 17-12-2022 - reuse cell style */
/* CY2 ELH 06-06-2023 - check XLSX files */

class Workbook
   /**
    * Workbook constructor
    *
    * @param p_file The full path and file name of the Excel file to open or create.
    * @exception An exception thrown if the file path does not exist or cannot be created.
    */
   method Workbook(&p_filePath As string);
   
   /**
    * Creates a worksheet in the workbook with the given name and makes it the
    * active sheet. A sheet with that name already exists, it becomes active.
    * Sheets are modified to meet Excel sheet name requirements.
    * 
    * @param name The name of the worksheet to create
    */
   method CreateSheet(&name As string);
   
   /** 
     * Saves the Excel file and creates the file if it doesn't exist.
     */
   method Save();
   
   /**
    * Set the value of the given cell to the given string value. If the row or
    * column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param string The string value to set the cell to.
    */
   method SetCellString(&row As integer, &column As integer, &string As string);
   
   /**
    * Set the value of the given cell to the given numeric value. If the row or
    * column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param value The number to set the cell to.
    */
   method SetCellNumber(&row As integer, &column As integer, &value As number);
   
   /**
    * Set the value of the given cell to the given numeric value with the given Excel format. 
    * If the row or column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param number The number to set the cell to.
    * @param format The Excel format to apply to the number.
    */
   method SetCellNumberFormat(&row As integer, &column As integer, &value As number, &format As string);
   
   /**
    * Set the value of the given cell to the given date value. The DateTime is formatted as
    * dd/mm/yyyy hh:mm:ss .If the row or column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param dateTime The DateTime value to set the cell to.
    */
   method SetCellDateTime(&row As integer, &column As integer, &dateTime As datetime);
   
   /**
    * Set the value of the given cell to the given date value. The Date is formatted as
    * dd/mm/yyyy .If the row or column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the ceanll to set. It must be a positive integer.
    * @param dateTime The Date value to set the cell to
    */
   method SetCellDate(&row As integer, &column As integer, &date As date);
   
   /**
    * Set the value of the given cell to the given Boolean value. 
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param dateTime The Boolean value to set the cell to
    */
   method SetCellBoolean(&row As integer, &column As integer, &boolean As boolean);
   
   rem  method SetCellDateTimeFormated(&row As integer, &column As integer, &date As datetime, &locale As string, &timezone As string, &format As string);
   rem  method SetCellDateFormated(&row As integer, &column As integer, &date As datetime, &locale As string, &timezone As string, &format As string);
   
   /**
    * Set the value of the given cell to the given formula value. If the row or
    * column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param formula The formula
    */
   method SetCellFormula(&row As integer, &column As integer, &formula As string);
   
   /**
    * Set the value of the given cell to the given hyperlink. If the row or
    * column number is not positive, nothing is done.
    *
    * @param row The row number of the cell to set. It must be a positive integer.
    * @param column The column number of the cell to set. It must be a positive integer.
    * @param text The text value of the cell.
    * @param column The URL the link points to.
    */
   method SetCellHyperlink(&row As integer, &column As integer, &text As string, &url As string);
   
   /**
     * Returns the value of the requested cell as a string. 
     * If the cell does not exist (or an invalid cell location is given)
     * an empty string is returned.
     * 
     * @param row The row number of the cell. It must be a positive integer.
     * @param column The column number of the cell. It must be a positive integer.
     * @return String - the string value of the cell. If the cell is not a string cell
     * the value is converted into string.
     */
   method GetCellString(&row As integer, &column As integer) Returns string;
   
   /**
     * Retrieves the numeric value of a cell and places it value into the out parameter &value. 
     * 
     * @param row The row number of the cell. It must be a positive integer.
     * @param column The column number of the cell. It must be a positive integer.
     * @param value The numeric value of a cell if it contains a numeric value or if cell
     * value can be conveted to a number.
     * @return Boolean - a numeric value was successfully retrieved.
     */
   method GetCellNumber(&row As integer, &column As integer, &value As number out) Returns boolean;
   
   /**
     * Retrieves the Boolean value of a cell and places it value into the out parameter &value. 
     *
     * @param row The row number of the cell. It must be a positive integer.
     * @param column The column number of the cell. It must be a positive integer.
     * @param value The Boolean value of a cell if it contains a Boolean value or if cell
     * value can be conveted to a Boolean.
     * @return Boolean - a numeric value was successfully retrieved.
     */
   method GetCellBoolean(&row As integer, &column As integer, &value As boolean out) Returns boolean;
   
   /**
     * Retrieves the DateTime value of a cell and places it value into the out parameter &value. 
     * 
     * @param row The row number of the cell. It must be a positive integer.
     * @param column The column number of the cell. It must be a positive integer.
     * @param value The DataTime value of a cell if it contains a date or date/time value or if cell
     * value can be conveted to a DateTime.
     * @return Boolean - a DateTime value was successfully retrieved.
     */
   method GetCellDateTime(&row As integer, &column As integer, &value As datetime out) Returns boolean;
   
   /**
     * Rename a worksheet. If the requested worksheet does not exist, nothing happend.
     *
     * @param oldName The name of the old worksheet to rename
     * @param newName The new name for the worksheet
     */
   method RenameSheet(&oldName As string, &newName As string);
   
   /**
     * Rename the current worksheet. 
     *
     * @param oldName The name of the old worksheet to rename
     * @param newName The new name for the worksheet
     */
   method RenameCurrentSheet(&newName As string);
   
   /**
     * The requested worksheet becomes the current worksheet.
     * If the requested worksheet does not exist, nothing happend.
     * 
     * @param name The name of the worksheet to select
     */
   method SelectSheet(&name As string);
   
   /** 
     * Deletes/removes a row from the active worksheet.
     *
     * @param row The row to delete
     */
   method DeleteRow(&row As integer);
   
   /** 
     * Clears the data from the requested row, does not remove it, 
     * from the active worksheet
     *
     * @param row The row to clear
     */
   method ClearRow(&row As integer);
   
   /** 
     * Moves a group of rows up or down by the given the given number, 
     * from the active worksheet
     *
     * @param &startRow The starting row (inclusive) of the group
     * @param &endRow The ending row (inclusive) of the group
     * @param &numberOfRows The rows to shift the group. To shift the group up
     * a negative number should be used.
     */
   method ShiftRows(&startRow As integer, &endRow As integer, &numberOfRows As integer);
   
   /** 
     * Save workbook to the given path. 
     *
     * @param &path The full path to save the workbook to.
     */
   method SaveAs(&path As string);
   
   /** Close the workbook and clean up and temp resources */
   method Close();
   
   /* Default Formats */
   /* Defaults to "dd/MM/yyyy HH:mm:ss" */
   property string DateTimeFormat;
   
   /* Adjust date's day value in accordance to local timezone */
   /* Defaults to False for backward compatibility */
   property boolean LocalTimezoneAdjustment;
   
private
   
   Constant &NumericCell = "NUMERIC";
   Constant &StringCell = "STRING";
   Constant &FormulaCell = "FORMULA";
   Constant &BlankCell = "BLANK";
   Constant &NoneCell = "_NONE";
   Constant &BooleanCell = "BOOLEAN";
   Constant &ErrorCell = "ERROR";
   
   method GetCell(&row As integer, &column As integer) Returns JavaObject;
   method GetSheet(&sheetName As string) Returns JavaObject;
   method GetSheetAt(&index As integer) Returns JavaObject;
   method CreateNewSheet(&sheetName As string) Returns JavaObject;
   method GetRow(&index As integer) Returns JavaObject;
   method CreateRow(&index As integer) Returns JavaObject;
   method GetSheetCell(&sheetRow As JavaObject, &index As integer) Returns JavaObject;
   method CreateSheetCell(&sheetRow As JavaObject, &index As integer) Returns JavaObject;
   method CreateCellStyle() Returns JavaObject;
   method GetCellStyle(&cell As JavaObject) Returns JavaObject;
   method CreateDataFormat() Returns JavaObject;
   method CreateFont() Returns JavaObject;
   method CreateHyperlink(&type As JavaObject) Returns JavaObject;
   method SetDataFormat(&cellStyle As JavaObject, &dataFormat As integer);
   method CreateWorkbook(&inputSream As JavaObject) Returns JavaObject;
   
   
   /** 
     * Adjust date day value in accordance to local timezone 
     * Apache POI uses the server timezone to transform date values to string values 
     * method adjust the day value in a date in case its off by 1 day due to server timezone issues
     *
     * @param &dttm the time that should be adjusted against local timezone*/
   method ApplyLocalTimezoneAdjustment(&dttm As datetime) Returns datetime;
   
   instance JavaObject &_workbook;
   instance JavaObject &_workBookUtils;
   instance JavaObject &_activeSheet;
   instance JavaObject &_creationHelper;
   instance JavaObject &_fontClass;
   instance JavaObject &_indexedColors;
   instance JavaObject &_dateUtil;
   instance JavaObject &_createSheetMethod;
   instance JavaObject &_getSheetMethod;
   instance JavaObject &_getSheetAtMethod;
   instance JavaObject &_getRowMethod;
   instance JavaObject &_createRowMethod;
   instance JavaObject &_getCellMethod;
   instance JavaObject &_createCellStyleMethod;
   instance JavaObject &_createCellMethod;
   instance JavaObject &_getCellStyleMethod;
   instance JavaObject &_setDataFormatMethod;
   instance JavaObject &_createFontMethod;
   instance JavaObject &_createHyperlinkMethod;
   instance JavaObject &_dataFormat;
   instance JavaObject &_workbookClass;
   instance string &_filePath;
   
   instance JavaObject &_dateStyle;
   instance JavaObject &_dateTimeStyle;
   instance JavaObject &_cellStyle;
end-class;


method Workbook
   /+ &p_filePath as String +/
   &_filePath = &p_filePath;
   
   /* the class supports XLSX files only so we validate the file name */
   Local array of string &split = Split(&_filePath, ".");
   Local string &ext = &split [&split.Len];
   
   /* CY2 ELH 06-06-2023 start */
   If Upper(&ext) <> "XLSX" Then
      Error MsgGetText(0, 0, "Your file has the wrong extension: %1, the program only support XLSX files", &ext);
   Else
      /* CY2 ELH 06-06-2023 end */
      Local JavaObject &inputSream;
      Local JavaObject &workbookFactory = GetJavaClass("org.apache.poi.xssf.usermodel.XSSFWorkbookFactory");
      
      If Not FileExists(&_filePath, %FilePath_Absolute) Then
         /* this is a temp hack until we find a better way than this or using reflection... 
         There is a glitch with PS not being able to find the XSSFWorkbook methods (cannot find override)
         unless we use reflection. so, we'll create an empty file and load it back, 
         to we have a type of Workbook. 
         note, casting did not solve this issue (also problem here with PS so we have to use
            &_workbook.getClass().getClassLoader() otherwise we get a class not found exception.
            maybe because Workbook and XSSFWorkbook are different jar files?
            Local JavaObject &class = GetJavaClass("java.lang.Class").forName("org.apache.poi.ss.usermodel.Workbook", True, &workbook.getClass().getClassLoader());
            &_workbook = &class.cast(&workbook);
       */
         Local JavaObject &workbook = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbook");
         Local JavaObject &class = &workbook.getClass();
         Local JavaObject &javaString = CreateJavaObject("java.lang.String", "Sheet1");
         Local JavaObject &stringType = CreateJavaObject("java.lang.Class[]", &javaString.getClass());
         Local JavaObject &createSheet = &class.getDeclaredMethod("createSheet", &stringType);
         Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &javaString);
         &createSheet.invoke(&workbook, &args);
         
         Local JavaObject &outStream = CreateJavaObject("java.io.FileOutputStream", &_filePath);
         &workbook.write(&outStream);
         &outStream.close();
         &workbook = Null;
         &class = Null;
      End-If;
      
      &inputSream = CreateJavaObject("java.io.FileInputStream", &_filePath);
      rem Local JavaObject &createWorkbook = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbookFactory").getClass().getDeclaredMethod("createWorkbook", CreateJavaObject("java.lang.Class[]", GetJavaClass("java.lang.Class").forName("java.io.InputStream")));
      rem &_workbook = &createWorkbook.invoke(&workbookFactory, CreateJavaObject("java.lang.Object[]", &inputSream));
      rem &_workbook = &_workbook.getClass().cast(&_workbook);
      
      try
         /* Apache POI Pre-5.0 way of creating worbook */
         &_workbook = &workbookFactory.createWorkbook(&inputSream);
      catch Exception &ex
         /* Apache POI 5.0 way of creating worbook */
         &_workbook = %This.CreateWorkbook(&inputSream);
      end-try;
      
      &_workbookClass = &_workbook.getClass();
      
      &_activeSheet = %This.GetSheetAt(&_workbook.getActiveSheetIndex());
      
      &_workBookUtils = GetJavaClass("org.apache.poi.ss.util.WorkbookUtil");
      Local JavaObject &getCreationHelper = &_workbookClass.getDeclaredMethod("getCreationHelper", CreateJavaObject("java.lang.Class[]"));
      &_creationHelper = &getCreationHelper.invoke(&_workbook, CreateJavaObject("java.lang.Object[]"));
      &_creationHelper = &_creationHelper.getClass().cast(&_creationHelper);
      &_dataFormat = %This.CreateDataFormat();
      
      &_fontClass = GetJavaClass("org.apache.poi.ss.usermodel.Font");
      &_indexedColors = GetJavaClass("org.apache.poi.ss.usermodel.IndexedColors");
      &_dateUtil = GetJavaClass("org.apache.poi.ss.usermodel.DateUtil");
      
      &DateTimeFormat = "dd/MM/yyyy HH:mm:ss";
      &LocalTimezoneAdjustment = False;
      
      &_cellStyle = %This.CreateCellStyle();
      
      /* CY2 ELH 06-06-2023 start */
   End-If;
   /* CY2 ELH 06-06-2023 end */
end-method;

method CreateSheet
   /+ &name as String +/
   
   Local string &cleanName = &_workBookUtils.createSafeSheetName(&name);
   Local JavaObject &sheet = %This.GetSheet(&cleanName);
   
   If &sheet = Null Then
      &_activeSheet = %This.CreateNewSheet(&cleanName);
   Else
      &_activeSheet = &sheet;
   End-If;
   
   &_workbook.setActiveSheet(&_workbook.getSheetIndex(&cleanName));
   
end-method;

method Save
   Local JavaObject &outStream = CreateJavaObject("java.io.FileOutputStream", &_filePath);
   &_workbook.write(&outStream);
   &outStream.close();
end-method;

method SetCellString
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &string as String +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   
   &cell.setCellValue(&string);
end-method;

method SetCellDate
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &date as Date +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   Local datetime &dateTime = DateTime6(Year(&date), Month(&date), Day(&date), 0, 0, 0);
   &cell.setCellValue(&dateTime);
   
   If &_dateStyle = Null Then
      &_dateStyle = %This.CreateCellStyle();
      %This.SetDataFormat(&_dateStyle, &_dataFormat.getFormat("dd/mm/yyyy"));
   End-If;
   &cell.setCellStyle(&_dateStyle);
   
end-method;

method SetCellDateTime
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &dateTime as DateTime +/
   
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   &cell.setCellValue(&dateTime);
   
   If &_dateTimeStyle = Null Then
      &_dateTimeStyle = %This.CreateCellStyle();
      %This.SetDataFormat(&_dateTimeStyle, &_dataFormat.getFormat("dd/mm/yyyy hh:mm:ss"));
   End-If;
   &cell.setCellStyle(&_dateTimeStyle);
   
end-method;

method SetCellFormula
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &formula as String +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   Local string &first = Substring(&formula, 1, 1);
   
   /* formulas cannot start with an equal sign */
   If &first = "=" Then
      &formula = Substring(&formula, 2, Len(&formula) - 1);
   End-If;
   
   &cell.setCellFormula(&formula);
end-method;

method SetCellNumber
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &value as Number +/
   %This.SetCellNumberFormat(&row, &column, &value, "");
end-method;

method SetCellNumberFormat
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &value as Number, +/
   /+ &format as String +/
   
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   &cell.setCellValue(Float(&value));
   If &format <> "" Then
      
      %This.SetDataFormat(&_cellStyle, &_dataFormat.getFormat(&format));
      &cell.setCellStyle(&_cellStyle);
      
   End-If;
end-method;

method SetCellHyperlink
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &text as String, +/
   /+ &url as String +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   
   &cell.setCellValue(&text);
   Local JavaObject &style = %This.CreateCellStyle();
   Local JavaObject &font = %This.CreateFont();
   &font.setUnderline(&_fontClass.U_SINGLE);
   &font.setColor(&_indexedColors.BLUE.getIndex());
   &style.setFont(&font);
   
   /* depending on the tools version, the version of createHyperlink is different (differnt POI version) 
      need to figure out a better way here
   */
   Local JavaObject &link;
   try
      &link = &_creationHelper.createHyperlink(1);
   catch Exception &exp
      Local JavaObject &hyperlinkClass = GetJavaClass("org.apache.poi.common.usermodel.HyperlinkType");
      &link = %This.CreateHyperlink(&hyperlinkClass.URL);
   end-try;
   &link.setAddress(&url);
   &cell.setHyperlink(&link);
   &cell.setCellStyle(&style);
   
end-method;

method SetCellBoolean
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &boolean as Boolean +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return;
   End-If;
   
   &cell.setCellValue(&boolean);
   
end-method;

method GetCellString
   /+ &row as Integer, +/
   /+ &column as Integer +/
   /+ Returns String +/
   
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return "";
   End-If;
   
   Local boolean &success = False;
   
   Evaluate &cell.getCellType().name()
   When &NumericCell
      If &_dateUtil.isCellDateFormatted(&cell) Then
         Return DateTimeToLocalizedString(%This.ApplyLocalTimezoneAdjustment(&cell.getDateCellValue()), &DateTimeFormat);
      Else
         Return RTrim(RTrim(String(&cell.getNumericCellValue()), "0"), ".");
      End-If;
      Break;
      
   When &StringCell
      Return LTrim(RTrim(&cell.getStringCellValue()));
      Break;
      /* formula cell, returns formula not the resolved value */
      
   When &FormulaCell
      /*Excel stores two objects for the cell when a formula calculates its value. 
		One is the formula itself, and the second is the cached value. 
		The cached value contains the last value evaluated by the formula.

		So the idea here is we can fetch the last cached value and consider it as cell value. 
		It may not always be true that the last cached value is the correct cell value.
		However, when we're working with an Excel file that is saved, and there are no recent modifications to the file, 
		then the last cached value should be the cell value.*/
      
      Evaluate &cell.getCachedFormulaResultType().name()
      When &NumericCell
         If &_dateUtil.isCellDateFormatted(&cell) Then
            Return DateTimeToLocalizedString(%This.ApplyLocalTimezoneAdjustment(&cell.getDateCellValue()), &DateTimeFormat);
         Else
            Return RTrim(RTrim(String(&cell.getNumericCellValue()), "0"), ".");
         End-If;
         Break;
      When &StringCell
         Return LTrim(RTrim(&cell.getStringCellValue()));
         Break;
      When &BooleanCell
         If &cell.getBooleanCellValue() Then
            Return "TRUE"
         Else
            Return "FALSE";
         End-If;
         Break;
      When &ErrorCell
         Return "error: " | String(&cell.getErrorCellValue());
         Break;
      When-Other
         Return "";
         Break;
      End-Evaluate;
      
      Break;
      
   When &BooleanCell
      If &cell.getBooleanCellValue() Then
         Return "TRUE"
      Else
         Return "FALSE";
      End-If;
      Break;
      
   When &ErrorCell
      Return "error: " | String(&cell.getErrorCellValue());
      Break;
      
      /* blank or null cell */
   When-Other
      Return "";
   End-Evaluate
end-method;


method GetCellNumber
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &value as Number out +/
   /+ Returns Boolean +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return False;
   End-If;
   
   Local boolean &success = False;
   Evaluate &cell.getCellType().name()
   When &NumericCell
      &value = &cell.getNumericCellValue();
      &success = True;
      Break;
      
   When &StringCell
      try
         &value = Value(&cell.getStringCellValue());
         &success = True;
      catch Exception &exp
      end-try;
      Break;
   End-Evaluate;
   
   Return &success;
end-method;

method GetCellBoolean
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &value as Boolean out +/
   /+ Returns Boolean +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return False;
   End-If;
   
   Local boolean &success = False;
   Evaluate &cell.getCellType().name()
   When &BooleanCell
      &value = &cell.getBooleanCellValue();
      &success = True;
      Break;
   When &NumericCell
      Local number &number = &cell.getNumericCellValue();
      If &number = 1 Then
         &value = True;
         &success = True;
      End-If;
      If &number = 0 Then
         &value = False;
         &success = True;
      End-If;
      
   When &StringCell
      try
         Local string &string = LTrim(RTrim(Upper(&cell.getStringCellValue())));
         If &string = "TRUE" Or
               &string = "Y" Or
               &string = "1" Then
            &value = True;
            &success = True;
         End-If;
         If &string = "FALSE" Or
               &string = "N" Or
               &string = "0" Then
            &value = False;
            &success = True;
         End-If;
      catch Exception &exp
      end-try;
      Break;
   End-Evaluate;
   
   Return &success;
end-method;

method GetCellDateTime
   /+ &row as Integer, +/
   /+ &column as Integer, +/
   /+ &value as DateTime out +/
   /+ Returns Boolean +/
   Local JavaObject &cell = %This.GetCell(&row, &column);
   If &cell = Null Then
      Return False;
   End-If;
   
   Local boolean &success = False;
   Evaluate &cell.getCellType().name()
   When &NumericCell
      &value = &cell.getDateCellValue();
      &success = True;
      Break;
   When &StringCell
      try
         &value = DateTimeValue(&cell.getStringValue());
         &success = True;
      catch Exception &exp
      end-try;
      Break;
   End-Evaluate;
   
   /* Adjust the DateTime according to the local timezone, if requested */
   &value = %This.ApplyLocalTimezoneAdjustment(&value);
   
   Return &success;
end-method;

method GetCell
   /+ &row as Integer, +/
   /+ &column as Integer +/
   /+ Returns JavaObject +/
   If &row < 1 Or
         &column < 1 Or
         &column > 1048576 Then
      Return Null;
   End-If;
   
   Local JavaObject &sheetRow = %This.GetRow(&row - 1);
   If &sheetRow = Null Then
      &sheetRow = %This.CreateRow(&row - 1);
   End-If;
   
   Local JavaObject &cell = %This.GetSheetCell(&sheetRow, &column - 1);
   If &cell = Null Then
      &cell = %This.CreateSheetCell(&sheetRow, &column - 1);
   End-If;
   
   Return &cell;
end-method;

method RenameSheet
   /+ &oldName as String, +/
   /+ &newName as String +/;
   
   Local string &cleanOldName = &_workBookUtils.createSafeSheetName(&oldName);
   Local integer &index = &_workbook.getSheetIndex(&cleanOldName);
   If &index >= 0 Then
      Local string &cleanNewName = &_workBookUtils.createSafeSheetName(&newName);
      &_workbook.setSheetName(&index, &cleanNewName);
   End-If;
end-method;

method SelectSheet
   /+ &name as String +/
   Local string &cleanName = &_workBookUtils.createSafeSheetName(&name);
   Local integer &index = &_workbook.getSheetIndex(&name);
   If &index >= 0 Then
      &_activeSheet = %This.GetSheetAt(&index);
      
      &_workbook.setActiveSheet(&index);
   End-If;
   
end-method;

method RenameCurrentSheet
   /+ &newName as String +/
   %This.RenameSheet(&_activeSheet.getSheetName(), &newName);
end-method;

method ClearRow
   /+ &row as Integer +/
   If &row < 1 Or
         &row >= &_activeSheet.getLastRowNum() Then
      Return;
   End-If;
   
   &_activeSheet.removeRow(%This.GetRow(&row - 1));
end-method;

method ShiftRows
   /+ &startRow as Integer, +/
   /+ &endRow as Integer, +/
   /+ &numberOfRows as Integer +/
   If &startRow < 1 Or
         &startRow >= &_activeSheet.getLastRowNum() Then
      Return;
   End-If;
   
   If &endRow < 1 Or
         &endRow >= &_activeSheet.getLastRowNum() Then
      Return;
   End-If;
   
   If &endRow <= &startRow Then
      Return;
   End-If;
   
   &_activeSheet.shiftRows(&startRow - 1, &endRow - 1, &numberOfRows);
end-method;

method DeleteRow
   /+ &row as Integer +/
   If &row < 1 Or
         &row > &_activeSheet.getLastRowNum() Then
      Return;
   End-If;
   
   &_activeSheet.shiftRows(&row, &_activeSheet.getLastRowNum(), - 1);
end-method;

method SaveAs
   /+ &path as String +/
   Local JavaObject &outStream = CreateJavaObject("java.io.FileOutputStream", &path);
   &_workbook.write(&outStream);
   &outStream.close();
end-method;

method Close
   &_workbook.close();
end-method;

method ApplyLocalTimezoneAdjustment
   /+ &dttm as DateTime +/
   /+ Returns DateTime +/
   
   Local boolean &before;
   Local datetime &newDttm = &dttm;
   
   /* Apply the adjustment only if desred */
   If %This.LocalTimezoneAdjustment Then
      /* The date is the previous day, the timezone offset is negative */
      &before = (Substring(TimeZoneOffset(&dttm), 1, 1) = "-");
      
      If &before Then
         /* We are the previous day so add one, trimming off any hours, minutes and seconds info */
         &newDttm = AddToDateTime(DateTime6(Year(&dttm), Month(&dttm), Day(&dttm), 0, 0, 0), 0, 0, 1, 0, 0, 0);
      End-If;
   End-If;
   
   Return &newDttm;
end-method;

method CreateNewSheet
   /+ &sheetName as String +/
   /+ Returns JavaObject +/
   
   If &_createSheetMethod = Null Then
      Local JavaObject &javaString = CreateJavaObject("java.lang.String", &sheetName);
      Local JavaObject &stringType = CreateJavaObject("java.lang.Class[]", &javaString.getClass());
      &_createSheetMethod = &_workbookClass.getDeclaredMethod("createSheet", &stringType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &sheetName);
   Local JavaObject &newSheet = &_createSheetMethod.invoke(&_workbook, &args);
   
   Return &newSheet;
end-method;

method GetSheet
   /+ &sheetName as String +/
   /+ Returns JavaObject +/
   
   If &_getSheetMethod = Null Then
      Local JavaObject &javaString = CreateJavaObject("java.lang.String", &sheetName);
      Local JavaObject &stringType = CreateJavaObject("java.lang.Class[]", &javaString.getClass());
      &_getSheetMethod = &_workbookClass.getDeclaredMethod("getSheet", &stringType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &sheetName);
   Local JavaObject &sheet = &_getSheetMethod.invoke(&_workbook, &args);
   
   If &sheet = Null Then
      Return Null;
   Else
      Return &sheet.getClass().cast(&sheet);
   End-If;
end-method;

method GetSheetAt
   /+ &index as Integer +/
   /+ Returns JavaObject +/
   If &_getSheetAtMethod = Null Then
      Local JavaObject &javaInteger = CreateJavaObject("java.lang.Integer", 1);
      Local JavaObject &intType = CreateJavaObject("java.lang.Class[]", &javaInteger.TYPE);
      &_getSheetAtMethod = &_workbookClass.getDeclaredMethod("getSheetAt", &intType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &index);
   Local JavaObject &sheet = &_getSheetAtMethod.invoke(&_workbook, &args);
   If &sheet = Null Then
      Return Null;
   Else
      Return &sheet.getClass().cast(&sheet);
   End-If;
   
end-method;

method GetRow
   /+ &index as Integer +/
   /+ Returns JavaObject +/
   If &_getRowMethod = Null Then
      Local JavaObject &javaInteger = CreateJavaObject("java.lang.Integer", 1);
      Local JavaObject &intType = CreateJavaObject("java.lang.Class[]", &javaInteger.TYPE);
      &_getRowMethod = &_activeSheet.getClass().getDeclaredMethod("getRow", &intType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &index);
   Local JavaObject &sheetRow = &_getRowMethod.invoke(&_activeSheet, &args);
   If &sheetRow = Null Then
      Return Null;
   Else
      Return &sheetRow.getClass().cast(&sheetRow);
   End-If;
end-method;

method CreateRow
   /+ &index as Integer +/
   /+ Returns JavaObject +/
   If &_createRowMethod = Null Then
      Local JavaObject &javaInteger = CreateJavaObject("java.lang.Integer", 1);
      Local JavaObject &intType = CreateJavaObject("java.lang.Class[]", &javaInteger.TYPE);
      &_createRowMethod = &_activeSheet.getClass().getDeclaredMethod("createRow", &intType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &index);
   Local JavaObject &sheetRow = &_createRowMethod.invoke(&_activeSheet, &args);
   If &sheetRow = Null Then
      Return Null;
   Else
      Return &sheetRow.getClass().cast(&sheetRow);
   End-If;
end-method;


method GetSheetCell
   /+ &sheetRow as JavaObject, +/
   /+ &index as Integer +/
   /+ Returns JavaObject +/
   If &_getCellMethod = Null Then
      Local JavaObject &javaInteger = CreateJavaObject("java.lang.Integer", 1);
      Local JavaObject &intType = CreateJavaObject("java.lang.Class[]", &javaInteger.TYPE);
      &_getCellMethod = &sheetRow.getClass().getDeclaredMethod("getCell", &intType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &index);
   Local JavaObject &cell = &_getCellMethod.invoke(&sheetRow, &args);
   If &cell = Null Then
      Return Null;
   Else
      Return &cell.getClass().cast(&cell);
   End-If;
end-method;

method CreateSheetCell
   /+ &sheetRow as JavaObject, +/
   /+ &index as Integer +/
   /+ Returns JavaObject +/
   If &_createCellMethod = Null Then
      Local JavaObject &javaInteger = CreateJavaObject("java.lang.Integer", 1);
      Local JavaObject &intType = CreateJavaObject("java.lang.Class[]", &javaInteger.TYPE);
      &_createCellMethod = &sheetRow.getClass().getDeclaredMethod("createCell", &intType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &index);
   Local JavaObject &cell = &_createCellMethod.invoke(&sheetRow, &args);
   If &cell = Null Then
      Return Null;
   Else
      Return &cell.getClass().cast(&cell);
   End-If;
end-method;

method CreateCellStyle
   /+ Returns JavaObject +/
   If &_createCellStyleMethod = Null Then
      &_createCellStyleMethod = &_workbookClass.getDeclaredMethod("createCellStyle", CreateJavaObject("java.lang.Class[]"));
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]");
   Local JavaObject &style = &_createCellStyleMethod.invoke(&_workbook, &args);
   Return &style.getClass().cast(&style);
end-method;

method CreateFont
   /+ Returns JavaObject +/
   If &_createFontMethod = Null Then
      &_createFontMethod = &_workbookClass.getDeclaredMethod("createFont", CreateJavaObject("java.lang.Class[]"));
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]");
   Local JavaObject &font = &_createFontMethod.invoke(&_workbook, &args);
   Return &font.getClass().cast(&font);
end-method;

method GetCellStyle
   /+ &cell as JavaObject +/
   /+ Returns JavaObject +/
   If &_getCellStyleMethod = Null Then
      &_getCellStyleMethod = &cell.getClass().getDeclaredMethod("getCellStyle", CreateJavaObject("java.lang.Class[]"));
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]");
   Local JavaObject &style = &_getCellStyleMethod.invoke(&cell, &args);
   Return &style.getClass().cast(&style);
end-method;

method CreateDataFormat
   /+ Returns JavaObject +/
   Local JavaObject &method = &_creationHelper.getClass().getDeclaredMethod("createDataFormat", CreateJavaObject("java.lang.Class[]"));
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]");
   Local JavaObject &dataFormat = &method.invoke(&_creationHelper, &args);
   Return &dataFormat.getClass().cast(&dataFormat);
end-method;

method SetDataFormat
   /+ &cellStyle as JavaObject, +/
   /+ &dataFormat as Integer +/
   Local JavaObject &javaShort = CreateJavaObject("java.lang.Short", &dataFormat);
   Local JavaObject &shortType = CreateJavaObject("java.lang.Class[]", &javaShort.TYPE);
   
   If &_setDataFormatMethod = Null Then
      &_setDataFormatMethod = &cellStyle.getClass().getDeclaredMethod("setDataFormat", &shortType);
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &javaShort);
   &_setDataFormatMethod.invoke(&cellStyle, &args);
   
end-method;

method CreateHyperlink
   /+ &type as JavaObject +/
   /+ Returns JavaObject +/
   If &_createHyperlinkMethod = Null Then
      &_createHyperlinkMethod = &_creationHelper.getClass().getDeclaredMethod("createHyperlink", CreateJavaObject("java.lang.Class[]", &type.getClass()));
   End-If;
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &type);
   Local JavaObject &hyperlink = &_createHyperlinkMethod.invoke(&_creationHelper, &args);
   Return &hyperlink.getClass().cast(&hyperlink);
end-method;

method CreateWorkbook
   /+ &inputSream as JavaObject +/
   /+ Returns JavaObject +/
   
   Local JavaObject &inputStreamClass = GetJavaClass("java.lang.Class").forName("java.io.InputStream");
   Local JavaObject &workbookFactory = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbookFactory");
   Local JavaObject &createMethod = &workbookFactory.getClass().getDeclaredMethod("create", CreateJavaObject("java.lang.Class[]", &inputStreamClass));
   
   Local JavaObject &args = CreateJavaObject("java.lang.Object[]", &inputSream);
   Local JavaObject &workBook = &createMethod.invoke(&workbookFactory, &args);
   
   Return &workBook.getClass().cast(&workBook);
end-method;