Index: src/java/org/apache/poi/hssf/record/ArrayRecord.java
===================================================================
--- src/java/org/apache/poi/hssf/record/ArrayRecord.java (revision 884552)
+++ src/java/org/apache/poi/hssf/record/ArrayRecord.java (working copy)
@@ -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,60 +31,100 @@
* Treated in a similar way to SharedFormulaRecord
*
* @author Josh Micich
+ * @author vabramovs(VIA) - Array Formula support
*/
public final class ArrayRecord extends SharedValueRecordBase {
- public final static short sid = 0x0221;
- private static final int OPT_ALWAYS_RECALCULATE = 0x0001;
- private static final int OPT_CALCULATE_ON_OPEN = 0x0002;
+ public final static short sid = 0x0221;
+ private static final int OPT_ALWAYS_RECALCULATE = 0x0001;
+ private static final int OPT_CALCULATE_ON_OPEN = 0x0002;
- private int _options;
- private int _field3notUsed;
- private Formula _formula;
+ private int _options;
+ private int _field3notUsed;
+ private Formula _formula;
- public ArrayRecord(RecordInputStream in) {
- super(in);
- _options = in.readUShort();
- _field3notUsed = in.readInt();
- int formulaTokenLen = in.readUShort();
- int totalFormulaLen = in.available();
- _formula = Formula.read(formulaTokenLen, in, totalFormulaLen);
- }
+ public ArrayRecord(RecordInputStream in) {
+ super(in);
+ _options = in.readUShort();
+ _field3notUsed = in.readInt();
+ int formulaTokenLen = in.readUShort();
+ int totalFormulaLen = in.available();
+ _formula = Formula.read(formulaTokenLen, in, totalFormulaLen);
+ }
- public boolean isAlwaysRecalculate() {
- return (_options & OPT_ALWAYS_RECALCULATE) != 0;
- }
- public boolean isCalculateOnOpen() {
- return (_options & OPT_CALCULATE_ON_OPEN) != 0;
- }
+ public ArrayRecord(int options, Formula formula, CellRangeAddress8Bit range ) {
+ super(range);
+ _options = options;
+ _field3notUsed = 0;
+ _formula = formula;
+ }
- protected int getExtraDataSize() {
- return 2 + 4
- + _formula.getEncodedSize();
- }
- protected void serializeExtraData(LittleEndianOutput out) {
- out.writeShort(_options);
- out.writeInt(_field3notUsed);
- _formula.serialize(out);
- }
+ public boolean isAlwaysRecalculate() {
+ return (_options & OPT_ALWAYS_RECALCULATE) != 0;
+ }
+ public boolean isCalculateOnOpen() {
+ return (_options & OPT_CALCULATE_ON_OPEN) != 0;
+ }
- public short getSid() {
- return sid;
- }
+ protected int getExtraDataSize() {
+ return 2 + 4
+ + _formula.getEncodedSize();
+ }
+ protected void serializeExtraData(LittleEndianOutput out) {
+ out.writeShort(_options);
+ out.writeInt(_field3notUsed);
+ _formula.serialize(out);
+ }
- public String toString() {
- StringBuffer sb = new StringBuffer();
- sb.append(getClass().getName()).append(" [ARRAY]\n");
- sb.append(" range=").append(getRange().toString()).append("\n");
- sb.append(" options=").append(HexDump.shortToHex(_options)).append("\n");
- sb.append(" notUsed=").append(HexDump.intToHex(_field3notUsed)).append("\n");
- sb.append(" formula:").append("\n");
- Ptg[] ptgs = _formula.getTokens();
- for (int i = 0; i < ptgs.length; i++) {
- Ptg ptg = ptgs[i];
- sb.append(ptg.toString()).append(ptg.getRVAType()).append("\n");
- }
- sb.append("]");
- return sb.toString();
- }
+ public short getSid() {
+ return sid;
+ }
+
+ public String toString() {
+ StringBuffer sb = new StringBuffer();
+ sb.append(getClass().getName()).append(" [ARRAY]\n");
+ sb.append(" range=").append(getRange().toString()).append("\n");
+ sb.append(" options=").append(HexDump.shortToHex(_options)).append("\n");
+ sb.append(" notUsed=").append(HexDump.intToHex(_field3notUsed)).append("\n");
+ sb.append(" formula:").append("\n");
+ Ptg[] ptgs = _formula.getTokens();
+ for (int i = 0; i < ptgs.length; i++) {
+ Ptg ptg = ptgs[i];
+ sb.append(ptg.toString()).append(ptg.getRVAType()).append("\n");
+ }
+ 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
*
* @author Josh Micich
+ * @author vabramovs(VIA) add getArray
*/
public final class SharedValueManager {
@@ -111,7 +112,7 @@
public static final SharedValueManager EMPTY = new SharedValueManager(
new SharedFormulaRecord[0], new CellReference[0], new ArrayRecord[0], new TableRecord[0]);
- private final ArrayRecord[] _arrayRecords;
+ private ArrayRecord[] _arrayRecords;
private final TableRecord[] _tableRecords;
private final Map _groupsBySharedFormulaRecord;
/** cached for optimization purposes */
@@ -275,4 +276,36 @@
}
svg.unlinkSharedFormulas();
}
+
+ /**
+ * Get array, if this is Array Formula, null otherwise
+ *
+ * @return Records for all array formulas.
+ */
+ public ArrayRecord[] getArray() {
+ return this._arrayRecords;
+ }
+
+ /**
+ * Add specified Array Record.
+ */
+ public void addArrayRecord(ArrayRecord ar) {
+ ArrayRecord[] newArray = new ArrayRecord[_arrayRecords.length + 1];
+ System.arraycopy(_arrayRecords, 0, newArray, 0, _arrayRecords.length);
+ newArray[_arrayRecords.length] = ar;
+ _arrayRecords = newArray;
+ }
+
+ /**
+ * Remove Array Record by index
+ */
+ public void removeArrayRecord(int index) {
+ if (index < 0 || index > _arrayRecords.length) {
+ throw new RuntimeException("Array Record did not find ");
+ }
+ ArrayRecord[] newArray = new ArrayRecord[_arrayRecords.length - 1];
+ System.arraycopy(_arrayRecords, 0, newArray, 0, index);
+ System.arraycopy(_arrayRecords, index + 1, newArray, index, newArray.length - index);
+ _arrayRecords = newArray;
+ }
}
Index: src/java/org/apache/poi/hssf/record/constant/ConstantValueParser.java
===================================================================
--- src/java/org/apache/poi/hssf/record/constant/ConstantValueParser.java (revision 884552)
+++ src/java/org/apache/poi/hssf/record/constant/ConstantValueParser.java (working copy)
@@ -17,6 +17,7 @@
package org.apache.poi.hssf.record.constant;
+import org.apache.poi.hssf.record.UnicodeString;
import org.apache.poi.util.LittleEndianInput;
import org.apache.poi.util.LittleEndianOutput;
import org.apache.poi.util.StringUtil;
@@ -109,7 +110,13 @@
if(cls == Boolean.class || cls == Double.class || cls == ErrorConstant.class) {
return 8;
}
- String strVal = (String)object;
+ String strVal;
+ if (object instanceof UnicodeString) {
+ strVal = ((UnicodeString) object).getString();
+
+ } else {
+ strVal = (String) object;
+ }
return StringUtil.getEncodedSize(strVal);
}
@@ -144,6 +151,11 @@
StringUtil.writeUnicodeString(out, val);
return;
}
+ if (value instanceof UnicodeString) {
+ out.writeByte(TYPE_STRING);
+ StringUtil.writeUnicodeString(out, ((UnicodeString) value).getString());
+ return;
+ }
if (value instanceof ErrorConstant) {
ErrorConstant ecVal = (ErrorConstant) value;
out.writeByte(TYPE_ERROR_CODE);
Index: src/java/org/apache/poi/hssf/record/formula/ExpPtg.java
===================================================================
--- src/java/org/apache/poi/hssf/record/formula/ExpPtg.java (revision 884552)
+++ src/java/org/apache/poi/hssf/record/formula/ExpPtg.java (working copy)
@@ -39,6 +39,12 @@
field_2_first_col = in.readShort();
}
+ public ExpPtg(short field_1_first_row,short field_2_first_col)
+ {
+ this.field_1_first_row = field_1_first_row;
+ this.field_2_first_col = field_2_first_col;
+ }
+
public void write(LittleEndianOutput out) {
out.writeByte(sid + getPtgClass());
out.writeShort(field_1_first_row);
Index: src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
===================================================================
--- src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (revision 884552)
+++ src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (working copy)
@@ -43,6 +43,7 @@
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.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;
@@ -608,6 +610,19 @@
}
agg.setParsedExpression(ptgs);
}
+
+ /* package */void setCellArrayFormula(String formula, 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((short) range.getFirstRow(), (short) range.getFirstColumn()) };
+ FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
+ agg.setParsedExpression(ptgsForCell);
+ }
+
/**
* Should be called any time that a formula could potentially be deleted.
* Does nothing if this cell currently does not hold a formula
@@ -1155,4 +1170,15 @@
}
return ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
}
+
+ public CellRangeAddress getArrayFormulaRange() {
+ if(_record instanceof FormulaRecordAggregate){
+ return ((FormulaRecordAggregate)_record).getFormulaRange();
+ }
+ return null;
+ }
+
+ public boolean isArrayFormulaContext() {
+ return getArrayFormulaRange() != null;
+ }
}
Index: src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
===================================================================
--- src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (revision 884552)
+++ src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (working copy)
@@ -31,8 +31,10 @@
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.ArrayRecord;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.record.DVRecord;
import org.apache.poi.hssf.record.EscherAggregate;
@@ -44,10 +46,16 @@
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.SharedValueManager;
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.CellRangeAddress8Bit;
import org.apache.poi.hssf.util.PaneInformation;
import org.apache.poi.hssf.util.Region;
+import org.apache.poi.ss.formula.Formula;
+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 +72,7 @@
* @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);
@@ -1870,4 +1879,50 @@
return wb.getSheetName(idx);
}
+ public void setArrayFormula(String formula, CellRangeAddress range) {
+ for (int rowIn = range.getFirstRow(); rowIn <= range.getLastRow(); rowIn++) {
+ for (int colIn = range.getFirstColumn(); 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(formula, range);
+ }
+ }
+ registerArrayFormulaInSharedValueManager(formula, range);
+ }
+
+ private void registerArrayFormulaInSharedValueManager(String formula, CellRangeAddress range){
+ HSSFCell firstArrayFormulaCell = getRow(range.getFirstRow()).getCell(range.getFirstColumn());
+ FormulaRecordAggregate agg = (FormulaRecordAggregate) firstArrayFormulaCell.getCellValueRecord();
+ Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.CELL, _workbook.getSheetIndex(this));
+ ArrayRecord arr = new ArrayRecord(2/* options */, Formula.create(ptgs), new CellRangeAddress8Bit(range
+ .getFirstRow(), range.getLastRow(), range.getFirstColumn(), range.getLastColumn()));
+ agg.getSharedValueManager().addArrayRecord(arr);
+ }
+
+ public void removeArrayFormula(Cell cell) {
+ CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
+ if (rec instanceof FormulaRecordAggregate) {
+ SharedValueManager sm = ((FormulaRecordAggregate) rec).getSharedValueManager();
+ ArrayRecord[] array = sm.getArray();
+ for (int i = 0; i < array.length; i++) {
+ ArrayRecord ar = (ArrayRecord) array[i];
+ if (ar.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
+ sm.removeArrayRecord(i);
+ for (int rowIn = ar.getFirstRow(); rowIn <= ar.getLastRow(); rowIn++)
+ for (int colIn = ar.getFirstColumn(); colIn <= ar.getLastColumn(); colIn++) {
+ Cell rCell = this.getRow(rowIn).getCell(colIn);
+ rCell.setCellType(Cell.CELL_TYPE_BLANK);
+ }
+ return;
+ }
+ }
+ }
+ throw new RuntimeException("Cell does not belong to Array Formula");
+ }
}
Index: src/java/org/apache/poi/ss/usermodel/Cell.java
===================================================================
--- src/java/org/apache/poi/ss/usermodel/Cell.java (revision 884552)
+++ src/java/org/apache/poi/ss/usermodel/Cell.java (working copy)
@@ -20,6 +20,8 @@
import java.util.Calendar;
import java.util.Date;
+import org.apache.poi.ss.util.CellRangeAddress;
+
/**
* High level representation of a cell in a row of a spreadsheet.
*
@@ -364,4 +366,16 @@
* @param link hypelrink associated with this cell
*/
void setHyperlink(Hyperlink link);
+
+ /**
+ * get reference for Array Formula
+ * @return
+ */
+ CellRangeAddress getArrayFormulaRange();
+
+ /**
+ * Check is cell belong to Array Formula Range
+ * @return
+ */
+ public boolean isArrayFormulaContext();
}
Index: src/java/org/apache/poi/ss/usermodel/Sheet.java
===================================================================
--- src/java/org/apache/poi/ss/usermodel/Sheet.java (revision 884552)
+++ src/java/org/apache/poi/ss/usermodel/Sheet.java (working copy)
@@ -30,6 +30,8 @@
* 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.
*
+ *
+ * @author PUdalau set/remove array formulas
*/
public interface Sheet extends Iterable {
@@ -781,4 +783,19 @@
*/
boolean isSelected();
+
+ /**
+ * Sets array formula to specified region for result.
+ *
+ * @param formula Formula
+ * @param range Region of array formula for result.
+ */
+ void setArrayFormula(String formula, CellRangeAddress range);
+
+ /**
+ * Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well
+ *
+ * @param cell any cell within Array Formula range
+ */
+ void removeArrayFormula(Cell cell);
}
Index: src/java/org/apache/poi/ss/util/CellRangeAddress.java
===================================================================
--- src/java/org/apache/poi/ss/util/CellRangeAddress.java (revision 884552)
+++ src/java/org/apache/poi/ss/util/CellRangeAddress.java (working copy)
@@ -84,6 +84,9 @@
public static CellRangeAddress valueOf(String ref) {
int sep = ref.indexOf(":");
+ if (sep == -1) {
+ return valueOf(ref + ":" + ref);
+ }
CellReference cellFrom = new CellReference(ref.substring(0, sep));
CellReference cellTo = new CellReference(ref.substring(sep + 1));
return new CellRangeAddress(cellFrom.getRow(), cellTo.getRow(), cellFrom.getCol(), cellTo.getCol());
Index: src/java/org/apache/poi/ss/util/CellRangeAddressBase.java
===================================================================
--- src/java/org/apache/poi/ss/util/CellRangeAddressBase.java (revision 884552)
+++ src/java/org/apache/poi/ss/util/CellRangeAddressBase.java (working copy)
@@ -18,6 +18,7 @@
package org.apache.poi.ss.util;
import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.Cell;
/**
@@ -111,6 +112,13 @@
return _lastRow;
}
+ public boolean isInRange(Cell cell){
+ int rowInd = cell.getRowIndex();
+ int colInd = cell.getColumnIndex();
+ return ( getFirstRow() <= rowInd && rowInd <= getLastRow() &&
+ getFirstColumn() <= colInd && colInd <= getLastColumn());
+ }
+
/**
* @param firstCol column number for the upper left hand corner
*/
Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
===================================================================
--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (revision 884552)
+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (working copy)
@@ -37,6 +37,7 @@
import org.apache.poi.ss.usermodel.FormulaError;
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.util.CellReference;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
@@ -336,6 +337,10 @@
if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);
CTCellFormula f = _cell.getF();
+ if (isArrayFormulaContext() && f == null) {
+ XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
+ return cell.getCellFormula();
+ }
if(f.getT() == STCellFormulaType.SHARED){
return convertSharedFormula((int)f.getSi());
}
@@ -391,6 +396,13 @@
if(_cell.isSetV()) _cell.unsetV();
}
+ /* package */ void setCellArrayFormula(String formula, CellRangeAddress range) {
+ setCellFormula(formula);
+ CTCellFormula cellFormula = _cell.getF();
+ cellFormula.setT(STCellFormulaType.ARRAY);
+ cellFormula.setRef(range.formatAsString());
+ }
+
/**
* Returns column index of this cell
*
@@ -463,11 +475,9 @@
* @see Cell#CELL_TYPE_ERROR
*/
public int getCellType() {
-
- if (_cell.getF() != null) {
+ if (_cell.getF() != null || getSheet().isCellInArrayFormulaContext(this)) {
return CELL_TYPE_FORMULA;
- }
-
+ }
return getBaseCellType(true);
}
@@ -904,4 +914,17 @@
throw new RuntimeException("Unexpected cell type (" + cellType + ")");
}
+ public CellRangeAddress getArrayFormulaRange() {
+ XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
+ if (cell != null) {
+ String formulaRef = cell._cell.getF().getRef();
+ return CellRangeAddress.valueOf(formulaRef);
+ } else {
+ return null;
+ }
+ }
+
+ public boolean isArrayFormulaContext() {
+ return getSheet().isCellInArrayFormulaContext(this);
+ }
}
Index: src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
===================================================================
--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (revision 884552)
+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java (working copy)
@@ -39,6 +39,7 @@
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
@@ -95,6 +96,8 @@
* 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.
*
+ *
+ * @author PUdalau set/remove array formulas
*/
public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
private static final POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
@@ -106,6 +109,7 @@
private ColumnHelper columnHelper;
private CommentsTable sheetComments;
private Map sharedFormulas;
+ private List arrayFormulas;
/**
* Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch.
@@ -180,6 +184,7 @@
private void initRows(CTWorksheet worksheet) {
rows = new TreeMap();
sharedFormulas = new HashMap();
+ arrayFormulas = new ArrayList();
for (CTRow row : worksheet.getSheetData().getRowArray()) {
XSSFRow r = new XSSFRow(row, this);
rows.put(r.getRowNum(), r);
@@ -2280,6 +2285,10 @@
if(f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null){
sharedFormulas.put((int)f.getSi(), cell);
}
+ CTCellFormula formula = ct.getF();
+ if (formula != null && formula.getT() == STCellFormulaType.ARRAY && formula.getRef() != null) {
+ arrayFormulas.add(CellRangeAddress.valueOf(formula.getRef()));
+ }
}
@Override
@@ -2637,4 +2646,54 @@
private boolean sheetProtectionEnabled() {
return worksheet.getSheetProtection().getSheet();
}
+
+ /* package */ boolean isCellInArrayFormulaContext(XSSFCell cell) {
+ for (CellRangeAddress range : arrayFormulas) {
+ if (range.isInRange(cell)) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ /* package */ XSSFCell getFirstCellInArrayFormula(XSSFCell cell) {
+ for (CellRangeAddress range : arrayFormulas) {
+ if (range.isInRange(cell)) {
+ return getRow(range.getFirstRow()).getCell(range.getFirstColumn());
+ }
+ }
+ return null;
+ }
+
+ public void setArrayFormula(String formula, CellRangeAddress range) {
+ XSSFRow row = getRow(range.getFirstRow());
+ if (row == null) {
+ row = createRow(range.getFirstRow());
+ }
+ XSSFCell mainArrayFormulaCell = row.getCell(range.getFirstColumn());
+ if (mainArrayFormulaCell == null) {
+ mainArrayFormulaCell = row.createCell(range.getFirstColumn());
+ }
+ mainArrayFormulaCell.setCellArrayFormula(formula, range);
+ arrayFormulas.add(range);
+ }
+
+ public void removeArrayFormula(Cell cell) {
+ for (CellRangeAddress range : arrayFormulas) {
+ if (range.isInRange(cell)) {
+ arrayFormulas.remove(range);
+ for (int rowIndex = range.getFirstRow(); rowIndex <= range.getLastRow(); rowIndex++) {
+ XSSFRow row = getRow(rowIndex);
+ for (int columnIndex = range.getFirstColumn(); columnIndex <= range.getLastColumn(); columnIndex++) {
+ XSSFCell arrayFormulaCell = row.getCell(columnIndex);
+ if (arrayFormulaCell != null) {
+ arrayFormulaCell.setCellType(Cell.CELL_TYPE_BLANK);
+ }
+ }
+ }
+ return;
+ }
+ }
+ throw new RuntimeException("Cell does not belong to Array Formula");
+ }
}