Package net.sf.jett.util

Class FormulaUtil

java.lang.Object

net.sf.jett.util.FormulaUtil

public class FormulaUtil
extends java.lang.Object

The FormulaUtil utility class provides methods for Excel formula creation and manipulation.

Author:

Randy Gettman

Field Summary

Fields

Modifier and Type	Field	Description
static java.lang.String	EXPLICIT_REF_PREFIX	Prefix for explicit cell map references.
static java.lang.String	IMPLICIT_REF_PREFIX	Prefix for implicit cell map references.

Constructor Summary

Constructors

Constructor	Description
FormulaUtil()

Method Summary

Modifier and Type	Method	Description
static void	addSheetNameRefsAfterClone(WorkbookContext context, java.lang.String origSheetName, java.lang.String newSheetName, int clonePos)	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.
static void	copyCellReferencesInRange(java.lang.String sheetName, WorkbookContext context, int left, int right, int top, int bottom, int numCols, int numRows, java.lang.String currSuffix, java.lang.String newSuffix)	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).
static java.util.Map<java.lang.String,java.util.List<CellRef>>	createCellRefMap(java.util.Map<java.lang.String,Formula> formulaMap)	Finds unique cell references in all Formulas in the given formula map.
static java.lang.String	createExcelFormulaString(java.lang.String formulaText, Formula formula, java.lang.String sheetName, WorkbookContext context)	Replaces cell references in the given formula text with the translated cell references, and returns the formula string.
static java.lang.String	createExcelFormulaString(Formula formula, java.lang.String sheetName, WorkbookContext context)	Replaces cell references in the given formula text with the translated cell references, and returns the formula string.
static void	findAndReplaceCellRanges(java.util.Map<java.lang.String,java.util.List<CellRef>> cellRefMap)	Examines all CellRefs in each List.
static java.lang.String	formatSheetNames(java.lang.String formula, java.util.List<CellRef> cellReferences)	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.
static java.lang.String	getCellKey(CellRef cellRef, java.lang.String sheetName)	Creates a "cell key" from a cell ref, with a sheet name supplied if the cell ref doesn't refer to a sheet name.
static int	getEndOfJettFormula(java.lang.String cellText, int formulaStartIdx)	Finds the end of the JETT formula substring.
static void	replaceSheetNameRefs(WorkbookContext context, java.lang.String oldSheetName, java.lang.String newSheetName)	When a Sheet is renamed, then this updates all CellRefs in the cell reference map need to be updated too.
static void	shiftCellReferencesInRange(java.lang.String sheetName, WorkbookContext context, int left, int right, int top, int bottom, int numCols, int numRows, boolean remove, boolean add)	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).
static void	updateSheetNameRefsAfterClone(WorkbookContext context)	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.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

EXPLICIT_REF_PREFIX

public static final java.lang.String EXPLICIT_REF_PREFIX

Prefix for explicit cell map references.

Since:

0.9.1

See Also:

Constant Field Values

IMPLICIT_REF_PREFIX

public static final java.lang.String IMPLICIT_REF_PREFIX

Prefix for implicit cell map references.

Since:

0.9.1

See Also:

Constant Field Values

Constructor Detail

FormulaUtil

public FormulaUtil()

Method Detail

createCellRefMap

public static java.util.Map<java.lang.String,java.util.List<CellRef>> createCellRefMap(java.util.Map<java.lang.String,Formula> formulaMap)

Finds unique cell references in all 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.

Parameters:

formulaMap - A formula map.

Returns:

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]

getCellKey

public static java.lang.String getCellKey(CellRef cellRef,
                                          java.lang.String sheetName)

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.

Parameters:

cellRef - The CellRef.

sheetName - The sheet name to use if the CellRef doesn't supply one.

Returns:

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

createExcelFormulaString

public static java.lang.String createExcelFormulaString(Formula formula,
                                                        java.lang.String sheetName,
                                                        WorkbookContext context)

Replaces cell references in the given formula text with the translated cell references, and returns the formula string.

Parameters:

formula - The Formula, for its access to its original CellRefs.

sheetName - The name of the Sheet on which the formula exists.

context - The WorkbookContext, for its access to the cell reference map.

Returns:

A string suitable for an Excel formula, for use in the method Cell.setCellFormula().

Since:

0.9.1

createExcelFormulaString

public static java.lang.String createExcelFormulaString(java.lang.String formulaText,
                                                        Formula formula,
                                                        java.lang.String sheetName,
                                                        WorkbookContext context)

