public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
}
private string d2007()
{
string fileName = "G:/" + DateTime.Now.Ticks.ToString() + ".xlsx";
XSSFWorkbook wk = new XSSFWorkbook();
ISheet sheet = wk.CreateSheet();
CellRangeAddressList regions = new CellRangeAddressList(0, 5, 2, 2);
CT_DataValidation ctDataValidation = new CT_DataValidation();
ctDataValidation.allowBlank = true;
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.showInputMessage = true;
ctDataValidation.showErrorMessage = true;
ctDataValidation.sqref = "C1:C88";
ctDataValidation.formula1 = "\"itemA,itemB,itemC\"";
//ctDataValidation.showDropDown = true;
//XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(new string[] { "itemA", "itemB", "itemC" });
XSSFDataValidation dataValidate = new XSSFDataValidation(regions, ctDataValidation);
//dataValidate= new XSSFDataValidation()
sheet.AddValidationData(dataValidate);
using (FileStream fs = File.OpenWrite(fileName))
{
wk.Write(fs);
}
return(fileName);
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = ST_DataValidationErrorStyle.stop;
this.ctDdataValidation.allowBlank = true;
}
private XSSFDataValidationConstraint GetConstraint(CT_DataValidation ctDataValidation)
{
string formula1 = ctDataValidation.formula1;
string formula2 = ctDataValidation.formula2;
ST_DataValidationOperator index = ctDataValidation.@operator;
ST_DataValidationType type = ctDataValidation.type;
return(new XSSFDataValidationConstraint(XSSFDataValidation.validationTypeReverseMappings[type], XSSFDataValidation.operatorTypeReverseMappings[index], formula1, formula2));
}
public XSSFDataValidation(CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = GetConstraint(ctDataValidation);
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = (ST_DataValidationErrorStyle.stop);
this.ctDdataValidation.allowBlank = (true);
}
private static XSSFDataValidationConstraint GetConstraint(CT_DataValidation ctDataValidation)
{
XSSFDataValidationConstraint constraint = null;
String formula1 = ctDataValidation.formula1;
String formula2 = ctDataValidation.formula2;
ST_DataValidationOperator operator1 = ctDataValidation.@operator;
ST_DataValidationType type = ctDataValidation.type;
int validationType = XSSFDataValidation.validationTypeReverseMappings[type];
int operatorType = XSSFDataValidation.operatorTypeReverseMappings[operator1];
constraint = new XSSFDataValidationConstraint(validationType, operatorType, formula1, formula2);
return(constraint);
}
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint constraint1 = (XSSFDataValidationConstraint)constraint;
CT_DataValidation ctDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case 0:
ctDataValidation.type = ST_DataValidationType.none;
break;
case 1:
ctDataValidation.type = ST_DataValidationType.whole;
break;
case 2:
ctDataValidation.type = ST_DataValidationType.@decimal;
break;
case 3:
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.formula1 = constraint.Formula1;
break;
case 4:
ctDataValidation.type = ST_DataValidationType.date;
break;
case 5:
ctDataValidation.type = ST_DataValidationType.time;
break;
case 6:
ctDataValidation.type = ST_DataValidationType.textLength;
break;
case 7:
ctDataValidation.type = ST_DataValidationType.custom;
break;
default:
ctDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != 0 && validationType != 3)
{
ctDataValidation.@operator = ST_DataValidationOperator.between;
if (XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
{
ctDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
}
if (constraint.Formula1 != null)
{
ctDataValidation.formula1 = constraint.Formula1;
}
if (constraint.Formula2 != null)
{
ctDataValidation.formula2 = constraint.Formula2;
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List <string> stringList = new List <string>();
for (int index = 0; index < cellRangeAddresses.Length; ++index)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[index];
stringList.Add(cellRangeAddress.FormatAsString());
}
ctDataValidation.sqref = stringList;
return((IDataValidation) new XSSFDataValidation(constraint1, cellRangeAddressList, ctDataValidation));
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = ST_DataValidationOperator.between;
if (XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
{
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
}
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
string sqref = string.Empty;
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
if (sqref.Length == 0)
{
sqref = cellRangeAddress.FormatAsString();
}
else
{
sqref = " " + cellRangeAddress.FormatAsString();
}
}
newDataValidation.sqref = sqref;
newDataValidation.allowBlank = (true);
newDataValidation.errorStyle = ST_DataValidationErrorStyle.stop;
return(new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation));
}
public XSSFDataValidation(CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: this(GetConstraint(ctDataValidation), regions, ctDataValidation)
{
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List <String> sqref = new List <String>();
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
sqref.Add(cellRangeAddress.FormatAsString());
}
newDataValidation.sqref = (sqref);
return(new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation));
}
