Skip to Content

Wiki source code of Development-Excel Generation

Last modified by Pascal Robert on 2010/09/13 00:35
Show last authors
1 For a "clean" copy of this file, head over to [[http:~~/~~/www.overhyped.com./downloads/OTCPOIArchive.java>>url:http://www.overhyped.com./downloads/OTCPOIArchive.java||shape="rect"]]
2
3 {{code}}
4
5 /*
6 Copyright (c) 2003 Overhyped Technologies, LLC.
7
8 Permission is hereby granted, free of charge, to any person obtaining a copy
9 of this software and associated documentation files (the "Software"), to deal
10 in the Software without restriction, including without limitation the rights
11 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
12 copies of the Software, and to permit persons to whom the Software is
13 furnished to do so, subject to the following conditions: The above copyright
14 notice and this permission notice shall be included in all copies or
15 substantial portions of the Software.
16
17 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
18 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
19 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
20 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
21 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
22 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
23 SOFTWARE. */
24
25 package com.overhyped.webobjects;
26
27 import com.webobjects.foundation.*;
28 import com.webobjects.appserver.*;
29 import com.webobjects.eocontrol.*;
30 import com.webobjects.eoaccess.*;
31 import com.webobjects.directtoweb.*;
32
33 import java.io.*;
34 import java.util.Date;
35
36 import org.apache.poi.hssf.usermodel.*;
37
38 public class OTCPOIArchive extends Object {
39 protected D2WContext d2wContext;
40 protected EOEnterpriseObject object;
41 static String _fileUploadPath = null;
42
43 /** Returns the current D2WContext. */
44 public D2WContext d2wContext() {
45 return d2wContext;
46 }
47 /** Sets the current D2WContext. */
48 public void setLocalContext(D2WContext d2w) {
49 d2wContext = d2w;
50 }
51
52 /** Returns the current EOEnterpriseObject. */
53 public EOEnterpriseObject object() {
54 return object;
55 }
56 /** Sets the current EOEnterpriseObject. */
57 public void setObject(EOEnterpriseObject newEO) {
58 object = newEO;
59 }
60
61 /** Creates an Excel Spreadsheet using Apache's POI project. If the objectArray
62 * is null or empty, this method will fetch all objects from the database for
63 * the given entityNameForArchive. If the entityNameForArchive is null or
64 * empty, this method will fetch all entities and iterate over them, while
65 * fetching all objects for each entityName. This method will use aKey to set
66 * an Integer into the target, allowing for feedback while the calculation is
67 * occurring. The resulting file can be found in the /excel/ directory of the
68 * WebServer. */
69 public String calculate(NSArray objectArray, String entityNameForArchive, String aKey, WOComponent target) {
70
71 try {
72 EOEditingContext ec;
73 HSSFWorkbook wb;
74 NSArray entities;
75 java.util.Enumeration entityEnum, attributeEnum, eoEnum;
76 int sCount;
77 String eName;
78 HSSFSheet sheet;
79 NSArray attributes;
80 short col;
81 HSSFRow row;
82 String attribute, attribute2, value;
83 short r;
84 EOEnterpriseObject item;
85
86 // set up the d2wcontext
87 if (d2wContext() == null) setLocalContext( new D2WContext(target.session()) );
88 // set the task for archive
89 d2wContext().setTask("archive");
90 // get an EditingContext
91 ec = target.session().defaultEditingContext();
92 // create a workbook
93 wb = new HSSFWorkbook();
94 // get the entities
95 if ((entityNameForArchive == null) || (entityNameForArchive.length() == 0)) {
96 entities = D2WUtils.allEntities();
97 objectArray = null; // if there's no entity name, we'd better clear out any objects.
98 }
99 else {
100 entities = new NSArray(EOUtilities.entityNamed(ec,entityNameForArchive));
101 }
102 // create an enumerator
103 entityEnum = entities.objectEnumerator();
104 // count the sheets
105 sCount = 0;
106 // enumerate
107 while (entityEnum.hasMoreElements()) {
108 // get the next entity
109 eName = ((EOEntity)entityEnum.nextElement()).name();
110 // set the entity into the D2WContext
111 d2wContext().setEntity(EOUtilities.entityNamed(ec, eName));
112 // get all objects for this entity
113 if ((objectArray == null) || (objectArray.count() == 0)) objectArray = EOUtilities.objectsForEntityNamed(ec, eName);
114 // check if there are any objects
115 if ((objectArray == null) || (objectArray.count() == 0)) continue;
116 // create a worksheet
117 sheet = wb.createSheet(displayName());
118 // get the propertyKeys for this entity
119 attributes = (NSArray) d2wContext().valueForKey("displayPropertyKeys");
120 // Label the columns
121 attributeEnum = attributes.objectEnumerator();
122 // zero out the column count
123 col = 0;
124 // Create a row and put some cells in it. Rows are 0 based.
125 row = sheet.createRow((short)0);
126 // iterate the attributes
127 while ( attributeEnum.hasMoreElements() ) {
128 // get the next attribute
129 attribute = (String) attributeEnum.nextElement();
130 // set the property into the D2WContext
131 d2wContext().setPropertyKey(attribute);
132 // insert it at the appropriate column
133 // add the cell to the worksheet
134 row.createCell(col).setCellValue(d2wContext().displayNameForProperty());
135 // move to the next column
136 col++;
137 }
138 // Insert the Rows
139 // make sure we start at row 1, otherwise we'll lose the column headings
140 r = 1;
141 // set up to iterate the EOs
142 eoEnum = objectArray.objectEnumerator();
143 // iterate the EOs
144 while ( eoEnum.hasMoreElements() ) {
145 try {
146 // zero out the column count
147 col = 0;
148 // create a new row
149 row = sheet.createRow(r);
150 // an EO
151 item = (EOEnterpriseObject) eoEnum.nextElement();
152 // set it into the D2W page
153 setObject(item);
154 // set up to iterate the attributes again...
155 attributeEnum = attributes.objectEnumerator();
156 // iterate the attributes again...
157 while ( attributeEnum.hasMoreElements() ) {
158 // the attribute
159 attribute2 = (String) attributeEnum.nextElement();
160 // set the property into the D2WContext
161 d2wContext().setPropertyKey(attribute2);
162 try {
163 // the final value to insert from the current object based on the d2wcontext
164 value = currentValue();
165 // create the cell for the spreadsheet
166 row.createCell(col).setCellValue(value);
167 }
168 catch (Exception e1) {
169 System.err.println(e1);
170 }
171 // move over a column
172 col++;
173 }
174 }
175 catch (Exception e2) {
176 System.err.println(e2);
177 }
178 // move down a row, since there's a new object
179 r++;
180 target.takeValueForKey(new Integer(r),aKey);
181 }
182 // add another worksheet to the spreadsheet, since there's another entity
183 sCount++;
184 // clear out the objectArray, since there's another entity
185 objectArray = null;
186
187 // System.out.println("POIArchive: calculate: Completed Archive: " + eName);
188 }
189 // All sheets and cells added. Now write out the workbook
190 String fn = fileUploadPath() + File.separator + "POI" + new NSTimestamp().getTime() + ".xls";
191 File file = new File(fn);
192 FileOutputStream fileOut = new FileOutputStream(file);
193 wb.write(fileOut);
194 fileOut.close();
195
196 objectArray = null;
197 entityNameForArchive = null;
198
199 System.out.println("POI @ " + fn);
200 return fn;
201 }
202 catch (Exception e) {
203 ByteArrayOutputStream baos = new ByteArrayOutputStream();
204 e.printStackTrace(new PrintStream(baos));
205 System.out.println("POIArchive: archive(): error:" + baos.toString());
206 return null;
207 }
208 }
209
210 /** Returns the current value provided by the d2wContext. Found objects use
211 * the d2wContext for timestamp formatting, relationships, etc. If a
212 * relationship fo an EOEnterpriseObject is resolved, and no keyForRelationship
213 * is found, this method will use the EO's userPresentableDescription. */
214 public String currentValue() {
215 Object o = null;
216 try {
217 if (d2wContext().propertyKeyIsKeyPath()) {
218 o = NSKeyValueCodingAdditions.Utility.valueForKeyPath(object(), d2wContext().propertyKey());
219 } else {
220 o = NSKeyValueCoding.DefaultImplementation.valueForKey(object(), d2wContext().propertyKey());
221 }
222 if ( (o != null) && (o instanceof EOEnterpriseObject) ) {
223 o = ((EOEnterpriseObject) o).valueForKey(d2wContext().keyWhenRelationship());
224 if ( (o != null) && (o instanceof EOEnterpriseObject) ) {
225 try {
226 return ((EOEnterpriseObject)o).userPresentableDescription();
227 }
228 catch (Exception ex) {
229 ByteArrayOutputStream baos = new ByteArrayOutputStream();
230 ex.printStackTrace(new PrintStream(baos));
231 System.out.println("POIArchive: currentValue(): error" + baos.toString());
232 return o.toString();
233 }
234 }
235 }
236 if ( (o != null) && (o instanceof NSArray) ) {
237 /***** ENHANCE ME: could use componentsSeparatedByString along with userPresentableDescription. jpaul. *****/
238 int count;
239 count = ((NSArray) o).count();
240 if (count == 0) return "0";
241 return " " + count;
242 }
243 if ( (o != null) && (o instanceof NSTimestamp) ) {
244 NSTimestampFormatter formatter = new NSTimestampFormatter((String) d2wContext().valueForKey(D2WModel.FormatterKey));
245 return formatter.format((NSTimestamp) o);
246 }
247 if ( (o != null) && (o instanceof java.lang.Number) ) {
248 NSNumberFormatter formatter;
249 String pattern = (String) d2wContext().valueForKey(D2WModel.FormatterKey);
250 if ((pattern != null) && (pattern.length() > 0)) {
251 formatter = new NSNumberFormatter(pattern);
252 }
253 else {
254 formatter = new NSNumberFormatter();
255 }
256 return formatter.stringForObjectValue(o);
257 }
258 if (o != null) return o.toString();
259 return "";
260 }
261 catch (Exception e) {
262 return "(Binary Data)";
263 }
264 }
265
266 /** Returns an EOEnterpriseObject's 'displayNameForEntity', using the d2wContext.
267 * If it is not found, the method will check the userInfoDictionary for
268 * displayName. If all else fails, it will return the entity's name. */
269 public String displayName() {
270 String s;
271
272 s = (String) d2wContext().valueForKey("displayNameForEntity");
273 if (s == null) {
274 s = (String) d2wContext().entity().userInfo().objectForKey("displayName");
275 if (s != null) return s;
276 return (String) d2wContext().entity().name();
277 }
278 return s;
279 }
280
281 /**
282 * Returns the directory where uploaded files will be written to disk.
283 */
284 public static String fileUploadPath() {
285 if (_fileUploadPath == null) {
286 String tmpdir = System.getProperty("OTCUploadDirectory");
287 if (tmpdir != null) {
288 File tmpPath = new File(tmpdir);
289 if (tmpPath.exists()) {
290 _fileUploadPath = tmpPath.getAbsolutePath();
291 }
292 }
293 if (_fileUploadPath == null) {
294 NSLog.err.appendln("FileUpload: 'OTCUploadDirectory' does not exist."
295 + " Please launch this application again with the 'OTCUploadDirectory'"
296 + " System Property set to a directory to which you have write permission.");
297 }
298 }
299 return _fileUploadPath;
300 }
301 }
302
303 {{/code}}