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