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 org.hl7.fhir.exceptions.FHIRException;
052import org.hl7.fhir.utilities.TextFile;
053import org.hl7.fhir.utilities.Utilities;
054import org.hl7.fhir.utilities.filesystem.ManagedFileAccess;
055import org.hl7.fhir.utilities.xml.XMLUtil;
056import org.w3c.dom.Document;
057import org.w3c.dom.Element;
058import org.w3c.dom.Node;
059import org.xml.sax.SAXException;
060
061public class XLSXmlNormaliser {
062  
063  private static final String XLS_NS = "urn:schemas-microsoft-com:office:spreadsheet";
064
065  private Document xml;
066
067  private String source;
068  private String dest;
069  private boolean exceptionIfExcelNotNormalised;
070  
071  public XLSXmlNormaliser(String source, String dest, boolean exceptionIfExcelNotNormalised) {
072    super();
073    this.source = source;
074    this.dest = dest;
075    this.exceptionIfExcelNotNormalised = exceptionIfExcelNotNormalised;
076  }
077  
078  public XLSXmlNormaliser(String source, boolean exceptionIfExcelNotNormalised) {
079    super();
080    this.source = source;
081    this.dest = source;
082    this.exceptionIfExcelNotNormalised = exceptionIfExcelNotNormalised;
083  }
084  
085  public void go() throws FHIRException, TransformerException, ParserConfigurationException, SAXException, IOException {
086    File inp = ManagedFileAccess.file(source);
087    long time = inp.lastModified();
088    xml = parseXml(ManagedFileAccess.inStream(inp));
089    
090    Element root = xml.getDocumentElement();
091
092    boolean hasComment = false;
093    Node n = root.getFirstChild();
094    while (n != null) {
095      if (n.getNodeType() == Node.COMMENT_NODE && "canonicalized".equals(n.getTextContent())) {
096        hasComment = true;
097        break;
098      }
099      n = n.getNextSibling();
100    }
101    if (hasComment)
102      return;
103    if (exceptionIfExcelNotNormalised)
104      throw new FHIRException("The spreadsheet "+dest+" was committed after editing in excel, but before the build could run *after Excel was closed*");
105    
106    System.out.println("normalise: "+source);
107    
108    XMLUtil.deleteByName(root, "ActiveSheet");
109    Element xw = XMLUtil.getNamedChild(root, "ExcelWorkbook");
110    XMLUtil.deleteByName(xw, "WindowHeight");
111    XMLUtil.deleteByName(xw, "WindowWidth");
112    XMLUtil.deleteByName(xw, "WindowTopX");
113    XMLUtil.deleteByName(xw, "WindowTopY");
114
115    for (Element wk : XMLUtil.getNamedChildren(root, "Worksheet"))
116      processWorksheet(wk);
117    
118    if (!hasComment)
119      root.appendChild(xml.createComment("canonicalized"));
120    try {
121      FileOutputStream fs = ManagedFileAccess.outStream(dest);
122      try {
123        saveXml(fs);
124      } finally {
125        fs.close();
126      }
127      String s = TextFile.fileToString(dest);
128      s = s.replaceAll("\r\n","\n");
129      s = replaceSignificantEoln(s);
130      TextFile.stringToFile(s, dest);
131      ManagedFileAccess.file(dest).setLastModified(time);
132    } catch (Exception e) {
133      System.out.println("The file "+dest+" is still open in Excel, and you will have to run the build after closing Excel before committing");
134    }
135  }
136
137  private String replaceSignificantEoln(String s) {
138    StringBuilder b = new StringBuilder();
139    boolean hasText = false;
140    for (char c : s.toCharArray()) {
141      if (c == '>' || c == '<' ) {
142        hasText = false;
143        b.append(c);
144      } else if (c == '\n') {
145        if (hasText) {
146          b.append("&#10;");
147        } else
148          b.append(c);
149        
150      } else if (!Character.isWhitespace(c)) {
151        b.append(c);
152        hasText = true;
153      } else 
154        b.append(c);
155    }
156    
157    return b.toString();
158  }
159
160  private void processWorksheet(Element wk) throws FHIRException  {
161    Element tbl = XMLUtil.getNamedChild(wk, "Table");
162    processTable(tbl);
163    for (Element row : XMLUtil.getNamedChildren(tbl, "Row"))
164      processRow(row);      
165    for (Element col : XMLUtil.getNamedChildren(tbl, "Column"))
166      processCol(col);      
167    for (Element wo : XMLUtil.getNamedChildren(wk, "WorksheetOptions"))
168      processOptions(wo);      
169  }
170  
171  private void processOptions(Element wo) {
172    XMLUtil.deleteByName(wo, "Unsynced");
173    XMLUtil.deleteByName(wo, "Panes");
174    for (Element panes : XMLUtil.getNamedChildren(wo, "Panes"))
175      processPanes(panes);      
176  }
177
178  private void processPanes(Element panes) {
179    for (Element pane : XMLUtil.getNamedChildren(panes, "Pane"))
180      processPane(pane);        
181  }
182
183  private void processPane(Element pane) {
184    XMLUtil.deleteByName(pane, "ActiveRow");
185    XMLUtil.deleteByName(pane, "ActiveCol");    
186  }
187
188//  private void setTextElement(Element e, String name, String text) {
189//    Element te = XMLUtil.getNamedChild(e, name);
190//    if (te != null)
191//      te.setTextContent(text);
192//  }
193
194  private void processTable(Element col) {
195    XMLUtil.deleteAttr(col, "urn:schemas-microsoft-com:office:spreadsheet", "DefaultColumnWidth");
196    XMLUtil.deleteAttr(col, "urn:schemas-microsoft-com:office:spreadsheet", "DefaultRowHeight");
197  }
198
199
200  private void processCol(Element col) {
201    String width = col.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Width");
202    if (!Utilities.noString(width)) {
203      Double d = Double.valueOf(width);
204      width = Double.toString(Math.round(d*2)/2);
205      col.setAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "ss:Width", width);
206    }        
207  }
208
209  private void processRow(Element row) {
210    String height = row.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Height");
211    if (!Utilities.noString(height) && height.contains(".")) {
212      Double d = Double.valueOf(height);
213      row.setAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "ss:Height", Long.toString(Math.round(d)));
214    }    
215  }
216
217  private void check(boolean test, String message) throws FHIRException  {
218    if (!test)
219      throw new FHIRException(message+" in "+getLocation());
220  }
221  
222
223  private Document parseXml(InputStream in) throws FHIRException, ParserConfigurationException, SAXException, IOException  {
224    DocumentBuilderFactory factory = XMLUtil.newXXEProtectedDocumentBuilderFactory();
225    factory.setNamespaceAware(true);
226    DocumentBuilder builder = factory.newDocumentBuilder();
227    return builder.parse(in);
228  }
229
230  private void saveXml(FileOutputStream stream) throws TransformerException, IOException {
231
232    TransformerFactory factory = XMLUtil.newXXEProtectedTransformerFactory();
233    Transformer transformer = factory.newTransformer();
234    Result result = new StreamResult(stream);
235    Source source = new DOMSource(xml);
236    transformer.transform(source, result);    
237    stream.flush();
238  }
239
240  private String getLocation() {
241    return source; //+", row "+rowIndex.toString();
242  }
243
244
245}