C# ISheet.AddValidationData方法代码示例

        private void CopyRowAreaApplyDetail(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
        {
            var getLastRowNum = destinationWorksheet.LastRowNum;

            for (int i = 0; i < rowNum; i++)
            {
                CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
            }

            var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);

            var markNumColumn_4 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 4, 4);
            var markdvNum_4 = new HSSFDataValidation(markNumColumn_4, markNumConstraint);


            destinationWorksheet.AddValidationData(markdvNum_4);
        }

        private void CopyRowAreaApply(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum)
        {
            var getLastRowNum = destinationWorksheet.LastRowNum;

            for (int i = 0; i < rowNum; i++)
            {
                CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i);
            }

            var markListConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "氣體", "液體", "固體" });
            var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null);
            var markUnitLongConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "kcal/m³", "kcal/L", "kcal/kg" });
            var markUnitShortConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "m³", "kL", "kg" });

            var markColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 3, 3);
            var markdv = new HSSFDataValidation(markColumn, markListConstraint);

            var markULColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 6, 6);
            var markULdv = new HSSFDataValidation(markULColumn, markUnitLongConstraint);

            var markSLColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 8, 8);
            var markSLdv = new HSSFDataValidation(markSLColumn, markUnitShortConstraint);

            var markNumColumn_5 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 5, 5);
            var markdvNum_5 = new HSSFDataValidation(markNumColumn_5, markNumConstraint);

            var markNumColumn_7 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 7, 7);
            var markdvNum_7 = new HSSFDataValidation(markNumColumn_7, markNumConstraint);

            var markNumColumn_9 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 9, 9);
            var markdvNum_9 = new HSSFDataValidation(markNumColumn_9, markNumConstraint);

            var markNumColumn_10 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 10, 10);
            var markdvNum_10 = new HSSFDataValidation(markNumColumn_10, markNumConstraint);

            destinationWorksheet.AddValidationData(markdv);
            destinationWorksheet.AddValidationData(markULdv);
            destinationWorksheet.AddValidationData(markSLdv);

            destinationWorksheet.AddValidationData(markdvNum_5);
            destinationWorksheet.AddValidationData(markdvNum_7);
            destinationWorksheet.AddValidationData(markdvNum_9);
            destinationWorksheet.AddValidationData(markdvNum_10);
        }

        /// <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;
        }

            private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
            {
                var rn0 = ws.PhysicalNumberOfRows;
                var rn = rn0;
                for (var i = 0; i < names.Length; ++i)
                {
                    var rnc = rn++;
                    var r = ws.CreateRow(rnc);
                    if (1 < names[i].Length)
                    {
                        r.RowStyle = cs3;
                        var vh = ws.GetDataValidationHelper();
                        var vl = names[i].Skip(1).ToList();
                        vl.Sort();
                        var vd = vh.CreateValidation(
                            vh.CreateExplicitListConstraint(vl.ToArray()),
                            new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
                            );
                        vd.ShowErrorBox = false;
                        vd.ShowPromptBox = true;
                        ws.AddValidationData(vd);
                    }

                    var c = r.CreateCell(0);
                    c.SetCellValue(names[i][0]);
                    if (0 == i)
                    {
                        c.CellStyle = cs1;
                        r.RowStyle = cs1;
                    }
                    else
                    {
                        c.CellStyle = cs2;
                    }
                }
                if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
                //ws.SetRowGroupCollapsed(rn0 + 1, true);
                ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
            }

        private static void buildTemplateSheet(IWorkbook workbook, ISheet sheet, ISheet sheetDS, List<WFActivityField> fields, ref int dsStartIndex, int sheetIndex)
        {
            if (fields != null)
            {
                IRow rowColumn = sheet.CreateRow(0);
                for (int i = 0; i < fields.Count; i++)
                {
                    ICell cell = rowColumn.CreateCell(i);
                    string colName = String.IsNullOrWhiteSpace(fields[i].DisplayName) ? fields[i].FieldName : fields[i].DisplayName;
                    cell.SetCellValue(colName);
                    ICellStyle cellStyle = workbook.CreateCellStyle();

                    if (fields[i].IsRequired)
                    {
                        cellStyle.FillForegroundColor = IndexedColors.Yellow.Index;
                    }
                    else
                    {
                        cellStyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
                    }
                    cellStyle.FillPattern = FillPattern.SolidForeground;
                    cell.CellStyle = cellStyle;
                    sheet.AutoSizeColumn(i);

                    switch (fields[i].DataType)
                    {
                        case FieldInfo.DATATYPE_LIST:
                        case FieldInfo.DATATYPE_LIST_SQL:
                            string lstFormulaName = fields[i].FieldName + "fn";
                            int dsEndIndex = BuildDataSource(fields[i], sheetDS, dsStartIndex);
                            if (dsEndIndex > dsStartIndex)
                            {
                                IName name = sheet.Workbook.CreateName();
                                name.RefersToFormula = String.Format("'DataSource'!$A${0}:$A${1}", dsStartIndex + 1, dsEndIndex);
                                name.NameName = lstFormulaName;
                                name.SheetIndex = sheetIndex;
                                CellRangeAddressList addressList = new CellRangeAddressList(1, 1, i, i);
                                IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
                                IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(lstFormulaName);
                                IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);
                                sheet.AddValidationData(validation);
                                dsStartIndex = dsEndIndex;
                            }
                            break;
                    }
                }
            }
        }