# Poi /XSSF : Excel-Tabelle auslesen



## Plastikeimer (19. Mai 2011)

Hallo 
Ich habe folgendes Problem:
Ich möchte aus einer Excel-Tabelle im 2007er Format (.xslx) mit Hilfe der Poi-Api von Apache Daten auslesen. 
Dazu gibt es nun ja bereits genug Dokumentation. Ich habe mir einen Beispiel-Code herausgesucht, der auch prima funktioniert. Nun möchte ich allerdings die Zellinhalte in ein Array speichern. Da es keine sichtbaren Zellzugriffe gibt, und zudem die Iteratoren einem die Spurensuche erschweren, ist es nicht so leicht, sich einfach die Position der Zelle zurückgeben zu lassen, bzw. an der Position der Zelle auch in das zweidimensionale Array zu schreiben.  Mir würde ein solcher Zugriff, der mir die Position der Zelle verrät helfen.  Alternativ würde mir die Einstellung helfen, dass Poi leere Zellen nicht ignoriert. Ich habe nämlich Zähler eingebaut, die an den entsprechenden Stellen zählen, sodass man, solange alle Zellen befüllt sind, die Zellinhalte auch korrekt in das Array einspeichern kann. Das Problem tritt dann auf, wenn Zellen, Reihen oder Spalten fehlen, dann ist es nämlich so, dass der Zähler nicht zählen kann, da der Iterator, der über die Spalten/Zeilen läuft, diese sofort überspringt.
Wenn sich hier jemand mit POI/Xssf auskennt, und mir einen Hinweis gibt, wäre ich seeeehr dankbar !

LG
Plastikeimer



