private DVConstraint CreateConstraint(int operatorType, String firstFormula,
String secondFormula, String[] explicitListValues)
{
if (_validationType == DVConstraint.ValidationType.LIST)
{
if (explicitListValues != null)
{
return(DVConstraint.CreateExplicitListConstraint(explicitListValues));
}
return(DVConstraint.CreateFormulaListConstraint(firstFormula));
}
if (_validationType == DVConstraint.ValidationType.TIME)
{
return(DVConstraint.CreateTimeConstraint(operatorType, firstFormula, secondFormula));
}
if (_validationType == DVConstraint.ValidationType.DATE)
{
return(DVConstraint.CreateDateConstraint(operatorType, firstFormula, secondFormula, null));
}
if (_validationType == DVConstraint.ValidationType.FORMULA)
{
return(DVConstraint.CreateCustomFormulaConstraint(firstFormula));
}
return(DVConstraint.CreateNumericConstraint(_validationType, operatorType, firstFormula, secondFormula));
}
/// <summary>
/// 设置只是输入数字
/// </summary>
/// <param name="sheet"></param>
/// <param name="firstRow"></param>
/// <param name="lastRow"></param>
/// <param name="firstCol"></param>
/// <param name="lastCol"></param>
public static void SetCellInputNumber(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
{
var cellRegions = new CellRangeAddressList(firstRow, lastRow, firstCol, firstCol);
DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, "0", "999999999");
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("", "经过语言");
sheet.AddValidationData(dataValidate);
}
/// <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);
}
}
}
public static HSSFDataValidation CreateNumericConstraint(this HSSFWorkbook book, int columnIndex, string minvalue, string maxvalue, bool isInteger = false)
{
ISheet sheet1 = book.GetSheetAt(0);
CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
DVConstraint constraint = DVConstraint.CreateNumericConstraint(isInteger ? ValidationType.INTEGER : ValidationType.DECIMAL, OperatorType.BETWEEN, minvalue, maxvalue);
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("error", "You must input a numeric between " + minvalue + " and " + maxvalue + ".");
sheet1.AddValidationData(dataValidate);
return(new HSSFDataValidation(cellRegions, constraint));
}
/// <summary>
/// 设置单元格只能输入数字
/// </summary>
/// <param name="sheet"></param>
public static void SetCellInputNumber(this ISheet sheet, int index, string min, string max, string msg)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
//第二个参数int comparisonOperator 参考源码获取
//https://github.com/tonyqus/npoi
//NPOITest项目
DVConstraint constraint = DVConstraint.CreateNumericConstraint(
ValidationType.INTEGER, OperatorType.BETWEEN, min, max);
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", msg);
//dataValidate.PromptBoxTitle = "ErrorInput";
sheet.AddValidationData(dataValidate);
}
public void CreateDataValidation(string filePath, int sheetIndex, decimal fromNumber, decimal toNumber, int fromRow, int fromColumn, int toRow, int toColumn)
{
FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
if (hssfworkbook != null)
{
CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0
DVConstraint dvconstraint = DVConstraint.CreateNumericConstraint(2, OperatorType.BETWEEN, fromNumber.ToString(), toNumber.ToString());
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex - 1);
if (sheet != null)
{
sheet.AddValidationData(dataValidation);
file = new FileStream(filePath, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
}
}
/// <summary>
/// Create data validation
/// Hungnd 04/04/2013
/// </summary>
/// <param name="sheetIndex"></param>
/// <param name="fromRow"></param>
/// <param name="fromCol"></param>
/// <param name="toRow"></param>
/// <param name="toCol"></param>
/// <param name="validationType">The value in VTValidationType</param>
/// <param name="fromValue"></param>
/// <param name="toValue"></param>
/// <param name="errorTitle">Default "Error value"</param>
/// <param name="errorMessage">Default "Error input value"</param>
/// <param name="errorStyle">Default ErrorStyle.STOP</param>
/// <param name="lstContrains">Default null</param>
public void CreateCellValidation(int sheetIndex,
int fromRow, int fromColumn, int toRow, int toColumn,
int validationType, string fromValue, string toValue, string[] lstContrains = null,
string errorTitle = "Error value", string errorMessage = "Error input value",
int errorStyle = ErrorStyle.STOP
)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(sfile);
if (hssfworkbook != null)
{
CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0
DVConstraint dvconstraint = null;
if (validationType.Equals(VTValidationType.LIST))
{
dvconstraint = DVConstraint.CreateExplicitListConstraint(lstContrains);
}
else
{
dvconstraint = DVConstraint.CreateNumericConstraint(validationType, OperatorType.BETWEEN, fromValue, toValue);
}
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint);
if (!validationType.Equals(VTValidationType.LIST))
{
dataValidation.CreateErrorBox(errorTitle, errorMessage);
dataValidation.ErrorStyle = errorStyle;
}
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
sheet.AddValidationData(dataValidation);
sfile = new FileStream(FilePath, FileMode.Create);
hssfworkbook.Write(sfile);
}
}
}
//数据有效性以及下拉框的设置
public static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo,
ICellStyle styleContent, HSSFWorkbook workbook)
{
IDataFormat format = workbook.CreateDataFormat();
CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index);
DVConstraint constraint = null;
HSSFDataValidation dataValidation = null;
if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable)
{
IEnumerable <IDecoderItem> data = GetDecoderItem(fieldInfo);
if (data != null)
{
List <string> optionList = new List <string>();
foreach (IDecoderItem item in data)
{
if (item != null)
{
TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null);
optionList.Add(item.Name);
}
}
constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray());
dataValidation = DisplayMsg(region, constraint, "请从下拉框选项中选择");
}
}
else
{
if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox)
{
constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√" });
dataValidation = DisplayMsg(region, constraint, "请在下拉框选项中进行选择");
}
else
{
switch (fieldInfo.DataType)
{
case TkDataType.DateTime:
case TkDataType.Date:
if (fieldInfo.DataType == TkDataType.DateTime)
{
styleContent.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm");
}
else
{
styleContent.DataFormat = format.GetFormat("yyyy-MM-dd");
}
constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd");
dataValidation = DisplayMsg(region, constraint, "请输入一个日期类型的值");
break;
case TkDataType.Double:
case TkDataType.Decimal:
case TkDataType.Money:
constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0");
dataValidation = DisplayMsg(region, constraint, "请输入数值类型的值");
styleContent.DataFormat = format.GetFormat("0");
break;
case TkDataType.Long:
case TkDataType.Int:
case TkDataType.Short:
case TkDataType.Byte:
case TkDataType.Bit:
constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0");
dataValidation = DisplayMsg(region, constraint, "请输入一个整数");
styleContent.DataFormat = format.GetFormat("0");
break;
default:
styleContent.DataFormat = format.GetFormat("@");
break;
}
}
}
return(dataValidation);
}
/// <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);
}
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.
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
int dvRow = 0;
HSSFSheet sheet = (HSSFSheet)wb.GetSheetAt(0);
DVConstraint dc = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.EQUAL, "42", null);
HSSFDataValidation dv = new HSSFDataValidation(new CellRangeAddressList(dvRow, dvRow, 0, 0), dc);
dv.EmptyCellAllowed = (false);
dv.ErrorStyle = (HSSFDataValidation.ERRORSTYLE.STOP);
dv.ShowPromptBox = (true);
dv.CreateErrorBox("Xxx", "Yyy");
dv.SuppressDropDownArrow = (true);
sheet.AddValidationData(dv);
MemoryStream baos = new MemoryStream();
try
{
wb.Write(baos);
}
catch (IOException)
{
throw;
}
byte[] wbData = baos.ToArray();
//if (false)
//{ // TODO (Jul 2008) fix EventRecordFactory to Process unknown records, (and DV records for that matter)
// ERFListener erfListener = null; // new MyERFListener();
// EventRecordFactory erf = new EventRecordFactory(erfListener, null);
// try
// {
// POIFSFileSystem fs = new POIFSFileSystem(new MemoryStream(baos.ToArray()));
// erf.ProcessRecords(fs.CreatePOIFSDocumentReader("Workbook"));
// }
// catch (RecordFormatException)
// {
// throw;
// }
// catch (IOException)
// {
// throw;
// }
//}
// else verify record ordering by navigating the raw bytes
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 AssertFailedException("Identified bug 45519");
}
Assert.AreEqual(DVRecord.sid, nextSid);
}
