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);
}
protected void SetOtherValidationParameters(IDataValidation validation)
{
bool yesNo = true;
validation.EmptyCellAllowed = yesNo;
validation.ShowErrorBox = yesNo;
validation.ShowPromptBox = yesNo;
validation.CreateErrorBox("Error Message Title", "Error Message");
validation.CreatePromptBox("Prompt", "Enter some value");
validation.SuppressDropDownArrow = yesNo;
}
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>
/// Sheet中引用校验引用区域
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="item"></param>
private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item)
{
if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域， 则退出
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex);
IDataValidation dataValidate = null;
if (excelVersion == ExcelVersion.XLSX)
{
XSSFSheet xssfSheet = sheet as XSSFSheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference);
dataValidate = dvHelper.CreateValidation(dvConstraint, regions);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
else
{
IName range = workbook.CreateName();
range.RefersToFormula = item.ConstraintReference;
range.NameName = item.ConstraintName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
sheet.AddValidationData(dataValidate);
}
//刷新表注释
private void RefreshNote(string fileFullName)
{
bool change = false;
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
change = true; break;
}
}
if (!change)
{
return;
}
IWorkbook workbook = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite));
ISheet sheet = workbook.GetSheetAt(0);
string sheetName = sheet.SheetName;
ISheet sheetBack = workbook.CreateSheet("__sheetBackup");
sheetBack.CreateFreezePane(2, START_ROW);
int columnNum = 0;
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.LastCellNum > columnNum)
{
columnNum = row.LastCellNum;
}
IRow rowBack = sheetBack.CreateRow(i);
for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j)
{
string val = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK));
var cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
Util.SetCellString(cell, val);
}
}
for (int i = 0; i < columnNum; ++i)
{
sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i));
}
for (int i = 0; i < mFields.Count; ++i)
{
var filed = mFields[i];
if (filed.Enum)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
CellRangeAddressList cellRange = new CellRangeAddressList(START_ROW, 65535, i, i);
IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(GetEnumList(filed.Type));
IDataValidation dataValidation = helper.CreateValidation(constraint, cellRange);
dataValidation.SuppressDropDownArrow = false;
dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type));
sheetBack.AddValidationData(dataValidation);
}
}
workbook.RemoveSheetAt(0);
workbook.SetSheetOrder(sheetBack.SheetName, 0);
workbook.SetSheetName(0, sheetName);
using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) {
workbook.Write(stream);
}
}
protected void SetOtherValidationParameters(IDataValidation validation)
{
bool yesNo = true;
validation.EmptyCellAllowed = yesNo;
validation.ShowErrorBox = yesNo;
validation.ShowPromptBox = yesNo;
validation.CreateErrorBox("Error Message Title", "Error Message");
validation.CreatePromptBox("Prompt", "Enter some value");
validation.SuppressDropDownArrow = yesNo;
}
