コード例 #1
0
ファイル: ExcelManager.cs プロジェクト: wuleisky/Test1
        // <summary>
        /// The add validation.
        /// </summary>
        /// <param name="sheet">
        /// 要加入列表的sheet
        /// </param>
        /// <param name="itemSheet">
        /// 选项 sheet.
        /// </param>
        /// <param name="headerCell">
        /// 标题单元格
        /// </param>
        /// <param name="items">
        /// 列表项
        /// </param>
        private static void AddValidation(ISheet sheet, ISheet itemSheet, ICell headerCell, List <string> items)
        {
            // 新建行
            var row = itemSheet.CreateRow(itemSheet.PhysicalNumberOfRows);

            // 新行中写入选项
            for (int i = 0; i < items.Count; i++)
            {
                var cell = row.CreateCell(i);
                cell.SetCellValue(items[i]);
            }

            // 要加下拉列表的范围
            var addressList = new CellRangeAddressList(
                headerCell.RowIndex + 1,
                65535,
                headerCell.ColumnIndex,
                headerCell.ColumnIndex);

            var dvHelper = sheet.GetDataValidationHelper();

            // 格式 Sheet2!$A$1:$E$1
            var dvConstraint = dvHelper.CreateFormulaListConstraint(
                $"{itemSheet.SheetName}!$A${row.RowNum + 1}:${(items.Count)}${row.RowNum + 1}");
            var validation = dvHelper.CreateValidation(dvConstraint, addressList);

            // 强制必须填下拉列表给出的值
            // validation.ShowErrorBox = true;

            sheet.AddValidationData(validation);
        }
コード例 #2
0
        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);
            }
        }
コード例 #3
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);
            }
コード例 #4
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();
        }
コード例 #5
0
ファイル: Excel2003Service.cs プロジェクト: A1X71/twilight
        public void SetSheetValidationForListConstraint(string sheetName, string rangeName, MergeCellRange cellRange)
        {
            CellRangeAddressList rangeList = new CellRangeAddressList(cellRange.FirstRowIndex, cellRange.LastRowIndex, cellRange.FirstColumnIndex, cellRange.LastColumnIndex);
            ISheet sheet = _workbook.GetSheet(sheetName);
            IDataValidationHelper     dataValidationHelper = sheet.GetDataValidationHelper();
            IDataValidationConstraint constraint           = dataValidationHelper.CreateFormulaListConstraint(rangeName);
            HSSFDataValidation        validation           = (HSSFDataValidation)dataValidationHelper.CreateValidation(constraint, rangeList);

            sheet.AddValidationData(validation);
        }
コード例 #6
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);
        }
コード例 #7
0
ファイル: ExcelUtil.cs プロジェクト: xkandj/proj-15-pks1.1
        /// <summary>设置列下拉列表</summary>
        public static void SetColumnDropdownList(this ISheet sheet, string[] values, int startRow, int column)
        {
            var regions    = new CellRangeAddressList(startRow, 65535, column, column);
            var helper     = sheet.GetDataValidationHelper();
            var constraint = helper.CreateExplicitListConstraint(values);
            var validation = helper.CreateValidation(constraint, regions);

            validation.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
            validation.ShowPromptBox = true;
            sheet.AddValidationData(validation);
        }
コード例 #8
0
 /// <summary>
 /// 添加下拉框
 /// </summary>
 /// <param name="exSheet">Excel表单对象</param>
 /// <param name="field">字段规则</param>
 /// <param name="rowIndex">起始行号</param>
 /// <param name="rowSpan">结束行号</param>
 public static void AddDropDownList(ISheet exSheet, string[] values, int colIndex, int rowIndex, int colSpan = 1, int rowSpan = 1)
 {
     if (values.Length > 0)
     {
         IDataValidationHelper     helper       = exSheet.GetDataValidationHelper();
         IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(values);
         CellRangeAddressList      rangeList    = new CellRangeAddressList(rowIndex, rowIndex + rowSpan - 1, colIndex, colIndex + colSpan - 1);
         //DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
         //exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
         exSheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
     }
 }
