public HSSFAutoFilter(string formula, HSSFWorkbook workbook) { //this.workbook = workbook; Ptg[] ptgs = HSSFFormulaParser.Parse(formula, workbook); if (!(ptgs[0] is Area3DPtg)) { throw new ArgumentException("incorrect formula"); } Area3DPtg ptg = (Area3DPtg)ptgs[0]; HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(ptg.ExternSheetIndex); //look for the prior record int loc = sheet.Sheet.FindFirstRecordLocBySid(DefaultColWidthRecord.sid); CreateFilterModeRecord(sheet, loc + 1); CreateAutoFilterInfoRecord(sheet, loc + 2, ptg); //look for "_FilterDatabase" NameRecord of the sheet NameRecord name = workbook.Workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1); if (name == null) { name = workbook.Workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1); } name.IsHiddenName = true; name.NameDefinition = ptgs; }
public void TestDiscontinousReference() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls"); HSSFWorkbook wb = new HSSFWorkbook(is1); NPOI.HSSF.Model.Workbook workbook = wb.Workbook; HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual(1, wb.NumberOfNames); String sheetName = "Tabelle1"; String rawRefA = "$C$10:$C$14"; String rawRefB = "$C$16:$C$18"; String refA = sheetName + "!" + rawRefA; String refB = sheetName + "!" + rawRefB; String ref1 = refA + "," + refB; // Check the low level record NameRecord nr = workbook.GetNameRecord(0); Assert.IsNotNull(nr); Assert.AreEqual("test", nr.NameText); Ptg[] def = nr.NameDefinition; Assert.AreEqual(4, def.Length); MemFuncPtg ptgA = (MemFuncPtg)def[0]; Area3DPtg ptgB = (Area3DPtg)def[1]; Area3DPtg ptgC = (Area3DPtg)def[2]; UnionPtg ptgD = (UnionPtg)def[3]; Assert.AreEqual("", ptgA.ToFormulaString()); Assert.AreEqual(refA, ptgB.ToFormulaString(eb)); Assert.AreEqual(refB, ptgC.ToFormulaString(eb)); Assert.AreEqual(",", ptgD.ToFormulaString()); Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // Check the high level definition int idx = wb.GetNameIndex("test"); Assert.AreEqual(0, idx); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(idx); // Should have 2 references Assert.AreEqual(ref1, aNamedCell.RefersToFormula); // Check the parsing of the reference into cells Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula)); AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula); Assert.AreEqual(2, arefs.Length); Assert.AreEqual(refA, arefs[0].FormatAsString()); Assert.AreEqual(refB, arefs[1].FormatAsString()); for (int i = 0; i < arefs.Length; i++) { AreaReference ar = arefs[i]; ConfirmResolveCellRef(wb, ar.FirstCell); ConfirmResolveCellRef(wb, ar.LastCell); } }
public ValueEval GetArea3DEval(Area3DPtg aptg) { SheetRangeEvaluator sre = CreateExternSheetRefEvaluator(aptg.ExternSheetIndex); return(new LazyAreaEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn, sre)); }
public static HSSFSheet GetPtgSheet(HSSFSheet sheet, Ptg ptg) { if (ptg is RefPtg) { return(sheet); } if (ptg is Ref3DPtg) { Ref3DPtg ptg3 = (Ref3DPtg)ptg; int idx = sheet.book.GetSheetIndexFromExternSheetIndex(ptg3.ExternSheetIndex); if (idx == -1) { return(null); } return(sheet.Workbook.GetSheetAt(idx) as HSSFSheet); } if (ptg is Area2DPtgBase) { return(sheet); } if (ptg is Area3DPtg) { Area3DPtg aptg = (Area3DPtg)ptg; int idx = sheet.book.GetSheetIndexFromExternSheetIndex(aptg.ExternSheetIndex); if (idx == -1) { return(null); } return(sheet.Workbook.GetSheetAt(idx) as HSSFSheet); } return(null); }
private static Ptg CreateDeletedRef(Ptg ptg) { if (ptg is RefPtg) { return(new RefErrorPtg()); } if (ptg is Ref3DPtg) { Ref3DPtg rptg = (Ref3DPtg)ptg; return(new DeletedRef3DPtg(rptg.ExternSheetIndex)); } if (ptg is AreaPtg) { return(new AreaErrPtg()); } if (ptg is Area3DPtg) { Area3DPtg area3DPtg = (Area3DPtg)ptg; return(new DeletedArea3DPtg(area3DPtg.ExternSheetIndex)); } if (ptg is Ref3DPxg) { Ref3DPxg pxg = (Ref3DPxg)ptg; return(new Deleted3DPxg(pxg.ExternalWorkbookNumber, pxg.SheetName)); } if (ptg is Area3DPxg) { Area3DPxg pxg = (Area3DPxg)ptg; return(new Deleted3DPxg(pxg.ExternalWorkbookNumber, pxg.SheetName)); } throw new ArgumentException("Unexpected ref ptg class (" + ptg.GetType().Name + ")"); }
/** * Call this on any ptg reference contained in a row of cells that was copied. * If the ptg reference is relative, the references will be shifted by the distance * that the rows were copied. * In the future similar functions could be written due to column copying or * individual cell copying. Just make sure to only call adjustPtgDueToRowCopy on * formula cells that are copied (unless row shifting, where references outside * of the shifted region need to be updated to reflect the shift, a copy is self-contained). * * @param ptg the ptg to shift * @return deleted ref ptg, in-place modified ptg, or null * If Ptg would be shifted off the first or last row of a sheet, return deleted ref * If Ptg needs to be changed, modifies Ptg in-place * If Ptg doesn't need to be changed, returns <code>null</code> */ private Ptg AdjustPtgDueToRowCopy(Ptg ptg) { if (ptg is RefPtg) { RefPtg rptg = (RefPtg)ptg; return(RowCopyRefPtg(rptg)); } if (ptg is Ref3DPtg) { Ref3DPtg rptg = (Ref3DPtg)ptg; return(RowCopyRefPtg(rptg)); } if (ptg is Ref3DPxg) { Ref3DPxg rpxg = (Ref3DPxg)ptg; return(RowCopyRefPtg(rpxg)); } if (ptg is Area2DPtgBase) { return(RowCopyAreaPtg((Area2DPtgBase)ptg)); } if (ptg is Area3DPtg) { Area3DPtg aptg = (Area3DPtg)ptg; return(RowCopyAreaPtg(aptg)); } if (ptg is Area3DPxg) { Area3DPxg apxg = (Area3DPxg)ptg; return(RowCopyAreaPtg(apxg)); } return(null); }
public void TestAutoFilter() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = (HSSFSheet)wb.CreateSheet(); InternalWorkbook iwb = wb.Workbook; InternalSheet ish = sh.Sheet; Assert.IsNull(iwb.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1)); Assert.IsNull(ish.FindFirstRecordBySid(AutoFilterInfoRecord.sid)); CellRangeAddress range = CellRangeAddress.ValueOf("A1:B10"); sh.SetAutoFilter(range); NameRecord name = iwb.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1); Assert.IsNotNull(name); // The built-in name for auto-filter must consist of a single Area3d Ptg. Ptg[] ptg = name.NameDefinition; Assert.AreEqual(1, ptg.Length, "The built-in name for auto-filter must consist of a single Area3d Ptg"); Assert.IsTrue(ptg[0] is Area3DPtg, "The built-in name for auto-filter must consist of a single Area3d Ptg"); Area3DPtg aref = (Area3DPtg)ptg[0]; Assert.AreEqual(range.FirstColumn, aref.FirstColumn); Assert.AreEqual(range.FirstRow, aref.FirstRow); Assert.AreEqual(range.LastColumn, aref.LastColumn); Assert.AreEqual(range.LastRow, aref.LastRow); // verify AutoFilterInfoRecord AutoFilterInfoRecord afilter = (AutoFilterInfoRecord)ish.FindFirstRecordBySid(AutoFilterInfoRecord.sid); Assert.IsNotNull(afilter); Assert.AreEqual(2, afilter.NumEntries); //filter covers two columns HSSFPatriarch dr = (HSSFPatriarch)sh.DrawingPatriarch; Assert.IsNotNull(dr); HSSFSimpleShape comboBoxShape = (HSSFSimpleShape)dr.Children[0]; Assert.AreEqual(comboBoxShape.ShapeType, HSSFSimpleShape.OBJECT_TYPE_COMBO_BOX); Assert.IsNull(ish.FindFirstRecordBySid(ObjRecord.sid)); // ObjRecord will appear after serializetion wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = (HSSFSheet)wb.GetSheetAt(0); ish = sh.Sheet; ObjRecord objRecord = (ObjRecord)ish.FindFirstRecordBySid(ObjRecord.sid); IList subRecords = objRecord.SubRecords; Assert.AreEqual(3, subRecords.Count); Assert.IsTrue(subRecords[0] is CommonObjectDataSubRecord); Assert.IsTrue(subRecords[1] is FtCblsSubRecord); // must be present, see Bug 51481 Assert.IsTrue(subRecords[2] is LbsDataSubRecord); }
public ValueEval GetNameXEval(NameXPtg nameXPtg) { ExternalSheet externSheet = _workbook.GetExternalSheet(nameXPtg.SheetRefIndex); if (externSheet == null) { return(new NameXEval(nameXPtg)); } String workbookName = externSheet.GetWorkbookName(); ExternalName externName = _workbook.GetExternalName( nameXPtg.SheetRefIndex, nameXPtg.NameIndex ); try { WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.GetOtherWorkbookEvaluator(workbookName); IEvaluationName evaluationName = refWorkbookEvaluator.GetName(externName.Name, externName.Ix - 1); if (evaluationName != null && evaluationName.HasFormula) { if (evaluationName.NameDefinition.Length > 1) { throw new Exception("Complex name formulas not supported yet"); } Ptg ptg = evaluationName.NameDefinition[0]; if (ptg is Ref3DPtg) { Ref3DPtg ref3D = (Ref3DPtg)ptg; int sheetIndex = refWorkbookEvaluator.GetSheetIndexByExternIndex(ref3D.ExternSheetIndex); String sheetName = refWorkbookEvaluator.GetSheetName(sheetIndex); SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName); return(new LazyRefEval(ref3D.Row, ref3D.Column, sre)); } else if (ptg is Area3DPtg) { Area3DPtg area3D = (Area3DPtg)ptg; int sheetIndex = refWorkbookEvaluator.GetSheetIndexByExternIndex(area3D.ExternSheetIndex); String sheetName = refWorkbookEvaluator.GetSheetName(sheetIndex); SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName); return(new LazyAreaEval(area3D.FirstRow, area3D.FirstColumn, area3D.LastRow, area3D.LastColumn, sre)); } } return(ErrorEval.REF_INVALID); } catch (WorkbookNotFoundException) { return(ErrorEval.REF_INVALID); } }
public void TestToFormulaString() { Area3DPtg target = new Area3DPtg("A1:B1", (short)0); string sheetName = "my sheet"; HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "Sheet1"); Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "C64"); Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book)); }
private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg) { //look for the AutoFilterInfo Record NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid); AutoFilterInfoRecord info; if (record == null) { info = new AutoFilterInfoRecord(); sheet.Sheet.Records.Insert(insertPos, info); } else { info = record as AutoFilterInfoRecord; } info.NumberOfFilterColumns = (short)(ptg.LastColumn - ptg.FirstColumn + 1); }
// Fetch the workbook this refers to, and the name as defined with that private ValueEval GetExternalNameXEval(ExternalName externName, string workbookName) { try { WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.GetOtherWorkbookEvaluator(workbookName); IEvaluationName evaluationName = refWorkbookEvaluator.GetName(externName.Name, externName.Ix - 1); if (evaluationName != null && evaluationName.HasFormula) { if (evaluationName.NameDefinition.Length > 1) { throw new Exception("Complex name formulas not supported yet"); } // Need to Evaluate the reference in the context of the other book OperationEvaluationContext refWorkbookContext = new OperationEvaluationContext( refWorkbookEvaluator, refWorkbookEvaluator.Workbook, -1, -1, -1, _tracker); Ptg ptg = evaluationName.NameDefinition[0]; if (ptg is Ref3DPtg) { Ref3DPtg ref3D = (Ref3DPtg)ptg; return(refWorkbookContext.GetRef3DEval(ref3D)); } else if (ptg is Ref3DPxg) { Ref3DPxg ref3D = (Ref3DPxg)ptg; return(refWorkbookContext.GetRef3DEval(ref3D)); } else if (ptg is Area3DPtg) { Area3DPtg area3D = (Area3DPtg)ptg; return(refWorkbookContext.GetArea3DEval(area3D)); } else if (ptg is Area3DPxg) { Area3DPxg area3D = (Area3DPxg)ptg; return(refWorkbookContext.GetArea3DEval(area3D)); } } return(ErrorEval.REF_INVALID); } catch (WorkbookNotFoundException) { return(ErrorEval.REF_INVALID); } }
/** * @return <c>true</c> if this Ptg needed to be changed */ private Ptg AdjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx) { if (ptg is RefPtg) { if (currentExternSheetIx != _externSheetIndex) { // local refs on other sheets are unaffected return(null); } RefPtg rptg = (RefPtg)ptg; return(RowMoveRefPtg(rptg)); } if (ptg is Ref3DPtg) { Ref3DPtg rptg = (Ref3DPtg)ptg; if (_externSheetIndex != rptg.ExternSheetIndex) { // only move 3D refs that refer to the sheet with cells being moved // (currentExternSheetIx is irrelevant) return(null); } return(RowMoveRefPtg(rptg)); } if (ptg is Area2DPtgBase) { if (currentExternSheetIx != _externSheetIndex) { // local refs on other sheets are unaffected return(ptg); } return(RowMoveAreaPtg((Area2DPtgBase)ptg)); } if (ptg is Area3DPtg) { Area3DPtg aptg = (Area3DPtg)ptg; if (_externSheetIndex != aptg.ExternSheetIndex) { // only move 3D refs that refer to the sheet with cells being moved // (currentExternSheetIx is irrelevant) return(null); } return(RowMoveAreaPtg(aptg)); } return(null); }
public void TestLoad() { BRAIRecord record = new BRAIRecord(TestcaseRecordInputStream.Create((short)0x1051, data)); Assert.AreEqual(BRAIRecord.LINK_TYPE_VALUES, record.LinkType); Assert.AreEqual(BRAIRecord.REFERENCE_TYPE_WORKSHEET, record.ReferenceType); Assert.AreEqual(0, record.Options); Assert.AreEqual(false, record.IsCustomNumberFormat); Assert.AreEqual(0, record.IndexNumberFmtRecord); Area3DPtg ptgExpected = new Area3DPtg(0, 7936, 0, 0, false, false, false, false, 0); Object ptgActual = record.FormulaOfLink[0]; Assert.AreEqual(ptgExpected.ToString(), ptgActual.ToString()); Assert.AreEqual(data.Length + 4, record.RecordSize); }
public void TestStore() { BRAIRecord record = new BRAIRecord(); record.LinkType = (BRAIRecord.LINK_TYPE_VALUES); record.ReferenceType = (BRAIRecord.REFERENCE_TYPE_WORKSHEET); record.Options = ((short)0); record.IsCustomNumberFormat = (false); record.IndexNumberFmtRecord = ((short)0); Area3DPtg ptg = new Area3DPtg(0, 7936, 0, 0, false, false, false, false, 0); record.FormulaOfLink = (new Ptg[] { ptg, }); byte[] recordBytes = record.Serialize(); Assert.AreEqual(recordBytes.Length - 4, data.Length); for (int i = 0; i < data.Length; i++) { Assert.AreEqual(data[i], recordBytes[i + 4], "At offset " + i); } }
/** * returns an appropriate Eval impl instance for the Ptg. The Ptg must be * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be * passed here! */ private ValueEval GetEvalForPtg(Ptg ptg, OperationEvaluationContext ec) { // consider converting all these (ptg is XxxPtg) expressions To (ptg.GetType() == XxxPtg.class) if (ptg is NamePtg) { // named ranges, macro functions NamePtg namePtg = (NamePtg)ptg; IEvaluationName nameRecord = _workbook.GetName(namePtg); if (nameRecord.IsFunctionName) { return(new NameEval(nameRecord.NameText)); } if (nameRecord.HasFormula) { return(EvaluateNameFormula(nameRecord.NameDefinition, ec)); } throw new Exception("Don't now how To evalate name '" + nameRecord.NameText + "'"); } if (ptg is NameXPtg) { return(ec.GetNameXEval(((NameXPtg)ptg))); } if (ptg is IntPtg) { return(new NumberEval(((IntPtg)ptg).Value)); } if (ptg is NumberPtg) { return(new NumberEval(((NumberPtg)ptg).Value)); } if (ptg is StringPtg) { return(new StringEval(((StringPtg)ptg).Value)); } if (ptg is BoolPtg) { return(BoolEval.ValueOf(((BoolPtg)ptg).Value)); } if (ptg is ErrPtg) { return(ErrorEval.ValueOf(((ErrPtg)ptg).ErrorCode)); } if (ptg is MissingArgPtg) { return(MissingArgEval.instance); } if (ptg is AreaErrPtg || ptg is RefErrorPtg || ptg is DeletedArea3DPtg || ptg is DeletedRef3DPtg) { return(ErrorEval.REF_INVALID); } if (ptg is Ref3DPtg) { Ref3DPtg rptg = (Ref3DPtg)ptg; return(ec.GetRef3DEval(rptg.Row, rptg.Column, rptg.ExternSheetIndex)); } if (ptg is Area3DPtg) { Area3DPtg aptg = (Area3DPtg)ptg; return(ec.GetArea3DEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn, aptg.ExternSheetIndex)); } if (ptg is RefPtg) { RefPtg rptg = (RefPtg)ptg; return(ec.GetRefEval(rptg.Row, rptg.Column)); } if (ptg is AreaPtg) { AreaPtg aptg = (AreaPtg)ptg; return(ec.GetAreaEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn)); } if (ptg is UnknownPtg) { // POI uses UnknownPtg when the encoded Ptg array seems To be corrupted. // This seems To occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790) // In any case, formulas are re-parsed before execution, so UnknownPtg should not Get here throw new RuntimeException("UnknownPtg not allowed"); } if (ptg is ExpPtg) { // ExpPtg is used for array formulas and shared formulas. // it is currently unsupported, and may not even get implemented here throw new RuntimeException("ExpPtg currently not supported"); } throw new RuntimeException("Unexpected ptg class (" + ptg.GetType().Name + ")"); }