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