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.pexcel; 25 26 import java.io.IOException; 27 import java.util.Vector; 28 import java.util.Enumeration; 29 import java.util.NoSuchElementException; 30 31 import org.openoffice.xmerge.ConvertData; 32 import org.openoffice.xmerge.converter.xml.OfficeConstants; 33 import org.openoffice.xmerge.util.Debug; 34 import org.openoffice.xmerge.converter.xml.sxc.BookSettings; 35 import org.openoffice.xmerge.converter.xml.sxc.SpreadsheetDecoder; 36 import org.openoffice.xmerge.converter.xml.sxc.Format; 37 import org.openoffice.xmerge.converter.xml.sxc.NameDefinition; 38 import org.openoffice.xmerge.converter.xml.sxc.ColumnRowInfo; 39 import org.openoffice.xmerge.converter.xml.sxc.pexcel.records.*; 40 41 /** 42 * This class is used by {@link SxcDocumentDeserializerImpl} 43 * SxcDocumentDeserializerImpl} to decode the Pocket Excel format. 44 * 45 * @author Paul Rank 46 */ 47 final class PocketExcelDecoder extends SpreadsheetDecoder { 48 49 private Workbook wb; 50 private Worksheet ws; 51 private CellValue cell; 52 private int maxRows = 0; 53 private int maxCols = 0; 54 private int wsIndex; 55 private Enumeration cellValue; 56 private Format fmt = null; 57 58 /** 59 * Constructor creates a Pocket Excel WorkBook. 60 * 61 * @param name The name of the WorkBook. 62 * @param worksheetNames set of Strings equivalent to the worksheets 63 * contained in the workbook 64 * @param password The password for the workBook. 65 * 66 * @throws IOException If any I/O error occurs. 67 */ PocketExcelDecoder(String name, String[] worksheetNames, String password)68 PocketExcelDecoder(String name, String[] worksheetNames, String password) throws IOException { 69 super(name, password); 70 71 fmt = new Format(); 72 } 73 74 75 /** 76 * This method takes a <code>ConvertData</code> as input and 77 * converts it into a PocketWord WorkSheet. The WorkSheet is then 78 * added to the WorkBook. 79 * 80 * @param cd An <code>ConvertData</code> containing a 81 * Pocket Excel WorkSheet. 82 * 83 * @throws IOException If any I/O error occurs. 84 */ addDeviceContent(ConvertData cd)85 public void addDeviceContent(ConvertData cd) throws IOException { 86 87 Enumeration e = cd.getDocumentEnumeration(); 88 wb = (Workbook) e.nextElement(); 89 } 90 91 92 /** 93 * This method returns the number of spreadsheets 94 * stored in the WorkBook. 95 * 96 * @return The number of sheets in the WorkBook. 97 */ getNumberOfSheets()98 public int getNumberOfSheets() { 99 100 Vector v = wb.getWorksheetNames(); 101 Debug.log(Debug.TRACE,"Total Number of Sheets : " + v.size()); 102 return (v.size()); 103 } 104 105 /** 106 * This method returns the number of spreadsheets 107 * stored in the WorkBook. 108 * 109 * @return The number of sheets in the WorkBook. 110 */ getNameDefinitions()111 public Enumeration getNameDefinitions() { 112 113 Enumeration e = wb.getDefinedNames(); 114 Vector nameDefinitionVector = new Vector(); 115 while(e.hasMoreElements()) { 116 DefinedName dn = (DefinedName)e.nextElement(); 117 NameDefinition nameDefinitionEntry = dn.getNameDefinition(); 118 nameDefinitionVector.add(nameDefinitionEntry); 119 } 120 Debug.log(Debug.TRACE,"Getting " + nameDefinitionVector.size() + " DefinedName records"); 121 return (nameDefinitionVector.elements()); 122 } 123 124 /** 125 * This method returns an enumeration of Settings object(s), 126 * one for each worksheet 127 * 128 * @return An enumerattion of <code>Settings</code> 129 */ getSettings()130 public BookSettings getSettings() { 131 132 return (wb.getSettings()); 133 } 134 /** 135 * This method returns the number of spreadsheets 136 * stored in the WorkBook. 137 * 138 * @return The number of sheets in the WorkBook. 139 */ getColumnRowInfos()140 public Enumeration getColumnRowInfos() { 141 142 Vector colRowVector = new Vector(); 143 144 // Collect Columns from worksheet and add them to the vector 145 for(Enumeration e = ws.getColInfos();e.hasMoreElements();) { 146 ColInfo ci = (ColInfo)e.nextElement(); 147 int repeated = ci.getLast() - ci.getFirst() + 1; 148 ColumnRowInfo colInfo = new ColumnRowInfo( ci.getColWidth(), 149 repeated, 150 ColumnRowInfo.COLUMN); 151 colRowVector.add(colInfo); 152 } 153 154 // Collect Rows from worksheet and add them to the vector 155 for(Enumeration e = ws.getRows();e.hasMoreElements();) { 156 Row rw = (Row)e.nextElement(); 157 // We will use the repeat field for number (unlike columns rows 158 // cannot be repeated, we have unique record for each row in pxl 159 int repeated = rw.getRowNumber(); 160 ColumnRowInfo rowInfo = new ColumnRowInfo( rw.getRowHeight(), 161 repeated, 162 ColumnRowInfo.ROW); 163 colRowVector.add(rowInfo); 164 } 165 Debug.log(Debug.TRACE,"Getting " + colRowVector.size() + " ColRowInfo records"); 166 return (colRowVector.elements()); 167 } 168 169 /** 170 * This method gets the requested WorkSheet from the 171 * WorkBook and sets it as the selected WorkSheet. All 172 * other "get" methods will now get data from this WorkSheet. 173 * 174 * @param sheetIndex The index number of the sheet to open. 175 * 176 * @throws IOException If any I/O error occurs. 177 */ setWorksheet(int sheetIndex)178 public void setWorksheet(int sheetIndex) throws IOException { 179 Debug.log(Debug.TRACE,"Setting to worksheet : " + sheetIndex); 180 ws = wb.getWorksheet(sheetIndex); 181 cellValue = ws.getCellEnumerator(); 182 wsIndex = sheetIndex; 183 while(goToNextCell()) { 184 maxRows = Math.max(maxRows, cell.getRow()); 185 maxCols = Math.max(maxCols, cell.getCol()); 186 } 187 cellValue = ws.getCellEnumerator(); 188 Debug.log(Debug.TRACE,"Max Cols : " + maxCols + " MaxRows : " + maxRows); 189 } 190 191 192 /** 193 * This method returns the name of the current spreadsheet. 194 * 195 * @return The name of the current WorkSheet. 196 */ getSheetName()197 public String getSheetName() { 198 199 String wsName = wb.getSheetName(wsIndex); 200 Debug.log(Debug.TRACE,"The name of the current Worksheet is : " + wsName); 201 return wsName; 202 } 203 204 205 /** 206 * This method gets the next cell from the WorkSheet 207 * and sets it as the selected cell. All other "get" 208 * methods will now get data from this cell. 209 * 210 * @return True if we were able to go to another cell 211 * in the sheet, false if there were no cells 212 * left. 213 * 214 * @throws IOException If any I/O error occurs. 215 */ goToNextCell()216 public boolean goToNextCell() throws IOException { 217 218 boolean success = false; 219 220 try { 221 cell = (CellValue) cellValue.nextElement(); 222 Debug.log(Debug.TRACE,"Current Cell : " + cell.getString()); 223 readCellFormat(); 224 success = true; 225 } catch (NoSuchElementException e) { 226 Debug.log(Debug.TRACE,"Could't find current cell"); 227 } 228 229 return success; 230 } 231 232 233 /** 234 * This method returns the row number of the current cell. 235 * 236 * @return The row number of the current cell. Returns 237 * -1 if no cell is currently selected. 238 */ getRowNumber()239 public int getRowNumber() { 240 241 int row = -1; 242 243 if (cell != null) { 244 row = cell.getRow(); 245 Debug.log(Debug.TRACE,"cell row is " + row); 246 } 247 return (row); 248 } 249 250 /** 251 * This method returns the number of rows in the current sheet. 252 * 253 * @return The number of rows in the current sheet. 254 */ getNumberOfRows()255 public int getNumberOfRows() { 256 return maxRows; 257 } 258 259 /** 260 * This method returns the number of columns in the current sheet. 261 * 262 * @return The number of columns in the current sheet. 263 */ getNumberOfColumns()264 public int getNumberOfColumns() { 265 return maxCols; 266 } 267 268 269 /** 270 * This method returns the col number of the current cell. 271 * 272 * @return The col number of the current cell. Returns 273 * -1 if no cell is currently selected. 274 */ getColNumber()275 public int getColNumber() { 276 277 int col = -1; 278 279 if (cell != null) { 280 col = cell.getCol(); 281 Debug.log(Debug.TRACE,"cell col is " + col); 282 } 283 return (col); 284 } 285 286 /** 287 * This method returns the contents of the current cell. 288 * 289 * @return The contents of the current cell. Returns 290 * null if no cell is currently selected. 291 */ getCellContents()292 public String getCellContents() { 293 294 String contents = new String(""); 295 296 if (cell != null) { 297 try { 298 contents = cell.getString(); 299 if (contents.startsWith("=")) { 300 contents = parseFormula(contents); 301 } 302 } 303 catch (IOException e) { 304 System.err.println("Could Not retrieve Cell contents"); 305 System.err.println("Setting contents of cell(" + cell.getRow() 306 + "," + cell.getCol() + ") to an empty string"); 307 System.err.println("Error msg: " + e.getMessage()); 308 } 309 } 310 311 return contents; 312 } 313 314 /** 315 * <p>This method takes a formula and parses it into 316 * StarOffice XML formula format.</p> 317 * 318 * <p>Many spreadsheets use ',' as a separator. 319 * StarOffice XML format uses ';' as a separator instead.</p> 320 * 321 * <p>Many spreadsheets use '!' as a separator when refencing 322 * a cell in a different sheet.</p> 323 * 324 * <blockquote> 325 * Example: =sheet1!A1 326 * </blockquote> 327 * 328 * <p>StarOffice XML format uses '.' as a separator instead.</p> 329 * 330 * <blockquote> 331 * Example: =sheet1.A1 332 * </blockquote> 333 * 334 * @param formula A formula string. 335 * 336 * @return A StarOffice XML format formula string. 337 */ parseFormula(String formula)338 protected String parseFormula(String formula) { 339 340 formula = formula.replace(',', ';'); 341 formula = formula.replace('!', '.'); 342 343 return formula; 344 } 345 346 /** 347 * This method returns the contents of the current cell. 348 * 349 * @return The contents of the current cell. Returns 350 * null if no cell is currently selected. 351 */ getCellValue()352 public String getCellValue() { 353 354 String contents = new String(""); 355 356 if (cell != null) { 357 try { 358 contents = ((Formula)cell).getValue(); 359 } 360 catch (IOException e) { 361 System.err.println("Could Not retrieve Cell value"); 362 System.err.println("Setting value of cell(" + cell.getRow() 363 + "," + cell.getCol() + ") to an empty string"); 364 System.err.println("Error msg: " + e.getMessage()); 365 } 366 } 367 return contents; 368 } 369 370 /** 371 * <p>This method returns the type of the data in the current cell. 372 * Currently the only type supported is String.</p> 373 * 374 * @return The type of the data in the current cell. 375 */ getCellDataType()376 public String getCellDataType() { 377 378 String type = OfficeConstants.CELLTYPE_STRING; 379 380 if(cell instanceof FloatNumber) 381 type = OfficeConstants.CELLTYPE_FLOAT; 382 if(cell instanceof Formula) 383 type = OfficeConstants.CELLTYPE_FLOAT; 384 385 return type; 386 } 387 388 389 /** 390 * Return the Format object describing the active cell formatting. 391 * 392 * @return The Format object describing the active cell formatting. 393 */ getCellFormat()394 public Format getCellFormat() { 395 return new Format(fmt); 396 } 397 398 399 /** 400 * Create the format data for the new cell. 401 */ readCellFormat()402 private void readCellFormat() throws IOException { 403 404 fmt.clearFormatting(); 405 406 Debug.log(Debug.TRACE," ixfe for Current Cell " + cell.getIxfe()); 407 ExtendedFormat xf = wb.getExtendedFormat(cell.getIxfe()); 408 Debug.log(Debug.TRACE," ixfnt for Current Cell " + xf.getFontIndex()); 409 FontDescription fd = wb.getFontDescription(xf.getFontIndex()); 410 411 fmt.setAttribute(Format.ITALIC, fd.isItalic()); 412 fmt.setAttribute(Format.BOLD, fd.isBold()); 413 fmt.setAttribute(Format.UNDERLINE, fd.isUnderline()); 414 fmt.setForeground(fd.getForeground()); 415 416 fmt.setBackground(xf.getBackground()); 417 fmt.setAlign(xf.getAlign()); 418 fmt.setVertAlign(xf.getVertAlign()); 419 fmt.setAttribute(Format.WORD_WRAP, xf.isWordWrap()); 420 421 fmt.setAttribute(Format.TOP_BORDER, xf.isBorder(ExtendedFormat.TOP_BORDER)); 422 fmt.setAttribute(Format.BOTTOM_BORDER, xf.isBorder(ExtendedFormat.BOTTOM_BORDER)); 423 fmt.setAttribute(Format.RIGHT_BORDER, xf.isBorder(ExtendedFormat.RIGHT_BORDER)); 424 fmt.setAttribute(Format.LEFT_BORDER, xf.isBorder(ExtendedFormat.LEFT_BORDER)); 425 426 fmt.setFontName(fd.getFont()); 427 fmt.setFontSize(fd.getFontSize()); 428 429 fmt.setCategory(getCellDataType()); 430 431 } 432 } 433 434