コード例 #9
0
 public void Render(ICell cell)
 {
     if (ValueList.Length > 0)
     {
         ISheet sheet = cell.Sheet;
         IDataValidationHelper     helper       = sheet.GetDataValidationHelper();
         IDataValidationConstraint dvconstraint = helper.CreateExplicitListConstraint(ValueList);
         CellRangeAddressList      rangeList    = new CellRangeAddressList(FillArea.RowIndex, FillArea.RowIndex + FillArea.RowCount - 1,
                                                                           FillArea.ColIndex, FillArea.ColIndex + FillArea.ColCount - 1);
         //DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
         //exSheet.AddValidationData(new HSSFDataValidation(rangeList, constraint));
         sheet.AddValidationData(helper.CreateValidation(dvconstraint, rangeList));
     }
 }
コード例 #10
0
ファイル: ExcelHelper.cs プロジェクト: TengLongH/Paper
        /// <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
        }
コード例 #11
0
        protected void AddOneFromManyValidation(int columnIndex, string sheetWithValuesName, string[] values, ISheet sheet)
        {
            var workbook         = sheet.Workbook;
            var valuesSheet      = workbook.CreateSheet(sheetWithValuesName);
            var valuesSheetIndex = workbook.GetSheetIndex(valuesSheet);

            workbook.SetSheetHidden(valuesSheetIndex, SheetState.VeryHidden);
            for (int i = 0; i < values.Length; i++)
            {
                valuesSheet.CreateRow(i)
                .CreateCell(0)
                .SetCellValue(values[i]);
            }

            var addressList          = new CellRangeAddressList(1, MaxRowsPerSheet - 1, columnIndex, columnIndex);
            var validationHelper     = sheet.GetDataValidationHelper();
            var validationConstraint = validationHelper.CreateFormulaListConstraint($"{sheetWithValuesName}!$A$1:$A$" + values.Length);
            var validation           = validationHelper.CreateValidation(validationConstraint, addressList);

            validation.ShowErrorBox = true;
            validation.CreateErrorBox("Validation failed", "Please choose a valid value.");
            sheet.AddValidationData(validation);
        }
コード例 #12
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);
        }
コード例 #13
0
ファイル: TemplateExcel.cs プロジェクト: 269378737/go81
            private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
            {
                var rn0 = ws.PhysicalNumberOfRows;
                var rn = rn0;
                for (var i = 0; i < names.Length; ++i)
                {
                    var rnc = rn++;
                    var r = ws.CreateRow(rnc);
                    if (1 < names[i].Length)
                    {
                        r.RowStyle = cs3;
                        var vh = ws.GetDataValidationHelper();
                        var vl = names[i].Skip(1).ToList();
                        vl.Sort();
                        var vd = vh.CreateValidation(
                            vh.CreateExplicitListConstraint(vl.ToArray()),
                            new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
                            );
                        vd.ShowErrorBox = false;
                        vd.ShowPromptBox = true;
                        ws.AddValidationData(vd);
                    }

                    var c = r.CreateCell(0);
                    c.SetCellValue(names[i][0]);
                    if (0 == i)
                    {
                        c.CellStyle = cs1;
                        r.RowStyle = cs1;
                    }
                    else
                    {
                        c.CellStyle = cs2;
                    }
                }
                if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
                //ws.SetRowGroupCollapsed(rn0 + 1, true);
                ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
            }
コード例 #14
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);
        }
コード例 #15
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();

#if !HIDE_UNREACHABLE_CODE
            if (false)
            { // TODO (Jul 2008) fix EventRecordFactory to process unknown records, (and DV records for that matter)

                ERFListener erfListener = null; // new MyERFListener();
                EventRecordFactory erf = new EventRecordFactory(erfListener, null);
                try
                {
                    POIFSFileSystem fs = new POIFSFileSystem(new MemoryStream(baos.ToArray()));
                    throw new NotImplementedException("The method CreateDocumentInputStream of POIFSFileSystem is not implemented.");
                    //erf.ProcessRecords(fs.CreateDocumentInputStream("Workbook"));
                }
                catch (RecordFormatException e)
                {
                    throw new RuntimeException(e);
                }
                catch (IOException e)
                {
                    throw new RuntimeException(e);
                }
            }
            // else verify record ordering by navigating the raw bytes
