net.sf.jett.util.FormulaUtil Maven / Gradle / Ivy

package net.sf.jett.util;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.formula.SheetNameFormatter;

import net.sf.jett.formula.CellRef;
import net.sf.jett.formula.CellRefRange;
import net.sf.jett.formula.Formula;
import net.sf.jett.model.WorkbookContext;

/**
 * The FormulaUtil utility class provides methods for Excel
 * formula creation and manipulation.
 *
 * @author Randy Gettman
 */
public class FormulaUtil
{
   private static final boolean DEBUG = false;

   // Prevents a mapping of "A1" => "A21" and "A2" => "A22" from yielding
   // "A1 + A2" => "A21 + A2" => "A221 + A22".
   private static final String NEGATIVE_LOOKBEHIND_ALPHA = "(?Formulas in the given
    * formula map.  The string "e/" (explicit) or "i/" (implicit) is prepended
    * to the cell key to distinguish when both a formula with an explicit sheet
    * name and another formula with an implicit sheet name would otherwise
    * resolve to the same cell key.
    * @param formulaMap A formula map.
    * @return A cell reference map, a Map of cell key strings to
    *    Lists of CellRefs.  Each List
    *    is initialized to contain only one CellRef, the original
    *    from the cell key string, e.g. "Sheet1!C2" => [Sheet1!C2]
    */
   public static Map> createCellRefMap(Map formulaMap)
   {
      if (DEBUG)
      {
         System.err.println("FU.cCRM");
      }
      Map> cellRefMap = new HashMap>();
      for (String key : formulaMap.keySet())
      {
         Formula formula = formulaMap.get(key);
         if (DEBUG)
         {
            System.err.println("  Processing key " + key + " => " + formula);
         }
         // Formula keys always are of the format "Sheet!CellRef".
         // The key was created internally, so "!" is expected.
         String keySheetName = key.substring(0, key.indexOf("!"));

         for (CellRef cellRef : formula.getCellRefs())
         {
            String sheetName = cellRef.getSheetName();
            String cellKey = getCellKey(cellRef, keySheetName);
            if (sheetName != null)
            {
               cellKey = EXPLICIT_REF_PREFIX + cellKey;
            }
            else
            {
               cellKey = IMPLICIT_REF_PREFIX + cellKey;
            }
            if (!cellRefMap.containsKey(cellKey))
            {
               List cellRefs = new ArrayList();
               CellRef mappedCellRef;
               if (sheetName == null || "".equals(sheetName))
               {
                  // Local sheet reference.
                  mappedCellRef = new CellRef(cellRef.getRow(), cellRef.getCol(),
                     cellRef.isRowAbsolute(), cellRef.isColAbsolute());
               }
               else
               {
                  // Refer to the template sheet name in the CellRef for now.
                  // If necessary, it will be translated into resultant sheet
                  // name(s) later in "updateSheetNameRefsAfterClone".
                  mappedCellRef = new CellRef(sheetName, cellRef.getRow(), cellRef.getCol(),
                     cellRef.isRowAbsolute(), cellRef.isColAbsolute());
               }
               cellRefs.add(mappedCellRef);
               if (DEBUG)
               {
                  System.err.println("    New CellRefMap entry: " + cellKey + " => [" +
                          mappedCellRef.formatAsString() + "]");
               }
               cellRefMap.put(cellKey, cellRefs);
            }
         }
      }
      return cellRefMap;
   }

   /**
    * Creates a "cell key" from a cell ref, with a sheet name supplied if the
    * cell ref doesn't refer to a sheet name.  The returned string is suitable
    * as a key in the cell ref map.
    * @param cellRef The CellRef.
    * @param sheetName The sheet name to use if the CellRef
    *    doesn't supply one.
    * @return A string of the format "sheetName!cellRef", where "sheetName" is
    *    from the CellRef or it defaults to the
    *    sheetName parameter if it doesn't exist.  No single-
    *    quotes are in the cell key.
    * @since 0.8.0
    */
   public static String getCellKey(CellRef cellRef, String sheetName)
   {
      String cellKey;
      String cellRefSheetName = cellRef.getSheetName();
      // If no sheet name, then prepend the sheet name from the Formula key.
      if (cellRefSheetName == null || "".equals(cellRefSheetName))
      {
         // Prepend sheet name from formula key.
         cellKey = sheetName + "!" + cellRef.formatAsString();
      }
      else
      {
         // Single quotes may be in the cell reference.
         // Don't store single-quotes in cell key:
         // "'Test Sheet'!C3" => "Test Sheet!C3"
         cellKey = cellRef.formatAsString().replace("'", "");
      }
      return cellKey;
   }

   /**
    * Replaces cell references in the given formula text with the translated
    * cell references, and returns the formula string.
    * @param formula The Formula, for its access to its original
    *    CellRefs.
    * @param sheetName The name of the Sheet on which the formula
    *    exists.
    * @param context The WorkbookContext, for its access to the
    *    cell reference map.
    * @return A string suitable for an Excel formula, for use in the method
    *    Cell.setCellFormula().
    * @since 0.9.1
    */
   public static String createExcelFormulaString(Formula formula,
      String sheetName, WorkbookContext context)
   {
      return createExcelFormulaString(formula.getFormulaText(), formula, sheetName, context);
   }

