001package org.hl7.fhir.utilities.xls; 002 003/*- 004 * #%L 005 * org.hl7.fhir.utilities 006 * %% 007 * Copyright (C) 2014 - 2019 Health Level 7 008 * %% 009 * Licensed under the Apache License, Version 2.0 (the "License"); 010 * you may not use this file except in compliance with the License. 011 * You may obtain a copy of the License at 012 * 013 * http://www.apache.org/licenses/LICENSE-2.0 014 * 015 * Unless required by applicable law or agreed to in writing, software 016 * distributed under the License is distributed on an "AS IS" BASIS, 017 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 018 * See the License for the specific language governing permissions and 019 * limitations under the License. 020 * #L% 021 */ 022 023 024import java.io.File; 025import java.io.FileInputStream; 026import java.io.FileOutputStream; 027import java.io.IOException; 028import java.io.InputStream; 029 030import javax.xml.parsers.DocumentBuilder; 031import javax.xml.parsers.DocumentBuilderFactory; 032import javax.xml.parsers.ParserConfigurationException; 033import javax.xml.transform.Result; 034import javax.xml.transform.Source; 035import javax.xml.transform.Transformer; 036import javax.xml.transform.TransformerException; 037import javax.xml.transform.TransformerFactory; 038import javax.xml.transform.dom.DOMSource; 039import javax.xml.transform.stream.StreamResult; 040 041import org.hl7.fhir.exceptions.FHIRException; 042import org.hl7.fhir.utilities.TextFile; 043import org.hl7.fhir.utilities.Utilities; 044import org.hl7.fhir.utilities.xml.XMLUtil; 045import org.w3c.dom.Document; 046import org.w3c.dom.Element; 047import org.w3c.dom.Node; 048import org.xml.sax.SAXException; 049 050public class XLSXmlNormaliser { 051 052 private static final String XLS_NS = "urn:schemas-microsoft-com:office:spreadsheet"; 053 054 private Document xml; 055 056 private String source; 057 private String dest; 058 private boolean exceptionIfExcelNotNormalised; 059 060 public XLSXmlNormaliser(String source, String dest, boolean exceptionIfExcelNotNormalised) { 061 super(); 062 this.source = source; 063 this.dest = dest; 064 this.exceptionIfExcelNotNormalised = exceptionIfExcelNotNormalised; 065 } 066 067 public XLSXmlNormaliser(String source, boolean exceptionIfExcelNotNormalised) { 068 super(); 069 this.source = source; 070 this.dest = source; 071 this.exceptionIfExcelNotNormalised = exceptionIfExcelNotNormalised; 072 } 073 074 public void go() throws FHIRException, TransformerException, ParserConfigurationException, SAXException, IOException { 075 File inp = new File(source); 076 long time = inp.lastModified(); 077 xml = parseXml(new FileInputStream(inp)); 078 079 Element root = xml.getDocumentElement(); 080 081 boolean hasComment = false; 082 Node n = root.getFirstChild(); 083 while (n != null) { 084 if (n.getNodeType() == Node.COMMENT_NODE && "canonicalized".equals(n.getTextContent())) { 085 hasComment = true; 086 break; 087 } 088 n = n.getNextSibling(); 089 } 090 if (hasComment) 091 return; 092 if (exceptionIfExcelNotNormalised) 093 throw new FHIRException("The spreadsheet "+dest+" was committed after editing in excel, but before the build could run *after Excel was closed*"); 094 095 System.out.println("normalise: "+source); 096 097 XMLUtil.deleteByName(root, "ActiveSheet"); 098 Element xw = XMLUtil.getNamedChild(root, "ExcelWorkbook"); 099 XMLUtil.deleteByName(xw, "WindowHeight"); 100 XMLUtil.deleteByName(xw, "WindowWidth"); 101 XMLUtil.deleteByName(xw, "WindowTopX"); 102 XMLUtil.deleteByName(xw, "WindowTopY"); 103 104 for (Element wk : XMLUtil.getNamedChildren(root, "Worksheet")) 105 processWorksheet(wk); 106 107 if (!hasComment) 108 root.appendChild(xml.createComment("canonicalized")); 109 try { 110 saveXml(new FileOutputStream(dest)); 111 String s = TextFile.fileToString(dest); 112 s = s.replaceAll("\r\n","\n"); 113 s = replaceSignificantEoln(s); 114 TextFile.stringToFile(s, dest, false); 115 new File(dest).setLastModified(time); 116 } catch (Exception e) { 117 System.out.println("The file "+dest+" is still open in Excel, and you will have to run the build after closing Excel before committing"); 118 } 119 } 120 121 private String replaceSignificantEoln(String s) { 122 StringBuilder b = new StringBuilder(); 123 boolean hasText = false; 124 for (char c : s.toCharArray()) { 125 if (c == '>' || c == '<' ) { 126 hasText = false; 127 b.append(c); 128 } else if (c == '\n') { 129 if (hasText) { 130 b.append(" "); 131 } else 132 b.append(c); 133 134 } else if (!Character.isWhitespace(c)) { 135 b.append(c); 136 hasText = true; 137 } else 138 b.append(c); 139 } 140 141 return b.toString(); 142 } 143 144 private void processWorksheet(Element wk) throws FHIRException { 145 Element tbl = XMLUtil.getNamedChild(wk, "Table"); 146 processTable(tbl); 147 for (Element row : XMLUtil.getNamedChildren(tbl, "Row")) 148 processRow(row); 149 for (Element col : XMLUtil.getNamedChildren(tbl, "Column")) 150 processCol(col); 151 for (Element wo : XMLUtil.getNamedChildren(wk, "WorksheetOptions")) 152 processOptions(wo); 153 } 154 155 private void processOptions(Element wo) { 156 XMLUtil.deleteByName(wo, "Unsynced"); 157 XMLUtil.deleteByName(wo, "Panes"); 158 for (Element panes : XMLUtil.getNamedChildren(wo, "Panes")) 159 processPanes(panes); 160 } 161 162 private void processPanes(Element panes) { 163 for (Element pane : XMLUtil.getNamedChildren(panes, "Pane")) 164 processPane(pane); 165 } 166 167 private void processPane(Element pane) { 168 XMLUtil.deleteByName(pane, "ActiveRow"); 169 XMLUtil.deleteByName(pane, "ActiveCol"); 170 } 171 172// private void setTextElement(Element e, String name, String text) { 173// Element te = XMLUtil.getNamedChild(e, name); 174// if (te != null) 175// te.setTextContent(text); 176// } 177 178 private void processTable(Element col) { 179 XMLUtil.deleteAttr(col, "urn:schemas-microsoft-com:office:spreadsheet", "DefaultColumnWidth"); 180 XMLUtil.deleteAttr(col, "urn:schemas-microsoft-com:office:spreadsheet", "DefaultRowHeight"); 181 } 182 183 184 private void processCol(Element col) { 185 String width = col.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Width"); 186 if (!Utilities.noString(width)) { 187 Double d = Double.valueOf(width); 188 width = Double.toString(Math.round(d*2)/2); 189 col.setAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "ss:Width", width); 190 } 191 } 192 193 private void processRow(Element row) { 194 String height = row.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Height"); 195 if (!Utilities.noString(height) && height.contains(".")) { 196 Double d = Double.valueOf(height); 197 row.setAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "ss:Height", Long.toString(Math.round(d))); 198 } 199 } 200 201 private void check(boolean test, String message) throws FHIRException { 202 if (!test) 203 throw new FHIRException(message+" in "+getLocation()); 204 } 205 206 207 private Document parseXml(InputStream in) throws FHIRException, ParserConfigurationException, SAXException, IOException { 208 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); 209 factory.setNamespaceAware(true); 210 DocumentBuilder builder = factory.newDocumentBuilder(); 211 return builder.parse(in); 212 } 213 214 private void saveXml(FileOutputStream stream) throws TransformerException, IOException { 215 216 TransformerFactory factory = TransformerFactory.newInstance(); 217 Transformer transformer = factory.newTransformer(); 218 Result result = new StreamResult(stream); 219 Source source = new DOMSource(xml); 220 transformer.transform(source, result); 221 stream.flush(); 222 } 223 224 private String getLocation() { 225 return source; //+", row "+rowIndex.toString(); 226 } 227 228 229}