Source code

001/*
002Copyright (c) 2011+, HL7, Inc
003All rights reserved.
004
005Redistribution and use in source and binary forms, with or without modification, 
006are permitted provided that the following conditions are met:
007
008 * Redistributions of source code must retain the above copyright notice, this 
009   list of conditions and the following disclaimer.
010 * Redistributions in binary form must reproduce the above copyright notice, 
011   this list of conditions and the following disclaimer in the documentation 
012   and/or other materials provided with the distribution.
013 * Neither the name of HL7 nor the names of its contributors may be used to 
014   endorse or promote products derived from this software without specific 
015   prior written permission.
016
017THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND 
018ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED 
019WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
020IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
021INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
022NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
023PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, 
024WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
025ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
026POSSIBILITY OF SUCH DAMAGE.
027
028*/
029package org.hl7.fhir.utilities.xls;
030
031/*-
032 * #%L
033 * org.hl7.fhir.utilities
034 * %%
035 * Copyright (C) 2014 - 2019 Health Level 7
036 * %%
037 * Licensed under the Apache License, Version 2.0 (the "License");
038 * you may not use this file except in compliance with the License.
039 * You may obtain a copy of the License at
040 * 
041 *      http://www.apache.org/licenses/LICENSE-2.0
042 * 
043 * Unless required by applicable law or agreed to in writing, software
044 * distributed under the License is distributed on an "AS IS" BASIS,
045 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
046 * See the License for the specific language governing permissions and
047 * limitations under the License.
048 * #L%
049 */
050
051
052import java.io.InputStream;
053import java.util.ArrayList;
054import java.util.HashMap;
055import java.util.List;
056import java.util.Map;
057
058import javax.xml.parsers.DocumentBuilder;
059import javax.xml.parsers.DocumentBuilderFactory;
060
061import org.hl7.fhir.exceptions.FHIRException;
062import org.hl7.fhir.utilities.Utilities;
063import org.hl7.fhir.utilities.xml.XMLUtil;
064import org.w3c.dom.DOMException;
065import org.w3c.dom.Document;
066import org.w3c.dom.Element;
067import org.w3c.dom.Node;
068import org.w3c.dom.NodeList;
069
070public class XLSXmlParser {
071
072  private static final String XLS_NS = "urn:schemas-microsoft-com:office:spreadsheet";
073
074  public class Row extends ArrayList<String> {  private static final long serialVersionUID = 1L; }
075  
076  public class Sheet {
077    public String title;
078    public Row columns;
079    public List<Row> rows = new ArrayList<Row>();
080
081    public boolean hasColumn(String column)  {
082      for (int i = 0; i < columns.size(); i++) {
083        if (columns.get(i).equalsIgnoreCase(column))
084          return true;
085      }
086      return false;
087    }
088    
089    public boolean hasColumn(int row, String column)  {
090      String s = getColumn(row, column);
091      return s != null && !s.equals("");     
092    }
093    
094    public String getColumn(int row, String column)  {
095      int c = -1;
096      String s = "";
097      for (int i = 0; i < columns.size(); i++) {
098        s = s + ","+columns.get(i);
099        if (columns.get(i).equalsIgnoreCase(column))
100          c = i;
101      }
102      if (c == -1)
103        return ""; // throw new FHIRException("unable to find column "+column+" in "+s.substring(1));
104      else if (rows.get(row).size() <= c)
105        return "";
106      else {
107        s = rows.get(row).get(c); 
108        return s == null ? "" : s.trim().replace("\t",  "  ").replace("\u00A0", " ");
109      }
110    }
111
112    public List<String> getColumnNamesBySuffix(String suffix)  {
113      List<String> names = new ArrayList<String>();
114      for (int i = 0; i < columns.size(); i++) {
115        if (columns.get(i).endsWith(suffix))
116          names.add(columns.get(i));
117      }
118      return names;
119    }
120
121    public String getByColumnPrefix(int row, String column)  {
122      int c = -1;
123      String s = "";
124      for (int i = 0; i < columns.size(); i++) {
125        s = s + ","+columns.get(i);
126        if (columns.get(i).startsWith(column))
127          c = i;
128      }
129      if (c == -1)
130        return ""; // throw new FHIRException("unable to find column "+column+" in "+s.substring(1));
131      else if (rows.get(row).size() <= c)
132        return "";
133      else
134        return rows.get(row).get(c).trim();
135    }
136
137    public List<Row> getRows() {
138      return rows;
139    }
140
141    public int getIntColumn(int row, String column)  {
142      String value = getColumn(row, column);
143      if (Utilities.noString(value))
144        return 0;
145      else
146        return Integer.parseInt(value);
147    }
148
149    public String getNonEmptyColumn(int row, String column) throws FHIRException  {
150     String value = getColumn(row, column);
151     if (Utilities.noString(value))
152       throw new FHIRException("The colummn "+column+" cannot be empty");
153     return value;
154    }
155
156    public boolean hasColumnContent(String col) {
157      int i = columns.indexOf(col);
158      if (i == -1)
159        return false;
160      for (Row r : rows) {
161        if (r.size() > i && !Utilities.noString(r.get(i)))
162          return true;
163      }
164      return false;
165    }
166    
167    
168  }
169  
170  private Map<String, Sheet> sheets;
171  private Document xml;
172  private String name;
173  
174  public XLSXmlParser(InputStream in, String name) throws FHIRException  {
175    this.name = name;
176    try {
177      xml = parseXml(in);
178      sheets = new HashMap<String, Sheet>();
179      readXml();
180    } catch (Exception e) {
181      throw new FHIRException("unable to load "+name+": "+e.getMessage(), e);
182    }
183  }
184
185  private Document parseXml(InputStream in) throws FHIRException  {
186    try {
187      DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
188      factory.setNamespaceAware(true);
189      DocumentBuilder builder = factory.newDocumentBuilder();
190      return builder.parse(in);
191    } catch (Exception e) {
192      throw new FHIRException("Error processing "+name+": "+e.getMessage(), e);
193    }
194  }
195
196  private void readXml() throws FHIRException  {
197    Element root = xml.getDocumentElement();
198    check(root.getNamespaceURI().equals(XLS_NS), "Spreadsheet namespace incorrect");
199    check(root.getNodeName().equals("Workbook"), "Spreadsheet element name incorrect");
200    Node node = root.getFirstChild();
201    while (node != null) {
202      if (node.getNodeName().equals("Worksheet"))
203        processWorksheet((Element)node);
204      node = node.getNextSibling();
205    }
206  }
207  
208  private Integer rowIndex;
209  private void processWorksheet(Element node) throws FHIRException  {
210    Sheet sheet = new Sheet();
211    sheet.title = node.getAttributeNS(XLS_NS, "Name");
212    sheets.put(node.getAttributeNS(XLS_NS, "Name"), sheet);
213    NodeList table = node.getElementsByTagNameNS(XLS_NS, "Table");
214    check(table.getLength() == 1, "multiple table elements");
215    NodeList rows = ((Element)table.item(0)).getElementsByTagNameNS(XLS_NS, "Row");
216    if (rows.getLength() == 0) 
217      return;
218    rowIndex = 1;
219    sheet.columns = readRow((Element) rows.item(0));
220    for (int i = 1; i < rows.getLength(); i++) {
221      rowIndex++;
222      sheet.rows.add(readRow((Element) rows.item(i)));
223    }
224       
225    //Remove empty rows at the end of the sheet
226    while( sheet.rows.size() != 0 && isEmptyRow(sheet.rows.get(sheet.rows.size()-1) ) )
227        sheet.rows.remove(sheet.rows.size()-1);
228  }
229
230  
231  private boolean isEmptyRow(Row w)
232  { 
233          for( int col=0; col<w.size(); col++ )
234                  if( !w.get(col).trim().isEmpty() ) return false;
235          
236          return true;
237  }
238  
239  private Row readRow(Element row) throws DOMException, FHIRException  {
240    Row res = new Row();
241    int ndx = 1;    
242    NodeList cells = row.getElementsByTagNameNS(XLS_NS, "Cell");
243    for (int i = 0; i < cells.getLength(); i++) {
244      Element cell = (Element) cells.item(i);
245      if (cell.hasAttributeNS(XLS_NS, "Index")) {
246        int index = Integer.parseInt(cell.getAttributeNS(XLS_NS, "Index"));
247        while (ndx < index) {
248          res.add("");
249          ndx++;
250        }
251      }
252      res.add(readData(cell, ndx, res.size() > 0 ? res.get(0) : "?"));
253      ndx++;      
254    }
255    return res;
256  }
257
258  private String readData(Element cell, int col, String s) throws DOMException, FHIRException  {
259    List<Element> data = new ArrayList<Element>(); 
260    XMLUtil.getNamedChildren(cell, "Data", data); // cell.getElementsByTagNameNS(XLS_NS, "Data");
261    if (data.size() == 0)
262      return "";
263    check(data.size() == 1, "Multiple Data encountered ("+Integer.toString(data.size())+" @ col "+Integer.toString(col)+" - "+cell.getTextContent()+" ("+s+"))");
264    Element d = data.get(0);
265    String type = d.getAttributeNS(XLS_NS, "Type");
266    if ("Boolean".equals(type)) {
267      if (d.getTextContent().equals("1"))
268        return "True";
269      else
270        return "False";
271    } else if ("String".equals(type)) {
272      return d.getTextContent();
273    } else if ("Number".equals(type)) {
274      return d.getTextContent();
275    } else if ("DateTime".equals(type)) {
276      return d.getTextContent();
277    } else if ("Error".equals(type)) {
278      return null;
279    } else 
280      throw new FHIRException("Cell Type is not known ("+d.getAttributeNodeNS(XLS_NS, "Type")+") in "+getLocation());
281  }
282
283  private void check(boolean test, String message) throws FHIRException  {
284    if (!test)
285      throw new FHIRException(message+" in "+getLocation());
286  }
287  
288  private String getLocation() {
289    return name+", row "+rowIndex.toString();
290  }
291
292  public Map<String, Sheet> getSheets() {
293    return sheets;
294  }
295
296  
297}