xlSheet


1   /*(Header: NiLOSTEP / xlSQL)
2   
3    Copyright (C) 2004 NiLOSTEP
4      NiLOSTEP Information Sciences
5      http://nilostep.com
6      nilo.de.roock@nilostep.com
7   
8    This program is free software; you can redistribute it and/or modify it under 
9    the terms of the GNU General Public License as published by the Free Software 
10   Foundation; either version 2 of the License, or (at your option) any later 
11   version.
12  
13   This program is distributed in the hope that it will be useful, 
14   but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
15   or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for 
16   more details. You should have received a copy of the GNU General Public License 
17   along with this program; if not, write to the Free Software Foundation, 
18   Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19  */
20  package com.nilostep.xlsql.database.excel;
21  
22  import com.nilostep.xlsql.database.*;
23  import com.nilostep.xlsql.sql.*;
24  
25  import java.io.*;
26  
27  import java.sql.*;
28  
29  import java.text.*;
30  
31  import java.util.*;
32  import java.util.logging.*;
33  
34  import jxl.*;
35  
36  import jxl.write.*;
37  
38  
39  /**
40   * xlSheet represents a single sheet in an Excel workbook.
41   * 
42   * @version $Revision: 1.12 $
43   * @author $author$
44   */
45  public class xlSheet extends xlFile {
46      private static final String   XLS = ".xls";
47  
48      /**
49       * Creates a new xlSheet object.
50       * 
51       * @param dir (relative) root directory contains excel files
52       * @param folder name of workbook without extension
53       * @param name sheet identifier as in excel
54       * 
55       * @throws xlException when this object cannot be instantiated
56       */
57      xlSheet(File dir, String   folder, String   name) throws xlException {
58          super(dir, folder, name);
59      }
60  
61      /**
62       * Creates a new xlSheet object.
63       * 
64       * @param dir (relative) root directory contains excel files
65       * @param folder name of workbook without extension
66       * @param name sheet identifier as in excel
67       * @param bdirty DOCUMENT ME!
68       */
69      xlSheet(File dir, String   folder, String   name, boolean bdirty) {
70          super(dir, folder, name, bdirty);
71      }
72  
73      private jxl.Sheet getSheet() throws xlException {
74          // COST! ( Performance problems?? Better fix JXL.ow first. :-) )
75          Sheet ret = null;
76  
77          try {
78              File f = new File(directory.getPath() + File.separator + 
79                                subFolderName + XLS);
80              ret = Workbook.getWorkbook(f).getSheet(fileName);
81          } catch (IOException ioe) {
82              throw new xlException("xlSQL: -excel> io ERR: " + 
83                                    ioe.getMessage());
84          } catch (jxl.read.biff.BiffException biffe) {
85              throw new xlException("xlSQL: -excel> biff ERR: " + 
86                                    biffe.getMessage());
87          }
88  
89          //return workBook;
90          return ret;
91      }
92  
93      /**
94       * Close a sheet and flush changes in the sql engine.
95       * 
96       * @param subOut DOCUMENT ME!
97       * @param select DOCUMENT ME!
98       * 
99       * @throws xlException DOCUMENT ME!
100      */
101     public void close(Object   subOut, xlSqlSelect select)
102                throws xlException {
103         Sheet workSheet;
104         ResultSet rs;
105 
106         WritableWorkbook wbOut = (WritableWorkbook) subOut;
107 
108         if (isChanged[xlConstants.ADD]) {
109             workSheet = wbOut.createSheet(fileName, wbOut.getNumberOfSheets());
110 
111             WritableSheet wsh = (WritableSheet) workSheet;
112 
113             try {
114                 rs = select.QueryData(subFolderName, fileName);
115             } catch (SQLException sqe) {
116                 throw new xlException(sqe.getMessage());
117             }
118 
119 
120             //Create new empty sheet
121             write(wsh, rs);
122         } else if (isChanged[xlConstants.UPDATE]) {
123             // Remove sheet by name
124             int i;
125             WritableSheet _s;
126 
127             for (i = 0; i < wbOut.getNumberOfSheets(); i++) {
128                 _s = wbOut.getSheet(i);
129 
130                 if (_s.getName().equals(fileName)) {
131                     break;
132                 }
133             }
134 
135             wbOut.removeSheet(i);
136 
137             try {
138                 rs = select.QueryData(subFolderName, fileName);
139             } catch (SQLException sqe) {
140                 throw new xlException(sqe.getMessage());
141             }
142 
143 
144             // Create new empty sheet
145             write(wbOut.createSheet(fileName, i), rs);
146         } else if (isChanged[xlConstants.DELETE]) {
147             // Remove sheet by name, we have to find the index first
148             int i;
149             WritableSheet _s;
150 
151             for (i = 0; i < wbOut.getNumberOfSheets(); i++) {
152                 _s = wbOut.getSheet(i);
153 
154                 if (_s.getName().equals(fileName)) {
155                     break;
156                 }
157             }
158 
159             wbOut.removeSheet(i);
160         }
161     }
162 
163     protected boolean readFile() throws xlException {
164         Sheet sheet = getSheet();
165         boolean ret = true;
166         columnCount = sheet.getColumns();
167         rowCount = sheet.getRows();
168 
169         // invalidate sheets with either 0 columns or 0 rows
170         // as reported by the JXL Sheet interface
171         for (int z = 0; z < 1; z++) {
172             if ((columnCount == 0) || (rowCount == 0)) {
173                 ret = false;
174 
175                 break;
176             }
177 
178             // investigate the 'header row', invalidate sheets with
179             // incomplete or empty cells as they become table columns
180             Cell[] c = sheet.getRow(0);
181 
182             if ((c.length == 0) || (c.length != columnCount)) {
183                 ret = false;
184 
185                 break;
186             }
187 
188             // the header row is OK, so we can transfer the data to the 
189             // columnNames String[] array
190             columnNames = new String  [c.length];
191 
192             // we have to look a bit closer to each column because we have 
193             // to be sure that the CREATE TABLE is accepted in all(!) SQL 
194             // dialects.
195             for (int i = 0; i < c.length; i++) {
196                 // the header must be of type LABEL, otherwise invalidate
197                 if (c[i].getType() != CellType.LABEL) {
198                     ret = false;
199 
200                     break;
201                 }
202 
203                 // the COLUMNNAME must match a regular expression pattern, 
204                 // notice we are conservative here, but all SQLs must accept it
205                 // ( "^[A-Za-z0-9._-]{1,30}+$" is the so called 'pattern' )
206                 // if at least one column does not match: invalidate table
207                 if (!c[i].getContents().matches("^[A-Za-z0-9._-]{1,30}+$")) {
208                     ret = false;
209 
210                     break;
211                 }
212                 
213                 // columnnames are OK, so transfer
214                 columnNames[i] = c[i].getContents();
215             }
216             
217             // *BUG* issue 163. Duplicate columnnames
218             if (!ret) { break; }
219             // we have to check if all columnnames are different
220             HashMap index = new HashMap();
221             String   key, value;
222             for (int n = 0; n < columnNames.length; n++) {
223                 key = columnNames[n].toUpperCase();
224                 value = columnNames[n];
225                 if (index.containsKey(key)) {
226                     ret = false;
227                     break;
228                 }
229                 else {
230                     index.put(key, value);
231                 }
232             }
233             if (!ret) { break; }
234             //
235             
236 
237             Cell[] t;
238 
239             if (rowCount == 1) {
240                 t = c;
241                 logger.warning(fileName + " has no data, assuming VARCHAR");
242             } else {
243                 // investigate the first row with data: determine datatypes
244                 t = sheet.getRow(1);
245 
246                 // invalidate when there are less values than columns
247                 if (t.length != c.length) {
248                     ret = false;
249 
250                     break;
251                 }
252             }
253 
254             columnTypes = new String  [t.length];
255 
256             // look closer at the values of the columns in the first row, the 
257             // SQL datatype that will be used in the engine is determined here
258             for (int j = 0; j < t.length; j++) {
259                 if ((t[j].getType() == CellType.NUMBER) || 
260                         (t[j].getType() == CellType.NUMBER_FORMULA)) {
261                     columnTypes[j] = "DOUBLE";
262                 } else if ((t[j].getType() == CellType.LABEL) || 
263                                (t[j].getType() == CellType.STRING_FORMULA)) {
264                     columnTypes[j] = "VARCHAR";
265                 } else if ((t[j].getType() == CellType.DATE) || 
266                                (t[j].getType() == CellType.DATE_FORMULA)) {
267                     columnTypes[j] = "DATE";
268                 } else if ((t[j].getType() == CellType.BOOLEAN) || 
269                                (t[j].getType() == CellType.BOOLEAN_FORMULA)) {
270                     columnTypes[j] = "BIT";
271                 // finally there could be an empty or null cell, we can only
272                 // assume VARCHAR --> unless we would look at the next row
273                 // NiLOSTEP... says
274                 //      (*BUG* fixed! issue 162. Empty data. 29/8-'04)
275                 } else if (t[j].getType() == CellType.EMPTY) {
276                     columnTypes[j] = "VARCHAR"; 
277                 } else {
278                     // we -could- add some assert here ?
279                     ret = false;
280                     break;
281                 }
282             }
283         }
284 
285         if (!ret) {
286             logger.info(fileName + " contains no SQL data: invalidated");
287         }
288 
289         sheet = null;
290 
291         return ret;
292     }
293 
294     private void write(WritableSheet wsh, ResultSet rs)
295                 throws xlException {
296         // Transfer a query to an Excel sheet in the JXL API
297         try {
298             ResultSetMetaData rsmd = rs.getMetaData();
299 
300             // Write header row
301             int col = 0;
302             int row = 0;
303             int cols = rsmd.getColumnCount();
304             int[] type = new int[cols];
305 
306             for (col = 0; col < cols; col++) {
307                 Label label = new Label(col, row, rsmd.getColumnName(col + 1));
308                 type[col] = xlConstants.xlType(rsmd.getColumnType(col + 1));
309                 wsh.addCell(label);
310             }
311 
312 
313             //Write data rows
314             row++;
315 
316             while (rs.next()) {
317                 for (col = 0; col < cols; col++) {
318                     switch (type[col]) {
319                     case 1: //Number
320 
321                         jxl.write.Number nm = new jxl.write.Number(col, row, 
322                                                                    rs.getDouble(col + 1));
323                         wsh.addCell(nm);
324 
325                         break;
326 
327                     case 2: //Text
328 
329                         Label lb = new Label(col, row, rs.getString(col + 1));
330                         wsh.addCell(lb);
331 
332                         break;
333 
334                     case 3: //Date
335 
336                         java.util.Date   bug;
337 
338                         if (rs.getDate(col + 1) == null) {
339                             bug = new java.util.Date  (0);
340                         } else {
341                             bug = rs.getDate(col + 1);
342                         }
343 
344                         DateTime dt = new DateTime(col, row, bug);
345                         wsh.addCell(dt);
346 
347                         break;
348 
349                     case 4: //Boolean
350 
351                         jxl.write.Boolean bl = new jxl.write.Boolean(col, row, 
352                                                                      rs.getBoolean(col + 1));
353                         wsh.addCell(bl);
354 
355                         break;
356 
357                     default:
358 
359                         //NiLOSTEP...
360                         //   workaround for MySQL 'TEXT' ...
361                         //End                            
362                         lb = new Label(col, row, rs.getString(col + 1));
363                         wsh.addCell(lb);
364                     }
365                 }
366 
367                 row++;
368             }
369         } catch (SQLException sqe) {
370             throw new xlException(sqe.getMessage());
371         } catch (jxl.write.WriteException jxw) {
372             throw new xlException(jxw.getMessage());
373         }
374         
375     }
376 
377     private static int xlType(int sqlType) {
378         int ret = 0;
379 
380         switch (sqlType) {
381         case (-6):
382         case (-5):
383         case (-2):
384         case 2:
385         case 3:
386         case 4:
387         case 5:
388         case 6:
389         case 7:
390         case 8:
391             ret = 1;
392 
393         case 1:
394         case 12:
395         case 70:
396             ret = 2;
397 
398         case 91:
399         case 92:
400         case 93:
401             ret = 3;
402 
403         case -7:
404         case 16:
405             ret = 4;
406 
407         default:
408             ret = 0;
409         }
410 
411         return ret;
412     }
413 
414     /**
415      * Return sheet as String matrix
416      * 
417      * @return table as 2D array
418      * 
419      * @throws xlException DOCUMENT ME!
420      * @throws IllegalArgumentException if called on invalidated data
421      */
422     public String  [][] getValues() throws xlException {
423         String  [][] ret = 
424         {
425             { "" }
426         };
427 
428         if (validAsSqlTable) {
429             Sheet sheet = getSheet();
430             ret = new String  [columnCount][rowCount - 1];
431 
432             for (int i = 0; i < (rowCount - 1); i++) {
433                 // Process all columns for row: i
434                 for (int j = 0; j < columnCount; j++) {
435                     Cell c = sheet.getCell(j, i + 1);
436 
437                     if ((c == null) || (c.getType() == CellType.EMPTY)) {
438                         ret[j][i] = "";
439                     } else if ((c.getType() == CellType.NUMBER) || 
440                                    (c.getType() == CellType.NUMBER_FORMULA)) {
441                         try {
442                             Locale.setDefault(new Locale("en", "US"));
443 
444                             Double   db = new Double  (((NumberCell) c).getValue());
445                             ret[j][i] = db.toString();
446                         } catch (ClassCastException   ce) {
447                             try {
448                                 SimpleDateFormat dateFormat = 
449                                         new SimpleDateFormat("dd/MM/yyyy");
450                                 java.util.Date   d;
451                                 d = dateFormat.parse(c.getContents());
452                                 ret[j][i] = c.getContents();
453                             } catch (ParseException pe) {
454                                 ret[j][i] = "";
455                             }
456                         }
457                     } else {
458                         ret[j][i] = c.getContents();
459                         ret[j][i] = ret[j][i].replaceAll("'", "''");
460                     }
461                 }
462             }
463 
464             sheet = null;
465         } else {
466             throw new IllegalArgumentException  (xlConstants.NOARGS);
467         }
468 
469         return ret;
470     }
471 
472     // ---  Test Method ---
473 
474     /*    
475         public static void main (String args[]) {
476             //Constructor looks like this>
477             //  xlSheet(File dir, String folder, String name
478             File dir = new File("P:/xlSQL/db");
479             String workbook = "DEMO";
480             String name = "notebooks";
481             try {
482                 xlSheet s = new xlSheet(dir, workbook, name);
483                 String values[][] = s.getValues();
484                 for (int i=0; i < values.length; i++){
485                     for (int j=0; j < values[i].length; j++){
486                         System.out.println(values[i][j]);
487                     }
488                     System.out.println("-----");
489                 }
490             }
491             catch (Exception e) {
492                 System.out.println("Oops...: ERR");
493                 e.printStackTrace();
494             }
495         }
496     */
497 }
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags