public ExternalSheet GetExternalSheet(String firstSheetName, String lastSheetName, int externalWorkbookNumber)
{
String workbookName;
if (externalWorkbookNumber > 0)
{
// External reference - reference is 1 based, link table is 0 based
int linkNumber = externalWorkbookNumber - 1;
ExternalLinksTable linkTable = _uBook.ExternalLinksTable[(linkNumber)];
workbookName = linkTable.LinkedFileName;
}
else
{
// Internal reference
workbookName = null;
}
if (lastSheetName == null || firstSheetName.Equals(lastSheetName))
{
return(new ExternalSheet(workbookName, firstSheetName));
}
else
{
return(new ExternalSheetRange(workbookName, firstSheetName, lastSheetName));
}
}
public ExternalName GetExternalName(String nameName, String sheetName, int externalWorkbookNumber)
{
if (externalWorkbookNumber > 0)
{
// External reference - reference is 1 based, link table is 0 based
int linkNumber = externalWorkbookNumber - 1;
ExternalLinksTable linkTable = _uBook.ExternalLinksTable[(linkNumber)];
foreach (IName name in linkTable.DefinedNames)
{
if (name.NameName.Equals(nameName))
{
// HSSF returns one sheet higher than normal, and various bits
// of the code assume that. So, make us match that behaviour!
int nameSheetIndex = name.SheetIndex + 1;
// TODO Return a more specialised form of this, see bug #56752
// Should include the cached values, for in case that book isn't available
// Should support XSSF stuff Lookups
return(new ExternalName(nameName, -1, nameSheetIndex));
}
}
throw new ArgumentException("Name '" + nameName + "' not found in " +
"reference to " + linkTable.LinkedFileName);
}
else
{
// Internal reference
int nameIdx = _uBook.GetNameIndex(nameName);
return(new ExternalName(nameName, nameIdx, 0)); // TODO Is this right?
}
}
public void BasicRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ref-56737.xlsx");
Assert.IsNotNull(wb.ExternalLinksTable);
IName name = null;
Assert.AreEqual(1, wb.ExternalLinksTable.Count);
ExternalLinksTable links = wb.ExternalLinksTable[0];
Assert.AreEqual(3, links.SheetNames.Count);
Assert.AreEqual(2, links.DefinedNames.Count);
Assert.AreEqual("Uses", links.SheetNames[(0)]);
Assert.AreEqual("Defines", links.SheetNames[(1)]);
Assert.AreEqual("56737", links.SheetNames[(2)]);
name = links.DefinedNames[(0)];
Assert.AreEqual("NR_Global_B2", name.NameName);
Assert.AreEqual(-1, name.SheetIndex);
Assert.AreEqual(null, name.SheetName);
Assert.AreEqual("'Defines'!$B$2", name.RefersToFormula);
name = links.DefinedNames[(1)];
Assert.AreEqual("NR_To_A1", name.NameName);
Assert.AreEqual(1, name.SheetIndex);
Assert.AreEqual("Defines", name.SheetName);
Assert.AreEqual("'Defines'!$A$1", name.RefersToFormula);
Assert.AreEqual("56737.xlsx", links.LinkedFileName);
}
public void readWithReferencesToTwoExternalBooks()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ref2-56737.xlsx");
Assert.IsNotNull(wb.ExternalLinksTable);
IName name = null;
Assert.AreEqual(2, wb.ExternalLinksTable.Count);
// Check the first one, links to 56737.xlsx
ExternalLinksTable links = wb.ExternalLinksTable[0];
Assert.AreEqual("56737.xlsx", links.LinkedFileName);
Assert.AreEqual(3, links.SheetNames.Count);
Assert.AreEqual(2, links.DefinedNames.Count);
Assert.AreEqual("Uses", links.SheetNames[0]);
Assert.AreEqual("Defines", links.SheetNames[1]);
Assert.AreEqual("56737", links.SheetNames[2]);
name = links.DefinedNames[0];
Assert.AreEqual("NR_Global_B2", name.NameName);
Assert.AreEqual(-1, name.SheetIndex);
Assert.AreEqual(null, name.SheetName);
Assert.AreEqual("'Defines'!$B$2", name.RefersToFormula);
name = links.DefinedNames[1];
Assert.AreEqual("NR_To_A1", name.NameName);
Assert.AreEqual(1, name.SheetIndex);
Assert.AreEqual("Defines", name.SheetName);
Assert.AreEqual("'Defines'!$A$1", name.RefersToFormula);
// Check the second one, links to 56737.xls, slightly differently
links = wb.ExternalLinksTable[1];
Assert.AreEqual("56737.xls", links.LinkedFileName);
Assert.AreEqual(2, links.SheetNames.Count);
Assert.AreEqual(2, links.DefinedNames.Count);
Assert.AreEqual("Uses", links.SheetNames[0]);
Assert.AreEqual("Defines", links.SheetNames[1]);
name = links.DefinedNames[0];
Assert.AreEqual("NR_Global_B2", name.NameName);
Assert.AreEqual(-1, name.SheetIndex);
Assert.AreEqual(null, name.SheetName);
Assert.AreEqual("'Defines'!$B$2", name.RefersToFormula);
name = links.DefinedNames[1];
Assert.AreEqual("NR_To_A1", name.NameName);
Assert.AreEqual(1, name.SheetIndex);
Assert.AreEqual("Defines", name.SheetName);
Assert.AreEqual("'Defines'!$A$1", name.RefersToFormula);
}
public void BasicReadWriteRead()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("ref-56737.xlsx");
IName name = wb.ExternalLinksTable[0].DefinedNames[(1)];
name.NameName = (/*setter*/ "Testing");
name.RefersToFormula = (/*setter*/ "$A$1");
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
Assert.AreEqual(1, wb.ExternalLinksTable.Count);
ExternalLinksTable links = wb.ExternalLinksTable[(0)];
name = links.DefinedNames[(0)];
Assert.AreEqual("NR_Global_B2", name.NameName);
Assert.AreEqual(-1, name.SheetIndex);
Assert.AreEqual(null, name.SheetName);
Assert.AreEqual("'Defines'!$B$2", name.RefersToFormula);
name = links.DefinedNames[(1)];
Assert.AreEqual("Testing", name.NameName);
Assert.AreEqual(1, name.SheetIndex);
Assert.AreEqual("Defines", name.SheetName);
Assert.AreEqual("$A$1", name.RefersToFormula);
}