```
import java.io.File; 
  import java.io.FileInputStream; 
  import java.io.FileOutputStream; 
  import java.io.IOException; 
  import java.io.InputStream; 
  import java.io.PrintStream; 
   
  import javax.xml.parsers.ParserConfigurationException; 
  import javax.xml.parsers.SAXParser; 
  import javax.xml.parsers.SAXParserFactory; 
   
  import org.apache.poi.openxml4j.exceptions.OpenXML4JException; 
  import org.apache.poi.openxml4j.opc.OPCPackage; 
  import org.apache.poi.openxml4j.opc.PackageAccess; 
  import org.apache.poi.ss.usermodel.BuiltinFormats; 
  import org.apache.poi.ss.usermodel.DataFormatter; 
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
  import org.apache.poi.xssf.model.StylesTable; 
  import org.apache.poi.xssf.usermodel.XSSFCellStyle; 
  import org.apache.poi.xssf.usermodel.XSSFRichTextString; 
  import org.xml.sax.Attributes; 
  import org.xml.sax.ContentHandler; 
  import org.xml.sax.InputSource; 
  import org.xml.sax.SAXException; 
  import org.xml.sax.XMLReader; 
import org.xml.sax.helpers.DefaultHandler; 
   
  /**
  
   */ 
  public class XLSX2CSV { 
   
     
      public static String[] Stringarray=new String[9999];
      public static int Stringarraypos=0;
      /**
       * The type of the data value is indicated by an attribute on the cell.
       * The value is usually in a "v" element within the cell.
       */ 
      enum xssfDataType { 
          BOOL, 
          ERROR, 
          FORMULA, 
          INLINESTR, 
          SSTINDEX, 
          NUMBER, 
      } 
  int countrows =0; 
   
      /**
       * Derived from [url=http://poi.apache.org/spreadsheet/how-to.html]The New Halloween Document[/url] xssf_sax_api
       * <p/>
       * Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at
       * [url=http://www.ecma-international.org/publications/standards/Ecma-376.htm]Standard ECMA-376[/url]
       * <p/>
       * A web-friendly version is [url]http://openiso.org/Ecma/376/Part4[/url]
       */ 
      class MyXSSFSheetHandler extends DefaultHandler { 
   
          /**
           * Table with styles
           */ 
          private StylesTable stylesTable; 
   
          /**
           * Table with unique strings
           */ 
          private ReadOnlySharedStringsTable sharedStringsTable; 
   
          /**
           * Destination for data
           */ 
          private final PrintStream output; 
   
          /**
           * Number of columns to read starting with leftmost
           */ 
          private final int minColumnCount; 
   
          // Set when V start element is seen 
          private boolean vIsOpen; 
   
          // Set when cell start element is seen; 
          // used when cell close element is seen. 
          private xssfDataType nextDataType; 
   
          // Used to format numeric cell values. 
          private short formatIndex; 
          private String formatString; 
          private final DataFormatter formatter; 
   
          private int thisColumn = -1; 
          // The last column printed to the output stream 
          private int lastColumnNumber = -1; 
   
          // Gathers characters as they are seen. 
          private StringBuffer value; 
   
          /**
           * Accepts objects needed while parsing.
           *
           * @param styles  Table of styles
           * @param strings Table of shared strings
           * @param cols    Minimum number of columns to show
           * @param target  Sink for output
           */ 
          public MyXSSFSheetHandler( 
                  StylesTable styles, 
                  ReadOnlySharedStringsTable strings, 
                  int cols, 
                  PrintStream target) { 
              this.stylesTable = styles; 
              this.sharedStringsTable = strings; 
              this.minColumnCount = cols; 
              this.output = target; 
              this.value = new StringBuffer(); 
              this.nextDataType = xssfDataType.NUMBER; 
              this.formatter = new DataFormatter(); 
          } 
   
          /*
             * (non-Javadoc)
             * @see org.xml.sax.helpers.DefaultHandler startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
             */ 
          public void startElement(String uri, String localName, String name, 
                                   Attributes attributes) throws SAXException { 
   
              if ("inlineStr".equals(name) || "v".equals(name)) { 
                  vIsOpen = true; 
                  // Clear contents cache 
                  value.setLength(0); 
              } 
              // c => cell 
              else if ("c".equals(name)) { 
                  // Get the cell reference 
                  String r = attributes.getValue("r"); 
                  int firstDigit = -1; 
                  for (int c = 0; c < r.length(); ++c) { 
                      if (Character.isDigit(r.charAt(c))) { 
                          firstDigit = c; 
                          break; 
                      } 
                  } 
                  thisColumn = nameToColumn(r.substring(0, firstDigit)); 
   
                  // Set up defaults. 
                  this.nextDataType = xssfDataType.NUMBER; 
                  this.formatIndex = -1; 
                  this.formatString = null; 
                  String cellType = attributes.getValue("t"); 
                  String cellStyleStr = attributes.getValue("s"); 
                  if ("b".equals(cellType)) 
                      nextDataType = xssfDataType.BOOL; 
                  else if ("e".equals(cellType)) 
                      nextDataType = xssfDataType.ERROR; 
                  else if ("inlineStr".equals(cellType)) 
                      nextDataType = xssfDataType.INLINESTR; 
                  else if ("s".equals(cellType)) 
                      nextDataType = xssfDataType.SSTINDEX; 
                  else if ("str".equals(cellType)) 
                      nextDataType = xssfDataType.FORMULA; 
                  else if (cellStyleStr != null) { 
                      // It's a number, but almost certainly one 
                      //  with a special style or format  
                      int styleIndex = Integer.parseInt(cellStyleStr); 
                      XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); 
                      this.formatIndex = style.getDataFormat(); 
                      this.formatString = style.getDataFormatString(); 
                      if (this.formatString == null) 
                          this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex); 
                  } 
              } 
   
          } 
   
          /*
             * (non-Javadoc)
             * @see org.xml.sax.helpers.DefaultHandler endElement(java.lang.String, java.lang.String, java.lang.String)
             */ 
          public void endElement(String uri, String localName, String name) 
                  throws SAXException { 
   
              String thisStr = null; 
   
              // v => contents of a cell 
              if ("v".equals(name)) { 
                  // Process the value contents as required. 
                  // Do now, as characters() may be called more than once 
                  switch (nextDataType) { 
   
                      case BOOL: 
                          char first = value.charAt(0); 
                          thisStr = first == '0' ? "FALSE" : "TRUE"; 
                          break; 
   
                      case ERROR: 
                          thisStr = "\"ERROR:" + value.toString() + '"'; 
                          break; 
   
                      case FORMULA: 
                          // A formula could result in a string value, 
                          // so always add double-quote characters. 
                          thisStr = '"' + value.toString() + '"'; 
                          break; 
   
                      case INLINESTR: 
                          // TODO: have seen an example of this, so it's untested. 
                          XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); 
                          thisStr = '"' + rtsi.toString() + '"'; 
                          break; 
   
                      case SSTINDEX: 
                          String sstIndex = value.toString(); 
                          try { 
                              int idx = Integer.parseInt(sstIndex); 
                              XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)); 
                              thisStr = '"' + rtss.toString() + '"'; 
                          } 
                          catch (NumberFormatException ex) { 
                              output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString()); 
                          } 
                          break; 
   
                      case NUMBER: 
                          String n = value.toString(); 
                          if (this.formatString != null) 
                              thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString); 
                          else 
                              thisStr = n; 
                          break; 
   
                      default: 
                          thisStr = "(TODO: Unexpected type: " + nextDataType + ")"; 
                          break; 
                  } 
   
                  // Output after we've seen the string contents 
                  // Emit commas for any fields that were missing on this row 
                  if (lastColumnNumber == -1) { 
                      lastColumnNumber = 0; 
                  } 
                  for (int i = lastColumnNumber; i < thisColumn; ++i) 
                      output.print(','); 
   
                  // Might be the empty string. 
                  output.print(thisStr);
                  Stringarray[Stringarraypos++]=thisStr;
   
                  // Update column 
                  if (thisColumn > -1) 
                      lastColumnNumber = thisColumn; 
   
              } else if ("row".equals(name)) { 
   
                  // Print out any missing commas if needed 
                  if (minColumns > 0) { 
                      // Columns are 0 based 
                      if (lastColumnNumber == -1) { 
                          lastColumnNumber = 0; 
                      } 
                      for (int i = lastColumnNumber; i < (this.minColumnCount); i++) { 
                          output.print(','); 
                      } 
                  } 
   
                  // We're onto a new row 
                   
                  output.println(); 
                  output.println(countrows++); 
                  lastColumnNumber = -1; 
                   
              } 
   
          } 
   
          /**
           * Captures characters only if a suitable element is open.
           * Originally was just "v"; extended for inlineStr also.
           */ 
          public void characters(char[] ch, int start, int length) 
                  throws SAXException { 
              if (vIsOpen) 
                  value.append(ch, start, length); 
          } 
   
          /**
           * Converts an Excel column name like "C" to a zero-based index.
           *
           * @param name
           * @return Index corresponding to the specified name
           */ 
          private int nameToColumn(String name) { 
              int column = -1; 
              for (int i = 0; i < name.length(); ++i) { 
                  int c = name.charAt(i); 
                  column = (column + 1) * 26 + c - 'A'; 
              } 
              return column; 
          } 
   
      } 
   
      /////////////////////////////////////// 
   
      private OPCPackage xlsxPackage; 
      private int minColumns; 
      private PrintStream output; 
   
      /**
       * Creates a new XLSX -> CSV converter
       *
       * @param pkg        The XLSX package to process
       * @param output     The PrintStream to output the CSV to
       * @param minColumns The minimum number of columns to output, or -1 for no minimum
       */ 
      public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns) { 
          this.xlsxPackage = pkg; 
          this.output = output; 
          this.minColumns = minColumns; 
      } 
   
      /**
       * Parses and shows the content of one sheet
       * using the specified styles and shared-strings tables.
       *
       * @param styles
       * @param strings
       * @param sheetInputStream
       */ 
      public void processSheet( 
              StylesTable styles, 
              ReadOnlySharedStringsTable strings, 
              InputStream sheetInputStream) 
              throws IOException, ParserConfigurationException, SAXException { 
   
          InputSource sheetSource = new InputSource(sheetInputStream); 
          SAXParserFactory saxFactory = SAXParserFactory.newInstance(); 
          SAXParser saxParser = saxFactory.newSAXParser(); 
          XMLReader sheetParser = saxParser.getXMLReader(); 
          ContentHandler handler = new MyXSSFSheetHandler(styles, strings, this.minColumns, this.output); 
          sheetParser.setContentHandler(handler); 
          sheetParser.parse(sheetSource); 
      } 
   
      /**
       * Initiates the processing of the XLS workbook file to CSV.
       *
       * @throws IOException
       * @throws OpenXML4JException
       * @throws ParserConfigurationException
       * @throws SAXException
       */ 
      public void process() 
              throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { 
   
          ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage); 
          XSSFReader xssfReader = new XSSFReader(this.xlsxPackage); 
       
          StylesTable styles = xssfReader.getStylesTable(); 
          XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); 
          int index = 0; 
          while (iter.hasNext()) { 
              InputStream stream = iter.next(); 
              String sheetName = iter.getSheetName();  
              this.output.println(); 
              this.output.println(sheetName + " [index=" + index + "]:"); 
              processSheet(styles, strings, stream); 
              stream.close(); 
              ++index; 
          } 
      } 
   
      public static void main(String[] args) throws Exception { 
         /* if (args.length < 1) {
              System.err.println("Use:");
              System.err.println("  XLSX2CSV <xlsx file> [min columns]");
              return;
          }*/ 
   
          //File xlsxFile = new File(args[0]); 
          File xlsxFile = new File("src/bla.xlsx"); 
          if (!xlsxFile.exists()) { 
              System.err.println("Not found or not a file: " + xlsxFile.getPath()); 
              return; 
          } 
   
          int minColumns = -1; 
          //if (args.length >= 2) 
              //minColumns = Integer.parseInt(args[1]); 
   
           
          //File originFile = new File("c:\\excel\\file1.txt"); 
          File destinationFile = new File("src/bla.txt"); 
      
          try { 
      
            //FileInputStream fis = new FileInputStream(originFile); 
            FileOutputStream fos = new FileOutputStream(destinationFile); 
   
   
       
      
            fos.close(); 
          } catch (IOException e) { 
            System.out.println(e); 
          } 
           
   
              minColumns = 2; 
          // The package open is instantaneous, as it should be. 
          OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ); 
          XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns); 
          xlsx2csv.process(); 
         
         
          for (int i=0; i<Stringarray.length; i++){
             
              if(Stringarray[i]!=null)
              System.out.println(Stringarray[i]);
             
          }
      } 
   
  }
```