   /**
    * Replaces cell references in the given formula text with the translated
    * cell references, and returns the formula string.
    * @param formulaText The Formula text, e.g. "SUM(C2)".
    * @param formula The Formula, for its access to its original
    *    CellRefs.
    * @param sheetName The name of the Sheet on which the formula
    *    exists.
    * @param context The WorkbookContext, for its access to the
    *    cell reference map.
    * @return A string suitable for an Excel formula, for use in the method
    *    Cell.setCellFormula().
    */
   public static String createExcelFormulaString(String formulaText, Formula formula,
      String sheetName, WorkbookContext context)
   {
      Map> cellRefMap = context.getCellRefMap();
      List origCellRefs = formula.getCellRefs();
      StringBuilder buf = new StringBuilder();
      String excelFormula, suffix;
      int endFormulaIdx = getEndOfJettFormula(formulaText, 0);
      int idx = formulaText.indexOf("[", endFormulaIdx);  // Get pos of any suffixes (e.g. "[0,0]").
      if (idx > -1)
      {
         excelFormula = formulaText.substring(0, idx);
         suffix = formulaText.substring(idx);
      }
      else
      {
         excelFormula = formulaText;
         suffix = "";
      }
      // Strip any $[ and ] off the Excel Formula, which at this point has been
      // stripped of any suffixes already.
      if (excelFormula.startsWith(Formula.BEGIN_FORMULA) && excelFormula.endsWith(Formula.END_FORMULA))
         excelFormula = excelFormula.substring(Formula.BEGIN_FORMULA.length(),
            excelFormula.length() - Formula.END_FORMULA.length());

      if (DEBUG)
      {
         System.err.println("FU.cEFS: Formula text:\"" + formulaText + "\" on sheet " + sheetName);
         System.err.println("  excelFormula: \"" + excelFormula + "\"");
      }

      for (CellRef origCellRef : origCellRefs)
      {
         if (DEBUG)
         {
            System.err.println("  Original cell ref: " + origCellRef.formatAsString());
         }
         // Look up the translated cells by cell key, which requires a sheet name.
         String cellKey = getCellKey(origCellRef, sheetName);
         boolean isExplicit = origCellRef.getSheetName() != null;
         if (!isExplicit)
         {
            cellKey = IMPLICIT_REF_PREFIX + cellKey;
         }
         else
         {
            cellKey = EXPLICIT_REF_PREFIX + cellKey;
         }
         // Append the suffix to the cell key to look up the correct references.
         cellKey += suffix;

         // Find the appropriate cell references.
         // It may be necessary to remove suffixes iteratively, if the cell key
         // represents a formula cell reference outside of a looping tag.
         List transCellRefs;
         do
         {
            transCellRefs = cellRefMap.get(cellKey);
            if (DEBUG)
            {
               System.err.println("  cellKey: " + cellKey + " => " + transCellRefs);
            }

            // Remove suffixes, one at a time if it's not found.
            if (transCellRefs == null)
            {
               int lastSuffixIdx = cellKey.lastIndexOf("[");
               if (lastSuffixIdx != -1)
               {
                  cellKey = cellKey.substring(0, lastSuffixIdx);
               }
               else
               {
                  throw new IllegalStateException("Unable to find cell references for cell key \"" + cellKey + "\"!");
               }
            }
         }
         while (transCellRefs == null);

         // Construct the replacement string.
         String cellRefs;
         // Avoid re-allocation of the internal buffer.
         buf.delete(0, buf.length());
         int numCellRefs = transCellRefs.size();
         if (DEBUG)
         {
            System.err.println("  Number of translated cell refs: " + numCellRefs);
         }
         if (numCellRefs > 0)
         {
            for (int i = 0; i < numCellRefs; i++)
            {
               if (i > 0)
                  buf.append(",");
               String cellRef = transCellRefs.get(i).formatAsString();
               if (DEBUG)
               {
                  System.err.println("    Appending cell ref string: \"" + cellRef + "\".");
               }
               buf.append(cellRef);
            }
            cellRefs = buf.toString();
         }
         else
         {
            // All cell references were deleted.  Must use the cell reference's
            // default value.  If that doesn't exist, that means that a default
            // value wasn't specified.  Use the "default" default.
            cellRefs = origCellRef.getDefaultValue();
            if (cellRefs == null)
               cellRefs = CellRef.DEF_DEFAULT_VALUE;
            if (DEBUG)
            {
               System.err.println("    Appending default value: \"" + cellRefs + "\".");
            }
         }
         // Replace the formula text, including any default value, with the
         // updated cell references.
         if(DEBUG)
         {
            System.err.println("Regex: " + NEGATIVE_LOOKBEHIND_ALPHA +
               Pattern.quote(origCellRef.formatAsStringWithDef()) +
               NEGATIVE_LOOKAHEAD_ALPHAN);
            System.err.println("cellRefs: " + Matcher.quoteReplacement(cellRefs));
         }
         excelFormula = excelFormula.replaceAll(
             NEGATIVE_LOOKBEHIND_ALPHA + 
                 Pattern.quote(origCellRef.formatAsStringWithDef()) +
                 NEGATIVE_LOOKAHEAD_ALPHAN,
             Matcher.quoteReplacement(cellRefs));
      }
      return excelFormula;
   }

