public void CloseDoesNotModifyWorkbook()
{
String filename = "SampleSS.xlsx";
FileInfo file = POIDataSamples.GetSpreadSheetInstance().GetFileInfo(filename);
IWorkbook wb;
// Some tests commented out because close() modifies the file
// See bug 58779
// String
//wb = new XSSFWorkbook(file.Path);
//assertCloseDoesNotModifyFile(filename, wb);
// File
//wb = new XSSFWorkbook(file);
//assertCloseDoesNotModifyFile(filename, wb);
// InputStream
wb = new XSSFWorkbook(file.Create());
assertCloseDoesNotModifyFile(filename, wb);
// OPCPackage
//wb = new XSSFWorkbook(OPCPackage.open(file));
//assertCloseDoesNotModifyFile(filename, wb);
}
public void TestFindCommentShape()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
vml.Read(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("vmlDrawing1.vml"));
CT_Shape sh_a1 = vml.FindCommentShape(0, 0);
Assert.IsNotNull(sh_a1);
Assert.AreEqual("_x0000_s1025", sh_a1.id);
CT_Shape sh_b1 = vml.FindCommentShape(0, 1);
Assert.IsNotNull(sh_b1);
Assert.AreEqual("_x0000_s1026", sh_b1.id);
CT_Shape sh_c1 = vml.FindCommentShape(0, 2);
Assert.IsNull(sh_c1);
CT_Shape sh_d1 = vml.newCommentShape();
Assert.AreEqual("_x0000_s1027", sh_d1.id);
sh_d1.GetClientDataArray(0).SetRowArray(0, 0);
sh_d1.GetClientDataArray(0).SetColumnArray(0, 3);
Assert.AreSame(sh_d1, vml.FindCommentShape(0, 3));
//newly created drawing
XSSFVMLDrawing newVml = new XSSFVMLDrawing();
Assert.IsNull(newVml.FindCommentShape(0, 0));
sh_a1 = newVml.newCommentShape();
Assert.AreEqual("_x0000_s1025", sh_a1.id);
sh_a1.GetClientDataArray(0).SetRowArray(0, 0);
sh_a1.GetClientDataArray(0).SetColumnArray(0, 1);
Assert.AreSame(sh_a1, newVml.FindCommentShape(0, 1));
}
public void TestXLSX()
{
POIDataSamples pds = POIDataSamples.GetSpreadSheetInstance();
AssertReadWrite(
PackageHelper.Open(pds.OpenResourceAsStream("ExcelWithAttachments.xlsm"))
);
}
public void TestExcel()
{
POIXMLDocument doc = new XSSFWorkbook(
POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("ExcelWithAttachments.xlsm")
);
Test(doc, 4);
}
public void TestRemoveCommentShape()
{
XSSFVMLDrawing vml = new XSSFVMLDrawing();
vml.Read(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("vmlDrawing1.vml"));
CT_Shape sh_a1 = vml.FindCommentShape(0, 0);
Assert.IsNotNull(sh_a1);
Assert.IsTrue(vml.RemoveCommentShape(0, 0));
Assert.IsNull(vml.FindCommentShape(0, 0));
}
private static POIFSFileSystem OpenSampleFS()
{
Stream is1 = POIDataSamples.GetSpreadSheetInstance().GetFile("39234.xls");
try
{
return(new POIFSFileSystem(is1));
}
catch (IOException)
{
throw;
}
}
//[TestMethod]
public void TestExcelToHtmlConverter()
{
string[] fileNames = POIDataSamples.GetSpreadSheetInstance().GetFiles("*.xls");
List <string> toConverter = new List <string>();
StringBuilder stringBuilder = new StringBuilder();
foreach (string filename in fileNames)
{
if (filename.EndsWith(".xls"))
{
toConverter.Add(filename);
}
else
{
continue;
}
try
{
Test(filename);
}
catch (Exception ex)
{
failingFiles.Add(filename);
stringBuilder.AppendLine(filename);
stringBuilder.AppendLine(ex.Source);
stringBuilder.AppendLine(ex.Message);
stringBuilder.AppendLine(ex.StackTrace);
stringBuilder.AppendLine("**************************************");
}
}
//
// TODO: 在此 添加测试逻辑
//
string output = string.Empty;
if (failingFiles.Count > 0)
{
output = Path.GetDirectoryName(failingFiles[0]) + "\\failxls.txt";
using (StreamWriter sw = new StreamWriter(output, false))
{
foreach (string file in failingFiles)
{
sw.WriteLine(file);
}
sw.WriteLine("**********************************************************");
sw.Write(stringBuilder.ToString());
sw.Close();
}
}
Assert.IsTrue(failingFiles.Count == 0, "{0}({1}) files failed to convert to html. see " + output, failingFiles.Count, toConverter.Count);
}
public void Test49658()
{
// test if inserted EscherMetafileBlip will be read again
IWorkbook wb = new HSSFWorkbook();
byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf");
int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF);
byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png");
int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG);
byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf");
int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF);
ISheet sheet = wb.CreateSheet();
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
ICreationHelper ch = wb.GetCreationHelper();
IClientAnchor anchor = ch.CreateClientAnchor();
anchor.Col1 = (/*setter*/ 2);
anchor.Col2 = (/*setter*/ 5);
anchor.Row1 = (/*setter*/ 1);
anchor.Row2 = (/*setter*/ 6);
patriarch.CreatePicture(anchor, indexEmf);
anchor = ch.CreateClientAnchor();
anchor.Col1 = (/*setter*/ 2);
anchor.Col2 = (/*setter*/ 5);
anchor.Row1 = (/*setter*/ 10);
anchor.Row2 = (/*setter*/ 16);
patriarch.CreatePicture(anchor, indexPng);
anchor = ch.CreateClientAnchor();
anchor.Col1 = (/*setter*/ 6);
anchor.Col2 = (/*setter*/ 9);
anchor.Row1 = (/*setter*/ 1);
anchor.Row2 = (/*setter*/ 6);
patriarch.CreatePicture(anchor, indexWmf);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook);
byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data;
Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut));
byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22];
Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22);
pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data;
Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut));
}
public void testTidyStreamOnInvalidFile()
{
// Spreadsheet has a good mix of alternate file types
POIDataSamples files = POIDataSamples.GetSpreadSheetInstance();
FileInfo[] notValidF = new FileInfo[] {
files.GetFileInfo("SampleSS.ods"), files.GetFileInfo("SampleSS.txt")
};
Stream[] notValidS = new Stream[] {
files.OpenResourceAsStream("SampleSS.ods"), files.OpenResourceAsStream("SampleSS.txt")
};
foreach (FileInfo notValid in notValidF)
{
ZipPackage pkg = new ZipPackage(notValid, PackageAccess.READ);
Assert.IsNotNull(pkg.ZipArchive);
Assert.IsFalse(pkg.ZipArchive.IsClosed);
try
{
pkg.GetParts();
Assert.Fail("Shouldn't work");
}
catch (ODFNotOfficeXmlFileException e)
{
}
catch (NotOfficeXmlFileException ne) { }
pkg.Close();
Assert.IsNotNull(pkg.ZipArchive);
Assert.IsTrue(pkg.ZipArchive.IsClosed);
}
foreach (InputStream notValid in notValidS)
{
ZipPackage pkg = new ZipPackage(notValid, PackageAccess.READ);
Assert.IsNotNull(pkg.ZipArchive);
Assert.IsFalse(pkg.ZipArchive.IsClosed);
try
{
pkg.GetParts();
Assert.Fail("Shouldn't work");
}
catch (ODFNotOfficeXmlFileException e)
{
}
catch (NotOfficeXmlFileException ne) { }
pkg.Close();
Assert.IsNotNull(pkg.ZipArchive);
Assert.IsTrue(pkg.ZipArchive.IsClosed);
}
}
public void Test47920()
{
POIFSFileSystem fs1 = new POIFSFileSystem(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("47920.xls"));
IWorkbook wb = new HSSFWorkbook(fs1);
ClassID clsid1 = fs1.Root.StorageClsid;
MemoryStream out1 = new MemoryStream(4096);
wb.Write(out1);
byte[] bytes = out1.ToArray();
POIFSFileSystem fs2 = new POIFSFileSystem(new MemoryStream(bytes));
ClassID clsid2 = fs2.Root.StorageClsid;
Assert.IsTrue(clsid1.Equals(clsid2));
}
public void TestListOfCustomProperties()
{
FileInfo inp = POIDataSamples.GetSpreadSheetInstance().GetFileInfo("ExcelWithAttachments.xlsm");
OPCPackage pkg = OPCPackage.Open(inp, PackageAccess.READ);
XSSFWorkbook wb = new XSSFWorkbook(pkg);
Assert.IsNotNull(wb.GetProperties());
Assert.IsNotNull(wb.GetProperties().CustomProperties);
foreach (CT_Property prop in wb.GetProperties().CustomProperties.GetUnderlyingProperties().GetPropertyList())
{
Assert.IsNotNull(prop);
}
wb.Close();
pkg.Close();
}
static TestVBAMacroReader()
{
Dictionary <POIDataSamples, String> _expectedMacroContents = new Dictionary <POIDataSamples, String>();
POIDataSamples[] dataSamples =
{
POIDataSamples.GetSpreadSheetInstance(),
POIDataSamples.GetSlideShowInstance(),
POIDataSamples.GetDocumentInstance(),
POIDataSamples.GetDiagramInstance()
};
foreach (POIDataSamples sample in dataSamples)
{
_expectedMacroContents.Add(sample, ReadVBA(sample));
}
expectedMacroContents = _expectedMacroContents;
}
private List <String> ReadStrings(String filename)
{
List <String> strs = new List <String>();
POIDataSamples samples = POIDataSamples.GetSpreadSheetInstance();
StreamReader br =
new StreamReader(samples.OpenResourceAsStream(filename));
String s;
while ((s = br.ReadLine()) != null)
{
if (s.Trim().Length > 0)
{
strs.Add(s.Trim());
}
}
br.Close();
return(strs);
}
public void closeDoesNotModifyWorkbook()
{
String filename = "SampleSS.xlsx";
FileInfo file = POIDataSamples.GetSpreadSheetInstance().GetFileInfo(filename);
SXSSFWorkbook wb = null;
XSSFWorkbook xwb = null;
// Some tests commented out because close() modifies the file
// See bug 58779
// String
//wb = new SXSSFWorkbook(new XSSFWorkbook(file.Path));
//assertCloseDoesNotModifyFile(filename, wb);
// File
//wb = new SXSSFWorkbook(new XSSFWorkbook(file));
//assertCloseDoesNotModifyFile(filename, wb);
// InputStream
FileStream fis = file.Open(FileMode.Open, FileAccess.ReadWrite);
try
{
xwb = new XSSFWorkbook(fis);
wb = new SXSSFWorkbook(xwb);
assertCloseDoesNotModifyFile(filename, wb);
}
finally
{
if (xwb != null)
{
xwb.Close();
}
if (wb != null)
{
wb.Close();
}
fis.Close();
}
// OPCPackage
//wb = new SXSSFWorkbook(new XSSFWorkbook(OPCPackage.open(file)));
//assertCloseDoesNotModifyFile(filename, wb);
}
public void BinaryRC4Encryption()
{
// please contribute a real sample file, which is binary rc4 encrypted
// ... at least the output can be opened in Excel Viewer
String password = "******";
Stream is1 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("SimpleMultiCell.xlsx");
MemoryStream payloadExpected = new MemoryStream();
IOUtils.Copy(is1, payloadExpected);
is1.Close();
POIFSFileSystem fs = new POIFSFileSystem();
EncryptionInfo ei = new EncryptionInfo(EncryptionMode.BinaryRC4);
Encryptor enc = ei.Encryptor;
enc.ConfirmPassword(password);
Stream os = enc.GetDataStream(fs.Root);
payloadExpected.WriteTo(os);
os.Close();
MemoryStream bos = new MemoryStream();
fs.WriteFileSystem(bos);
fs = new POIFSFileSystem(new MemoryStream(bos.ToArray()));
ei = new EncryptionInfo(fs);
Decryptor dec = ei.Decryptor;
bool b = dec.VerifyPassword(password);
Assert.IsTrue(b);
MemoryStream payloadActual = new MemoryStream();
is1 = dec.GetDataStream(fs.Root);
IOUtils.Copy(is1, payloadActual);
is1.Close();
Assert.IsTrue(Arrays.Equals(payloadExpected.ToArray(), payloadActual.ToArray()));
//assertArrayEquals(payloadExpected.ToArray(), payloadActual.ToArray());
}
public void HelpfulExceptionOnOldFiles()
{
Stream excel4 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("testEXCEL_4.xls");
try
{
new HSSFWorkbook(excel4);
Assert.Fail("Shouldn't be able to load an Excel 4 file");
}
catch (OldExcelFormatException e)
{
POITestCase.AssertContains(e.Message, "BIFF4");
}
excel4.Close();
Stream excel5 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("testEXCEL_5.xls");
try
{
new HSSFWorkbook(excel5);
Assert.Fail("Shouldn't be able to load an Excel 5 file");
}
catch (OldExcelFormatException e)
{
POITestCase.AssertContains(e.Message, "BIFF8");
}
excel5.Close();
Stream excel95 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("testEXCEL_95.xls");
try
{
new HSSFWorkbook(excel95);
Assert.Fail("Shouldn't be able to load an Excel 95 file");
}
catch (OldExcelFormatException e)
{
POITestCase.AssertContains(e.Message, "BIFF5");
}
excel95.Close();
}
public void Bug59858()
{
try
{
FromFile(POIDataSamples.GetSpreadSheetInstance(), "59858.xls");
POITestCase.TestPassesNow(59858);
}
catch (IOException e)
{
if (Regex.Match(e.Message, "Module offset for '.+' was never Read.").Success)
{
//e.PrintStackTrace();
// NPE when Reading module.offset in VBAMacroReader.ReadMacros (approx line 258)
POITestCase.SkipTest(e);
}
else
{
// something unexpected failed
throw e;
}
}
}
public void NPOIFSReadCopyWritePOIFSRead()
{
FileStream testFile = POIDataSamples.GetSpreadSheetInstance().GetFile("Simple.xls");
NPOIFSFileSystem src = new NPOIFSFileSystem(testFile);
byte[] wbDataExp = IOUtils.ToByteArray(src.CreateDocumentInputStream("Workbook"));
NPOIFSFileSystem nfs = new NPOIFSFileSystem();
EntryUtils.CopyNodes(src.Root, nfs.Root);
src.Close();
MemoryStream bos = new MemoryStream();
nfs.WriteFileSystem(bos);
nfs.Close();
POIFSFileSystem pfs = new POIFSFileSystem(new MemoryStream(bos.ToArray()));
byte[] wbDataAct = IOUtils.ToByteArray(pfs.CreateDocumentInputStream("Workbook"));
Assert.That(wbDataExp, new EqualConstraint(wbDataAct));
}
public void TestExtractFromEmbeded()
{
POIFSFileSystem fs = new POIFSFileSystem(POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream(filename3));
HWPFDocument doc;
WordExtractor extractor3;
DirectoryNode dirA = (DirectoryNode)fs.Root.GetEntry("MBD0000A3B7");
DirectoryNode dirB = (DirectoryNode)fs.Root.GetEntry("MBD0000A3B2");
// Should have WordDocument and 1Table
Assert.IsNotNull(dirA.GetEntry("1Table"));
Assert.IsNotNull(dirA.GetEntry("WordDocument"));
Assert.IsNotNull(dirB.GetEntry("1Table"));
Assert.IsNotNull(dirB.GetEntry("WordDocument"));
// Check each in turn
doc = new HWPFDocument(dirA, fs);
extractor3 = new WordExtractor(doc);
Assert.IsNotNull(extractor3.Text);
Assert.IsTrue(extractor3.Text.Length > 20);
Assert.AreEqual("I am a sample document\r\nNot much on me\r\nI am document 1\r\n", extractor3
.Text);
Assert.AreEqual("Sample Doc 1", extractor3.SummaryInformation.Title);
Assert.AreEqual("Sample Test", extractor3.SummaryInformation.Subject);
doc = new HWPFDocument(dirB, fs);
extractor3 = new WordExtractor(doc);
Assert.IsNotNull(extractor3.Text);
Assert.IsTrue(extractor3.Text.Length > 20);
Assert.AreEqual("I am another sample document\r\nNot much on me\r\nI am document 2\r\n",
extractor3.Text);
Assert.AreEqual("Sample Doc 2", extractor3.SummaryInformation.Title);
Assert.AreEqual("Another Sample Test", extractor3.SummaryInformation.Subject);
}
public void NonOOXMLFileTypes()
{
// Spreadsheet has a good mix of alternate file types
POIDataSamples files = POIDataSamples.GetSpreadSheetInstance();
// OLE2 - Stream
try
{
Stream stream = files.OpenResourceAsStream("SampleSS.xls");
try
{
OPCPackage.Open(stream);
}
finally
{
stream.Dispose();//.Close();
}
Assert.Fail("Shouldn't be able to open OLE2");
}
catch (OLE2NotOfficeXmlFileException e)
{
Assert.IsTrue(e.Message.Contains("The supplied data appears to be in the OLE2 Format"));
Assert.IsTrue(e.Message.Contains("You are calling the part of POI that deals with OOXML"));
}
// OLE2 - File
try
{
OPCPackage.Open(files.GetFile("SampleSS.xls"));
Assert.Fail("Shouldn't be able to open OLE2");
}
catch (OLE2NotOfficeXmlFileException e)
{
Assert.IsTrue(e.Message.Contains("The supplied data appears to be in the OLE2 Format"));
Assert.IsTrue(e.Message.Contains("You are calling the part of POI that deals with OOXML"));
}
// Raw XML - Stream
try
{
Stream stream = files.OpenResourceAsStream("SampleSS.xml");
try
{
OPCPackage.Open(stream);
}
finally
{
stream.Dispose();//.Close();
}
Assert.Fail("Shouldn't be able to open XML");
}
catch (NotOfficeXmlFileException e)
{
Assert.IsTrue(e.Message.Contains("The supplied data appears to be a raw XML file"));
Assert.IsTrue(e.Message.Contains("Formats such as Office 2003 XML"));
}
// Raw XML - File
try
{
OPCPackage.Open(files.GetFile("SampleSS.xml"));
Assert.Fail("Shouldn't be able to open XML");
}
catch (NotOfficeXmlFileException e)
{
Assert.IsTrue(e.Message.Contains("The supplied data appears to be a raw XML file"));
Assert.IsTrue(e.Message.Contains("Formats such as Office 2003 XML"));
}
// ODF / ODS - Stream
try
{
Stream stream = files.OpenResourceAsStream("SampleSS.ods");
try
{
OPCPackage.Open(stream);
}
finally
{
stream.Dispose();//.Close();
}
Assert.Fail("Shouldn't be able to open ODS");
}
catch (ODFNotOfficeXmlFileException e)
{
Assert.IsTrue(e.ToString().Contains("The supplied data appears to be in ODF"));
Assert.IsTrue(e.ToString().Contains("Formats like these (eg ODS"));
}
// ODF / ODS - File
try
{
OPCPackage.Open(files.GetFile("SampleSS.ods"));
Assert.Fail("Shouldn't be able to open ODS");
}
catch (ODFNotOfficeXmlFileException e)
{
Assert.IsTrue(e.ToString().Contains("The supplied data appears to be in ODF"));
Assert.IsTrue(e.ToString().Contains("Formats like these (eg ODS"));
}
// Plain Text - Stream
try
{
Stream stream = files.OpenResourceAsStream("SampleSS.txt");
try
{
OPCPackage.Open(stream);
}
finally
{
stream.Dispose();//.Close();
}
Assert.Fail("Shouldn't be able to open Plain Text");
}
catch (NotOfficeXmlFileException e)
{
Assert.IsTrue(e.Message.Contains("No valid entries or contents found"));
Assert.IsTrue(e.Message.Contains("not a valid OOXML"));
}
// Plain Text - File
try
{
OPCPackage.Open(files.GetFile("SampleSS.txt"));
Assert.Fail("Shouldn't be able to open Plain Text");
}
catch (UnsupportedFileFormatException)
{
// Unhelpful low-level error, sorry
}
}
public byte[] GetTestDataFileContent(String fileName)
{
return(POIDataSamples.GetSpreadSheetInstance().ReadFile(fileName));
}
public void HSSFfromNPOIFS()
{
FromNPOIFS(POIDataSamples.GetSpreadSheetInstance(), "SimpleMacro.xls");
}
private static Stream OpenSampleStream(String sampleFileName)
{
return(POIDataSamples.GetSpreadSheetInstance().GetFile(sampleFileName));
}
public void XSSFfromStream()
{
FromStream(POIDataSamples.GetSpreadSheetInstance(), "SimpleMacro.xlsm");
}