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 List _arrayRecords; private final TableRecord[] _tableRecords; private final Map _groupsBySharedFormulaRecord; /** cached for optimization purposes */ @@ -123,7 +126,7 @@ public final class SharedValueManager { if (nShF != firstCells.length) { throw new IllegalArgumentException("array sizes don't match: " + nShF + "!=" + firstCells.length + "."); } - _arrayRecords = arrayRecords; + _arrayRecords = toList(arrayRecords); _tableRecords = tableRecords; Map m = new HashMap(nShF * 3 / 2); for (int i = 0; i < nShF; i++) { @@ -133,6 +136,14 @@ public final class SharedValueManager { _groupsBySharedFormulaRecord = m; } + private static List toList(Z[] zz) { + List result = new ArrayList(zz.length); + for (int i = 0; i < zz.length; i++) { + result.add(zz[i]); + } + return result; + } + /** * @param firstCells * @param recs list of sheet records (possibly contains records for other parts of the Excel file) @@ -248,14 +259,12 @@ public final class SharedValueManager { // The first cell will be the top left in the range. So we can match the // ARRAY/TABLE record directly. - for (int i = 0; i < _tableRecords.length; i++) { - TableRecord tr = _tableRecords[i]; + for (TableRecord tr : _tableRecords) { if (tr.isFirstCell(row, column)) { return tr; } } - for (int i = 0; i < _arrayRecords.length; i++) { - ArrayRecord ar = _arrayRecords[i]; + for (ArrayRecord ar : _arrayRecords) { if (ar.isFirstCell(row, column)) { return ar; } @@ -269,10 +278,45 @@ public final class SharedValueManager { */ public void unlink(SharedFormulaRecord sharedFormulaRecord) { SharedFormulaGroup svg = _groupsBySharedFormulaRecord.remove(sharedFormulaRecord); - _groups = null; // be sure to reset cached value if (svg == null) { throw new IllegalStateException("Failed to find formulas for shared formula"); } + _groups = null; // be sure to reset cached value svg.unlinkSharedFormulas(); } + + /** + * Add specified Array Record. + */ + public void addArrayRecord(ArrayRecord ar) { + // could do a check here to make sure none of the ranges overlap + _arrayRecords.add(ar); + } + + /** + * Removes the {@link ArrayRecord} for the cell group containing the specified cell. + * The caller should clear (set blank) all cells in the returned range. + * @return the range of the array formula which was just removed. Never null. + */ + 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. *

+ * + * @author PUdalau set/remove array formulas */ public interface Sheet extends Iterable { @@ -781,4 +783,19 @@ public interface Sheet extends Iterable { */ 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); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java index 2ad093e..69e0955 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -37,6 +37,7 @@ import org.apache.poi.ss.usermodel.DateUtil; 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 @@ public final class XSSFCell implements Cell { if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false); CTCellFormula f = _cell.getF(); + if (isPartOfArrayFormulaGroup() && f == null) { + XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); + return cell.getCellFormula(); + } if(f.getT() == STCellFormulaType.SHARED){ return convertSharedFormula((int)f.getSi()); } @@ -363,6 +368,17 @@ public final class XSSFCell implements Cell { } public void setCellFormula(String formula) { + setFormula(formula, FormulaType.CELL); + } + + /* package */ void setCellArrayFormula(String formula, CellRangeAddress range) { + setFormula(formula, FormulaType.ARRAY); + CTCellFormula cellFormula = _cell.getF(); + cellFormula.setT(STCellFormulaType.ARRAY); + cellFormula.setRef(range.formatAsString()); + } + + private void setFormula(String formula, int formulaType) { XSSFWorkbook wb = _row.getSheet().getWorkbook(); if (formula == null) { wb.onDeleteFormula(this); @@ -372,7 +388,7 @@ public final class XSSFCell implements Cell { XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); //validate through the FormulaParser - FormulaParser.parse(formula, fpb, FormulaType.CELL, wb.getSheetIndex(getSheet())); + FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet())); CTCellFormula f = CTCellFormula.Factory.newInstance(); f.setStringValue(formula); @@ -453,7 +469,7 @@ public final class XSSFCell implements Cell { */ public int getCellType() { - if (_cell.getF() != null) { + if (_cell.getF() != null || getSheet().isCellInArrayFormulaContext(this)) { return CELL_TYPE_FORMULA; } @@ -911,4 +927,16 @@ public final class XSSFCell implements Cell { throw new RuntimeException("Unexpected cell type (" + cellType + ")"); } + public CellRangeAddress getArrayFormulaRange() { + XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); + if (cell == null) { + throw new IllegalStateException("not an array formula cell."); + } + String formulaRef = cell._cell.getF().getRef(); + return CellRangeAddress.valueOf(formulaRef); + } + + public boolean isPartOfArrayFormulaGroup() { + return getSheet().isCellInArrayFormulaContext(this); + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java index 318f2f6..d5f70ef 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -65,6 +65,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; * 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); @@ -78,6 +80,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { private ColumnHelper columnHelper; private CommentsTable sheetComments; private Map sharedFormulas; + private List arrayFormulas; /** * Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch. @@ -152,6 +155,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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); @@ -2317,6 +2321,10 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { 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 @@ -2674,4 +2682,54 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet { private boolean sheetProtectionEnabled() { return worksheet.getSheetProtection().getSheet(); } + + /* package */ boolean isCellInArrayFormulaContext(XSSFCell cell) { + for (CellRangeAddress range : arrayFormulas) { + if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { + return true; + } + } + return false; + } + + /* package */ XSSFCell getFirstCellInArrayFormula(XSSFCell cell) { + for (CellRangeAddress range : arrayFormulas) { + if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) { + 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.getRowIndex(), cell.getColumnIndex())) { + 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"); + } }