src/testcases/org/apache/poi/hssf/extractor/TestExcelExtractor.java

   1 /* ====================================================================
   2    Licensed to the Apache Software Foundation (ASF) under one or more
   3    contributor license agreements.  See the NOTICE file distributed with
   4    this work for additional information regarding copyright ownership.
   5    The ASF licenses this file to You under the Apache License, Version 2.0
   6    (the "License"); you may not use this file except in compliance with
   7    the License.  You may obtain a copy of the License at
   8
   9        http://www.apache.org/licenses/LICENSE-2.0
  10
  11    Unless required by applicable law or agreed to in writing, software
  12    distributed under the License is distributed on an "AS IS" BASIS,
  13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14    See the License for the specific language governing permissions and
  15    limitations under the License.
  16 ==================================================================== */
  17
  18 package org.apache.poi.hssf.extractor;
  19
  20 import java.io.FileInputStream;
  21 import java.io.IOException;
  22 import java.io.InputStream;
  23
  24 import junit.framework.TestCase;
  25
  26 import org.apache.poi.hssf.HSSFTestDataSamples;
  27 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  28 import org.apache.poi.poifs.filesystem.DirectoryNode;
  29 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  30 /**
  31  *
  32  */
  33 public final class TestExcelExtractor extends TestCase {
  34
  35         private static final ExcelExtractor createExtractor(String sampleFileName) {
  36
  37                 InputStream is = HSSFTestDataSamples.openSampleFileStream(sampleFileName);
  38
  39                 try {
  40                         return new ExcelExtractor(new POIFSFileSystem(is));
  41                 } catch (IOException e) {
  42                         throw new RuntimeException(e);
  43                 }
  44         }
  45
  46
  47         public void testSimple() {
  48
  49                 ExcelExtractor extractor = createExtractor("Simple.xls");
  50
  51                 assertEquals("Sheet1\nreplaceMe\nSheet2\nSheet3\n", extractor.getText());
  52
  53                 // Now turn off sheet names
  54                 extractor.setIncludeSheetNames(false);
  55                 assertEquals("replaceMe\n", extractor.getText());
  56         }
  57
  58         public void testNumericFormula() {
  59
  60                 ExcelExtractor extractor = createExtractor("sumifformula.xls");
  61
  62                 assertEquals(
  63                                 "Sheet1\n" +
  64                                 "1000.0\t1.0\t5.0\n" +
  65                                 "2000.0\t2.0\t\n" +
  66                                 "3000.0\t3.0\t\n" +
  67                                 "4000.0\t4.0\t\n" +
  68                                 "5000.0\t5.0\t\n" +
  69                                 "Sheet2\nSheet3\n",
  70                                 extractor.getText()
  71                 );
  72
  73                 extractor.setFormulasNotResults(true);
  74
  75                 assertEquals(
  76                                 "Sheet1\n" +
  77                                 "1000.0\t1.0\tSUMIF(A1:A5,\">4000\",B1:B5)\n" +
  78                                 "2000.0\t2.0\t\n" +
  79                                 "3000.0\t3.0\t\n" +
  80                                 "4000.0\t4.0\t\n" +
  81                                 "5000.0\t5.0\t\n" +
  82                                 "Sheet2\nSheet3\n",
  83                                 extractor.getText()
  84                 );
  85         }
  86
  87         public void testwithContinueRecords() {
  88
  89                 ExcelExtractor extractor = createExtractor("StringContinueRecords.xls");
  90
  91                 extractor.getText();
  92
  93                 // Has masses of text
  94                 // Until we fixed bug #41064, this would've
  95                 //   failed by now
  96                 assertTrue(extractor.getText().length() > 40960);
  97         }
  98
  99         public void testStringConcat() {
 100
 101                 ExcelExtractor extractor = createExtractor("SimpleWithFormula.xls");
 102
 103                 // Comes out as NaN if treated as a number
 104                 // And as XYZ if treated as a string
 105                 assertEquals("Sheet1\nreplaceme\nreplaceme\nreplacemereplaceme\nSheet2\nSheet3\n", extractor.getText());
 106
 107                 extractor.setFormulasNotResults(true);
 108
 109                 assertEquals("Sheet1\nreplaceme\nreplaceme\nCONCATENATE(A1,A2)\nSheet2\nSheet3\n", extractor.getText());
 110         }
 111
 112         public void testStringFormula() {
 113
 114                 ExcelExtractor extractor = createExtractor("StringFormulas.xls");
 115
 116                 // Comes out as NaN if treated as a number
 117                 // And as XYZ if treated as a string
 118                 assertEquals("Sheet1\nXYZ\nSheet2\nSheet3\n", extractor.getText());
 119
 120                 extractor.setFormulasNotResults(true);
 121
 122                 assertEquals("Sheet1\nUPPER(\"xyz\")\nSheet2\nSheet3\n", extractor.getText());
 123         }
 124
 125         /**
 126          * Embded in a non-excel file
 127          */
 128         public void testWithEmbeded() throws Exception {
 129                 String pdirname = System.getProperty("POIFS.testdata.path");
 130                 String filename = pdirname + "/word_with_embeded.doc";
 131                 POIFSFileSystem fs = new POIFSFileSystem(
 132                                 new FileInputStream(filename)
 133                 );
 134
 135                 DirectoryNode objPool = (DirectoryNode)
 136                         fs.getRoot().getEntry("ObjectPool");
 137                 DirectoryNode dirA = (DirectoryNode)
 138                         objPool.getEntry("_1269427460");
 139                 DirectoryNode dirB = (DirectoryNode)
 140                         objPool.getEntry("_1269427461");
 141
 142                 HSSFWorkbook wbA = new HSSFWorkbook(dirA, fs, true);
 143                 HSSFWorkbook wbB = new HSSFWorkbook(dirB, fs, true);
 144
 145                 ExcelExtractor exA = new ExcelExtractor(wbA);
 146                 ExcelExtractor exB = new ExcelExtractor(wbB);
 147
 148                 assertEquals("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n",
 149                                 exA.getText());
 150                 assertEquals("Sample Excel", exA.getSummaryInformation().getTitle());
 151
 152                 assertEquals("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n",
 153                                 exB.getText());
 154                 assertEquals("Sample Excel 2", exB.getSummaryInformation().getTitle());
 155         }
 156
 157         /**
 158          * Excel embeded in excel
 159          */
 160         public void testWithEmbededInOwn() throws Exception {
 161                 String pdirname = System.getProperty("POIFS.testdata.path");
 162                 String filename = pdirname + "/excel_with_embeded.xls";
 163                 POIFSFileSystem fs = new POIFSFileSystem(
 164                                 new FileInputStream(filename)
 165                 );
 166
 167         DirectoryNode dirA = (DirectoryNode)
 168                         fs.getRoot().getEntry("MBD0000A3B5");
 169                 DirectoryNode dirB = (DirectoryNode)
 170                         fs.getRoot().getEntry("MBD0000A3B4");
 171
 172                 HSSFWorkbook wbA = new HSSFWorkbook(dirA, fs, true);
 173                 HSSFWorkbook wbB = new HSSFWorkbook(dirB, fs, true);
 174
 175                 ExcelExtractor exA = new ExcelExtractor(wbA);
 176                 ExcelExtractor exB = new ExcelExtractor(wbB);
 177
 178                 assertEquals("Sheet1\nTest excel file\nThis is the first file\nSheet2\nSheet3\n",
 179                                 exA.getText());
 180                 assertEquals("Sample Excel", exA.getSummaryInformation().getTitle());
 181
 182                 assertEquals("Sheet1\nAnother excel file\nThis is the second file\nSheet2\nSheet3\n",
 183                                 exB.getText());
 184                 assertEquals("Sample Excel 2", exB.getSummaryInformation().getTitle());
 185
 186                 // And the base file too
 187                 ExcelExtractor ex = new ExcelExtractor(fs);
 188                 assertEquals("Sheet1\nI have lots of embeded files in me\nSheet2\nSheet3\n",
 189                                 ex.getText());
 190                 assertEquals("Excel With Embeded", ex.getSummaryInformation().getTitle());
 191         }
 192 }