title: Reading an Excel sheet using POI's XSSF and SAX (Event API)
body: <p>I am reading an Excel sheet using POI's XSSF and SAX (Event API). The Excel sheet has thousands of rows of user information like user name, email, address, age, department etc. </p>
  
  <p>I need to read each row from Excel, convert it into a User object and add this User object to a List of User objects. </p>
  
  <p>I can read the Excel sheet successfully, but I am not sure at what point while reading I should create an instance of the User object and populate it with the data from the Excel sheet.</p>
  
  <p>Below is my entire working code.</p>
  
  <pre><code>    import java.io.File;
      import java.io.FileOutputStream;
      import java.io.IOException;
      import java.io.InputStream;
      import java.io.PrintStream;
      import java.util.ArrayList;
      import java.util.List;
  
      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 ExcelSheetParser {
  
          enum xssfDataType {
              BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
          }
  
          int countrows = 0;
  
          class XSSFSheetHandler extends DefaultHandler {
  
              /**
               * Table with styles
               */
              private StylesTable stylesTable;
  
              /**
               * Table with unique strings
               */
              private ReadOnlySharedStringsTable sharedStringsTable;
  
              /**
               * Destination for data
               */
              private final PrintStream output;
  
              private List&lt;?&gt; list = new ArrayList();
  
              private Class clazz;
  
              /**
               * 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 XSSFSheetHandler(StylesTable styles,
                      ReadOnlySharedStringsTable strings, int cols, PrintStream target, Class clazz) {
                  this.stylesTable = styles;
                  this.sharedStringsTable = strings;
                  this.minColumnCount = cols;
                  this.output = target;
                  this.value = new StringBuffer();
                  this.nextDataType = xssfDataType.NUMBER;
                  this.formatter = new DataFormatter();
                  this.clazz = clazz;
              }
  
              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 =&gt; cell
                  else if ("c".equals(name)) {
                      // Get the cell reference
                      String r = attributes.getValue("r");
                      int firstDigit = -1;
                      for (int c = 0; c &lt; 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);
                      }
                  }
  
              }
  
              public void endElement(String uri, String localName, String name)
                      throws SAXException {
  
                  String thisStr = null;
  
                  // v =&gt; 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 &lt; thisColumn; ++i)
                          output.print(',');
  
                      // Might be the empty string.
                      output.print(thisColumn +" : "+thisStr);
  
                      // Update column
                      if (thisColumn &gt; -1)
                          lastColumnNumber = thisColumn;
  
                  } else if ("row".equals(name)) {
  
                      // Print out any missing commas if needed
                      if (minColumns &gt; 0) {
                          // Columns are 0 based
                          if (lastColumnNumber == -1) {
                              lastColumnNumber = 0;
                          }
                          for (int i = lastColumnNumber; i &lt; (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 &lt; 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;
          private Class clazz;
  
          /**
           * Creates a new XLSX -&gt; 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 ExcelSheetParser(OPCPackage pkg, PrintStream output, int minColumns, Class clazz) {
              this.xlsxPackage = pkg;
              this.output = output;
              this.minColumns = minColumns;
              this.clazz = clazz;
  
          }
  
          /**
           * 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 XSSFSheetHandler(styles, strings,
                      this.minColumns, this.output, this.clazz);
              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(sheetName + " [index=" + index + "]:");
                  processSheet(styles, strings, stream);
                  stream.close();
                  ++index;
              }
          }
      }
  </code></pre>