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);
        }
Пример #2
0
 private DVConstraint CreateConstraint(int operatorType, String firstFormula,
                                       String secondFormula, String[] explicitListValues)
 {
     if (_validationType == DVConstraint.ValidationType.LIST)
     {
         if (explicitListValues != null)
         {
             return(DVConstraint.CreateExplicitListConstraint(explicitListValues));
         }
         return(DVConstraint.CreateFormulaListConstraint(firstFormula));
     }
     if (_validationType == DVConstraint.ValidationType.TIME)
     {
         return(DVConstraint.CreateTimeConstraint(operatorType, firstFormula, secondFormula));
     }
     if (_validationType == DVConstraint.ValidationType.DATE)
     {
         return(DVConstraint.CreateDateConstraint(operatorType, firstFormula, secondFormula, null));
     }
     if (_validationType == DVConstraint.ValidationType.FORMULA)
     {
         return(DVConstraint.CreateCustomFormulaConstraint(firstFormula));
     }
     return(DVConstraint.CreateNumericConstraint(_validationType, operatorType, firstFormula, secondFormula));
 }
Пример #3
0
        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);
        }
Пример #4
0
        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]);
        }
Пример #5
0
            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);
            }
Пример #6
0
        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);
        }
        /// <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);
        }
Пример #8
0
        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);
    }
Пример #9
0
        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);
        }
Пример #10
0
        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);
        }
Пример #11
0
        private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
        {
            HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);

            dataValidate.CreateErrorBox("error", errorMsg);
            return(dataValidate);
        }
Пример #12
0
        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();
        }
Пример #13
0
        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);
        }
Пример #14
0
        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);
        }
Пример #15
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);
        }
Пример #16
0
        //public static void test1()
        //{
        //    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        //    HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
        //    CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
        //    DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
        //    HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
        //    sheet1.AddValidationData(dataValidate);
        //    MemoryStream ms = new MemoryStream();
        //    hssfworkbook.Write(ms);
        //    string workbookFile = @"D:\\wulei22.xls";
        //    hssfworkbook = null;
        //    FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
        //    byte[] data = ms.ToArray();
        //    fs.Write(data, 0, data.Length);
        //    fs.Flush();
        //    fs.Close();
        //}

        public static void setdownlist()
        {
            //创建工作簿
            HSSFWorkbook ssfworkbook = new HSSFWorkbook();
            //创建工作表(页)
            HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
            //创建一行
            HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);

            //设置表头
            headerRow.CreateCell(0).SetCellValue("ID");
            //设置表头的宽度
            sheet1.SetColumnWidth(0, 15 * 256);
            #region     添加显示下拉列表
            HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
            ssfworkbook.SetSheetHidden(1, true);                     //隐藏
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
            sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
            sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
            HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
            // range.Reference = "ShtDictionary!$A$1:$A$3";//格式
            range.NameName = "dicRange";
            #endregion
            headerRow.CreateCell(1).SetCellValue("Selected");
            sheet1.SetColumnWidth(1, 15 * 256);
            //将下拉列表添加
            CellRangeAddressList regions      = new CellRangeAddressList(1, 65535, 1, 1);
            DVConstraint         constraint   = DVConstraint.CreateFormulaListConstraint("dicRange");
            HSSFDataValidation   dataValidate = new HSSFDataValidation(regions, constraint);
            sheet1.AddValidationData(dataValidate);

            headerRow.CreateCell(2).SetCellValue("VALUE");
            sheet1.SetColumnWidth(2, 15 * 256);

            //写入数据
            //创建数据行
            HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
            //填充数据
            dataRow.CreateCell(0).SetCellValue("1"); //id
            dataRow.CreateCell(1).SetCellValue("");  //选择框
            dataRow.CreateCell(2).SetCellValue("值"); //选择框
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            ssfworkbook.Write(ms);
            string filename     = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
            object Response     = null;
            string workbookFile = @"D:\\wulei.xls";

            FileStream fs   = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
            byte[]     data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
            //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
            //Response.BinaryWrite(ms.ToArray());
            ms.Close();
            ms.Dispose();
        }
