1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 package org.openoffice.test.common; 22 23 import java.io.File; 24 import java.text.SimpleDateFormat; 25 import java.util.Date; 26 27 import org.w3c.dom.Document; 28 import org.w3c.dom.Element; 29 import org.w3c.dom.NodeList; 30 31 /** 32 * The class can be used to output data into an Microsoft Excel 2003 XML file. 33 * The file can be opened with OpenOffice or MS Office. 34 * 35 */ 36 public class DataSheet { 37 private File file = null; 38 private Document doc = null; 39 private Element workBookEl = null; 40 private static final SimpleDateFormat DATEFORMAT = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS"); 41 DataSheet(File file)42 public DataSheet(File file) { 43 this(file, false); 44 } 45 DataSheet(File file, boolean append)46 public DataSheet(File file, boolean append) { 47 this.file = file; 48 49 if (append && file.exists()) { 50 doc = FileUtil.parseXML(file.getAbsolutePath()); 51 if (doc != null) { 52 NodeList nodes = doc.getElementsByTagName("Workbook"); 53 if (nodes.getLength() > 0) { 54 workBookEl = (Element) nodes.item(0); 55 return; 56 } 57 } 58 } 59 60 doc = FileUtil.newXML(); 61 workBookEl = doc.createElement("Workbook"); 62 workBookEl.setAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet"); 63 workBookEl.setAttribute("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet"); 64 doc.appendChild(workBookEl); 65 // Element stylesEl = doc.createElement("Styles"); 66 // workBookEl.appendChild(stylesEl); 67 // Element styleEl = doc.createElement("Style"); 68 // stylesEl.appendChild(styleEl); 69 // styleEl.setAttribute("ss:ID", "ce1"); 70 // Element numberFormatEl = doc.createElement("NumberFormat"); 71 // styleEl.appendChild(numberFormatEl); 72 // numberFormatEl.setAttribute("ss:Format", "General Date"); 73 74 } 75 76 getTableElement(String sheetName)77 private Element getTableElement(String sheetName) { 78 NodeList nodes = workBookEl.getElementsByTagName("Worksheet"); 79 for (int i = 0; i < nodes.getLength(); i++) { 80 Element e = (Element) nodes.item(0); 81 if (sheetName.equals(e.getAttribute("ss:Name"))){ 82 return (Element) e.getElementsByTagName("Table").item(0); 83 } 84 } 85 86 Element worksheetEl = doc.createElement("Worksheet"); 87 worksheetEl.setAttribute("ss:Name", sheetName); 88 workBookEl.appendChild(worksheetEl); 89 Element tableEl = doc.createElement("Table"); 90 worksheetEl.appendChild(tableEl); 91 return tableEl; 92 } 93 addRow(String sheetName, Object... datas)94 public void addRow(String sheetName, Object... datas) { 95 Element tableEl = getTableElement(sheetName); 96 Element rowEl = doc.createElement("Row"); 97 for (Object o : datas) { 98 Element cellEl = doc.createElement("Cell"); 99 rowEl.appendChild(cellEl); 100 Element dataEl = doc.createElement("Data"); 101 cellEl.appendChild(dataEl); 102 if (o instanceof Number) { 103 dataEl.setAttribute("ss:Type", "Number"); 104 dataEl.setTextContent(o.toString()); 105 } else if (o instanceof Boolean) { 106 dataEl.setAttribute("ss:Type", "Boolean"); 107 dataEl.setTextContent(o.toString()); 108 } else if (o instanceof Date) { 109 dataEl.setAttribute("ss:Type", "String"); 110 dataEl.setTextContent(DATEFORMAT.format((Date) o)); 111 } else { 112 dataEl.setAttribute("ss:Type", "String"); 113 dataEl.setTextContent(o == null ? "" : o.toString()); 114 } 115 } 116 117 tableEl.appendChild(rowEl); 118 FileUtil.storeXML(doc, file); 119 } 120 } 121