// <summary>
/// The add validation.
/// </summary>
/// <param name="sheet">
/// 要加入列表的sheet
/// </param>
/// <param name="itemSheet">
/// 选项 sheet.
/// </param>
/// <param name="headerCell">
/// 标题单元格
/// </param>
/// <param name="items">
/// 列表项
/// </param>
private static void AddValidation(ISheet sheet, ISheet itemSheet, ICell headerCell, List <string> items)
{
// 新建行
var row = itemSheet.CreateRow(itemSheet.PhysicalNumberOfRows);
// 新行中写入选项
for (int i = 0; i < items.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(items[i]);
}
// 要加下拉列表的范围
var addressList = new CellRangeAddressList(
headerCell.RowIndex + 1,
65535,
headerCell.ColumnIndex,
headerCell.ColumnIndex);
var dvHelper = sheet.GetDataValidationHelper();
// 格式 Sheet2!$A$1:$E$1
var dvConstraint = dvHelper.CreateFormulaListConstraint(
$"{itemSheet.SheetName}!$A${row.RowNum + 1}:${(items.Count)}${row.RowNum + 1}");
var validation = dvHelper.CreateValidation(dvConstraint, addressList);
// 强制必须填下拉列表给出的值
// validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
public static void AddConstraint(this ISheet sheet, IWorkbook workbook, string name, string mula, int columnIndex, bool isCustom = false)
{
IName namedRange = workbook.CreateName();
namedRange.NameName = name;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint dvConstraint;
// XSSFDataValidation validation;
if (!isCustom)
{
namedRange.RefersToFormula = mula;//公式
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(namedRange.NameName);
}
else
{
//自定义
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(name.Split(','));
}
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, columnIndex, columnIndex);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
private string d2007()
{
string fileName = "G:/" + DateTime.Now.Ticks.ToString() + ".xlsx";
XSSFWorkbook wk = new XSSFWorkbook();
ISheet sheet = wk.CreateSheet();
CellRangeAddressList regions = new CellRangeAddressList(0, 5, 2, 2);
CT_DataValidation ctDataValidation = new CT_DataValidation();
ctDataValidation.allowBlank = true;
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.showInputMessage = true;
ctDataValidation.showErrorMessage = true;
ctDataValidation.sqref = "C1:C88";
ctDataValidation.formula1 = "\"itemA,itemB,itemC\"";
//ctDataValidation.showDropDown = true;
//XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(new string[] { "itemA", "itemB", "itemC" });
XSSFDataValidation dataValidate = new XSSFDataValidation(regions, ctDataValidation);
//dataValidate= new XSSFDataValidation()
sheet.AddValidationData(dataValidate);
using (FileStream fs = File.OpenWrite(fileName))
{
wk.Write(fs);
}
return(fileName);
}
public void TestGetDataValidationsDecimal(){
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.CreateDecimalConstraint(OperatorType.BETWEEN, "=A2",
"200");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DECIMAL, c.GetValidationType());
Assert.AreEqual(OperatorType.BETWEEN, c.Operator);
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(200, c.Value2);
}
public void TestGetDataValidationsListExplicit()
{
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.CreateExplicitListConstraint(new String[] { "aaa",
"bbb", "ccc" });
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
String[] values = c.ExplicitListValues;
Assert.AreEqual(3, values.Length);
Assert.AreEqual("aaa", values[0]);
Assert.AreEqual("bbb", values[1]);
Assert.AreEqual("ccc", values[2]);
}
static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations)
{
IDataValidationHelper helper = sheet.GetDataValidationHelper();
foreach (DataValidation validation in validations)
{
if ((validation.List == null || validation.List.Count == 0) && validation.Name == null)
{
throw new InvalidOperationException("Validation is invalid");
}
IDataValidationConstraint constraint = validation.Name != null?
helper.CreateFormulaListConstraint(validation.Name) :
helper.CreateExplicitListConstraint(validation.List.ToArray());
var range = new CellRangeAddressList(
validation.Range.RowStart ?? 0,
validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1,
validation.Range.ColumnStart ?? 0,
validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1);
IDataValidation dataValidation = helper.CreateValidation(constraint, range);
sheet.AddValidationData(dataValidation);
}
}
private static void SetCodeColumn(XSSFSheet sheet, string columnLetter, int colindex, int elements, string sheetName, bool createCodeColumns)
{
if (createCodeColumns)
{
var row0 = sheet.GetRow(0);
var lastColIndex = row0.LastCellNum;
var headerCell = row0.CreateCell(lastColIndex, CellType.String);
headerCell.SetCellValue(sheetName + " Code");
row0.Cells.Add(headerCell);
headerCell.CellStyle = sheet.Workbook?.GetSheetAt(0)?.GetRow(0)?.GetCell(0)?.CellStyle;
for (var i = 1; i <= elements; i++)
{
var row = sheet.GetRow(i) ?? sheet.CreateRow(i);
var cell = row.CreateCell(lastColIndex, CellType.Formula);
var formula = $"INDEX('{sheetName}'!A1:D{elements},MATCH(${columnLetter}{i + 1},'{sheetName}'!D1:D{elements},0),1)";
cell.SetCellFormula(formula);
}
sheet.SetColumnHidden(row0.LastCellNum - 1, true);
}
var validationHelper = new XSSFDataValidationHelper(sheet);
var addressList = new CellRangeAddressList(0, elements - 1, colindex - 1, colindex - 1);
var constraint = validationHelper.CreateFormulaListConstraint($"'{sheetName}'!$D$2:$D$" + elements);
var dataValidation = validationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(dataValidation);
}
public CFHeaderRecord(RecordInputStream in1)
{
field_1_numcf = in1.ReadShort();
field_2_need_recalculation = in1.ReadShort();
field_3_enclosing_cell_range = new CellRangeAddress(in1);
field_4_cell_ranges = new CellRangeAddressList(in1);
}
private static void CreateDropDownListForExcel(ISheet sheet, string[] dropDownValues, int startRow, int lastRow, int column)
{
if (sheet == null)
{
return;
}
IWorkbook workbook = sheet.Workbook;
string dropDownName = sheet.SheetName + "DropDownValuesForColumn" + column;
ISheet hiddenSheet = workbook.CreateSheet(dropDownName);
for (int i = 0, length = dropDownValues.Length; i < length; i++)
{
string name = dropDownValues[i];
IRow row = hiddenSheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(name);
}
IName namedCell = workbook.CreateName();
namedCell.NameName = dropDownName;
namedCell.RefersToFormula = (dropDownName + "!$A$1:$A$" + dropDownValues.Length);
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet as HSSFSheet);
IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(dropDownName);
CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, column, column);
HSSFDataValidation validation = (HSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
int hiddenSheetIndex = workbook.GetSheetIndex(hiddenSheet);
workbook.SetSheetHidden(hiddenSheetIndex, SheetState.Hidden);
sheet.AddValidationData(validation);
}
public void TestGetDataValidationsFormula()
{
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.CreateCustomConstraint("A2:A3");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.FORMULA, c.GetValidationType());
Assert.AreEqual("A2:A3", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
protected void Read(RecordInputStream in1)
{
field_1_numcf = in1.ReadShort();
field_2_need_recalculation_and_id = in1.ReadShort();
field_3_enclosing_cell_range = new CellRangeAddress(in1);
field_4_cell_ranges = new CellRangeAddressList(in1);
}
public void TestCreateMultipleRegionsValidation()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0);
cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1);
cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2);
XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation;
sheet.AddValidationData(dataValidation);
Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]);
Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]);
Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]);
Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref);
}
finally
{
wb.Close();
}
}
private void setSheet2(HSSFWorkbook workBook, ISheet sheet)
{
//创建表
ISheet sheet2 = workBook.CreateSheet("岗位数据");
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
using (var db = DbFactory.Open())
{
var builder = db.From <Model.Post.Post>().Where(w => w.PostType == ZZTXEnums.行政村防汛防台工作组.ToString());
var rlist = db.Select <PostViewModel>(builder);
for (int iRowIndex = 0; iRowIndex < rlist.Count; iRowIndex++)
{
sheet2.CreateRow(iRowIndex).CreateCell(0).SetCellValue(rlist[iRowIndex].PostName);
}
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = "岗位数据!$A:$A";
range.NameName = "PostDataName";
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(3, 65535, 1, 1);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("PostDataName");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 设置下拉选项
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheet"></param>
/// <param name="cellName"></param>
/// <param name="cellNo"></param>
/// <param name="list"></param>
public void setSheet2(HSSFWorkbook workBook, ISheet sheet, string cellName, int cellNo, List <string> list)
{
//创建表
ISheet sheet2 = workBook.CreateSheet(cellName);
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
for (int i = 0; i < list.Count; i++)
{
sheet2.CreateRow(i).CreateCell(0).SetCellValue(list[i]);
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = cellName + "!$A:$A";
range.NameName = cellName;
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, cellNo, cellNo);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(cellName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook,
CellRangeAddressList addressList, string formulaName)
{
var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss");
ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true);
//CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("");
IRow row = null;
ICell cell = null;
for (int i = 0; i < datas.Length; i++)
{
row = CourseSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(datas[i]);
}
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length);
range.NameName = formulaName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName);
HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);
}
public void TestGetDataValidationsListFormula()
{
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.CreateFormulaListConstraint("A2");
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.SuppressDropDownArrow = (/*setter*/true);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
Assert.AreEqual(true, dv.SuppressDropDownArrow);
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
Assert.AreEqual("A2", c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(double.NaN, c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public void TestGetDataValidationsDate()
{
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.CreateDateConstraint(OperatorType.EQUAL,
"2014/10/25", null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
DVConstraint c = dv.Constraint;
Assert.AreEqual(ValidationType.DATE, c.GetValidationType());
Assert.AreEqual(OperatorType.EQUAL, c.Operator);
Assert.AreEqual(null, c.Formula1);
Assert.AreEqual(null, c.Formula2);
Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1);
Assert.AreEqual(double.NaN, c.Value2);
}
public DVRecord(int validationType, int operator1, int errorStyle, bool emptyCellAllowed,
bool suppressDropDownArrow, bool isExplicitList,
bool showPromptBox, string promptTitle, string promptText,
bool showErrorBox, string errorTitle, string errorText,
Ptg[] formula1, Ptg[] formula2,
CellRangeAddressList regions)
{
int flags = 0;
flags = opt_data_type.SetValue(flags, validationType);
flags = opt_condition_operator.SetValue(flags, operator1);
flags = opt_error_style.SetValue(flags, errorStyle);
flags = opt_empty_cell_allowed.SetBoolean(flags, emptyCellAllowed);
flags = opt_suppress_dropdown_arrow.SetBoolean(flags, suppressDropDownArrow);
flags = opt_string_list_formula.SetBoolean(flags, isExplicitList);
flags = opt_show_prompt_on_cell_selected.SetBoolean(flags, showPromptBox);
flags = opt_show_error_on_invalid_value.SetBoolean(flags, showErrorBox);
_option_flags = flags;
_promptTitle = ResolveTitleText(promptTitle);
_promptText = ResolveTitleText(promptText);
_errorTitle = ResolveTitleText(errorTitle);
_errorText = ResolveTitleText(errorText);
_formula1 = Npoi.Core.SS.Formula.Formula.Create(formula1);
_formula2 = Npoi.Core.SS.Formula.Formula.Create(formula2);
_regions = regions;
}
private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
{
HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);
dataValidate.CreateErrorBox("error", errorMsg);
return(dataValidate);
}
/**
* Constructor which Initializes the cell range on which this object will be
* applied
* @param constraint
*/
public HSSFDataValidation(CellRangeAddressList regions, IDataValidationConstraint constraint)
{
_regions = regions;
//FIXME: This cast can be avoided.
_constraint = (DVConstraint)constraint;
}
/**
* Constructs a DV record and Sets its fields appropriately.
*
* @param in the RecordInputstream to Read the record from
*/
public DVRecord(RecordInputStream in1)
{
_option_flags = in1.ReadInt();
_promptTitle = ReadUnicodeString(in1);
_errorTitle = ReadUnicodeString(in1);
_promptText = ReadUnicodeString(in1);
_errorText = ReadUnicodeString(in1);
int field_size_first_formula = in1.ReadUShort();
_not_used_1 = in1.ReadShort();
//read first formula data condition
_formula1 = Npoi.Core.SS.Formula.Formula.Read(field_size_first_formula, in1);
int field_size_sec_formula = in1.ReadUShort();
_not_used_2 = in1.ReadShort();
//read sec formula data condition
_formula2 = Npoi.Core.SS.Formula.Formula.Read(field_size_sec_formula, in1);
//read cell range address list with all affected ranges
_regions = new CellRangeAddressList(in1);
}
public void AddDropDownListToCell(ISheet sheet, ICell cell, string[] list)
{
CellRangeAddressList cellRange = new CellRangeAddressList(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex);
DVConstraint constraint = null;
if (string.Join("", list).Length < 200)
{
constraint = DVConstraint.CreateExplicitListConstraint(list);
}
else
{
var workBook = sheet.Workbook;
var hiddenSheet = workBook.GetSheet("hidden") ?? workBook.CreateSheet("hidden");
workBook.SetSheetHidden(workBook.GetSheetIndex("hidden"), SheetState.Hidden);
var rowsCount = hiddenSheet.PhysicalNumberOfRows;
for (int i = 0; i < list.Length; i++)
{
hiddenSheet.CreateRow(rowsCount + i).CreateCell(0).SetCellValue(list[i]);
}
var formula = string.Format("hidden!$A{0}:$A{1}", rowsCount + 1, rowsCount + list.Length);
constraint = DVConstraint.CreateFormulaListConstraint(formula);
}
HSSFDataValidation validation = new HSSFDataValidation(cellRange, constraint);
((HSSFSheet)sheet).AddValidationData(validation);
}
static void Main(string[] args)
{
InitializeWorkbook();
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
Sheet sheet2 = hssfworkbook.CreateSheet("Sheet2");
//create three items in Sheet2
Row row0 = sheet2.CreateRow(0);
Cell cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product1");
row0 = sheet2.CreateRow(1);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product2");
row0 = sheet2.CreateRow(2);
cell0 = row0.CreateCell(4);
cell0.SetCellValue("Product3");
CellRangeAddressList rangeList = new CellRangeAddressList();
//add the data validation to the first column (1-100 rows)
rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, 0, 0));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3");
HSSFDataValidation dataValidation = new
HSSFDataValidation(rangeList, dvconstraint);
//add the data validation to sheet1
((HSSFSheet)sheet1).AddValidationData(dataValidation);
WriteToFile();
}
public void Test53965()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
List <IDataValidation> lst = sheet.GetDataValidations(); //<-- works
Assert.AreEqual(0, lst.Count);
//create the cell that will have the validation applied
sheet.CreateRow(0).CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.GetDataValidations();
Assert.AreEqual(1, lst.Count);
}
finally
{
wb.Close();
}
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = ST_DataValidationErrorStyle.stop;
this.ctDdataValidation.allowBlank = true;
}
public CFHeaderRecord(RecordInputStream in1)
{
field_1_numcf = in1.ReadShort();
field_2_need_recalculation = in1.ReadShort();
field_3_enclosing_cell_range = new CellRangeAddress(in1);
field_4_cell_ranges = new CellRangeAddressList(in1);
}
public static void AddValidationData(ISheet sheet, string listFormula, int colIndex, int firstRow, int lastRow = 65535)
{
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, colIndex, colIndex);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(listFormula);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidation);
}
static void Main(string[] args)
{
string workbookName = "test.xlsx";
IWorkbook workbook = null;
ISheet sheet = null;
IDataValidationHelper dvHelper = null;
IDataValidationConstraint dvConstraint = null;
IDataValidation validation = null;
CellRangeAddressList addressList = null;
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else
{
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.CreateSheet("Linked Validations");
BuildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.GetDataValidationHelper();
dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.CreateFormulaListConstraint(
"INDIRECT(UPPER($A$1))");
validation = dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
FileStream sw = File.OpenWrite(workbookName);
workbook.Write(sw);
sw.Close();
}
/// <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>
/// EXCEL2003下拉值
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
/// <param name="StarCol"></param>
/// <param name="EndCol"></param>
private static void CreateDropDwonListForXLS(HSSFSheet sheet, List <string> data, int StarCol, int EndCol)
{
HSSFDataValidationHelper Validation = new HSSFDataValidationHelper(sheet);
DVConstraint Constraint = (DVConstraint)Validation.CreateExplicitListConstraint(data.ToArray());
CellRangeAddressList AddressList = new CellRangeAddressList(1, 65535, StarCol, EndCol);
var HSSF = Validation.CreateValidation(Constraint, AddressList);
sheet.AddValidationData(HSSF);
}
/// <summary>
/// 设置某些列的值只能输入预制的数据,显示下拉框
/// </summary>
/// <param name="sheet">要设置的sheet</param>
/// <param name="textlist">下拉框显示的内容</param>
/// <param name="firstRow">开始行</param>
/// <param name="endRow">结束行</param>
/// <param name="firstCol">开始列</param>
/// <param name="endCol">结束列</param>
/// <returns>设置好的sheet</returns>
public static ISheet SetHSSFValidation(ISheet sheet,
string[] textlist, int firstRow, int endRow, int firstCol,
int endCol)
{
IWorkbook workbook = sheet.Workbook;
if (endRow > sheet.LastRowNum)
{
endRow = sheet.LastRowNum;
}
ISheet hidden = null;
string hiddenSheetName = "hidden" + sheet.SheetName;
int hIndex = workbook.GetSheetIndex(hiddenSheetName);
if (hIndex < 0)
{
hidden = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(sheet.Workbook.NumberOfSheets - 1, SheetState.HIDDEN);
}
else
{
hidden = workbook.GetSheetAt(hIndex);
}
IRow row = null;
ICell cell = null;
for (int i = 0, length = textlist.Length; i < length; i++)
{
row = hidden.GetRow(i);
if (row == null)
{
row = hidden.CreateRow(i);
}
cell = row.GetCell(firstCol);
if (cell == null)
{
cell = row.CreateCell(firstCol);
}
cell.SetCellValue(textlist[i]);
}
// 加载下拉列表内容
string nameCellKey = hiddenSheetName + firstCol;
IName namedCell = workbook.GetName(nameCellKey);
if (namedCell == null)
{
namedCell = workbook.CreateName();
namedCell.NameName = nameCellKey;
namedCell.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", hiddenSheetName, NumberToChar(firstCol + 1), textlist.Length);
}
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(nameCellKey);
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
//// 取消弹出错误框
//validation.ShowErrorBox = false;
sheet.AddValidationData(validation);
return sheet;
}
/** Creates new CFHeaderRecord */
public CFHeaderRecord()
{
field_4_cell_ranges = new CellRangeAddressList();
}