#endif

            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);
        }
コード例 #16
0
        /// <summary>
        /// 将数据生成为excel文件
        /// 创建者:戚鹏
        /// 创建日期:2013.5.20
        /// <param name="info">配置信息</param>
        /// <param name="tempFileFullPath">文件名</param>
        /// </summary>
        public bool WriteWorkbookAuto(string tempFileFullPath, SheetInfo info, DataTable dt,
                                      LAF.Common.ExcelOperation.ExcelOperationHelper.DelegateCheck dCheck, params string[] Edition)
        {
            try
            {
                XSSFWorkbook workbook;
                workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet(info.SheetName);

                //冻结列
                sheet.CreateFreezePane(0, 2, 0, 2);
                //工作簿加密
                //TODO:暂时不加密 为了标题能够过滤
                if (info.Protect)
                {
                    sheet.ProtectSheet(EXCELPWD);
                }

                #region 主体内容
                #region Title样式
                IRow rows = sheet.CreateRow(1);
                rows.HeightInPoints = 30;
                #endregion

                #region 隐藏行
                IRow rowHidden = sheet.CreateRow(0);
                rowHidden.Height = 1;
                #endregion
                for (int i = 0; i < info.ColInfos.Count; i++)
                {
                    int ColumnWidth = info.ColInfos[i].ColumnWidth == 0 ? 20 : info.ColInfos[i].ColumnWidth;
                    if (info.ColInfos[i].ColumnHidden)
                    {
                        sheet.SetColumnWidth(i, 0);
                    }
                    else
                    {
                        sheet.SetColumnWidth(i, ColumnWidth * 256);
                    }

                    #region Title样式
                    ICell cell1 = rows.CreateCell(i);
                    cell1.CellStyle = GetCellStyle(workbook, stylexls.Title);

                    string titleStr = info.ColInfos[i].ColumnTitle;
                    //标题*号进行样式设定
                    IRichTextString rt = workbook.GetCreationHelper().CreateRichTextString(titleStr);
                    if (titleStr.EndsWith("*"))
                    {
                        IFont fontStar = workbook.CreateFont();
                        fontStar.Boldweight = 600;
                        fontStar.Color      = HSSFColor.OliveGreen.Red.Index;
                        rt.ApplyFont(titleStr.Length - 1, titleStr.Length, fontStar);
                    }

                    cell1.SetCellValue(rt);

                    #endregion

                    #region 写入数据KEY
                    ICell cell = rowHidden.CreateCell(i);
                    cell.SetCellValue(info.ColInfos[i].ColumnName);
                    cell.CellStyle.IsLocked = true;
                    #endregion

                    #region 添加验证
                    IDataValidationHelper helper = sheet.GetDataValidationHelper();
                    CellRangeAddressList  range  = getCellRangeAddressList(info.DataArray.Count, i);
                    if (info.ColInfos[i].ColumnRangeValues != null)
                    {
                        sheet.AddValidationData(ExcelOperationValidationHelper.getValidationExplicitList(helper, range, info.ColInfos[i].ColumnRangeValues));
                    }
                    if (info.ColInfos[i].ColValMaxLength.HasValue)
                    {
                        sheet.AddValidationData(ExcelOperationValidationHelper.getValidationTextLength(helper, range,
                                                                                                       1, info.ColInfos[i].ColValMaxLength.GetValueOrDefault()));
                    }
                    switch (info.ColInfos[i].ColumnValidation)
                    {
                    case EmuExcelCellType.Integer:
                        sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range));
                        break;

                    case EmuExcelCellType.Decimal:
                        sheet.AddValidationData(ExcelOperationValidationHelper.getValidationInt(helper, range));
                        break;

                    case EmuExcelCellType.YearMonth:
                        sheet.AddValidationData(ExcelOperationValidationHelper.getValidationYearMonth(helper, range));
                        break;

                    default:
                        break;
                    }

                    #endregion

                    #region 筛选
                    CellRangeAddress CellRange = new CellRangeAddressList(1, 1, 0, i).CellRangeAddresses[0];
                    sheet.SetAutoFilter(CellRange);

                    #endregion
                }

                ICellStyle style     = SetCellBorder(workbook, false);
                ICellStyle styleLock = SetCellBorder(workbook, true);

                foreach (List <CellInfo> items in info.DataArray)
                {
                    foreach (CellInfo item in items)
                    {
                        IRow row = sheet.GetRow(int.Parse(item.YPosition));
                        if (row == null)
                        {
                            row = sheet.CreateRow(int.Parse(item.YPosition));
                        }
                        ICell cell = row.GetCell(int.Parse(item.XPosition));
                        if (cell == null)
                        {
                            cell = row.CreateCell(int.Parse(item.XPosition));
                        }

                        double cellValue = 0;
                        //如果是数值 把单元格格式设置为数值
                        if (double.TryParse(item.Value, out cellValue))
                        {
                            cell.SetCellType(CellType.Numeric);
                            cell.SetCellValue(cellValue);
                            //XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                            //IDataFormat dataFormat = workbook.CreateDataFormat();
                            //cellStyle.DataFormat = dataFormat;
                            //cellStyle.
                        }
                        else
                        {
                            cell.SetCellType(CellType.String);
                            cell.SetCellValue(item.Value);
                        }
                        cell.CellStyle = item.ColumnLock ? styleLock : style;
                    }
                }

                #endregion

                #region 数据校验
                bool hasErr = false;
                if (dCheck != null)
                {
                    //复制dt结构(不复制数据)
                    DataTable dtTemp = dt.Clone();
                    DataRow[] rules  = dt.Select();

                    foreach (var dr in rules)
                    {
                        //int RowCount = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).LastCellNum;
                        //for (int i = 2; i < RowCount; i++)
                        //{
                        //    ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Cells[i];
                        //    Cell.CellComment = null;
                        //    Cell.CellStyle = style;
                        //}

                        dtTemp.Rows.Add(dr.ItemArray);
                        Tuple <List <string[]> > tu = dCheck.Invoke(dtTemp);
                        if (tu.Item1.Count > 0)
                        {
                            hasErr = true;
                            for (int i = 0; i < tu.Item1.Count; i++)
                            {
                                int    iConNum = int.Parse(tu.Item1[i][0]);
                                string strMsg  = tu.Item1[i][1];

                                ICell Cell = sheet.GetRow(dt.Rows.IndexOf(dr) + 2).GetCell(iConNum);
                                Cell.CellStyle   = GetCellBorderException(workbook);
                                Cell.CellComment = null;
                                Cell.CellComment = GetCellComment(sheet, strMsg);
                            }
                        }
                        else
                        {
                            //隐藏正确行
                            sheet.GetRow(dt.Rows.IndexOf(dr) + 2).Height = 1;
                        }
                        dtTemp.Rows.Clear();
                    }
                }

                #endregion

                #region 版本号
                ISheet sheetEdition = null;
                sheetEdition = workbook.CreateSheet("Property");
                for (int r = 0; r < Edition.Length; r++)
                {
                    sheetEdition.CreateRow(r).CreateCell(0).SetCellValue(Edition[r]);
                }
                workbook.SetSheetHidden(1, SheetState.VeryHidden);
                #endregion

                //写入文件
                using (FileStream fs = new FileStream(tempFileFullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);
                    fs.Close();
                }

                return(hasErr);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
