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="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>
/// 验证手机号格式
/// </summary>
/// <param name="sheet"></param>
/// <param name="index"></param>
/// <param name="formula"></param>
/// <param name="message"></param>
public static void SetCellFormula(this ISheet sheet, int index, string formula, string message = "输入格式不正确")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
var constraint = DVConstraint.
CreateCustomFormulaConstraint(formula);
var dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", message);
//dataValidate.PromptBoxTitle = "ErrorInput";
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 验证手机号格式
/// </summary>
/// <param name="sheet"></param>
/// <param name="index"></param>
public static void SetCellInputPhone(this ISheet sheet, int index)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
//第二个参数int comparisonOperator 参考源码获取
//https://github.com/tonyqus/npoi
//NPOITest项目
//var constraint = DVConstraint.
// CreateCustomFormulaConstraint($"(((LEFT(A1,2)=\"13\")+(LEFT(A1,2)=\"15\")+(LEFT(A1,2)=\"18\"))*(LEN(A1)=11))");
var constraint = DVConstraint.
CreateCustomFormulaConstraint("COUNTIF(D2,\"*@*.*\")=1");
var dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入正确的手机号码");
//dataValidate.PromptBoxTitle = "ErrorInput";
sheet.AddValidationData(dataValidate);
}
private void PresetForProductSheet(HSSFSheet prdSheet, ISheet skuSheet, DateTime?sheetDate, ISheet atpSheet, Dictionary <int, ICellStyle> colors)
{
int?ThresholdQty = null;
if (ConfigurationManager.AppSettings["ThresholdQty"] != null)
{
int qty;
if (int.TryParse(ConfigurationManager.AppSettings["ThresholdQty"].ToString(), out qty))
{
ThresholdQty = qty;
}
}
for (int i = 1; i <= skuSheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)skuSheet.GetRow(i);
string cellPos = ((HSSFCell)row.Cells[0]).ToString();
string multipleValue = ((HSSFCell)row.Cells[3]).ToString();
string colIdxstring = string.Empty;
int colIdx = -1;
string rowIdxstring = string.Empty;
int rowIdx = 0;
for (int j = 0; j < cellPos.Length; j++)
{
int ascii = (int)cellPos[j];
if (ascii > 57)
{
if (colIdx == -1)
{
colIdx = ascii - 65;
}
else
{
colIdx = (colIdx + 1) * 26 + (ascii - 65);
}
colIdxstring += cellPos[j];
}
else
{
rowIdxstring += cellPos[j];
}
}
rowIdx = int.Parse(rowIdxstring) - 1;
ICell cell = prdSheet.GetRow(rowIdx).GetCell(colIdx);
if (cell == null)
{
WriteToLog(string.Format("Cell[{0}] is not created in sheet[{1}]", cellPos, prdSheet.SheetName));
}
else
{
if (sheetDate.HasValue)
{
try
{
ICell atpCell = atpSheet.GetRow(rowIdx).GetCell(colIdx);
string dateValues = (atpCell == null ? string.Empty : atpSheet.GetRow(rowIdx).GetCell(colIdx).StringCellValue);
string[] arr = dateValues.Split(new char[] { '|', ',' }, StringSplitOptions.RemoveEmptyEntries);
int availbleQty = 0;
if (arr.Length == 1)
{
DateTime atpDate = DateTime.Parse(arr[0]);
if (atpDate <= sheetDate.Value)
{
availbleQty = 999999;
}
}
else
{
for (int j = 0; j < arr.Length; j = j + 2)
{
DateTime atpDate = DateTime.Parse(arr[j]);
if (atpDate <= sheetDate.Value)
{
availbleQty += int.Parse(arr[j + 1]);
}
}
}
var list = colors.Where(kvp => kvp.Key < availbleQty);
if (list.Count() > 0)
{
int colorIdx = list.LastOrDefault().Key;
cell.CellStyle = colors[colorIdx];
}
if (availbleQty > 0)
{
cell.CellStyle.IsLocked = false;
//cell.SetCellValue(availbleQty.ToString());
//if (ThresholdQty.HasValue && availbleQty > ThresholdQty)
//{
// HSSFPatriarch patr = (HSSFPatriarch)prdSheet.CreateDrawingPatriarch();
// HSSFComment comment = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, colIdx, rowIdx, colIdx + 1, rowIdx + 1)) as HSSFComment;
// comment.String = new HSSFRichTextString(string.Format("{0}+", ThresholdQty.Value));
// comment.Author = "Plumriver";
// cell.CellComment = comment;
//}
}
}
catch (Exception ex) {
WriteToLog(ex.Message);
}
}
else
{
cell.CellStyle.IsLocked = false;
}
DVConstraint dvConstraint = DVConstraint.CreateCustomFormulaConstraint(string.Format("(MOD(indirect(address(row(),column())) ,{0})=0)", multipleValue));
HSSFDataValidation orderMultipleValidation = new HSSFDataValidation(new CellRangeAddressList(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex), dvConstraint);
orderMultipleValidation.CreateErrorBox("Multiple Value Cell", string.Format("You must enter a multiple of {0} in this cell.", multipleValue).Replace(".00 ", " ").Replace(".0 ", " "));
prdSheet.AddValidationData(orderMultipleValidation);
}
}
}