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 22 23 24 package org.openoffice.xmerge.converter.xml.sxc.minicalc; 25 26 import jmc.Workbook; 27 import jmc.Worksheet; 28 import jmc.CellAttributes; 29 import jmc.CellDescriptor; 30 import jmc.JMCconstants; 31 import jmc.JMCException; 32 33 import java.io.ByteArrayOutputStream; 34 import java.io.ByteArrayInputStream; 35 import java.io.DataOutputStream; 36 import java.io.IOException; 37 import java.io.InputStream; 38 import java.util.Enumeration; 39 40 import org.openoffice.xmerge.ConvertData; 41 import org.openoffice.xmerge.converter.xml.OfficeConstants; 42 import org.openoffice.xmerge.converter.palm.PalmDB; 43 import org.openoffice.xmerge.converter.palm.Record; 44 import org.openoffice.xmerge.converter.palm.PalmDocument; 45 import org.openoffice.xmerge.util.Debug; 46 import org.openoffice.xmerge.converter.xml.sxc.SxcDocumentDeserializer; 47 import org.openoffice.xmerge.converter.xml.sxc.SpreadsheetDecoder; 48 import org.openoffice.xmerge.converter.xml.sxc.Format; 49 50 /** 51 * This class is used by {@link 52 * org.openoffice.xmerge.converter.xml.sxc.SxcDocumentDeserializerImpl} 53 * SxcDocumentDeserializerImpl} to decode the MiniCalc format. 54 * 55 * @author Paul Rank 56 */ 57 final class MinicalcDecoder extends SpreadsheetDecoder { 58 59 /** MiniCalc WorkBook to store sheets. */ 60 private Workbook wb; 61 62 /** MiniCalc sheet - only one sheet can be open at a time. */ 63 private Worksheet ws; 64 65 /** The current cell - only one cell can be active at a time. */ 66 private CellDescriptor cell = null; 67 68 /** Format object describing the current cell. */ 69 private Format fmt = null; 70 71 /** The password for the WorkBook. */ 72 private String password = null; 73 74 /** The number of rows in the current WorkSheet. */ 75 private int maxRows = 0; 76 77 /** The number of columns in the current WorkSheet. */ 78 private int maxCols = 0; 79 80 /** The names of the worksheets. */ 81 private String[] worksheetNames = null; 82 83 /** 84 * Constructor creates a MiniCalc WorkBook. 85 * 86 * @param name The name of the WorkBook. 87 * @param password The password for the workBook. 88 * 89 * @throws IOException If any I/O error occurs. 90 */ MinicalcDecoder(String name, String[] worksheetNames, String password)91 MinicalcDecoder(String name, String[] worksheetNames, String password) throws IOException { 92 93 super(name, password); 94 95 fmt = new Format(); 96 97 this.password = password; 98 this.worksheetNames = worksheetNames; 99 100 try { 101 102 wb = new Workbook(name, password); 103 104 } 105 catch (JMCException e) { 106 107 Debug.log(Debug.ERROR, "MinicalcDecoder.constructor:" + e.getMessage()); 108 109 throw new IOException(e.getMessage()); 110 // e.printStackTrace(); 111 112 } 113 } 114 115 116 /** 117 * This method takes a <code>ConvertData</code> as input and 118 * converts it into a MiniCalc WorkSheet. The WorkSheet is then 119 * added to the WorkBook. 120 * 121 * @param InputStream An <code>ConvertData</code> containing a 122 * MiniCalc WorkSheet. 123 * 124 * @throws IOException If any I/O error occurs. 125 */ addDeviceContent(ConvertData cd)126 public void addDeviceContent(ConvertData cd) throws IOException { 127 128 try { 129 PalmDocument palmDoc; 130 int j = 0; 131 132 Enumeration e = cd.getDocumentEnumeration(); 133 while(e.hasMoreElements()) { 134 135 palmDoc = (PalmDocument) e.nextElement(); 136 // Convert PDB to WorkBook/WorkSheet format 137 PalmDB pdb = palmDoc.getPdb(); 138 139 // This will be done at least once 140 String sheetName = worksheetNames[j]; 141 142 // Get number of records in the pdb 143 int numRecords = pdb.getRecordCount(); 144 145 // sheetName does not contain the WorkBook name, but we need the 146 // full name. 147 String fullSheetName = new String(wb.getWorkbookName() + "-" + sheetName); 148 149 // Create a new (empty) WorkSheet 150 ws = new Worksheet(); 151 152 // Initialize the WorkSheet 153 ws.initWorksheet(fullSheetName, numRecords); 154 155 // Loop over the number of records in the PDB 156 for (int i = 0; i < numRecords; i++) { 157 158 // Read record i from the PDB 159 Record rec = pdb.getRecord(i); 160 161 byte cBytes[] = rec.getBytes(); 162 163 // Create an InputStream 164 ByteArrayInputStream bis = new ByteArrayInputStream(cBytes); 165 166 // Get the size of the stream 167 int bisSize = cBytes.length; 168 169 // Add each record to the WorkSheet 170 ws.readNextRecord(bis, bisSize); 171 } 172 173 174 // Add the WorkSheet to the WorkBook 175 wb.addWorksheet(ws); 176 j++; 177 } 178 } 179 catch (JMCException e) { 180 181 Debug.log(Debug.ERROR, "MinicalcDecoder.addPDB:" + e.getMessage()); 182 183 throw new IOException(e.getMessage()); 184 } 185 } 186 187 188 /** 189 * This method returns the number of spreadsheets 190 * stored in the WorkBook. 191 * 192 * @return The number of sheets in the WorkBook. 193 */ getNumberOfSheets()194 public int getNumberOfSheets() { 195 196 return wb.getNumberOfSheets(); 197 } 198 199 200 /** 201 * This method gets the requested WorkSheet from the 202 * WorkBook and sets it as the selected WorkSheet. All 203 * other "get" methods will now get data from this WorkSheet. 204 * 205 * @param sheetIndex The index number of the sheet to open. 206 * 207 * @throws IOException If any I/O error occurs. 208 */ setWorksheet(int sheetIndex)209 public void setWorksheet(int sheetIndex) throws IOException { 210 211 try { 212 213 ws = wb.getWorksheet(sheetIndex); 214 215 // Initialize access to the WorkSheet so that we can calculate 216 // the number of rows and columns 217 ws.initAccess(password); 218 219 maxRows = 0; 220 maxCols = 0; 221 222 while (goToNextCell()) { 223 maxRows = Math.max(maxRows, cell.getRowNumber()); 224 maxCols = Math.max(maxCols, cell.getColNumber()); 225 } 226 227 // Re-initialize access to the WorkSheet 228 ws.initAccess(password); 229 230 } 231 catch (JMCException e) { 232 233 Debug.log(Debug.ERROR, "MinicalcDecoder.setWorksheet:" + e.getMessage()); 234 235 throw new IOException(e.getMessage()); 236 // e.printStackTrace(); 237 238 } 239 } 240 241 242 /** 243 * This method returns the name of the current spreadsheet. 244 * 245 * @return The name of the current WorkSheet. 246 */ getSheetName()247 public String getSheetName() { 248 249 String sheetName = ws.getName(); 250 251 return sheetName; 252 } 253 254 255 /** 256 * This method gets the next cell from the WorkSheet 257 * and sets it as the selected cell. All other "get" 258 * methods will now get data from this cell. 259 * 260 * @return True if we were able to go to another cell 261 * in the sheet, false if there were no cells 262 * left. 263 * 264 * @throws IOException If any I/O error occurs. 265 */ goToNextCell()266 public boolean goToNextCell() throws IOException { 267 268 boolean gotCell = false; 269 270 try { 271 cell = ws.getNextCell(); 272 273 if (cell != null) { 274 gotCell = true; 275 } 276 277 // As we read each cell, get its formatting info 278 readCellFormat(); 279 } 280 catch (JMCException e) { 281 282 Debug.log(Debug.ERROR, "MinicalcDecoder.goToNextCell:" + e.getMessage()); 283 284 throw new IOException(e.getMessage()); 285 // e.printStackTrace(); 286 287 } 288 289 return gotCell; 290 } 291 292 293 /** 294 * This method returns the row number of the current cell. 295 * 296 * @return The row number of the current cell. Returns 297 * -1 if no cell is currently selected. 298 */ getRowNumber()299 public int getRowNumber() { 300 301 int row = -1; 302 303 if (cell != null) { 304 305 row = cell.getRowNumber(); 306 } 307 308 return row; 309 } 310 311 /** 312 * This method returns the number of rows in the current sheet. 313 * 314 * @return The number of rows in the current sheet. 315 */ getNumberOfRows()316 public int getNumberOfRows() { 317 318 return maxRows; 319 } 320 321 /** 322 * This method returns the number of columns in the current sheet. 323 * 324 * @return The number of columns in the current sheet. 325 */ getNumberOfColumns()326 public int getNumberOfColumns() { 327 return maxCols; 328 } 329 330 331 /** 332 * This method returns the col number of the current cell. 333 * 334 * @return The col number of the current cell. Returns 335 * -1 if no cell is currently selected. 336 */ getColNumber()337 public int getColNumber() { 338 339 int col = -1; 340 341 if (cell != null) { 342 343 col = cell.getColNumber(); 344 } 345 346 return col; 347 } 348 349 350 /** 351 * This method returns the contents of the current cell. 352 * 353 * @return The contents of the current cell. Returns 354 * null if no cell is currently selected. 355 */ getCellContents()356 public String getCellContents() { 357 358 String contents = null; 359 360 if (cell != null) { 361 contents = cell.getCellContents(); 362 363 // Active cell, but no content 364 if (contents == null) 365 return new String(""); 366 367 // Does the cell contain a formula? 368 if (contents.startsWith("=")) { 369 contents = parseFormula(contents); 370 } 371 // Make sure that any MiniCalc peculiarities are stripped off 372 if (fmt.getCategory().equalsIgnoreCase(OfficeConstants.CELLTYPE_CURRENCY)) { 373 contents = currencyRemoveSign(contents); 374 } 375 else if (fmt.getCategory().equalsIgnoreCase(OfficeConstants.CELLTYPE_PERCENT)) { 376 contents = percentRemoveSign(contents); 377 } 378 else if (fmt.getCategory().equalsIgnoreCase(OfficeConstants.CELLTYPE_DATE)) { 379 contents = convertToStarDate(contents); 380 } 381 else if (fmt.getCategory().equalsIgnoreCase(OfficeConstants.CELLTYPE_TIME)) { 382 contents = convertToStarTime(contents); 383 } 384 } 385 386 return contents; 387 } 388 389 /** 390 * This method is meant to return the value of the formula cell. However 391 * in minicalc this value is not used so hence the stubbed function 392 * 393 * @return the value fo the formula cell 394 */ getCellValue()395 public String getCellValue() { 396 return null; 397 } 398 399 /** 400 * <p>This method takes a formula and parses it into 401 * StarOffice XML formula format.</p> 402 * 403 * <p>Many spreadsheets use ',' as a separator. 404 * StarOffice XML format uses ';' as a separator instead.</p> 405 * 406 * <p>Many spreadsheets use '!' as a separator when refencing 407 * a cell in a different sheet.</p> 408 * 409 * <blockquote> 410 * Example: =sheet1!A1 411 * </blockquote> 412 * 413 * <p>StarOffice XML format uses '.' as a separator instead.</p> 414 * 415 * <blockquote> 416 * Example: =sheet1.A1 417 * </blockquote> 418 * 419 * @param formula A formula string. 420 * 421 * @return A StarOffice XML format formula string. 422 */ parseFormula(String formula)423 protected String parseFormula(String formula) { 424 425 formula = formula.replace(',', ';'); 426 formula = formula.replace('!', '.'); 427 428 return formula; 429 } 430 431 /** 432 * <p>This method returns the type of the data in the current cell.</p> 433 * 434 * <p>Possible Data Types:</p> 435 * 436 * <ul><li> 437 * Percent - MiniCalc can store as a number or as a string. 438 * 439 * When stored as a string, the % sign is stored in the 440 * string . The MiniCalc format is "string". 441 * Example 10.1% is stored as the string "10.1%" 442 * 443 * When stored as a number, the decimal representation 444 * is stored. The MiniCalc format is "percentage". 445 * Example: 10.1% is stored as "0.101" 446 * </li><li> 447 * Currency - MiniCalc stores currency as a number with the format 448 * set to "currency". 449 * A user can also enter a value with a dollar sign 450 * (example $18.56) into MiniCalc and not set the format 451 * as currency. We treat this type of data as a 452 * currency data type. 453 * </li><li> 454 * Boolean - MiniCalc stores in a string as "true" or "false" 455 * </li><li> 456 * 457 * Date - MiniCalc stores a date in a string as either 458 * MM/DD/YY or MM/DD/YYYY. Any variation from the above 459 * format will not be considered a date. 460 * </li><li> 461 * Time - MiniCalc stores a time in a string as hh:mm:ss. Any 462 * variation from this format will not be considered a time. 463 * </li><li> 464 * Float - MiniCalc stores as a number and it is not percent 465 * or currency. 466 * </li><li> 467 * String - MiniCalc stores as a string (surprise). Doesn't parse 468 * to any of the other data types. 469 * </li><li> 470 * @return The type of the data in the current cell. 471 * </li></ul> 472 */ getCellDataType()473 public String getCellDataType() { 474 475 boolean isNumber = false; 476 477 // Get format value set on the cell in MiniCalc 478 String format = getCellFormatType(); 479 480 // Initialize the data type to the format 481 String type = format; 482 483 String contents = getCellContents(); 484 485 if (contents != null) { 486 487 MinicalcDataString data = new MinicalcDataString(contents); 488 489 // Check if it is a formula 490 if (data.isFormula()) { 491 Debug.log(Debug.INFO, " " + contents + " Is a Function Format = " 492 + format + "\n"); 493 return type; 494 } 495 496 try { 497 // Check to see if it is a number 498 Double d = Double.valueOf(contents); 499 isNumber = true; 500 Debug.log(Debug.INFO, " " + contents + " Is a Number Format = " + format); 501 502 } catch (NumberFormatException e) { 503 Debug.log(Debug.INFO, " " + contents + " Not a Number Format= " + format); 504 // no, it is not a number 505 isNumber = false; 506 } 507 508 509 if (isNumber) { 510 511 // Numbers are Float, Currency, and Percent 512 if (format.equals(OfficeConstants.CELLTYPE_CURRENCY)) { 513 514 type = OfficeConstants.CELLTYPE_CURRENCY; 515 516 } else if (format.equals(OfficeConstants.CELLTYPE_PERCENT)) { 517 518 type = OfficeConstants.CELLTYPE_PERCENT; 519 520 } else { 521 522 type = OfficeConstants.CELLTYPE_FLOAT; 523 } 524 525 } else if (data.isBoolean()) { 526 527 // Data is a Boolean type 528 type = OfficeConstants.CELLTYPE_BOOLEAN; 529 530 } else if (data.isDate()) { 531 532 // Data is a Date type 533 type = OfficeConstants.CELLTYPE_DATE; 534 535 } else if (data.isTime()) { 536 537 // Data is a Time type 538 type = OfficeConstants.CELLTYPE_TIME; 539 540 } else if (data.isPercent()) { 541 542 // Data is percent 543 type = OfficeConstants.CELLTYPE_PERCENT; 544 545 } else if (data.isCurrency()) { 546 547 // Data is a Currency type 548 type = OfficeConstants.CELLTYPE_CURRENCY; 549 550 } else { 551 552 // Data can't be float, since it isn't a number 553 554 // We've already tried parsing it as all other data 555 // types, the only remaining option is a string 556 type = OfficeConstants.CELLTYPE_STRING; 557 } 558 } 559 560 Debug.log(Debug.INFO, " TYPE = " + type + "\n"); 561 562 return type; 563 } 564 565 566 /** 567 * This method returns the format of the data in the current cell. 568 * 569 * @return The format of the data in the current cell. 570 */ getCellFormatType()571 String getCellFormatType() { 572 573 // Set type to default data type 574 String type = OfficeConstants.CELLTYPE_FLOAT; 575 576 if (cell != null) { 577 578 // Get the attributes for the current cell 579 CellAttributes att = cell.getCellAttributes(); 580 581 if (att != null) { 582 583 // Extract the format info from the attributes 584 long format = att.getFormat(); 585 586 // The cell type is stored in bits 5-8 587 long cellType = format & 0x000000F0L; 588 589 // The number of decimal places is stored in bits 1-4 590 long decimals = format & 0x0000000FL; 591 592 if (cellType == JMCconstants.FF_FORMAT_GENERIC) { 593 594 // MiniCalc stores both Strings and Booleans 595 // in the generic type. We must check the contents 596 // to differentiate between the two. 597 598 // Get cell's contents 599 String contents = getCellContents(); 600 601 if (contents.equalsIgnoreCase("false") || 602 contents.equalsIgnoreCase("true")) { 603 604 type = OfficeConstants.CELLTYPE_BOOLEAN; 605 606 607 } else { 608 609 type = OfficeConstants.CELLTYPE_STRING; 610 611 } 612 613 } else if (cellType == JMCconstants.FF_FORMAT_DECIMAL) { 614 615 type = OfficeConstants.CELLTYPE_FLOAT; 616 617 } else if (cellType == JMCconstants.FF_FORMAT_TIME) { 618 619 type = OfficeConstants.CELLTYPE_TIME; 620 621 } else if (cellType == JMCconstants.FF_FORMAT_DATE) { 622 623 type = OfficeConstants.CELLTYPE_DATE; 624 625 } else if (cellType == JMCconstants.FF_FORMAT_CURRENCY) { 626 627 type = OfficeConstants.CELLTYPE_CURRENCY; 628 629 } else if (cellType == JMCconstants.FF_FORMAT_PERCENT) { 630 631 type = OfficeConstants.CELLTYPE_PERCENT; 632 } 633 634 } 635 } 636 637 return type; 638 } 639 640 641 /** 642 * This method takes a <code>String</code> that contains a 643 * currency value and removes the $ from the <code>String</code>. 644 * If the dollar sign is not the first or last character of the 645 * input <code>String</code>, the input <code>String</code> is 646 * simply returned. 647 * 648 * @param contents The input <code>String</code> from which to 649 * remove the dollar sign. 650 * 651 * @return The input <code>String</code> minus the dollar sign. 652 * If the input <code>String</code> did not begin or end 653 * with a dollar sign, contents is returned. 654 */ currencyRemoveSign(String contents)655 private String currencyRemoveSign(String contents) { 656 MinicalcDataString mcString = new MinicalcDataString(contents); 657 String currencyString = mcString.currencyRemoveSign(); 658 return currencyString; 659 } 660 661 662 /** 663 * This method takes a <code>String</code> that contains a percent 664 * value and removes the % from the <code>String</code>. If the 665 * percent sign is not the last character of the input 666 * <code>String</code>, the input <code>String</code> is simply 667 * returned. 668 * 669 * @param contents The input String from which to remove the 670 * percent sign. 671 * 672 * @return The input <code>String</code> minus the percent sign. 673 * If the input <code>String</code> did not begin with 674 * a percent sign, contents is returned. 675 */ percentRemoveSign(String contents)676 private String percentRemoveSign(String contents) { 677 MinicalcDataString mcString = new MinicalcDataString(contents); 678 String percentString = mcString.percentRemoveSign(); 679 return percentString; 680 } 681 682 683 /** 684 * This method returns takes a <code>String</code> that contains 685 * a time value and converts it from MiniCalc format to StarOffice 686 * XML time format. 687 * 688 * @param contents The input <code>String</code> containing a 689 * MiniCalc time. 690 * 691 * @return The input <code>String</code> converted to StarOffice 692 * XML time format. 693 */ convertToStarTime(String contents)694 private String convertToStarTime(String contents) { 695 MinicalcDataString mcString = new MinicalcDataString(contents); 696 String timeString = mcString.convertToStarTime(); 697 return timeString; 698 } 699 700 /** 701 * This method returns takes a <code>String</code> that contains 702 * a date value and converts it from MiniCalc format to StarOffice 703 * XML date format. 704 * 705 * @param contents The input <code>String</code> containing a 706 * MiniCalc date. 707 * 708 * @return The input <code>String</code> converted to StarOffice 709 * XML date format. 710 */ convertToStarDate(String contents)711 private String convertToStarDate(String contents) { 712 MinicalcDataString mcString = new MinicalcDataString(contents); 713 String dateString = mcString.convertToStarDate(); 714 return dateString; 715 } 716 717 718 /** 719 * Return the Format object describing the active cell formatting. 720 * 721 * @return The Format object describing the active cell formatting. 722 */ getCellFormat()723 public Format getCellFormat() { 724 return new Format(fmt); 725 } 726 727 728 /** 729 * Create the format data for the new cell. 730 */ readCellFormat()731 private void readCellFormat() { 732 // Make sure there are no remnants from the last time 733 fmt.clearFormatting(); 734 735 fmt.setCategory(getCellFormatType()); 736 737 // TODO - Get any more formatting data here 738 } 739 } 740 741