   /**
    * Examines all CellRefs in each List.  If a group
    * of CellRefs represent a linear range, horizontally or
    * vertically, then they are replaced with a CellRefRange.
    * @param cellRefMap The cell reference map.
    */
   public static void findAndReplaceCellRanges(Map> cellRefMap)
   {
      for (String key : cellRefMap.keySet())
      {
         List cellRefs = cellRefMap.get(key);
         // This will put cells that should be part of a range in consecutive
         // positions.
         Collections.sort(cellRefs);
         if (DEBUG)
         {
            System.err.println("FU.fARCR: Replacing cell ref ranges for \"" + key + "\".");
            System.err.println("  cellRefs: " + cellRefs);
         }
         boolean vertical = false;
         boolean horizontal = false;
         CellRef first = null, prev = null;
         int firstIdx = -1;
         int size = cellRefs.size();

         for (int i = 0; i < size; i++)
         {
            CellRef curr = cellRefs.get(i);
            if (DEBUG)
            {
               System.err.println("  curr is " + curr.formatAsString());
            }
            if (first == null)
            {
               vertical = false;
               horizontal = false;
               first = curr;
               firstIdx = i;
               if (DEBUG)
               {
                  System.err.println("    Case first was null; first: " + first.formatAsString() + ", firstIdx = " + firstIdx);
               }
            }
            else if (vertical)
            {
               if (DEBUG)
               {
                  System.err.println("    Case vertical; first: " + first.formatAsString() + ", firstIdx = " + firstIdx);
               }
               if (!isBelow(prev, curr))
               {
                  // End of range.  Replace sequence of vertically arranged
                  // CellRefs with a single CellRefRange.
                  replaceRange(cellRefs, firstIdx, i - 1);
                  // The list has shrunk.
                  int shrink = size - cellRefs.size();
                  size -= shrink;
                  i -= shrink;
                  // Setup for next range.
                  vertical = false;
                  first = curr;
                  firstIdx = i;
               }
            }
            else if (horizontal)
            {
               if (DEBUG)
               {
                  System.err.println("    Case horizontal; first: " + first.formatAsString() + ", firstIdx = " + firstIdx);
               }
               if (!isRightOf(prev, curr))
               {
                  // End of range.  Replace sequence of vertically arranged
                  // CellRefs with a single CellRefRange.
                  replaceRange(cellRefs, firstIdx, i - 1);
                  // The list has shrunk.
                  int shrink = size - cellRefs.size();
                  size -= shrink;
                  i -= shrink;
                  // Setup for next range.
                  horizontal = false;
                  first = curr;
                  firstIdx = i;
               }
            }
            else
            {
               // Decide on the proper direction, if any.
               if (isRightOf(prev, curr))
                  horizontal = true;
               else if (isBelow(prev, curr))
                  vertical = true;
               else
               {
                  first = curr;
                  firstIdx = i;
               }
               if (DEBUG)
               {
                  System.err.println("    Case none; first: " + first.formatAsString() + ", firstIdx = " + firstIdx +
                     ", horizontal=" + horizontal + ", vertical = " + vertical);
               }
            }
            prev = curr;
         }

         // Don't forget the last one!
         if (horizontal || vertical)
            replaceRange(cellRefs, firstIdx, size - 1);
      }
   }

