You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ug...@apache.org on 2008/01/25 12:39:33 UTC
svn commit: r615185 - in /poi/branches/ooxml: ./
src/documentation/content/xdocs/ src/documentation/content/xdocs/hssf/
src/java/org/apache/poi/hssf/record/
src/java/org/apache/poi/hssf/record/formula/
src/java/org/apache/poi/hssf/usermodel/ src/scratc...
Author: ugo
Date: Fri Jan 25 03:39:29 2008
New Revision: 615185
URL: http://svn.apache.org/viewvc?rev=615185&view=rev
Log:
Merged revisions 614878-614909 via svnmerge from
https://svn.apache.org/repos/asf/poi/trunk
........
r614878 | nick | 2008-01-24 15:13:05 +0100 (Thu, 24 Jan 2008) | 1 line
Add another formula evaluation method, evaluateFormulaCell(cell), which will re-calculate the value for a formula, without affecting the formula itself. Add tests too, and update the documentation
........
r614909 | nick | 2008-01-24 17:05:27 +0100 (Thu, 24 Jan 2008) | 1 line
From bug #44254 - avoid some unread bytes warnings, and process the contents of DVALRecord
........
Added:
poi/branches/ooxml/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
- copied unchanged from r614909, poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java
Modified:
poi/branches/ooxml/ (props changed)
poi/branches/ooxml/src/documentation/content/xdocs/changes.xml
poi/branches/ooxml/src/documentation/content/xdocs/hssf/eval.xml
poi/branches/ooxml/src/documentation/content/xdocs/status.xml
poi/branches/ooxml/src/java/org/apache/poi/hssf/record/DVALRecord.java
poi/branches/ooxml/src/java/org/apache/poi/hssf/record/UncalcedRecord.java
poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java
poi/branches/ooxml/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
Propchange: poi/branches/ooxml/
------------------------------------------------------------------------------
--- svnmerge-integrated (original)
+++ svnmerge-integrated Fri Jan 25 03:39:29 2008
@@ -1 +1 @@
-/poi/trunk:1-612483,612512,612520,613395-613401,614211,614274,614870
+/poi/trunk:1-612483,612512,612520,613395-613401,614211,614274,614870,614878-614909
Modified: poi/branches/ooxml/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/documentation/content/xdocs/changes.xml?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/documentation/content/xdocs/changes.xml (original)
+++ poi/branches/ooxml/src/documentation/content/xdocs/changes.xml Fri Jan 25 03:39:29 2008
@@ -36,6 +36,8 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44254 - Avoid some unread byte warnings, and properly understand DVALRecord</action>
+ <action dev="POI-DEVELOPERS" type="add">Add another formula evaluation method, evaluateFormulaCell(cell), which will re-calculate the value for a formula, without affecting the formula itself.</action>
<action dev="POI-DEVELOPERS" type="fix">41726 - Fix how we handle signed cell offsets in relative areas and references</action>
<action dev="POI-DEVELOPERS" type="add">44233 - Support for getting and setting a flag on the sheet, which tells excel to re-calculate all formulas on it at next reload</action>
<action dev="POI-DEVELOPERS" type="fix">44201 - Enable cloning of sheets with data validation rules</action>
Modified: poi/branches/ooxml/src/documentation/content/xdocs/hssf/eval.xml
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/documentation/content/xdocs/hssf/eval.xml?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/documentation/content/xdocs/hssf/eval.xml (original)
+++ poi/branches/ooxml/src/documentation/content/xdocs/hssf/eval.xml Fri Jan 25 03:39:29 2008
@@ -55,10 +55,12 @@
<p>The following code demonstrates how to use the HSSFFormulaEvaluator
in the context of other POI excel reading code.
</p>
- <p>There are two ways in which you can use the HSSFFormulaEvalutator API.</p>
+ <p>There are several ways in which you can use the HSSFFormulaEvalutator API.</p>
<anchor id="Evaluate"/>
<section><title>Using HSSFFormulaEvaluator.<strong>evaluate</strong>(HSSFCell cell)</title>
+ <p>This evaluates a given cell, and returns the new value,
+ without affecting the cell</p>
<source>
FileInputStream fis = new FileInputStream("c:/temp/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
@@ -102,12 +104,60 @@
</p>
</section>
+ <anchor id="EvaluateFormulaCell"/>
+ <section><title>Using HSSFFormulaEvaluator.<strong>evaluateFormulaCell</strong>(HSSFCell cell)</title>
+ <p><strong>evaluateFormulaCell</strong>(HSSFCell cell)
+ will check to see if the supplied cell is a formula cell.
+ If it isn't, then no changes will be made to it. If it is,
+ then the formula is evaluated. The value for the formula
+ is saved alongside it, to be displayed in excel. The
+ formula remains in the cell, just with a new value</p>
+ <p>The return of the function is the type of the
+ formula result, such as HSSFCell.CELL_TYPE_BOOLEAN</p>
+ <source>
+FileInputStream fis = new FileInputStream("/somepath/test.xls");
+HSSFWorkbook wb = new HSSFWorkbook(fis);
+HSSFSheet sheet = wb.getSheetAt(0);
+HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
+
+// suppose your formula is in B3
+CellReference cellReference = new CellReference("B3");
+HSSFRow row = sheet.getRow(cellReference.getRow());
+HSSFCell cell = row.getCell(cellReference.getCol());
+evaluator.setCurrentRow(row);
+
+if (cell!=null) {
+ switch (<strong>evaluator.evaluateFormulaCell</strong>(cell)) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ System.out.println(cell.getBooleanCellValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ System.out.println(cell.getNumberCellValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ System.out.println(cell.getStringCellValue());
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ System.out.println(cell.getErrorCellValue());
+ break;
+
+ // CELL_TYPE_FORMULA will never occur
+ case HSSFCell.CELL_TYPE_FORMULA:
+ break;
+ }
+}
+ </source>
+ </section>
+
<anchor id="EvaluateInCell"/>
<section><title>Using HSSFFormulaEvaluator.<strong>evaluateInCell</strong>(HSSFCell cell)</title>
<p><strong>evaluateInCell</strong>(HSSFCell cell) will check to
see if the supplied cell is a formula cell. If it isn't,
then no changes will be made to it. If it is, then the
- formula is evaluated, and the new value saved into the cell.</p>
+ formula is evaluated, and the new value saved into the cell,
+ in place of the old formula.</p>
<source>
FileInputStream fis = new FileInputStream("/somepath/test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fis);
@@ -154,14 +204,14 @@
HSSFSheet sheet = wb.getSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
- for(Iterator rit = s.rowIterator(); rit.hasNext();) {
+ for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
HSSFRow r = (HSSFRow)rit.next();
evaluator.setCurrentRow(r);
for(Iterator cit = r.cellIterator(); cit.hasNext();) {
HSSFCell c = (HSSFCell)cit.next();
if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
- evaluator.evaluateInCell(c);
+ evaluator.evaluateFormulaCell(c);
}
}
}
Modified: poi/branches/ooxml/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/documentation/content/xdocs/status.xml?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/documentation/content/xdocs/status.xml (original)
+++ poi/branches/ooxml/src/documentation/content/xdocs/status.xml Fri Jan 25 03:39:29 2008
@@ -33,6 +33,8 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44254 - Avoid some unread byte warnings, and properly understand DVALRecord</action>
+ <action dev="POI-DEVELOPERS" type="add">Add another formula evaluation method, evaluateFormulaCell(cell), which will re-calculate the value for a formula, without affecting the formula itself.</action>
<action dev="POI-DEVELOPERS" type="fix">41726 - Fix how we handle signed cell offsets in relative areas and references</action>
<action dev="POI-DEVELOPERS" type="add">44233 - Support for getting and setting a flag on the sheet, which tells excel to re-calculate all formulas on it at next reload</action>
<action dev="POI-DEVELOPERS" type="fix">44201 - Enable cloning of sheets with data validation rules</action>
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/DVALRecord.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/DVALRecord.java?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/DVALRecord.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/DVALRecord.java Fri Jan 25 03:39:29 2008
@@ -29,19 +29,22 @@
public class DVALRecord extends Record
{
- public final static short sid = 0x01B2;
+ public final static short sid = 0x01B2;
- //unknown field ; it's size should be 10
- private short field_unknown = 0x0000;
+ /** Options of the DVAL */
+ private short field_1_options;
+ /** Horizontal position of the dialog */
+ private int field_2_horiz_pos;
+ /** Vertical position of the dialog */
+ private int field_3_vert_pos;
+
+ /** Object ID of the drop down arrow object for list boxes ;
+ * in our case this will be always FFFF , until
+ * MSODrawingGroup and MSODrawing records are implemented */
+ private int field_cbo_id = 0xFFFFFFFF;
- //Object ID of the drop down arrow object for list boxes ;
- //in our case this will be always FFFF , until
- //MSODrawingGroup and MSODrawing records are implemented
- private int field_cbo_id = 0xFFFFFFFF;
-
- //Number of following DV records
- //Default value is 1
- private int field_3_dv_no = 0x00000000;
+ /** Number of following DV Records */
+ private int field_5_dv_no = 0x00000000;
public DVALRecord()
{
@@ -66,17 +69,38 @@
}
}
- protected void fillFields(RecordInputStream in)
- {
- for ( int i=0; i<5; i++)
- {
- this.field_unknown = in.readShort();
- }
+ protected void fillFields(RecordInputStream in)
+ {
+ this.field_1_options = in.readShort();
+ this.field_2_horiz_pos = in.readInt();
+ this.field_3_vert_pos = in.readInt();
this.field_cbo_id = in.readInt();
- this.field_3_dv_no = in.readInt();
- }
+ this.field_5_dv_no = in.readInt();
+ }
+
/**
+ * @param field_1_options the options of the dialog
+ */
+ public void setOptions(short field_1_options) {
+ this.field_1_options = field_1_options;
+ }
+
+ /**
+ * @param field_2_horiz_pos the Horizontal position of the dialog
+ */
+ public void setHorizontalPos(int field_2_horiz_pos) {
+ this.field_2_horiz_pos = field_2_horiz_pos;
+ }
+
+ /**
+ * @param field_3_vert_pos the Vertical position of the dialog
+ */
+ public void setVerticalPos(int field_3_vert_pos) {
+ this.field_3_vert_pos = field_3_vert_pos;
+ }
+
+ /**
* set the object ID of the drop down arrow object for list boxes
* @param cboID - Object ID
*/
@@ -91,10 +115,33 @@
*/
public void setDVRecNo(int dvNo)
{
- this.field_3_dv_no = dvNo;
+ this.field_5_dv_no = dvNo;
}
+
+
/**
+ * @return the field_1_options
+ */
+ public short getOptions() {
+ return field_1_options;
+ }
+
+ /**
+ * @return the Horizontal position of the dialog
+ */
+ public int getHorizontalPos() {
+ return field_2_horiz_pos;
+ }
+
+ /**
+ * @return the the Vertical position of the dialog
+ */
+ public int getVerticalPos() {
+ return field_3_vert_pos;
+ }
+
+ /**
* get Object ID of the drop down arrow object for list boxes
*/
public int getObjectID( )
@@ -107,29 +154,32 @@
*/
public int getDVRecNo( )
{
- return this.field_3_dv_no;
+ return this.field_5_dv_no;
}
- public String toString()
- {
- StringBuffer buffer = new StringBuffer();
-
- buffer.append("[DVAL]\n");
- buffer.append(" .comboObjectID = ").append(Integer.toHexString(this.getObjectID())).append("\n");
- buffer.append(" .DVRecordsNumber = ").append(Integer.toHexString(this.getDVRecNo())).append("\n");
- buffer.append("[/DVAL]\n");
- return buffer.toString();
- }
+ public String toString()
+ {
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append("[DVAL]\n");
+ buffer.append(" .options = ").append(this.getOptions()).append('\n');
+ buffer.append(" .horizPos = ").append(this.getHorizontalPos()).append('\n');
+ buffer.append(" .vertPos = ").append(this.getVerticalPos()).append('\n');
+ buffer.append(" .comboObjectID = ").append(Integer.toHexString(this.getObjectID())).append("\n");
+ buffer.append(" .DVRecordsNumber = ").append(Integer.toHexString(this.getDVRecNo())).append("\n");
+ buffer.append("[/DVAL]\n");
+ return buffer.toString();
+ }
public int serialize(int offset, byte [] data)
{
LittleEndian.putShort(data, 0 + offset, this.sid);
LittleEndian.putShort(data, 2 + offset, ( short)(this.getRecordSize()-4));
- for ( int i=0; i<5; i++)
- {
- LittleEndian.putShort(data, 4 + i*2 + offset, (short)this.field_unknown);
- }
+
+ LittleEndian.putShort(data, 4 + offset, this.getOptions());
+ LittleEndian.putInt(data, 6 + offset, this.getHorizontalPos());
+ LittleEndian.putInt(data, 10 + offset, this.getVerticalPos());
LittleEndian.putInt(data, 14 + offset, this.getObjectID());
LittleEndian.putInt(data, 18 + offset, this.getDVRecNo());
return getRecordSize();
@@ -149,9 +199,11 @@
public Object clone()
{
DVALRecord rec = new DVALRecord();
- rec.field_unknown = this.field_unknown;
+ rec.field_1_options = field_1_options;
+ rec.field_2_horiz_pos = field_2_horiz_pos;
+ rec.field_3_vert_pos = field_3_vert_pos;
rec.field_cbo_id = this.field_cbo_id;
- rec.field_3_dv_no = this.field_3_dv_no;
+ rec.field_5_dv_no = this.field_5_dv_no;
return rec;
}
-}
\ No newline at end of file
+}
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/UncalcedRecord.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/UncalcedRecord.java?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/UncalcedRecord.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/UncalcedRecord.java Fri Jan 25 03:39:29 2008
@@ -55,6 +55,7 @@
}
protected void fillFields(RecordInputStream in) {
+ short unused = in.readShort();
}
public String toString() {
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/record/formula/ErrPtg.java Fri Jan 25 03:39:29 2008
@@ -29,7 +29,7 @@
public class ErrPtg extends Ptg
{
public static final short sid = 0x1c;
- private static final int SIZE = 7;
+ private static final int SIZE = 2;
private byte field_1_error_code;
/** Creates new ErrPtg */
Modified: poi/branches/ooxml/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/branches/ooxml/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Fri Jan 25 03:39:29 2008
@@ -538,7 +538,13 @@
{
setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
}
- (( NumberRecord ) record).setValue(value);
+
+ // Save into the apropriate record
+ if(record instanceof FormulaRecordAggregate) {
+ (( FormulaRecordAggregate ) record).getFormulaRecord().setValue(value);
+ } else {
+ (( NumberRecord ) record).setValue(value);
+ }
}
/**
Modified: poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=615185&r1=615184&r2=615185&view=diff
==============================================================================
--- poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/branches/ooxml/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Fri Jan 25 03:39:29 2008
@@ -217,14 +217,66 @@
/**
- * If cell contains formula, it evaluates the formula, and puts the
- * formula result back into the cell.
- * Else if cell does not contain formula, this method leaves the cell
- * unchanged. Note that the same instance of HSSFCell is returned to
+ * If cell contains formula, it evaluates the formula,
+ * and saves the result of the formula. The cell
+ * remains as a formula cell.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the type of the formula result is returned,
+ * so you know what kind of value is also stored with
+ * the formula.
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
+ * </pre>
+ * Be aware that your cell will hold both the formula,
+ * and the result. If you want the cell replaced with
+ * the result of the formula, use {@link #evaluateInCell(HSSFCell)}
+ * @param cell The cell to evaluate
+ * @return The type of the formula result (the cell's type remains as HSSFCell.CELL_TYPE_FORMULA however)
+ */
+ public int evaluateFormulaCell(HSSFCell cell) {
+ if (cell != null) {
+ switch (cell.getCellType()) {
+ case HSSFCell.CELL_TYPE_FORMULA:
+ CellValue cv = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
+ switch (cv.getCellType()) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ cell.setCellValue(cv.getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ cell.setCellValue(cv.getErrorValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ cell.setCellValue(cv.getNumberValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellValue(cv.getRichTextStringValue());
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ break;
+ case HSSFCell.CELL_TYPE_FORMULA: // this will never happen, we have already evaluated the formula
+ break;
+ }
+ return cv.getCellType();
+ }
+ }
+ return -1;
+ }
+
+ /**
+ * If cell contains formula, it evaluates the formula, and
+ * puts the formula result back into the cell, in place
+ * of the old formula.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the same instance of HSSFCell is returned to
* allow chained calls like:
* <pre>
* int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
* </pre>
+ * Be aware that your cell value will be changed to hold the
+ * result of the formula. If you simply want the formula
+ * value computed for you, use {@link #evaluateFormulaCell(HSSFCell)}
* @param cell
*/
public HSSFCell evaluateInCell(HSSFCell cell) {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org