org.jxls.builder.xls.XlsCommentAreaBuilder Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of jxls-core Show documentation
Show all versions of jxls-core Show documentation
Small library for Excel generation based on XLS templates
The newest version!
package org.jxls.builder.xls;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.jxls.area.Area;
import org.jxls.area.CommandData;
import org.jxls.area.XlsArea;
import org.jxls.builder.AreaBuilder;
import org.jxls.command.Command;
import org.jxls.command.EachCommand;
import org.jxls.command.GridCommand;
import org.jxls.command.IfCommand;
import org.jxls.command.ImageCommand;
import org.jxls.command.MergeCellsCommand;
import org.jxls.command.UpdateCellCommand;
import org.jxls.common.AreaRef;
import org.jxls.common.CellData;
import org.jxls.common.CellRef;
import org.jxls.transform.Transformer;
import org.jxls.util.Util;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
/**
* Builds {@link org.jxls.area.XlsArea} from Excel comments in the Excel template
*
* Command syntax
* A command is specified in a cell comment like the following
* jx:COMMAND_NAME(attr1="value1" attr2="value2" ... attrN="valueN" lastCell="LAST_CELL" areas=["AREA_REF1", "AREA_REF2", ... , "AREA_REFN"])
* where
* - COMMAND_NAME - the name of the command
*
* - attr1, attr2, ... attrN, value1, value2, ... , valueN - command attributes and their values
*
* - lastCell, LAST_CELL - attribute name and cell reference value specifying the last cell where this command is placed
* in the parent area. The first cell is defined by the cell where the comment is defined.
* If there is no "areas" attribute defined it also defines the single area for this command to operate on.
*
* - AREA_REF1, AREA_REF2, ... , AREA_REFN - additional area references for this command (if supported by the command)
* 'areas' attribute is optional and only needed for commands which work with more than one area.
* If there is only a single area for the command it is usually enough to define just lastCell attribute
*
*
* Multiple commands can be specified in a single cell comment separated by new lines.
* In this case the area of the first command will contain the second command and so on.
*
* This class defines the following pre-defined mappings between the command names and Command classes:
* "jx:each" - {@link org.jxls.command.EachCommand}
* "jx:if" - {@link IfCommand}
* "jx:area" - {@link AreaCommand} - for defining the top areas
* "jx:image" - {@link ImageCommand}
* "jx:grid" - {@link GridCommand}
* "jx:updateCell" - {@link UpdateCellCommand}
* "jx:mergeCells" - {@link MergeCellsCommand}
*
* Custom command classes mapping can be added using addCommandMapping(String commandName, Class clazz) method
*
* Command examples
*
* jx:if(condition="employee.payment <= 2000", lastCell="F9", areas=["A9:F9","A30:F30"])
*
* Here we define {@link IfCommand} with a condition expression 'employee.payment <= 2000' and first area (if-area) "A9:F9"
* and second area (else-area) "A30:F30". The command is added to the parent area covering a range from the cell where
* the comment is placed and to the cell defined in lastCell attribute "F9".
*
* jx:each(items="department.staff", var="employee", lastCell="F9")
*
* Here we define {@link org.jxls.command.EachCommand} with items attribute set to 'department.staff' and var attribute set to 'employee'.
* The command area is defined from the cell where the comment is defined and till the lastCell "F9"
*
* jx:area(lastCell="G26")
*
* Specifies the top area range with {@link AreaCommand} starting from the cell where the comment is defined and in
* the cell defined in lastCell("G26").
*
* Note: Clearing comments from the cells appears to have some issues in POI so should be used with caution.
* The easiest approach will be just removing the template sheet.
*
* @author Leonid Vysochyn
*/
public class XlsCommentAreaBuilder implements AreaBuilder {
private static Logger logger = LogManager.getLogger(XlsCommentAreaBuilder.class);
public static final String COMMAND_PREFIX = "jx:";
private static final String ATTR_PREFIX = "(";
private static final String ATTR_SUFFIX = ")";
/*
* In addition to normal (straight) single and double quotes, this regex
* includes the following commonly occurring quote-like characters (some
* of which have been observed in recent versions of LibreOffice):
*
* U+201C - LEFT DOUBLE QUOTATION MARK
* U+201D - RIGHT DOUBLE QUOTATION MARK
* U+201E - DOUBLE LOW-9 QUOTATION MARK
* U+201F - DOUBLE HIGH-REVERSED-9 QUOTATION MARK
* U+2033 - DOUBLE PRIME
* U+2036 - REVERSED DOUBLE PRIME
* U+2018 - LEFT SINGLE QUOTATION MARK
* U+2019 - RIGHT SINGLE QUOTATION MARK
* U+201A - SINGLE LOW-9 QUOTATION MARK
* U+201B - SINGLE HIGH-REVERSED-9 QUOTATION MARK
* U+2032 - PRIME
* U+2035 - REVERSED PRIME
*/
private static final String ATTR_REGEX = "\\s*\\w+\\s*=\\s*([\"|'\u201C\u201D\u201E\u201F\u2033\u2036\u2018\u2019\u201A\u201B\u2032\u2035])(?:(?!\\1).)*\\1";
private static final Pattern ATTR_REGEX_PATTERN = Pattern.compile(ATTR_REGEX);
private static final String AREAS_ATTR_REGEX = "areas\\s*=\\s*\\[[^]]*]";
private static final Pattern AREAS_ATTR_REGEX_PATTERN = Pattern.compile(AREAS_ATTR_REGEX);
private static Map> commandMap = new ConcurrentHashMap<>();
private static final String LAST_CELL_ATTR_NAME = "lastCell";
static {
commandMap.put(EachCommand.COMMAND_NAME, EachCommand.class);
commandMap.put(IfCommand.COMMAND_NAME, IfCommand.class);
commandMap.put(AreaCommand.COMMAND_NAME, AreaCommand.class);
commandMap.put(ImageCommand.COMMAND_NAME, ImageCommand.class);
commandMap.put(GridCommand.COMMAND_NAME, GridCommand.class);
commandMap.put(UpdateCellCommand.COMMAND_NAME, UpdateCellCommand.class);
commandMap.put(MergeCellsCommand.COMMAND_NAME, MergeCellsCommand.class);
}
private Transformer transformer;
private boolean clearTemplateCells = true;
public XlsCommentAreaBuilder() {
}
public XlsCommentAreaBuilder(Transformer transformer) {
this.transformer = transformer;
}
public XlsCommentAreaBuilder(Transformer transformer, boolean clearTemplateCells) {
this(transformer);
this.clearTemplateCells = clearTemplateCells;
}
@Override
public Transformer getTransformer() {
return transformer;
}
@Override
public void setTransformer(Transformer transformer) {
this.transformer = transformer;
}
public static void addCommandMapping(String commandName, Class clazz) {
commandMap.put(commandName, clazz);
}
/**
* Builds a list of {@link org.jxls.area.XlsArea} objects defined by top level AreaCommand markup ("jx:area")
* containing a tree of all nested commands
*
* @return Area list
*/
@Override
public List build() {
List userAreas = new ArrayList();
List commentedCells = transformer.getCommentedCells();
List allCommands = new ArrayList();
List allAreas = new ArrayList();
for (CellData cellData : commentedCells) {
String comment = cellData.getCellComment();
List commandDatas = buildCommands(cellData, comment);
for (CommandData commandData : commandDatas) {
if (commandData.getCommand() instanceof AreaCommand) {
XlsArea userArea = new XlsArea(commandData.getAreaRef(), transformer);
allAreas.add(userArea);
userAreas.add(userArea);
} else {
List areas = commandData.getCommand().getAreaList();
allAreas.addAll(areas);
allCommands.add(commandData);
}
}
}
for (int i = 0; i < allCommands.size(); i++) {
CommandData commandData = allCommands.get(i);
AreaRef commandAreaRef = commandData.getAreaRef();
List commandAreas = commandData.getCommand().getAreaList();
Area minArea = null;
List minAreas = new ArrayList();
for (Area area : allAreas) {
if (commandAreas.contains(area) || !area.getAreaRef().contains(commandAreaRef)) continue;
boolean belongsToNextCommand = false;
for (int j = i + 1; j < allCommands.size(); j++) {
CommandData nextCommand = allCommands.get(j);
if (nextCommand.getCommand().getAreaList().contains(area)) {
belongsToNextCommand = true;
break;
}
}
if (belongsToNextCommand || (minArea != null && !minArea.getAreaRef().contains(area.getAreaRef()))) continue;
if (minArea != null && minArea.equals(area)) {
minAreas.add(area);
} else {
minArea = area;
minAreas.clear();
minAreas.add( minArea );
}
}
for (Area area : minAreas) {
area.addCommand(commandData.getAreaRef(), commandData.getCommand());
}
}
if (clearTemplateCells) {
for (Area area : userAreas) {
((XlsArea) area).clearCells();
}
}
return userAreas;
}
private List buildCommands(CellData cellData, String text) {
String[] commentLines = text.split("\\n");
List commandDatas = new ArrayList();
for (String commentLine : commentLines) {
String line = commentLine.trim();
if (isCommandString(line)) {
int nameEndIndex = line.indexOf(ATTR_PREFIX, COMMAND_PREFIX.length());
if (nameEndIndex < 0) {
String errMsg = "Failed to parse command line [" + line + "]. Expected '" + ATTR_PREFIX + "' symbol.";
logger.error(errMsg);
throw new IllegalStateException(errMsg);
}
String commandName = line.substring(COMMAND_PREFIX.length(), nameEndIndex).trim();
Map attrMap = buildAttrMap(line, nameEndIndex);
CommandData commandData = createCommandData(cellData, commandName, attrMap);
if (commandData != null) {
commandDatas.add(commandData);
List areas = buildAreas(cellData, line);
for (Area area : areas) {
commandData.getCommand().addArea(area);
}
if (areas.isEmpty()) {
Area area = new XlsArea(commandData.getAreaRef(), transformer);
commandData.getCommand().addArea(area);
}
}
}
}
return commandDatas;
}
public static boolean isCommandString(String str) {
return str.startsWith(COMMAND_PREFIX) && !str.startsWith(CellData.JX_PARAMS_PREFIX);
}
private List buildAreas(CellData cellData, String commandLine) {
List areas = new ArrayList();
Matcher areasAttrMatcher = AREAS_ATTR_REGEX_PATTERN.matcher(commandLine);
if (areasAttrMatcher.find()) {
String areasAttr = areasAttrMatcher.group();
List areaRefs = extractAreaRefs(cellData, areasAttr);
for (AreaRef areaRef : areaRefs) {
Area area = new XlsArea(areaRef, transformer);
areas.add(area);
}
}
return areas;
}
private List extractAreaRefs(CellData cellData, String areasAttr) {
List areaRefs = new ArrayList();
Matcher areaRefMatcher = Util.regexAreaRefPattern.matcher(areasAttr);
while (areaRefMatcher.find()) {
String areaRefName = areaRefMatcher.group();
AreaRef areaRef = new AreaRef(areaRefName);
if (areaRef.getSheetName() == null || areaRef.getSheetName().trim().length() == 0) {
areaRef.getFirstCellRef().setSheetName(cellData.getSheetName());
}
areaRefs.add(areaRef);
}
return areaRefs;
}
private Map buildAttrMap(String commandLine, int nameEndIndex) {
int paramsEndIndex = commandLine.lastIndexOf(ATTR_SUFFIX);
if (paramsEndIndex < 0) {
String errMsg = "Failed to parse command line [" + commandLine + "]. Expected '" + ATTR_SUFFIX + "' symbol.";
logger.error(errMsg);
throw new IllegalArgumentException(errMsg);
}
String attrString = commandLine.substring(nameEndIndex + 1, paramsEndIndex).trim();
return parseCommandAttributes(attrString);
}
private CommandData createCommandData(CellData cellData, String commandName, Map attrMap) {
Class clazz = commandMap.get(commandName);
if (clazz == null) {
logger.warn("Failed to find Command class mapped to command name '" + commandName + "'");
return null;
}
try {
Command command = clazz.newInstance();
for (Map.Entry attr : attrMap.entrySet()) {
if (!attr.getKey().equals(LAST_CELL_ATTR_NAME)) {
Util.setObjectProperty(command, attr.getKey(), attr.getValue(), true);
}
}
String lastCellRef = attrMap.get(LAST_CELL_ATTR_NAME);
if (lastCellRef == null) {
logger.warn("Failed to find last cell ref attribute '" + LAST_CELL_ATTR_NAME + "' for command '"
+ commandName + "' in cell " + cellData.getCellRef());
return null;
}
CellRef lastCell = new CellRef(lastCellRef);
if (lastCell.getSheetName() == null || lastCell.getSheetName().trim().length() == 0) {
lastCell.setSheetName(cellData.getSheetName());
}
return new CommandData(new AreaRef(cellData.getCellRef(), lastCell), command);
} catch (Exception e) {
logger.warn("Failed to instantiate command class '" + clazz.getName() + "' mapped to command name '" + commandName + "'", e);
return null;
}
}
private Map parseCommandAttributes(String attrString) {
Map attrMap = new LinkedHashMap();
Matcher attrMatcher = ATTR_REGEX_PATTERN.matcher(attrString);
while (attrMatcher.find()) {
String attrData = attrMatcher.group();
int attrNameEndIndex = attrData.indexOf("=");
String attrName = attrData.substring(0, attrNameEndIndex).trim();
String attrValuePart = attrData.substring(attrNameEndIndex + 1).trim();
String attrValue = attrValuePart.substring(1, attrValuePart.length() - 1);
attrMap.put(attrName, attrValue);
}
return attrMap;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy