src/java/org/apache/poi/hssf/extractor/ExcelExtractor.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 package org.apache.poi.hssf.extractor;
  18
  19 import java.io.IOException;
  20
  21 import org.apache.poi.POIOLE2TextExtractor;
  22 import org.apache.poi.hssf.usermodel.HSSFCell;
  23 import org.apache.poi.hssf.usermodel.HSSFComment;
  24 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
  25 import org.apache.poi.hssf.usermodel.HSSFRow;
  26 import org.apache.poi.hssf.usermodel.HSSFSheet;
  27 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  28 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  29
  30 /**
  31  * A text extractor for Excel files.
  32  * Returns the textual content of the file, suitable for
  33  *  indexing by something like Lucene, but not really
  34  *  intended for display to the user.
  35  * To turn an excel file into a CSV or similar, then see
  36  *  the XLS2CSVmra example
  37  * @see org.apache.poi.hssf.eventusermodel.examples.XLS2CSVmra
  38  */
  39 public class ExcelExtractor extends POIOLE2TextExtractor {
  40         private HSSFWorkbook wb;
  41         private boolean includeSheetNames = true;
  42         private boolean formulasNotResults = false;
  43         private boolean includeCellComments = false;
  44
  45         public ExcelExtractor(HSSFWorkbook wb) {
  46                 super(wb);
  47                 this.wb = wb;
  48         }
  49         public ExcelExtractor(POIFSFileSystem fs) throws IOException {
  50                 this(new HSSFWorkbook(fs));
  51         }
  52
  53
  54         /**
  55          * Should sheet names be included? Default is true
  56          */
  57         public void setIncludeSheetNames(boolean includeSheetNames) {
  58                 this.includeSheetNames = includeSheetNames;
  59         }
  60         /**
  61          * Should we return the formula itself, and not
  62          *  the result it produces? Default is false
  63          */
  64         public void setFormulasNotResults(boolean formulasNotResults) {
  65                 this.formulasNotResults = formulasNotResults;
  66         }
  67         /**
  68      * Should cell comments be included? Default is true
  69      */
  70     public void setIncludeCellComments(boolean includeCellComments) {
  71         this.includeCellComments = includeCellComments;
  72     }
  73
  74         /**
  75          * Retreives the text contents of the file
  76          */
  77         public String getText() {
  78                 StringBuffer text = new StringBuffer();
  79
  80                 for(int i=0;i<wb.getNumberOfSheets();i++) {
  81                         HSSFSheet sheet = wb.getSheetAt(i);
  82                         if(sheet == null) { continue; }
  83
  84                         if(includeSheetNames) {
  85                                 String name = wb.getSheetName(i);
  86                                 if(name != null) {
  87                                         text.append(name);
  88                                         text.append("\n");
  89                                 }
  90                         }
  91
  92                         int firstRow = sheet.getFirstRowNum();
  93                         int lastRow = sheet.getLastRowNum();
  94                         for(int j=firstRow;j<=lastRow;j++) {
  95                                 HSSFRow row = sheet.getRow(j);
  96                                 if(row == null) { continue; }
  97
  98                                 // Check each cell in turn
  99                                 int firstCell = row.getFirstCellNum();
 100                                 int lastCell = row.getLastCellNum();
 101                                 for(int k=firstCell;k<lastCell;k++) {
 102                                         HSSFCell cell = row.getCell((short)k);
 103                                         boolean outputContents = false;
 104                                         if(cell == null) { continue; }
 105
 106                                         switch(cell.getCellType()) {
 107                                                 case HSSFCell.CELL_TYPE_STRING:
 108                                                         text.append(cell.getRichStringCellValue().getString());
 109                                                         outputContents = true;
 110                                                         break;
 111                                                 case HSSFCell.CELL_TYPE_NUMERIC:
 112                                                         // Note - we don't apply any formatting!
 113                                                         text.append(cell.getNumericCellValue());
 114                                                         outputContents = true;
 115                                                         break;
 116                                                 case HSSFCell.CELL_TYPE_BOOLEAN:
 117                                                         text.append(cell.getBooleanCellValue());
 118                                                         outputContents = true;
 119                                                         break;
 120                                                 case HSSFCell.CELL_TYPE_FORMULA:
 121                                                         if(formulasNotResults) {
 122                                                                 text.append(cell.getCellFormula());
 123                                                         } else {
 124                                                                 // Try it as a string, if not as a number
 125                                                                 HSSFRichTextString str =
 126                                                                         cell.getRichStringCellValue();
 127                                                                 if(str != null && str.length() > 0) {
 128                                                                         text.append(str.toString());
 129                                                                 } else {
 130                                                                         // Try and treat it as a number
 131                                                                         double val = cell.getNumericCellValue();
 132                                                                         text.append(val);
 133                                                                 }
 134                                                         }
 135                                                         outputContents = true;
 136                                                         break;
 137                                         }
 138
 139                                         // Output the comment, if requested and exists
 140                                     HSSFComment comment = cell.getCellComment();
 141                                         if(includeCellComments && comment != null) {
 142                                             // Replace any newlines with spaces, otherwise it
 143                                             //  breaks the output
 144                                             String commentText = comment.getString().getString().replace('\n', ' ');
 145                                             text.append(" Comment by "+comment.getAuthor()+": "+commentText);
 146                                         }
 147
 148                                         // Output a tab if we're not on the last cell
 149                                         if(outputContents && k < (lastCell-1)) {
 150                                                 text.append("\t");
 151                                         }
 152                                 }
 153
 154                                 // Finish off the row
 155                                 text.append("\n");
 156                         }
 157                 }
 158
 159                 return text.toString();
 160         }
 161 }