void Button2_Click(object sender, EventArgs e) { NpoiLib npl = new NpoiLib(); List<Row> list = npl.ReadExcel(Server.MapPath("text.xls"), 0); Response.Write("<table><tr><td>111</td><td>222</td><td>333</td></tr>"); int i = 0; string[] explicitListValues = new string[] { "AS", "CE CP", "SC IC", "Others" }; foreach (Row r in list) { if (r != null) { Cell c = r.GetCell(0); NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList(); ranglist.AddCellRangeAddress(new CellRangeAddress(0, 10, 0, 3)); DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(explicitListValues); HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint); ((HSSFSheet)c.Sheet).AddValidationData(dataValidation); string v1 = npl.ReadRowData(r, 0); string v2 = npl.ReadRowData(r, 1); string v3 = npl.ReadRowData(r, 2); Response.Write("<tr><td>" + v1 + "</td><td>" + v2 + "</td><td>" + v3 + "</td></tr>"); } } Response.Write("</table>"); }
static void Main(string[] args) { InitializeWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); ISheet sheet2 = hssfworkbook.CreateSheet("Sheet2"); //create three items in Sheet2 IRow row0 = sheet2.CreateRow(0); ICell cell0 = row0.CreateCell(4); cell0.SetCellValue("Product1"); row0 = sheet2.CreateRow(1); cell0 = row0.CreateCell(4); cell0.SetCellValue("Product2"); row0 = sheet2.CreateRow(2); cell0 = row0.CreateCell(4); cell0.SetCellValue("Product3"); CellRangeAddressList rangeList = new CellRangeAddressList(); //add the data validation to the first column (1-100 rows) rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, 0, 0)); DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint("Sheet2!$E1:$E3"); HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); //add the data validation to sheet1 ((HSSFSheet)sheet1).AddValidationData(dataValidation); WriteToFile(); }
/** * Constructor which Initializes the cell range on which this object will be * applied * @param constraint */ public HSSFDataValidation(CellRangeAddressList regions, IDataValidationConstraint constraint) { _regions = regions; //FIXME: This cast can be avoided. _constraint = (DVConstraint)constraint; }
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation) : base() { this.validationConstraint = constraint; this.ctDdataValidation = ctDataValidation; this.regions = regions; }
public CFHeaderRecord(RecordInputStream in1) { field_1_numcf = in1.ReadShort(); field_2_need_recalculation = in1.ReadShort(); field_3_enclosing_cell_range = new CellRangeAddress(in1); field_4_cell_ranges = new CellRangeAddressList(in1); }
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(); }
public XSSFDataValidation(CellRangeAddressList regions, CT_DataValidation ctDataValidation) : base() { this.validationConstraint = GetConstraint(ctDataValidation); this.ctDdataValidation = ctDataValidation; this.regions = regions; this.ctDdataValidation.errorStyle = (ST_DataValidationErrorStyle.stop); this.ctDdataValidation.allowBlank = (true); }
public CellRangeAddressList Copy() { CellRangeAddressList result = new CellRangeAddressList(); int nItems = _list.Count; for (int k = 0; k < nItems; k++) { CellRangeAddress region = (CellRangeAddress)_list[k]; result.AddCellRangeAddress(region.Copy()); } return(result); }
public void TestPLVRecord1() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(XLS_FILENAME); HSSFWorkbook workbook = new HSSFWorkbook(is1); CellRangeAddressList cellRange = new CellRangeAddressList(0, 0, 1, 1); IDataValidationConstraint constraint = DVConstraint.CreateFormulaListConstraint(DV_DEFINITION); HSSFDataValidation dataValidation = new HSSFDataValidation(cellRange, constraint); // This used to throw an error before try { workbook.GetSheet(SHEET_NAME).AddValidationData(dataValidation); } catch (InvalidOperationException) { Assert.Fail("Identified bug 53972, PLV record breaks addDataValidation()"); } }
public void Test53965() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.CreateSheet() as XSSFSheet; List<XSSFDataValidation> 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); }
//private BaseForm getCurrentForm() //{ // BaseForm baseForm = null; // using (Repositories.BaseFormRepository repository = new Repositories.BaseFormRepository(null)) // { // baseForm = repository.getInstanceByPeriodDate(DateTime.Now); // } // return baseForm; //} /// <summary> /// Traduz de Questionário da Amarribo para arquivo excel (Workbook) /// </summary> /// <param name="baseForm">Questionário de transparência</param> /// <param name="protectSheets">Flag de proteção do questionário</param> /// <returns></returns> private XSSFWorkbook translateFormToWorkBook(BaseForm baseForm, bool protectSheets, long rfId = 0) { try { XSSFWorkbook wb = null; if (baseForm != null) { wb = createWorkBook(); IFont titleFont = wb.CreateFont(); IFont subTitleFont = wb.CreateFont(); IFont titleQuestion = wb.CreateFont(); titleFont.FontHeight = 20; subTitleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; subTitleFont.FontHeight = 15; titleQuestion.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; ICellStyle cellTitleStyle = wb.CreateCellStyle(); //cellTitleStyle.Alignment = HorizontalAlignment.Center; cellTitleStyle.SetFont(titleFont); ICellStyle cellSubTitleStyle = wb.CreateCellStyle(); cellSubTitleStyle.SetFont(subTitleFont); //cellSubTitleStyle.Alignment = HorizontalAlignment.Center; ICellStyle cellTitleQuestionStyle = wb.CreateCellStyle(); cellTitleQuestionStyle.SetFont(titleQuestion); ICellStyle cellQuestion = wb.CreateCellStyle(); cellQuestion.WrapText = true; ICellStyle cellAnswer = wb.CreateCellStyle(); cellAnswer.IsLocked = false; ICellStyle cellHidden = wb.CreateCellStyle(); cellHidden.IsHidden = true; if (baseForm.BaseBlocks != null) { //Será criada uma sheet única para todas as perguntas XSSFSheet sheet = null; //Total rows no atual bloco int row = 0; //Titulo da aba tem limite de 30 caracteres. var sheetName = baseForm.Name.Length > 30 ? baseForm.Name.Substring(0, 30) : baseForm.Name; sheet = (XSSFSheet)wb.CreateSheet(Commons.StringUtils.removeSpecialCaracters(sheetName)); if (protectSheets) sheet.ProtectSheet(PROTECT_SHEET_PASSWORD); if (baseForm.BaseBlocks != null) { if (baseForm.BaseBlocks.Count > 0) { int count = 1; foreach (var bb in baseForm.BaseBlocks) { if (bb.BaseSubBlocks != null && bb.BaseSubBlocks.Count > 0) { sheet.CreateRow(row).CreateCell(0).SetCellValue(bb.Name); sheet.GetRow(row).GetCell(0).CellStyle = cellTitleStyle; sheet.GetRow(row).CreateCell(4).SetCellValue("B"); //Merge celula do titulo sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 3)); row++; //Para cada subbloco foreach (var bsb in bb.BaseSubBlocks) { sheet.CreateRow(row).CreateCell(0).SetCellValue(bsb.Name); sheet.GetRow(row).GetCell(0).CellStyle = cellSubTitleStyle; sheet.GetRow(row).CreateCell(4).SetCellValue("S"); //Merge celula do titulo sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 3)); row++; //Adiciona os subtitulos sheet.CreateRow(row).CreateCell(0).SetCellValue("Índice"); sheet.GetRow(row).GetCell(0).CellStyle = cellTitleQuestionStyle; sheet.GetRow(row).CreateCell(1).SetCellValue("Pergunta"); sheet.GetRow(row).GetCell(1).CellStyle = cellTitleQuestionStyle; sheet.GetRow(row).CreateCell(2).SetCellValue("Resposta"); sheet.GetRow(row).GetCell(2).CellStyle = cellTitleQuestionStyle; sheet.GetRow(row).CreateCell(3).SetCellValue("Observações"); sheet.GetRow(row).GetCell(3).CellStyle = cellTitleQuestionStyle; sheet.GetRow(row).CreateCell(4).SetCellValue("TP"); row++; if (bsb.BaseQuestions != null && bsb.BaseQuestions.Count > 0) { //Utilizado para gerar com respostas //var rand = new Random(DateTime.Now.Millisecond); int initRow = row; foreach (var bq in bsb.BaseQuestions) { Entities.Answer answer = null; if (rfId > 0) { answer = bq.Answers.Where(f => f.ResponseFormId == rfId).FirstOrDefault(); } //Indice da pergunta sheet.CreateRow(row).CreateCell(0).SetCellValue(count); //Pergunta //sheet.GetRow(row).CreateCell(1).SetCellValue(bq.Question); ICell cellQ = sheet.GetRow(row).CreateCell(1); cellQ.CellStyle = cellQuestion; formatStyleQuestion(bq.Question, ref cellQ, ref wb); //Adiciona o campo com dropdown e libera para edição //int r = rand.Next(1, 4); if (answer != null && rfId > 0) { if (answer.Score.HasValue) { sheet.GetRow(row).CreateCell(2).SetCellValue(answer.Score.Value.ToString());//("");//(actions[r]) } else { sheet.GetRow(row).CreateCell(2).SetCellValue("N/A");//("");//(actions[r]) } } else { sheet.GetRow(row).CreateCell(2).SetCellValue("");//("");//(actions[r]) } sheet.GetRow(row).GetCell(2).CellStyle = cellAnswer; //observações. sheet.GetRow(row).CreateCell(3).SetCellValue(""); sheet.GetRow(row).GetCell(3).CellStyle = cellAnswer; //Tipo da linha sheet.GetRow(row).CreateCell(4).SetCellValue("P"); sheet.GetRow(row).GetCell(4).CellStyle = cellHidden; //Adiciona o id da pergunta sheet.GetRow(row).CreateCell(5).SetCellValue(bq.Id); sheet.GetRow(row).GetCell(5).CellStyle = cellHidden; //Id do formulario sheet.GetRow(row).CreateCell(6).SetCellValue(bq.BaseSubBlock.BaseBlock.BaseFormId); sheet.GetRow(row).GetCell(6).CellStyle = cellHidden; //Id do responseForm sheet.GetRow(row).CreateCell(7).SetCellValue(rfId); sheet.GetRow(row).GetCell(7).CellStyle = cellHidden; row++; count++; } XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(actions); CellRangeAddressList addressList = new CellRangeAddressList(initRow, row - 1, 2, 2); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList); validation.ShowErrorBox = true; validation.CreateErrorBox("Valor inválido", "Valor inserido não é permitido"); sheet.AddValidationData(validation); } } } } } } sheet.SetColumnWidth(1, 25500); sheet.SetColumnWidth(3, 25500); sheet.SetColumnWidth(4, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna sheet.SetColumnWidth(5, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna sheet.SetColumnWidth(6, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna sheet.SetColumnWidth(7, 0); //0 de width para esconder a coluna do id, NPOI não tem solução para hide de coluna } } return wb; } catch (Exception ex) { Lib.Log.ErrorLog.saveError("Lib.Utils.ExcelUtils.translateFormToWorkBook", ex); throw ex; } }
//YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support public List<XSSFDataValidation> GetDataValidations() { List<XSSFDataValidation> xssfValidations = new List<XSSFDataValidation>(); CT_DataValidations dataValidations = this.worksheet.dataValidations; if (dataValidations != null && dataValidations.count > 0) { foreach (CT_DataValidation ctDataValidation in dataValidations.dataValidation) { CellRangeAddressList addressList = new CellRangeAddressList(); List<String> sqref = ctDataValidation.sqref; foreach (String stRef in sqref) { String[] regions = stRef.Split(new char[] { ' ' }); for (int i = 0; i < regions.Length; i++) { String[] parts = regions[i].Split(new char[] { ':' }); CellReference begin = new CellReference(parts[0]); CellReference end = parts.Length > 1 ? new CellReference(parts[1]) : begin; CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.Row, end.Row, begin.Col, end.Col); addressList.AddCellRangeAddress(cellRangeAddress); } } XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation); xssfValidations.Add(xssfDataValidation); } } return xssfValidations; }
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); }
/* * (non-Javadoc) * * @see * NPOI.SS.UserModel.DataValidationHelper#CreateValidation(org * .apache.poi.SS.UserModel.DataValidationConstraint, * NPOI.SS.Util.CellRangeAddressList) */ public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList) { return new HSSFDataValidation(cellRangeAddressList, constraint); }
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); }
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); }
/** * Constructs a DV record and Sets its fields appropriately. * * @param in the RecordInputstream to Read the record from */ public DVRecord(RecordInputStream in1) { _option_flags = in1.ReadInt(); _promptTitle = ReadUnicodeString(in1); _errorTitle = ReadUnicodeString(in1); _promptText = ReadUnicodeString(in1); _errorText = ReadUnicodeString(in1); int field_size_first_formula = in1.ReadUShort(); _not_used_1 = in1.ReadShort(); //read first formula data condition _formula1 = NPOI.SS.Formula.Formula.Read(field_size_first_formula, in1); int field_size_sec_formula = in1.ReadUShort(); _not_used_2 = in1.ReadShort(); //read sec formula data condition _formula2 = NPOI.SS.Formula.Formula.Read(field_size_sec_formula, in1); //read cell range address list with all affected ranges _regions = new CellRangeAddressList(in1); }
/** * Constructor which initializes the cell range on which this object will be * applied * @param constraint */ public HSSFDataValidation(CellRangeAddressList regions, DVConstraint constraint) { _regions = regions; _constraint = constraint; }
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); }
private void CopyRowAreaApply(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum) { var getLastRowNum = destinationWorksheet.LastRowNum; for (int i = 0; i < rowNum; i++) { CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i); } var markListConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "氣體", "液體", "固體" }); var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null); var markUnitLongConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "kcal/m³", "kcal/L", "kcal/kg" }); var markUnitShortConstraint = DVConstraint.CreateExplicitListConstraint(new string[] { "m³", "kL", "kg" }); var markColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 3, 3); var markdv = new HSSFDataValidation(markColumn, markListConstraint); var markULColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 6, 6); var markULdv = new HSSFDataValidation(markULColumn, markUnitLongConstraint); var markSLColumn = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 8, 8); var markSLdv = new HSSFDataValidation(markSLColumn, markUnitShortConstraint); var markNumColumn_5 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 5, 5); var markdvNum_5 = new HSSFDataValidation(markNumColumn_5, markNumConstraint); var markNumColumn_7 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 7, 7); var markdvNum_7 = new HSSFDataValidation(markNumColumn_7, markNumConstraint); var markNumColumn_9 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 9, 9); var markdvNum_9 = new HSSFDataValidation(markNumColumn_9, markNumConstraint); var markNumColumn_10 = new CellRangeAddressList(getLastRowNum, getLastRowNum, 10, 10); var markdvNum_10 = new HSSFDataValidation(markNumColumn_10, markNumConstraint); destinationWorksheet.AddValidationData(markdv); destinationWorksheet.AddValidationData(markULdv); destinationWorksheet.AddValidationData(markSLdv); destinationWorksheet.AddValidationData(markdvNum_5); destinationWorksheet.AddValidationData(markdvNum_7); destinationWorksheet.AddValidationData(markdvNum_9); destinationWorksheet.AddValidationData(markdvNum_10); }
//数据有效性以及下拉框的设置 private static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo) { CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index); DVConstraint constraint = null; HSSFDataValidation dataValidate = null; if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable) { IEnumerable<IDecoderItem> data = GetDecoderItem(fieldInfo); if (data != null) { List<string> optionList = new List<string>(); foreach (IDecoderItem item in data) { if (item != null) { TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null); optionList.Add(item.Name); } } constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray()); } } else { if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox) { constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√", "" }); } else { switch (fieldInfo.DataType) { case TkDataType.DateTime: case TkDataType.Date: constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd"); break; case TkDataType.Double: case TkDataType.Decimal: case TkDataType.Money: constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0"); break; case TkDataType.Long: case TkDataType.Int: case TkDataType.Short: case TkDataType.Byte: case TkDataType.Bit: constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0"); break; default: break; } } } if (constraint != null) { dataValidate = new HSSFDataValidation(region, constraint); dataValidate.CreateErrorBox("error", "You must input a valid value!"); } return dataValidate; }
/** Creates new CFHeaderRecord */ public CFHeaderRecord() { field_4_cell_ranges = new CellRangeAddressList(); }
public ActionResult Template(int? tagId) { var basicInfoSheetName = ProUploadService.BASIC_SHEET; var moreInfoSheetName = ProUploadService.MORE_SHEET; var supportSheetName = "不要修改"; var headerLabels = new Dictionary<string, dynamic>() { {"商品代码",new {dataformat=0,width=10}}, {"商品名称",new {dataformat=0,width=20}}, {"描述",new {dataformat=0,width=50}}, {"吊牌价",new {dataformat=2,width=8}}, {"现价",new {dataformat=2,width=8}}, {"品牌名",new {dataformat=0,width=20}}, { "分类名",new {dataformat=0,width=20}}, {"门店名",new {dataformat=0,width=20}}, { "促销活动编码",new {dataformat=0,width=20}}, { "专题编码(多个以,分割)",new {dataformat=0,width=20}}, {"可销售",new {dataformat=2,width=5}}, {"商品货号",new {dataformat=0,width=10}} }; var workbook = new HSSFWorkbook(); var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.BorderBottom = BorderStyle.THIN; headerLabelCellStyle.BorderLeft = BorderStyle.THIN; headerLabelCellStyle.BorderRight = BorderStyle.THIN; headerLabelCellStyle.BorderTop = BorderStyle.THIN; headerLabelCellStyle.WrapText = true; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); //set support sheet var supportSheet = workbook.CreateSheet(supportSheetName); workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true); Func<int, dynamic, int> supportFill = (rowIndex, data) => { var brandRow = supportSheet.CreateRow(rowIndex++); var brandCodeCol = brandRow.CreateCell(1); brandCodeCol.SetCellType(CellType.STRING); brandCodeCol.SetCellValue(data.Id); var brandNameCol = brandRow.CreateCell(2); brandNameCol.SetCellType(CellType.STRING); brandCodeCol.SetCellValue(data.Name); return rowIndex; }; int brandRowIndex = 0; foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name })) { brandRowIndex = supportFill(brandRowIndex, brand); } int tagRowIndex = brandRowIndex; var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }); //if (tagId.HasValue) // tagLinq = tagLinq.Where(t => t.Id == tagId.Value); foreach (var tag in tagLinq.OrderBy(t => t.Name)) { tagRowIndex = supportFill(tagRowIndex, tag); } int storeRowIndex = tagRowIndex; foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name })) { storeRowIndex = supportFill(storeRowIndex, store); } //set basic sheet var sheet1 = workbook.CreateSheet(basicInfoSheetName); //workbook.SetSheetOrder(basicInfoSheetName, 0); var rowFirst = sheet1.CreateRow(0); Action<int, string, dynamic> cellSetting = (cellindex, desc, option) => { var cell = rowFirst.CreateCell(cellindex); cell.SetCellType(CellType.STRING); cell.SetCellValue(desc); cell.CellStyle = headerLabelCellStyle; sheet1.SetColumnWidth(cellindex, option.width * 255); var currentCellStyle = workbook.CreateCellStyle(); currentCellStyle.DataFormat = (short)option.dataformat; sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle); }; int index = 0; foreach (var key in headerLabels.Keys) { cellSetting(index++, key, headerLabels[key]); } //set constraint DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex)); CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5); HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint); branddataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(branddataValidation); DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex)); CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6); HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint); tagdataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(tagdataValidation); DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex)); CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7); HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint); storedataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(storedataValidation); DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO }); CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10); HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint); is4saledataValidation.SuppressDropDownArrow = false; sheet1.AddValidationData(is4saledataValidation); //set sheet2 //create property value sheet var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted) .Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i }) .OrderBy(b => b.P.CategoryId) .ThenBy(b => b.P.PropertyDesc); int propertyRowIndex = storeRowIndex; foreach (var tag in tagLinq) { var propertyFromIndex = propertyRowIndex; foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc})) { propertyRowIndex = supportFill(propertyRowIndex, property); } if (propertyRowIndex > propertyFromIndex) { var rName = workbook.CreateName(); rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex); rName.NameName = tag.Name; } foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id)) { int valueFromIndex = propertyRowIndex; foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc })) { propertyRowIndex = supportFill(propertyRowIndex, pvalue); } if (propertyRowIndex > valueFromIndex) { var rName = workbook.CreateName(); rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex); rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc); } } } var moreheaderLabels = new Dictionary<string, dynamic>() { {"商品代码",new {dataformat=0,width=10}}, {"属性名",new {dataformat=0,width=20}}, {"属性值",new {dataformat=0,width=10}} }; var sheet2 = workbook.CreateSheet(moreInfoSheetName); var morerowFirst = sheet2.CreateRow(0); int moreCellIndex = 0; foreach (var key in moreheaderLabels.Keys) { var cell = morerowFirst.CreateCell(moreCellIndex); cell.SetCellType(CellType.STRING); cell.SetCellValue(key); cell.CellStyle = headerLabelCellStyle; sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255); var currentCellStyle = workbook.CreateCellStyle(); currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat; sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle); moreCellIndex++; } for (int i = 3; i <= 11; i++) { var cell = morerowFirst.CreateCell(i); cell.SetCellType(CellType.STRING); cell.SetCellValue(string.Empty); cell.CellStyle = headerLabelCellStyle; sheet2.SetColumnWidth(i, 10 * 255); } //set merge cell for last cell sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11)); //set constraint DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000)); CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0); HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint); codedataValidation.SuppressDropDownArrow = false; sheet2.AddValidationData(codedataValidation); for (int i = 1; i < 1000; i++) { //create hide helper cell sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName), new CellRangeAddress(i,i,12,12)); //create hide helper cell sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName), new CellRangeAddress(i, i, 13, 13)); sheet2.SetColumnHidden(12, true); sheet2.SetColumnHidden(13, true); DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex)); CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1); HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint); pdataValidation.SuppressDropDownArrow = false; sheet2.AddValidationData(pdataValidation); //set constraint DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1)); CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11); HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint); pvdataValidation.SuppressDropDownArrow = false; sheet2.AddValidationData(pvdataValidation); } workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1)); var ms = new MemoryStream(); workbook.Write(ms); ms.Position = 0; var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls"; return File(ms, "application/vnd.ms-excel", downloadName); }
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 CellRangeAddressList Copy() { CellRangeAddressList result = new CellRangeAddressList(); int nItems = _list.Count; for (int k = 0; k < nItems; k++) { CellRangeAddress region = (CellRangeAddress)_list[k]; result.AddCellRangeAddress(region.Copy()); } return result; }
public DVRecord(int validationType, int operator1, int errorStyle, bool emptyCellAllowed, bool suppressDropDownArrow, bool isExplicitList, bool showPromptBox, String promptTitle, String promptText, bool showErrorBox, String errorTitle, String errorText, Ptg[] formula1, Ptg[] formula2, CellRangeAddressList regions) { int flags = 0; flags = opt_data_type.SetValue(flags, validationType); flags = opt_condition_operator.SetValue(flags, operator1); flags = opt_error_style.SetValue(flags, errorStyle); flags = opt_empty_cell_allowed.SetBoolean(flags, emptyCellAllowed); flags = opt_suppress_dropdown_arrow.SetBoolean(flags, suppressDropDownArrow); flags = opt_string_list_formula.SetBoolean(flags, isExplicitList); flags = opt_show_prompt_on_cell_selected.SetBoolean(flags, showPromptBox); flags = opt_show_error_on_invalid_value.SetBoolean(flags, showErrorBox); _option_flags = flags; _promptTitle = ResolveTitleText(promptTitle); _promptText = ResolveTitleText(promptText); _errorTitle = ResolveTitleText(errorTitle); _errorText = ResolveTitleText(errorText); _formula1 = NPOI.SS.Formula.Formula.Create(formula1); _formula2 = NPOI.SS.Formula.Formula.Create(formula2); _regions = regions; }
/// <summary> /// 生成下拉列表 /// </summary> /// <param name="sheet"></param> /// <param name="firstRow"></param> /// <param name="lastRow"></param> /// <param name="FirstCol"></param> /// <param name="LastRow"></param> /// <param name="arr"></param> public void WriteDropDownList(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] arr) { NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList(); ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol)); DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(arr); HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint); ((HSSFSheet)sheet).AddValidationData(dataValidation); }
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); }
private void CopyRowAreaApplyDetail(HSSFWorkbook workbook, ISheet sourceWorksheet, ISheet destinationWorksheet, int sourceRowNum, int destinationRowNum, int rowNum) { var getLastRowNum = destinationWorksheet.LastRowNum; for (int i = 0; i < rowNum; i++) { CopyRow(workbook, sourceWorksheet, destinationWorksheet, sourceRowNum + i, destinationRowNum + i); } var markNumConstraint = DVConstraint.CreateNumericConstraint(0x02, 0x06, "0", null); var markNumColumn_4 = new CellRangeAddressList(getLastRowNum, getLastRowNum + rowNum, 4, 4); var markdvNum_4 = new HSSFDataValidation(markNumColumn_4, markNumConstraint); destinationWorksheet.AddValidationData(markdvNum_4); }
/// <summary> /// 生成下拉列表 /// 此方法暂时不能使用 /// </summary> /// <param name="sheet"></param> /// <param name="firstRow"></param> /// <param name="lastRow"></param> /// <param name="FirstCol"></param> /// <param name="LastCol"></param> /// <param name="arr"></param> public void WriteDropDownList2(Sheet sheet, int firstRow, int lastRow, int FirstCol, int LastCol, string[] data) { string tmpShtDictionary = "tmpShtDictionary"; Sheet tmpSheet = _workbook.CreateSheet(tmpShtDictionary); for (int i = 0; i < data.Length; i++) { tmpSheet.CreateRow(i).CreateCell(0).SetCellValue(data[i]); } Name range = _workbook.CreateName(); range.RefersToFormula = tmpShtDictionary + "!$A1:$A" + data.Length.ToString(); range.NameName = "dicRange"; NPOI.SS.Util.CellRangeAddressList ranglist = new NPOI.SS.Util.CellRangeAddressList(); ranglist.AddCellRangeAddress(new CellRangeAddress(firstRow, lastRow, FirstCol, LastCol)); DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(range.NameName); HSSFDataValidation dataValidation = new HSSFDataValidation(ranglist, dvconstraint); ((HSSFSheet)sheet).AddValidationData(dataValidation); _workbook.RemoveSheetAt(_workbook.GetSheetIndex(tmpSheet)); _workbook.RemoveName(range.NameName); }