   /**
    * After sheets have been cloned, all sheets could have been renamed,
    * leaving the situation where in the cell ref map, all cell keys are of the
    * new sheet names, but the CellRefs still refer to the
    * template sheet names.  This updates all CellRefs in the cell
    * ref map to the new sheet names, cloning the references if necessary.
    * @param context The WorkbookContext, which contains the cell
    *    ref map, the template sheet names, and the new sheet names.
    * @since 0.8.0
    */
   public static void updateSheetNameRefsAfterClone(WorkbookContext context)
   {
      Map> cellRefMap = context.getCellRefMap();
      List templateSheetNamesList = context.getTemplateSheetNames();
      List newSheetNamesList = context.getSheetNames();
      if (DEBUG)
      {
         System.err.println("FU.uSNRAC...");
      }
      for (String key : cellRefMap.keySet())
      {
         if (DEBUG)
         {
            System.err.println("key: \"" + key + "\".");
         }
         // Formula keys always are of the format "e/Sheet!CellRef" or "i/Sheet!CellRef".
         // The key was created internally, so "!" is expected.
         // 1. e/templateSheet!cellKey => templateSheet!cellRef
         // Must update cell refs to resultant sheet cell ref(s).
         // 2. i/resultantSheet!cellKey => cellRef
         // Don't update these.
         // Determine if it's a template sheet name.
         boolean isExplicitRef = key.startsWith(EXPLICIT_REF_PREFIX);
         if (!isExplicitRef)
         {
            // Assumed to be a resultant/implicit sheet name; skip.
            continue;
         }
         // Bypass the explicit/implicit prefix.
         String templateSheetName = key.substring(2, key.indexOf("!"));
         // At this point, keySheetName is known to be a template sheet name.
         // No transformation has taken place yet, so there should be exactly
         // one cell ref in the list.
         List cellRefs = cellRefMap.get(key);
         List addedCellRefs = new ArrayList();
         CellRef cellRef = cellRefs.get(0);
         if (DEBUG)
         {
            System.err.println("  cellRef: \"" + cellRef + "\".");
         }
         String templateRefSheetName = cellRef.getSheetName();

         // No cell ref sheet reference means a simple reference, e.g "B2",
         // meaning "this sheet", which means don't update.
         if (templateRefSheetName != null)
         {
            // Update the reference, plus clone the reference too, if more
            // than one template sheet name matches.
            boolean updatedFirstAlready = false;
            for (int j = 0; j < templateSheetNamesList.size(); j++)
            {
               String sheetName = templateSheetNamesList.get(j);
               if (sheetName.equals(templateSheetName))
               {
                  String newSheetName = newSheetNamesList.get(j);
                  CellRef newCellRef = new CellRef(newSheetName, cellRef.getRow(), cellRef.getCol(),
                     cellRef.isRowAbsolute(), cellRef.isColAbsolute());
                  if (updatedFirstAlready)
                  {
                     if (DEBUG)
                     {
                        System.err.println("    refers to other sheet: Adding \"" + newCellRef + "\".");
                     }
                     addedCellRefs.add(newCellRef);
                  }
                  else
                  {
                     if (DEBUG)
                     {
                        System.err.println("    refers to other sheet: Replacing \"" + cellRef + "\" with \"" +
                           newCellRef + "\" keyed by " + key + ".");
                     }
                     cellRefs.set(0, newCellRef);  // The only one so far.
                     updatedFirstAlready = true;
                  }
               }
            }  // End for loop on template sheet names
         }  // End null check on templateSheetRefName
         cellRefs.addAll(addedCellRefs);
      }  // End for loop on cell keys.
   }

   /**
    * After a sheet has been implicitly cloned, there is a sheet that is
    * unaccounted for in the template sheet names, new sheet names, the formula
    * map, and the cell ref map.  This inserts new CellRefs in the
    * cell ref map to the new sheet name, adds new keys in the formula map and
    * the cell ref map, and inserts the "template" sheet name and new sheet
    * name.
    * @param context The WorkbookContext, which contains the cell
    *    ref map, the template sheet names, and the new sheet names.
    * @param origSheetName The current name of the Sheet that was
    *    copied.
    * @param newSheetName The new name of the Sheet that is a
    *    clone of the sheet that was copied.
    * @param clonePos The 0-based index of the sheet that is a clone of the
    *    sheet that was copied.
    * @since 0.9.1
    */
   public static void addSheetNameRefsAfterClone(WorkbookContext context, String origSheetName,
      String newSheetName, int clonePos)
   {
      if (DEBUG)
      {
         System.err.println("FU.aSNRAC(context, " + origSheetName + ", " + newSheetName + ", " + clonePos + ")");
      }
      // Insert into the template and new sheet name lists (local copies,
      // doesn't affect the original list passed in to ExcelTransformer.transform).
      List templateSheetNames = context.getTemplateSheetNames();
      List newSheetNames = context.getSheetNames();
      int index = newSheetNames.indexOf(origSheetName);
      if (index != -1)
      {
         newSheetNames.add(clonePos, newSheetName);
         templateSheetNames.add(clonePos, templateSheetNames.get(index));
      }

      // Formula map: insert new keys.  Make it look like these formulas were
      // here since the beginning of the transformation.
      Map formulaMap = context.getFormulaMap();
      Map addToFormulaMap = new HashMap();
      for (String key : formulaMap.keySet())
      {
         index = key.indexOf("!");  // Expected to be present in all cell keys
         String sheetPartOfKey = key.substring(0, index);
         if (sheetPartOfKey.equals(origSheetName))
         {
            Formula formula = formulaMap.get(key);
            String newKey = newSheetName + "!" + key.substring(index + 1);
            if (DEBUG)
            {
               System.err.println("FU.aSNRAC: Adding formula map key " + newKey + " referring to formula " + formula);
            }
            addToFormulaMap.put(newKey, formula);
         }
      }
      formulaMap.putAll(addToFormulaMap);

      // Cell Ref Map:
      // Add keys for implicit cell references, copying the references.
      // Add cell refs to existing explicit cell references.
      Map> cellRefMap = context.getCellRefMap();
      Map> addToCellRefMap = new HashMap>();
      for (String key : cellRefMap.keySet())
      {
         List cellRefs = cellRefMap.get(key);
         List newCellRefs = null;  // Set if a new entry will be made.
         index = key.indexOf("!");  // Expected to be present in all cell keys
         boolean isExplicit = key.startsWith(EXPLICIT_REF_PREFIX);
         // Bypass explicit/implicit indicator.
         String sheetPartOfKey = key.substring(2, index);
         // If the sheet name in the key changed, then we must replace the entry.
         // This occurs with JETT formulas in a sheet whose name was changed
         // via an expression, when those formulas refer to local sheet cells.
         if (!isExplicit && sheetPartOfKey.equals(origSheetName))
         {
            String newKey = IMPLICIT_REF_PREFIX + newSheetName + "!" + key.substring(index + 1);
            if (DEBUG)
            {
               System.err.println("FU.aSNRAC: Adding cell ref map key " + newKey + " referring to " + cellRefs);
            }
            // Shallow copy is ok; CellRefs aren't changed; they are replaced if needed.
            newCellRefs = new ArrayList(cellRefs);
            addToCellRefMap.put(newKey, newCellRefs);
         }
         else
         {
            List addToCellRefs = new ArrayList();
            for (int i = 0; i < cellRefs.size(); i++)
            {
               CellRef cellRef = cellRefs.get(i);
               String cellRefSheetName = cellRef.getSheetName();
               if (cellRefSheetName != null && cellRefSheetName.equals(origSheetName))
               {
                  CellRef newCellRef = new CellRef(newSheetName, cellRef.getRow(), cellRef.getCol(),
                          cellRef.isRowAbsolute(), cellRef.isColAbsolute());
                  if (DEBUG)
                  {
                     System.err.println("FU.aSNRAC: adding cell ref " + newCellRef + " to list keyed by " + key);
                  }
                  addToCellRefs.add(i, newCellRef);
               }
            }
            cellRefs.addAll(addToCellRefs);
         }
      }
      // Add the new entries.
      cellRefMap.putAll(addToCellRefMap);
   }

