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];
}
/**
* 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];
}
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];
}
/**
* 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 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));
}
