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);
            }
Exemple #2
0
        private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
        {
            HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);

            dataValidate.CreateErrorBox("error", errorMsg);
            return(dataValidate);
        }
Exemple #3
0
        /// <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);
        }
Exemple #5
0
        /// <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);
                }
            }
        }
Exemple #6
0
        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));
        }
Exemple #7
0
        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));
        }
Exemple #8
0
        /// <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);
        }
Exemple #9
0
        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);
        }
Exemple #10
0
        /// <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);
        }
Exemple #11
0
        /// <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);
        }
Exemple #12
0
        /// <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);
        }
Exemple #13
0
        /// <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);
        }
Exemple #14
0
        /// <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);
                }
            }
        }
Exemple #15
0
        /// <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);
                }
            }
        }
Exemple #17
0
 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);
        }