public void TestCreateMultipleRegionsValidation()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0);
cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1);
cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2);
XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation;
sheet.AddValidationData(dataValidation);
Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]);
Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]);
Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]);
Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref);
}
finally
{
wb.Close();
}
}
static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
foreach (DataValidation validation in validations)
{
if ((validation.List == null || validation.List.Count == 0) && validation.Name == null)
{
throw new InvalidOperationException("Validation is invalid");
}
IDataValidationConstraint constraint = validation.Name != null?
helper.CreateFormulaListConstraint(validation.Name) :
helper.CreateExplicitListConstraint(validation.List.ToArray());
var range = new CellRangeAddressList(
validation.Range.RowStart ?? 0,
validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1,
validation.Range.ColumnStart ?? 0,
validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1);
IDataValidation dataValidation = helper.CreateValidation(constraint, range);
sheet.AddValidationData(dataValidation);
}
}
public void TestGetDataValidationsListFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint("A2");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public override Action <ISheet> SetExcelWorksheet()
{
return((s) =>
{
base.SetExcelWorksheet()(s);
var address = typeof(Student1).GetCellAddress(nameof(Student1.Email));
address = $"{address}2:{address}1000";
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)s);
//创建验证规则
IDataValidationConstraint constraint = helper.CreateCustomConstraint($"=COUNTIF({address},\"?*@*.*\")");
var validation = helper.CreateValidation(constraint, new CellRangeAddressList(1, 1000, 0, 0));
//设置约束提示信息
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true;
validation.ShowPromptBox = true;
validation.CreateErrorBox("请输入邮箱ErrorTitle", "请输入邮箱Error");
validation.CreatePromptBox("自定义错误信息PromptTitle", "自定义错误Prompt");
validation.ErrorStyle = 1;
s.AddValidationData(validation);
});
}
public void TestGetDataValidationsFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void TestGetDataValidationsDecimal(){
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
public DataValidationException(string message,string propertyName,IDataValidationConstraint constraint)
: base(message)
{
_propertyName=propertyName;
_constraint=constraint;
_thrownByType=ThrownByTypeEnum.Property;
}
public void TestGetDataValidationsListExplicit()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new String[] { "aaa",
"bbb", "ccc" });
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
String[] values = c.ExplicitListValues;
Assert.AreEqual(3, values.Length);
Assert.AreEqual("aaa", values[0]);
Assert.AreEqual("bbb", values[1]);
Assert.AreEqual("ccc", values[2]);
}
public DataValidationException(string message, string methodName, string parameterName, IDataValidationConstraint constraint) : base(message)
{
_methodName = methodName;
_parameterName = parameterName;
_constraint = constraint;
_thrownByType = ThrownByTypeEnum.MethodParameter;
}
private static void CreateDropDownListForExcel(ISheet sheet, string[] dropDownValues, int startRow, int lastRow, int column)
{
if (sheet == null)
{
return;
}
IWorkbook workbook = sheet.Workbook;
string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
for (int i = 0, length = dropDownValues.Length; i < length; i++)
{
string name = dropDownValues[i];
IRow row = hiddenSheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(name);
}
IName namedCell = workbook.CreateName();
namedCell.NameName = dropDownName;
namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Length);
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet as HSSFSheet);
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(dropDownName);
CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
HSSFDataValidation validation = (HSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
sheet.AddValidationData(validation);
}
public void Test53965()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
List <IDataValidation> lst = sheet.GetDataValidations(); //<-- works
Assert.AreEqual(0, lst.Count);
//create the cell that will have the validation applied
sheet.CreateRow(0).CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.GetDataValidations();
Assert.AreEqual(1, lst.Count);
}
finally
{
wb.Close();
}
}
private void AddValidationInternal(int operatorType, string firstFormula,
string secondFormula, int errorStyle, string ruleDescr, string promptDescr,
bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
string[] explicitListValues)
{
int rowNum = _currentRowIndex++;
IDataValidationHelper dataValidationHelper = _sheet.GetDataValidationHelper();
IDataValidationConstraint dc = CreateConstraint(dataValidationHelper, operatorType, firstFormula, secondFormula, explicitListValues);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(rowNum, rowNum, 0, 0));
dv.EmptyCellAllowed = (/*setter*/ allowEmpty);
dv.ErrorStyle = (/*setter*/ errorStyle);
dv.CreateErrorBox("Invalid Input", "Something is wrong - check condition!");
dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");
dv.ShowPromptBox = (/*setter*/ inputBox);
dv.ShowErrorBox = (/*setter*/ errorBox);
dv.SuppressDropDownArrow = (/*setter*/ suppressDropDown);
_sheet.AddValidationData(dv);
WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
inputBox, errorBox);
if (_cellStyle != null)
{
IRow row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
ICell cell = row.CreateCell(0);
cell.CellStyle = (/*setter*/ _cellStyle);
}
WriteOtherSettings(_sheet, _style_1, promptDescr);
}
/**
* Constructor which Initializes the cell range on which this object will be
* applied
* @param constraint
*/
public HSSFDataValidation(CellRangeAddressList regions, IDataValidationConstraint constraint)
{
_regions = regions;
//FIXME: This cast can be avoided.
_constraint = (DVConstraint)constraint;
}
/**
* Constructor which Initializes the cell range on which this object will be
* applied
* @param constraint
*/
public HSSFDataValidation(CellRangeAddressList regions, IDataValidationConstraint constraint)
{
_regions = regions;
//FIXME: This cast can be avoided.
_constraint = (DVConstraint)constraint;
}
public void TestGetDataValidationsDate()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List<IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateDateConstraint(OperatorType.EQUAL,
"2014/10/25", null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DATE, c.GetValidationType());
Assert.AreEqual(OperatorType.EQUAL, c.Operator);
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public DataValidationException(string message,string methodName,string parameterName,IDataValidationConstraint constraint)
: base(message)
{
_methodName=methodName;
_parameterName=parameterName;
_constraint=constraint;
_thrownByType=ThrownByTypeEnum.MethodParameter;
}
static void Main(string[] args)
{
string workbookName = "test.xlsx";
IWorkbook workbook = null;
ISheet sheet = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.CreateSheet("Linked Validations");
BuildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.CreateFormulaListConstraint(
"INDIRECT(UPPER($A$1))");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
FileStream sw = File.OpenWrite(workbookName);
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// 创建列表
/// </summary>
/// <param name="helper"></param>
/// <param name="ListData"></param>
/// <returns></returns>
internal static IDataValidation getValidationExplicitList(IDataValidationHelper helper, CellRangeAddressList range, string[] ListData)
{
IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(ListData);
IDataValidation validation = helper.CreateValidation(constraint, range);
//validation.ShowPromptBox = true;
validation.ShowErrorBox = true;
return(validation);
}
public void SetSheetValidationForListConstraint(string sheetName, string rangeName, MergeCellRange cellRange)
{
CellRangeAddressList rangeList = new CellRangeAddressList(cellRange.FirstRowIndex, cellRange.LastRowIndex, cellRange.FirstColumnIndex, cellRange.LastColumnIndex);
ISheet sheet = _workbook.GetSheet(sheetName);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint(rangeName);
HSSFDataValidation validation = (HSSFDataValidation)dataValidationHelper.CreateValidation(constraint, rangeList);
sheet.AddValidationData(validation);
}
internal static IDataValidation getValidationYearMonth(IDataValidationHelper helper, CellRangeAddressList range)
{
IDataValidationConstraint constraint = helper.CreateintConstraint(
ST_DataValidationOperator.between.GetHashCode(), "200001", "202012");
IDataValidation validation = helper.CreateValidation(constraint, range);
validation.CreateErrorBox("error", "请输入YYYYMM样式的年月，如2012年8月为【201208】");
validation.ShowErrorBox = true;
return(validation);
}
/// <summary>
/// 设置单元格验证(数字)
/// 创建者：戚鹏
/// 创建日期：2013.5.20
/// </summary>
/// <param name="RowCount"></param>
/// <param name="ColNum"></param>
/// <param name="FirstValue"></param>
/// <param name="LastValue"></param>
/// <returns></returns>
internal static IDataValidation getValidationInt(IDataValidationHelper helper, CellRangeAddressList range,
int from = 0, int to = 99999999)
{
IDataValidationConstraint constraint = helper.CreateintConstraint(
ST_DataValidationOperator.between.GetHashCode(), from.ToString(), to.ToString());
IDataValidation validation = helper.CreateValidation(constraint, range);
//validation.CreateErrorBox("error", "You must input a numeric between 1 and 50.");
validation.ShowErrorBox = true;
return(validation);
}
public void TestAddToExistingSheet()
{
// dvEmpty.xls is a simple one sheet workbook. With a DataValidations header record but no
// DataValidations. It's important that the example has one SHEETPROTECTION record.
// Such a workbook can be Created in Excel (2007) by Adding datavalidation for one cell
// and then deleting the row that Contains the cell.
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
ISheet sheet = wb.GetSheetAt(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dc = dataValidationHelper.CreateintConstraint(OperatorType.EQUAL, "42", null);
IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));
dv.EmptyCellAllowed = (/*setter*/ false);
dv.ErrorStyle = (/*setter*/ ERRORSTYLE.STOP);
dv.ShowPromptBox = (/*setter*/ true);
dv.CreateErrorBox("Xxx", "Yyy");
dv.SuppressDropDownArrow = (/*setter*/ true);
sheet.AddValidationData(dv);
MemoryStream baos = new MemoryStream();
try
{
wb.Write(baos);
}
catch (IOException e)
{
throw new RuntimeException(e);
}
byte[] wbData = baos.ToArray();
byte[] dvHeaderRecStart = { (byte)0xB2, 0x01, 0x12, 0x00, };
int dvHeaderOffset = FindIndex(wbData, dvHeaderRecStart);
Assert.IsTrue(dvHeaderOffset > 0);
int nextRecIndex = dvHeaderOffset + 22;
int nextSid
= ((wbData[nextRecIndex + 0] << 0) & 0x00FF)
+ ((wbData[nextRecIndex + 1] << 8) & 0xFF00)
;
// nextSid should be for a DVRecord. If anything comes between the DV header record
// and the DV records, Excel will not be able to open the workbook without error.
if (nextSid == 0x0867)
{
throw new AssertionException("Identified bug 45519");
}
Assert.AreEqual(DVRecord.sid, nextSid);
}
/// <summary>
/// 添加下拉框
/// </summary>
/// <param name="exSheet">Excel表单对象</param>
/// <param name="field">字段规则</param>
/// <param name="rowIndex">起始行号</param>
/// <param name="rowSpan">结束行号</param>
public static void AddDropDownList(ISheet exSheet, string[] values, int colIndex, int rowIndex, int colSpan = 1, int rowSpan = 1)
{
if (values.Length > 0)
{
IDataValidationHelper helper = exSheet.GetDataValidationHelper();
IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(values);
CellRangeAddressList rangeList = new CellRangeAddressList(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
//exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
exSheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
}
}
private XSSFDataValidation CreateValidation(XSSFSheet sheet)
{
//create the cell that will have the validation applied
IRow row = sheet.CreateRow(0);
row.CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("true");
XSSFDataValidation validation = (XSSFDataValidation)dataValidationHelper.CreateValidation(constraint, new CellRangeAddressList(0, 0, 0, 0));
return(validation);
}
public void Render(ICell cell)
{
if (ValueList.Length > 0)
{
ISheet sheet = cell.Sheet;
IDataValidationHelper helper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(ValueList);
CellRangeAddressList rangeList = new CellRangeAddressList(FillArea.RowIndex, FillArea.RowIndex + FillArea.RowCount - 1,
FillArea.ColIndex, FillArea.ColIndex + FillArea.ColCount - 1);
//DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
//exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
sheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
}
}
public void TestGetDataValidationsAny()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY,
OperatorType.IGNORED, null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.EmptyCellAllowed = (/*setter*/ true);
validation.CreateErrorBox("error-title", "error-text");
validation.CreatePromptBox("prompt-title", "prompt-text");
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
{
CellRangeAddressList regions = dv.Regions;
Assert.AreEqual(1, regions.CountRanges());
CellRangeAddress Address = regions.GetCellRangeAddress(0);
Assert.AreEqual(1, Address.FirstRow);
Assert.AreEqual(2, Address.LastRow);
Assert.AreEqual(3, Address.FirstColumn);
Assert.AreEqual(4, Address.LastColumn);
}
Assert.AreEqual(true, dv.EmptyCellAllowed);
Assert.AreEqual(false, dv.SuppressDropDownArrow);
Assert.AreEqual(true, dv.ShowErrorBox);
Assert.AreEqual("error-title", dv.ErrorBoxTitle);
Assert.AreEqual("error-text", dv.ErrorBoxText);
Assert.AreEqual(true, dv.ShowPromptBox);
Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
Assert.AreEqual("prompt-text", dv.PromptBoxText);
IDataValidationConstraint c = dv.ValidationConstraint;
Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
Assert.AreEqual(OperatorType.IGNORED, c.Operator);
}
/// <summary>
/// 设置excel下拉列表
/// </summary>
/// <param name="sheet">excel工作表</param>
/// <param name="sheetName">下拉列表使用数据的sheet的名字</param>
/// <param name="rangeName">下拉列表使用数据的域</param>
/// <param name="names">待处理的姓名字符串</param>
/// <param name="count">数据表中数据行数，ref引用格式</param>
/// <param name="Row">所要设置的下拉列表所在行</param>
/// <param name="Col">所要设置的下拉列表所在列</param>
public static void SetDropDownList(ISheet sheet, string sheetName, string rangeName, string names, ref int count, int Row, int Col)
{
ISheet dataSheet = null;
IRow row = null;
ICell cell = null;
IName name = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
dataSheet = sheet.Workbook.GetSheet(sheetName); //获取存储下拉数据的sheet
if (dataSheet == null) //若不存在，则创建
{
dataSheet = sheet.Workbook.CreateSheet(sheetName);
}
//string[] list = new string[] { "123", "456", "789" };
string[] list = BuildDropData(names); //获取下拉数据
row = dataSheet.CreateRow(count++);
for (int i = 0; i < list.Count(); i++) //将数据写入sheet中
{
cell = row.CreateCell(i);
cell.SetCellValue(list[i]);
}
//生成一个列表引用区域,并唯一标识它
name = sheet.Workbook.CreateName();
name.RefersToFormula = string.Format("'{0}'!$A${1}:${2}${3}", sheetName, count, IndexToColumn(list.Count()), count);
name.NameName = rangeName.ToUpper() + Row.ToString();
addressList = new CellRangeAddressList(Row, Row, Col, Col); //设置生成下拉框的行和列
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint(rangeName.ToUpper() + Row.ToString());
validation = dvHelper.CreateValidation(dvConstraint, addressList); //绑定下拉框和作用区域
sheet.AddValidationData(validation);
IWorkbook wb = sheet.Workbook;
wb.SetSheetHidden(wb.GetSheetIndex(dataSheet), 1); //隐藏数据sheet
}
public void TestPLVRecord1()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME);
HSSFWorkbook workbook = new HSSFWorkbook(is1);
CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1);
IDataValidationConstraint constraint = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION);
HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint);
// This used to throw an error before
try
{
workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation);
}
catch (InvalidOperationException)
{
Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()");
}
}
public void TestDvProtectionOrder_bug47363b()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
sheet.ProtectSheet("secret");
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint dvc = dataValidationHelper.CreateintConstraint(OperatorType.BETWEEN, "10", "100");
CellRangeAddressList numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
IDataValidation dv = dataValidationHelper.CreateValidation(dvc, numericCellAddressList);
try
{
sheet.AddValidationData(dv);
}
catch (InvalidOperationException e)
{
String expMsg = "Unexpected (NPOI.HSSF.Record.PasswordRecord) while looking for DV Table insert pos";
if (expMsg.Equals(e.Message))
{
throw new AssertionException("Identified bug 47363b");
}
throw e;
}
TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc;
rc = new RecordInspector.RecordCollector();
((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
int nRecsWithProtection = rc.Records.Length;
sheet.ProtectSheet(null);
rc = new RecordInspector.RecordCollector();
((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
int nRecsWithoutProtection = rc.Records.Length;
Assert.AreEqual(4, nRecsWithProtection - nRecsWithoutProtection);
}
/* (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;
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);
}
/*
* (non-Javadoc)
*
* @see
* NPOI.SS.UserModel.DataValidationHelper#CreateValidation(org
* .apache.poi.SS.UserModel.DataValidationConstraint,
* NPOI.SS.Util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
return(new HSSFDataValidation(cellRangeAddressList, constraint));
}
public void TestAddValidations()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx");
ISheet sheet = workbook.GetSheetAt(0);
List <IDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations();
/**
* For each validation type, there are two cells with the same validation. This Tests
* application of a single validation defInition to multiple cells.
*
* For list ( 3 validations for explicit and 3 for formula )
* - one validation that allows blank.
* - one that does not allow blank.
* - one that does not show the drop down arrow.
* = 2
*
* For number validations ( integer/decimal and text length ) with 8 different types of operators.
* = 50
*
* = 52 ( Total )
*/
Assert.AreEqual(52, dataValidations.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
int[] SingleOperandOperatorTypes = new int[] {
OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL,
OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL,
OperatorType.EQUAL, OperatorType.NOT_EQUAL
};
int[] doubleOperandOperatorTypes = new int[] {
OperatorType.BETWEEN, OperatorType.NOT_BETWEEN
};
decimal value = (decimal)10, value2 = (decimal)20;
double dvalue = (double)10.001, dvalue2 = (double)19.999;
int lastRow = sheet.LastRowNum;
int offset = lastRow + 3;
int lastKnownNumValidations = dataValidations.Count;
IRow row = sheet.CreateRow(offset++);
ICell cell = row.CreateCell(0);
IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
row = sheet.CreateRow(offset++);
cell = row.CreateCell(0);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT");
ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN");
ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL");
int rowNum = row.RowNum + 1;
String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString();
IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula);
dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
offset++;
offset++;
for (int i = 0; i < validationTypes.Length; i++)
{
int validationType = validationTypes[i];
offset = offset + 2;
IRow row0 = sheet.CreateRow(offset++);
ICell cell_10 = row0.CreateCell(0);
cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length");
offset++;
for (int j = 0; j < SingleOperandOperatorTypes.Length; j++)
{
int operatorType = SingleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
//For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null;
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null;
ICell cell_13 = row1.CreateCell(3);
cell_13.SetCellType(CellType.Numeric);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null);
if (i == 0 && j == 0)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else if (i == 0 && j == 1)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
for (int j = 0; j < doubleOperandOperatorTypes.Length; j++)
{
int operatorType = doubleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_13 = row1.CreateCell(3);
ICell cell_14 = row1.CreateCell(4);
String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString();
cell_13.SetCellType(CellType.Numeric);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value);
String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString();
cell_14.SetCellType(CellType.Numeric);
cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
ISheet sheetAt = workbook.GetSheetAt(0);
Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count);
}
/*
* (non-Javadoc)
*
* @see
* NPOI.SS.UserModel.DataValidationHelper#CreateValidation(org
* .apache.poi.SS.UserModel.DataValidationConstraint,
* NPOI.SS.Util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
return new HSSFDataValidation(cellRangeAddressList, 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));
}