コード例 #17
0
        /// <summary>
        /// 设置列类型
        /// </summary>
        /// <param name="excelGlobalDTO"></param>
        public void SetSheetColumnType(ExcelGlobalDTO <TEntity> excelGlobalDTO)
        {
            //遍历Sheet实体集合
            foreach (var item in excelGlobalDTO.Sheets)
            {
                ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);

                //判断是否为空
                if (item.SheetEntityList == null)
                {
                    continue;
                }

                //遍历行
                foreach (var entity in item.SheetEntityList)
                {
                    IRow row = sheet.GetRow(entity.RowNumber);
                    foreach (var head in item.SheetHeadList)
                    {
                        //获取列
                        ICell cell = row.GetCell(head.ColumnIndex);

                        //设置生成下拉框的行和列
                        //var cellRegions = new NPOI.SS.Util.CellRangeAddressList((item.StartRowIndex.Value) + 1, sheet.LastRowNum, head.ColumnIndex, head.ColumnIndex);
                        var cellRegions = new NPOI.SS.Util.CellRangeAddressList(entity.RowNumber, entity.RowNumber, head.ColumnIndex, head.ColumnIndex);

                        IDataValidationHelper     dvHelper     = sheet.GetDataValidationHelper();
                        IDataValidationConstraint dvConstraint = null;
                        IDataValidation           dataValidate = null;
                        switch (head.ColumnType)
                        {
                        //列类型为文本
                        case Attribute.Enum.ColumnTypeEnum.Text:
                            break;

                        //列类型为日期
                        case Attribute.Enum.ColumnTypeEnum.Date:
                            head.Format  = string.IsNullOrEmpty(head.Format) ? "yyyy-MM-dd" : head.Format;
                            dvConstraint = dvHelper.CreateDateConstraint(OperatorType.BETWEEN, DateTime.MinValue.ToString(head.Format), DateTime.MaxValue.ToString(head.Format), head.Format);
                            dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
                            dataValidate.CreateErrorBox("输入不合法", "必须为日期");
                            dataValidate.ShowPromptBox = true;
                            break;

                        //列类型为浮点
                        case Attribute.Enum.ColumnTypeEnum.Decimal:
                            dvConstraint = dvHelper.CreateDecimalConstraint(OperatorType.BETWEEN, "0", "9999999999");
                            dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
                            dataValidate.CreateErrorBox("输入不合法", "必须在0~9999999999之间。");
                            dataValidate.ShowPromptBox = true;
                            break;

                        //列类型为选项
                        case Attribute.Enum.ColumnTypeEnum.Option:

                            List <string> options = null;

                            #region 全局列设置
                            if (item.ColumnOptions != null && item.ColumnOptions.Count > 0)
                            {
                                string key = null;
                                //如果头部名称存在则取头部名称(以头部名称设置选项)
                                if (item.ColumnOptions.Keys.Contains(head.HeadName) == true)
                                {
                                    key = head.HeadName;
                                }
                                //如果属性存在则取头部名称(以属性名称设置选项)
                                if (item.ColumnOptions.Keys.Contains(head.PropertyName) == true)
                                {
                                    key = head.PropertyName;
                                }

                                //不为空说明存在,则设置选项
                                if (key != null)
                                {
                                    options = item.ColumnOptions[key];
                                }
                            }
                            #endregion

                            #region 单个列设置
                            //行的优先级高于Sheet的优先级
                            if (entity.ColumnOptions != null && entity.ColumnOptions.Count > 0)
                            {
                                string key = null;
                                //如果头部名称存在则取头部名称(以头部名称设置选项)
                                if (entity.ColumnOptions.Keys.Contains(head.HeadName) == true)
                                {
                                    key = head.HeadName;
                                }
                                //如果属性存在则取头部名称(以属性名称设置选项)
                                if (entity.ColumnOptions.Keys.Contains(head.PropertyName) == true)
                                {
                                    key = head.PropertyName;
                                }

                                //不为空说明存在,则设置选项
                                if (key != null)
                                {
                                    options = entity.ColumnOptions[key];
                                }
                            }
                            #endregion

                            //不符合条件则跳出
                            if (options == null)
                            {
                                continue;
                            }
                            if (options.Count() > 0)
                            {
                                dvConstraint = dvHelper.CreateExplicitListConstraint(options.ToArray());
                                dataValidate = dvHelper.CreateValidation(dvConstraint, cellRegions);
                                dataValidate.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
                                dataValidate.ShowPromptBox = true;
                            }
                            break;
                        }

                        //类型在指定的范围内是才设置校验
                        if (dataValidate != null)
                        {
                            sheet.AddValidationData(dataValidate);
                        }
                    }
                }
            }
        }