   /**
    * When a Sheet is renamed, then this updates all
    * CellRefs in the cell reference map need to be updated too.
    * @param context The WorkbookContext, on which the formula map
    *    and the cell ref map can be found.
    * @param oldSheetName The old sheet name.
    * @param newSheetName The new sheet name.
    * @since 0.8.0
    */
   public static void replaceSheetNameRefs(WorkbookContext context, String oldSheetName, String newSheetName)
   {
      // Update new sheet name list (local copy, doesn't affect the original
      // list passed in to ExcelTransformer.transform).
      List newSheetNames = context.getSheetNames();
      int index = newSheetNames.indexOf(oldSheetName);
      if (index != -1)
      {
         newSheetNames.set(index, newSheetName);
      }

      // Formula map: update keys.
      Map formulaMap = context.getFormulaMap();
      List removeFromFormulaMap = new ArrayList();
      Map addToFormulaMap = new HashMap();
      for (String key : formulaMap.keySet())
      {
         index = key.indexOf("!");  // Expected to be present in all cell keys
         String sheetPartOfKey = key.substring(0, index);
         if (sheetPartOfKey.equals(oldSheetName))
         {
            Formula formula = formulaMap.get(key);
            removeFromFormulaMap.add(key);
            String newKey = newSheetName + "!" + key.substring(index + 1);
            if (DEBUG)
            {
               System.err.println("FU.rSNR: Replacing formula map key " + key + " with " + newKey);
            }
            addToFormulaMap.put(newKey, formula);
         }
      }
      // Now remove all the keys marked to be removed, now that we're past the
      // Iterator and a possible ConcurrentModificationException.
      for (String key : removeFromFormulaMap)
      {
         formulaMap.remove(key);
      }
      // Put back all the replacements.
      formulaMap.putAll(addToFormulaMap);

      // Cell Ref Map: update keys and cell refs.
      Map> cellRefMap = context.getCellRefMap();
      List removeFromCellRefMap = new ArrayList();
      Map> addToCellRefMap = new HashMap>();
      for (String key : cellRefMap.keySet())
      {
         List cellRefs = cellRefMap.get(key);
         index = key.indexOf("!");  // Expected to be present in all cell keys
         boolean isExplicit = key.startsWith(EXPLICIT_REF_PREFIX);
         String sheetPartOfKey = key.substring(2, index);
         // If the sheet name in the key changed, then we must replace the entry.
         // This occurs with JETT formulas in a sheet whose name was changed
         // via an expression, when those formulas refer to local sheet cells.
         if (!isExplicit && sheetPartOfKey.equals(oldSheetName))
         {
            removeFromCellRefMap.add(key);
            String newKey = IMPLICIT_REF_PREFIX + newSheetName + "!" + key.substring(index + 1);
            if (DEBUG)
            {
               System.err.println("FU.rSNR: Replacing cell ref map key " + key + " with " + newKey);
            }
            addToCellRefMap.put(newKey, cellRefs);
         }
         else
         {
            for (int i = 0; i < cellRefs.size(); i++)
            {
               CellRef cellRef = cellRefs.get(i);
               String cellRefSheetName = cellRef.getSheetName();
               if (cellRefSheetName != null && cellRefSheetName.equals(oldSheetName))
               {
                  CellRef newCellRef = new CellRef(newSheetName, cellRef.getRow(), cellRef.getCol(),
                          cellRef.isRowAbsolute(), cellRef.isColAbsolute());
                  if (DEBUG)
                  {
                     System.err.println("FU.rSNR: replacing cell ref " + cellRef + " with " + newCellRef +
                        " for key " + key);
                  }
                  cellRefs.set(i, newCellRef);
               }
            }
         }
      }
      // Now remove all the keys marked to be removed, now that we're past the
      // Iterator and a possible ConcurrentModificationException.
      for (String key : removeFromCellRefMap)
      {
         cellRefMap.remove(key);
      }
      // Put back all the replacements.
      cellRefMap.putAll(addToCellRefMap);
   }

