public void TestDetectAsPOIFS()
{
Stream in1;
// ooxml file is
in1 = new PushbackStream(
HSSFTestDataSamples.OpenSampleFileStream("SampleSS.xlsx")
);
Assert.IsTrue(POIXMLDocument.HasOOXMLHeader(in1));
in1.Dispose();
// xls file isn't
in1 = new PushbackStream(
HSSFTestDataSamples.OpenSampleFileStream("SampleSS.xls")
);
Assert.IsFalse(POIXMLDocument.HasOOXMLHeader(in1));
in1.Dispose();
// text file isn't
in1 = new PushbackStream(
HSSFTestDataSamples.OpenSampleFileStream("SampleSS.txt")
);
Assert.IsFalse(POIXMLDocument.HasOOXMLHeader(in1));
in1.Dispose();
}
public void TestSpuriousSharedFormulaFlag()
{
long actualCRC = GetFileCRC(HSSFTestDataSamples.OpenSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
long expectedCRC = 2277445406L;
if (actualCRC != expectedCRC)
{
Console.Error.WriteLine("Expected crc " + expectedCRC + " but got " + actualCRC);
throw failUnexpectedTestFileChange();
}
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0); // Sheet1
String cellFormula;
cellFormula = GetFormulaFromFirstCell(s, 0); // row "1"
// the problem is1 not observable in the first row of the shared formula
if (!cellFormula.Equals("\"first formula\""))
{
throw new Exception("Something else wrong with this Test case");
}
// but the problem is1 observable in rows 2,3,4
cellFormula = GetFormulaFromFirstCell(s, 1); // row "2"
if (cellFormula.Equals("\"second formula\""))
{
throw new AssertionException("found bug 44449 (Wrong SharedFormulaRecord was used).");
}
if (!cellFormula.Equals("\"first formula\""))
{
throw new Exception("Something else wrong with this Test case");
}
}
public void TestOpenBOOK()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(xlsB);
POIFSFileSystem fs = new POIFSFileSystem(is1);
// Ensure that we have a BOOK entry
fs.Root.GetEntry("BOOK");
Assert.IsTrue(true);
// But not a Workbook one
try
{
fs.Root.GetEntry("Workbook");
Assert.Fail();
}
catch (FileNotFoundException e) { }
// And not a Summary one
try
{
fs.Root.GetEntry("\005SummaryInformation");
Assert.Fail();
}
catch (FileNotFoundException e) { }
// Try to open the workbook
HSSFWorkbook wb = new HSSFWorkbook(fs);
}
public void TestWritePreserve()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(xlsA);
POIFSFileSystem fs = new POIFSFileSystem(is1);
// Open the workbook, not preserving nodes
HSSFWorkbook wb = new HSSFWorkbook(fs, true);
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
// Check now
MemoryStream in1 = new MemoryStream(out1.ToArray());
POIFSFileSystem fs2 = new POIFSFileSystem(in1);
// Check that we have the new entries
fs2.Root.GetEntry("Workbook");
try
{
fs2.Root.GetEntry("WORKBOOK");
Assert.Fail();
}
catch (FileNotFoundException)
{
}
// As we preserved, should also have a few other streams
fs2.Root.GetEntry("\x0005SummaryInformation");
// And it can be Opened
HSSFWorkbook wb2 = new HSSFWorkbook(fs2);
}
public void TestCreateGeneric()
{
IWorkbook wb;
// InputStream -> either
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
try
{
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(txt)
);
Assert.Fail();
}
catch (ArgumentException)
{
// Good
}
}
public void TestRecord()
{
POIFSFileSystem fs = new POIFSFileSystem(
HSSFTestDataSamples.OpenSampleFileStream("WithFormattedGraphTitle.xls"));
// Check we can Open the file via usermodel
HSSFWorkbook hssf = new HSSFWorkbook(fs);
// Now process it through eventusermodel, and
// look out for the title records
ChartTitleFormatRecordGrabber grabber = new ChartTitleFormatRecordGrabber();
Stream din = fs.CreatePOIFSDocumentReader("Workbook");
HSSFRequest req = new HSSFRequest();
req.AddListenerForAllRecords(grabber);
HSSFEventFactory factory = new HSSFEventFactory();
factory.ProcessEvents(req, din);
din.Close();
// Should've found one
Assert.AreEqual(1, grabber.chartTitleFormatRecords.Count);
// And it should be of something interesting
ChartTitleFormatRecord r =
(ChartTitleFormatRecord)grabber.chartTitleFormatRecords[0];
Assert.AreEqual(3, r.GetFormatCount());
}
public void AssertDataValidation(IWorkbook wb)
{
MemoryStream baos = new MemoryStream(22000);
try
{
wb.Write(baos);
baos.Close();
}
catch (IOException e)
{
throw new RuntimeException(e);
}
byte[] generatedContent = baos.ToArray();
#if !HIDE_UNREACHABLE_CODE
bool isSame;
if (false)
{
// TODO - add proof spreadsheet and compare
Stream proofStream = HSSFTestDataSamples.OpenSampleFileStream("TestDataValidation.xls");
isSame = CompareStreams(proofStream, generatedContent);
}
isSame = true;
if (isSame)
{
return;
}
//File tempDir = new File(System.GetProperty("java.io.tmpdir"));
string tempDir = Path.GetTempFileName();
//File generatedFile = new File(tempDir, "GeneratedTestDataValidation.xls");
try
{
FileStream fileOut = new FileStream(tempDir, FileMode.Create);
fileOut.Write(generatedContent, 0, generatedContent.Length);
fileOut.Close();
}
catch (IOException e)
{
throw new RuntimeException(e);
}
Console.WriteLine("This Test case has failed because the generated file differs from proof copy '"
); // TODO+ proofFile.AbsolutePath + "'.");
Console.WriteLine("The cause is usually a change to this Test, or some common spreadsheet generation code. "
+ "The developer has to decide whether the Changes were wanted or unwanted.");
Console.WriteLine("If the Changes to the generated version were unwanted, "
+ "make the fix elsewhere (do not modify this Test or the proof spreadsheet to Get the Test working).");
Console.WriteLine("If the Changes were wanted, make sure to open the newly generated file in Excel "
+ "and verify it manually. The new proof file should be submitted After it is verified to be correct.");
Console.WriteLine("");
Console.WriteLine("One other possible (but less likely) cause of a failed Test is a problem in the "
+ "comparison logic used here. Perhaps some extra file regions need to be ignored.");
Console.WriteLine("The generated file has been saved to '" + tempDir + "' for manual inspection.");
Assert.Fail("Generated file differs from proof copy. See sysout comments for details on how to fix.");
#endif
}
public void Test27349()
{
// 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
HSSFWorkbook wb;
try
{
// original bug may have thrown exception here, or output warning to
// stderr
wb = new HSSFWorkbook(is1);
}
catch (IOException e)
{
throw new SystemException(e.Message);
}
ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(0);
// this defInitely would have failed due to 27349
Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
.CellFormula);
// We might as well Evaluate the formula
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(3.0, cv.NumberValue, 0.0);
}
public void TestWithEmbededInOwn()
{
POIFSFileSystem fs = new POIFSFileSystem(
HSSFTestDataSamples.OpenSampleFileStream("excel_with_embeded.xls")
);
DirectoryNode dirA = (DirectoryNode)fs.Root.GetEntry("MBD0000A3B5");
DirectoryNode dirB = (DirectoryNode)fs.Root.GetEntry("MBD0000A3B4");
HSSFWorkbook wbA = new HSSFWorkbook(dirA, fs, true);
HSSFWorkbook wbB = new HSSFWorkbook(dirB, fs, true);
ExcelExtractor exA = new ExcelExtractor(wbA);
ExcelExtractor exB = new ExcelExtractor(wbB);
Assert.AreEqual("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n",
exA.Text);
Assert.AreEqual("Sample Excel", exA.SummaryInformation.Title);
Assert.AreEqual("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n",
exB.Text);
Assert.AreEqual("Sample Excel 2", exB.SummaryInformation.Title);
// And the base file too
ExcelExtractor ex = new ExcelExtractor(fs);
Assert.AreEqual("Sheet1\nI have lots of embeded files in me\nSheet2\nSheet3\n",
ex.Text);
Assert.AreEqual("Excel With Embeded", ex.SummaryInformation.Title);
}
public void TestDifferentPOIFS()
{
//throw new NotImplementedException("class NPOIFSFileSystem is not implemented");
// Open the two filesystems
DirectoryNode[] files = new DirectoryNode[2];
files[0] = (new POIFSFileSystem(HSSFTestDataSamples.OpenSampleFileStream("Simple.xls"))).Root;
files[1] = (new NPOIFSFileSystem(HSSFTestDataSamples.OpenSampleFileStream("Simple.xls"))).Root;
// Open without preserving nodes
foreach (DirectoryNode dir in files)
{
IWorkbook workbook = new HSSFWorkbook(dir, false);
ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual("replaceMe", cell.RichStringCellValue.String);
}
// Now re-check with preserving
foreach (DirectoryNode dir in files)
{
IWorkbook workbook = new HSSFWorkbook(dir, true);
ISheet sheet = workbook.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
Assert.AreEqual("replaceMe", cell.RichStringCellValue.String);
}
}
public void TestWrite()
{
foreach (String file in new String[] { xlsA, xlsB })
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(file);
POIFSFileSystem fs = new POIFSFileSystem(is1);
// Open the workbook, not preserving nodes
HSSFWorkbook wb = new HSSFWorkbook(fs);
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
// Check now
MemoryStream in1 = new MemoryStream(out1.ToArray());
POIFSFileSystem fs2 = new POIFSFileSystem(in1);
// Check that we have the new entries
fs2.Root.GetEntry("Workbook");
try
{
fs2.Root.GetEntry("BOOK");
Assert.Fail();
}
catch (FileNotFoundException e) { }
try
{
fs2.Root.GetEntry("WORKBOOK");
Assert.Fail();
}
catch (FileNotFoundException) { }
// And it can be Opened
HSSFWorkbook wb2 = new HSSFWorkbook(fs2);
}
}
public void TestWordDocEmbeddedInXls()
{
//throw new NotImplementedException("class NPOIFSFileSystem is not implemented");
// Open the two filesystems
DirectoryNode[] files = new DirectoryNode[2];
files[0] = (new POIFSFileSystem(HSSFTestDataSamples.OpenSampleFileStream("WithEmbeddedObjects.xls"))).Root;
files[1] = (new NPOIFSFileSystem(HSSFTestDataSamples.OpenSampleFileStream("WithEmbeddedObjects.xls"))).Root;
// Check the embedded parts
foreach (DirectoryNode root in files)
{
HSSFWorkbook hw = new HSSFWorkbook(root, true);
IList <HSSFObjectData> objects = hw.GetAllEmbeddedObjects();
bool found = false;
for (int i = 0; i < objects.Count; i++)
{
HSSFObjectData embeddedObject = objects[i];
if (embeddedObject.HasDirectoryEntry())
{
DirectoryEntry dir = embeddedObject.GetDirectory();
if (dir is DirectoryNode)
{
DirectoryNode dNode = (DirectoryNode)dir;
if (HasEntry(dNode, "WordDocument"))
{
found = true;
}
}
}
}
Assert.IsTrue(found);
}
}
public void TestOK()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("Simple.xls");
POIFSFileSystem fs = new POIFSFileSystem(is1);
//set POIFS properties before constructing HSSFWorkbook
SummaryInformation summary1 = (SummaryInformation)PropertySetFactory.Create(fs.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME));
summary1.Title = (title);
fs.Root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME).Delete();
fs.CreateDocument(summary1.ToInputStream(), SummaryInformation.DEFAULT_STREAM_NAME);
HSSFWorkbook wb = new HSSFWorkbook(fs);
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
out1.Close();
//read the property
POIFSFileSystem fs2 = new POIFSFileSystem(new MemoryStream(out1.ToArray()));
SummaryInformation summary2 = (SummaryInformation)PropertySetFactory.Create(fs2.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME));
Assert.AreEqual(title, summary2.Title);
}
public void WriteWorkbookFromNPOIFS()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("WithEmbeddedObjects.xls");
try
{
NPOIFSFileSystem fs = new NPOIFSFileSystem(is1);
try
{
// Start as NPOIFS
HSSFWorkbook wb = new HSSFWorkbook(fs.Root, true);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual("Root xls", wb.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
// Will switch to POIFS
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual("Root xls", wb.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
}
finally
{
fs.Close();
}
}
finally
{
is1.Close();
}
}
public void TestFail()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("Simple.xls");
POIFSFileSystem fs = new POIFSFileSystem(is1);
HSSFWorkbook wb = new HSSFWorkbook(fs);
//set POIFS properties after constructing HSSFWorkbook
//(a piece of code that used to work up to POI 3.0.2)
SummaryInformation summary1 = (SummaryInformation)PropertySetFactory.Create(fs.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME));
summary1.Title = (title);
//Write the modified property back to POIFS
fs.Root.GetEntry(SummaryInformation.DEFAULT_STREAM_NAME).Delete();
fs.CreateDocument(summary1.ToInputStream(), SummaryInformation.DEFAULT_STREAM_NAME);
//save the workbook and read the property
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
out1.Close();
POIFSFileSystem fs2 = new POIFSFileSystem(new MemoryStream(out1.ToArray()));
SummaryInformation summary2 = (SummaryInformation)PropertySetFactory.Create(fs2.CreateDocumentInputStream(SummaryInformation.DEFAULT_STREAM_NAME));
//Assert.Failing assertion
Assert.AreEqual(title, summary2.Title);
}
public void TestDiscontinousReference()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls");
HSSFWorkbook wb = new HSSFWorkbook(is1);
InternalWorkbook 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.IName 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 void TestCreateGeneric()
{
IWorkbook wb;
// InputStream -> either
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx, wb);
// File -> either
wb = WorkbookFactory.Create(
Path.GetFullPath(testdataPath + xls)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
AssertCloseDoesNotModifyFile(xls, wb);
wb = WorkbookFactory.Create(
testdataPath + xlsx
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
AssertCloseDoesNotModifyFile(xlsx, wb);
// Invalid type -> exception
byte[] before = HSSFTestDataSamples.GetTestDataFileContent(txt);
try
{
Stream stream = HSSFTestDataSamples.OpenSampleFileStream(txt);
try
{
wb = WorkbookFactory.Create(stream);
}
finally
{
stream.Close();
}
Assert.Fail();
}
catch (InvalidFormatException)
{
// Good
}
byte[] after = HSSFTestDataSamples.GetTestDataFileContent(txt);
CollectionAssert.AreEqual(before, after, "Invalid type file was modified after trying to open the file as a spreadsheet");
}
public void TestCreateGeneric()
{
IWorkbook wb;
// InputStream -> either
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xls)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
wb.Close();
wb = WorkbookFactory.Create(
HSSFTestDataSamples.OpenSampleFileStream(xlsx)
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
// File -> either
wb = WorkbookFactory.Create(
testdataPath + xls
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
wb.Close();
wb = WorkbookFactory.Create(
testdataPath + xlsx
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
// TODO: close() re-writes the sample-file?! Resort to revert() for now to close file handle...
((XSSFWorkbook)wb).Package.Revert();
// Invalid type -> exception
try
{
Stream stream = HSSFTestDataSamples.OpenSampleFileStream(txt);
try
{
wb = WorkbookFactory.Create(stream);
}
finally
{
stream.Close();
}
Assert.Fail();
}
catch (ArgumentException)
{
// Good
}
}
private static POIFSFileSystem OpenSampleFS()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("39234.xls");
try
{
return(new POIFSFileSystem(is1));
}
catch (IOException)
{
throw;
}
}
protected XSSFExcelExtractor GetExtractor(String sampleName)
{
ExtractorFactory.SetAllThreadsPreferEventExtractors(false);
ExtractorFactory.SetThreadPrefersEventExtractors(false);
try
{
return((XSSFExcelExtractor)ExtractorFactory.CreateExtractor(HSSFTestDataSamples.OpenSampleFileStream(sampleName)));
}
catch (Exception e)
{
throw new RuntimeException(e);
}
}
private static byte[] ReadSampleHexData(String sampleFileName, String sectionName,int recSid)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(sampleFileName);
byte[] data;
try {
data = HexRead.ReadData(is1, sectionName);
}
catch (IOException)
{
throw;
}
return TestcaseRecordInputStream.MergeDataAndSid(recSid, data.Length, data);
}
private static ExcelExtractor CreateExtractor(String sampleFileName)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(sampleFileName);
try
{
return(new ExcelExtractor(new POIFSFileSystem(is1)));
}
catch (IOException)
{
throw;
}
}
public void TestBOFRecord1()
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("bug_42794.xls");
// This used to throw an error before
try
{
new HSSFWorkbook(is1);
}
catch (IndexOutOfRangeException)
{
throw new AssertionException("Identified bug 42794");
}
}
public void SetUp()
{
if (workbook == null)
{
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME);
OPCPackage pkg = OPCPackage.Open(is1);
workbook = new XSSFWorkbook(pkg);
sheet = workbook.GetSheet(SS.TEST_SHEET_NAME);
}
_functionFailureCount = 0;
_functionSuccessCount = 0;
_EvaluationFailureCount = 0;
_EvaluationSuccessCount = 0;
}
public void TestExisting()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("Formatting.xlsx");
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
// And check a few low level bits too
OPCPackage pkg = OPCPackage.Open(HSSFTestDataSamples.OpenSampleFileStream("Formatting.xlsx"));
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared, styles and themes
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(6, wbPart.Relationships.Size);
}
public void TestWriteWorkbookFromNPOIFS()
{
//throw new NotImplementedException("class NPOIFSFileSystem is not implemented");
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("WithEmbeddedObjects.xls");
NPOIFSFileSystem fs = new NPOIFSFileSystem(is1);
// Start as NPOIFS
HSSFWorkbook wb = new HSSFWorkbook(fs.Root, true);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual("Root xls", wb.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
// Will switch to POIFS
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual("Root xls", wb.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
}
public void TestEventExtractor()
{
EventBasedExcelExtractor extractor;
// First up, a simple file with string
// based formulas in it
extractor = new EventBasedExcelExtractor(
new POIFSFileSystem(
HSSFTestDataSamples.OpenSampleFileStream("SimpleWithFormula.xls")
)
);
extractor.IncludeSheetNames = (true);
String text = extractor.Text;
Assert.AreEqual("Sheet1\nreplaceme\nreplaceme\nreplacemereplaceme\nSheet2\nSheet3\n", text);
extractor.IncludeSheetNames = (false);
extractor.FormulasNotResults = (true);
text = extractor.Text;
Assert.AreEqual("replaceme\nreplaceme\nCONCATENATE(A1,A2)\n", text);
// Now, a slightly longer file with numeric formulas
extractor = new EventBasedExcelExtractor(
new POIFSFileSystem(
HSSFTestDataSamples.OpenSampleFileStream("sumifformula.xls")
)
);
extractor.IncludeSheetNames = (false);
extractor.FormulasNotResults = (true);
text = extractor.Text;
Assert.AreEqual(
"1000\t1\tSUMIF(A1:A5,\">4000\",B1:B5)\n" +
"2000\t2\n" +
"3000\t3\n" +
"4000\t4\n" +
"5000\t5\n",
text
);
}
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 TestCreateNative()
{
IWorkbook wb;
// POIFS -> hssf
wb = WorkbookFactory.Create(
new POIFSFileSystem(HSSFTestDataSamples.OpenSampleFileStream(xls))
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is HSSFWorkbook);
// Package -> xssf
wb = WorkbookFactory.Create(
OPCPackage.Open(
HSSFTestDataSamples.OpenSampleFileStream(xlsx))
);
Assert.IsNotNull(wb);
Assert.IsTrue(wb is XSSFWorkbook);
}
public void Test27349()
{
// 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
IWorkbook wb = new HSSFWorkbook(is1);
ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(0);
// this defInitely would have failed due to 27349
Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
.CellFormula);
// We might as well Evaluate the formula
IFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Numeric, cv.CellType);
Assert.AreEqual(3.0, cv.NumberValue, 0.0);
}