----------



## c_sidi90 (23. Mai 2011)

Kannst du deine Frage ein wenig verdeutlichen mit weniger Text  dann helf ich dir gerne


----------



## c_sidi90 (23. Mai 2011)

Also falls du einfach vorhast das Sheet in ein Array zu speichern (Alle Spalten mit Ihren Zellen und Werten) Kannst du das am besten so lösen.


```
FileInputStream myInput = new FileInputStream("deinedatei.xls");
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myWorkbook = new HSSFWorkbook(myFileSystem);
            HSSFSheet mySheet = myWorkbook.getSheetAt(kategorieBox.getSelectedIndex()+1);
            Iterator rowIter = mySheet.rowIterator();
            Object data[][] = new Object[50][50]; //In diesem Array speicherst du die Werte (50 maximal) zum testen
            
         
          while(rowIter.hasNext()){ //Solange es weitere Spalten gibt
            	
            	HSSFRow row = (HSSFRow) rowIter.next(); //Erzeuge ein Objekt der aktuellen Spalte
            	Iterator cellIter = row.cellIterator(); //Iterator der durch alle Zellen in der Spalte iteriert.
            	
            	while(cellIter.hasNext()){ //Solange es mehr Zellen in dieser Spalte gibt:
            		HSSFCell cell = (HSSFCell) cellIter.next(); //Erzeugt ein Objekt der aktuellen Zelle
            		data[cell.getRowIndex()][cell.getColumnIndex()] = cell.toString(); //Speichert die Werte an der Stelle an der Sie auch im Excelsheet sind in das Array.
            	}
            	
            //Dieses 2D Object-Array könntest du z.B. an ein TableModel übergeben um die Struktur darstellen zu lassen, wie sie auch im Excelsheet aufgebaut ist.
            	
            }
```


----------