   /**
    * Returns true if curr is directly to the right
    * of prev, i.e., all of the following are true:
    * 
    * The sheet names match or they are both null.
    * 
The row indexes match.
    * 
The column index of curr is one more than the column
    *    index of prev.
    * 
    * @param prev The previous CellRef.
    * @param curr The current CellRef.
    * @return true if curr is directly to the right
    *    of prev, else false.
    */
   private static boolean isRightOf(CellRef prev, CellRef curr)
   {
      return (curr.getRow() == prev.getRow() && curr.getCol() == prev.getCol() + 1 &&
                   ((curr.getSheetName() == null && prev.getSheetName() == null) ||
                    (curr.getSheetName() != null && curr.getSheetName().equals(prev.getSheetName()))));
   }

   /**
    * Returns true if curr is directly below
    * prev, i.e., all of the following are true:
    * 
    * The sheet names match or they are both null.
    * 
The column indexes match.
    * 
The row index of curr is one more than the row
    *    index of prev.
    * 
    * @param prev The previous CellRef.
    * @param curr The current CellRef.
    * @return true if curr is directly below
    *    prev, else false.
    */
   private static boolean isBelow(CellRef prev, CellRef curr)
   {
      return (curr.getCol() == prev.getCol() && curr.getRow() == prev.getRow() + 1 &&
                   ((curr.getSheetName() == null && prev.getSheetName() == null) ||
                    (curr.getSheetName() != null && curr.getSheetName().equals(prev.getSheetName()))));
   }

   /**
    * Replace the CellRefs in the given List of
    * CellRefs, in the range of indexes between
    * startIdx and endIdx with a single
    * CellRefRange.
    * @param cellRefs Modifies this List of CellRefs.
    * @param startIdx The CellRef at this index is treated as the
    *    start of the range (inclusive).
    * @param endIdx The CellRef at this index is treated as the
    *    end of the range (inclusive).
    */
   private static void replaceRange(List cellRefs, int startIdx, int endIdx)
   {
      // Create the range.
      CellRef first = cellRefs.get(startIdx);
      CellRef prev = cellRefs.get(endIdx);
      CellRefRange range = new CellRefRange(first.getSheetName(), first.getRow(), first.getCol(),
         first.isRowAbsolute(), first.isColAbsolute());
      range.setRangeEndCellRef(prev);
      if (DEBUG)
      {
         System.err.println("  Replacing " + first.formatAsString() + " through " +
            prev.formatAsString() + " with " + range.formatAsString());
      }
      // Replace the first with the range.
      cellRefs.set(startIdx, range);
      // Remove the others in the range.  The end index for the "subList"
      // method is exclusive.
      cellRefs.subList(startIdx + 1, endIdx + 1).clear();
   }

   /**
    * Shifts all CellRefs that are in range and on the same
    * Sheet by the given number of rows and/or columns (usually
    * one of those two will be zero).  Modifies the Lists that are
    * the values of cellRefMap.
    * @param sheetName The name of the Sheet on which to shift
    *    cell references.
    * @param context The WorkbookContext which holds the cell ref
    *    map, template sheet names, and new sheet names.
    * @param left The 0-based index of the column on which to start shifting
    *    cell references.
    * @param right The 0-based index of the column on which to end shifting
    *    cell references.
    * @param top The 0-based index of the row on which to start shifting
    *    cell references.
    * @param bottom The 0-based index of the row on which to end shifting
    *    cell references.
    * @param numCols The number of columns to shift the cell reference (can be
    *    negative).
    * @param numRows The number of rows to shift the cell reference (can be
    *    negative).
    * @param remove Determines whether to remove the old cell reference,
    *    resulting in a shift, or not to remove the old cell reference,
    *    resulting in a copy.
    * @param add Determines whether to add the new cell reference, resulting in
    *    a copy, or not to add the new cell reference, resulting in a shift.
    */
   public static void shiftCellReferencesInRange(String sheetName, WorkbookContext context,
      int left, int right, int top, int bottom, int numCols, int numRows,
      boolean remove, boolean add)
   {
      if (DEBUG)
      {
         System.err.println("    FU.sCRIR: left " + left + ", right " + right +
            ", top " + top + ", bottom " + bottom + ", numCols " + numCols +
            ", numRows " + numRows + ", remove " + remove + ", add " + add);
      }
      Map> cellRefMap = context.getCellRefMap();
      List templateSheetNames = context.getTemplateSheetNames();
      List newSheetNames = context.getSheetNames();
      if (numCols == 0 && numRows == 0 && remove && add)
         return;
      for (String cellKey : cellRefMap.keySet())
      {
         // All cell keys have the sheet name in them.
         boolean isExplicit = cellKey.startsWith(EXPLICIT_REF_PREFIX);
         // Bypass explicit/implicit indicator.
         String keySheetName = cellKey.substring(2, cellKey.indexOf("!"));
         if (!keySheetName.equals(sheetName))
         {
            // No exact match.  Check the corresponding template sheet name, if
            // it exists.
            int index = newSheetNames.indexOf(sheetName);
            if (isExplicit || (index != -1 && keySheetName.equals(templateSheetNames.get(index))))
            {
               // Template sheet name match.
               // Update keySheetName (the template sheet name) to the new sheet name.
               keySheetName = sheetName;
            }
            else
            {
               continue;
            }
         }

         List cellRefs = cellRefMap.get(cellKey);
         List delete = new ArrayList();
         List insert = new ArrayList();
         for (CellRef cellRef : cellRefs)
         {
            String cellRefSheetName = cellRef.getSheetName();
            int row = cellRef.getRow();
            int col = cellRef.getCol();
            if ((cellRefSheetName == null || keySheetName.equals(cellRefSheetName)) &&
                    (row >= top && row <= bottom && col >= left && col <= right))
            {
               if (remove)
               {
                  if (DEBUG)
                  {
                     System.err.println("      Deleting cell reference: " + cellRef.formatAsString() +
                             " for cell key " + cellKey);
                  }
                  delete.add(cellRef);
               }
               if (add)
               {
                  CellRef adjCellRef = new CellRef(cellRefSheetName, row + numRows, col + numCols,
                          cellRef.isRowAbsolute(), cellRef.isColAbsolute());
                  if (DEBUG)
                  {
                     System.err.println("      Adding cell reference: " + adjCellRef.formatAsString() +
                             " for cell key " + cellKey);
                  }
                  insert.add(adjCellRef);
               }
            }
         }
         if (remove)
            cellRefs.removeAll(delete);
         if (add)
            cellRefs.addAll(insert);
      }
   }