コード例 #18
0
        /// <summary>
        /// 生成Sheet的级联指定
        /// </summary>
        private static void CreateCascadeSheet(ISheet sheet, MDataTable header, Dictionary <string, string[]> validateData, int maxLevel)
        {
            Dictionary <string, int> formatdic = new Dictionary <string, int>();

            MDataTable[] dt2 = header.Split(Config_Grid.Field + " like 'mg_%'");
            header = dt2[1];//去掉多级表头的数据。
            for (int i = 0; i < header.Rows.Count; i++)
            {
                string formater = header.Rows[i].Get <string>(Config_Grid.Formatter);
                if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
                {
                    formatdic.Add(formater, i);//存储列索引
                }
            }

            int maxRow = 1000;//限制最大行数(07之前版本的excel最大行数为65536,但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
            IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();


            for (int i = 0; i < header.Rows.Count; i++)
            {
                MDataRow dtRow = header.Rows[i];

                string formatter = dtRow.Get <string>(Config_Grid.Formatter);

                if (formatter == "boolFormatter")
                {
                    formatter = "#是否";                                                                                             //对bool型特殊处理。
                }
                if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
                {
                    //处理数据的有效性
                    CellRangeAddressList      regions      = null;
                    IDataValidationConstraint constraint   = null;
                    IDataValidation           dataValidate = null;
                    if (validateData.ContainsKey(formatter))
                    {
                        regions = new CellRangeAddressList(maxLevel, maxRow, i, i);
                        string key = formatter.Split('=')[0].Replace("#", "").Replace(" ", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
                        constraint   = dvHelper.CreateFormulaListConstraint(key);
                        dataValidate = dvHelper.CreateValidation(constraint, regions);
                        sheet.AddValidationData(dataValidate);
                    }
                    if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
                    {
                        string parentFormatter = formatter;
                        while (formatdic.ContainsKey(parentFormatter))
                        {
                            int point       = 0;
                            int parentindex = formatdic[parentFormatter];
                            formatdic.Remove(parentFormatter);
                            foreach (var item in formatdic)
                            {
                                if (item.Key.IndexOf('=') > -1)
                                {
                                    string parent = item.Key.Split('=')[1];
                                    parent = parent.Replace(">", "#");
                                    if (parent.Equals(parentFormatter.Split('=')[0]))
                                    {
                                        int    selfindex = item.Value;
                                        string t         = ConvertIndexToChar(parentindex);
                                        for (int im = maxLevel; im < maxRow; im++)
                                        {
                                            string func = string.Format("INDIRECT(${0}${1})", t, im + 1);
                                            regions      = new CellRangeAddressList(im, im, selfindex, selfindex);
                                            constraint   = dvHelper.CreateFormulaListConstraint(func);
                                            dataValidate = dvHelper.CreateValidation(constraint, regions);
                                            sheet.AddValidationData(dataValidate);
                                        }
                                        parentFormatter = item.Key;
                                        break;
                                    }
                                }
                                point += 1;
                            }
                            if (point.Equals(formatdic.Count))
                            {
                                parentFormatter = string.Empty;
                            }
                        }
                    }
                }
            }
        }
コード例 #19
0
ファイル: TableReset.cs プロジェクト: qipa/ScorpioConversion
    void ResetData(IWorkbook workbook, ISheet sheet)
    {
        var sheetIndex  = workbook.GetSheetIndex(sheet);
        var sheetName   = sheet.SheetName;
        var copySheet   = workbook.CreateSheet("copySheet");
        var columNumber = 0;

        for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
        {
            var row = sheet.GetRow(i);
            if (row == null)
            {
                continue;
            }
            var copyRow = copySheet.CreateRow(i);
            columNumber = System.Math.Max(columNumber, row.LastCellNum);
            for (int j = 0; j < row.LastCellNum; ++j)
            {
                var cell = row.GetCell(j);
                if (cell == null)
                {
                    continue;
                }
                var copyCell = copyRow.CreateCell(j);
                if (cell.CellStyle != null)
                {
                    copyCell.CellStyle = cell.CellStyle;
                }
                if (cell.CellComment != null)
                {
                    copyCell.CellComment = cell.CellComment;
                }
                if (cell.Hyperlink != null)
                {
                    copyCell.Hyperlink = cell.Hyperlink;
                }
                copyCell.SetCellType(cell.CellType);
                switch (cell.CellType)
                {
                case CellType.Numeric: copyCell.SetCellValue(cell.NumericCellValue); break;

                case CellType.String: copyCell.SetCellValue(cell.RichStringCellValue); break;

                case CellType.Formula: copyCell.SetCellFormula(cell.CellFormula); break;

                case CellType.Blank: copyCell.SetCellValue(cell.StringCellValue); break;

                case CellType.Boolean: copyCell.SetCellValue(cell.BooleanCellValue); break;

                case CellType.Error: copyCell.SetCellErrorValue(cell.ErrorCellValue); break;
                }
            }
        }
        for (int i = 0; i < columNumber; ++i)
        {
            copySheet.SetColumnWidth(i, sheet.GetColumnWidth(i));
        }
        sheet.GetDataValidations().ForEach((data) => { if (data.ErrorBoxTitle != EnumCheckError)
                                                       {
                                                           copySheet.AddValidationData(data);
                                                       }
                                           });
        for (int i = 0; i < sheet.SheetConditionalFormatting.NumConditionalFormattings; ++i)
        {
            var formatting = sheet.SheetConditionalFormatting.GetConditionalFormattingAt(i);
            var had        = false;
            for (var j = 0; j < formatting.NumberOfRules; ++j)
            {
                var rule = formatting.GetRule(j);
                if (rule.Formula1.EndsWith(EnumConditional))
                {
                    had = true;
                    break;
                }
            }
            if (!had)
            {
                copySheet.SheetConditionalFormatting.AddConditionalFormatting(formatting);
            }
        }
        var start = -1;

        for (var i = copySheet.FirstRowNum; i <= copySheet.LastRowNum; ++i)
        {
            var row = copySheet.GetRow(i);
            if (row == null)
            {
                continue;
            }
            var keyCell = row.GetCellString(0);
            if (keyCell == KEYWORD_BEGIN)
            {
                start = i;
                break;
            }
        }
        if (start == -1)
        {
            return;
        }
        for (var i = 0; i < mFields.Count; ++i)
        {
            var field = mFields[i];
            if (field.IsValid && !field.IsArray && field.IsEnum)
            {
                var cellRange = new CellRangeAddressList(start, 65535, i + 1, i + 1);
                //添加下拉框
                {
                    var helper         = sheet.GetDataValidationHelper();
                    var constraint     = helper.CreateExplicitListConstraint(mParser.GetEnumList(field.Type));
                    var dataValidation = helper.CreateValidation(constraint, cellRange);
                    dataValidation.SuppressDropDownArrow = true;
                    dataValidation.CreateErrorBox(EnumCheckError, "");
                    copySheet.AddValidationData(dataValidation);
                }
                //添加值验证
                {
                    var lineName = (i + 1).GetLineName();
                    var ands     = new List <string>();
                    Array.ForEach(mParser.GetEnumList(field.Type), (value) => { ands.Add($"{lineName}{start + 1}<>\"{value}\""); });
                    var conditional = copySheet.SheetConditionalFormatting.CreateConditionalFormattingRule(string.Format("=AND({0})+{1}", string.Join(",", ands.ToArray()), EnumConditional));
                    var pattern     = conditional.CreatePatternFormatting();
                    pattern.FillBackgroundColor = IndexedColors.Red.Index;
                    pattern.FillPattern         = FillPattern.SolidForeground;
                    copySheet.SheetConditionalFormatting.AddConditionalFormatting(cellRange.CellRangeAddresses, conditional);
                }
            }
        }
        workbook.RemoveSheetAt(sheetIndex);
        workbook.SetSheetOrder(copySheet.SheetName, sheetIndex);
        workbook.SetSheetName(sheetIndex, sheetName);
    }
コード例 #20
0
ファイル: ExcelHelper.cs プロジェクト: rivernli/SGP
        private static void buildTemplateSheet(IWorkbook workbook, ISheet sheet, ISheet sheetDS, List<WFActivityField> fields, ref int dsStartIndex, int sheetIndex)
        {
            if (fields != null)
            {
                IRow rowColumn = sheet.CreateRow(0);
                for (int i = 0; i < fields.Count; i++)
                {
                    ICell cell = rowColumn.CreateCell(i);
                    string colName = String.IsNullOrWhiteSpace(fields[i].DisplayName) ? fields[i].FieldName : fields[i].DisplayName;
                    cell.SetCellValue(colName);
                    ICellStyle cellStyle = workbook.CreateCellStyle();

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

                    switch (fields[i].DataType)
                    {
                        case FieldInfo.DATATYPE_LIST:
                        case FieldInfo.DATATYPE_LIST_SQL:
                            string lstFormulaName = fields[i].FieldName + "fn";
                            int dsEndIndex = BuildDataSource(fields[i], sheetDS, dsStartIndex);
                            if (dsEndIndex > dsStartIndex)
                            {
                                IName name = sheet.Workbook.CreateName();
                                name.RefersToFormula = String.Format("'DataSource'!$A${0}:$A${1}", dsStartIndex + 1, dsEndIndex);
                                name.NameName = lstFormulaName;
                                name.SheetIndex = sheetIndex;
                                CellRangeAddressList addressList = new CellRangeAddressList(1, 1, i, i);
                                IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
                                IDataValidationConstraint dvConstraint = dvHelper.CreateFormulaListConstraint(lstFormulaName);
                                IDataValidation validation = dvHelper.CreateValidation(dvConstraint, addressList);
                                sheet.AddValidationData(validation);
                                dsStartIndex = dsEndIndex;
                            }
                            break;
                    }
                }
            }
        }
コード例 #21
0
    //刷新表注释
    private void RefreshNote(string fileFullName)
    {
        bool change = false;

        for (int i = 0; i < mFields.Count; ++i)
        {
            var filed = mFields[i];
            if (filed.Enum)
            {
                change = true; break;
            }
        }
        if (!change)
        {
            return;
        }
        IWorkbook workbook  = new HSSFWorkbook(new FileStream(fileFullName, FileMode.Open, FileAccess.ReadWrite));
        ISheet    sheet     = workbook.GetSheetAt(0);
        string    sheetName = sheet.SheetName;
        ISheet    sheetBack = workbook.CreateSheet("__sheetBackup");

        sheetBack.CreateFreezePane(2, START_ROW);
        int columnNum = 0;

        for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
        {
            IRow row = sheet.GetRow(i);
            if (row == null)
            {
                continue;
            }
            if (row.LastCellNum > columnNum)
            {
                columnNum = row.LastCellNum;
            }
            IRow rowBack = sheetBack.CreateRow(i);
            for (int j = 0; j < (mMaxColumn == -1 ? row.LastCellNum : mMaxColumn); ++j)
            {
                string val  = Util.GetCellString(row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK));
                var    cell = rowBack.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                Util.SetCellString(cell, val);
            }
        }
        for (int i = 0; i < columnNum; ++i)
        {
            sheetBack.SetColumnWidth(i, sheet.GetColumnWidth(i));
        }
        for (int i = 0; i < mFields.Count; ++i)
        {
            var filed = mFields[i];
            if (filed.Enum)
            {
                IDataValidationHelper     helper         = sheet.GetDataValidationHelper();
                CellRangeAddressList      cellRange      = new CellRangeAddressList(START_ROW, 65535, i, i);
                IDataValidationConstraint constraint     = helper.CreateExplicitListConstraint(GetEnumList(filed.Type));
                IDataValidation           dataValidation = helper.CreateValidation(constraint, cellRange);
                dataValidation.SuppressDropDownArrow = false;
                dataValidation.CreatePromptBox(filed.Type, GetEnumComment(filed.Type));
                sheetBack.AddValidationData(dataValidation);
            }
        }
        workbook.RemoveSheetAt(0);
        workbook.SetSheetOrder(sheetBack.SheetName, 0);
        workbook.SetSheetName(0, sheetName);
        using (FileStream stream = new FileStream(fileFullName, FileMode.Create)) {
            workbook.Write(stream);
        }
    }
コード例 #22
0
 public IDataValidationHelper GetDataValidationHelper()
 {
     return(_sheet.GetDataValidationHelper());
 }