package org.apache.poi.xssf.usermodel;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.EvaluationName;
import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaParsingWorkbook;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.SheetIdentifier;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.ptg.Area3DPxg;
import org.apache.poi.ss.formula.ptg.NamePtg;
import org.apache.poi.ss.formula.ptg.NameXPtg;
import org.apache.poi.ss.formula.ptg.NameXPxg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.Ref3DPxg;
import org.apache.poi.ss.formula.udf.IndexedUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.NotImplemented;
import org.apache.poi.util.Internal;
import org.apache.poi.xssf.model.ExternalLinksTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;
@Internal
public abstract class BaseXSSFEvaluationWorkbook implements FormulaRenderingWorkbook, EvaluationWorkbook, FormulaParsingWorkbook {
protected final XSSFWorkbook _uBook;
private Map<String, XSSFTable> _tableCache;
protected BaseXSSFEvaluationWorkbook(XSSFWorkbook book) {
_uBook = book;
}
@Override
public void clearAllCachedResultValues() {
_tableCache = null;
}
private int convertFromExternalSheetIndex(int externSheetIndex) {
return externSheetIndex;
}
@Override
public int convertFromExternSheetIndex(int externSheetIndex) {
return externSheetIndex;
}
private int convertToExternalSheetIndex(int sheetIndex) {
return sheetIndex;
}
@Override
public int getExternalSheetIndex(String sheetName) {
int sheetIndex = _uBook.getSheetIndex(sheetName);
return convertToExternalSheetIndex(sheetIndex);
}
private int resolveBookIndex(String bookName) {
if (bookName.startsWith("[") && bookName.endsWith("]")) {
bookName = bookName.substring(1, bookName.length()-2);
}
try {
return Integer.parseInt(bookName);
} catch (NumberFormatException e) {}
List<ExternalLinksTable> tables = _uBook.getExternalLinksTable();
int index = findExternalLinkIndex(bookName, tables);
if (index != -1) return index;
if (bookName.startsWith("'file:///") && bookName.endsWith("'")) {
String relBookName = bookName.substring(bookName.lastIndexOf('/')+1);
relBookName = relBookName.substring(0, relBookName.length()-1);
index = findExternalLinkIndex(relBookName, tables);
if (index != -1) return index;
ExternalLinksTable fakeLinkTable = new FakeExternalLinksTable(relBookName);
tables.add(fakeLinkTable);
return tables.size();
}
throw new RuntimeException("Book not linked for filename " + bookName);
}
private int findExternalLinkIndex(String bookName, List<ExternalLinksTable> tables) {
int i = 0;
for (ExternalLinksTable table : tables) {
if (table.getLinkedFileName().equals(bookName)) {
return i+1;
}
i++;
}
return -1;
}
private static class FakeExternalLinksTable extends ExternalLinksTable {
private final String fileName;
private FakeExternalLinksTable(String fileName) {
this.fileName = fileName;
}
@Override
public String getLinkedFileName() {
return fileName;
}
}
@Override
public EvaluationName getName(String name, int sheetIndex) {
for (int i = 0; i < _uBook.getNumberOfNames(); i++) {
XSSFName nm = _uBook.getNameAt(i);
String nameText = nm.getNameName();
int nameSheetindex = nm.getSheetIndex();
if (name.equalsIgnoreCase(nameText) &&
(nameSheetindex == -1 || nameSheetindex == sheetIndex)) {
return new Name(nm, i, this);
}
}
return sheetIndex == -1 ? null : getName(name, -1);
}
@Override
public String getSheetName(int sheetIndex) {
return _uBook.getSheetName(sheetIndex);
}
@Override
public ExternalName getExternalName(int externSheetIndex, int externNameIndex) {
throw new IllegalStateException("HSSF-style external references are not supported for XSSF");
}
@Override
public ExternalName getExternalName(String nameName, String sheetName, int externalWorkbookNumber) {
if (externalWorkbookNumber > 0) {
int linkNumber = externalWorkbookNumber - 1;
ExternalLinksTable linkTable = _uBook.getExternalLinksTable().get(linkNumber);
for (org.apache.poi.ss.usermodel.Name name : linkTable.getDefinedNames()) {
if (name.getNameName().equals(nameName)) {
int nameSheetIndex = name.getSheetIndex() + 1;
return new ExternalName(nameName, -1, nameSheetIndex);
}
}
throw new IllegalArgumentException("Name '"+nameName+"' not found in " +
"reference to " + linkTable.getLinkedFileName());
} else {
int nameIdx = _uBook.getNameIndex(nameName);
return new ExternalName(nameName, nameIdx, 0);
}
}
@Override
public NameXPxg getNameXPtg(String name, SheetIdentifier sheet) {
IndexedUDFFinder udfFinder = (IndexedUDFFinder)getUDFFinder();
FreeRefFunction func = udfFinder.findFunction(name);
if (func != null) {
return new NameXPxg(null, name);
}
if (sheet == null) {
if (!_uBook.getNames(name).isEmpty()) {
return new NameXPxg(null, name);
}
return null;
}
if (sheet._sheetIdentifier == null) {
int bookIndex = resolveBookIndex(sheet._bookName);
return new NameXPxg(bookIndex, null, name);
}
String sheetName = sheet._sheetIdentifier.getName();
if (sheet._bookName != null) {
int bookIndex = resolveBookIndex(sheet._bookName);
return new NameXPxg(bookIndex, sheetName, name);
} else {
return new NameXPxg(sheetName, name);
}
}
@Override
public Ptg get3DReferencePtg(CellReference cell, SheetIdentifier sheet) {
if (sheet._bookName != null) {
int bookIndex = resolveBookIndex(sheet._bookName);
return new Ref3DPxg(bookIndex, sheet, cell);
} else {
return new Ref3DPxg(sheet, cell);
}
}
@Override
public Ptg get3DReferencePtg(AreaReference area, SheetIdentifier sheet) {
if (sheet._bookName != null) {
int bookIndex = resolveBookIndex(sheet._bookName);
return new Area3DPxg(bookIndex, sheet, area);
} else {
return new Area3DPxg(sheet, area);
}
}
@Override
public String resolveNameXText(NameXPtg n) {
int idx = n.getNameIndex();
String name = null;
IndexedUDFFinder udfFinder = (IndexedUDFFinder)getUDFFinder();
name = udfFinder.getFunctionName(idx);
if (name != null) return name;
XSSFName xname = _uBook.getNameAt(idx);
if (xname != null) {
name = xname.getNameName();
}
return name;
}
@Override
public ExternalSheet getExternalSheet(int externSheetIndex) {
throw new IllegalStateException("HSSF-style external references are not supported for XSSF");
}
@Override
public ExternalSheet getExternalSheet(String firstSheetName, String lastSheetName, int externalWorkbookNumber) {
String workbookName;
if (externalWorkbookNumber > 0) {
int linkNumber = externalWorkbookNumber - 1;
ExternalLinksTable linkTable = _uBook.getExternalLinksTable().get(linkNumber);
workbookName = linkTable.getLinkedFileName();
} else {
workbookName = null;
}
if (lastSheetName == null || firstSheetName.equals(lastSheetName)) {
return new ExternalSheet(workbookName, firstSheetName);
} else {
return new ExternalSheetRange(workbookName, firstSheetName, lastSheetName);
}
}
@NotImplemented
public int getExternalSheetIndex(String workbookName, String sheetName) {
throw new RuntimeException("not implemented yet");
}
@Override
public int getSheetIndex(String sheetName) {
return _uBook.getSheetIndex(sheetName);
}
@Override
public String getSheetFirstNameByExternSheet(int externSheetIndex) {
int sheetIndex = convertFromExternalSheetIndex(externSheetIndex);
return _uBook.getSheetName(sheetIndex);
}
@Override
public String getSheetLastNameByExternSheet(int externSheetIndex) {
return getSheetFirstNameByExternSheet(externSheetIndex);
}
@Override
public String getNameText(NamePtg namePtg) {
return _uBook.getNameAt(namePtg.getIndex()).getNameName();
}
@Override
public EvaluationName getName(NamePtg namePtg) {
int ix = namePtg.getIndex();
return new Name(_uBook.getNameAt(ix), ix, this);
}
@Override
public XSSFName createName() {
return _uBook.createName();
}
private static String caseInsensitive(String s) {
return s.toUpperCase(Locale.ROOT);
}
private Map<String, XSSFTable> getTableCache() {
if ( _tableCache != null ) {
return _tableCache;
}
_tableCache = new HashMap<>();
for (Sheet sheet : _uBook) {
for (XSSFTable tbl : ((XSSFSheet)sheet).getTables()) {
String lname = caseInsensitive(tbl.getName());
_tableCache.put(lname, tbl);
}
}
return _tableCache;
}
@Override
public XSSFTable getTable(String name) {
if (name == null) return null;
String lname = caseInsensitive(name);
return getTableCache().get(lname);
}
@Override
public UDFFinder getUDFFinder(){
return _uBook.getUDFFinder();
}
@Override
public SpreadsheetVersion getSpreadsheetVersion(){
return SpreadsheetVersion.EXCEL2007;
}
private static final class Name implements EvaluationName {
private final XSSFName _nameRecord;
private final int _index;
private final FormulaParsingWorkbook _fpBook;
public Name(XSSFName name, int index, FormulaParsingWorkbook fpBook) {
_nameRecord = name;
_index = index;
_fpBook = fpBook;
}
public Ptg[] getNameDefinition() {
return FormulaParser.parse(_nameRecord.getRefersToFormula(), _fpBook, FormulaType.NAMEDRANGE, _nameRecord.getSheetIndex());
}
public String getNameText() {
return _nameRecord.getNameName();
}
public boolean hasFormula() {
CTDefinedName ctn = _nameRecord.getCTName();
String strVal = ctn.getStringValue();
return !ctn.getFunction() && strVal != null && strVal.length() > 0;
}
public boolean isFunctionName() {
return _nameRecord.isFunctionName();
}
public boolean isRange() {
return hasFormula();
}
public NamePtg createPtg() {
return new NamePtg(_index);
}
}
}