diff --git a/src/java/org/apache/poi/hssf/record/ArrayRecord.java b/src/java/org/apache/poi/hssf/record/ArrayRecord.java
index 0cf6f44..4832126 100644
--- a/src/java/org/apache/poi/hssf/record/ArrayRecord.java
+++ b/src/java/org/apache/poi/hssf/record/ArrayRecord.java
@@ -17,7 +17,10 @@
package org.apache.poi.hssf.record;
+import org.apache.poi.hssf.record.formula.AreaPtgBase;
import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.RefPtgBase;
+import org.apache.poi.hssf.util.CellRangeAddress8Bit;
import org.apache.poi.ss.formula.Formula;
import org.apache.poi.util.HexDump;
import org.apache.poi.util.LittleEndianOutput;
@@ -28,6 +31,7 @@ import org.apache.poi.util.LittleEndianOutput;
* Treated in a similar way to SharedFormulaRecord
*
* @author Josh Micich
+ * @author vabramovs(VIA) - Array Formula support
*/
public final class ArrayRecord extends SharedValueRecordBase {
@@ -35,7 +39,7 @@ public final class ArrayRecord extends SharedValueRecordBase {
private static final int OPT_ALWAYS_RECALCULATE = 0x0001;
private static final int OPT_CALCULATE_ON_OPEN = 0x0002;
- private int _options;
+ private int _options;
private int _field3notUsed;
private Formula _formula;
@@ -48,6 +52,13 @@ public final class ArrayRecord extends SharedValueRecordBase {
_formula = Formula.read(formulaTokenLen, in, totalFormulaLen);
}
+ public ArrayRecord(Formula formula, CellRangeAddress8Bit range ) {
+ super(range);
+ _options = OPT_CALCULATE_ON_OPEN;
+ _field3notUsed = 0;
+ _formula = formula;
+ }
+
public boolean isAlwaysRecalculate() {
return (_options & OPT_ALWAYS_RECALCULATE) != 0;
}
@@ -84,4 +95,37 @@ public final class ArrayRecord extends SharedValueRecordBase {
sb.append("]");
return sb.toString();
}
+
+ /**
+ * @return the equivalent {@link Ptg} array that the formula would have,
+ * were it not shared.
+ */
+ public Ptg[] getFormulaTokens() {
+ int formulaRow = this.getFirstRow();
+ int formulaColumn = this.getLastColumn();
+
+ // Use SharedFormulaRecord static method to convert formula
+
+ Ptg[] ptgs = _formula.getTokens();
+
+ // Convert from relative addressing to absolute
+ // because all formulas in array need to be referenced to the same
+ // ref/range
+ for (int i = 0; i < ptgs.length; i++) {
+ Ptg ptg = ptgs[i];
+ if (ptg instanceof AreaPtgBase) {
+ AreaPtgBase aptg = (AreaPtgBase) ptg;
+ aptg.setFirstRowRelative(false);
+ aptg.setLastRowRelative(false);
+ aptg.setFirstColRelative(false);
+ aptg.setLastColRelative(false);
+
+ } else if (ptg instanceof RefPtgBase) {
+ RefPtgBase rptg = (RefPtgBase) ptg;
+ rptg.setRowRelative(false);
+ rptg.setColRelative(false);
+ }
+ }
+ return SharedFormulaRecord.convertSharedFormulas(ptgs, formulaRow, formulaColumn);
+ }
}
diff --git a/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java b/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
index 0f4e976..60a9220 100644
--- a/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
+++ b/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
@@ -17,6 +17,7 @@
package org.apache.poi.hssf.record.aggregates;
+import org.apache.poi.hssf.record.ArrayRecord;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.Record;
@@ -25,13 +26,17 @@ import org.apache.poi.hssf.record.SharedFormulaRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.record.formula.ExpPtg;
import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.util.CellRangeAddress8Bit;
import org.apache.poi.hssf.util.CellReference;
+import org.apache.poi.ss.formula.Formula;
+import org.apache.poi.ss.util.CellRangeAddress;
/**
* The formula record aggregate is used to join together the formula record and it's
* (optional) string record and (optional) Shared Formula Record (template reads, excel optimization).
*
* @author Glen Stampoultzis (glens at apache.org)
+ * @author vabramovs(VIA) - Array Formula support
*/
public final class FormulaRecordAggregate extends RecordAggregate implements CellValueRecordInterface {
@@ -181,10 +186,15 @@ public final class FormulaRecordAggregate extends RecordAggregate implements Cel
}
public Ptg[] getFormulaTokens() {
- if (_sharedFormulaRecord == null) {
- return _formulaRecord.getParsedExpression();
+ if (_sharedFormulaRecord != null) {
+ return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);
+ }
+ CellReference expRef = _formulaRecord.getFormula().getExpReference();
+ if (expRef != null) {
+ ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
+ return arec.getFormulaTokens();
}
- return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);
+ return _formulaRecord.getParsedExpression();
}
/**
@@ -216,4 +226,36 @@ public final class FormulaRecordAggregate extends RecordAggregate implements Cel
_sharedValueManager.unlink(_sharedFormulaRecord);
}
}
+ public boolean isPartOfArrayFormula() {
+ if (_sharedFormulaRecord != null) {
+ return false;
+ }
+ return _formulaRecord.getFormula().getExpReference() != null;
+ }
+
+ public CellRangeAddress getArrayFormulaRange() {
+ if (_sharedFormulaRecord != null) {
+ throw new IllegalStateException("not an array formula cell.");
+ }
+ CellReference expRef = _formulaRecord.getFormula().getExpReference();
+ if (expRef == null) {
+ throw new IllegalStateException("not an array formula cell.");
+ }
+ ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
+ CellRangeAddress8Bit a = arec.getRange();
+ return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(),a.getLastColumn());
+ }
+ public void setArrayFormula(CellRangeAddress r, Ptg[] ptgs) {
+
+ ArrayRecord arr = new ArrayRecord(Formula.create(ptgs), new CellRangeAddress8Bit(r.getFirstRow(), r.getLastRow(), r.getFirstColumn(), r.getLastColumn()));
+ _sharedValueManager.addArrayRecord(arr);
+ }
+ /**
+ * Removes an array formula
+ * @return the range of the array formula containing the specified cell. Never null
+ */
+ public CellRangeAddress removeArrayFormula(int rowIndex, int columnIndex) {
+ CellRangeAddress8Bit a = _sharedValueManager.removeArrayFormula(rowIndex, columnIndex);
+ return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(), a.getLastColumn());
+ }
}
diff --git a/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java b/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java
index 8c4d19d..1e0ab0a 100644
--- a/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java
+++ b/src/java/org/apache/poi/hssf/record/aggregates/SharedValueManager.java
@@ -6,7 +6,7 @@
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
- http://www.apache.org/licenses/LICENSE-2.0
+ http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
@@ -17,9 +17,11 @@
package org.apache.poi.hssf.record.aggregates;
+import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
+import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.record.ArrayRecord;
@@ -41,6 +43,7 @@ import org.apache.poi.ss.util.CellReference;
*
*
* @author Josh Micich
+ * @author vabramovs(VIA) add getArray
*/
public final class SharedValueManager {
@@ -111,7 +114,7 @@ public final class SharedValueManager {
public static final SharedValueManager EMPTY = new SharedValueManager(
new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]);
- private final ArrayRecord[] _arrayRecords;
+ private final Listnull
.
+ */
+ public CellRangeAddress8Bit removeArrayFormula(int rowIndex, int columnIndex) {
+ for (ArrayRecord ar : _arrayRecords) {
+ if (ar.isInRange(rowIndex, columnIndex)) {
+ _arrayRecords.remove(ar);
+ return ar.getRange();
+ }
+ }
+ throw new IllegalArgumentException("Specified cell is not part of an array formula.");
+ }
+
+ /**
+ * @return the shared ArrayRecord identified by (firstRow, firstColumn). never null
.
+ */
+ public ArrayRecord getArrayRecord(int firstRow, int firstColumn) {
+ for(ArrayRecord ar : _arrayRecords) {
+ if(ar.isFirstCell(firstRow, firstColumn)) {
+ return ar;
+ }
+ }
+ return null;
+ }
}
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
index ad1c535..2abf6dd 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
@@ -43,6 +43,7 @@ import org.apache.poi.hssf.record.SubRecord;
import org.apache.poi.hssf.record.TextObjectRecord;
import org.apache.poi.hssf.record.UnicodeString;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
+import org.apache.poi.hssf.record.formula.ExpPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
@@ -50,6 +51,7 @@ import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.RichTextString;
+import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.util.POILogger;
@@ -1144,4 +1146,30 @@ public class HSSFCell implements Cell {
}
return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
}
+
+ void setCellArrayFormula(CellRangeAddress range) {
+ int row=_record.getRow();
+ short col=_record.getColumn();
+ short styleIndex=_record.getXFIndex();
+ setCellType(CELL_TYPE_FORMULA, false, row, col, styleIndex);
+
+ // Billet for formula in rec
+ Ptg[] ptgsForCell = { new ExpPtg(range.getFirstRow(), range.getFirstColumn()) };
+ FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
+ agg.setParsedExpression(ptgsForCell);
+ }
+
+ public CellRangeAddress getArrayFormulaRange() {
+ if (_cellType != CELL_TYPE_FORMULA) {
+ throw new IllegalArgumentException("Only formula cells can have array ranges");
+ }
+ return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
+ }
+
+ public boolean isPartOfArrayFormulaGroup() {
+ if (_cellType != CELL_TYPE_FORMULA) {
+ return false;
+ }
+ return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
+ }
}
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
index c7972ec..5e3d6d4 100644
--- a/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
+++ b/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
@@ -31,6 +31,7 @@ import java.util.List;
import java.util.TreeMap;
import org.apache.poi.ddf.EscherRecord;
+import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.CellValueRecordInterface;
@@ -44,10 +45,13 @@ import org.apache.poi.hssf.record.SCLRecord;
import org.apache.poi.hssf.record.WSBoolRecord;
import org.apache.poi.hssf.record.WindowTwoRecord;
import org.apache.poi.hssf.record.aggregates.DataValidityTable;
+import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import org.apache.poi.hssf.record.aggregates.WorksheetProtectionBlock;
import org.apache.poi.hssf.record.formula.FormulaShifter;
+import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.hssf.util.Region;
+import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
@@ -64,6 +68,7 @@ import org.apache.poi.util.POILogger;
* @author Shawn Laubach (slaubach at apache dot org) (Just a little)
* @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)
* @author Yegor Kozlov (yegor at apache.org) (Autosizing columns)
+ * @author PUdalau set/remove array formulas
*/
public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
private static final POILogger log = POILogFactory.getLogger(HSSFSheet.class);
@@ -636,12 +641,12 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
/**
* Sets whether the worksheet is displayed from right to left instead of from left to right.
- *
+ *
* @param value true for right to left, false otherwise.
*/
public void setRightToLeft(boolean value)
{
- _sheet.getWindowTwo().setArabic(value);
+ _sheet.getWindowTwo().setArabic(value);
}
/**
@@ -651,7 +656,7 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
*/
public boolean isRightToLeft()
{
- return _sheet.getWindowTwo().getArabic();
+ return _sheet.getWindowTwo().getArabic();
}
/**
@@ -1870,4 +1875,46 @@ public final class HSSFSheet implements org.apache.poi.ss.usermodel.Sheet {
return wb.getSheetName(idx);
}
+ public void setArrayFormula(String formula, CellRangeAddress range) {
+ // make sure the formula parses OK first
+ Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, _workbook.getSheetIndex(this));
+ int firstRow = range.getFirstRow();
+ int firstColumn = range.getFirstColumn();
+ for (int rowIn = firstRow; rowIn <= range.getLastRow(); rowIn++) {
+ for (int colIn = firstColumn; colIn <= range.getLastColumn(); colIn++) {
+ HSSFRow row = getRow(rowIn);
+ if (row == null) {
+ row = createRow(rowIn);
+ }
+ HSSFCell cell = row.getCell(colIn);
+ if (cell == null) {
+ cell = row.createCell(colIn);
+ }
+ cell.setCellArrayFormula(range);
+ }
+ }
+ HSSFCell firstArrayFormulaCell = getRow(firstRow).getCell(firstColumn);
+ FormulaRecordAggregate agg = (FormulaRecordAggregate) firstArrayFormulaCell.getCellValueRecord();
+ agg.setArrayFormula(range, ptgs);
+ }
+
+
+ public void removeArrayFormula(Cell cell) {
+ CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
+ if (!(rec instanceof FormulaRecordAggregate)) {
+ throw new IllegalArgumentException("Specified cell is not a formula cell.");
+ }
+ FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
+ CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());
+ if (range == null) {
+ throw new IllegalArgumentException("Specified cell does not contain an array formula.");
+ }
+ // clear all cells in the range
+ for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
+ for (int colIn = range.getFirstColumn(); colIn <= range.getLastColumn(); colIn++) {
+ Cell rCell = getRow(rowIn).getCell(colIn);
+ rCell.setCellType(Cell.CELL_TYPE_BLANK);
+ }
+ }
+ }
}
diff --git a/src/java/org/apache/poi/ss/usermodel/Cell.java b/src/java/org/apache/poi/ss/usermodel/Cell.java
index 17af183..627d1d7 100644
--- a/src/java/org/apache/poi/ss/usermodel/Cell.java
+++ b/src/java/org/apache/poi/ss/usermodel/Cell.java
@@ -21,6 +21,7 @@ import java.util.Calendar;
import java.util.Date;
import org.apache.poi.ss.formula.FormulaParseException;
+import org.apache.poi.ss.util.CellRangeAddress;
/**
* High level representation of a cell in a row of a spreadsheet.
@@ -372,4 +373,15 @@ public interface Cell {
* @param link hypelrink associated with this cell
*/
void setHyperlink(Hyperlink link);
+
+ /**
+ * Only valid for array formula cells
+ * @return range of the array formula group that the cell belongs to.
+ */
+ CellRangeAddress getArrayFormulaRange();
+
+ /**
+ * @return true
if this cell is part of group of cells having a common array formula.
+ */
+ public boolean isPartOfArrayFormulaGroup();
}
diff --git a/src/java/org/apache/poi/ss/usermodel/Sheet.java b/src/java/org/apache/poi/ss/usermodel/Sheet.java
index 1ec70bd..544e703 100644
--- a/src/java/org/apache/poi/ss/usermodel/Sheet.java
+++ b/src/java/org/apache/poi/ss/usermodel/Sheet.java
@@ -30,6 +30,8 @@ import org.apache.poi.ss.util.CellRangeAddress;
* The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can
* contain text, numbers, dates, and formulas. Cells can also be formatted.
*