private void AddValidationInternal(int operatorType, String firstFormula,
String secondFormula, HSSFDataValidation.ERRORSTYLE errorStyle, String ruleDescr, String promptDescr,
bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
String[] explicitListValues)
{
int rowNum = _currentRowIndex++;
DVConstraint dc = CreateConstraint(operatorType, firstFormula, secondFormula, explicitListValues);
HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(rowNum, rowNum, 0, 0), dc);
dv.EmptyCellAllowed = (allowEmpty);
dv.ErrorStyle = (errorStyle);
dv.CreateErrorBox("Invalid Input", "Something is wrong - Check condition!");
dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");
dv.ShowPromptBox = (inputBox);
dv.ShowErrorBox = (errorBox);
dv.SuppressDropDownArrow = (suppressDropDown);
_sheet.AddValidationData(dv);
WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
inputBox, errorBox);
if (_cellStyle != null)
{
Row row = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
Cell cell = row.CreateCell(0);
cell.CellStyle = (_cellStyle);
}
WriteOtherSettings(_sheet, _style_1, promptDescr);
}
/// <summary>
/// 验证与提示
/// </summary>
/// <param name="validItem"></param>
/// <param name="colIndex"></param>
/// <param name="sheet"></param>
private void ValidAndMessage(ColumnValidItem validItem, int colIndex, ISheet sheet)
{
DVConstraint constraint = null;
HSSFDataValidation dataValidate = null;
switch (validItem.ValidType)//验证类型
{
case EnumValidType.Integer:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.Decimal:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.DateTime:
constraint = DVConstraint.CreateDateConstraint((int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax, "yyyy-MM-dd");
break;
case EnumValidType.TextLength:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)validItem.ValidQualifier, validItem.ValidMin ?? "1", validItem.ValidMax ?? "200");
break;
case EnumValidType.Customize:
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format(validItem.ValidFormula, Chr(colIndex)));
break;
}
if (constraint != null)
{
CellRangeAddressList regions = null;
if (validItem.ValidType == EnumValidType.Customize && validItem.ValidFormula == "COUNTIF({0}:{0},\"*@*.*\")=1") //邮件格式验证
{
int rowCount = 2000; //只支持前2000行邮件格式验证
for (int i = 0; i < rowCount; i++)
{
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format("COUNTIF({0}{1},\"*@*.*\")=1", Chr(colIndex), i + 2));
regions = new CellRangeAddressList(i + 1, i + 1, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
else
{
regions = new CellRangeAddressList(1, 65535, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
}
/// <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);
}
public void VisitRecord(Record r)
{
if (!(r is DVRecord))
{
return;
}
DVRecord dvRecord = (DVRecord)r;
CellRangeAddressList regions = dvRecord.CellRangeAddress.Copy();
DVConstraint constraint = DVConstraint.CreateDVConstraint(dvRecord, book);
HSSFDataValidation hssfDataValidation = new HSSFDataValidation(regions, constraint);
hssfDataValidation.ErrorStyle = (dvRecord.ErrorStyle);
hssfDataValidation.EmptyCellAllowed = (dvRecord.EmptyCellAllowed);
hssfDataValidation.SuppressDropDownArrow = (dvRecord.SuppressDropdownArrow);
hssfDataValidation.CreatePromptBox(dvRecord.PromptTitle, dvRecord.PromptText);
hssfDataValidation.ShowPromptBox = (dvRecord.ShowPromptOnCellSelected);
hssfDataValidation.CreateErrorBox(dvRecord.ErrorTitle, dvRecord.ErrorText);
hssfDataValidation.ShowErrorBox = (dvRecord.ShowErrorOnInvalidValue);
hssfValidations.Add(hssfDataValidation);
}
/// <summary>
/// 设置Excel单元格样式（标题），数据格式
/// </summary>
/// <param name="dateType">数据类型</param>
/// <param name="porpetyIndex">单元格索引</param>
/// <param name="sheet">Sheet页</param>
/// <param name="dataSheet">数据Sheet页</param>
/// <param name="dataStyle">样式</param>
/// <param name="dataFormat">格式</param>
public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, HSSFSheet sheet, HSSFSheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat)
{
HSSFDataValidation dataValidation = null;
switch (dateType)
{
case ColumnDataType.Date:
this.MinValueOrLength = DateTime.Parse("1950/01/01").ToString("yyyy/MM/dd");
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? DateTime.MaxValue.ToString("yyyy/MM/dd") : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength, "yyyy/MM/dd"));
dataValidation.CreateErrorBox("错误", "请输入日期");
dataValidation.CreatePromptBox("请输入日期格式 yyyy/mm/dd", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
//dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("yyyy/MM/dd");
dataStyle.DataFormat = dataFormat.GetFormat("yyyy/mm/dd");
break;
case ColumnDataType.Number:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "请输入数字");
dataStyle.DataFormat = dataFormat.GetFormat("0");
dataValidation.CreatePromptBox("请输入数字格式", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.Float:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "请输入小数");
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
dataValidation.CreatePromptBox("请输入小数", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.Bool:
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateFormulaListConstraint("Sheet1!$A$1:$B$1"));
dataValidation.CreateErrorBox("错误", "请输入下拉菜单中存在的数据");
sheet.AddValidationData(dataValidation);
dataValidation.CreatePromptBox("下拉菜单", "请输入下拉菜单中存在的数据");
break;
case ColumnDataType.Text:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength;
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, MinValueOrLength, MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "文本长度不符合要求");
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataValidation.CreatePromptBox("请输入文本", "在" + MinValueOrLength + " 到 " + MaxValuseOrLength + "之间");
break;
case ColumnDataType.ComboBox:
case ColumnDataType.Enum:
int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count();
string cloIndex = "";
if (porpetyIndex > 25)
{
cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65);
}
cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString();
//定义名称
IName range = sheet.Workbook.CreateName();
range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count;
range.NameName = "dicRange" + porpetyIndex;
//dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
// DVConstraint.CreateFormulaListConstraint("Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count));
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateFormulaListConstraint("dicRange" + porpetyIndex));
dataValidation.CreateErrorBox("错误", "请输入下拉菜单中存在的数据");
var listItemsTemp = this.ListItems.ToList();
for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++)
{
//HSSFRow dataSheetRow = (HSSFRow)dataSheet.CreateRow(rowIndex);
HSSFRow dataSheetRow = (HSSFRow)dataSheet.GetRow(rowIndex);
if (dataSheetRow == null)
{
dataSheetRow = (HSSFRow)dataSheet.CreateRow(rowIndex);
}
//dataSheetRow.CreateCell(porpetyIndex).SetCellValue(this.ListItems.ToList()[rowIndex].Text);
dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle;
}
sheet.AddValidationData(dataValidation);
dataValidation.CreatePromptBox("下拉菜单", "请输入下拉菜单中存在的数据");
break;
default:
dataValidation = new HSSFDataValidation(new CellRangeAddressList(1, 65535, porpetyIndex, porpetyIndex),
DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength));
dataValidation.CreateErrorBox("错误", "文本长度不符合要求");
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
break;
}
if (!this.IsNullAble)
{
dataValidation.EmptyCellAllowed = false;
}
sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle);
sheet.AddValidationData(dataValidation);
}
