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(" "); 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}