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);
        }
示例#2
0
            private void AddValidationInternal(int operatorType, string firstFormula,
                                               string secondFormula, int errorStyle, string ruleDescr, string promptDescr,
                                               bool allowEmpty, bool inputBox, bool errorBox, bool suppressDropDown,
                                               string[] explicitListValues)
            {
                int rowNum = _currentRowIndex++;

                IDataValidationHelper     dataValidationHelper = _sheet.GetDataValidationHelper();
                IDataValidationConstraint dc = CreateConstraint(dataValidationHelper, operatorType, firstFormula, secondFormula, explicitListValues);

                IDataValidation dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(rowNum, rowNum, 0, 0));

                dv.EmptyCellAllowed = (/*setter*/ allowEmpty);
                dv.ErrorStyle       = (/*setter*/ errorStyle);
                dv.CreateErrorBox("Invalid Input", "Something is wrong - check condition!");
                dv.CreatePromptBox("Validated Cell", "Allowable values have been restricted");

                dv.ShowPromptBox         = (/*setter*/ inputBox);
                dv.ShowErrorBox          = (/*setter*/ errorBox);
                dv.SuppressDropDownArrow = (/*setter*/ suppressDropDown);


                _sheet.AddValidationData(dv);
                WriteDataValidationSettings(_sheet, _style_1, _style_2, ruleDescr, allowEmpty,
                                            inputBox, errorBox);
                if (_cellStyle != null)
                {
                    IRow  row  = _sheet.GetRow(_sheet.PhysicalNumberOfRows - 1);
                    ICell cell = row.CreateCell(0);
                    cell.CellStyle = (/*setter*/ _cellStyle);
                }
                WriteOtherSettings(_sheet, _style_1, promptDescr);
            }
        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);
    }
示例#4
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);
        }
示例#5
0
        /// <summary>
        /// Used to fill the attendance details
        /// </summary>
        /// <param name="worksheet">worksheet used to get the range and fill attendance details</param>
        private void FillAttendanceDetails(IWorksheet worksheet)
        {
            int rowIndex = 2;

            foreach (EmployeeDetails empDetails in _employeeAttendanceList)
            {
                worksheet["A" + rowIndex].Text = empDetails.Name;
                worksheet["B" + rowIndex].Text = empDetails.Supervisor;
                for (int colIndex = 0; colIndex < empDetails.Attendances.Count; colIndex++)
                {
                    worksheet[rowIndex, colIndex + 8].Text = empDetails.Attendances[colIndex];
                }
                rowIndex++;
            }
            //Data validation for list
            IDataValidation validation = worksheet.Range["H2:AL31"].DataValidation;

            validation.ListOfValues = new string[] { "P", "A", "L", "WE" };


            worksheet["C2:C31"].Formula      = "=CountIf('H2:AL2',\"P\")";
            worksheet["D2:D31"].Formula      = "=CountIf('H2:AL2',\"L\")";
            worksheet["E2:E31"].Formula      = "=CountIf('H2:AL2',\"A\")";
            worksheet["F2:F31"].Formula      = "=E2/(C2+D2+E2)";
            worksheet["G2:G31"].Formula      = "=D2/(C2+D2+E2)";
            worksheet["F2:G31"].NumberFormat = ".00 %";
        }
        public void TestGetDataValidationsListExplicit()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
            List<IDataValidation> list = sheet.GetDataValidations();
            Assert.AreEqual(0, list.Count);

            IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
            IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new String[] { "aaa",
                "bbb", "ccc" });
            CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0);
            IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
            validation.SuppressDropDownArrow = (/*setter*/true);
            sheet.AddValidationData(validation);

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

            HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
            Assert.AreEqual(true, dv.SuppressDropDownArrow);

            DVConstraint c = dv.Constraint;
            Assert.AreEqual(ValidationType.LIST, c.GetValidationType());
            Assert.AreEqual(null, c.Formula1);
            Assert.AreEqual(null, c.Formula2);
            Assert.AreEqual(double.NaN, c.Value1);
            Assert.AreEqual(double.NaN, c.Value2);
            String[] values = c.ExplicitListValues;
            Assert.AreEqual(3, values.Length);
            Assert.AreEqual("aaa", values[0]);
            Assert.AreEqual("bbb", values[1]);
            Assert.AreEqual("ccc", values[2]);
        }
        static void AddValidations(ISheet sheet, ExcelVersion version, params DataValidation[] validations)
        {
            IDataValidationHelper helper = sheet.GetDataValidationHelper();

            foreach (DataValidation validation in validations)
            {
                if ((validation.List == null || validation.List.Count == 0) && validation.Name == null)
                {
                    throw new InvalidOperationException("Validation is invalid");
                }

                IDataValidationConstraint constraint = validation.Name != null?
                                                       helper.CreateFormulaListConstraint(validation.Name) :
                                                           helper.CreateExplicitListConstraint(validation.List.ToArray());

                var range = new CellRangeAddressList(
                    validation.Range.RowStart ?? 0,
                    validation.Range.RowEnd ?? ExcelHelper.GetRowMax(version) - 1,
                    validation.Range.ColumnStart ?? 0,
                    validation.Range.ColumnEnd ?? ExcelHelper.GetColumnMax(version) - 1);

                IDataValidation dataValidation = helper.CreateValidation(constraint, range);
                sheet.AddValidationData(dataValidation);
            }
        }
        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);
        }
示例#9
0
        public void Test53965()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet            = wb.CreateSheet() as XSSFSheet;
                List <IDataValidation> lst = sheet.GetDataValidations();    //<-- works
                Assert.AreEqual(0, lst.Count);

                //create the cell that will have the validation applied
                sheet.CreateRow(0).CreateCell(0);

                IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
                IDataValidationConstraint constraint           = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
                CellRangeAddressList      addressList          = new CellRangeAddressList(0, 0, 0, 0);
                IDataValidation           validation           = dataValidationHelper.CreateValidation(constraint, addressList);
                sheet.AddValidationData(validation);

                // this line caused XmlValueOutOfRangeException , see Bugzilla 3965
                lst = sheet.GetDataValidations();
                Assert.AreEqual(1, lst.Count);
            }
            finally
            {
                wb.Close();
            }
        }
示例#10
0
文件: Program.cs 项目: zqb971/npoi-1
        static void Main(string[] args)
        {
            string                    workbookName = "test.xlsx";
            IWorkbook                 workbook     = null;
            ISheet                    sheet        = null;
            IDataValidationHelper     dvHelper     = null;
            IDataValidationConstraint dvConstraint = null;
            IDataValidation           validation   = null;
            CellRangeAddressList      addressList  = null;

            // Using the ss.usermodel allows this class to support both binary
            // and xml based workbooks. The choice of which one to create is
            // made by checking the file extension.
            if (workbookName.EndsWith(".xlsx"))
            {
                workbook = new XSSFWorkbook();
            }
            else
            {
                workbook = new HSSFWorkbook();
            }

            // Build the sheet that will hold the data for the validations. This
            // must be done first as it will create names that are referenced
            // later.
            sheet = workbook.CreateSheet("Linked Validations");
            BuildDataSheet(sheet);

            // Build the first data validation to occupy cell A1. Note
            // that it retrieves it's data from the named area or region called
            // CHOICES. Further information about this can be found in the
            // static buildDataSheet() method below.
            addressList  = new CellRangeAddressList(0, 0, 0, 0);
            dvHelper     = sheet.GetDataValidationHelper();
            dvConstraint = dvHelper.CreateFormulaListConstraint("CHOICES");
            validation   = dvHelper.CreateValidation(dvConstraint, addressList);
            sheet.AddValidationData(validation);

            // Now, build the linked or dependent drop down list that will
            // occupy cell B1. The key to the whole process is the use of the
            // INDIRECT() function. In the buildDataSheet(0 method, a series of
            // named regions are created and the names of three of them mirror
            // the options available to the user in the first drop down list
            // (in cell A1). Using the INDIRECT() function makes it possible
            // to convert the selection the user makes in that first drop down
            // into the addresses of a named region of cells and then to use
            // those cells to populate the second drop down list.
            addressList  = new CellRangeAddressList(0, 0, 1, 1);
            dvConstraint = dvHelper.CreateFormulaListConstraint(
                "INDIRECT(UPPER($A$1))");
            validation = dvHelper.CreateValidation(dvConstraint, addressList);
            sheet.AddValidationData(validation);

            FileStream sw = File.OpenWrite(workbookName);

            workbook.Write(sw);
            sw.Close();
        }
