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.records; 25 26 import java.io.OutputStream; 27 import java.io.InputStream; 28 import java.io.IOException; 29 30 import org.openoffice.xmerge.util.Debug; 31 import org.openoffice.xmerge.util.EndianConverter; 32 import org.openoffice.xmerge.converter.xml.OfficeConstants; 33 import org.openoffice.xmerge.converter.xml.sxc.Format; 34 import org.openoffice.xmerge.converter.xml.sxc.pexcel.records.formula.FormulaHelper; 35 import org.openoffice.xmerge.converter.xml.sxc.pexcel.records.Workbook; 36 import org.openoffice.xmerge.converter.xml.sxc.pexcel.PocketExcelConstants; 37 38 39 /** 40 * Represents a BIFF Record describing a formula 41 */ 42 public class Formula extends CellValue implements OfficeConstants { 43 44 private byte[] num = new byte[8]; 45 private byte grbit; 46 private byte[] cce = new byte[2]; 47 private byte[] rgce; 48 private FormulaHelper fh = new FormulaHelper(); 49 50 /** 51 * Constructs a <code>Formula</code> using specified attributes 52 * 53 * @param row row number 54 * @param column column number 55 * @param cellContents contents of the cell 56 * @param ixfe font index 57 */ Formula(int row, int column, String cellContents, int ixfe, Format fmt, Workbook wb)58 public Formula(int row, int column, String cellContents, int ixfe, Format fmt, Workbook wb) 59 throws Exception { 60 61 fh.setWorkbook(wb); 62 63 setRow(row); 64 setCol(column); 65 setIxfe(ixfe); 66 setFormula(cellContents); 67 68 String category = fmt.getCategory(); 69 String value = fmt.getValue(); 70 71 if(category.equalsIgnoreCase(CELLTYPE_BOOLEAN)) { 72 num[0]=(byte)0x01; 73 num[1]=(byte)0x00; 74 if(value.equalsIgnoreCase("true")) { 75 num[2]=(byte)0x01; 76 } else { 77 num[2]=(byte)0x00; 78 } 79 num[3]=(byte)0x00;num[4]=(byte)0x00;num[5]=(byte)0x00; 80 num[6]=(byte)0xFF;num[7]=(byte)0xFF; 81 } else if(category.equalsIgnoreCase(CELLTYPE_DATE)) { 82 Debug.log(Debug.TRACE,"Date Formula"); 83 num = EndianConverter.writeDouble(toExcelSerialDate(fmt.getValue())); 84 } else if(category.equalsIgnoreCase(CELLTYPE_TIME)) { 85 Debug.log(Debug.TRACE,"Time Formula"); 86 num = EndianConverter.writeDouble(toExcelSerialTime(fmt.getValue())); 87 } else if(category.equalsIgnoreCase(CELLTYPE_PERCENT)) { 88 Debug.log(Debug.TRACE,"Percent Formula"); 89 double percent = (double) Double.parseDouble(fmt.getValue()); 90 num = EndianConverter.writeDouble(percent); 91 } else if(category.equalsIgnoreCase(CELLTYPE_CURRENCY)) { 92 Debug.log(Debug.TRACE,"Currency Formula"); 93 } else if(category.equalsIgnoreCase(CELLTYPE_STRING)) { 94 Debug.log(Debug.TRACE,"String Formula"); 95 num[0]=(byte)0x00; 96 num[1]=(byte)0x00; 97 num[2]=(byte)0x00; 98 num[3]=(byte)0x00; 99 num[4]=(byte)0x00; 100 num[5]=(byte)0x00; 101 num[6]=(byte)0xFF; 102 num[7]=(byte)0xFF; 103 } else { 104 Debug.log(Debug.TRACE,"Float Formula"); 105 double cellLong = (double) Double.parseDouble(fmt.getValue()); 106 num = EndianConverter.writeDouble(cellLong); 107 } 108 } 109 110 /** 111 * Translates a <code>String</code> written in infix which represents a 112 * formula into a byte[] what can be written to pocket excel file. 113 * 114 * @param inFormula formula string 115 */ setFormula(String inFormula)116 public void setFormula(String inFormula) throws Exception { 117 118 rgce = fh.convertCalcToPXL(inFormula); 119 cce = EndianConverter.writeShort((short) rgce.length); 120 } 121 122 /** 123 * Constructs a pocket Excel formula from the 124 * <code>InputStream</code> 125 * 126 * @param is InputStream containing a Pocket Excel Data file. 127 */ Formula(InputStream is, Workbook wb)128 public Formula(InputStream is, Workbook wb) throws IOException { 129 read(is); 130 fh.setWorkbook(wb); 131 } 132 133 /** 134 * Get the hex code for this particular <code>BIFFRecord</code> 135 * 136 * @return the hex code for <code>Formula</code> 137 */ getBiffType()138 public short getBiffType() { 139 return PocketExcelConstants.FORMULA_CELL; 140 } 141 142 /** 143 * Reads the formula data members from the stream. Byte arrays for Strings 144 * are doubled as they are stored as unicode 145 * 146 * @return total number of bytes read 147 */ read(InputStream input)148 public int read(InputStream input) throws IOException { 149 150 int numOfBytesRead = super.read(input); 151 152 numOfBytesRead += input.read(num); 153 grbit = (byte) input.read(); 154 numOfBytesRead ++; 155 numOfBytesRead += input.read(cce); 156 157 int strLen = EndianConverter.readShort(cce); 158 rgce = new byte[strLen]; 159 input.read(rgce, 0, strLen); 160 161 Debug.log(Debug.TRACE, " num : " + num + 162 "\n\tgrbit : " + grbit + 163 " cce : " + EndianConverter.readShort(cce) + 164 " rgce : " + new String(rgce,"UTF-16LE") + 165 "\n" + numOfBytesRead + " Bytes Read"); 166 167 return numOfBytesRead; 168 } 169 170 /** 171 * Writes the Formula record to the <code>OutputStream</code> 172 * 173 * @param output the <code>OutputStream</code> being written to 174 */ write(OutputStream output)175 public void write(OutputStream output) throws IOException { 176 177 output.write(getBiffType()); 178 179 super.write(output); 180 181 output.write(num); 182 output.write(grbit); 183 output.write(cce); 184 output.write(rgce); 185 186 Debug.log(Debug.TRACE,"Writing Formula record"); 187 } 188 189 /** 190 * Gets the <code>String</code> representing the cell value 191 * 192 * @return the <code>String</code> representing the cell value 193 */ getValue()194 public String getValue() throws IOException { 195 196 double value = EndianConverter.readDouble(num); 197 Double myDo = new Double(value); 198 return myDo.toString(); 199 } 200 201 /** 202 * Gets the <code>String</code> representing the cells contents 203 * 204 * @return the <code>String</code> representing the cells contents 205 */ getString()206 public String getString() throws IOException { 207 208 return fh.convertPXLToCalc(rgce); 209 } 210 211 /** 212 * Excel dates are the number of days since 1/1/1900. This method converts 213 * to this date. 214 * 215 * @param s String representing a date in the form YYYY-MM-DD 216 * @return The excel serial date 217 */ toExcelSerialDate(String s)218 public long toExcelSerialDate(String s) throws IOException { 219 220 int year = Integer.parseInt(s.substring(0,4)); 221 int month = Integer.parseInt(s.substring(5,7)); 222 int day = Integer.parseInt(s.substring(8,10)); 223 224 long serialDate = (1461 * (year + 4800 + (month - 14) / 12)) / 4 + 225 (367 * (month - 2 - 12 * ((month - 14) / 12))) / 12 - 226 (3 * ((year + 4900 + (month - 14) / 12)) / 100) / 4 + 227 day - 2415019 - 32075; 228 229 return serialDate; 230 } 231 232 /** 233 * Excel times are a fraction of a 24 hour day expressed in seconds. This method converts 234 * to this time. 235 * 236 * @param s String representing a time in the form ??HH?MM?SS? 237 * @return The excel serial time 238 */ toExcelSerialTime(String s)239 public double toExcelSerialTime(String s) throws IOException { 240 241 int hours = Integer.parseInt(s.substring(2,4)); 242 int mins = Integer.parseInt(s.substring(5,7)); 243 int secs = Integer.parseInt(s.substring(8,10)); 244 245 int timeSecs = (hours*3600) + (mins*60) + (secs); 246 247 double d = (double) timeSecs / (24 * 3600); 248 249 return d; 250 } 251 252 } 253