1 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 45 public class xlSheet extends xlFile { 46 private static final String XLS = ".xls"; 47 48 57 xlSheet(File dir, String folder, String name) throws xlException { 58 super(dir, folder, name); 59 } 60 61 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 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 ret; 91 } 92 93 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 write(wsh, rs); 122 } else if (isChanged[xlConstants.UPDATE]) { 123 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 write(wbOut.createSheet(fileName, i), rs); 146 } else if (isChanged[xlConstants.DELETE]) { 147 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 for (int z = 0; z < 1; z++) { 172 if ((columnCount == 0) || (rowCount == 0)) { 173 ret = false; 174 175 break; 176 } 177 178 Cell[] c = sheet.getRow(0); 181 182 if ((c.length == 0) || (c.length != columnCount)) { 183 ret = false; 184 185 break; 186 } 187 188 columnNames = new String [c.length]; 191 192 for (int i = 0; i < c.length; i++) { 196 if (c[i].getType() != CellType.LABEL) { 198 ret = false; 199 200 break; 201 } 202 203 if (!c[i].getContents().matches("^[A-Za-z0-9._-]{1,30}+$")) { 208 ret = false; 209 210 break; 211 } 212 213 columnNames[i] = c[i].getContents(); 215 } 216 217 if (!ret) { break; } 219 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 236 237 Cell[] t; 238 239 if (rowCount == 1) { 240 t = c; 241 logger.warning(fileName + " has no data, assuming VARCHAR"); 242 } else { 243 t = sheet.getRow(1); 245 246 if (t.length != c.length) { 248 ret = false; 249 250 break; 251 } 252 } 253 254 columnTypes = new String [t.length]; 255 256 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 } else if (t[j].getType() == CellType.EMPTY) { 276 columnTypes[j] = "VARCHAR"; 277 } else { 278 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 try { 298 ResultSetMetaData rsmd = rs.getMetaData(); 299 300 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 row++; 315 316 while (rs.next()) { 317 for (col = 0; col < cols; col++) { 318 switch (type[col]) { 319 case 1: 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: 329 Label lb = new Label(col, row, rs.getString(col + 1)); 330 wsh.addCell(lb); 331 332 break; 333 334 case 3: 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: 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 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 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 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 474 497 }
| Popular Tags
|