package gw.rating.rtm.excel

uses com.guidewire.pl.system.server.Version
uses gw.api.extension.ExtensionInterfaces
uses gw.api.locale.DisplayKey
uses gw.api.productmodel.PolicyLinePatternLookup
uses gw.api.upgrade.Coercions
uses gw.api.util.DisplayableException
uses gw.api.util.LocaleUtil
uses gw.plugin.Plugins
uses gw.plugin.rateflow.IRateRoutinePlugin
uses gw.rating.rtm.domain.RateBookExportVisitor
uses gw.rating.rtm.domain.table.MultiSelectCell
uses gw.rating.rtm.domain.table.RateBookModel
uses gw.rating.rtm.domain.table.RateTableCell
uses gw.rating.rtm.domain.table.RateTableModel
uses gw.rating.rtm.domain.table.RateTableRow
uses gw.rating.rtm.util.ProductModelUtils
uses gw.rating.rtm.valueprovider.RateTableCellValueProviderFactory
uses org.apache.poi.common.usermodel.HyperlinkType
uses org.apache.poi.ss.usermodel.ClientAnchor
uses org.apache.poi.ss.usermodel.Comment
uses org.apache.poi.ss.usermodel.Drawing
uses org.apache.poi.ss.usermodel.RichTextString
uses org.apache.poi.ss.usermodel.Sheet
uses org.apache.poi.ss.util.CellRangeAddress
uses org.apache.poi.ss.util.WorkbookUtil
uses org.apache.poi.xssf.usermodel.XSSFCell
uses org.apache.poi.xssf.usermodel.XSSFCellStyle
uses org.apache.poi.xssf.usermodel.XSSFClientAnchor
uses org.apache.poi.xssf.usermodel.XSSFCreationHelper
uses org.apache.poi.xssf.usermodel.XSSFDrawing
uses org.apache.poi.xssf.usermodel.XSSFHyperlink
uses org.apache.poi.xssf.usermodel.XSSFRichTextString
uses org.apache.poi.xssf.usermodel.XSSFRow
uses org.apache.poi.xssf.usermodel.XSSFSheet
uses org.apache.poi.xssf.usermodel.XSSFWorkbook

uses java.math.BigDecimal

class RateBookExcelExportVisitor extends RateBookExportVisitor {
  // cell address offsets for the rate book detail properties in the worksheet
  public static final var RATE_BOOK_PROPERTY_COLUMN_OFFSET: int = 0
  public static final var RATE_BOOK_PROPERTY_ROW_OFFSET: int = 2
  // cell address offsets for the policy criteria properties in the worksheet
  public static final var RATE_BOOK_POLICY_CRITERIA_COLUMN_OFFSET: int = 3
  public static final var RATE_BOOK_POLICY_CRITERIA_ROW_OFFSET: int = 2
  // cell address offsets for the localization properties in the rate book details worksheet
  public static final var RATE_BOOK_LOCALIZATIONS_COLUMN_OFFSET: int = 8
  public static final var RATE_BOOK_LOCALIZATIONS_ROW_OFFSET: int = 2
  // cell address offsets for the rate table links
  public static final var RATE_BOOK_TABLE_LINK_COLUMN_OFFSET: int = 0
  public static final var RATE_BOOK_TABLE_LINK_ROW_OFFSET: int = 16
  // cell address offsets for the rate routine links
  public static final var RATE_BOOK_ROUTINE_LINK_COLUMN_OFFSET: int = 3
  public static final var RATE_BOOK_ROUTINE_LINK_ROW_OFFSET: int = 16

  // cell address offsets for the policy criteria properties in the worksheet
  public static final var RATE_ROUTINE_LOCALIZATIONS_COLUMN_OFFSET: int = 8
  public static final var RATE_ROUTINE_LOCALIZATIONS_ROW_OFFSET: int = 2

  public static final var RATE_ROUTINE_MERGED_COLUMN_START: int = 2
  public static final var RATE_ROUTINE_MERGED_COLUMN_END: int = 6

  public static final var RATE_TABLE_PROPERTIES_SECTION_ROWS_COUNT: int = 6

  public static final var EXCEL_SHEET_NAME_LIMIT: int = 31

  final static var WORKBOOK_SEPARATOR_COLUMN_WIDTH = 400
  static var MICROSOFT_EXCEL_MAX_PRECISION = 15
  static var NUM_RATE_ROUTINE_COLUMNS = 11
  static final var NUM_RATE_BOOK_DETAIL_COLUMNS = 11

  protected var _workbook: XSSFWorkbook
  protected var _worksheet: XSSFSheet
  protected var _row: XSSFRow
  var _repo: RateTableCellValueProviderFactory
  var _rowCount = 0
  protected var _nextCell: int = 0
  var _creationHelper: XSSFCreationHelper
  var _worksheetNameMap: Map<String, Integer>
  var _worksheetNames: Set<String>

  var _excelStyles: ExcelStyles as readonly Styles

  protected var _tableHyperlinkRowNum: int = 0
  var _routineHyperlinkRowNum = 0
  var _showJurisdiction: boolean
  protected var _workbookDetailsSheet: XSSFSheet

  construct(workbook: XSSFWorkbook) {
    this._workbook = workbook
    this._creationHelper = _workbook.CreationHelper
    _worksheetNameMap = {}
    _worksheetNames = {}
    _excelStyles = new ExcelStyles(workbook)
  }

  construct(worksheet: XSSFSheet, repo: RateTableCellValueProviderFactory) {
    this._worksheet = worksheet
    this._repo = repo
    _rowCount = ExcelHeader.RATE_TABLE_HEADER_END_ROW
    _worksheetNameMap = {}
    _worksheetNames = {}
    _excelStyles = new ExcelStyles(worksheet.Workbook)
  }

  override function visit(ratingRow: RateTableRow) {
    increment_rowCount()
    _row = _worksheet.createRow(_rowCount)
    _nextCell = 0
  }


  override function visitBoolean(ratingCell: RateTableCell<Comparable>) {
    var cell = createCell()
    cell.setCellValue(ratingCell.ExportValue as Boolean)
    incrementCellCount()
    maybeAddDisplayName(ratingCell)
  }

  override function visitDate(ratingCell: RateTableCell<Comparable>) {
    var cell = createCell()
    cell.setCellValue(Coercions.makeDateFrom(ratingCell.ExportValue))
    incrementCellCount()
    maybeAddDisplayName(ratingCell)
  }

  override function visitDecimal(ratingCell: RateTableCell<Comparable>) {
    checkPrecision(ratingCell)
    var cell = createCell()
    cell.setCellValue(ratingCell.ExportValue as Double)
    incrementCellCount()
    maybeAddDisplayName(ratingCell)
  }

  override function visitInteger(ratingCell: RateTableCell<Comparable>) {
    var cell = createCell()
    cell.setCellValue(ratingCell.ExportValue as Double)
    incrementCellCount()
    maybeAddDisplayName(ratingCell)
  }

  override function visitString(ratingCell: RateTableCell<Comparable>) {
    var cell = createCell()
    cell.setCellValue(ratingCell.ExportValue as String)
    incrementCellCount()
    maybeAddDisplayName(ratingCell)
  }

  override function visitEmpty(ratingCell: RateTableCell<Comparable>) {
    addBlankCell()
    if (shouldAddDisplayCell(ratingCell)) {
      addBlankDisplayCell()
    }
  }

  protected function createCell(): XSSFCell {
    var cell = _row.createCell(_nextCell)
    setCellStyle(cell)
    return cell
  }

  protected function setCellStyle(cell: XSSFCell) {
    cell.setCellStyle(_worksheet.getColumnStyle(_nextCell) as XSSFCellStyle)
  }

  function isFactorCell(ratingCell: RateTableCell<Comparable>): boolean {
    return ratingCell.ColumnDefinition.DefinitionForFactor != null
  }

  function addBlankCell() {
    var cell = createCell()
    cell.setCellType(XSSFCell.CELL_TYPE_BLANK)
    incrementCellCount()
  }

  function addBlankDisplayCell() {
    var cell = createCell()
    cell.setCellType(XSSFCell.CELL_TYPE_STRING)
    incrementCellCount()
  }

  override function visitingEmptyCells(): boolean {
    return true
  }

  override function visit(bookModel: RateBookModel) {
    this.RateBookExportResult = bookModel.Book.ExcelExportResult
    progressToNextItem()
    _worksheet = createNewSheet(DisplayKey.get("Web.Rating.Export.RateBookDetailWorksheetName"))
    _workbookDetailsSheet = _worksheet
    var rateBook = bookModel.Book
    addRateBookNameToWorkSheet(rateBook)
    addRateBookDetailsToWorkSheet(rateBook)
    makeSeparatorColumn(RATE_BOOK_POLICY_CRITERIA_COLUMN_OFFSET - 1)
    addPolicyCriteriaToWorkSheet(rateBook)
    addRateTableLinkHeadersToWorksheet(RATE_BOOK_TABLE_LINK_ROW_OFFSET)
    addRateRoutineLinkHeadersToWorksheet()
    makeSeparatorColumn(RATE_BOOK_LOCALIZATIONS_COLUMN_OFFSET - 1)
    addRateBookDetailLocalizationsToWorkSheet(rateBook)
    autoSizeColumns(NUM_RATE_BOOK_DETAIL_COLUMNS)
    addNoImportDisclaimer()
  }

  override function visit(tableModel: RateTableModel) {
    progressToNextItem()
    _worksheet = createNewSheet(tableModel.RateTable.Definition.TableCode)

    init(tableModel)

    if (_workbookDetailsSheet != null) {
      addRateTableLinksToWorkSheet(tableModel, _worksheet)
    }
  }

  override function visit(routine: CalcRoutineDefinition) {
    progressToNextItem()
    _worksheet = createNewSheet(routine.Code)
    addRateRoutineTitle(routine)
    addRateRoutineProperties(routine)
    addRateRoutineLocalizationsToWorkSheet(routine)
    addRateRoutineRows(routine)
    makeSeparatorColumn(RATE_ROUTINE_LOCALIZATIONS_COLUMN_OFFSET - 1)

    if (_workbookDetailsSheet != null) {
      addRateRoutineLinksToWorkSheet(routine, _worksheet)
    }
  }

  protected function autoSizeColumns(numColumns: int) {
    for (columnIndex in 0..|numColumns) {
      _worksheet.autoSizeColumn(columnIndex)
    }
  }

  override function visitAfter(tableModel: RateTableModel) {
    //Only use the faster autoSize for huge RateTables cause it isn't as perfect as
    //POI's slow autosize
    if (_worksheet.getLastRowNum() > ExtensionInterfaces.IExcelAutoSizerExtensions.TABLE_AUTO_SIZE_THRESHOLD) {
      ExtensionInterfaces.IExcelAutoSizerExtensions.autoSizeForLargeRateTables(_worksheet)
    } else {
      autoSizeColumns(_worksheet.RateTableColumnCount)
    }
    // Even though the notes are at the top of the worksheet,
    // add the import notes after autosizing the worksheet || aka applyBodyStyle
    addRateTablePropertiesToWorkSheet(tableModel)
    addRatingTableImportInstructions()
  }

  /**
   * Init to set value provider repository, row count and add header
   * @param tableModel
   */
  protected function init(tableModel: RateTableModel){
    _repo = tableModel.ValueProviderRepository
    _rowCount = ExcelHeader.RATE_TABLE_HEADER_END_ROW
    addHeaderTo(tableModel)
  }

  private function addHeaderTo(model: RateTableModel) {
    for (fillerIndex in 0..|ExcelHeader.RATE_TABLE_HEADER_START_ROW) {
      _worksheet.createRow(fillerIndex)
    }

    // Add the table name to the worksheet
    var tableNameRow = _worksheet.createRow(ExcelHeader.RATE_TABLE_HEADER_START_ROW)
    var tableNameCell = tableNameRow.createCell(0)
    tableNameCell.setCellValue(model.RateTableDefinition.TableName)
    tableNameCell.setCellStyle(Styles.TitleStyle)

    // Add the table column headers to the worksheet
    var columnHeadersRow = _worksheet.createRow(ExcelHeader.RATE_TABLE_HEADER_END_ROW)
    _worksheet.createFreezePane(0, ExcelHeader.RATE_TABLE_HEADER_END_ROW + 1)

    var cellIndexOffset = 0
    //to account for any display names that are added, should only affect the excel sheet being created, rating tables should not take this into account

    for (column in model.RateTableDefinition.AllColumns index indx) {
      //if the column is a coded value type then need to add in a column for the codes display name
      var headerCell = columnHeadersRow.createCell(indx + cellIndexOffset)

      //set default cell style to be unlocked
      headerCell.setCellStyle(Styles.ColHeaderStyle)

      //if the column is for encoded values such as typelists or product model codes that have display names
      if (column.ValueProvider != null and RateTableCellValueProviderFactory.getValueProvider(column).HasCodedValue) {
        headerCell.setCellValue(composeCodeName(column.LocalizedColumnLabel))
        _worksheet.setDefaultColumnStyle(indx + cellIndexOffset, Styles.PropValueStyle)

        cellIndexOffset++
        var nameHeaderCell = columnHeadersRow.createCell(indx + cellIndexOffset)
        //internationalize
        nameHeaderCell.setCellValue(composeDisplayName(column.LocalizedColumnLabel))
        nameHeaderCell.setCellStyle(Styles.ColHeaderStyle)
        nameHeaderCell.setCellComment(createDisplayComment(_worksheet))
        _worksheet.setDefaultColumnStyle(indx + cellIndexOffset, Styles.DisplayNameStyle)
      } else {
        headerCell.setCellValue(column.LocalizedColumnLabel)
        if (column.DefinitionForFactor != null) {
          _worksheet.setDefaultColumnStyle(indx + cellIndexOffset, Styles.FactorStyle)
        } else if (column.ColumnType == TC_DATE) {
          headerCell.setCellStyle(Styles.RightAlignColHeaderStyle)
          _worksheet.setDefaultColumnStyle(indx + cellIndexOffset, Styles.DateStyle)
        } else if (column.ColumnType == TC_INTEGER || column.ColumnType == TC_DECIMAL) {
          headerCell.setCellStyle(Styles.RightAlignColHeaderStyle)
          _worksheet.setDefaultColumnStyle(indx + cellIndexOffset, Styles.BodyStyle)
        } else {
          //if it is not a coded value
          _worksheet.setDefaultColumnStyle(indx + cellIndexOffset, Styles.BodyStyle)
        }
      }
    }

    // We need the number of columns after the header columns are created to know
    // how many filler cells to color to match the table width
    for (columnIndex in 1..|_worksheet.RateTableColumnCount) {
      tableNameRow.createCell(columnIndex).setCellStyle(Styles.TitleStyle)
    }

    if (_worksheet.RateTableColumnCount > 1) {
      _worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, _worksheet.RateTableColumnCount - 1))
      for (rowIndex in 1..|ExcelHeader.RATE_TABLE_HEADER_END_ROW) {
        var row = _worksheet.createRow(rowIndex)
        for (columnIndex in 0..|_worksheet.RateTableColumnCount) {
          row.createCell(columnIndex).setCellStyle(Styles.InstructionStyle)
        }
      }
    }
  }

  private function addRateTablePropertiesToWorkSheet(model: RateTableModel) {
    var rateTablePropertiesRowOffset = ExcelHeader.RATE_TABLE_HEADER_END_ROW - RATE_TABLE_PROPERTIES_SECTION_ROWS_COUNT
    var rateTablePropertiesCellOffset = 0

    var rowNum = rateTablePropertiesRowOffset
    var cellNum = rateTablePropertiesCellOffset

    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateTables.Code"), model.RateTableDefinition.TableCode)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateTables.Description"), model.RateTableDefinition.LocalizedTableDesc)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateTables.PolicyLine"), PolicyLinePatternLookup.getByPublicID(model.RateTableDefinition.PolicyLine).DisplayName)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.PolicyCenterVersion"), Version.getAppVersion())
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Locale"), LocaleUtil.CurrentLanguageType.Code)
  }

  private function addRatingTableImportInstructions() {
    var infoRow1 = getOrCreateRow(ExcelHeader.RATE_TABLE_HEADER_START_ROW + 1)
    var infoRow2 = getOrCreateRow(ExcelHeader.RATE_TABLE_HEADER_START_ROW + 2)
    var infoRow3 = getOrCreateRow(ExcelHeader.RATE_TABLE_HEADER_START_ROW + 3)

    var infoCell1 = infoRow1.createCell(0)
    infoCell1.setCellValue(DisplayKey.get("Web.Rating.Export.RateTable.Instruction.Line1"))
    infoCell1.setCellStyle(Styles.InstructionStyle)
    var infoCell2 = infoRow2.createCell(0)
    infoCell2.setCellValue(DisplayKey.get("Web.Rating.Export.RateTable.Instruction.Line2"))
    infoCell2.setCellStyle(Styles.InstructionStyle)
    var infoCell3 = infoRow3.createCell(0)
    infoCell3.setCellValue(DisplayKey.get("Web.Rating.Export.RateTable.Instruction.Line3"))
    infoCell3.setCellStyle(Styles.InstructionStyle)
  }

  private function shouldAddDisplayCell(ratingCell: RateTableCell<Comparable>): boolean {
    return ratingCell.ColumnDefinition.ValueProvider != null and _repo.getValueProviderFor(ratingCell).HasCodedValue
  }

  private function maybeAddDisplayName(ratingCell: RateTableCell<Comparable>) {
    if (!shouldAddDisplayCell(ratingCell)) {
      return
    }
    var cell = createCell()
    cell.setCellValue(getDisplayValue(ratingCell, _repo))
    incrementCellCount()
  }

  public static function getDisplayValue(ratingCell: RateTableCell<Comparable>, valueProviderRepository: RateTableCellValueProviderFactory): String {
    if (typeof(ratingCell) == MultiSelectCell) {
      var rateTableCellValueProvider = valueProviderRepository.getValueProviderFor(ratingCell)
      var unpackedValues = RateTableColumn.unpackMultiSelectString(ratingCell.Value as String)
      var displayList = unpackedValues.map<String>(\value -> rateTableCellValueProvider.valueByCode(ratingCell.Adapter, value as String))
      return displayList.join(", ")
    }

    return valueProviderRepository.getValueProviderFor(ratingCell).valueByCode(ratingCell.Adapter, ratingCell.Value as String)
  }

  private function increment_rowCount() {
    _rowCount++
  }

  private function incrementCellCount() {
    _nextCell++
  }

  private function checkPrecision(targetCell: RateTableCell<Comparable>) {
    if (targetCell == null or targetCell.Value == null) {
      return
    }
    var decValue = targetCell.Value as BigDecimal
    if (decValue.precision() > MICROSOFT_EXCEL_MAX_PRECISION) {
      throw new DisplayableException(DisplayKey.get("Web.Rating.Errors.PrecisionExceededOnRateTableExport",
          targetCell.ColumnDefinition.ColumnLabel,
          decValue,
          MICROSOFT_EXCEL_MAX_PRECISION))
    }
  }

  private function makeSeparatorColumn(columnIndex: int) {
    _worksheet.setColumnWidth(columnIndex, WORKBOOK_SEPARATOR_COLUMN_WIDTH)
  }

  /**
   * adds a column for each header
   */
  protected function addTableHeadersAt(rowNum: int, cellNum: int, headers: List<String>) {
    headers.each(\header -> {
      var row = getOrCreateRow(rowNum)
      var cell = row.createCell(cellNum)
      cell.setCellStyle(Styles.ColHeaderStyle)
      cell.setCellValue(header)
      cellNum++
    })
  }

  /**
   * adds a row for the localized field value in each language
   */
  private function addLocalizationTableRowsFor(rowNum: int, cellNum: int, object: Object, fields: List<String>) {
    var defaultLanguage = LocaleUtil.getDefaultLanguageType()
    // for each language available add the localization row
    LocaleUtil.getAllLanguages().each(\language -> {
      var row = getOrCreateRow(rowNum)
      var cell = row.createCell(cellNum)

      cell.setCellValue(language.DisplayName + (language == defaultLanguage ? DisplayKey.get("Web.Admin.LocalizedValuesLV.Default") : ""))

      fields.eachWithIndex(\field, index -> {
        cell = row.createCell(cellNum + index + 1)
        cell.setCellValue(object[field + "_" + language.Code] as String)
        cell.setCellStyle(Styles.PropValueStyle)
      })

      rowNum++
    })
  }

  private function addLocalizationTableFor(rowNum: int, cellNum: int, object: Object, headers: List<String>, fields: List<String>) {
    // add the default language column
    headers.add(0, DisplayKey.get("Web.Admin.LocalizedValuesDV.Language"))
    addTableHeadersAt(rowNum, cellNum, headers)
    rowNum++
    addLocalizationTableRowsFor(rowNum, cellNum, object, fields)
  }

  private function addRateBookDetailLocalizationsToWorkSheet(rateBook: RateBook) {
    var rowNum = RATE_BOOK_LOCALIZATIONS_ROW_OFFSET
    var cellNum = RATE_BOOK_LOCALIZATIONS_COLUMN_OFFSET

    // add a section header that spans the length of the data in the columns
    addSectionHeaderAt(rowNum, cellNum, 3, DisplayKey.get("Web.Admin.LocalizedValuesDV.Localization"))

    // make a list of headers for which the localized values will be displayed
    var headers = {DisplayKey.get("Web.Rating.RateBooks.Name"), DisplayKey.get("Web.Rating.RateBooks.Description")}

    // make a list of fields for which the localized values will be displayed
    var fields = {RateBook#BookName.PropertyInfo.Name, RateBook#BookDesc.PropertyInfo.Name}

    rowNum++
    addLocalizationTableFor(rowNum, cellNum, rateBook, headers, fields)
  }

  private function addRateRoutineLocalizationsToWorkSheet(calcRoutineDefinition: CalcRoutineDefinition) {
    var rowNum = RATE_ROUTINE_LOCALIZATIONS_ROW_OFFSET
    var cellNum = RATE_ROUTINE_LOCALIZATIONS_COLUMN_OFFSET

    // add a section header that spans the length of the data in the columns
    addSectionHeaderAt(rowNum, cellNum, 3, DisplayKey.get("Web.Admin.LocalizedValuesDV.Localization"))

    // make a list of headers for which the localized values will be displayed
    var headers = {DisplayKey.get("Web.Rating.RateRoutines.Name"), DisplayKey.get("Web.Rating.RateRoutines.Description")}

    // make a list of fields for which the localized values will be displayed
    var fields = {CalcRoutineDefinition#Name.PropertyInfo.Name, CalcRoutineDefinition#Description.PropertyInfo.Name}

    rowNum++
    addLocalizationTableFor(rowNum, cellNum, calcRoutineDefinition, headers, fields)
  }

  private function addRateBookNameToWorkSheet(rateBook: RateBook) {
    var titleCell = _worksheet.createRow(0).createCell(0)
    titleCell.setCellValue(rateBook.LocalisedBookName)
    titleCell.setCellStyle(Styles.TitleStyle)
    _worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, NUM_RATE_BOOK_DETAIL_COLUMNS - 1))
  }

  private function addNoImportDisclaimer() {
    var noImportDisclaimerCell = _worksheet.createRow(1).createCell(0)
    noImportDisclaimerCell.setCellStyle(Styles.NoImportDisclaimerStyle)
    noImportDisclaimerCell.setCellValue(DisplayKey.get("Web.Rating.Export.RateBookDetail.NoImportDisclaimer"))
  }

  private function addRateBookDetailsToWorkSheet(rateBook: RateBook) {
    var rowNum = RATE_BOOK_PROPERTY_ROW_OFFSET
    var cellNum = RATE_BOOK_PROPERTY_COLUMN_OFFSET

    addSectionHeaderAt(rowNum, cellNum, 2, DisplayKey.get("Web.Rating.Export.RateBookDetailWorksheetName"))

    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Code"), rateBook.BookCode)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Edition"), rateBook.BookEdition)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Description"), rateBook.BookDesc)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Status"), rateBook.Status.DisplayName)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.CascadedLookup"), rateBook.CascadedLookup ?
        DisplayKey.get("Web.Rating.RateBooks.CascadedLookup.Boolean.True") :
        DisplayKey.get("Web.Rating.RateBooks.CascadedLookup.Boolean.False"))
    if (RateBook.Type.TypeInfo.getProperty("BookGroup") != null) {
      rowNum++
      insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.BookGroup"), rateBook["BookGroup"] as String)
    }
    rowNum++
    // discussed with PM and agreed to just use the LastStatusChangeDate instead of the activation date
    insertTimeProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.LastStatusChangeDate"), rateBook.LastStatusChangeDate)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.LastUpdatedBy"), rateBook.UpdateUser.DisplayName)
    rowNum++
    insertTimeProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.LastUpdateTime"), rateBook.UpdateTime)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.PolicyCenterVersion"), Version.getAppVersion())
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Locale"), LocaleUtil.CurrentLanguageType.Code)
  }

  private function addPolicyCriteriaToWorkSheet(rateBook: RateBook) {
    var rowNum = RATE_BOOK_POLICY_CRITERIA_ROW_OFFSET
    var cellNum = RATE_BOOK_POLICY_CRITERIA_COLUMN_OFFSET

    addSectionHeaderAt(rowNum, cellNum, 2, DisplayKey.get("Web.Rating.RateBooks.PolicyCriteriaTitle"))

    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.PolicyLine"), rateBook.PolicyLine == null ? "" : rateBook.policyLineCodeToDescription(rateBook.PolicyLine))
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Jurisdiction"), rateBook.BookJurisdiction.DisplayName)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.UWCompany"), rateBook.UWCompany.DisplayName)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.Offering"), rateBook.BookOffering == null ? "" : ProductModelUtils.getOfferingDisplayName(rateBook.BookOffering))
    rowNum++
    addSubSectionPropertyHeader(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.EffectiveDate"))
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.EffectiveDate.After"), rateBook.EffectiveDate)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.RenewalEffectiveDate.Before"), rateBook.ExpirationDate)
    rowNum++
    addSubSectionPropertyHeader(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.RenewalEffectiveDate"))
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.EffectiveDate.After"), rateBook.RenewalEffectiveDate)
    rowNum++
    insertProperty(rowNum, cellNum, DisplayKey.get("Web.Rating.RateBooks.RenewalEffectiveDate.Before"), rateBook.ExpirationDate)
  }

  protected function addRateTableLinkHeadersToWorksheet(startingRowNumber: int) {
    _tableHyperlinkRowNum = startingRowNumber
    var cellNum = RATE_BOOK_TABLE_LINK_COLUMN_OFFSET

    addSectionHeaderAt(_tableHyperlinkRowNum, cellNum, 2, DisplayKey.get("Web.Rating.RateBooks.SelectedRateTablesTitle"))

    _tableHyperlinkRowNum++
    var headers = {DisplayKey.get("Web.Rating.RateTables.Name"),
        DisplayKey.get("Web.Rating.RateTables.Code")}
    addTableHeadersAt(_tableHyperlinkRowNum, cellNum, headers)
    _tableHyperlinkRowNum++
  }

  private function addRateRoutineLinkHeadersToWorksheet() {
    _routineHyperlinkRowNum = RATE_BOOK_ROUTINE_LINK_ROW_OFFSET
    var cellNum = RATE_BOOK_ROUTINE_LINK_COLUMN_OFFSET
    // since this is from an etx, the jurisdiction can be removable
    _showJurisdiction = Plugins.get(IRateRoutinePlugin).BranchingFields.hasMatch(\p -> p.Name == "Jurisdiction")

    addSectionHeaderAt(_routineHyperlinkRowNum, cellNum, 4, DisplayKey.get("Web.Rating.RateBooks.SelectedRateRoutinesTitle"))
    _routineHyperlinkRowNum++

    var headers = {DisplayKey.get("Web.Rating.RateRoutines.Name"),
        DisplayKey.get("Web.Rating.RateRoutines.Code"),
        DisplayKey.get("Web.Rating.RateRoutines.Version")}
    if (_showJurisdiction) {
      headers.add(DisplayKey.get("Web.Rating.RateRoutines.Jurisdiction"))
    }
    addTableHeadersAt(_routineHyperlinkRowNum, cellNum, headers)

    _routineHyperlinkRowNum++
  }

  private function addRateTableLinksToWorkSheet(tableModel: RateTableModel, worksheet: XSSFSheet) {
    var cellNum = RATE_BOOK_TABLE_LINK_COLUMN_OFFSET
    var row = getOrCreateRow(_tableHyperlinkRowNum, _workbookDetailsSheet)
    addPropertyValueToRow(row, cellNum, tableModel.RateTableDefinition.LocalizedTableName, makeCellHyperlinkWithSheetAddress(worksheet.getSheetName()))
    addPropertyValueToRow(row, cellNum + 1, tableModel.RateTableDefinition.TableCode)
    _tableHyperlinkRowNum++
  }

  private function addRateRoutineLinksToWorkSheet(routine: CalcRoutineDefinition, worksheet: XSSFSheet) {
    var cellNum = RATE_BOOK_ROUTINE_LINK_COLUMN_OFFSET

    var row = getOrCreateRow(_routineHyperlinkRowNum, _workbookDetailsSheet)
    addPropertyValueToRow(row, cellNum, routine.LocalizedName, makeCellHyperlinkWithSheetAddress(worksheet.getSheetName()))
    addPropertyValueToRow(row, cellNum + 1, routine.Code)
    addPropertyValueToRow(row, cellNum + 2, routine.Version)
    if (_showJurisdiction) {
      addPropertyValueToRow(row, cellNum + 3, routine["Jurisdiction"]["DisplayName"] as String)
    }
    _routineHyperlinkRowNum++
  }

  protected function makeCellHyperlinkWithSheetAddress(targetSheetName: String): XSSFHyperlink {
    var link = _creationHelper.createHyperlink(HyperlinkType.DOCUMENT)
    link.Address = ExcelExport.createHyperlinkAddressToWorksheet(targetSheetName)
    return link
  }

  protected function addSectionHeaderAt(rowNum: int, cellNum: int, len: int, key: String) {
    var row = getOrCreateRow(rowNum)
    var cell = row.createCell(cellNum)
    cell.setCellValue(key)
    cell.setCellStyle(Styles.SectionHeaderStyle)
    _worksheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, cellNum, cellNum + len - 1))
  }

  /**
   * The method will add a row (if one doesn't exist) with
   * a property name (at cell address [rowNum][cellNum]) and
   * a value (at cell address [rowNum][cellNum + 1]).
   */
  private function addSubSectionPropertyHeader(rowNum: int, cellNum: int, name: String) {
    var row = getOrCreateRow(rowNum)
    addPropertyHeaderNameToRow(row, cellNum, name)
  }

  /**
   * The method will add a row (if one doesn't exist) with
   * a property name (at cell address [rowNum][cellNum]) and
   * a value (at cell address [rowNum][cellNum + 1]).
   */
  private function insertProperty(rowNum: int, cellNum: int, name: String, value: String) {
    var row = getOrCreateRow(rowNum)
    addPropertyNameToRow(row, cellNum, name)
    addPropertyValueToRow(row, cellNum + 1, value)
  }

  /**
   * The method will add a row (if one doesn't exist) with
   * a property name (at cell address [rowNum][cellNum]) and
   * a value (at cell address [rowNum][cellNum + 1]).
   */
  private function insertProperty(rowNum: int, cellNum: int, name: String, value: Integer) {
    var row = getOrCreateRow(rowNum)
    addPropertyNameToRow(row, cellNum, name)
    addPropertyValueToRow(row, cellNum + 1, value)
  }

  /**
   * The method will add a row (if one doesn't exist) with
   * a property name (at cell address [rowNum][cellNum]) and
   * a value (at cell address [rowNum][cellNum + 1]).
   */
  private function insertProperty(rowNum: int, cellNum: int, name: String, value: Date) {
    var row = getOrCreateRow(rowNum)
    addPropertyNameToRow(row, cellNum, name)
    addPropertyValueToRow(row, cellNum + 1, value)
  }

  /**
   * The method will add a row (if one doesn't exist) with
   * a property name (at cell address [rowNum][cellNum]) and
   * a value (at cell address [rowNum][cellNum + 1]).
   */
  private function insertTimeProperty(rowNum: int, cellNum: int, name: String, value: Date) {
    var row = getOrCreateRow(rowNum)
    addPropertyNameToRow(row, cellNum, name)
    addDateTimePropertyValueToRow(row, cellNum + 1, value)
  }

  protected function getOrCreateRow(rowNumber: int): XSSFRow {
    return getOrCreateRow(rowNumber, _worksheet)
  }

  protected function getOrCreateRow(rowNumber: int, worksheet: XSSFSheet): XSSFRow {
    var row = worksheet.getRow(rowNumber)
    return (row != null) ? row : worksheet.createRow(rowNumber)
  }

  private function addPropertyHeaderNameToRow(row: XSSFRow, cellNum: int, name: String) {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropValueHeaderStyle)
    nameCell.setCellValue(name)
    var valueCell = row.createCell(cellNum + 1)
    valueCell.setCellStyle(Styles.PropValueHeaderStyle)
  }

  private function addPropertyNameToRow(row: XSSFRow, cellNum: int, name: String) {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropNameStyle)
    nameCell.setCellValue(name)
  }

  protected function addPropertyValueToRow(row: XSSFRow, cellNum: int, string: String): XSSFCell {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropValueStyle)
    if (string != null) {
      nameCell.setCellValue(string)
    }
    return nameCell
  }

  protected function addPropertyValueToRow(row: XSSFRow, cellNum: int, string: String, link: XSSFHyperlink) {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropValueLinkStyle)
    if (string != null) {
      nameCell.setCellValue(string)
      nameCell.setHyperlink(link)
    }
  }

  private function addPropertyValueToRow(row: XSSFRow, cellNum: int, integer: Integer) {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropValueStyle)
    if (integer != null) {
      nameCell.setCellValue(integer)
    }
  }

  private function addPropertyValueToRow(row: XSSFRow, cellNum: int, date: Date) {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropValueDateStyle)
    if (date != null) {
      nameCell.setCellValue(date)
    }
  }

  private function addDateTimePropertyValueToRow(row: XSSFRow, cellNum: int, date: Date) {
    var nameCell = row.createCell(cellNum)
    nameCell.setCellStyle(Styles.PropValueDateTimeStyle)
    if (date != null) {
      nameCell.setCellValue(date)
    }
  }

  protected function addRateRoutineTitle(routine: CalcRoutineDefinition) {
    var titleCell = _worksheet.createRow(0).createCell(0)
    titleCell.setCellValue(routine.Name)
    titleCell.setCellStyle(Styles.TitleStyle)
    _worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, NUM_RATE_ROUTINE_COLUMNS - 1))
  }

  private function addRateRoutineProperties(routine: CalcRoutineDefinition) {
    var rowCount = _worksheet.PhysicalNumberOfRows
    _worksheet.createRow(rowCount) //blank line
    rowCount++

    var startingPropertiesRowNum = rowCount
    //add routine properties
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.Code"), routine.Code)
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.Name"), routine.Name)
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.Version"), routine.Version)
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.Description"), routine.Description)
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.PolicyLine"), routine.PolicyLinePatternCode)
    // since this is from an etx, the jurisdiction can be removable
    if (Plugins.get(IRateRoutinePlugin).BranchingFields.hasMatch(\p -> p.Name == "Jurisdiction")) {
      rowCount++
      insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.Jurisdiction"), routine["Jurisdiction"]["Code"] as String)
    }
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.ParameterSet"), routine.ParameterSet.Code)
    rowCount++
    var parameters = ExtensionInterfaces.IRateRoutineParameterSetHelperFactory.newInstance(routine).getStringForParamTypes()
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.Parameters"), parameters)
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.LastUpdatedBy"), routine.UpdateUser.DisplayName)
    rowCount++
    insertTimeProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateRoutines.LastUpdateTime"), routine.UpdateTime)
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateBooks.PolicyCenterVersion"), Version.getAppVersion())
    rowCount++
    insertProperty(rowCount, 1, DisplayKey.get("Web.Rating.RateBooks.Locale"), LocaleUtil.CurrentLanguageType.Code)

    for (rowToMerge in startingPropertiesRowNum..rowCount) {
      _worksheet.addMergedRegion(new CellRangeAddress(rowToMerge, rowToMerge, RATE_ROUTINE_MERGED_COLUMN_START, RATE_ROUTINE_MERGED_COLUMN_END))
    }
  }

  private function addRateRoutineRows(routine: CalcRoutineDefinition) {
    var rowCount = _worksheet.PhysicalNumberOfRows
    _worksheet.createRow(rowCount)

    rowCount++
    addSectionHeaderAt(rowCount, 0, 7, DisplayKey.get("Web.Rating.Flow.CalcRoutine.Steps"))

    //add header
    rowCount++
    var headerTitles = {DisplayKey.get("Web.Rating.Flow.CalcRoutine.LineNumber"),
        DisplayKey.get("Web.Rating.Flow.CalcRoutine.Instruction"),
        DisplayKey.get("Web.Rating.Flow.CalcRoutine.Operator"),
        DisplayKey.get("Web.Rating.Flow.CalcRoutine.LeftParenthesis"),
        DisplayKey.get("Web.Rating.Flow.CalcRoutine.Operand"),
        DisplayKey.get("Web.Rating.Flow.CalcRoutine.RightParenthesis"),
        DisplayKey.get("Web.Rating.Flow.CalcRoutine.Notes")
    }

    var row = _worksheet.createRow(rowCount)
    headerTitles.each(\columnName -> {
      var cell = row.createCell(row.PhysicalNumberOfCells)
      cell.setCellStyle(Styles.ColHeaderStyle)
      cell.setCellValue(columnName)
    })
    rowCount++

    // get indentation information
    var indentLevelMap = routine.StepIndentLevels

    //add rows
    var rowsWithComments = new ArrayList<Integer>()
    routine.OrderedSteps.each(\step -> {
      row = _worksheet.createRow(rowCount)

      addPropertyValueToRow(row, row.PhysicalNumberOfCells, step.Column_StepNumber)
      addPropertyValueToRow(row, row.PhysicalNumberOfCells, routine.indentValue(step, step.Column_Instruction, indentLevelMap))
      addPropertyValueToRow(row, row.PhysicalNumberOfCells, step.Column_Op)
      addPropertyValueToRow(row, row.PhysicalNumberOfCells, step.Column_LeftParenthesis)
      addPropertyValueToRow(row, row.PhysicalNumberOfCells, step.Column_Operand)
      addPropertyValueToRow(row, row.PhysicalNumberOfCells, step.Column_RightParenthesis)
      addPropertyValueToRow(row, row.PhysicalNumberOfCells, step.Column_Comments)

      if ((step.IsSectionCommentStep || step.IsVoidFunctionStep) and step.Column_Op != null) {
        _worksheet.addMergedRegion(new CellRangeAddress(rowCount, rowCount, RATE_ROUTINE_MERGED_COLUMN_START, RATE_ROUTINE_MERGED_COLUMN_END))
        var mergedCell = row.getCell(RATE_ROUTINE_MERGED_COLUMN_START)
        mergedCell.setCellStyle(Styles.PropValueCommentStyle)
        mergedCell.CellStyle.setWrapText(true)
        rowsWithComments.add(rowCount)
      }

      rowCount++
    })

    //autosize has to be done first in order to set width of columns
    //then we can calculate the number of times a comment cell wraps
    autoSizeColumns(NUM_RATE_ROUTINE_COLUMNS)

    //override line comment width
    _worksheet.setColumnWidth(RATE_ROUTINE_MERGED_COLUMN_END, 20000)

    rowsWithComments.each(\rowNumber -> autoSizeHeightForMergedCellWithTextWrap(_worksheet.getRow(rowNumber)))
  }

  private function autoSizeHeightForMergedCellWithTextWrap(row: XSSFRow) {
    var mergedCell = row.getCell(RATE_ROUTINE_MERGED_COLUMN_START)

    var mergedCellWidth = 0.0f
    for (colNum in RATE_ROUTINE_MERGED_COLUMN_START..RATE_ROUTINE_MERGED_COLUMN_END) {
      mergedCellWidth += row.Sheet.getColumnWidth(colNum)
    }

    mergedCellWidth /= 350    // random magic number that sets height kind of properly, sigh...

    var cellValue = mergedCell.StringCellValue
    var lineCount = (cellValue.length / mergedCellWidth) + 1
    row.setHeight((row.Height * lineCount) as short)
  }

  protected function createNewSheet(sheetName: String): XSSFSheet {
    var safeName = WorkbookUtil.createSafeSheetName(sheetName)
    safeName = createUniqueSheetName(safeName)
    return _workbook.createSheet(safeName)
  }

  protected function createUniqueSheetName(safeName: String): String {
    var uniqueName = safeName
    var uniqueNameLowerCaseKey = uniqueName.toLowerCase()
    var resultName = uniqueName

    if (_worksheetNames.contains(uniqueNameLowerCaseKey)) {
      var i = _worksheetNameMap.getOrDefault(uniqueNameLowerCaseKey, 1)

      //finding the correct version number that hasn't been used yet
      while (_worksheetNames.contains(formatUniqueSheetName(uniqueNameLowerCaseKey, i))) {
        i++;
      }

      _worksheetNameMap.put(uniqueNameLowerCaseKey, i + 1)

      resultName = formatUniqueSheetName(uniqueName, i)
    }

    _worksheetNames.add(resultName.toLowerCase())
    return resultName
  }

  private function formatUniqueSheetName(name: String, version: int): String {
    var result = name
    var postfix = " (${version})"

    result += postfix
    if (result.length > EXCEL_SHEET_NAME_LIMIT) {
      // trim the result to be 31 characters, including the version number
      result = result.substring(0, EXCEL_SHEET_NAME_LIMIT - postfix.length) + postfix
    }

    return result
  }

  private function composeDisplayName(codeDisplayName: String): String {
    return codeDisplayName.concat(DisplayKey.get("Web.Rating.Export.DisplayName"))
  }

  private function composeCodeName(code: String): String {
    return code.concat(DisplayKey.get("Web.Rating.Export.Code"))
  }

  private function createDisplayComment(sheet: Sheet): Comment {
    var drawing = sheet.createDrawingPatriarch()
    return createCellComment(drawing, DisplayKey.get("Web.Rating.Export.DisplayOnly"))
  }

  private function createCellComment(drawing: Drawing, comment: String): Comment {
    var cmt: Comment
    var txt: RichTextString
    if (drawing typeis XSSFDrawing) {
      var anchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 3, 3)
      anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE)
      cmt = drawing.createCellComment(anchor)
      txt = new XSSFRichTextString(comment)
    }
    cmt.setString(txt)
    return cmt
  }
}