in poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java [620:853]
private ParseNode parseStructuredReference(String tableName) {
if ( ! (_ssVersion.equals(SpreadsheetVersion.EXCEL2007)) ) {
throw new FormulaParseException("Structured references work only on XSSF (Excel 2007+)!");
}
Table tbl = _book.getTable(tableName);
if (tbl == null) {
throw new FormulaParseException("Illegal table name: '" + tableName + "'");
}
String sheetName = tbl.getSheetName();
int startCol = tbl.getStartColIndex();
int endCol = tbl.getEndColIndex();
int startRow = tbl.getStartRowIndex();
int endRow = tbl.getEndRowIndex();
// Do NOT return before done reading all the structured reference tokens from the input stream.
// Throwing exceptions is okay.
int savePtr0 = _pointer;
nextChar();
// Special case: Table1[] is equivalent to Table1[#Data]
if (look == ']') {
// Consume the ']' character
nextChar();
// Skip header and total rows if available
int actualStartRow = startRow;
if (tbl.getHeaderRowCount() > 0) {
actualStartRow = startRow + 1;
}
int actualEndRow = endRow;
if (tbl.getTotalsRowCount() > 0) {
actualEndRow = endRow - 1;
}
final CellReference topLeft = new CellReference(actualStartRow, startCol);
final CellReference bottomRight = new CellReference(actualEndRow, endCol);
final SheetIdentifier sheetIden = new SheetIdentifier( null, new NameIdentifier(sheetName, true));
final Ptg ptg = _book.get3DReferencePtg(new AreaReference(topLeft, bottomRight, _ssVersion), sheetIden);
return new ParseNode(ptg);
}
boolean isTotalsSpec = false;
boolean isThisRowSpec = false;
boolean isDataSpec = false;
boolean isHeadersSpec = false;
boolean isAllSpec = false;
int nSpecQuantifiers = 0; // The number of special quantifiers
while (true) {
int savePtr1 = _pointer;
String specName = parseAsSpecialQuantifier();
if (specName == null) {
resetPointer(savePtr1);
break;
}
switch (specName) {
case specAll:
isAllSpec = true;
break;
case specData:
isDataSpec = true;
break;
case specHeaders:
isHeadersSpec = true;
break;
case specThisRow:
isThisRowSpec = true;
break;
case specTotals:
isTotalsSpec = true;
break;
default:
throw new FormulaParseException("Unknown special quantifier " + specName);
}
nSpecQuantifiers++;
if (look == ','){
nextChar();
} else {
break;
}
}
boolean isThisRow = false;
skipWhite();
if (look == '@') {
isThisRow = true;
nextChar();
}
// parse column quantifier
String endColumnName = null;
int nColQuantifiers = 0;
int savePtr1 = _pointer;
String startColumnName = parseAsColumnQuantifier();
if (startColumnName == null) {
resetPointer(savePtr1);
} else {
nColQuantifiers++;
if (look == ','){
throw new FormulaParseException("The formula "+ _formulaString + " is illegal: you should not use ',' with column quantifiers");
} else if (look == ':') {
nextChar();
endColumnName = parseAsColumnQuantifier();
nColQuantifiers++;
if (endColumnName == null) {
throw new FormulaParseException("The formula "+ _formulaString + " is illegal: the string after ':' must be column quantifier");
}
}
}
if(nColQuantifiers == 0 && nSpecQuantifiers == 0){
resetPointer(savePtr0);
savePtr0 = _pointer;
startColumnName = parseAsColumnQuantifier();
if (startColumnName != null) {
nColQuantifiers++;
} else {
resetPointer(savePtr0);
String name = parseAsSpecialQuantifier();
if (name!=null) {
switch (name) {
case specAll:
isAllSpec = true;
break;
case specData:
isDataSpec = true;
break;
case specHeaders:
isHeadersSpec = true;
break;
case specThisRow:
isThisRowSpec = true;
break;
case specTotals:
isTotalsSpec = true;
break;
default:
throw new FormulaParseException("Unknown special quantifier " + name);
}
nSpecQuantifiers++;
} else {
throw new FormulaParseException("The formula "+ _formulaString + " is illegal");
}
}
} else {
match(']');
}
// Done reading from input stream
// Ok to return now
if (isTotalsSpec && tbl.getTotalsRowCount() == 0) {
return new ParseNode(ErrPtg.REF_INVALID);
}
if ((isThisRow || isThisRowSpec) && (_rowIndex < startRow || endRow < _rowIndex)) {
// structured reference is trying to reference a row above or below the table with [#This Row] or [@]
if (_rowIndex >= 0) {
return new ParseNode(ErrPtg.VALUE_INVALID);
} else {
throw new FormulaParseException(
"Formula contained [#This Row] or [@] structured reference but this row < 0. " +
"Row index must be specified for row-referencing structured references.");
}
}
int actualStartRow = startRow;
int actualEndRow = endRow;
int actualStartCol = startCol;
int actualEndCol = endCol;
if (nSpecQuantifiers > 0) {
//Selecting rows
if (nSpecQuantifiers == 1 && isAllSpec) {
//do nothing
} else if (isDataSpec && isHeadersSpec) {
if (tbl.getTotalsRowCount() > 0) {
actualEndRow = endRow - 1;
}
} else if (isDataSpec && isTotalsSpec) {
actualStartRow = startRow + 1;
} else if (nSpecQuantifiers == 1 && isDataSpec) {
actualStartRow = startRow + 1;
if (tbl.getTotalsRowCount() > 0) {
actualEndRow = endRow - 1;
}
} else if (nSpecQuantifiers == 1 && isHeadersSpec) {
actualEndRow = actualStartRow;
} else if (nSpecQuantifiers == 1 && isTotalsSpec) {
actualStartRow = actualEndRow;
} else if ((nSpecQuantifiers == 1 && isThisRowSpec) || isThisRow) {
actualStartRow = _rowIndex; //The rowNum is 0 based
actualEndRow = _rowIndex;
} else {
throw new FormulaParseException("The formula "+ _formulaString + " is illegal");
}
} else {
if (isThisRow) { // there is a @
actualStartRow = _rowIndex; //The rowNum is 0 based
actualEndRow = _rowIndex;
} else { // Really no special quantifiers
actualStartRow++;
if (tbl.getTotalsRowCount() > 0) actualEndRow--;
}
}
//Selecting cols
if (nColQuantifiers == 2) {
if (startColumnName == null || endColumnName == null) {
throw new IllegalStateException("Cannot parse column: " + startColumnName + " and " + endColumnName + " with formula " + _formulaString);
}
int startIdx = tbl.findColumnIndex(startColumnName);
int endIdx = tbl.findColumnIndex(endColumnName);
if (startIdx == -1 || endIdx == -1) {
throw new FormulaParseException("One of the columns "+ startColumnName +", "+ endColumnName +" doesn't exist in table "+ tbl.getName());
}
actualStartCol = startCol+ startIdx;
actualEndCol = startCol + endIdx;
} else if (nColQuantifiers == 1 && !isThisRow) {
if (startColumnName == null) {
throw new IllegalStateException("Cannot parse column: " + startColumnName + " with formula " + _formulaString);
}
int idx = tbl.findColumnIndex(startColumnName);
if (idx == -1) {
throw new FormulaParseException("The column "+ startColumnName + " doesn't exist in table "+ tbl.getName());
}
actualStartCol = startCol + idx;
actualEndCol = actualStartCol;
}
CellReference topLeft = new CellReference(actualStartRow, actualStartCol);
CellReference bottomRight = new CellReference(actualEndRow, actualEndCol);
SheetIdentifier sheetIden = new SheetIdentifier( null, new NameIdentifier(sheetName, true));
Ptg ptg = _book.get3DReferencePtg(new AreaReference(topLeft, bottomRight, _ssVersion), sheetIden);
return new ParseNode(ptg);
}