public void TestMissingExternSheetRecord_bug47001b() { Record[] recs = { SupBookRecord.CreateAddInFunctions(), new SSTRecord(), }; List <Record> recList = new List <Record>(recs); WorkbookRecordList wrl = new WorkbookRecordList(); LinkTable lt; try { lt = new LinkTable(recList, 0, wrl, new Dictionary <String, NameCommentRecord>()); } catch (Exception e) { if (e.Message.Equals("Expected an EXTERNSHEET record but got (Npoi.Core.HSSF.record.SSTRecord)")) { throw new AssertionException("Identified bug 47001b"); } throw e; } Assert.IsNotNull(lt); }
public int GetExternalSheetIndex(String workbookName, String sheetName) { SupBookRecord ebrTarget = null; int externalBookIndex = -1; for (int i = 0; i < _externalBookBlocks.Length; i++) { SupBookRecord ebr = _externalBookBlocks[i].GetExternalBookRecord(); if (!ebr.IsExternalReferences) { continue; } if (workbookName.Equals(ebr.URL)) { // not sure if 'equals()' works when url has a directory ebrTarget = ebr; externalBookIndex = i; break; } } if (ebrTarget == null) { throw new NullReferenceException("No external workbook with name '" + workbookName + "'"); } int sheetIndex = GetSheetIndex(ebrTarget.SheetNames, sheetName); int result = _externSheetRecord.GetRefIxForSheet(externalBookIndex, sheetIndex); if (result < 0) { throw new InvalidOperationException("ExternSheetRecord does not contain combination (" + externalBookIndex + ", " + sheetIndex + ")"); } return(result); }
/// <summary> /// Creates a stub Workbook from the supplied records, /// suitable for use with the {@link HSSFFormulaParser} /// </summary> /// <param name="externs">The ExternSheetRecords in your file</param> /// <param name="bounds">The BoundSheetRecords in your file</param> /// <param name="sst">TThe SSTRecord in your file.</param> /// <returns>A stub Workbook suitable for use with HSSFFormulaParser</returns> public static InternalWorkbook CreateStubWorkbook(ExternSheetRecord[] externs, BoundSheetRecord[] bounds, SSTRecord sst) { List <Record> wbRecords = new List <Record>(); // Core Workbook records go first if (bounds != null) { for (int i = 0; i < bounds.Length; i++) { wbRecords.Add(bounds[i]); } } if (sst != null) { wbRecords.Add(sst); } // Now we can have the ExternSheetRecords, // preceded by a SupBookRecord if (externs != null) { wbRecords.Add(SupBookRecord.CreateInternalReferences( (short)externs.Length)); for (int i = 0; i < externs.Length; i++) { wbRecords.Add(externs[i]); } } // Finally we need an EoF record wbRecords.Add(EOFRecord.instance); return(InternalWorkbook.CreateWorkbook(wbRecords)); }
public int CheckExternSheet(int firstSheetIndex, int lastSheetIndex) { int thisWbIndex = -1; // this is probably always zero for (int i = 0; i < _externalBookBlocks.Length; i++) { SupBookRecord ebr = _externalBookBlocks[i].GetExternalBookRecord(); if (ebr.IsInternalReferences) { thisWbIndex = i; break; } } if (thisWbIndex < 0) { throw new InvalidOperationException("Could not find 'internal references' EXTERNALBOOK"); } //Trying to find reference to this sheet int j = _externSheetRecord.GetRefIxForSheet(thisWbIndex, firstSheetIndex, lastSheetIndex); if (j >= 0) { return(j); } //We haven't found reference to this sheet return(_externSheetRecord.AddRef(thisWbIndex, firstSheetIndex, lastSheetIndex)); }
public void TestLoadAIF() { SupBookRecord record = new SupBookRecord(TestcaseRecordInputStream.Create(0x01AE, dataAIF)); Assert.IsTrue(record.IsAddInFunctions); //expected flag Assert.AreEqual(0x1, record.NumberOfSheets); //expected # of sheets Assert.AreEqual(8, record.RecordSize); //sid+size+data }
public void TestStoreER() { string url = "testURL"; string[] sheetNames = { "Sheet1", "Sheet2", }; SupBookRecord record = SupBookRecord.CreateExternalReferences(url, sheetNames); TestcaseRecordInputStream.ConfirmRecordEncoding(0x01AE, dataER, record.Serialize()); }
public void TestLoadIR() { SupBookRecord record = new SupBookRecord(TestcaseRecordInputStream.Create(0x01AE, dataIR)); Assert.IsTrue(record.IsInternalReferences); //expected flag Assert.AreEqual(0x4, record.NumberOfSheets); //expected # of sheets Assert.AreEqual(8, record.RecordSize); //sid+size+data }
/** * Changes an external referenced file to another file. * A formular in Excel which refers a cell in another file is saved in two parts: * The referenced file is stored in an reference table. the row/cell information is saved separate. * This method invokation will only change the reference in the lookup-table itself. * @param oldUrl The old URL to search for and which is to be replaced * @param newUrl The URL replacement * @return true if the oldUrl was found and replaced with newUrl. Otherwise false */ public bool ChangeExternalReference(String oldUrl, String newUrl) { foreach (ExternalBookBlock ex in _externalBookBlocks) { SupBookRecord externalRecord = ex.GetExternalBookRecord(); if (externalRecord.IsExternalReferences && externalRecord.URL.Equals(oldUrl)) { externalRecord.URL = (newUrl); return(true); } } return(false); }
public void TestLoadER() { SupBookRecord record = new SupBookRecord(TestcaseRecordInputStream.Create(0x01AE, dataER)); Assert.IsTrue(record.IsExternalReferences); //expected flag Assert.AreEqual(0x2, record.NumberOfSheets); //expected # of sheets Assert.AreEqual(34, record.RecordSize); //sid+size+data Assert.AreEqual("testURL", record.URL); string[] sheetNames = record.SheetNames; Assert.AreEqual(2, sheetNames.Length); Assert.AreEqual("Sheet1", sheetNames[0]); Assert.AreEqual("Sheet2", sheetNames[1]); }
private int GetExternalWorkbookIndex(String workbookName) { for (int i = 0; i < _externalBookBlocks.Length; i++) { SupBookRecord ebr = _externalBookBlocks[i].GetExternalBookRecord(); if (!ebr.IsExternalReferences) { continue; } if (workbookName.Equals(ebr.URL)) { // not sure if 'equals()' works when url has a directory return(i); } } return(-1); }
public String[] GetExternalBookAndSheetName(int extRefIndex) { int ebIx = _externSheetRecord.GetExtbookIndexFromRefIndex(extRefIndex); SupBookRecord ebr = _externalBookBlocks[ebIx].GetExternalBookRecord(); if (!ebr.IsExternalReferences) { return(null); } int shIx = _externSheetRecord.GetFirstSheetIndexFromRefIndex(extRefIndex); String usSheetName = (String)ebr.SheetNames.GetValue(shIx); return(new String[] { ebr.URL, usSheetName, }); }
public ExternalBookBlock(RecordStream rs) { _externalBookRecord = (SupBookRecord)rs.GetNext(); List <object> temp = new List <object>(); while (rs.PeekNextClass() == typeof(ExternalNameRecord)) { temp.Add(rs.GetNext()); } _externalNameRecords = (ExternalNameRecord[])temp.ToArray(); temp.Clear(); while (rs.PeekNextClass() == typeof(CRNCountRecord)) { temp.Add(new CRNBlock(rs)); } _crnBlocks = (CRNBlock[])temp.ToArray(); }
public void TestExternalReferenceUrl() { string[] sheetNames = new string[] { "SampleSheet" }; char startMarker = (char)1; SupBookRecord record; record = new SupBookRecord(startMarker + "test.xls", sheetNames); Assert.AreEqual("test.xls", record.URL); //UNC path notation record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_VOLUME + "@servername" + SupBookRecord.CH_DOWN_DIR + "test.xls", sheetNames); Assert.AreEqual("\\\\servername" + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Absolute path notation - different device record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_VOLUME + "D" + SupBookRecord.CH_DOWN_DIR + "test.xls", sheetNames); Assert.AreEqual("D:" + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Absolute path notation - same device record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_SAME_VOLUME + "folder" + SupBookRecord.CH_DOWN_DIR + "test.xls", sheetNames); Assert.AreEqual(SupBookRecord.PATH_SEPERATOR + "folder" + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Relative path notation - down record = new SupBookRecord(startMarker + "folder" + SupBookRecord.CH_DOWN_DIR + "test.xls", sheetNames); Assert.AreEqual("folder" + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Relative path notation - up record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_UP_DIR + "test.xls", sheetNames); Assert.AreEqual(".." + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Relative path notation - for EXCEL.exe - fallback record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_STARTUP_DIR + "test.xls", sheetNames); Assert.AreEqual("." + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Relative path notation - for EXCEL lib folder - fallback record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_LIB_DIR + "test.xls", sheetNames); Assert.AreEqual("." + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); //Relative path notation - for alternative EXCEL.exe - fallback record = new SupBookRecord(startMarker + "" + SupBookRecord.CH_ALT_STARTUP_DIR + "test.xls", sheetNames); Assert.AreEqual("." + SupBookRecord.PATH_SEPERATOR + "test.xls", record.URL); }
public String[] GetExternalBookAndSheetName(int extRefIndex) { int ebIx = _externSheetRecord.GetExtbookIndexFromRefIndex(extRefIndex); SupBookRecord ebr = _externalBookBlocks[ebIx].GetExternalBookRecord(); if (!ebr.IsExternalReferences) { return(null); } // Sheet name only applies if not a global reference int shIx1 = _externSheetRecord.GetFirstSheetIndexFromRefIndex(extRefIndex); int shIx2 = _externSheetRecord.GetLastSheetIndexFromRefIndex(extRefIndex); String firstSheetName = null; String lastSheetName = null; if (shIx1 >= 0) { firstSheetName = ebr.SheetNames[shIx1]; } if (shIx2 >= 0) { lastSheetName = ebr.SheetNames[shIx2]; } if (shIx1 == shIx2) { return(new String[] { ebr.URL, firstSheetName }); } else { return(new String[] { ebr.URL, firstSheetName, lastSheetName }); } }
public int GetExternalSheetIndex(String workbookName, String firstSheetName, String lastSheetName) { int externalBookIndex = GetExternalWorkbookIndex(workbookName); if (externalBookIndex == -1) { throw new RuntimeException("No external workbook with name '" + workbookName + "'"); } SupBookRecord ebrTarget = _externalBookBlocks[externalBookIndex].GetExternalBookRecord(); int firstSheetIndex = GetSheetIndex(ebrTarget.SheetNames, firstSheetName); int lastSheetIndex = GetSheetIndex(ebrTarget.SheetNames, lastSheetName); // Find or add the external sheet record definition for this int result = _externSheetRecord.GetRefIxForSheet(externalBookIndex, firstSheetIndex, lastSheetIndex); if (result < 0) { result = _externSheetRecord.AddRef(externalBookIndex, firstSheetIndex, lastSheetIndex); } return(result); }
public LinkTable(int numberOfSheets, WorkbookRecordList workbookRecordList) { _workbookRecordList = workbookRecordList; _definedNames = new List <NameRecord>(); _externalBookBlocks = new ExternalBookBlock[] { new ExternalBookBlock(numberOfSheets), }; _externSheetRecord = new ExternSheetRecord(); _recordCount = 2; // tell _workbookRecordList about the 2 new records SupBookRecord supbook = _externalBookBlocks[0].GetExternalBookRecord(); int idx = FindFirstRecordLocBySid(CountryRecord.sid); if (idx < 0) { throw new Exception("CountryRecord not found"); } _workbookRecordList.Add(idx + 1, _externSheetRecord); _workbookRecordList.Add(idx + 1, supbook); }
/** * Create a new block for registering add-in functions * * @see org.apache.poi.hssf.model.LinkTable#addNameXPtg(String) */ public ExternalBookBlock() { _externalBookRecord = SupBookRecord.CreateAddInFunctions(); _externalNameRecords = new ExternalNameRecord[0]; _crnBlocks = new CRNBlock[0]; }
/** * Create a new block for internal references. It is called when constructing a new LinkTable. * * @see org.apache.poi.hssf.model.LinkTable#LinkTable(int, WorkbookRecordList) */ public ExternalBookBlock(int numberOfSheets) { _externalBookRecord = SupBookRecord.CreateInternalReferences((short)numberOfSheets); _externalNameRecords = new ExternalNameRecord[0]; _crnBlocks = new CRNBlock[0]; }
public void TestStoreIR() { SupBookRecord record = SupBookRecord.CreateInternalReferences((short)4); TestcaseRecordInputStream.ConfirmRecordEncoding(0x01AE, dataIR, record.Serialize()); }
public void TestAddNameX() { WorkbookRecordList wrl = new WorkbookRecordList(); wrl.Add(0, new BOFRecord()); wrl.Add(1, new CountryRecord()); wrl.Add(2, EOFRecord.instance); int numberOfSheets = 3; LinkTable tbl = new LinkTable(numberOfSheets, wrl); // creation of a new LinkTable insert two new records: SupBookRecord followed by ExternSheetRecord // assure they are in place: // [BOFRecord] // [CountryRecord] // [SUPBOOK Internal References nSheets= 3] // [EXTERNSHEET] // [EOFRecord] Assert.AreEqual(5, wrl.Records.Count); Assert.IsTrue(wrl[(2)] is SupBookRecord); SupBookRecord sup1 = (SupBookRecord)wrl[(2)]; Assert.AreEqual(numberOfSheets, sup1.NumberOfSheets); Assert.IsTrue(wrl[(3)] is ExternSheetRecord); ExternSheetRecord extSheet = (ExternSheetRecord)wrl[(3)]; Assert.AreEqual(0, extSheet.NumOfRefs); Assert.IsNull(tbl.GetNameXPtg("ISODD", -1)); Assert.AreEqual(5, wrl.Records.Count); //still have five records NameXPtg namex1 = tbl.AddNameXPtg("ISODD"); // Adds two new rercords Assert.AreEqual(0, namex1.SheetRefIndex); Assert.AreEqual(0, namex1.NameIndex); Assert.AreEqual(namex1.ToString(), tbl.GetNameXPtg("ISODD", -1).ToString()); // Can only find on the right sheet ref, if restricting Assert.AreEqual(namex1.ToString(), tbl.GetNameXPtg("ISODD", 0).ToString()); Assert.IsNull(tbl.GetNameXPtg("ISODD", 1)); Assert.IsNull(tbl.GetNameXPtg("ISODD", 2)); // assure they are in place: // [BOFRecord] // [CountryRecord] // [SUPBOOK Internal References nSheets= 3] // [SUPBOOK Add-In Functions nSheets= 1] // [EXTERNALNAME .name = ISODD] // [EXTERNSHEET] // [EOFRecord] Assert.AreEqual(7, wrl.Records.Count); Assert.IsTrue(wrl[(3)] is SupBookRecord); SupBookRecord sup2 = (SupBookRecord)wrl[(3)]; Assert.IsTrue(sup2.IsAddInFunctions); Assert.IsTrue(wrl[(4)] is ExternalNameRecord); ExternalNameRecord ext1 = (ExternalNameRecord)wrl[(4)]; Assert.AreEqual("ISODD", ext1.Text); Assert.IsTrue(wrl[(5)] is ExternSheetRecord); Assert.AreEqual(1, extSheet.NumOfRefs); //check that Assert.AreEqual(0, tbl.ResolveNameXIx(namex1.SheetRefIndex, namex1.NameIndex)); Assert.AreEqual("ISODD", tbl.ResolveNameXText(namex1.SheetRefIndex, namex1.NameIndex, null)); Assert.IsNull(tbl.GetNameXPtg("ISEVEN", -1)); NameXPtg namex2 = tbl.AddNameXPtg("ISEVEN"); // Adds two new rercords Assert.AreEqual(0, namex2.SheetRefIndex); Assert.AreEqual(1, namex2.NameIndex); // name index increased by one Assert.AreEqual(namex2.ToString(), tbl.GetNameXPtg("ISEVEN", -1).ToString()); Assert.AreEqual(8, wrl.Records.Count); // assure they are in place: // [BOFRecord] // [CountryRecord] // [SUPBOOK Internal References nSheets= 3] // [SUPBOOK Add-In Functions nSheets= 1] // [EXTERNALNAME .name = ISODD] // [EXTERNALNAME .name = ISEVEN] // [EXTERNSHEET] // [EOFRecord] Assert.IsTrue(wrl[(3)] is SupBookRecord); Assert.IsTrue(wrl[(4)] is ExternalNameRecord); Assert.IsTrue(wrl[(5)] is ExternalNameRecord); Assert.AreEqual("ISODD", ((ExternalNameRecord)wrl[(4)]).Text); Assert.AreEqual("ISEVEN", ((ExternalNameRecord)wrl[(5)]).Text); Assert.IsTrue(wrl[(6)] is ExternSheetRecord); Assert.IsTrue(wrl[(7)] is EOFRecord); Assert.AreEqual(0, tbl.ResolveNameXIx(namex2.SheetRefIndex, namex2.NameIndex)); Assert.AreEqual("ISEVEN", tbl.ResolveNameXText(namex2.SheetRefIndex, namex2.NameIndex, null)); }
/** * Create a new block for external references. */ public ExternalBookBlock(String url, String[] sheetNames) { _externalBookRecord = SupBookRecord.CreateExternalReferences(url, sheetNames); _crnBlocks = new CRNBlock[0]; }
public ExternalBookBlock(RecordStream rs) { _externalBookRecord = (SupBookRecord)rs.GetNext(); ArrayList temp = new ArrayList(); while (rs.PeekNextClass() == typeof(ExternalNameRecord)) { temp.Add(rs.GetNext()); } _externalNameRecords = (ExternalNameRecord[])temp.ToArray(typeof(ExternalNameRecord)); temp.Clear(); while (rs.PeekNextClass() == typeof(CRNCountRecord)) { temp.Add(new CRNBlock(rs)); } _crnBlocks = (CRNBlock[])temp.ToArray(typeof(CRNBlock)); }
/** * Register an external name in this workbook * * @param name the name to register * @return a NameXPtg describing this name */ public NameXPtg AddNameXPtg(String name) { int extBlockIndex = -1; ExternalBookBlock extBlock = null; // find ExternalBlock for Add-In functions and remember its index for (int i = 0; i < _externalBookBlocks.Length; i++) { SupBookRecord ebr = _externalBookBlocks[i].GetExternalBookRecord(); if (ebr.IsAddInFunctions) { extBlock = _externalBookBlocks[i]; extBlockIndex = i; break; } } // An ExternalBlock for Add-In functions was not found. Create a new one. if (extBlock == null) { extBlock = new ExternalBookBlock(); extBlockIndex = ExtendExternalBookBlocks(extBlock); // add the created SupBookRecord before ExternSheetRecord int idx = FindFirstRecordLocBySid(ExternSheetRecord.sid); _workbookRecordList.Add(idx, extBlock.GetExternalBookRecord()); // register the SupBookRecord in the ExternSheetRecord // -2 means that the scope of this name is Workbook and the reference applies to the entire workbook. _externSheetRecord.AddRef(_externalBookBlocks.Length - 1, -2, -2); } // create a ExternalNameRecord that will describe this name ExternalNameRecord extNameRecord = new ExternalNameRecord(); extNameRecord.Text = (name); // The docs don't explain why Excel set the formula to #REF! extNameRecord.SetParsedExpression(new Ptg[] { ErrPtg.REF_INVALID }); int nameIndex = extBlock.AddExternalName(extNameRecord); int supLinkIndex = 0; // find the posistion of the Add-In SupBookRecord in the workbook stream, // the created ExternalNameRecord will be appended to it for (IEnumerator iterator = _workbookRecordList.GetEnumerator(); iterator.MoveNext(); supLinkIndex++) { Record record = (Record)iterator.Current; if (record is SupBookRecord) { if (((SupBookRecord)record).IsAddInFunctions) { break; } } } int numberOfNames = extBlock.NumberOfNames; // a new name is inserted in the end of the SupBookRecord, after the last name _workbookRecordList.Add(supLinkIndex + numberOfNames, extNameRecord); int fakeSheetIdx = -2; /* the scope is workbook*/ int ix = _externSheetRecord.GetRefIxForSheet(extBlockIndex, fakeSheetIdx, fakeSheetIdx); return(new NameXPtg(ix, nameIndex)); }