reworked patch

patch48292c.txt (text/plain), 27.77 KB, created by Josh Micich on 2009-11-30 18:39:51 UTC

(hide)

Description: reworked patch

Filename:

MIME Type:

Creator: Josh Micich

Created: 2009-11-30 18:39:51 UTC

Size: 27.77 KB

patch

obsolete

>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 <code>null</code> >+ */ >+ 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; > * </ul> > * > * @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<ArrayRecord> _arrayRecords; > private final TableRecord[] _tableRecords; > private final Map<SharedFormulaRecord, SharedFormulaGroup> _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<SharedFormulaRecord, SharedFormulaGroup> m = new HashMap<SharedFormulaRecord, SharedFormulaGroup>(nShF * 3 / 2); > for (int i = 0; i < nShF; i++) { >@@ -133,6 +136,14 @@ public final class SharedValueManager { > _groupsBySharedFormulaRecord = m; > } > >+ private static <Z> List<Z> toList(Z[] zz) { >+ List<Z> result = new ArrayList<Z>(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 <code>null</code>. >+ */ >+ 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 <code>null</code>. >+ */ >+ 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 <code>true</code> 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. > * </p> >+ * >+ * @author PUdalau set/remove array formulas > */ > public interface Sheet extends Iterable<Row> { > >@@ -781,4 +783,19 @@ public interface Sheet extends Iterable<Row> { > */ > 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. > * </p> >+ * >+ * @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<Integer, XSSFCell> sharedFormulas; >+ private List<CellRangeAddress> 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<Integer, XSSFRow>(); > sharedFormulas = new HashMap<Integer, XSSFCell>(); >+ arrayFormulas = new ArrayList<CellRangeAddress>(); > 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"); >+ } > } View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]