   /**
    * Copies cell references that are on the same Sheet in the
    * given cell reference map by the given number of rows and/or columns
    * (usually one of those two will be zero).  Modifies the Lists
    * that are the values of cellRefMap.
    * @param sheetName The name of the Sheet on which to copy
    *    references.
    * @param context The WorkbookContext which holds the cell ref
    *    map, template sheet names, and new sheet names.
    * @param left The 0-based index of the column on which to start shifting
    *    cell references.
    * @param right The 0-based index of the column on which to end shifting
    *    cell references.
    * @param top The 0-based index of the row on which to start shifting
    *    cell references.
    * @param bottom The 0-based index of the row on which to end shifting
    *    cell references.
    * @param numCols The number of columns to shift the cell reference (can be
    *    negative).
    * @param numRows The number of rows to shift the cell reference (can be
    *    negative).
    * @param currSuffix The current "[loop,iter]*" suffix we're already in.
    * @param newSuffix The new "[loop,iter]" suffix to add for new entries.
    */
   public static void copyCellReferencesInRange(String sheetName, WorkbookContext context,
      int left, int right, int top, int bottom, int numCols, int numRows, String currSuffix, String newSuffix)
   {
      if (DEBUG)
      {
         System.err.println("    FU.cCRIR: left " + left + ", right " + right +
            ", top " + top + ", bottom " + bottom + ", numCols " + numCols +
            ", numRows " + numRows + ", currSuffix: \"" + currSuffix + "\", newSuffix: \"" + newSuffix + "\"");
      }
      Map> cellRefMap = context.getCellRefMap();
      Map> newCellRefEntries = new HashMap>();
      List templateSheetNames = context.getTemplateSheetNames();
      List newSheetNames = context.getSheetNames();
      for (String cellKey : cellRefMap.keySet())
      {
         // All cell keys have the sheet name in them.
         boolean isExplicit = cellKey.startsWith(EXPLICIT_REF_PREFIX);
         // Bypass the explicit/implicit indicator.
         String keySheetName = cellKey.substring(2, cellKey.indexOf("!"));
         if (!keySheetName.equals(sheetName))
         {
            // No exact match.  Check the corresponding template sheet name, if
            // it exists.
            int index = newSheetNames.indexOf(sheetName);
            if (isExplicit || (index != -1 && keySheetName.equals(templateSheetNames.get(index))))
            {
               // Template sheet name match.
               // Update keySheetName (the template sheet name) to the new sheet name.
               keySheetName = sheetName;
            }
            else
            {
               continue;
            }
         }

         // A cell key may have a suffix, e.g. [0,1].
         String keySuffix = "";
         int idx = cellKey.indexOf("[");
         if (idx > -1)
            keySuffix = cellKey.substring(idx);
         if (currSuffix.startsWith(keySuffix)) // Suffix matches
         {
            List cellRefs = cellRefMap.get(cellKey);
            List insert = new ArrayList();
            for (CellRef cellRef : cellRefs)
            {
               String cellRefSheetName = cellRef.getSheetName();
               int row = cellRef.getRow();
               int col = cellRef.getCol();
               if ((cellRefSheetName == null || keySheetName.equals(cellRefSheetName)) &&    // Sheet matches
                   (row >= top && row <= bottom && col >= left && col <= right))             // In cell range
               {
                  CellRef adjCellRef = new CellRef(cellRefSheetName, row + numRows, col + numCols,
                     cellRef.isRowAbsolute(), cellRef.isColAbsolute());
                  // Only add the reference if being translated!
                  if (numRows != 0 || numCols != 0)
                  {
                     if (DEBUG)
                        System.err.println("      Adding cell reference: " + adjCellRef.formatAsString() +
                           " for cell key " + cellKey);
                     insert.add(adjCellRef);
                  }
                  // Introduce new mappings with the new suffix, e.g. [2,0], appended to
                  // the current suffix, e.g. [0,1][2,0].
                  // Look for formulas in the range.
                  // Only do this once (pick out those without suffixes to accomplish this).
                  if (idx == -1)
                  {
                     String newCellKey = cellKey + currSuffix + newSuffix;
                     List newCellRefs = new ArrayList();
                     newCellRefs.add(adjCellRef);
                     if (DEBUG)
                     {
                        System.err.println("      Adding new entry: " + newCellKey + " => [" +
                                adjCellRef.formatAsString() + "]");
                     }
                     newCellRefEntries.put(newCellKey, newCellRefs);
                  }
               }
            }
            cellRefs.addAll(insert);
         }
      }
      cellRefMap.putAll(newCellRefEntries);
   }

   /**
    * Finds the end of the Jett formula substring.  This accounts for bracket
    * characters ([]) that may be nested inside the JETT formula;
    * they are legal characters in Excel formulas.  It also accounts for Excel
    * string literals, by ignoring bracket characters inside Excel string
    * literals, which are enclosed in double-quotes.  Note that escaped
    * double-quote characters ("") don't change the "inside double
    * quotes" variable, once both double-quotes have been processed.
    * @param cellText The cell text.
    * @param formulaStartIdx The start of the formula.
    * @return The index of the ']' character that ends the JETT formula, or
    *    -1 if not found.
    * @since 0.9.1
    */
   public static int getEndOfJettFormula(String cellText, int formulaStartIdx)
   {
      int numUnMatchedBrackets = 0;
      boolean insideDoubleQuotes = false;

      for (int i = formulaStartIdx + Formula.BEGIN_FORMULA.length(); i < cellText.length(); i++)
      {
         char c = cellText.charAt(i);
         switch (c)
         {
            case '[':
               if (!insideDoubleQuotes)
               {
                  numUnMatchedBrackets++;
               }
               break;
            case ']':
               if (!insideDoubleQuotes)
               {
                  if (numUnMatchedBrackets == 0)
                     return i;

                  numUnMatchedBrackets--;
               }
               break;
            case '"':
               insideDoubleQuotes = !insideDoubleQuotes;
               break;
            default:
               break;
         }
      }
      // End of cell text without matching end-bracket.  Not found.
      return -1;
   }

   /**
    * It's possible that a JETT formula was entered that wouldn't be accepted
    * by Excel because the sheet name needs to be formatted -- enclosed in
    * single quotes, e.g. $[SUM(${dvs.name}$@i=n;l=10;v=s;r=DNE!B3)]
    * -> $[SUM('${dvs.name}$@i=n;l=10;v=s;r=DNE'!B3)]
    * @param formula The original JETT formula text, as entered in the template.
    * @param cellReferences The List of CellRefs
    *    already found by the FormulaParser.
    * @return Formula text with sheet names formatted properly for Excel.
    * @since 0.9.1
    */
   public static String formatSheetNames(String formula, List cellReferences)
   {
      for (CellRef cellRef : cellReferences)
      {
         String sheetName = cellRef.getSheetName();
         if (sheetName != null && !sheetName.startsWith("'") && !sheetName.endsWith("'"))
         {
            String formattedSheetName = SheetNameFormatter.format(sheetName);
            // If not already in single quotes.
            formula = formula.replaceAll("(?