org.hl7.fhir.utilities.XLSXmlParser Maven / Gradle / Ivy

Go to download
/*
Copyright (c) 2011+, HL7, Inc
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, 
are permitted provided that the following conditions are met:

 * Redistributions of source code must retain the above copyright notice, this 
   list of conditions and the following disclaimer.
 * Redistributions in binary form must reproduce the above copyright notice, 
   this list of conditions and the following disclaimer in the documentation 
   and/or other materials provided with the distribution.
 * Neither the name of HL7 nor the names of its contributors may be used to 
   endorse or promote products derived from this software without specific 
   prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND 
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED 
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, 
WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
POSSIBILITY OF SUCH DAMAGE.

*/
package org.hl7.fhir.utilities;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.hl7.fhir.utilities.xml.XMLUtil;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class XLSXmlParser {

  private static final String XLS_NS = "urn:schemas-microsoft-com:office:spreadsheet";

  public class Row extends ArrayList {  private static final long serialVersionUID = 1L; }
  
  public class Sheet {
    public String title;
    public Row columns;
    public List rows = new ArrayList();

    public boolean hasColumn(int row, String column) throws Exception {
      String s = getColumn(row, column);
      return s != null && !s.equals("");     
    }
    
    public String getColumn(int row, String column) throws Exception {
      int c = -1;
      String s = "";
      for (int i = 0; i < columns.size(); i++) {
        s = s + ","+columns.get(i);
        if (columns.get(i).equalsIgnoreCase(column))
          c = i;
      }
      if (c == -1)
        return ""; // throw new Exception("unable to find column "+column+" in "+s.substring(1));
      else if (rows.get(row).size() <= c)
        return "";
      else
        return rows.get(row).get(c).trim();
    }

    public List getRows() {
      return rows;
    }
    
    
  }
  
  private Map sheets;
  private Document xml;
  private String name;
  
  public XLSXmlParser(InputStream in, String name) throws Exception {
    this.name = name;
    xml = parseXml(in);
    sheets = new HashMap();
    readXml();
  }

  private Document parseXml(InputStream in) throws Exception {
    try {
      DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
      factory.setNamespaceAware(true);
      DocumentBuilder builder = factory.newDocumentBuilder();
      return builder.parse(in);
    } catch (Exception e) {
      throw new Exception("Error processing "+name+": "+e.getMessage(), e);
    }
  }

  private void readXml() throws Exception {
    Element root = xml.getDocumentElement();
    check(root.getNamespaceURI().equals(XLS_NS), "Spreadsheet namespace incorrect");
    check(root.getNodeName().equals("Workbook"), "Spreadsheet element name incorrect");
    Node node = root.getFirstChild();
    while (node != null) {
      if (node.getNodeName().equals("Worksheet"))
        processWorksheet((Element)node);
      node = node.getNextSibling();
    }
  }
  
  private Integer rowIndex;
  private void processWorksheet(Element node) throws Exception {
    Sheet sheet = new Sheet();
    sheet.title = node.getAttributeNS(XLS_NS, "Name");
    sheets.put(node.getAttributeNS(XLS_NS, "Name"), sheet);
    NodeList table = node.getElementsByTagNameNS(XLS_NS, "Table");
    check(table.getLength() == 1, "multiple table elements");
    NodeList rows = ((Element)table.item(0)).getElementsByTagNameNS(XLS_NS, "Row");
    if (rows.getLength() == 0) 
      return;
    rowIndex = 1;
    sheet.columns = readRow((Element) rows.item(0));
    for (int i = 1; i < rows.getLength(); i++) {
      rowIndex++;
      sheet.rows.add(readRow((Element) rows.item(i)));
    }
       
    //Remove empty rows at the end of the sheet
    while( sheet.rows.size() != 0 && isEmptyRow(sheet.rows.get(sheet.rows.size()-1) ) )
    	sheet.rows.remove(sheet.rows.size()-1);
  }

  
  private boolean isEmptyRow(Row w)
  { 
	  for( int col=0; col 0 ? res.get(0) : "?"));
      ndx++;      
    }
    return res;
  }

  private String readData(Element cell, int col, String s) throws Exception {
    List data = new ArrayList(); 
    XMLUtil.getNamedChildren(cell, "Data", data); // cell.getElementsByTagNameNS(XLS_NS, "Data");
    if (data.size() == 0)
      return "";
    check(data.size() == 1, "Multiple Data encountered ("+Integer.toString(data.size())+" @ col "+Integer.toString(col)+" - "+cell.getTextContent()+" ("+s+"))");
    Element d = data.get(0);
    String type = d.getAttributeNS(XLS_NS, "Type");
    if ("Boolean".equals(type)) {
      if (d.getTextContent().equals("1"))
        return "True";
      else
        return "False";
    } else if ("String".equals(type)) {
      return d.getTextContent();
    } else if ("Number".equals(type)) {
      return d.getTextContent();
    } else if ("DateTime".equals(type)) {
      return d.getTextContent();
    } else if ("Error".equals(type)) {
      return null;
    } else 
      throw new Exception("Cell Type is not known ("+d.getAttributeNodeNS(XLS_NS, "Type")+") in "+getLocation());
  }

  private void check(boolean test, String message) throws Exception {
    if (!test)
      throw new Exception(message+" in "+getLocation());
  }
  
  private String getLocation() {
    return name+", row "+rowIndex.toString();
  }

  public Map getSheets() {
    return sheets;
  }

  
}