Пример #17
0
        /// <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);
        }
Пример #18
0
        /// <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);
        }
Пример #19
0
        /// <summary>
        /// 引用另一个工作表的形式 得到下拉
        /// </summary>
        /// <param name="book"></param>
        /// <param name="columnIndex"></param>
        /// <param name="values"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "")
        {
            if (values == null)
            {
                return;
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                sheetName = "_constraintSheet_";
            }
            //创建下拉数据到新表中
            ISheet sheet          = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);;
            var    firstRow       = sheet.GetRow(0);
            var    conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
            var    rowIndex       = 0;
            var    lastValue      = string.Empty;

            foreach (var value in values)
            {
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                row.CreateCell(conColumnIndex).SetCellValue(value);
                rowIndex++;
                lastValue = value;
            }

            //如果无可选值的话,则增加一个空选项,防止用户填写内容
            if (values.Count() == 0)
            {
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                row.CreateCell(conColumnIndex).SetCellValue("    ");
                rowIndex++;
            }

            //给该列所有单元格加上下拉选择
            IName range = book.CreateName();

            range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
                                                  (Char)('A' + conColumnIndex),
                                                  rowIndex.ToString(), sheetName);
            string rangeName = "dicRange" + columnIndex;

            range.NameName = rangeName;
            var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
            var constraint  = DVConstraint.CreateFormulaListConstraint(rangeName);

            book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN);

            //创建验证
            HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint);

            //关联验证
            HSSFSheet v = book.GetSheetAt(0) as HSSFSheet;

            v.AddValidationData(valid);
        }
Пример #20
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);
                }
            }
        }
Пример #21
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));
        }
Пример #22
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));
        }
Пример #23
0
        private void WriteReportWithData()
        {
            var reportdata = _data.ReportData.ToList().AsParallel();

            InitializeWorkbook();
            var sheet = _hssfworkbook.CreateSheet("request");

            _hssfworkbook.CreateSheet("hidden");

            var style = _hssfworkbook.CreateCellStyle();

            style.FillForegroundColor = HSSFColor.DarkBlue.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            var font = _hssfworkbook.CreateFont();

            font.Color = HSSFColor.White.Index;
            style.SetFont(font);

            var rows = reportdata.Count() + 1;

            for (var j = 0; j < rows; j++)
            {
                var row = sheet.CreateRow(j);

                for (var i = 0; i < _data.Columns.Count; i++)
                {
                    var header = _data.Columns.ElementAt(i);
                    if (j == 0)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(header);
                        cell.CellStyle = style;
                    }
                    else
                    {
                        var dictionary = reportdata.ElementAt(j - 1);
                        row.CreateCell(i).SetCellValue(dictionary[header]);
                    }
                }
                sheet.AutoSizeColumn(j);
            }
            sheet.SetColumnHidden(0, true);
            var namedcell = _hssfworkbook.CreateName();

            namedcell.NameName = "hidden";
            var constraint  = DVConstraint.CreateFormulaListConstraint("hidden");
            var addressList = new CellRangeAddressList(1, reportdata.Count(), _data.Columns.Count,
                                                       _data.Columns.Count);
            var validation = new HSSFDataValidation(addressList, constraint);

            _hssfworkbook.SetSheetHidden(1, true);
        }
Пример #24
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);
        }
