001package org.hl7.fhir.utilities.xls;
002
003/*
004  Copyright (c) 2011+, HL7, Inc.
005  All rights reserved.
006  
007  Redistribution and use in source and binary forms, with or without modification, 
008  are permitted provided that the following conditions are met:
009    
010   * Redistributions of source code must retain the above copyright notice, this 
011     list of conditions and the following disclaimer.
012   * Redistributions in binary form must reproduce the above copyright notice, 
013     this list of conditions and the following disclaimer in the documentation 
014     and/or other materials provided with the distribution.
015   * Neither the name of HL7 nor the names of its contributors may be used to 
016     endorse or promote products derived from this software without specific 
017     prior written permission.
018  
019  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND 
020  ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED 
021  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
022  IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
023  INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
024  NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
025  PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, 
026  WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
027  ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
028  POSSIBILITY OF SUCH DAMAGE.
029  
030 */
031
032
033
034import java.io.File;
035import java.io.FileInputStream;
036import java.io.FileOutputStream;
037import java.io.IOException;
038import java.io.InputStream;
039
040import javax.xml.parsers.DocumentBuilder;
041import javax.xml.parsers.DocumentBuilderFactory;
042import javax.xml.parsers.ParserConfigurationException;
043import javax.xml.transform.Result;
044import javax.xml.transform.Source;
045import javax.xml.transform.Transformer;
046import javax.xml.transform.TransformerException;
047import javax.xml.transform.TransformerFactory;
048import javax.xml.transform.dom.DOMSource;
049import javax.xml.transform.stream.StreamResult;
050
051import lombok.extern.slf4j.Slf4j;
052import org.hl7.fhir.exceptions.FHIRException;
053import org.hl7.fhir.utilities.FileUtilities;
054import org.hl7.fhir.utilities.Utilities;
055import org.hl7.fhir.utilities.filesystem.ManagedFileAccess;
056import org.hl7.fhir.utilities.xml.XMLUtil;
057import org.w3c.dom.Document;
058import org.w3c.dom.Element;
059import org.w3c.dom.Node;
060import org.xml.sax.SAXException;
061
062@Slf4j
063public class XLSXmlNormaliser {
064  
065  private static final String XLS_NS = "urn:schemas-microsoft-com:office:spreadsheet";
066
067  private Document xml;
068
069  private String source;
070  private String dest;
071  private boolean exceptionIfExcelNotNormalised;
072  
073  public XLSXmlNormaliser(String source, String dest, boolean exceptionIfExcelNotNormalised) {
074    super();
075    this.source = source;
076    this.dest = dest;
077    this.exceptionIfExcelNotNormalised = exceptionIfExcelNotNormalised;
078  }
079  
080  public XLSXmlNormaliser(String source, boolean exceptionIfExcelNotNormalised) {
081    super();
082    this.source = source;
083    this.dest = source;
084    this.exceptionIfExcelNotNormalised = exceptionIfExcelNotNormalised;
085  }
086  
087  public void go() throws FHIRException, TransformerException, ParserConfigurationException, SAXException, IOException {
088    File inp = ManagedFileAccess.file(source);
089    long time = inp.lastModified();
090    xml = parseXml(ManagedFileAccess.inStream(inp));
091    
092    Element root = xml.getDocumentElement();
093
094    boolean hasComment = false;
095    Node n = root.getFirstChild();
096    while (n != null) {
097      if (n.getNodeType() == Node.COMMENT_NODE && "canonicalized".equals(n.getTextContent())) {
098        hasComment = true;
099        break;
100      }
101      n = n.getNextSibling();
102    }
103    if (hasComment)
104      return;
105    if (exceptionIfExcelNotNormalised)
106      throw new FHIRException("The spreadsheet "+dest+" was committed after editing in excel, but before the build could run *after Excel was closed*");
107    
108    log.info("normalise: "+source);
109    
110    XMLUtil.deleteByName(root, "ActiveSheet");
111    Element xw = XMLUtil.getNamedChild(root, "ExcelWorkbook");
112    XMLUtil.deleteByName(xw, "WindowHeight");
113    XMLUtil.deleteByName(xw, "WindowWidth");
114    XMLUtil.deleteByName(xw, "WindowTopX");
115    XMLUtil.deleteByName(xw, "WindowTopY");
116
117    for (Element wk : XMLUtil.getNamedChildren(root, "Worksheet"))
118      processWorksheet(wk);
119    
120    if (!hasComment)
121      root.appendChild(xml.createComment("canonicalized"));
122    try {
123      FileOutputStream fs = ManagedFileAccess.outStream(dest);
124      try {
125        saveXml(fs);
126      } finally {
127        fs.close();
128      }
129      String s = FileUtilities.fileToString(dest);
130      s = s.replaceAll("\r\n","\n");
131      s = replaceSignificantEoln(s);
132      FileUtilities.stringToFile(s, dest);
133      ManagedFileAccess.file(dest).setLastModified(time);
134    } catch (Exception e) {
135      log.error("The file "+dest+" is still open in Excel, and you will have to run the build after closing Excel before committing");
136    }
137  }
138
139  private String replaceSignificantEoln(String s) {
140    StringBuilder b = new StringBuilder();
141    boolean hasText = false;
142    for (char c : s.toCharArray()) {
143      if (c == '>' || c == '<' ) {
144        hasText = false;
145        b.append(c);
146      } else if (c == '\n') {
147        if (hasText) {
148          b.append("&#10;");
149        } else
150          b.append(c);
151        
152      } else if (!Character.isWhitespace(c)) {
153        b.append(c);
154        hasText = true;
155      } else 
156        b.append(c);
157    }
158    
159    return b.toString();
160  }
161
162  private void processWorksheet(Element wk) throws FHIRException  {
163    Element tbl = XMLUtil.getNamedChild(wk, "Table");
164    processTable(tbl);
165    for (Element row : XMLUtil.getNamedChildren(tbl, "Row"))
166      processRow(row);      
167    for (Element col : XMLUtil.getNamedChildren(tbl, "Column"))
168      processCol(col);      
169    for (Element wo : XMLUtil.getNamedChildren(wk, "WorksheetOptions"))
170      processOptions(wo);      
171  }
172  
173  private void processOptions(Element wo) {
174    XMLUtil.deleteByName(wo, "Unsynced");
175    XMLUtil.deleteByName(wo, "Panes");
176    for (Element panes : XMLUtil.getNamedChildren(wo, "Panes"))
177      processPanes(panes);      
178  }
179
180  private void processPanes(Element panes) {
181    for (Element pane : XMLUtil.getNamedChildren(panes, "Pane"))
182      processPane(pane);        
183  }
184
185  private void processPane(Element pane) {
186    XMLUtil.deleteByName(pane, "ActiveRow");
187    XMLUtil.deleteByName(pane, "ActiveCol");    
188  }
189
190//  private void setTextElement(Element e, String name, String text) {
191//    Element te = XMLUtil.getNamedChild(e, name);
192//    if (te != null)
193//      te.setTextContent(text);
194//  }
195
196  private void processTable(Element col) {
197    XMLUtil.deleteAttr(col, "urn:schemas-microsoft-com:office:spreadsheet", "DefaultColumnWidth");
198    XMLUtil.deleteAttr(col, "urn:schemas-microsoft-com:office:spreadsheet", "DefaultRowHeight");
199  }
200
201
202  private void processCol(Element col) {
203    String width = col.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Width");
204    if (!Utilities.noString(width)) {
205      Double d = Double.valueOf(width);
206      width = Double.toString(Math.round(d*2)/2);
207      col.setAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "ss:Width", width);
208    }        
209  }
210
211  private void processRow(Element row) {
212    String height = row.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Height");
213    if (!Utilities.noString(height) && height.contains(".")) {
214      Double d = Double.valueOf(height);
215      row.setAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "ss:Height", Long.toString(Math.round(d)));
216    }    
217  }
218
219  private void check(boolean test, String message) throws FHIRException  {
220    if (!test)
221      throw new FHIRException(message+" in "+getLocation());
222  }
223  
224
225  private Document parseXml(InputStream in) throws FHIRException, ParserConfigurationException, SAXException, IOException  {
226    DocumentBuilderFactory factory = XMLUtil.newXXEProtectedDocumentBuilderFactory();
227    factory.setNamespaceAware(true);
228    DocumentBuilder builder = factory.newDocumentBuilder();
229    return builder.parse(in);
230  }
231
232  private void saveXml(FileOutputStream stream) throws TransformerException, IOException {
233
234    TransformerFactory factory = XMLUtil.newXXEProtectedTransformerFactory();
235    Transformer transformer = factory.newTransformer();
236    Result result = new StreamResult(stream);
237    Source source = new DOMSource(xml);
238    transformer.transform(source, result);    
239    stream.flush();
240  }
241
242  private String getLocation() {
243    return source; //+", row "+rowIndex.toString();
244  }
245
246
247}