package org.apache.poi.xssf.usermodel;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationErrorStyle;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationOperator.Enum;
public class XSSFDataValidation implements DataValidation {
private static final int MAX_TEXT_LENGTH = 255;
private CTDataValidation ctDdataValidation;
private XSSFDataValidationConstraint validationConstraint;
private CellRangeAddressList regions;
static Map<Integer, STDataValidationOperator.Enum> operatorTypeMappings = new HashMap<>();
static Map<STDataValidationOperator.Enum, Integer> operatorTypeReverseMappings = new HashMap<>();
static Map<Integer, STDataValidationType.Enum> validationTypeMappings = new HashMap<>();
static Map<STDataValidationType.Enum, Integer> validationTypeReverseMappings = new HashMap<>();
static Map<Integer, STDataValidationErrorStyle.Enum> errorStyleMappings = new HashMap<>();
static Map<STDataValidationErrorStyle.Enum, Integer> reverseErrorStyleMappings;
static {
errorStyleMappings.put(DataValidation.ErrorStyle.INFO, STDataValidationErrorStyle.INFORMATION);
errorStyleMappings.put(DataValidation.ErrorStyle.STOP, STDataValidationErrorStyle.STOP);
errorStyleMappings.put(DataValidation.ErrorStyle.WARNING, STDataValidationErrorStyle.WARNING);
reverseErrorStyleMappings = MapUtils.invertMap(errorStyleMappings);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.BETWEEN,STDataValidationOperator.BETWEEN);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.NOT_BETWEEN,STDataValidationOperator.NOT_BETWEEN);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.EQUAL,STDataValidationOperator.EQUAL);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.NOT_EQUAL,STDataValidationOperator.NOT_EQUAL);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.GREATER_THAN,STDataValidationOperator.GREATER_THAN);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL,STDataValidationOperator.GREATER_THAN_OR_EQUAL);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.LESS_THAN,STDataValidationOperator.LESS_THAN);
operatorTypeMappings.put(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,STDataValidationOperator.LESS_THAN_OR_EQUAL);
for( Map.Entry<Integer,STDataValidationOperator.Enum> entry : operatorTypeMappings.entrySet() ) {
operatorTypeReverseMappings.put(entry.getValue(),entry.getKey());
}
validationTypeMappings.put(DataValidationConstraint.ValidationType.FORMULA,STDataValidationType.CUSTOM);
validationTypeMappings.put(DataValidationConstraint.ValidationType.DATE,STDataValidationType.DATE);
validationTypeMappings.put(DataValidationConstraint.ValidationType.DECIMAL,STDataValidationType.DECIMAL);
validationTypeMappings.put(DataValidationConstraint.ValidationType.LIST,STDataValidationType.LIST);
validationTypeMappings.put(DataValidationConstraint.ValidationType.ANY,STDataValidationType.NONE);
validationTypeMappings.put(DataValidationConstraint.ValidationType.TEXT_LENGTH,STDataValidationType.TEXT_LENGTH);
validationTypeMappings.put(DataValidationConstraint.ValidationType.TIME,STDataValidationType.TIME);
validationTypeMappings.put(DataValidationConstraint.ValidationType.INTEGER,STDataValidationType.WHOLE);
for( Map.Entry<Integer,STDataValidationType.Enum> entry : validationTypeMappings.entrySet() ) {
validationTypeReverseMappings.put(entry.getValue(),entry.getKey());
}
}
XSSFDataValidation(CellRangeAddressList regions,CTDataValidation ctDataValidation) {
this(getConstraint(ctDataValidation), regions, ctDataValidation);
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint,CellRangeAddressList regions,CTDataValidation ctDataValidation) {
super();
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
}
CTDataValidation getCtDdataValidation() {
return ctDdataValidation;
}
public void createErrorBox(String title, String text) {
if(title != null && title.length() > MAX_TEXT_LENGTH) {
throw new IllegalStateException("Error-title cannot be longer than 32 characters, but had: " + title);
}
if(text != null && text.length() > MAX_TEXT_LENGTH) {
throw new IllegalStateException("Error-text cannot be longer than 255 characters, but had: " + text);
}
ctDdataValidation.setErrorTitle(encodeUtf(title));
ctDdataValidation.setError(encodeUtf(text));
}
public void createPromptBox(String title, String text) {
if(title != null && title.length() > MAX_TEXT_LENGTH) {
throw new IllegalStateException("Error-title cannot be longer than 32 characters, but had: " + title);
}
if(text != null && text.length() > MAX_TEXT_LENGTH) {
throw new IllegalStateException("Error-text cannot be longer than 255 characters, but had: " + text);
}
ctDdataValidation.setPromptTitle(encodeUtf(title));
ctDdataValidation.setPrompt(encodeUtf(text));
}
private String encodeUtf(String text) {
if(text == null) {
return null;
}
StringBuilder builder = new StringBuilder();
for(char c : text.toCharArray()) {
if(c < 32) {
builder.append("_x").append(c < 16 ? "000" : "00").append(Integer.toHexString(c)).append("_");
} else {
builder.append(c);
}
}
return builder.toString();
}
public boolean getEmptyCellAllowed() {
return ctDdataValidation.getAllowBlank();
}
public String getErrorBoxText() {
return ctDdataValidation.getError();
}
public String getErrorBoxTitle() {
return ctDdataValidation.getErrorTitle();
}
public int getErrorStyle() {
return reverseErrorStyleMappings.get(ctDdataValidation.getErrorStyle());
}
public String getPromptBoxText() {
return ctDdataValidation.getPrompt();
}
public String getPromptBoxTitle() {
return ctDdataValidation.getPromptTitle();
}
public boolean getShowErrorBox() {
return ctDdataValidation.getShowErrorMessage();
}
public boolean getShowPromptBox() {
return ctDdataValidation.getShowInputMessage();
}
public boolean getSuppressDropDownArrow() {
return !ctDdataValidation.getShowDropDown();
}
public DataValidationConstraint getValidationConstraint() {
return validationConstraint;
}
public void setEmptyCellAllowed(boolean allowed) {
ctDdataValidation.setAllowBlank(allowed);
}
public void setErrorStyle(int errorStyle) {
ctDdataValidation.setErrorStyle(errorStyleMappings.get(errorStyle));
}
public void setShowErrorBox(boolean show) {
ctDdataValidation.setShowErrorMessage(show);
}
public void setShowPromptBox(boolean show) {
ctDdataValidation.setShowInputMessage(show);
}
public void setSuppressDropDownArrow(boolean suppress) {
if (validationConstraint.getValidationType()==ValidationType.LIST) {
ctDdataValidation.setShowDropDown(!suppress);
}
}
public CellRangeAddressList getRegions() {
return regions;
}
public String prettyPrint() {
StringBuilder builder = new StringBuilder();
for(CellRangeAddress address : regions.getCellRangeAddresses()) {
builder.append(address.formatAsString());
}
builder.append(" => ");
builder.append(this.validationConstraint.prettyPrint());
return builder.toString();
}
private static XSSFDataValidationConstraint getConstraint(CTDataValidation ctDataValidation) {
String formula1 = ctDataValidation.getFormula1();
String formula2 = ctDataValidation.getFormula2();
Enum operator = ctDataValidation.getOperator();
org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType.Enum type = ctDataValidation.getType();
Integer validationType = XSSFDataValidation.validationTypeReverseMappings.get(type);
Integer operatorType = XSSFDataValidation.operatorTypeReverseMappings.get(operator);
return new XSSFDataValidationConstraint(validationType,operatorType, formula1,formula2);
}
}