org.jxls.formula.StandardFormulaProcessor Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of jxls Show documentation
Show all versions of jxls Show documentation
Small library for Excel generation based on XLS templates
package org.jxls.formula;
import org.jxls.area.Area;
import org.jxls.common.AreaRef;
import org.jxls.common.CellData;
import org.jxls.common.CellRef;
import org.jxls.transform.Transformer;
import org.jxls.util.CellRefUtil;
import org.jxls.util.Util;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* This is a standard formula processor implementation which takes into account all performed cell
* transformations to properly evaluate all the formulas even for complex templates.
* But it is very-very slow.
* In many cases it is better to use {@link FastFormulaProcessor} as it is much-much faster
* although may produce incorrect results in some specific cases
*/
public class StandardFormulaProcessor implements FormulaProcessor {
private static Logger logger = LoggerFactory.getLogger(StandardFormulaProcessor.class);
private static final int MAX_NUM_ARGS_FOR_SUM = 255;
@Override
public void processAreaFormulas(Transformer transformer) {
processAreaFormulas(transformer, null);
}
@Override
public void processAreaFormulas(Transformer transformer, Area area) {
Set formulaCells = transformer.getFormulaCells();
for (CellData formulaCellData : formulaCells) {
logger.debug("Processing formula cell {}", formulaCellData);
List formulaCellRefs = Util.getFormulaCellRefs(formulaCellData.getFormula());
List jointedCellRefs = Util.getJointedCellRefs(formulaCellData.getFormula());
List targetFormulaCells = formulaCellData.getTargetPos();
Map> targetCellRefMap = new LinkedHashMap<>();
Map> jointedCellRefMap = new LinkedHashMap<>();
for (String cellRef : formulaCellRefs) {
CellRef pos = new CellRef(cellRef);
if( pos.isValid() ) {
if (pos.getSheetName() == null) {
pos.setSheetName(formulaCellData.getSheetName());
pos.setIgnoreSheetNameInFormat(true);
}
List targetCellDataList = transformer.getTargetCellRef(pos);
if(targetCellDataList.isEmpty() && area != null && !area.getAreaRef().contains(pos)){
targetCellDataList.add(pos);
}
targetCellRefMap.put(pos, targetCellDataList);
}
}
for (String jointedCellRef : jointedCellRefs) {
List nestedCellRefs = Util.getCellRefsFromJointedCellRef(jointedCellRef);
List jointedCellRefList = new ArrayList();
for (String cellRef : nestedCellRefs) {
CellRef pos = new CellRef(cellRef);
if(pos.getSheetName() == null ){
pos.setSheetName(formulaCellData.getSheetName());
pos.setIgnoreSheetNameInFormat(true);
}
List targetCellDataList = transformer.getTargetCellRef(pos);
jointedCellRefList.addAll(targetCellDataList);
}
jointedCellRefMap.put(jointedCellRef, jointedCellRefList);
}
List usedCellRefs = new ArrayList<>();
for (int i = 0; i < targetFormulaCells.size(); i++) {
CellRef targetFormulaCellRef = targetFormulaCells.get(i);
String targetFormulaString = formulaCellData.getFormula();
if( formulaCellData.getArea() == null ) {
continue;
}
AreaRef formulaSourceAreaRef = formulaCellData.getArea().getAreaRef();
AreaRef formulaTargetAreaRef = formulaCellData.getTargetParentAreaRef().get(i);
boolean isFormulaCellRefsEmpty = true;
for (Map.Entry> cellRefEntry : targetCellRefMap.entrySet()) {
List targetCells = cellRefEntry.getValue();
if( targetCells.isEmpty() ) {
continue;
}
isFormulaCellRefsEmpty = false;
List replacementCells = findFormulaCellRefReplacements(formulaSourceAreaRef, formulaTargetAreaRef, cellRefEntry);
if( formulaCellData.getFormulaStrategy() == CellData.FormulaStrategy.BY_COLUMN ){
replacementCells = Util.createTargetCellRefListByColumn(targetFormulaCellRef, replacementCells, usedCellRefs);
usedCellRefs.addAll(replacementCells);
}
String replacementString = Util.createTargetCellRef(replacementCells);
if(targetFormulaString.startsWith("SUM") && Util.countOccurences(replacementString, ',') >= MAX_NUM_ARGS_FOR_SUM ) {
// Excel doesn't support more than 255 arguments in functions.
// Thus, we just concatenate all cells with "+" to have the same effect (see issue#59 for more detail)
targetFormulaString = replacementString.replaceAll(",", "+");
System.out.println(targetFormulaString);
} else {
targetFormulaString = targetFormulaString.replaceAll(Util.regexJointedLookBehind + Util.sheetNameRegex(cellRefEntry) + Util.regexExcludePrefixSymbols + Pattern.quote(cellRefEntry.getKey().getCellName()), Matcher.quoteReplacement(replacementString));
}
}
for (Map.Entry> jointedCellRefEntry : jointedCellRefMap.entrySet()) {
List targetCellRefList = jointedCellRefEntry.getValue();
Collections.sort(targetCellRefList);
if( targetCellRefList.isEmpty() ) {
continue;
}
isFormulaCellRefsEmpty = false;
Map.Entry> cellRefMapEntryParam = new AbstractMap.SimpleImmutableEntry>(null, targetCellRefList);
List replacementCells = findFormulaCellRefReplacements(formulaSourceAreaRef, formulaTargetAreaRef, cellRefMapEntryParam);
String replacementString = Util.createTargetCellRef(replacementCells);
targetFormulaString = targetFormulaString.replaceAll(Pattern.quote(jointedCellRefEntry.getKey()), replacementString);
}
String sheetNameReplacementRegex = Pattern.quote(targetFormulaCellRef.getFormattedSheetName() + CellRefUtil.SHEET_NAME_DELIMITER);
targetFormulaString = targetFormulaString.replaceAll(sheetNameReplacementRegex, "");
if( isFormulaCellRefsEmpty ){
targetFormulaString = formulaCellData.getDefaultValue() != null ? formulaCellData.getDefaultValue() : "0";
}
if(!targetFormulaString.isEmpty()) {
transformer.setFormula(new CellRef(targetFormulaCellRef.getSheetName(),
targetFormulaCellRef.getRow(), targetFormulaCellRef.getCol()),
targetFormulaString);
}
}
}
}
private List findFormulaCellRefReplacements(AreaRef formulaSourceAreaRef, AreaRef formulaTargetAreaRef, Map.Entry> cellReferenceEntry) {
CellRef cellReference = cellReferenceEntry.getKey();
List cellReferenceTargets = cellReferenceEntry.getValue();
if( cellReference != null && !formulaSourceAreaRef.contains(cellReference) ){
// this cell is outside of the formula cell area. so we just return all the cell reference targets `as is`
return cellReferenceTargets;
}
// the source cell reference is inside parent formula area. so let's find target cells related to particular transformation
// we'll iterate through all target cell references and find all the ones which belong to the target formula area
List relevantCellRefs = findRelevantCellReferences(cellReferenceTargets, formulaTargetAreaRef);
return relevantCellRefs;
}
private List findRelevantCellReferences(List cellReferenceTargets, AreaRef targetFormulaArea) {
List relevantCellRefs = new ArrayList<>(cellReferenceTargets.size());
for(CellRef targetCellRef: cellReferenceTargets){
if( targetFormulaArea.contains(targetCellRef)){
relevantCellRefs.add(targetCellRef);
}
}
return relevantCellRefs;
}
}