Replaces cell references in the given formula text with the translated cell references, and returns the formula string.

Parameters:

formulaText - The Formula text, e.g. "SUM(C2)".

formula - The Formula, for its access to its original CellRefs.

sheetName - The name of the Sheet on which the formula exists.

context - The WorkbookContext, for its access to the cell reference map.

Returns:

A string suitable for an Excel formula, for use in the method Cell.setCellFormula().

findAndReplaceCellRanges

public static void findAndReplaceCellRanges(java.util.Map<java.lang.String,java.util.List<CellRef>> cellRefMap)

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.

Parameters:

cellRefMap - The cell reference map.

updateSheetNameRefsAfterClone

public static void updateSheetNameRefsAfterClone(WorkbookContext context)

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.

Parameters:

context - The WorkbookContext, which contains the cell ref map, the template sheet names, and the new sheet names.

Since:

0.8.0

addSheetNameRefsAfterClone

public static void addSheetNameRefsAfterClone(WorkbookContext context,
                                              java.lang.String origSheetName,
                                              java.lang.String newSheetName,
                                              int clonePos)

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.

Parameters:

context - The WorkbookContext, which contains the cell ref map, the template sheet names, and the new sheet names.

origSheetName - The current name of the Sheet that was copied.

newSheetName - The new name of the Sheet that is a clone of the sheet that was copied.

clonePos - The 0-based index of the sheet that is a clone of the sheet that was copied.

Since:

0.9.1

replaceSheetNameRefs

public static void replaceSheetNameRefs(WorkbookContext context,
                                        java.lang.String oldSheetName,
                                        java.lang.String newSheetName)

When a Sheet is renamed, then this updates all CellRefs in the cell reference map need to be updated too.

Parameters:

context - The WorkbookContext, on which the formula map and the cell ref map can be found.

oldSheetName - The old sheet name.

newSheetName - The new sheet name.

Since:

0.8.0

shiftCellReferencesInRange

public static void shiftCellReferencesInRange(java.lang.String sheetName,
                                              WorkbookContext context,
                                              int left,
                                              int right,
                                              int top,
                                              int bottom,
                                              int numCols,
                                              int numRows,
                                              boolean remove,
                                              boolean add)

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.

Parameters:

sheetName - The name of the Sheet on which to shift cell references.

context - The WorkbookContext which holds the cell ref map, template sheet names, and new sheet names.

left - The 0-based index of the column on which to start shifting cell references.

right - The 0-based index of the column on which to end shifting cell references.

top - The 0-based index of the row on which to start shifting cell references.

bottom - The 0-based index of the row on which to end shifting cell references.

numCols - The number of columns to shift the cell reference (can be negative).

numRows - The number of rows to shift the cell reference (can be negative).

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.

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.

copyCellReferencesInRange

public static void copyCellReferencesInRange(java.lang.String sheetName,
                                             WorkbookContext context,
                                             int left,
                                             int right,
                                             int top,
                                             int bottom,
                                             int numCols,
                                             int numRows,
                                             java.lang.String currSuffix,
                                             java.lang.String newSuffix)

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.

Parameters:

sheetName - The name of the Sheet on which to copy references.

context - The WorkbookContext which holds the cell ref map, template sheet names, and new sheet names.

left - The 0-based index of the column on which to start shifting cell references.

right - The 0-based index of the column on which to end shifting cell references.

top - The 0-based index of the row on which to start shifting cell references.

bottom - The 0-based index of the row on which to end shifting cell references.

numCols - The number of columns to shift the cell reference (can be negative).

numRows - The number of rows to shift the cell reference (can be negative).

currSuffix - The current "[loop,iter]*" suffix we're already in.

newSuffix - The new "[loop,iter]" suffix to add for new entries.

getEndOfJettFormula

public static int getEndOfJettFormula(java.lang.String cellText,
                                      int formulaStartIdx)

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.

Parameters:

cellText - The cell text.

formulaStartIdx - The start of the formula.

Returns:

The index of the ']' character that ends the JETT formula, or -1 if not found.

Since:

0.9.1

formatSheetNames

public static java.lang.String formatSheetNames(java.lang.String formula,
                                                java.util.List<CellRef> cellReferences)

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)]

Parameters:

formula - The original JETT formula text, as entered in the template.

cellReferences - The List of CellRefs already found by the FormulaParser.

Returns:

Formula text with sheet names formatted properly for Excel.

Since:

0.9.1