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 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); }
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 TestGetDataValidationsDate() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; List<IDataValidation> list = sheet.GetDataValidations(); Assert.AreEqual(0, list.Count); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateDateConstraint(OperatorType.EQUAL, "2014/10/25", null, null); CellRangeAddressList AddressList = new CellRangeAddressList(0, 0, 0, 0); IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList); sheet.AddValidationData(validation); list = sheet.GetDataValidations(); // <-- works Assert.AreEqual(1, list.Count); HSSFDataValidation dv = list[(0)] as HSSFDataValidation; DVConstraint c = dv.Constraint; Assert.AreEqual(ValidationType.DATE, c.GetValidationType()); Assert.AreEqual(OperatorType.EQUAL, c.Operator); Assert.AreEqual(null, c.Formula1); Assert.AreEqual(null, c.Formula2); Assert.AreEqual(DateUtil.GetExcelDate(DateUtil.ParseYYYYMMDDDate("2014/10/25")), c.Value1); Assert.AreEqual(double.NaN, c.Value2); }
public void 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); }
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(); } }
public void TestCreateMultipleRegionsValidation() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" }); CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(); cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0); cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1); cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2); XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation; sheet.AddValidationData(dataValidation); Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]); Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]); Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]); Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref); } finally { wb.Close(); } }
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]); }
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); }
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(); }
/// <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); }
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); }
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); }
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); }
/// <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); }
/// <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)); } }
private XSSFDataValidation CreateValidation(XSSFSheet sheet) { //create the cell that will have the validation applied IRow row = sheet.CreateRow(0); row.CreateCell(0); IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper(); IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("true"); XSSFDataValidation validation = (XSSFDataValidation)dataValidationHelper.CreateValidation(constraint, new CellRangeAddressList(0, 0, 0, 0)); return(validation); }
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)); } }
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); }
/// <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 }
private IDataValidationConstraint CreateConstraint(IDataValidationHelper dataValidationHelper, int operatorType, string firstFormula, string secondFormula, string[] explicitListValues) { if (_validationType == ValidationType.LIST) { if (explicitListValues != null) { return(dataValidationHelper.CreateExplicitListConstraint(explicitListValues)); } return(dataValidationHelper.CreateFormulaListConstraint(firstFormula)); } if (_validationType == ValidationType.TIME) { return(dataValidationHelper.CreateTimeConstraint(operatorType, firstFormula, secondFormula)); } if (_validationType == ValidationType.DATE) { return(dataValidationHelper.CreateDateConstraint(operatorType, firstFormula, secondFormula, null)); } if (_validationType == ValidationType.FORMULA) { return(dataValidationHelper.CreateCustomConstraint(firstFormula)); } if (_validationType == ValidationType.INTEGER) { return(dataValidationHelper.CreateintConstraint(operatorType, firstFormula, secondFormula)); } if (_validationType == ValidationType.DECIMAL) { return(dataValidationHelper.CreateDecimalConstraint(operatorType, firstFormula, secondFormula)); } if (_validationType == ValidationType.TEXT_LENGTH) { return(dataValidationHelper.CreateTextLengthConstraint(operatorType, firstFormula, secondFormula)); } return(null); }
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); }
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); }
/// <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; } } } } } }
/// <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); } } } } }
private IDataValidationConstraint CreateConstraint(IDataValidationHelper dataValidationHelper, int operatorType, String firstFormula, String secondFormula, String[] explicitListValues) { if (_validationType == ValidationType.LIST) { if (explicitListValues != null) { return dataValidationHelper.CreateExplicitListConstraint(explicitListValues); } return dataValidationHelper.CreateFormulaListConstraint(firstFormula); } if (_validationType == ValidationType.TIME) { return dataValidationHelper.CreateTimeConstraint(operatorType, firstFormula, secondFormula); } if (_validationType == ValidationType.DATE) { return dataValidationHelper.CreateDateConstraint(operatorType, firstFormula, secondFormula, null); } if (_validationType == ValidationType.FORMULA) { return dataValidationHelper.CreateCustomConstraint(firstFormula); } if (_validationType == ValidationType.INTEGER) { return dataValidationHelper.CreateintConstraint(operatorType, firstFormula, secondFormula); } if (_validationType == ValidationType.DECIMAL) { return dataValidationHelper.CreateDecimalConstraint(operatorType, firstFormula, secondFormula); } if (_validationType == ValidationType.TEXT_LENGTH) { return dataValidationHelper.CreateTextLengthConstraint(operatorType, firstFormula, secondFormula); } return null; }
//刷新表注释 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); } }
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); }
/// <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; } }
/// <summary> /// 导出Excel DataTable /// </summary> /// <param name="records">records必须都为DataTable</param> /// <param name="formatter">Dictionary key:DataTable中的列明此处必须小写 value:EnumColumnTrans</param> /// <returns></returns> public byte[] Write(DataTable records, Dictionary <string, ExcelFormatter> formatter = null, string imgBasepath = "") { Stopwatch sw = new Stopwatch(); sw.Start(); var workBook = new XSSFWorkbook(); MemoryStream ms = new MemoryStream(); var sheet = workBook.CreateSheet(); var headerRow = sheet.CreateRow(0); var cellIndex = 0; Color lightGrey = Color.FromArgb(221, 221, 221); ICellStyle cstyle = workBook.CreateCellStyle(); cstyle.Alignment = HorizontalAlignment.Center; cstyle.IsLocked = true; // cstyle.FillForegroundColor = new XSSFColor(lightGrey).Indexed; cstyle.FillForegroundColor = IndexedColors.Grey25Percent.Index; foreach (var map in Maps) { var hcell = headerRow.CreateCell(cellIndex, CellType.String); hcell.CellStyle = cstyle; hcell.SetCellValue(map.Name); cellIndex++; } IDataValidationHelper dvHelper = sheet.GetDataValidationHelper(); var rowIndex = 1; IDrawing patriarch = sheet.CreateDrawingPatriarch(); bool isimg = false; foreach (DataRow record in records.Rows) { var dr = sheet.CreateRow(rowIndex); for (int i = 0; i < Maps.Count; i++) { string drValue = record[Maps[i].Info.ToString()].ToString(); ICell cell = dr.CreateCell(i); if (formatter.Any() && formatter.ContainsKey(Maps[i].Info.ToLower()) && formatter[Maps[i].Info.ToLower()] != null) { ExcelFormatter excelFormatter = formatter[Maps[i].Info.ToLower()]; if (!string.IsNullOrEmpty(drValue)) { if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertDownList) { cell.SetCellValue(drValue); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(excelFormatter.DropSource.Split(',')); CellRangeAddressList regions = new CellRangeAddressList(1, 65535, i, i); XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions); sheet.AddValidationData(dataValidate); } else if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertImage) { if (File.Exists(@"" + imgBasepath + drValue)) { if (!isimg) { sheet.SetColumnWidth(i, 256 * 20); isimg = true; } dr.HeightInPoints = 90; byte[] bytes = System.IO.File.ReadAllBytes(@"" + imgBasepath + drValue); int pictureIdx = workBook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG); IClientAnchor anchor = new XSSFClientAnchor(100, 50, 0, 0, i, rowIndex, i + 1, rowIndex + 1); IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(0.3); } else { cell.SetCellValue(""); } } else { cell.SetCellValue(FormatterCoulumn(drValue, excelFormatter.ColumnTrans)); } } else { cell.SetCellValue(drValue); } } else { switch (records.Columns[Maps[i].Info].DataType.ToString()) { case "System.String": //字符串类型 cell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(dateV); //cell.CellStyle = break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 cell.SetCellValue(""); break; default: cell.SetCellValue(""); break; } } } rowIndex++; } workBook.Write(ms); byte[] buffer = ms.ToArray(); ms.Close(); sw.Stop(); return(buffer); }