1
2
3
4
5
6
7 package org.wiztools.xml2spreadsheet;
8
9 import org.wiztools.xml2spreadsheet.entity.CellEntity;
10 import org.wiztools.xml2spreadsheet.entity.RowEntity;
11 import org.wiztools.xml2spreadsheet.entity.SheetEntity;
12 import org.wiztools.xml2spreadsheet.entity.WorkBookEntity;
13 import org.wiztools.xml2spreadsheet.exception.XML2XLSFatalException;
14 import org.wiztools.xml2spreadsheet.exception.XMLInvalidNestedElementException;
15 import org.wiztools.xml2spreadsheet.exception.XMLSyntaxException;
16 import java.io.IOException;
17 import java.io.InputStream;
18 import java.text.ParseException;
19 import java.text.SimpleDateFormat;
20 import java.util.Calendar;
21 import java.util.Date;
22 import java.util.HashMap;
23 import java.util.List;
24 import java.util.Map;
25 import org.jdom.Document;
26 import org.jdom.Element;
27 import org.jdom.JDOMException;
28 import org.jdom.input.SAXBuilder;
29
30 /***
31 *
32 * @author subhash
33 */
34 public class XML2XLSGenerator {
35
36 private WorkBookGenerationHandler wbgh;
37
38 /*** Creates a new instance of XML2XLSGenerator */
39 public XML2XLSGenerator() {
40 }
41
42 private int getIntFromStr(final String str)
43 throws IllegalArgumentException{
44 try{
45 int i = Integer.parseInt(str);
46 if(i<0){
47 throw new IllegalArgumentException(
48 "Illegal attribute value: "+i);
49 }
50 return i;
51 } catch(NumberFormatException nfe){
52 throw new IllegalArgumentException(
53 "Illegal attribute value: "+str);
54 }
55 }
56
57 private Date getDate(final String date, final String format)
58 throws XML2XLSFatalException{
59 SimpleDateFormat sdf = new SimpleDateFormat(format);
60 try{
61 return sdf.parse(date);
62 } catch(ParseException pe){
63 throw new XML2XLSFatalException(
64 "Date format ("+format+") conversion failed: "+date, pe);
65 }
66 }
67
68 private Calendar getCalendar(final String date, final String format)
69 throws XML2XLSFatalException{
70 Date dt = getDate(date, format);
71 Calendar cal = Calendar.getInstance();
72 cal.setTime(dt);
73 return cal;
74 }
75
76 private Document getDocument(final InputStream is)
77 throws IOException, XML2XLSFatalException{
78 SAXBuilder builder = new SAXBuilder();
79 Document doc;
80 try{
81 doc = builder.build(is);
82 } catch(JDOMException je){
83 throw new XML2XLSFatalException("", je);
84 }
85 return doc;
86 }
87
88 private void processCell(final Element ecell)
89 throws XML2XLSFatalException{
90 List<Element> l = ecell.getChildren();
91 if(l.size()>1){
92 throw new XMLSyntaxException(
93 "<cell> can have only one child element: "
94 + "text|number|formula");
95 }
96 for(Element e: l){
97 String name = e.getName();
98 CellEntity cell = new CellEntity();
99 if("text".equals(name)){
100 wbgh.setCellValue(e.getText());
101 } else if("number".equals(name)){
102 String doubleStr = e.getTextTrim();
103 double d = -1;
104 try{
105 d = Double.parseDouble(doubleStr);
106 } catch(NumberFormatException nfe){
107 throw new XML2XLSFatalException(
108 "<number> cell value is not valid double: "
109 + doubleStr, nfe);
110 }
111 wbgh.setCellValue(d);
112 } else if("formula".equals(name)){
113 String formula = e.getTextTrim();
114 if(formula == null || "".equals(formula)){
115 throw new XML2XLSFatalException(
116 "element <formula> cannot be empty");
117 }
118 wbgh.setCellFormula(formula);
119 } else if("date".equals(name)){
120 String format = e.getAttributeValue("format");
121 if(format == null){
122 throw new XML2XLSFatalException(
123 "<date> element should have `format'"
124 + " attribute specified");
125 }
126 wbgh.setCellValue(getDate(e.getTextTrim(), format));
127 } else if("calendar".equals(name)){
128 String format = e.getAttributeValue("format");
129 if(format == null){
130 throw new XML2XLSFatalException(
131 "<date> element should have `format'"
132 + " attribute specified");
133 }
134 wbgh.setCellValue(getCalendar(e.getTextTrim(), format));
135 } else if("boolean".equals(name)){
136 String value = e.getTextTrim();
137 boolean bolVal;
138 if("true".equals(value)){
139 bolVal = true;
140 } else if("false".equals(value)){
141 bolVal = false;
142 } else{
143 throw new XML2XLSFatalException(
144 "<boolean> cell can have only true/false as value. "
145 + "The present value: " + value);
146 }
147 wbgh.setCellValue(bolVal);
148 } else{
149 throw new XMLInvalidNestedElementException("cell", name);
150 }
151 }
152 }
153
154 private void processRow(final Element erow)
155 throws XML2XLSFatalException{
156 List<Element> l = erow.getChildren();
157 for(Element e: l){
158 String name = e.getName();
159 if("cell".equals(name)){
160 String placementStr = e.getAttributeValue("placement");
161 int placement = getIntFromStr(placementStr);
162 CellEntity cell = new CellEntity();
163 String styleVal = e.getAttributeValue("style");
164 if(styleVal != null){
165 Map<String, String> attributes = new HashMap<String, String>();
166 attributes.put("style", styleVal);
167 cell.setAttributes(attributes);
168 }
169 wbgh.createCell(cell, (short)placement);
170 processCell(e);
171 } else{
172 throw new XMLInvalidNestedElementException("row", name);
173 }
174 }
175 }
176
177 private void processMergeRegion(final Element emerge)
178 throws XML2XLSFatalException{
179 String reg = emerge.getAttributeValue("region");
180 if(reg == null){
181 throw new XML2XLSFatalException(
182 "<merge> element should have `region' attribute");
183 }
184 String[] arr = reg.split("//s*,//s*");
185 if(arr.length != 4){
186 throw new XML2XLSFatalException(
187 "region attribute for <merge> should have the "
188 + "format: N,N,N,N: "+reg);
189 }
190 int[] regions = new int[4];
191 for(int i=0;i<arr.length;i++){
192 int tmp = -1;
193 try{
194 regions[i] = Integer.parseInt(arr[i]);
195 if(regions[i]<0){
196 throw new XML2XLSFatalException(
197 "region attribute cannot have -ve value: "+reg);
198 }
199 } catch(NumberFormatException nfe){
200 throw new XML2XLSFatalException(
201 "region attribute has non-number value: "+reg);
202 }
203 }
204 wbgh.mergeCells(regions[0],
205 (short)regions[1],
206 regions[2],
207 (short)regions[3]);
208 }
209
210 private void processColumnWidth(final Element e)
211 throws XML2XLSFatalException{
212 String columnStr = e.getAttributeValue("column");
213 String widthStr = e.getAttributeValue("width");
214 if(columnStr == null || widthStr == null){
215 throw new XML2XLSFatalException(
216 "<column-width> element should have both the attributes: column and width");
217 }
218 short column = -1;
219 short width = -1;
220 try{
221 column = Short.parseShort(columnStr);
222 width = Short.parseShort(widthStr);
223 if(column < 0 || width < 0){
224 throw new XML2XLSFatalException("column or width attribute cannot be negative");
225 }
226 } catch(NumberFormatException nfe){
227 throw new XML2XLSFatalException("column and width attribute should be numbers");
228 }
229 wbgh.setColumnWidth(column, width);
230 }
231
232 private void processSheet(final Element esheet)
233 throws XML2XLSFatalException{
234 List<Element> l = esheet.getChildren();
235 for(Element e: l){
236 String name = e.getName();
237 if("row".equals(name)){
238 String placementStr = e.getAttributeValue("placement");
239 int placement = getIntFromStr(placementStr);
240 RowEntity row = new RowEntity();
241 wbgh.createRow(row, placement);
242 processRow(e);
243 } else if("merge".equals(name)){
244 processMergeRegion(e);
245 } else if("column-width".equals(name)){
246 processColumnWidth(e);
247 } else{
248 throw new XMLInvalidNestedElementException("sheet", name);
249 }
250 }
251 }
252
253 public void parse(final WorkBookGenerationHandler wbgh, final InputStream is)
254 throws IOException, XML2XLSFatalException{
255 this.wbgh = wbgh;
256 Document doc = getDocument(is);
257 Element root = doc.getRootElement();
258 if(!"workbook".equals(root.getName())){
259 throw new XMLSyntaxException("Root element should be <workbook>");
260 }
261 WorkBookEntity workBook = new WorkBookEntity();
262 wbgh.createWorkBook(workBook);
263 List<Element> l = root.getChildren();
264 for(Element e: l){
265 String name = e.getName();
266 if("sheet".equals(name)){
267 SheetEntity sheet = new SheetEntity();
268 String namea = e.getAttributeValue("name");
269 if(namea!=null && !"".equals(namea)){
270 Map<String, String> m = new HashMap<String, String>();
271 m.put("name", namea);
272 sheet.setAttributes(m);
273 wbgh.createSheet(sheet);
274 } else{
275 wbgh.createSheet(sheet);
276 }
277 processSheet(e);
278 } else{
279 throw new XMLInvalidNestedElementException("workbook", name);
280 }
281 }
282 }
283 }
284