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);
    }
Beispiel #2
0
        public HSSFWorkbook CreateWorkbookTemplate()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet    sheet    = (HSSFSheet)workbook.CreateSheet(fMetaData.Table.TableDesc);
            HSSFRow      dataRow  = (HSSFRow)sheet.CreateRow(0);
            int          index    = 0;

            foreach (Tk5FieldInfoEx fieldInfo in fMetaData.Table.TableList)
            {
                int colWith = ExcelUtil.GetColWidth(fieldInfo);
                sheet.SetColumnWidth(index, colWith << 8);
                ICellStyle         styleContent = BorderAndFontSetting(workbook, fieldInfo, Model.Content);
                HSSFDataValidation dataValidate = ExcelUtil.CreateDataValidation(index, fieldInfo, styleContent, workbook);
                sheet.SetDefaultColumnStyle(index, styleContent);
                if (dataValidate != null)
                {
                    ((HSSFSheet)sheet).AddValidationData(dataValidate);
                }

                ICell      cell        = dataRow.CreateCell(index);
                ICellStyle styleHeader = BorderAndFontSetting(workbook, fieldInfo, Model.Header);
                cell.SetCellValue(fieldInfo.DisplayName);
                cell.CellStyle = styleHeader;
                index++;
            }
            return(workbook);
        }
        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);
        }
Beispiel #4
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);
        }
        /// <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);
        }
Beispiel #6
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);
        }
        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 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]);
        }
Beispiel #9
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);
        }
Beispiel #10
0
        private static HSSFDataValidation DisplayMsg(CellRangeAddressList region, DVConstraint constraint, string errorMsg)
        {
            HSSFDataValidation dataValidate = new HSSFDataValidation(region, constraint);

            dataValidate.CreateErrorBox("error", errorMsg);
            return(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);
        }
Beispiel #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();
        }
Beispiel #13
0
        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);
        }
Beispiel #14
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);
            }
Beispiel #15
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);
        }
Beispiel #16
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);
        }
Beispiel #17
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();
        }
        /// <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);
        }
Beispiel #19
0
        public void SetSheetValidationForListConstraint(string sheetName, string rangeName, MergeCellRange cellRange)
        {
            CellRangeAddressList rangeList = new CellRangeAddressList(cellRange.FirstRowIndex, cellRange.LastRowIndex, cellRange.FirstColumnIndex, cellRange.LastColumnIndex);
            ISheet sheet = _workbook.GetSheet(sheetName);
            IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
            IDataValidationConstraint constraint           = dataValidationHelper.CreateFormulaListConstraint(rangeName);
            HSSFDataValidation        validation           = (HSSFDataValidation)dataValidationHelper.CreateValidation(constraint, rangeList);

            sheet.AddValidationData(validation);
        }
Beispiel #20
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);
        }
Beispiel #21
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);
                }
            }
        }
Beispiel #22
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));
        }
Beispiel #23
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));
        }
Beispiel #24
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);
        }
Beispiel #25
0
        // 模板
        public static byte[] CreateExcelTemplate(Tk5ListMetaData metaData)
        {
            MemoryStream ms = new MemoryStream();

            using (ms)
            {
                HSSFWorkbook         workbook         = new HSSFWorkbook();
                HSSFSheet            sheet            = (HSSFSheet)workbook.CreateSheet(metaData.Table.TableDesc);
                HSSFRow              dataRow          = (HSSFRow)sheet.CreateRow(0);
                int                  index            = 0;
                ExportExcelPageMaker headerConfigData =
                    new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
                {
                    UserBorder = true
                };
                ExportExcelPageMaker contentConfigData =
                    new ExportExcelPageMaker(ContentFormat.DefaultHead, ContentFormat.DefaultContent)
                {
                    UserBorder = false
                };

                foreach (Tk5FieldInfoEx fieldInfo in metaData.Table.TableList)
                {
                    int colWith = NPOIWrite.GetColWidth(fieldInfo);
                    sheet.SetColumnWidth(index, colWith << 8);
                    ICellStyle styleContent = NPOIWrite.BorderAndFontSetting(workbook,
                                                                             contentConfigData, fieldInfo, NPOIWrite.Model.Content);
                    HSSFDataValidation dataValidate = CreateDataValidation(index, fieldInfo, styleContent, workbook);
                    sheet.SetDefaultColumnStyle(index, styleContent);
                    if (dataValidate != null)
                    {
                        ((HSSFSheet)sheet).AddValidationData(dataValidate);
                    }

                    ICell      cell        = dataRow.CreateCell(index);
                    ICellStyle styleHeader = NPOIWrite.BorderAndFontSetting(workbook,
                                                                            headerConfigData, fieldInfo, NPOIWrite.Model.Header);
                    cell.SetCellValue(fieldInfo.DisplayName);
                    cell.CellStyle = styleHeader;
                    index++;
                }
                //string strFileName = @"D:\EmportTemplateTest.xls";
                //using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                //{
                //    workbook.Write(fs);
                //}

                workbook.Write(ms);
                ms.Flush();
                return(ms.ToArray());
            }
        }
Beispiel #26
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);
        }
Beispiel #27
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);
        }
Beispiel #28
0
        public void TestGetDataValidationsAny()
        {
            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.CreateNumericConstraint(ValidationType.ANY,
                                                                                                          OperatorType.IGNORED, null, null);
            CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
            IDataValidation      validation  = dataValidationHelper.CreateValidation(constraint, AddressList);

            validation.EmptyCellAllowed = (/*setter*/ true);
            validation.CreateErrorBox("error-title", "error-text");
            validation.CreatePromptBox("prompt-title", "prompt-text");
            sheet.AddValidationData(validation);

            list = sheet.GetDataValidations(); // <-- works
            Assert.AreEqual(1, list.Count);

            HSSFDataValidation dv = list[(0)] as HSSFDataValidation;

            {
                CellRangeAddressList regions = dv.Regions;
                Assert.AreEqual(1, regions.CountRanges());

                CellRangeAddress Address = regions.GetCellRangeAddress(0);
                Assert.AreEqual(1, Address.FirstRow);
                Assert.AreEqual(2, Address.LastRow);
                Assert.AreEqual(3, Address.FirstColumn);
                Assert.AreEqual(4, Address.LastColumn);
            }
            Assert.AreEqual(true, dv.EmptyCellAllowed);
            Assert.AreEqual(false, dv.SuppressDropDownArrow);
            Assert.AreEqual(true, dv.ShowErrorBox);
            Assert.AreEqual("error-title", dv.ErrorBoxTitle);
            Assert.AreEqual("error-text", dv.ErrorBoxText);
            Assert.AreEqual(true, dv.ShowPromptBox);
            Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
            Assert.AreEqual("prompt-text", dv.PromptBoxText);

            IDataValidationConstraint c = dv.ValidationConstraint;

            Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
            Assert.AreEqual(OperatorType.IGNORED, c.Operator);
        }
Beispiel #29
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);
        }
Beispiel #30
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);
        }
Beispiel #31
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);
 }
Beispiel #32
0
        /// <summary>
        /// Creates a data validation object
        /// </summary>
        /// <param name="dataValidation">The Data validation object settings</param>
        public void AddValidationData(HSSFDataValidation dataValidation)
        {
            if (dataValidation == null)
            {
                throw new ArgumentException("objValidation must not be null");
            }
            DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable();

            DVRecord dvRecord = dataValidation.CreateDVRecord(_workbook);
            dvt.AddDataValidation(dvRecord);
        }
Beispiel #33
0
        /// <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;
        }