示例#11
0
 public App(IDBAccess dbAccess, IStager stager, IScriptRunner scriptRunner, IMessageBroker messageBroker, IDataValidation dataValidation, ILogger <App> log)
 {
     Stager         = stager;
     ScriptRunner   = scriptRunner;
     DBAccess       = dbAccess;
     MessageBroker  = messageBroker;
     DataValidation = dataValidation;
     _log           = log;
 }
示例#12
0
        /// <summary>
        /// 创建列表
        /// </summary>
        /// <param name="helper"></param>
        /// <param name="ListData"></param>
        /// <returns></returns>
        internal static IDataValidation getValidationExplicitList(IDataValidationHelper helper, CellRangeAddressList range, string[] ListData)
        {
            IDataValidationConstraint constraint = helper.CreateExplicitListConstraint(ListData);
            IDataValidation           validation = helper.CreateValidation(constraint, range);

            //validation.ShowPromptBox = true;
            validation.ShowErrorBox = true;
            return(validation);
        }
示例#13
0
        public static void Test()
        {
            var filepath = "E:/test.xlsx";

            if (File.Exists(filepath))
            {
                File.Delete(filepath);
            }
            using (var ms = new FileStream(filepath, FileMode.OpenOrCreate))
            {
                IWorkbook workbook = new XSSFWorkbook();
                try
                {
                    ISheet sheetRef = workbook.CreateSheet("ref");//名为ref的工作表
                    var    items    = new dynamic[] {
                        new { code = "1", name = "项目" },
                        new { code = "2", name = "标段" },
                        new { code = "3", name = "桥梁" },
                        new { code = "4", name = "隧道" }
                    };
                    for (int i = 0; i < items.Length; i++)//A1到A4格子里存放0001到0004,这是下拉框可以选择的4个选项
                    {
                        var r = sheetRef.CreateRow(i);
                        r.CreateCell(0).SetCellValue(items[i].code);
                        r.CreateCell(1).SetCellValue(items[i].name);
                        //sheetRef.GetRow(i);
                    }
                    IName range = workbook.CreateName();                   //创建一个命名公式
                    range.RefersToFormula = "ref!$A$1:$A$" + items.Length; //公式内容,就是上面的区域
                    range.NameName        = "sectionName";                 //公式名称,可以在"公式"-->"名称管理器"中看到

                    ISheet sheet1 = workbook.CreateSheet("data");          //获得第一个工作表
                    IRow   row    = sheet1.CreateRow(0);
                    row.CreateCell(0).SetCellValue("项目名称");
                    row.CreateCell(1).SetCellValue("地图名称");
                    row.CreateCell(2).SetCellValue("地图类型-代码");
                    row.CreateCell(3).SetCellValue("地图类型-名称");
                    row.CreateCell(4).SetCellValue("经纬度");
                    //设定公式
                    row.GetCell(3).SetCellFormula("VLOOKUP(C2,ref!A:B,2,FALSE)");
                    CellRangeAddressList     regions    = new CellRangeAddressList(1, 65535, 2, 3);                                            //约束范围:B1到B65535
                    XSSFDataValidationHelper helper     = new XSSFDataValidationHelper((XSSFSheet)sheet1);                                     //获得一个数据验证Helper
                    IDataValidation          validation = helper.CreateValidation(helper.CreateFormulaListConstraint("sectionName"), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式)
                    validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");                                                                            //不符合约束时的提示
                    validation.ShowErrorBox = true;                                                                                            //显示上面提示 = True
                    sheet1.AddValidationData(validation);                                                                                      //添加进去
                    sheet1.ForceFormulaRecalculation = true;

                    workbook.Write(ms);
                }
                finally
                {
                    workbook.Close();
                }
            }
        }
示例#14
0
        internal static IDataValidation getValidationYearMonth(IDataValidationHelper helper, CellRangeAddressList range)
        {
            IDataValidationConstraint constraint = helper.CreateintConstraint(
                ST_DataValidationOperator.between.GetHashCode(), "200001", "202012");
            IDataValidation validation = helper.CreateValidation(constraint, range);

            validation.CreateErrorBox("error", "请输入YYYYMM样式的年月,如2012年8月为【201208】");
            validation.ShowErrorBox = true;
            return(validation);
        }
示例#15
0
        public IActionResult ExportToExcel()
        {
            //创建EXCEL工作薄
            IWorkbook workBook = new XSSFWorkbook();
            //创建sheet文件表
            ISheet sheet = workBook.CreateSheet("上下料点信息");

            var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
                                       DateTime.Now.ToString("yyyyMM"));

            if (!Directory.Exists(expDir))
            {
                Directory.CreateDirectory(expDir);
            }

            string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));

            #region 创建Excel表头
            //创建表头
            IRow  header = sheet.CreateRow(0);
            ICell cell   = header.CreateCell(0);
            cell.SetCellValue("料点编号");

            cell = header.CreateCell(1);
            cell.SetCellValue("料点名称");

            cell = header.CreateCell(2);
            cell.SetCellValue("仓库编号");

            cell = header.CreateCell(3);
            cell.SetCellValue("巷道编号");

            cell = header.CreateCell(4);
            cell.SetCellValue("料点类型");

            ISheet sheet1 = workBook.GetSheetAt(0);                                                                                                                        //获得第一个工作表
            CellRangeAddressList     regions    = new CellRangeAddressList(1, 65535, 4, 4);                                                                                //设定位置 行起,行止,列起,列终
            XSSFDataValidationHelper helper     = new XSSFDataValidationHelper((XSSFSheet)sheet1);                                                                         //获得一个数据验证Helper
            IDataValidation          validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "In", "Out", "InOut", "OutBack" }), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式)
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");                                                                                                                //不符合约束时的提示
            validation.ShowErrorBox = true;                                                                                                                                //显示上面提示 = True
            sheet1.AddValidationData(validation);                                                                                                                          //添加进去
            sheet1.ForceFormulaRecalculation = true;

            #endregion
            //工作流写入,通过流的方式进行创建生成文件
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();

                return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("上下料点信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
            }
        }
示例#16
0
        /// <summary>
        /// 设置单元格验证(数字)
        /// 创建者:戚鹏
        /// 创建日期:2013.5.20
        /// </summary>
        /// <param name="RowCount"></param>
        /// <param name="ColNum"></param>
        /// <param name="FirstValue"></param>
        /// <param name="LastValue"></param>
        /// <returns></returns>
        internal static IDataValidation getValidationInt(IDataValidationHelper helper, CellRangeAddressList range,
                                                         int from = 0, int to = 99999999)
        {
            IDataValidationConstraint constraint = helper.CreateintConstraint(
                ST_DataValidationOperator.between.GetHashCode(), from.ToString(), to.ToString());
            IDataValidation validation = helper.CreateValidation(constraint, range);

            //validation.CreateErrorBox("error", "You must input a numeric between 1 and 50.");
            validation.ShowErrorBox = true;
            return(validation);
        }
示例#17
0
        public void TestAddToExistingSheet()
        {
            // dvEmpty.xls is a simple one sheet workbook.  With a DataValidations header record but no
            // DataValidations.  It's important that the example has one SHEETPROTECTION record.
            // Such a workbook can be Created in Excel (2007) by Adding datavalidation for one cell
            // and then deleting the row that Contains the cell.
            IWorkbook                 wb    = HSSFTestDataSamples.OpenSampleWorkbook("dvEmpty.xls");
            int                       dvRow = 0;
            ISheet                    sheet = wb.GetSheetAt(0);
            IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
            IDataValidationConstraint dc = dataValidationHelper.CreateintConstraint(OperatorType.EQUAL, "42", null);
            IDataValidation           dv = dataValidationHelper.CreateValidation(dc, new CellRangeAddressList(dvRow, dvRow, 0, 0));

            dv.EmptyCellAllowed = (/*setter*/ false);
            dv.ErrorStyle       = (/*setter*/ ERRORSTYLE.STOP);
            dv.ShowPromptBox    = (/*setter*/ true);
            dv.CreateErrorBox("Xxx", "Yyy");
            dv.SuppressDropDownArrow = (/*setter*/ true);

            sheet.AddValidationData(dv);

            MemoryStream baos = new MemoryStream();

            try
            {
                wb.Write(baos);
            }
            catch (IOException e)
            {
                throw new RuntimeException(e);
            }

            byte[] wbData = baos.ToArray();


            byte[] dvHeaderRecStart = { (byte)0xB2, 0x01, 0x12, 0x00, };
            int    dvHeaderOffset   = FindIndex(wbData, dvHeaderRecStart);

            Assert.IsTrue(dvHeaderOffset > 0);
            int nextRecIndex = dvHeaderOffset + 22;
            int nextSid
                = ((wbData[nextRecIndex + 0] << 0) & 0x00FF)
                  + ((wbData[nextRecIndex + 1] << 8) & 0xFF00)
                ;

            // nextSid should be for a DVRecord.  If anything comes between the DV header record
            // and the DV records, Excel will not be able to open the workbook without error.

            if (nextSid == 0x0867)
            {
                throw new AssertionException("Identified bug 45519");
            }
            Assert.AreEqual(DVRecord.sid, nextSid);
        }
示例#18
0
        protected void SetOtherValidationParameters(IDataValidation validation)
        {
            bool yesNo = true;

            validation.EmptyCellAllowed = yesNo;
            validation.ShowErrorBox     = yesNo;
            validation.ShowPromptBox    = yesNo;
            validation.CreateErrorBox("Error Message Title", "Error Message");
            validation.CreatePromptBox("Prompt", "Enter some value");
            validation.SuppressDropDownArrow = yesNo;
        }
示例#19
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);
        }
示例#20
0
        /// <summary>
        /// 设置excel下拉列表
        /// </summary>
        /// <param name="sheet">excel工作表</param>
        /// <param name="sheetName">下拉列表使用数据的sheet的名字</param>
        /// <param name="rangeName">下拉列表使用数据的域</param>
        /// <param name="names">待处理的姓名字符串</param>
        /// <param name="count">数据表中数据行数,ref引用格式</param>
        /// <param name="Row">所要设置的下拉列表所在行</param>
        /// <param name="Col">所要设置的下拉列表所在列</param>
        public static void SetDropDownList(ISheet sheet, string sheetName, string rangeName, string names, ref int count, int Row, int Col)
        {
            ISheet dataSheet = null;
            IRow   row       = null;
            ICell  cell      = null;
            IName  name      = null;

            IDataValidationHelper     dvHelper     = null;
            IDataValidationConstraint dvConstraint = null;
            IDataValidation           validation   = null;
            CellRangeAddressList      addressList  = null;

            dataSheet = sheet.Workbook.GetSheet(sheetName); //获取存储下拉数据的sheet
            if (dataSheet == null)                          //若不存在,则创建
            {
                dataSheet = sheet.Workbook.CreateSheet(sheetName);
            }

            //string[] list = new string[] { "123", "456", "789" };
            string[] list = BuildDropData(names);   //获取下拉数据

            row = dataSheet.CreateRow(count++);

            for (int i = 0; i < list.Count(); i++)  //将数据写入sheet中
            {
                cell = row.CreateCell(i);
                cell.SetCellValue(list[i]);
            }

            //生成一个列表引用区域,并唯一标识它
            name = sheet.Workbook.CreateName();
            name.RefersToFormula = string.Format("'{0}'!$A${1}:${2}${3}", sheetName, count, IndexToColumn(list.Count()), count);
            name.NameName        = rangeName.ToUpper() + Row.ToString();

            addressList  = new CellRangeAddressList(Row, Row, Col, Col); //设置生成下拉框的行和列
            dvHelper     = sheet.GetDataValidationHelper();
            dvConstraint = dvHelper.CreateFormulaListConstraint(rangeName.ToUpper() + Row.ToString());
            validation   = dvHelper.CreateValidation(dvConstraint, addressList); //绑定下拉框和作用区域
            sheet.AddValidationData(validation);

            IWorkbook wb = sheet.Workbook;

            wb.SetSheetHidden(wb.GetSheetIndex(dataSheet), 1);  //隐藏数据sheet
        }
示例#21
0
        /// <summary>
        /// 设置字段为下拉框
        /// </summary>
        /// <param name="ruleName">规则名称</param>
        /// <param name="fieldName">字段名称</param>
        private void SetField2Select(string ruleName, string fieldName)
        {
            //查找字符索引
            var field = _config.Row.Where(t => t.Field == fieldName).FirstOrDefault();

            if (field == null)
            {
                return;
            }

            CellRangeAddressList     regions    = new CellRangeAddressList(_config.Prop.StartRow - 1, 65535, field.ColumnIndex, field.ColumnIndex); //约束范围:B1到B65535
            XSSFDataValidationHelper helper     = new XSSFDataValidationHelper((XSSFSheet)_sheet);                                                  //获得一个数据验证Helper
            IDataValidation          validation = helper.CreateValidation(helper.CreateFormulaListConstraint(ruleName), regions);                   //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式)

            validation.EmptyCellAllowed = true;
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
            validation.ShowErrorBox = true;                 //显示上面提示 = True
            _sheet.AddValidationData(validation);           //添加进去
        }
示例#22
0
        /// <summary>
        /// Sheet中引用校验引用区域
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="item"></param>
        private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item)
        {
            if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域, 则退出
            {
                return;
            }

            CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex);

            IDataValidation dataValidate = null;

            if (excelVersion == ExcelVersion.XLSX)
            {
                XSSFSheet xssfSheet = sheet as XSSFSheet;
                XSSFDataValidationHelper     dvHelper     = new XSSFDataValidationHelper(xssfSheet);
                XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference);
                dataValidate = dvHelper.CreateValidation(dvConstraint, regions);
                dataValidate.EmptyCellAllowed = true;
                dataValidate.ShowErrorBox     = true;
                dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
                dataValidate.ShowPromptBox = true;
                dataValidate.CreatePromptBox("", item.ColumnHeader);
            }
            else
            {
                IName range = workbook.CreateName();
                range.RefersToFormula = item.ConstraintReference;
                range.NameName        = item.ConstraintName;

                DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName);
                dataValidate = new HSSFDataValidation(regions, constraint);
                dataValidate.EmptyCellAllowed = true;
                dataValidate.ShowErrorBox     = true;
                dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
                dataValidate.ShowPromptBox = true;
                dataValidate.CreatePromptBox("", item.ColumnHeader);
            }

            sheet.AddValidationData(dataValidate);
        }
示例#23
0
        public void TestDvProtectionOrder_bug47363b()
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet    sheet    = workbook.CreateSheet("Sheet1");

            sheet.ProtectSheet("secret");

            IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
            IDataValidationConstraint dvc = dataValidationHelper.CreateintConstraint(OperatorType.BETWEEN, "10", "100");
            CellRangeAddressList      numericCellAddressList = new CellRangeAddressList(0, 0, 1, 1);
            IDataValidation           dv = dataValidationHelper.CreateValidation(dvc, numericCellAddressList);

            try
            {
                sheet.AddValidationData(dv);
            }
            catch (InvalidOperationException e)
            {
                String expMsg = "Unexpected (NPOI.HSSF.Record.PasswordRecord) while looking for DV Table insert pos";
                if (expMsg.Equals(e.Message))
                {
                    throw new AssertionException("Identified bug 47363b");
                }
                throw e;
            }
            TestCases.HSSF.UserModel.RecordInspector.RecordCollector rc;
            rc = new RecordInspector.RecordCollector();
            ((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
            int nRecsWithProtection = rc.Records.Length;

            sheet.ProtectSheet(null);
            rc = new RecordInspector.RecordCollector();
            ((HSSFSheet)sheet).Sheet.VisitContainedRecords(rc, 0);
            int nRecsWithoutProtection = rc.Records.Length;

            Assert.AreEqual(4, nRecsWithProtection - nRecsWithoutProtection);
        }
示例#24
0
 protected void SetOtherValidationParameters(IDataValidation validation)
 {
     bool yesNo = true;
     validation.EmptyCellAllowed = yesNo;
     validation.ShowErrorBox = yesNo;
     validation.ShowPromptBox = yesNo;
     validation.CreateErrorBox("Error Message Title", "Error Message");
     validation.CreatePromptBox("Prompt", "Enter some value");
     validation.SuppressDropDownArrow = yesNo;
 }
示例#25
0
 private void WriteWorkSheet_DataValidations_formulas(IDataValidation item, XElement dataValidationNode)
 {
     if (item is DataValidationInt)
     {
         DataValidationInt intItem = item as DataValidationInt;
         WriteWorkSheet_DataValidations_formulas_int(intItem, dataValidationNode);
     }
     else if (item is DataValidationDecimal)
     {
         DataValidationDecimal decimalItem = item as DataValidationDecimal;
         WriteWorkSheet_DataValidations_formulas_decimal(decimalItem, dataValidationNode);
     }
     else if (item is DataValidationList)
     {
         DataValidationList listItem = item as DataValidationList;
         WriteWorkSheet_DataValidations_formulas_list(listItem, dataValidationNode);
     }
     else if (item is DataValidationDateTime)
     {
         DataValidationDateTime dateTime = item as DataValidationDateTime;
         WriteWorkSheet_DataValidations_formulas_dateTime(dateTime, dataValidationNode);
     }
     else if (item is DataValidationTime)
     {
         DataValidationTime timeItem = item as DataValidationTime;
         WriteWorkSheet_DataValidations_formulas_Time(timeItem, dataValidationNode);
     }
     else if (item is DataValidationCustom)
     {
         DataValidationCustom customItem = item as DataValidationCustom;
         WriteWorkSheet_DataValidations_formulas_Custom(customItem, dataValidationNode);
     }
 }
示例#26
0
 void ISheet.AddValidationData(IDataValidation dataValidation)
 {
     throw new System.NotImplementedException();
 }
示例#27
0
        /// <summary>
        /// Creates a data validation object
        /// </summary>
        /// <param name="dataValidation">The data validation object settings</param>
        public void AddValidationData(IDataValidation dataValidation)
        {
            if (dataValidation == null)
            {
                throw new ArgumentException("objValidation must not be null");
            }

            HSSFDataValidation hssfDataValidation = (HSSFDataValidation)dataValidation;
            DataValidityTable dvt = _sheet.GetOrCreateDataValidityTable();

            DVRecord dvRecord = hssfDataValidation.CreateDVRecord(this);
            dvt.AddDataValidation(dvRecord);
        }
示例#28
0
 public void AddValidationData(IDataValidation dataValidation)
 {
     _sh.AddValidationData(dataValidation);
 }
示例#29
0
        public IActionResult ExportToExcel()
        {
            //创建EXCEL工作薄
            IWorkbook workBook = new XSSFWorkbook();
            //创建sheet文件表
            ISheet sheet = workBook.CreateSheet("客户信息");

            var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
                                       DateTime.Now.ToString("yyyyMM"));

            if (!Directory.Exists(expDir))
            {
                Directory.CreateDirectory(expDir);
            }

            string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));

            #region 创建Excel表头
            //创建表头
            IRow  header = sheet.CreateRow(0);
            ICell cell   = header.CreateCell(0);
            cell.SetCellValue("客户编号");

            cell = header.CreateCell(1);
            cell.SetCellValue("客户名称");

            cell = header.CreateCell(2);
            cell.SetCellValue("客户类型");

            cell = header.CreateCell(3);
            cell.SetCellValue("联系电话/Phone");

            cell = header.CreateCell(4);
            cell.SetCellValue("传真/Fax");

            cell = header.CreateCell(5);
            cell.SetCellValue("邮箱/Email");

            //IName range = workBook.CreateName();//创建一个命名公式
            //range.RefersToFormula = "客户信息!$A$1:$A$4";//公式内容,就是上面的区域
            //range.NameName = "sectionName";//公式名称,可以在"公式"-->"名称管理器"中看到

            ISheet sheet1 = workBook.GetSheetAt(0);                                                                                                                                     //获得第一个工作表
            CellRangeAddressList     regions    = new CellRangeAddressList(1, 65535, 2, 2);                                                                                             //设定位置 行起,行止,列起,列终
            XSSFDataValidationHelper helper     = new XSSFDataValidationHelper((XSSFSheet)sheet1);                                                                                      //获得一个数据验证Helper
            IDataValidation          validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "Company", "Personal", "Virtual", "Internal" }), regions); //创建一个特定约束范围内的公式列表约束(即第一节里说的"自定义"方式)
            validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");                                                                                                                             //不符合约束时的提示
            validation.ShowErrorBox = true;                                                                                                                                             //显示上面提示 = True
            sheet1.AddValidationData(validation);                                                                                                                                       //添加进去
            sheet1.ForceFormulaRecalculation = true;

            #endregion
            //工作流写入,通过流的方式进行创建生成文件
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.Write(stream);
                byte[] buffer = stream.ToArray();

                return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("客户信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
            }
        }
示例#30
0
        private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
        {
            StorageFile storageFile;

            if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
            {
                FileSavePicker savePicker = new FileSavePicker();
                savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
                savePicker.SuggestedFileName      = "DataValidationSample";
                if (rdBtn2003.IsChecked.Value)
                {
                    savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
                    {
                        ".xls"
                    });
                }
                else
                {
                    savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
                    {
                        ".xlsx",
                    });
                }
                storageFile = await savePicker.PickSaveFileAsync();
            }
            else
            {
                StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
                if (rdBtn2003.IsChecked.Value)
                {
                    storageFile = await local.CreateFileAsync("DataValidationSample.xls", CreationCollisionOption.ReplaceExisting);
                }
                else
                {
                    storageFile = await local.CreateFileAsync("DataValidationSample.xlsx", CreationCollisionOption.ReplaceExisting);
                }
            }


            if (storageFile == null)
            {
                return;
            }
            //Instantiate excel Engine
            ExcelEngine  excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;

            if (rdBtn2003.IsChecked.Value)
            {
                application.DefaultVersion = ExcelVersion.Excel97to2003;
            }
            else
            {
                application.DefaultVersion = ExcelVersion.Excel2013;
            }

            IWorkbook  workbook = application.Workbooks.Create(1);
            IWorksheet sheet    = workbook.Worksheets[0];

            //Data validation to list the values in the first cell
            IDataValidation validation = sheet.Range["C7"].DataValidation;

            sheet.Range["B7"].Text = "Select an item from the validation list";

            validation.ListOfValues       = new string[] { "PDF", "XlsIO", "DocIO" };
            validation.PromptBoxText      = "Data Validation list";
            validation.IsPromptBoxVisible = true;
            validation.ShowPromptBox      = true;

            sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for Numbers
            IDataValidation validation1 = sheet.Range["C9"].DataValidation;

            sheet.Range["B9"].Text      = "Enter a Number to validate";
            validation1.AllowType       = ExcelDataType.Integer;
            validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation1.FirstFormula    = "0";
            validation1.SecondFormula   = "10";
            validation1.ShowErrorBox    = true;
            validation1.ErrorBoxText    = "Value must be between 0 and 10";
            validation1.ErrorBoxTitle   = "ERROR";
            validation1.PromptBoxText   = "Value must be between 0 and 10";
            validation1.ShowPromptBox   = true;
            sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for Date
            IDataValidation validation2 = sheet.Range["C11"].DataValidation;

            sheet.Range["B11"].Text     = "Enter the Date to validate";
            validation2.AllowType       = ExcelDataType.Date;
            validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation2.FirstDateTime   = new DateTime(2012, 1, 1);
            validation2.SecondDateTime  = new DateTime(2012, 12, 31);
            validation2.ShowErrorBox    = true;
            validation2.ErrorBoxText    = "Value must be from 01/1/2012 to 31/12/2012";
            validation2.ErrorBoxTitle   = "ERROR";
            validation2.PromptBoxText   = "Value must be from 01/1/2012 to 31/12/2012";
            validation2.ShowPromptBox   = true;
            sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for TextLength
            IDataValidation validation3 = sheet.Range["C13"].DataValidation;

            sheet.Range["B13"].Text     = "Enter the Text to validate";
            validation3.AllowType       = ExcelDataType.TextLength;
            validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation3.FirstFormula    = "1";
            validation3.SecondFormula   = "6";
            validation3.ShowErrorBox    = true;
            validation3.ErrorBoxText    = "Maximum 6 characters are allowed";
            validation3.ErrorBoxTitle   = "ERROR";
            validation3.PromptBoxText   = "Maximum 6 characters are allowed";
            validation3.ShowPromptBox   = true;
            sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for Time
            IDataValidation validation4 = sheet.Range["C15"].DataValidation;

            sheet.Range["B15"].Text     = "Enter the Time to validate";
            validation4.AllowType       = ExcelDataType.Time;
            validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation4.FirstFormula    = "10";
            validation4.SecondFormula   = "12";
            validation4.ShowErrorBox    = true;
            validation4.ErrorBoxText    = "Time must be between 10 and 12";
            validation4.ErrorBoxTitle   = "ERROR";
            validation4.PromptBoxText   = "Time must be between 10 and 12";
            validation4.ShowPromptBox   = true;
            sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;
            sheet.Range["B2:C2"].Merge();
            sheet.Range["B2"].Text = "Simple Data validation";
            sheet.Range["B5"].Text = "Validation criteria";
            sheet.Range["C5"].Text = "Validation";
            sheet.Range["B5"].CellStyle.Font.Bold           = true;
            sheet.Range["C5"].CellStyle.Font.Bold           = true;
            sheet.Range["B2"].CellStyle.Font.Bold           = true;
            sheet.Range["B2"].CellStyle.Font.Size           = 16;
            sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            sheet.AutofitColumn(2);
            sheet["B7"].ColumnWidth = 40.3;
            sheet.UsedRange.AutofitRows();

            await workbook.SaveAsAsync(storageFile);

            workbook.Close();
            excelEngine.Dispose();

            MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");

            UICommand yesCmd = new UICommand("Yes");

            msgDialog.Commands.Add(yesCmd);
            UICommand noCmd = new UICommand("No");

            msgDialog.Commands.Add(noCmd);
            IUICommand cmd = await msgDialog.ShowAsync();

            if (cmd == yesCmd)
            {
                // Launch the saved file
                bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
            }
        }
示例#31
0
        private void WriteWorkSheet_DataValidations_Attributes(XElement dataValidationNode, IDataValidation item)
        {
            if (item.AllowBlank.ConvertBool())
            {
                dataValidationNode.Add(new XAttribute(XName.Get("allowBlank"), 1));
            }

            if (item.ShowInputMessage.ConvertBool())
            {
                dataValidationNode.Add(new XAttribute(XName.Get("showInputMessage"), 1));
            }
            if (item.ShowErrorMessage.ConvertBool())
            {
                dataValidationNode.Add(new XAttribute(XName.Get("showErrorMessage"), 1));
            }

            if (!string.IsNullOrEmpty(item.ErrorTitle))
            {
                dataValidationNode.Add(new XAttribute(XName.Get("errorTitle"), item.ErrorTitle));
            }

            if (!string.IsNullOrEmpty(item.Error))
            {
                dataValidationNode.Add(new XAttribute(XName.Get("error"), item.Error));
            }

            if (!string.IsNullOrEmpty(item.PromptTitle))
            {
                dataValidationNode.Add(new XAttribute(XName.Get("promptTitle"), item.PromptTitle));
            }

            if (!string.IsNullOrEmpty(item.Prompt))
            {
                dataValidationNode.Add(new XAttribute(XName.Get("prompt"), item.Prompt));
            }
        }
示例#32
0
 void ISheet.AddValidationData(IDataValidation dataValidation)
 {
     throw new System.NotImplementedException();
 }
        //
        // GET: /Default/

        public ActionResult Default(string SaveOption)
        {
            if (SaveOption == null)
            {
                return(View());
            }

            //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;

            //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
            //The new workbook will have 12 worksheets
            IWorkbook workbook = application.Workbooks.Open(DefaultResolveApplicationDataPath(@"BudgetPlanner.xls"));

            IWorksheet sheet = workbook.Worksheets[1];

            sheet.FirstVisibleRow = 3;

            IFont font = workbook.CreateFont();

            font.Bold = true;

            #region TextBox
            ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140);
            textbox.Text = "Quick Budget";
            textbox.RichText.SetFont(0, 11, font);
            textbox.VAlignment             = ExcelCommentVAlign.Center;
            textbox.HAlignment             = ExcelCommentHAlign.Center;
            textbox.Fill.FillType          = ExcelFillType.Gradient;
            textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
            textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
            textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);

            textbox      = sheet.TextBoxes.AddTextBox(7, 2, 25, 140);
            textbox.Text = "Income";
            textbox.RichText.SetFont(0, 5, font);
            textbox.VAlignment             = ExcelCommentVAlign.Center;
            textbox.HAlignment             = ExcelCommentHAlign.Center;
            textbox.Fill.FillType          = ExcelFillType.Gradient;
            textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
            textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
            textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);

            textbox      = sheet.TextBoxes.AddTextBox(16, 2, 25, 140);
            textbox.Text = "Spending";
            textbox.RichText.SetFont(0, 7, font);
            textbox.VAlignment             = ExcelCommentVAlign.Center;
            textbox.HAlignment             = ExcelCommentHAlign.Center;
            textbox.Fill.FillType          = ExcelFillType.Gradient;
            textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
            textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
            textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);

            #endregion

            #region Write Text and Numbers
            sheet.Range["O6"].Text = "Weekly";
            sheet.Range["E7"].Text = "Frequency";
            sheet.Range["F7"].Text = "Amount";
            sheet.Range["G7"].Text = "Monthly";
            sheet.Range["H7"].Text = "Yearly";

            sheet.Range["B8"].Text  = "Total Income";
            sheet.Range["C9"].Text  = "Salary/Wages";
            sheet.Range["C10"].Text = "Salary/Wages(Spouse)";
            sheet.Range["C11"].Text = "Other";
            sheet.Range["C12"].Text = "Other";
            sheet.Range["C13"].Text = "Other";
            sheet.Range["B17"].Text = "Transportation";

            sheet.Range["F25"].Number = 3000;
            sheet.Range["F9"].Number  = 55000;
            sheet.Range["F10"].Number = 35000;


            sheet.Range["C18"].Text = "Auto Loan/Lease";
            sheet.Range["C19"].Text = "Insurance";
            sheet.Range["C20"].Text = "Gas ";
            sheet.Range["C21"].Text = "Maintenance ";
            sheet.Range["C22"].Text = "Registration/Inspection";
            sheet.Range["C23"].Text = "Bill's train pass";
            sheet.Range["C24"].Text = "Jane's bus pass";
            sheet.Range["C25"].Text = "Other";

            sheet.Range["E16"].Text = "Total";

            sheet.Range["N6"].Text = "Chart";

            sheet.Range["B27"].Text = "Home";

            sheet.Range["F28"].Number = 20000;
            sheet.Range["F29"].Number = 5000;
            sheet.Range["F33"].Number = 5000;

            sheet.Range["C28"].Text = "EMI";
            sheet.Range["C29"].Text = "Rent";
            sheet.Range["C30"].Text = "Maintanence";
            sheet.Range["C31"].Text = "Insurance";
            sheet.Range["C32"].Text = "Furniture";
            sheet.Range["C33"].Text = "Household Supplies";
            sheet.Range["C34"].Text = "Groceries";
            sheet.Range["C35"].Text = "Real Estate Tax";
            sheet.Range["C36"].Text = "Other";

            sheet.Range["B39"].Text = "Utilities";

            sheet.Range["F41"].Number = 1000;
            sheet.Range["F42"].Number = 250;
            sheet.Range["F43"].Number = 150;
            sheet.Range["F45"].Number = 175;

            sheet.Range["C40"].Text = "Phone - Home";
            sheet.Range["C41"].Text = "Phone - Cell";
            sheet.Range["C42"].Text = "Cable";
            sheet.Range["C43"].Text = "Gas";
            sheet.Range["C44"].Text = "Water";
            sheet.Range["C45"].Text = "Electricity";
            sheet.Range["C46"].Text = "Internet";
            sheet.Range["C47"].Text = "Other";

            sheet.Range["B50"].Text = "Health";

            sheet.Range["F53"].Number = 500;


            sheet.Range["C51"].Text = "Dental";
            sheet.Range["C52"].Text = "Medical";
            sheet.Range["C53"].Text = "Medication";
            sheet.Range["C54"].Text = "Vision/contacts";
            sheet.Range["C55"].Text = "Life Insurance";
            sheet.Range["C56"].Text = "Electricity";
            sheet.Range["C57"].Text = "Other";

            #endregion

            #region Cell styles

            IStyle tableStyle = workbook.Styles.Add("TableStyle");

            tableStyle.BeginUpdate();
            tableStyle.Color = Color.White;
            tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB  = Color.FromArgb(150, 150, 150);
            tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
            tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB    = Color.FromArgb(150, 150, 150);
            tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle   = ExcelLineStyle.Thin;
            tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB   = Color.FromArgb(150, 150, 150);
            tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle  = ExcelLineStyle.Thin;
            tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB     = Color.FromArgb(150, 150, 150);
            tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle    = ExcelLineStyle.Thin;
            tableStyle.EndUpdate();

            sheet.Range["E7:H7"].CellStyle.Font.Bold = true;
            sheet.Range["B17"].CellStyle.Font.Bold   = true;
            sheet.Range["B27"].CellStyle.Font.Bold   = true;
            sheet.Range["B39"].CellStyle.Font.Bold   = true;
            sheet.Range["B50"].CellStyle.Font.Bold   = true;

            sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;


            sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
            sheet.Range["C9:C13"].CellStyle       = tableStyle;
            sheet.Range["E9:F13"].CellStyle       = tableStyle;


            sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
            sheet.Range["B17:C17"].CellStyle.Color = Color.White;

            sheet.Range["C18:C25"].CellStyle = tableStyle;
            sheet.Range["O6"].CellStyle      = tableStyle;
            sheet.Range["E18:F25"].CellStyle = tableStyle;

            sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
            sheet.Range["C28:C36"].CellStyle       = tableStyle;
            sheet.Range["B27:C27"].CellStyle.Color = Color.White;
            sheet.Range["E28:F36"].CellStyle       = tableStyle;

            sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
            sheet.Range["C40:C47"].CellStyle       = tableStyle;
            sheet.Range["B39:C39"].CellStyle.Color = Color.White;
            sheet.Range["E40:F47"].CellStyle       = tableStyle;

            sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
            sheet.Range["C51:C57"].CellStyle       = tableStyle;
            sheet.Range["B50:C50"].CellStyle.Color = Color.White;
            sheet.Range["E51:F57"].CellStyle       = tableStyle;


            #endregion

            #region Data Validation
            IDataValidation validation = sheet.Range["E9:E13"].DataValidation;
            sheet.Range["E9:E13"].Text = "Monthly";
            validation.ListOfValues    = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };

            IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation;
            sheet.Range["E18:E25"].Text = "Monthly";
            validation1.ListOfValues    = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };

            IDataValidation validation2 = sheet.Range["O6"].DataValidation;
            validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };

            IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation;
            sheet.Range["E28:E36"].Text = "Monthly";
            validation3.ListOfValues    = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };


            IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation;
            sheet.Range["E40:E47"].Text = "Monthly";
            validation4.ListOfValues    = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };

            IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation;
            sheet.Range["E51:E57"].Text = "Monthly";
            validation5.ListOfValues    = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };

            #endregion

            #region Formulas
            sheet.Range["G8"].Formula = "SUM(G9:G13)";
            sheet.Range["H8"].Formula = "SUM(H9:H13)";

            sheet.Range["G17"].Formula = "SUM(G18:G25)";
            sheet.Range["H17"].Formula = "SUM(H18:H25)";
            sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
            sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";

            for (int i = 9; i <= 13; i++)
            {
                sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
                sheet.Range["H" + i].Formula = "G" + i + "*12";
            }

            for (int i = 18; i <= 25; i++)
            {
                sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
                sheet.Range["H" + i].Formula = "G" + i + "*12";
            }

            sheet.Range["G27"].Formula = "SUM(G28:G36)";
            sheet.Range["H27"].Formula = "SUM(H28:H37)";
            for (int i = 28; i <= 36; i++)
            {
                sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
                sheet.Range["H" + i].Formula = "G" + i + "*12";
            }

            sheet.Range["G39"].Formula = "SUM(G40:G47)";
            sheet.Range["H39"].Formula = "SUM(H40:H47)";
            for (int i = 40; i <= 47; i++)
            {
                sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
                sheet.Range["H" + i].Formula = "G" + i + "*12";
            }

            sheet.Range["G50"].Formula = "SUM(G51:G57)";
            sheet.Range["H50"].Formula = "SUM(H51:H57)";
            for (int i = 51; i <= 57; i++)
            {
                sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
                sheet.Range["H" + i].Formula = "G" + i + "*12";
            }

            #endregion

            #region SummaryChart
            //Clustered Column Chart
            IChartShape chart = sheet.Charts.Add();

            //Set Chart Type
            chart.ChartType = ExcelChartType.Bar_Clustered;

            //Set DataRange.

            chart.Series.Add("Expense");
            chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
            chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
            chart.Series[0].DataPoints[0].DataLabels.Size    = 7f;

            chart.Series.Add("Income");
            chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
            chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
            chart.Series[1].DataPoints[0].DataLabels.Size    = 7f;

            chart.Series.Add("Balance");
            chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
            chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
            chart.Series[2].DataPoints[0].DataLabels.Size    = 7f;

            chart.PrimaryValueAxis.NumberFormat = "$#,##0";
            chart.PrimaryCategoryAxis.Visible   = false;

            //Format Chart Area
            IChartFrameFormat chartArea = chart.ChartArea;

            //Style
            chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
            chartArea.Border.LineColor   = Color.Gray;
            chartArea.Border.LineWeight  = ExcelChartLineWeight.Medium;

            //Plot Area
            IChartFrameFormat chartPlotArea = chart.PlotArea;
            chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
            chart.PlotArea.Border.LineColor  = Color.Gray;

            chart.Legend.Position = ExcelLegendPosition.Bottom;

            //Embedded chart position.
            chart.TopRow      = 7;
            chart.BottomRow   = 22;
            chart.LeftColumn  = 9;
            chart.RightColumn = 16;

            chart.ChartTitle          = "Budget Summary";
            chart.ChartTitleArea.Bold = true;
            #endregion

            #region SpendingChart

            chart                     = sheet.Charts.Add();
            chart.ChartTitle          = "Spending Summary";
            chart.ChartTitleArea.Bold = true;
            //Set Chart Type
            chart.ChartType = ExcelChartType.Pie_3D;

            //Set DataRange.
            chart.DataRange                = workbook.Worksheets["Sheet1"].Range["J9:K12"];
            chart.IsSeriesInRows           = false;
            chart.Series[0].Values         = workbook.Worksheets["Sheet1"].Range["K9:K12"];
            chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
            chart.Series[0].Name           = "Spending summary";

            chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
            chart.Series[0].DataPoints[0].DataLabels.Size    = 7f;

            chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
            chart.Series[0].DataPoints[1].DataLabels.Size    = 7f;

            chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
            chart.Series[0].DataPoints[2].DataLabels.Size    = 7f;

            chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
            chart.Series[0].DataPoints[3].DataLabels.Size    = 7f;

            chart.PrimaryValueAxis.NumberFormat = "$#,##0";

            //Format Chart Area
            chartArea = chart.ChartArea;

            //Style
            chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
            chartArea.Border.LineColor   = Color.Gray;
            chartArea.Border.LineWeight  = ExcelChartLineWeight.Medium;

            //Plot Area
            chartPlotArea = chart.PlotArea;
            chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
            chart.PlotArea.Border.LineColor  = Color.Gray;
            chartPlotArea.Fill.ForeColor     = Color.FromArgb(223, 223, 223);

            chart.Legend.Position = ExcelLegendPosition.Bottom;

            //Embedded chart position.
            chart.TopRow      = 25;
            chart.BottomRow   = 42;
            chart.LeftColumn  = 9;
            chart.RightColumn = 16;
            #endregion

            #region Sheet View
            workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
            workbook.Worksheets[0].Activate();
            workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
            workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
            workbook.Worksheets[1].IsRowColumnHeadersVisible = false;

            sheet.InsertColumn(9);
            #endregion

            try
            {
                //Saving the workbook to disk.
                if (SaveOption == "Xls")
                {
                    //Save as .xls format
                    return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
                }
                //Save as .xlsx format
                else
                {
                    workbook.Version = ExcelVersion.Excel2016;
                    return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
                }
            }
            catch (Exception)
            {
            }

            //Close the workbook.
            workbook.Close();
            excelEngine.Dispose();
            return(View());
        }
        //
        // GET: /DataValidation/

        public ActionResult DataValidation(string SaveOption)
        {
            if (SaveOption == null)
            {
                return(View());
            }

            //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
            //The instantiation process consists of two steps.

            //Step 1 : Instantiate the spreadsheet creation engine.
            ExcelEngine excelEngine = new ExcelEngine();
            //Step 2 : Instantiate the excel application object.
            IApplication application = excelEngine.Excel;

            if (SaveOption == "Xls")
            {
                application.DefaultVersion = ExcelVersion.Excel97to2003;
            }
            else
            {
                application.DefaultVersion = ExcelVersion.Excel2016;
            }

            //A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
            //The new workbook will have 3 worksheets
            IWorkbook workbook = application.Workbooks.Create(3);
            //The first worksheet object in the worksheets collection is accessed.
            IWorksheet sheet = workbook.Worksheets[0];

            //Data validation to list the values in the first cell
            IDataValidation validation = sheet.Range["C7"].DataValidation;

            sheet.Range["B7"].Text        = "Select an item from the validation list";
            validation.ListOfValues       = new string[] { "PDF", "XlsIO", "DocIO" };
            validation.PromptBoxText      = "Data Validation list";
            validation.IsPromptBoxVisible = true;
            validation.ShowPromptBox      = true;

            sheet.Range["C7"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C7"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for Numbers
            IDataValidation validation1 = sheet.Range["C9"].DataValidation;

            sheet.Range["B9"].Text      = "Enter a Number to validate";
            validation1.AllowType       = ExcelDataType.Integer;
            validation1.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation1.FirstFormula    = "0";
            validation1.SecondFormula   = "10";
            validation1.ShowErrorBox    = true;
            validation1.ErrorBoxText    = "Enter Value between 0 to 10";
            validation1.ErrorBoxTitle   = "ERROR";
            validation1.PromptBoxText   = "Data Validation using Condition for Numbers";
            validation1.ShowPromptBox   = true;
            sheet.Range["C9"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C9"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for Date
            IDataValidation validation2 = sheet.Range["C11"].DataValidation;

            sheet.Range["B11"].Text     = "Enter the Date to validate";
            validation2.AllowType       = ExcelDataType.Date;
            validation2.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation2.FirstDateTime   = new DateTime(2003, 5, 10);
            validation2.SecondDateTime  = new DateTime(2004, 5, 10);
            validation2.ShowErrorBox    = true;
            validation2.ErrorBoxText    = "Enter Value between 5/10/2003 to 5/10/2004";
            validation2.ErrorBoxTitle   = "ERROR";
            validation2.PromptBoxText   = "Data Validation using Condition for Date";
            validation2.ShowPromptBox   = true;
            sheet.Range["C11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for TextLength
            IDataValidation validation3 = sheet.Range["C13"].DataValidation;

            sheet.Range["B13"].Text     = "Enter the Text to validate";
            validation3.AllowType       = ExcelDataType.TextLength;
            validation3.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation3.FirstFormula    = "1";
            validation3.SecondFormula   = "6";
            validation3.ShowErrorBox    = true;
            validation3.ErrorBoxText    = "Retype text length to 6 character";
            validation3.ErrorBoxTitle   = "ERROR";
            validation3.PromptBoxText   = "Data Validation using Condition for TextLength";
            validation3.ShowPromptBox   = true;
            sheet.Range["C13"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C13"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;

            // Data Validation for Time
            IDataValidation validation4 = sheet.Range["C15"].DataValidation;

            sheet.Range["B15"].Text     = "Enter the Time to validate";
            validation4.AllowType       = ExcelDataType.Time;
            validation4.CompareOperator = ExcelDataValidationComparisonOperator.Between;
            validation4.FirstFormula    = "10";
            validation4.SecondFormula   = "12";
            validation4.ShowErrorBox    = true;
            validation4.ErrorBoxText    = "Enter the Correct time between 10 to 12 ";
            validation4.ErrorBoxTitle   = "ERROR";
            validation4.PromptBoxText   = "Data Validation using Condition for Time";
            validation4.ShowPromptBox   = true;
            sheet.Range["C15"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
            sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
            sheet.Range["C15"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine   = false;
            sheet.Range["B2:C2"].Merge();
            sheet.Range["B2"].Text = "Simple Data validation";
            sheet.Range["B5"].Text = "Validation criteria";
            sheet.Range["C5"].Text = "Validation";
            sheet.Range["B5"].CellStyle.Font.Bold           = true;
            sheet.Range["C5"].CellStyle.Font.Bold           = true;
            sheet.Range["B2"].CellStyle.Font.Bold           = true;
            sheet.Range["B2"].CellStyle.Font.Size           = 16;
            sheet.Range["B2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
            sheet.UsedRange.AutofitColumns();
            sheet.UsedRange.AutofitRows();

            try
            {
                //Saving the workbook to disk.
                if (SaveOption == "Xls")
                {
                    return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
                }
                else
                {
                    return(excelEngine.SaveAsActionResult(workbook, "DataValidation.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
                }
            }
            catch (Exception)
            {
            }

            workbook.Close();
            excelEngine.Dispose();
            return(View());
        }
示例#35
0
        public void TestAddValidations()
        {
            XSSFWorkbook           workbook        = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx");
            ISheet                 sheet           = workbook.GetSheetAt(0);
            List <IDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations();

            /**
             *      For each validation type, there are two cells with the same validation. This Tests
             *      application of a single validation defInition to multiple cells.
             *
             *      For list ( 3 validations for explicit and 3 for formula )
             *          - one validation that allows blank.
             *          - one that does not allow blank.
             *          - one that does not show the drop down arrow.
             *      = 2
             *
             *      For number validations ( integer/decimal and text length ) with 8 different types of operators.
             *		= 50
             *
             *      = 52 ( Total )
             */
            Assert.AreEqual(52, dataValidations.Count);

            IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();

            int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };

            int[] SingleOperandOperatorTypes = new int[] {
                OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL,
                OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL,
                OperatorType.EQUAL, OperatorType.NOT_EQUAL
            };
            int[] doubleOperandOperatorTypes = new int[] {
                OperatorType.BETWEEN, OperatorType.NOT_BETWEEN
            };

            decimal value = (decimal)10, value2 = (decimal)20;
            double  dvalue = (double)10.001, dvalue2 = (double)19.999;
            int     lastRow = sheet.LastRowNum;
            int     offset  = lastRow + 3;

            int lastKnownNumValidations = dataValidations.Count;

            IRow  row  = sheet.CreateRow(offset++);
            ICell cell = row.CreateCell(0);
            IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" });
            CellRangeAddressList      cellRangeAddressList   = new CellRangeAddressList();

            cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
            IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList);

            SetOtherValidationParameters(dataValidation);
            sheet.AddValidationData(dataValidation);
            lastKnownNumValidations++;

            row  = sheet.CreateRow(offset++);
            cell = row.CreateCell(0);

            cellRangeAddressList = new CellRangeAddressList();
            cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);

            ICell firstCell  = row.CreateCell(1); firstCell.SetCellValue("UT");
            ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN");
            ICell thirdCell  = row.CreateCell(3); thirdCell.SetCellValue("IL");

            int    rowNum      = row.RowNum + 1;
            String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString();
            IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula);

            dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList);
            SetOtherValidationParameters(dataValidation);
            sheet.AddValidationData(dataValidation);
            lastKnownNumValidations++;

            offset++;
            offset++;

            for (int i = 0; i < validationTypes.Length; i++)
            {
                int validationType = validationTypes[i];
                offset = offset + 2;
                IRow  row0    = sheet.CreateRow(offset++);
                ICell cell_10 = row0.CreateCell(0);
                cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length");
                offset++;
                for (int j = 0; j < SingleOperandOperatorTypes.Length; j++)
                {
                    int  operatorType = SingleOperandOperatorTypes[j];
                    IRow row1         = sheet.CreateRow(offset++);

                    //For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
                    IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null;

                    cell_10 = row1.CreateCell(0);
                    cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
                    ICell cell_11 = row1.CreateCell(1);
                    ICell cell_21 = row1.CreateCell(2);
                    ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null;

                    ICell cell_13 = row1.CreateCell(3);


                    cell_13.SetCellType(CellType.Numeric);
                    cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value);


                    //First create value based validation;
                    IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null);
                    cellRangeAddressList = new CellRangeAddressList();
                    cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
                    IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
                    SetOtherValidationParameters(validation);
                    sheet.AddValidationData(validation);
                    Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);

                    //Now create real formula based validation.
                    String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
                    constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null);
                    if (i == 0 && j == 0)
                    {
                        cellRangeAddressList = new CellRangeAddressList();
                        cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
                        validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
                        SetOtherValidationParameters(validation);
                        sheet.AddValidationData(validation);
                        Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);

                        cellRangeAddressList = new CellRangeAddressList();
                        cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
                        validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
                        SetOtherValidationParameters(validation);
                        sheet.AddValidationData(validation);
                        Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
                    }
                    else if (i == 0 && j == 1)
                    {
                        cellRangeAddressList = new CellRangeAddressList();
                        cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
                        cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
                        validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
                        SetOtherValidationParameters(validation);
                        sheet.AddValidationData(validation);
                        Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
                    }
                    else
                    {
                        cellRangeAddressList = new CellRangeAddressList();
                        cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
                        validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
                        SetOtherValidationParameters(validation);
                        sheet.AddValidationData(validation);
                        Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
                    }
                }

                for (int j = 0; j < doubleOperandOperatorTypes.Length; j++)
                {
                    int  operatorType = doubleOperandOperatorTypes[j];
                    IRow row1         = sheet.CreateRow(offset++);

                    cell_10 = row1.CreateCell(0);
                    cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());

                    ICell cell_11 = row1.CreateCell(1);
                    ICell cell_21 = row1.CreateCell(2);

                    ICell cell_13 = row1.CreateCell(3);
                    ICell cell_14 = row1.CreateCell(4);


                    String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString();
                    cell_13.SetCellType(CellType.Numeric);
                    cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value);

                    String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString();
                    cell_14.SetCellType(CellType.Numeric);
                    cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2);


                    //First create value based validation;
                    IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String);
                    cellRangeAddressList = new CellRangeAddressList();
                    cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
                    IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
                    SetOtherValidationParameters(validation);
                    sheet.AddValidationData(validation);
                    Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);


                    //Now create real formula based validation.
                    String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
                    String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString();
                    constraint           = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2);
                    cellRangeAddressList = new CellRangeAddressList();
                    cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
                    validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);

                    SetOtherValidationParameters(validation);
                    sheet.AddValidationData(validation);
                    Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
                }
            }

            workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
            ISheet sheetAt = workbook.GetSheetAt(0);

            Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count);
        }
示例#36
0
文件: XSSFSheet.cs 项目: kenlen/npoi
        public void AddValidationData(IDataValidation dataValidation)
        {
            XSSFDataValidation xssfDataValidation = (XSSFDataValidation)dataValidation;
            CT_DataValidations dataValidations = worksheet.dataValidations;
            if (dataValidations == null)
            {
                dataValidations = worksheet.AddNewDataValidations();
            }

            int currentCount = dataValidations.sizeOfDataValidationArray();
            CT_DataValidation newval = dataValidations.AddNewDataValidation();
            newval.Set(xssfDataValidation.GetCTDataValidation());
            dataValidations.count = (uint)currentCount + 1;

        }