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);
}
private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
{
HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", errorMsg);
return(dataValidate);
}
/// <summary>
/// 生成老师导入表格
/// </summary>
/// <param name="columnName"></param>
/// <param name="col"></param>
/// <returns></returns>
public static string BuildTchExcel(List <string> columnName, List <string> col)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < columnName.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(columnName[i]);
}
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, 2, 2);
//设置科目 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(col.ToArray());
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
//设置生成性别下拉框的行和列
var cellRegions1 = new CellRangeAddressList(1, 65535, 4, 4);
//设置 下拉框内容
DVConstraint constraint1 = DVConstraint.CreateExplicitListConstraint(new String[] { "男", "女" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate1 = new HSSFDataValidation(cellRegions1, constraint1);
dataValidate1.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate1.ShowPromptBox = true;
sheet.AddValidationData(dataValidate1);
//设置生成职称下拉框的行和列
var cellRegions2 = new CellRangeAddressList(1, 65535, 5, 5);
//设置 下拉框内容
DVConstraint constraint2 = DVConstraint.CreateExplicitListConstraint(new String[] { "正高级教师", "高级教师", "一级教师", "二级教师", "三级教师" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate2 = new HSSFDataValidation(cellRegions2, constraint2);
dataValidate2.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate2.ShowPromptBox = true;
sheet.AddValidationData(dataValidate2);
workbook.Write(ms);
string fileName = "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd") + "\\" + "教师" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (!Directory.Exists(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd")))
{
Directory.CreateDirectory(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd"));
}
var f = File.Create(System.AppDomain.CurrentDomain.BaseDirectory + fileName);
ms.WriteTo(f);
ms.Close();
f.Close();
return(fileName);
}
/// <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));
}
public static HSSFDataValidation CreateDateConstraint(this HSSFWorkbook book, int columnIndex)
{
ISheet sheet1 = book.GetSheetAt(0);
CellRangeAddressList cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
DVConstraint constraint = DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("error", "You must input a date.");
sheet1.AddValidationData(dataValidate);
return(new HSSFDataValidation(cellRegions, constraint));
}
/// <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);
}
public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 设置单元格为日期
/// </summary>
/// <param name="sheet"></param>
/// <param name="index"></param>
public static void SetCellDate(this ISheet sheet, int index)
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(1, 65535, index, index);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateDateConstraint(OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入正确的时间格式");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
/// <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);
}
/// <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);
}
/// <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);
}
/// <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);
}
}
}
/// <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);
}
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);
}
}
}
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);
}
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);
}