Пример #25
0
        public static byte[] ModifyTemplete(string fileName, string[] deptList, string[] dutiesList)
        {
            IWorkbook workbook = null; //全局workbook
            ISheet    sheet;           //sheet

            try
            {
                FileInfo fileInfo = new FileInfo(fileName);//判断文件是否存在
                if (fileInfo.Exists)
                {
                    FileStream fileStream = fileInfo.OpenRead();//打开文件,得到文件流
                    switch (fileInfo.Extension)
                    {
                    //xls是03,用HSSFWorkbook打开,.xlsx是07或者10用XSSFWorkbook打开
                    case ".xls": workbook = new HSSFWorkbook(fileStream); break;

                    case ".xlsx": workbook = new XSSFWorkbook(fileStream); break;

                    default: break;
                    }
                    fileStream.Close();//关闭文件流
                }
                if (workbook == null)
                {
                    return(null);
                }
                sheet = workbook.GetSheetAt(0);

                if (deptList != null && deptList.Length > 0)
                {
                    CellRangeAddressList regions    = new CellRangeAddressList(0, 65535, 2, 2);
                    DVConstraint         constraint = DVConstraint.CreateExplicitListConstraint(deptList);
                    HSSFDataValidation   validate   = new HSSFDataValidation(regions, constraint);
                    sheet.AddValidationData(validate);
                }
                if (dutiesList != null && dutiesList.Length > 0)
                {
                    CellRangeAddressList regions    = new CellRangeAddressList(0, 65535, 4, 4);
                    DVConstraint         constraint = DVConstraint.CreateExplicitListConstraint(dutiesList);
                    HSSFDataValidation   validate   = new HSSFDataValidation(regions, constraint);
                    sheet.AddValidationData(validate);
                }

                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                byte[] buf = ms.ToArray();
                return(buf);
            }
            catch { }
            return(null);
        }
Пример #26
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);
        }
Пример #27
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);
        }
Пример #28
0
        private void CellDataWriter1(int row, int col, string FilePath, string SheetName)
        {
            var fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
            var templateWorkbook = new HSSFWorkbook(fs);


            var sheet = (HSSFSheet)templateWorkbook.GetSheet(SheetName);

            int r = row;

            string result1 = TempData["res1"].ToString();
            string result2 = TempData["res2"].ToString();
            string result  = TempData["res"].ToString();

            int c = col;


            CellRangeAddressList addressList1  = new CellRangeAddressList(1, 50, 1, 1);
            DVConstraint         dvConstraint1 = DVConstraint.CreateExplicitListConstraint(
                new String[] { result1 });
            HSSFDataValidation dataValidation1 = new HSSFDataValidation(addressList1, dvConstraint1);

            dataValidation1.SuppressDropDownArrow = false;
            ((HSSFSheet)sheet).AddValidationData(dataValidation1);

            CellRangeAddressList addressList2  = new CellRangeAddressList(1, 50, 2, 2);
            DVConstraint         dvConstraint2 = DVConstraint.CreateExplicitListConstraint(
                new String[] { result2 });
            HSSFDataValidation dataValidation2 = new HSSFDataValidation(addressList2, dvConstraint2);

            dataValidation2.SuppressDropDownArrow = false;
            ((HSSFSheet)sheet).AddValidationData(dataValidation2);
            CellRangeAddressList addressList  = new CellRangeAddressList(1, 50, 4, 4);
            DVConstraint         dvConstraint = DVConstraint.CreateExplicitListConstraint(
                new String[] { result });
            HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);

            dataValidation.SuppressDropDownArrow = false;
            ((HSSFSheet)sheet).AddValidationData(dataValidation);


            fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite);
            templateWorkbook.Write(fs);
            fs.Close();
        }
Пример #29
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);
        }
Пример #30
0
        public void TestPLVRecord1()
        {
            Stream       is1      = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME);
            HSSFWorkbook workbook = new HSSFWorkbook(is1);

            CellRangeAddressList      cellRange      = new CellRangeAddressList(0, 0, 1, 1);
            IDataValidationConstraint constraint     = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION);
            HSSFDataValidation        dataValidation = new HSSFDataValidation(cellRange, constraint);

            // This used to throw an error before
            try
            {
                workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation);
            }
            catch (InvalidOperationException)
            {
                Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()");
            }
        }