Index: java/org/apache/poi/hssf/record/FormulaRecord.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/FormulaRecord.java,v --- java/org/apache/poi/hssf/record/FormulaRecord.java 27 Jul 2003 18:49:52 -0000 1.23 +++ java/org/apache/poi/hssf/record/FormulaRecord.java 3 Oct 2003 06:44:12 -0000 @@ -64,6 +64,7 @@ import java.util.Stack; import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.util.BitField; import org.apache.poi.util.LittleEndian; /** @@ -90,6 +91,9 @@ private short field_3_xf; private double field_4_value; private short field_5_options; + private BitField alwaysCalc = new BitField(0x0001); + private BitField calcOnLoad = new BitField(0x0002); + private BitField sharedFormula = new BitField(0x0008); private int field_6_zero; private short field_7_expression_len; private Stack field_8_parsed_expr; @@ -271,6 +275,10 @@ return field_5_options; } + public boolean isSharedFormula() { + return sharedFormula.isSet(field_5_options); + } + /** * get the length (in number of tokens) of the expression * @return expression length @@ -573,6 +581,12 @@ buffer.append(" .value = ").append(getValue()) .append("\n"); buffer.append(" .options = ").append(getOptions()) + .append("\n"); + buffer.append(" .alwaysCalc = ").append(alwaysCalc.isSet(getOptions())) + .append("\n"); + buffer.append(" .calcOnLoad = ").append(calcOnLoad.isSet(getOptions())) + .append("\n"); + buffer.append(" .sharedFormula = ").append(sharedFormula.isSet(getOptions())) .append("\n"); buffer.append(" .zero = ").append(field_6_zero) .append("\n"); Index: java/org/apache/poi/hssf/record/SharedFormulaRecord.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/SharedFormulaRecord.java,v --- java/org/apache/poi/hssf/record/SharedFormulaRecord.java 19 Jul 2003 14:56:36 -0000 1.2 +++ java/org/apache/poi/hssf/record/SharedFormulaRecord.java 3 Oct 2003 06:44:12 -0000 @@ -56,6 +56,10 @@ package org.apache.poi.hssf.record; import org.apache.poi.util.LittleEndian; +import org.apache.poi.hssf.record.formula.*; + +import java.util.Stack; +import java.util.List; /** * Title: SharedFormulaRecord @@ -77,6 +81,15 @@ private byte[] thedata = null; int offset = 0; + private int field_1_first_row; + private int field_2_last_row; + private short field_3_first_column; + private short field_4_last_column; + private int field_5_reserved; + private short field_6_expression_len; + private Stack field_7_parsed_expr; + + public SharedFormulaRecord() { } @@ -125,6 +138,27 @@ return retval; } + public int getFirstRow() { + return field_1_first_row; + } + + public int getLastRow() { + return field_2_last_row; + } + + public short getFirstColumn() { + return field_3_first_column; + } + + public short getLastColumn() { + return field_4_last_column; + } + + public short getExpressionLength() + { + return field_6_expression_len; + } + protected void validateSid(short id) { @@ -146,6 +180,33 @@ buffer.append("[SHARED FORMULA RECORD:" + Integer.toHexString(sid) + "]\n"); buffer.append(" .id = ").append(Integer.toHexString(sid)) .append("\n"); + buffer.append(" .first_row = ") + .append(Integer.toHexString(getFirstRow())).append("\n"); + buffer.append(" .last_row = ") + .append(Integer.toHexString(getLastRow())) + .append("\n"); + buffer.append(" .first_column = ") + .append(Integer.toHexString(getFirstColumn())).append("\n"); + buffer.append(" .last_column = ") + .append(Integer.toHexString(getLastColumn())) + .append("\n"); + buffer.append(" .reserved = ") + .append(Integer.toHexString(field_5_reserved)) + .append("\n"); + buffer.append(" .expressionlength= ").append(getExpressionLength()) + .append("\n"); + + buffer.append(" .numptgsinarray = ").append(field_7_parsed_expr.size()) + .append("\n"); + + for (int k = 0; k < field_7_parsed_expr.size(); k++ ) { + buffer.append("Formula ") + .append(k) + .append("\n") + .append(field_7_parsed_expr.get(k).toString()) + .append("\n"); + } + buffer.append("[/SHARED FORMULA RECORD]\n"); return buffer.toString(); } @@ -160,10 +221,110 @@ */ protected void fillFields(byte [] data, short size, int offset) { + //Hold onto the raw data until this record is fully implemented. thedata = new byte[size]; System.arraycopy(data, 0, thedata, 0, size); + field_1_first_row = LittleEndian.getShort(data, 0 + offset); + field_2_last_row = LittleEndian.getShort(data, 2 + offset); + field_3_first_column = data[4 + offset]; + field_4_last_column = data[5 + offset]; + field_5_reserved = LittleEndian.getShort(data, 6 + offset); + field_6_expression_len = LittleEndian.getShort(data, 8 + offset); + field_7_parsed_expr = getParsedExpressionTokens(data, size, + 10 + offset); + } + + private Stack getParsedExpressionTokens(byte [] data, short size, + int offset) + { + Stack stack = new Stack(); + int pos = offset; + + while (pos < size) + { + Ptg ptg = Ptg.createPtg(data, pos); + pos += ptg.getSize(); + stack.push(ptg); + } + return stack; + } + + public boolean isFormulaInShared(FormulaRecord formula) { + final int formulaRow = formula.getRow(); + final int formulaColumn = formula.getColumn(); + return ((getFirstRow() <= formulaRow) && (getLastRow() >= formulaRow) && + (getFirstColumn() <= formulaColumn) && (getLastColumn() >= formulaColumn)); + } + + /** Creates a non shared formula from the shared formula counter part*/ + public void convertSharedFormulaRecord(FormulaRecord formula) { + //Sanity checks + final int formulaRow = formula.getRow(); + final int formulaColumn = formula.getColumn(); + if (isFormulaInShared(formula)) { + formula.setExpressionLength(getExpressionLength()); + List newPtgStack = new Stack(); + + for (int k = 0; k < field_7_parsed_expr.size(); k++) { + Ptg ptg = (Ptg) field_7_parsed_expr.get(k); + if (ptg instanceof RefNPtg) { + RefNPtg refNPtg = (RefNPtg)ptg; + ptg = new ReferencePtg( (short)(formulaRow + refNPtg.getRow()), + (byte)(formulaColumn + refNPtg.getColumn()), + refNPtg.isRowRelative(), + refNPtg.isColRelative()); + } else if (ptg instanceof RefNVPtg) { + RefNVPtg refNVPtg = (RefNVPtg)ptg; + ptg = new RefVPtg( (short)(formulaRow + refNVPtg.getRow()), + (byte)(formulaColumn + refNVPtg.getColumn()), + refNVPtg.isRowRelative(), + refNVPtg.isColRelative()); + } else if (ptg instanceof RefNAPtg) { + RefNAPtg refNAPtg = (RefNAPtg)ptg; + ptg = new RefAPtg( (short)(formulaRow + refNAPtg.getRow()), + (byte)(formulaColumn + refNAPtg.getColumn()), + refNAPtg.isRowRelative(), + refNAPtg.isColRelative()); + } else if (ptg instanceof AreaNPtg) { + AreaNPtg areaNPtg = (AreaNPtg)ptg; + ptg = new AreaPtg((short)(formulaRow + areaNPtg.getFirstRow()), + (short)(formulaRow + areaNPtg.getLastRow()), + (short)(formulaColumn + areaNPtg.getFirstColumn()), + (short)(formulaColumn + areaNPtg.getLastColumn()), + areaNPtg.isFirstRowRelative(), + areaNPtg.isLastRowRelative(), + areaNPtg.isFirstColRelative(), + areaNPtg.isLastColRelative()); + } else if (ptg instanceof AreaNVPtg) { + AreaNVPtg areaNVPtg = (AreaNVPtg)ptg; + ptg = new AreaVPtg((short)(formulaRow + areaNVPtg.getFirstRow()), + (short)(formulaRow + areaNVPtg.getLastRow()), + (short)(formulaColumn + areaNVPtg.getFirstColumn()), + (short)(formulaColumn + areaNVPtg.getLastColumn()), + areaNVPtg.isFirstRowRelative(), + areaNVPtg.isLastRowRelative(), + areaNVPtg.isFirstColRelative(), + areaNVPtg.isLastColRelative()); + } else if (ptg instanceof AreaNAPtg) { + AreaNAPtg areaNAPtg = (AreaNAPtg)ptg; + ptg = new AreaAPtg((short)(formulaRow + areaNAPtg.getFirstRow()), + (short)(formulaRow + areaNAPtg.getLastRow()), + (short)(formulaColumn + areaNAPtg.getFirstColumn()), + (short)(formulaColumn + areaNAPtg.getLastColumn()), + areaNAPtg.isFirstRowRelative(), + areaNAPtg.isLastRowRelative(), + areaNAPtg.isFirstColRelative(), + areaNAPtg.isLastColRelative()); + } + newPtgStack.add(ptg); + } + formula.setParsedExpression(newPtgStack); + } else { + throw new RuntimeException("Coding Error"); + } } + /** * Mirroring formula records so it is registered in the ValueRecordsAggregate Index: java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java,v --- java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java 21 Sep 2003 22:11:56 -0000 1.13 +++ java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java 3 Oct 2003 06:44:13 -0000 @@ -184,6 +184,7 @@ int k = 0; FormulaRecordAggregate lastFormulaAggregate = null; + SharedFormulaRecord lastSharedFormula = null; for (k = offset; k < records.size(); k++) { @@ -195,6 +196,28 @@ } if (rec instanceof FormulaRecord) { + FormulaRecord formula = (FormulaRecord)rec; + if (formula.isSharedFormula()) { + if ((lastSharedFormula != null) && (lastSharedFormula.isFormulaInShared(formula))) { + //Convert this Formula Record from a shared formula to a real formula + lastSharedFormula.convertSharedFormulaRecord(formula); + } else { + Record nextRecord = (Record) records.get(k + 1); + if (nextRecord instanceof SharedFormulaRecord) { + k++; + lastSharedFormula = (SharedFormulaRecord) nextRecord; + + //Convert this Formula Record from a shared formula to a real formula + lastSharedFormula.convertSharedFormulaRecord(formula); + } + else + throw new RuntimeException( + "Shared formula bit set but next record not Shared Formula??"); + } + } else { + //JMH TBD next record could also be ARRAY record. + } + lastFormulaAggregate = new FormulaRecordAggregate((FormulaRecord)rec, null); insertCell( lastFormulaAggregate ); } @@ -202,9 +225,9 @@ { lastFormulaAggregate.setStringRecord((StringRecord)rec); } - else if (rec instanceof SharedFormulaRecord) { - lastFormulaAggregate.setSharedFormulaRecord((SharedFormulaRecord)rec); - } +// else if (rec instanceof SharedFormulaRecord) { +// lastFormulaAggregate.setSharedFormulaRecord((SharedFormulaRecord)rec); +// } else if (rec.isValue()) { insertCell(( CellValueRecordInterface ) rec); Index: java/org/apache/poi/hssf/record/formula/AreaPtg.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java,v --- java/org/apache/poi/hssf/record/formula/AreaPtg.java 31 Aug 2003 06:16:56 -0000 1.16 +++ java/org/apache/poi/hssf/record/formula/AreaPtg.java 3 Oct 2003 06:44:13 -0000 @@ -87,10 +87,21 @@ private BitField colRelative = new BitField(0x4000); private BitField column = new BitField(0x3FFF); - private AreaPtg() { + protected AreaPtg() { //Required for clone methods } + public AreaPtg(short firstRow, short lastRow, short firstColumn, short lastColumn, boolean firstRowRelative, boolean lastRowRelative, boolean firstColRelative, boolean lastColRelative) { + setFirstRow(firstRow); + setLastRow(lastRow); + setFirstColumn(firstColumn); + setLastColumn(lastColumn); + setFirstRowRelative(firstRowRelative); + setLastRowRelative(lastRowRelative); + setFirstColRelative(firstColRelative); + setLastColRelative(lastColRelative); + } + public AreaPtg(String arearef) { AreaReference ar = new AreaReference(arearef); setFirstRow((short)ar.getCells()[0].getRow()); @@ -114,11 +125,16 @@ //System.out.println(toString()); } + public String getAreaPtgName() { + return "AreaPtg"; + } + public String toString() { StringBuffer buffer = new StringBuffer(); - buffer.append("AreaPtg\n"); + buffer.append(getAreaPtgName()); + buffer.append("\n"); buffer.append("firstRow = " + getFirstRow()).append("\n"); buffer.append("lastRow = " + getLastRow()).append("\n"); buffer.append("firstCol = " + getFirstColumn()).append("\n"); Index: java/org/apache/poi/hssf/record/formula/ExpPtg.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/ExpPtg.java,v --- java/org/apache/poi/hssf/record/formula/ExpPtg.java 31 Aug 2003 06:16:56 -0000 1.9 +++ java/org/apache/poi/hssf/record/formula/ExpPtg.java 3 Oct 2003 06:44:13 -0000 @@ -61,6 +61,7 @@ package org.apache.poi.hssf.record.formula; import org.apache.poi.hssf.model.Workbook; +import org.apache.poi.util.LittleEndian; /** * @@ -74,6 +75,8 @@ { private final static int SIZE = 5; public final static short sid = 0x1; + private short field_1_row; + private short field_2_col; private byte[] existing = null; /** Creates new ExpPtg */ @@ -86,15 +89,22 @@ public ExpPtg(byte [] array, int offset) { - existing = new byte[this.getSize()]; - System.arraycopy(array, offset, existing, 0, this.getSize()); + offset++; + + field_1_row = LittleEndian.getShort(array, offset + 0 ); + field_2_col = LittleEndian.getShort(array, offset + 2 ); +// existing = new byte[this.getSize()]; +// System.arraycopy(array, offset, existing, 0, this.getSize()); } public void writeBytes(byte [] array, int offset) { - if (existing != null) { - System.arraycopy(existing, 0, array, offset, existing.length); - } +// if (existing != null) { +// System.arraycopy(existing, 0, array, offset, existing.length); +// } + array[offset+0]= (byte) (sid + ptgClass); + LittleEndian.putShort(array, offset+1, field_1_row); + LittleEndian.putShort(array, offset+3, field_2_col); } public int getSize() @@ -102,11 +112,29 @@ return SIZE; } + public short getRow() { + return field_1_row; + } + + public short getColumn() { + return field_2_col; + } + public String toFormulaString(Workbook book) { return "NO IDEA SHARED FORMULA EXP PTG"; } + public String toString() + { + StringBuffer buffer = new StringBuffer("[Array Formula or Shared Formula]\n"); + + buffer.append("row = ").append(getRow()).append("\n"); + buffer.append("col = ").append(getColumn()).append("\n"); + return buffer.toString(); + } + + public byte getDefaultOperandClass() {return Ptg.CLASS_VALUE;} public Object clone() { Index: java/org/apache/poi/hssf/record/formula/Ptg.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/Ptg.java,v --- java/org/apache/poi/hssf/record/formula/Ptg.java 31 Aug 2003 06:16:56 -0000 1.32 +++ java/org/apache/poi/hssf/record/formula/Ptg.java 3 Oct 2003 06:44:13 -0000 @@ -134,14 +134,10 @@ byte id = data[ offset + 0 ]; Ptg retval = null; - final byte valueRef = ReferencePtg.sid + 0x20; - final byte arrayRef = ReferencePtg.sid + 0x40; final byte valueFunc = FuncPtg.sid + 0x20; final byte arrayFunc = FuncPtg.sid + 0x40; final byte valueFuncVar = FuncVarPtg.sid +0x20; final byte arrayFuncVar = FuncVarPtg.sid+0x40; - final byte valueArea = AreaPtg.sid + 0x20; - final byte arrayArea = AreaPtg.sid + 0x40; switch (id) { @@ -204,11 +200,20 @@ case AreaPtg.sid : retval = new AreaPtg(data, offset); break; - case valueArea: - retval = new AreaPtg(data, offset); + case AreaAPtg.sid : + retval = new AreaAPtg(data, offset); break; - case arrayArea: - retval = new AreaPtg(data, offset); + case AreaVPtg.sid : + retval = new AreaVPtg(data, offset); + break; + case AreaNAPtg.sid : + retval = new AreaNAPtg(data, offset); + break; + case AreaNPtg.sid : + retval = new AreaNPtg(data, offset); + break; + case AreaNVPtg.sid : + retval = new AreaNVPtg(data, offset); break; case MemErrPtg.sid : // 0x27 These 3 values case MemErrPtg.sid+0x20 : // 0x47 documented in @@ -223,11 +228,21 @@ case ReferencePtg.sid : retval = new ReferencePtg(data, offset); break; - case valueRef : - retval = new ReferencePtg(data, offset); + case RefAPtg.sid : + retval = new RefAPtg(data, offset); break; - case arrayRef : - retval = new ReferencePtg(data, offset); + case RefVPtg.sid : + retval = new RefVPtg(data, offset); + break; + + case RefNAPtg.sid : + retval = new RefNAPtg(data, offset); + break; + case RefNPtg.sid : + retval = new RefNPtg(data, offset); + break; + case RefNVPtg.sid : + retval = new RefNVPtg(data, offset); break; case ParenthesisPtg.sid : Index: java/org/apache/poi/hssf/record/formula/ReferencePtg.java =================================================================== RCS file: /home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java,v --- java/org/apache/poi/hssf/record/formula/ReferencePtg.java 31 Aug 2003 06:16:56 -0000 1.10 +++ java/org/apache/poi/hssf/record/formula/ReferencePtg.java 3 Oct 2003 06:44:13 -0000 @@ -81,8 +81,9 @@ private short field_2_col; private BitField rowRelative = new BitField(0x8000); private BitField colRelative = new BitField(0x4000); + private BitField column = new BitField(0x3FFF); - private ReferencePtg() { + protected ReferencePtg() { //Required for clone methods } @@ -98,6 +99,13 @@ setRowRelative(!c.isRowAbsolute()); } + public ReferencePtg(short row, short column, boolean isRowRelative, boolean isColumnRelative) { + setRow(row); + setColumn(column); + setRowRelative(isRowRelative); + setColRelative(isColumnRelative); + } + /** Creates new ValueReferencePtg */ public ReferencePtg(byte[] data, int offset) @@ -105,15 +113,20 @@ offset++; // adjust for ptg field_1_row = LittleEndian.getShort(data, offset + 0); field_2_col = LittleEndian.getShort(data, offset + 2); + } + public String getRefPtgName() { + return "ReferencePtg"; } public String toString() { - StringBuffer buffer = new StringBuffer("[ValueReferencePtg]\n"); + StringBuffer buffer = new StringBuffer("["); + buffer.append(getRefPtgName()); + buffer.append("]\n"); buffer.append("row = ").append(getRow()).append("\n"); - buffer.append("col = ").append(getColumnRaw()).append("\n"); + buffer.append("col = ").append(getColumn()).append("\n"); buffer.append("rowrelative = ").append(isRowRelative()).append("\n"); buffer.append("colrelative = ").append(isColRelative()).append("\n"); return buffer.toString(); @@ -166,12 +179,12 @@ public void setColumn(short col) { - field_2_col = col; // fix this + field_2_col = column.setShortValue(field_2_col, col); } public short getColumn() { - return rowRelative.setShortBoolean(colRelative.setShortBoolean(field_2_col,false),false); + return column.getShortValue(field_2_col); } public int getSize()