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.awt.Color; 34 35 import java.io.ByteArrayOutputStream; 36 import java.io.ByteArrayInputStream; 37 import java.io.DataOutputStream; 38 import java.io.IOException; 39 40 import org.openoffice.xmerge.converter.palm.Record; 41 import org.openoffice.xmerge.util.Debug; 42 import org.openoffice.xmerge.util.IntArrayList; 43 44 import org.openoffice.xmerge.converter.xml.sxc.SpreadsheetEncoder; 45 import org.openoffice.xmerge.converter.xml.sxc.Format; 46 import org.openoffice.xmerge.converter.xml.OfficeConstants; 47 48 /** 49 * This class is used by {@link 50 * org.openoffice.xmerge.converter.xml.sxc.SxcDocumentSerializerImpl 51 * SxcDocumentSerializerImpl} to encode the MiniCalc format. 52 * 53 * @author Paul Rank 54 */ 55 final class MinicalcEncoder extends SpreadsheetEncoder { 56 57 /** MiniCalc WorkBook to store sheets. */ 58 private Workbook wb; 59 60 /** MiniCalc sheet - only one sheet can be open at a time. */ 61 private Worksheet ws; 62 63 /** 64 * Estimate of the number of Palm pixels per character. Used for 65 * estimating the width of a cell on a Palm device. 66 */ 67 private final static int pixelsPerChar = 6; 68 69 /** 70 * The minimum width (in pixels) that we allow a column to be set to 71 * on a Palm device. 72 */ 73 private final static int minWidth = 10; 74 75 /** 76 * The maximum width (in pixels) that we allow a column to be set to 77 * on a Palm device. 78 */ 79 private final static int maxWidth = 80; 80 81 82 /** 83 * Constructor creates a MiniCalc WorkBook. 84 * 85 * @param log Log object for logging. 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 */ MinicalcEncoder(String name, String password)91 MinicalcEncoder(String name, String password) throws IOException { 92 93 super(name, password); 94 95 try { 96 wb = new Workbook(name, password); 97 } 98 catch (JMCException e) { 99 Debug.log(Debug.ERROR, "new Workbook threw exception:" + e.getMessage()); 100 throw new IOException(e.getMessage()); 101 } 102 } 103 104 105 /** 106 * This method creates a WorkSheet belonging to the 107 * WorkBook. 108 * 109 * @param sheetName The name of the WorkSheet. 110 * 111 * @throws IOException If any I/O error occurs. 112 */ createWorksheet(String sheetName)113 public void createWorksheet(String sheetName) throws IOException { 114 115 try { 116 ws = wb.createWorksheet(sheetName); 117 } 118 catch (JMCException e) { 119 Debug.log(Debug.ERROR, "wb.createWorksheet threw exception:" + e.getMessage()); 120 throw new IOException(e.getMessage()); 121 } 122 } 123 124 125 /** 126 * This method gets the number of sheets in the WorkBook. 127 * 128 * @return The number of sheets in the WorkBook. 129 */ getNumberOfSheets()130 public int getNumberOfSheets() { 131 132 int numSheets = wb.getNumberOfSheets(); 133 return numSheets; 134 } 135 136 137 /** 138 * This method encodes the MiniCalc WorkBook information 139 * into an palm <code>Record</code> array in MiniCalc 140 * database format. 141 * 142 * @return Array of <code>Record</code> holding MiniCalc 143 * contents. 144 * 145 * @throws IOException If any I/O error occurs. 146 */ getRecords(int sheetID)147 public Record[] getRecords(int sheetID) throws IOException { 148 149 // Get the WorkSheet for the input sheetID 150 ws = wb.getWorksheet(sheetID); 151 152 // Need to call ws.initWrite() before we start querying the WorkSheet 153 try { 154 ws.initWrite(); 155 } 156 catch (JMCException e) { 157 Debug.log(Debug.ERROR, "ws.initWrite in getRecords:" + e.getMessage()); 158 throw new IOException(e.getMessage()); 159 } 160 161 // Get the number of records in the WorkSheet 162 int numRecords = ws.getNumberOfRecords(); 163 164 // Create the Record array 165 Record[] allRecords = new Record[numRecords]; 166 167 168 // Get each record from the WorkSheet and store in allRecords[] 169 try { 170 for (int i = 0; i < allRecords.length; i++) { 171 172 ByteArrayOutputStream bos = new ByteArrayOutputStream(); 173 174 int length = ws.writeNextRecord(bos); 175 176 byte cBytes[] = bos.toByteArray(); 177 178 allRecords[i] = new Record(cBytes); 179 } 180 } 181 catch (Exception e) { 182 Debug.log(Debug.ERROR, "ws.writeNextRecord in getRecords:" + e.getMessage()); 183 throw new IOException(e.getMessage()); 184 } 185 186 return allRecords; 187 } 188 189 190 /** 191 * A cell reference in a StarOffice formula looks like 192 * [.C2] (for cell C2). MiniCalc is expecting cell references 193 * to look like C2. This method strips out the braces and 194 * the period. 195 * 196 * @param formula A StarOffice formula <code>String</code>. 197 * 198 * @return A MiniCalc formula <code>String</code>. 199 */ parseFormula(String formula)200 protected String parseFormula(String formula) { 201 202 StringBuffer inFormula = new StringBuffer(formula); 203 StringBuffer outFormula = new StringBuffer(); 204 205 boolean inBrace = false; 206 boolean firstCharAfterBrace = false; 207 boolean firstCharAfterColon = false; 208 209 int len = inFormula.length(); 210 211 for (int in = 0; in < len; in++) { 212 switch (inFormula.charAt(in)) { 213 case '[': 214 // We are now inside a StarOffice cell reference. 215 // We also need to strip out the '[' 216 inBrace = true; 217 218 // If the next character is a '.', we want to strip it out 219 firstCharAfterBrace = true; 220 break; 221 222 case ']': 223 // We are exiting a StarOffice cell reference 224 // We are stripping out the ']' 225 inBrace = false; 226 break; 227 228 case ':': 229 // We have a cell range reference. 230 // May need to strip out the leading '.' 231 if (inBrace) 232 firstCharAfterColon = true; 233 outFormula.append(inFormula.charAt(in)); 234 break; 235 236 case '.': 237 if (inBrace == true) { 238 if (firstCharAfterBrace == false && 239 firstCharAfterColon == false) { 240 // Not the first character after the open brace. 241 // We have hit a separator between a sheet reference 242 // and a cell reference. MiniCalc uses a ! as 243 // this type of separator. 244 outFormula.append('!'); 245 } 246 else { 247 firstCharAfterBrace = false; 248 firstCharAfterColon = false; 249 // Since we are in a StarOffice cell reference, 250 // and we are the first character, we need to 251 // strip out the '.' 252 } 253 break; 254 } else { 255 // We hit valid data, lets add it to the formula string 256 outFormula.append(inFormula.charAt(in)); 257 break; 258 } 259 260 case ';': 261 // StarOffice XML format uses ';' as a separator. MiniCalc (and 262 // many spreadsheets) use ',' as a separator instead. 263 outFormula.append(','); 264 break; 265 266 default: 267 // We hit valid data, lets add it to the formula string 268 outFormula.append(inFormula.charAt(in)); 269 270 // Need to make sure that firstCharAfterBrace is not true. 271 firstCharAfterBrace = false; 272 break; 273 } 274 } 275 276 return outFormula.toString(); 277 } 278 279 /** 280 * Add a cell to the current WorkSheet. 281 * 282 * @param row The row number of the cell. 283 * @param column The column number of the cell. 284 * @param fmt The <code>Format</code> object describing 285 * the appearance of this cell. 286 * @param cellContents The text or formula of the cell's contents. 287 * 288 * @throws IOException If any I/O error occurs. 289 */ addCell(int row, int column, Format fmt, String cellContents)290 public void addCell(int row, int column, Format fmt, String cellContents) throws IOException { 291 292 CellAttributes ca = new CellAttributes(getFormat(fmt), 293 fmt.getForeground(), 294 fmt.getBackground()); 295 if (cellContents.startsWith("=")) { 296 cellContents = parseFormula(cellContents); 297 Debug.log(Debug.INFO, "YAHOO Found Formula" + cellContents); 298 } 299 300 CellDescriptor cellDes = new CellDescriptor(row, column, ca, cellContents); 301 302 try { 303 ws.putCell(cellDes); 304 } 305 catch (JMCException jmce) { 306 Debug.log(Debug.ERROR, "ws.putCell threw exception: " + jmce.getMessage()); 307 throw new IOException(jmce.getMessage()); 308 } 309 } 310 311 312 /** 313 * Set the width of the columns in the WorkBook. 314 * 315 * @param columnWidths An <code>IntArrayList</code> of column 316 * widths. 317 */ setColumnWidths(IntArrayList columnWidths)318 public void setColumnWidths(IntArrayList columnWidths) throws IOException { 319 // Get the number of columns 320 int numColumns = columnWidths.size(); 321 322 // Return if there are no columns in the listr 323 if (numColumns == 0) { 324 return; 325 } 326 327 // Need to set the FORM_FLAGS_NONDEFAULT flag for the column widths 328 // to be used in MiniCalc 329 long format = JMCconstants.FORM_FLAGS_NONDEFAULT; 330 331 CellAttributes ca = new CellAttributes(format); 332 333 try { 334 for (int i = 0; i < numColumns; i++) { 335 // Get the column width in Palm pixels 336 int width = columnWidths.get(i) * pixelsPerChar; 337 338 // Check limits on column width 339 if (width < minWidth) { 340 width = minWidth; 341 } else if (width > maxWidth) { 342 width = maxWidth; 343 } 344 345 // Add the column descriptor to the WorkSheet 346 ws.putColumn(i + 1, width, ca); 347 } 348 } 349 catch (JMCException jmce) { 350 Debug.log(Debug.ERROR, "ws.putColumn threw exception: " + jmce.getMessage()); 351 throw new IOException(jmce.getMessage()); 352 } 353 } 354 355 356 /** 357 * This method sets the format of a cell to <i>string</i>. 358 * 359 * @param format The cell format-may already contain display info, 360 * such as alignment or font type. 361 * 362 * @return The updated format of the cell. 363 */ setFormatString(long format)364 private long setFormatString(long format) { 365 366 format = clearCellFormatType(format); 367 368 // Set format to generic, since MiniCalc does not have a string type. 369 format = format | JMCconstants.FF_FORMAT_GENERIC; 370 371 return format; 372 } 373 374 375 /** 376 * This method sets the format of a cell to <i>floating point</i>. 377 * 378 * @param format The cell format. May already contain 379 * display info, such as alignment or 380 * font type. 381 * @param decimalPlaces The number of decimal places to 382 * set in the floating point number. 383 * 384 * @return The updated format of the cell. 385 */ setFormatFloat(long format, int decimalPlaces)386 private long setFormatFloat(long format, int decimalPlaces) { 387 388 format = clearCellFormatType(format); 389 390 // Set format to floating point with correct number of decimal places 391 format = format | JMCconstants.FF_FORMAT_DECIMAL | decimalPlaces; 392 393 return format; 394 } 395 396 397 /** 398 * This method sets the format of a cell to <i>time</i>. 399 * 400 * @param format The cell format-may already contain display info, 401 * such as alignment or font type. 402 * 403 * @return The updated format of the cell. 404 */ setFormatTime(long format)405 private long setFormatTime(long format) { 406 407 format = clearCellFormatType(format); 408 409 // Set format to time. 410 format = format | JMCconstants.FF_FORMAT_TIME; 411 412 return format; 413 } 414 415 416 /** 417 * This method sets the format of a cell to <i>date</i>. 418 * 419 * @param format The cell format-may already contain display info, 420 * such as alignment or font type. 421 * 422 * @return The updated format of the cell. 423 */ setFormatDate(long format)424 private long setFormatDate(long format) { 425 426 format = clearCellFormatType(format); 427 428 // Set format to date. 429 format = format | JMCconstants.FF_FORMAT_DATE; 430 431 return format; 432 } 433 434 435 /** 436 * This method sets the format of a cell to <i>currency</i>. 437 * 438 * @param format The cell format-may already contain 439 * display info, such as alignment or 440 * font type. 441 * @param decimalPlaces The number of decimal places to set. 442 * 443 * @return The updated format of the cell. 444 */ setFormatCurrency(long format, int decimalPlaces)445 private long setFormatCurrency(long format, int decimalPlaces) { 446 447 format = clearCellFormatType(format); 448 449 // Set format to Currency with correct number of decimal places 450 format = format | JMCconstants.FF_FORMAT_CURRENCY | decimalPlaces; 451 452 return format; 453 } 454 455 456 /** 457 * This method sets the format of a cell to <i>boolean</i>. 458 * 459 * @param format The cell format-may already contain display info, 460 * such as alignment or font type. 461 * 462 * @return The updated format of the cell. 463 */ setFormatBoolean(long format)464 private long setFormatBoolean(long format) { 465 466 format = clearCellFormatType(format); 467 468 // Set format to generic, since MiniCalc does not have a Boolean type. 469 format = format | JMCconstants.FF_FORMAT_GENERIC; 470 471 return format; 472 } 473 474 475 /** 476 * This method sets the format of a cell to <i>percent</i>. 477 * 478 * @param format The cell format-may already contain 479 * display info, such as alignment or 480 * font type. 481 * @param decimalPlaces The number of decimal places to set. 482 * 483 * @return The updated format of the cell. 484 */ setFormatPercent(long format, int decimalPlaces)485 private long setFormatPercent(long format, int decimalPlaces) { 486 487 format = clearCellFormatType(format); 488 489 // Set format to Percent with correct number of decimal places 490 format = format | JMCconstants.FF_FORMAT_PERCENT | decimalPlaces; 491 492 return format; 493 } 494 495 496 /** 497 * This method clears out the format bits associated with 498 * the type of data (<i>float</i>, <i>time</i>, etc...) in 499 * a cell. 500 * 501 * @param format The original format for the cell. 502 * 503 * @return The updated cell format with the bits associated 504 * with the type of data (float, time, etc...) 505 * zeroed out. 506 */ clearCellFormatType(long format)507 private long clearCellFormatType(long format) { 508 509 // First 4 bits are for the number of decimal places 510 // bits 5-8 are for the data format (float, time, etc...) 511 512 // Clear out first 8 bits 513 format = format & 0xFFFFFFFFFFFFFF00L; 514 515 return format; 516 } 517 518 519 /** 520 * Set a cell's formatting options via a separately create 521 * <code>Format</code> object. 522 * 523 * @param row The row number of the cell to be changed. 524 * @param column The column number of the cell to be changed. 525 * @param fmt Object containing formatting settings for 526 * this cell. 527 */ setCellFormat(int row, int column, Format fmt)528 public void setCellFormat(int row, int column, Format fmt) { 529 } 530 531 532 /** 533 * Get the names of the sheets in the WorkBook. 534 * 535 * @param sheet The required sheet. 536 */ getSheetName(int sheet)537 public String getSheetName(int sheet) { 538 return wb.getWorksheet(sheet).getName(); 539 } 540 541 542 /* 543 * This method returns a MiniCalc style format from the 544 * <code>Format</code> object. 545 */ getFormat(Format fmt)546 private long getFormat(Format fmt) 547 { 548 String category = fmt.getCategory(); 549 550 if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_BOOLEAN)) { 551 return setFormatBoolean(0); 552 } 553 else if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_CURRENCY)) { 554 return setFormatCurrency(0, fmt.getDecimalPlaces()); 555 } 556 else if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_DATE)) { 557 return setFormatDate(0); 558 } 559 else if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_FLOAT)) { 560 return setFormatFloat(0, fmt.getDecimalPlaces()); 561 } 562 else if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_PERCENT)) { 563 return setFormatPercent(0, fmt.getDecimalPlaces()); 564 } 565 else if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_STRING)) { 566 return setFormatString(0); 567 } 568 else if (category.equalsIgnoreCase(OfficeConstants.CELLTYPE_TIME)) { 569 return setFormatTime(0); 570 } 571 else { 572 // Should never get here, but just in case 573 System.out.println("XXXXX Formatting information not found"); 574 return 0; 575 } 576 } 577 } 578 579